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()
ANY关键字:
假设any内部的查询语句返回的结果个数是三个,
那么,
select …from …where a>any(…)
select …from …where a > result1 or a > result2 or a > result3
ALL关键字:
ALL关键字与any关键字类似,只不过上面的or改成and。
SOME关键字:
some关键字和any关键字是一样的功能。
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,
————————————————
版权声明:本文为CSDN博主「yayazhang8180」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/yayazhang8180/article/details/80556239