mysql基本操作

安装mysql

mysqld install

删除mysql

mysqld remove

进入mysql

mysql -u root -p

查看所有数据库

 show databases;

进入某个数据库

use 数据库名;

查看所有的表

 show tables;

查看表结构

 desc t_student;

修改表结构

alter table t_user  add sex varchar(10);
alter table t_user drop sex ;
alter table t_user change sex  sex1;
alter table t_user change sex1 sex varchar(10) after password;     

创建一个表

 create table t_student1 (
		id int(11) primary key AUTO_INCREMENT,
		name varchar(20),
		age int(10),
		num int(10),
		born datetime,
		cla_id int(10),
		constraint foreign key (cla_id) references t_classroom (id)
 
 );

删除某个表 ；

  drop table t_student;

插入一条数据

insert into t_student (xx,xx,xx) value ('xx','xx','xx');
insert into t_student values(null,'xxx','xxx');
insert into t_student('xx','xx') select  xx,xx from t_student2;

更新一条数据

update 表名 set 字段名=新值 where 条件;

删除一条数据

delete from 表名 where 条件; 

清除表中内容，序号也变为0，

 truncate table t_student;

查询表中所有内容

select * from t_student;

条件查询

select * from t_student where 条件;
and ,or, in(x,x),  xx like('xx%')

分组group by

 select  cla_id  from t_stu group by cla_id;

显示年

select year(born) from t_stu;

年分组，having用于映射的字段，where不能用映射的字段

select year(born) as y from t_stu group by y;
select year(born) as y from t_stu group by y having y>1997;

计数，最大，最小

select count(*) from t_stu;
select max(born) from t_stu;
select min(born) from t_stu;

跨表查询，内连接（原来是有3个系有一个系没人，就不会显示，左右链接会解决这个问题）

select * from t_stu t1,t_cla t2 where t1.cla_id=t2.id and t2.id in (1,2);
select * from t_cla t1 join t_stu t2 on(t1.id=t2.cla_id)  where t1.id in (1,2);
select * from t_stu t1 join t_cla t2 on(t1.cla_id=t2.id) join t_spa t3 on(t2.spa_id=t3.id) join t_dep t4 on(t4.id=t3.dep_id) where t4.name='中文系';
select t4.name,t3.name,t1.sex,count(t1.id)  from t_stu t1 join t_cla t2 on(t1.cla_id=t2.id) join t_spa t3 on(t2.spa_id=t3.id) join t_dep t4 on(t4.id=t3.dep_id) group by t4.name,t3.name,t1.sex;

跨表查询，左连接，右链接。（左、右表的表和右、左的表对应，如果没有用null填充）

 select t4.name,t3.name,t1.sex,count(t1.id)  from t_stu t1 right  join t_cla t2 on(t1.cla_id=t2.id) right  join t_spa t3 on(t2.spa_id=t3.id) right join t_dep t4 on(t4.id=t3.dep_id) group by t4.name,t3.name,t1.sex;