安装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;