Mysql高阶语句

Mysql高阶语句

去重查询 : distinct

1、创建库

create database xy102;  create table info ( id int primary key, name varchar(10), score decimal(5,2), address varchar(20), hobbid int(5) );  insert into info values(1,'liuyi',80,'beijing',2); insert into info values(2,'wangwu',90,'shengzheng',2); insert into info values(3,'lisi',60,'shanghai',4); insert into info values(4,'tianqi',99,'hangzhou',5); insert into info values(5,'jiaoshou',98,'laowo',3); insert into info values(6,'hanmeimei',10,'nanjing',3); insert into info values(7,'lilei',11,'nanjing',5);  # 一行一行运行  select * from info; 

1.1 排序语法

1.1.1 升序和降序

1、# 升序和降序 # 默认的排序方式就是升序 ASC--升序 DESC--降序  需要配合order by语法  select * from info order by name; # 会默认按照字母顺序升序排列  select * from info order by name desc; # 按照字母的降序排列  select * from info order by id desc; # 按照id的大小进行降序排列  # 注意:以多个列作为排序关键字，只有第一个参数有相同的值，第二个字段才有意义,即在满足第一个条件的情况，且第一个条件大小一样，这个时候才开始比较后面的条件，否者直接执行第一个条件 select * from info order by hobbid,id;  select * from info order by hobbid desc,id;  

1.2 where条件的筛选

where条件的筛选功能（比较符号）

1.2.1 区间判断

1.2.2 嵌套多个条件

2 # where条件的筛选功能(比较符号) 2.1 # 区间判断: and   or  select * from info where score > 70 and score <=90; # 满足大于70小于等于90的数  select * from info where score > 90 or score <=70; # 满足大于90，或者小于等于70的数  2.2 # 嵌套多条件  select * from info where score > 70 or ( score > 0 and score < 60 ); # 满足大于70或者满足大于0小于60的 

1.3 分组查询

1.3.1 聚合函数

1.3.2 where和having

3、# 分组查询--对sql查询的结果进行分组，使用group by语句来使用，group by语句必须配合聚合函数一起使用  # 聚合函数类型:统计(count)、求和(sum)、求和平均数(avg)、最大值(max)、最小值(min)   select count(name),hobbid from info group by hobbid; # 以hobbid为分组，统计hobbid分组的有几个  select count(name),hobbid,name from info group by hobbid,name; # 在聚合函数分组语句中，所有的非聚合函数列，都要在group by语句当中，否则即使不报错，但是运行结果也是有问题的  # group by外面可以使用where select count(name),hobbid from info where score >=80 group by hobbid; # 大于等于80，以hobbid为列，统计name  # group by里面使用having select count(name),hobbid,score from info group by hobbid,score having score >=80; # 大于等于80，以hobbid为列，统计name，此时前后都需要有score，否则会报错  # 以兴趣为列作为分组，然后计算成绩的平均值，统计结果筛选出分组的平均成绩大于等于60分 select avg(score),hobbid from info group by hobbid having avg(score) >=60;  # 统计姓名，以兴趣和分数作为分组，统计出成绩大于80的，然后按照降序，对统计姓名的列进行 select count(name),hobbid,score from info group by hobbid,score having score > 80 order by count(name) desc;  select count(name),hobbid,score from info where score >80 group by hobbid,score desc;（待查证） 

1.4 limit

4、# limit # limit 1,3 1表示位子偏移量(可选参数),如果不设置位置偏移量，默认就从第一行开始，默认的值是 0   select * from info limit 1,3; # 显示2、3、4行 select * from info limit 3; # 显示1、2、3行  # 使用limit和降序排列，只显示最后三行 select * from info order by id desc limit 3; 

1.5 表和列的别名

1.5.1 表和列的别名

1.5.2 复制表

5、# 表和列的别名 # 在实际工作中，表的名字和列的名字可能会很长，书写起来可能会很长，多次声明表和列时，完整展示太复杂，设置别名可以使书写简化，可读性增加，简洁明了  # 给列取别名 select name as 姓名,score as 成绩 from info; select name 姓名, score 成绩 from info;  # 给表起别名 select i.name 姓名,i.score 成绩 from info as i; select i.name 姓名,i.score 成绩 from info i;  # 对表进行复制 create table test as select * from info;  # 对比info和test表,使用下面命令 desc test; desc info; # 可以对比出，复制的test的表没有主键，即主键无法复制  #  create table test1 as select * from info where score >= 60; select * from test1; 

1.6 通配符

1.6.1 模糊查询

1.6.2 关联语法

