MYSQL知识整理.LV2
MYSQL知识整理.LV2
()表结果,[ ]表范围
进入数据库 mysql –u root –p
select * from student;
select * from student where gender = 'female';
select name from student where age between 22 and 24;
select name from student where age<>25;
select classid from student union select classid from teacher;
select name from student where classid = (select classid from teacher where name ='A');
select name,gender from student join teacher on teacher.classid = student.classid;
update student set name = 'daxiong',age =22 where name = 'john';
delete from student where name = 'daxiong';
查看列:desc 表名;
修改表名:alter table t_book rename to bbb;添加列:alter table 表名 add column 列名 varchar(30);
删除列:alter table 表名 drop column 列名;
修改列名MySQL: alter table bbb change nnnnn hh int;
修改列属性:alter table t_book modify name varchar(22);
查看表的所有信息:show create table 表名;
添加主键约束:alter table 表名 add constraint 主键 (形如:PK_表名) primary key 表名(主键字段);
添加外键约束:alter table 从表 add constraint 外键(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
删除主键约束:alter table 表名 drop primary key;
删除外键约束:alter table 表名 drop foreign key 外键(区分大小写)
alter table 。。rename to 。。\\改名字
select 你要查询的东西 from 表名;\\\\你要查询的东西 name,*
select 你要查询的东西 from 表名 你的条件;
---模糊查询
select name from where name like ‘a%’;--名字a开头
---更新语句
update 表名 set (改的信息,,,) where 你的条件;
---删除语句
delete from 表名 where 你的条件;
select A from 表名 union (distinct)select A from 表名;
select A from 表名 union all select A from 表名;
select A from 表名 order by (排序方式)
select A from 表名 order by desc(降序排序方式)
select count(1) from 表名 group by 你的条件;
show create table 表名;(找到建表的语句)
eg:SELECT AVG(grade ) as averpoint FROM score;--平均值
-- SELECT SUM(grade) as sumgrade FROM score;--求和
-- SELECT COUNT(grade) as count from score;--计数
-- select MAX(grade) from score;---最高分数
-- select MIN(grade),score.name from score GROUP BY name;---分数最低的姓名
-- SELECT grade,name from score where grade=(select MIN(grade) from score);--分数最低的姓名
-- select gender,COUNT(*) from score GROUP BY gender;--性别分组计数
select gender,COUNT(1) from score GROUP BY gender HAVING gender='boy';男生分组计数
事务
开始-begin
结束-commit
回滚-rollback
子查询(最多嵌套32个)
select 最终结果 from 表 where 列名=(select 外查询的条件 from 表)
select * from 表1,表2 where 表1.A=表2.A and 题目的条件
eg:
select name from NAME where age=(select age from CLASS where class='A');
in: select name from name where age in(select age from class where class='A');--内查询结果是多个值时用in替换=又或者是在=后面加上some或者any
some: select name from name where age=some(select age from class where class='A’);----有些值为True,那么结果就为True
all:select name from name where age>any(select age from class where class='A’);----都为true,那么结果才能为true
内查询条件=some()
内查询条件=any()
内查询条件 in()
select 语句 in{select 语句 in{select 语句 in{select 语句 in{........}}}}---最多嵌套32个
(inner)join,连接查询
--别名 select * from student (as) s where s.id=1;
1)select * from student (inner) join teacher on student.classid=teacher.classid;
2)select * from student left join teacher on student.classid=teacher.classid;
3)select * from student right join teacher on student.classid=teacher.classid;
---teacher和student交换,并将right改为left,