6、# 通配符: 6.1 # like 模糊查询 # % 表示零个，一个或者多个字符 * # _ 表示单个字符  select * from info where address like 's%'; # 表示以s为开头即可 select * from info where address like 's_'; # 表示s后面有单个字符 select * from info where address like 's__'; # 表示s后面有两个单个字符 select * from info where address like 's%_'; # 查询以s为开头，后面的字符均认为是单个字符  # 子查询:内查询,又叫嵌套查询,select语句当中又嵌套了一个select,()里面嵌套的才是子查询,在查询的时候先执行子查询的语句，外部的select在再根据子条件的结果进行过滤查询 # 子查询可以是多个表，也可以是同一张表  6.2 # 关联语法 in、not in、exists select (select)  select id,name,score from info where id in ( select id from info where score>=80); # 先运行括号里面的内容,即分数大于80的id,外面where的id只运行和括号里面的一致的,这时候运行name和score  select id,name,score from info where id in ( select id from test1 where score>=80); # 先运行括号里面的表test1中分数大于80的id,外面where的id只运行和括号里面的一致的,这时候运行name和score    select * from info; # 更新第四列，把99分变成80分，用子查询语句 update info set score=80 where id in ( select id from test1 where id=4);  update info set score=80 where id in ( select id from test1 where score=99);  # exists 判断子查询的结果是否为空，不为空返回true,空返回false  select count(*) from info where exists(select id from test where score>80); # 注意这里不是in和not in，括号里面的结果不会传给主表，这里括号里面只是一个判断条件，括号内存在才执行括号外的表，不存在，则执行结果为0  # 查询分数，如果分数小于50的则统计info的字段数 select count(*) from info where exists(select id from test where score<50); 

1.7 子查询当中多表查询和别名

7、# 子查询当中多表查询和别名  # info表和test表，这两张表id部分相同，然后根据id相同部分，查询info表的id的值  select a.id from info a where id in (select b.id from test b where a.id = b.id); # a是info的别名,a.id表示info表的id;b是test的别名,b.id表示test表的id  # 查询出info表成绩大于80的数据 select a.name from info a where  a.score >80 and a.id in (select b.id from test b where a.id =b.id);  # 查询info表的平均成绩 select avg(a.score),a.id from info a where id in ( select b.id from test b where a.id = b.id);  select avg(a.score) from info a where a.id in ( select b.id from test b where a.id = b.id); 

1.8 mysql的视图

1.8.1 查看视图

1.8.2 创建视图

1.8.3 删除视图

1.8.4 视图和表的区别

8、# mysql的视图 # 视图是一个虚拟表，表的数据基于查询的结果生成，视图可以简化复杂的查询，可以隐藏复杂的细节，访问数据更安全。它是多表数据的集合体 # 视图和表的区别 1、存储方式:表是实际的数据行，视图不存储数据，仅仅是查询结果的虚拟表 2、数据更新:更新表可以直接更新视图表的数据 3、占用空间:表实际占用空间，视图不占用空间，只是一个动态结果的展示 总结:视图表的数据可能是一张表的部分查询数据，也可能是多个表的一部分查询数据  # 查看当前数据库中的视图表(VIEW需要大写) show full tables in xy102 where table_type like 'VIEW';  # 创建视图表(test2) create view test2 as select * from info where score >= 80;  select * from test2; # 执行这个命令相当于执行select * from info where score >= 80;这个命令  update test2 set score=90 where id =2; # 5.5之后修改视图表，视图表和表都会更新  # 对比两个表的结构 desc test2; desc info; # 结果test2,无主键  # 创建一张视图表，视图表包含id name address, 从info和test当中name值相同的部分创建，视图表的名v_info  create view v_info as select a.id,a.name,a.address from info a where a.name in (select b.name from test b where a.name = b.name);  select * from v_info;  # 视图表就是查询语句的别名，有了视图表可以简化查询的语句 # 表的权限是不一样的，库的权限是有控制的，索引查询视图表的权限相对低，既可以保证原表的数据安全，也简化了查询的过程  # 删除视图表 drop view test2; 

1.9 连接查询

1.9.1 内连接

1.9.2 左连接

19.3 右连接

9、# 连接查询 # 把两张表或者多个表的记录结合起来，基于这些表共同的字段，进行数据的拼接 # 首先，要确定一个主表作为结果集,然后将其他表的行有选择性的选定到主表的结果上。  9.1 # 内连接 # 两张表或者多张表之间符合条件的数据记录的集合 # 语法:inner join  create table test1 ( a_id int(11) default null, a_name varchar(32) default null, a_level int(11) default null);  create table test2 ( b_id int(11) default null, b_name varchar(32) default null, b_level int(11) default null);  insert into test1 values (1,'aaaa',10); insert into test1 values (2,'bbbb',20); insert into test1 values (3,'cccc',30); insert into test1 values (4,'dddd',40);  insert into test2 values (2,'bbbb',20); insert into test2 values (3,'cccc',30); insert into test2 values (5,'eeee',50); insert into test2 values (6,'ffff',60);  select a.a_id,a.a_name from test1 a inner join test2 b on a.a_name = b.b_name; select a.a_id,a.a_name,b.id,b.name from test1 a inner join test2 b on a.a_name = b.b_name;   # 内连接总结:取两个表或者多个表之间的交集  9.2 # 左连接:左外连接，left jion、left outer join # 左连接以左表为基础，接收左表的所有行，以左表的记录和右边的记录进行匹配，匹配左表的所有以及右表中符合条件的行，不符合的显示null。  # 写在left左边就是左表，写在left右边就是右表 select * from test1 a left join test2 b on a.a_name =b.b_name; # 以比较条件为标准，展示结果，两个表相同部分展示出来，做拼接，不同结果显示null  9.3 # 右连接:右外连接，right jion、right outer join # 右连接以左表为基础，接收右表的所有行，以右表的记录和右边的记录进行匹配，匹配右表的所有以及左表中符合条件的行，不符合的显示null。  # 写在right左边就是左表，写在right右边就是右表