mysql高阶语句

高级语法的查询语句

基础语句

<code>select * from 表名 where

limits

like

distinct

高阶语句

先建个表

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);

排序语法

也叫做关键字排序,要么升序要么降序,默认的排序方式就是升序

升序:asc

降序:desc

配合order by

#默认就是升序,加上desc就是降序

select * from info order by name desc;

select * from info order by id desc;

#多列排序有讲究,以多个列做为排序关键字,只有第一个参数有相同的值,第二个字段才有意义

select * from info order by hobbid desc,id;

select * from info order by score desc,id;

select id ,hobbid from info order by hobbid desc,id;

where条件筛选

#区间判断

select * from info where score > 70 and score <=90;

select * from infro where score >70 or score <=90;

嵌套多条件

select * from info where score > 70 or (score > 0 and score < 60);

分组查询

#分组查询,对sql查询的结果进行分组,使用group by语句实现

#只能配合聚合函数一块使用

#常用的聚合函数

#统计 couunt

#求和 sum

#求平均数 avg

#最大值 max

#最小值 min

#有聚合函数才能有group by

#最少要有两列,name列是被替换成了统计hobbid的次数

select count(name),hobbid from info group by hobbid;

#在聚合函数分组语句中,所有的非聚合函数列,都要在group by 语句当中.

select count(name),name,hobbid from info group by hobbid,name;

select count(name),hobbid from info where score >=80 group by hobbid;

select count(name),hobbid,name from info where score >=80 group by hobbid,name;

#group by 前面可以用where score >=80

select count(name),hobbid,score from info where score >= 80 group by hobbid;

#group by 后面要用having

select count(name),hobbid,score from info group by hobbid,score having score >=80;

#以兴趣这一列做为分组,计算成绩的平均分,统计的结果筛选出分组的平均成绩大于等于60分的

select avg(score),hobbid from info group by hobbid having avg(score) >=60;

#统计姓名,以兴趣和分数做为分组,统计出成绩大于80的分组,然后按照降序对统计姓名这一列进行降序

SELECT count(NAME),score,hobbid FROM info GROUP BY score,hobbid HAVING score > 80 order by count(name) desc;

limit

#limit 1,3

#1是未知偏移量(可选参数),如果不设定未知偏移量,默认就是从第一行开始,默认的值

select * from info limit 3;

#使用limit和降序排列,只显示最后三行

select * from info ORDER BY id DESC limit 3;

对表和列的别名

因为在实际工作中,表的名字和列的名字可能会很长,书写起来不太方便,多次声明表和列时,完整的展示太复杂

设置别名可以使其书写简化,简洁明了可读性增加

#对列取别名

#第一种

select name as 姓名,score 成绩 from info;

#第二种

select name 姓名,score 成绩 from info;

#对表取别名

select i.name,i.score from info i;

#第一种

select i.name 姓名,i.score 成绩 from info as i;

#第二种

select i.name 姓名,i.score 成绩 from info i;

#正常的复制表

create table stu_02(select * from student);

#这种复制表的方式会丢失主键,也就是只能复制数据,不能复制结构

create table test as select * from info;

#成绩大于60分复制表

create table test1 as select * from info where score >=60 ;

通配符

模糊查询

#like模糊查询

#% 表示零个,一个,或者多个字符

select * from info where address like 's%';

#包含s的

select * from info where address like '%s%';

#_ 表示单个字符,__两个下划线表示两个字符

select * from info where address like 's_';

#以s为开头,任意长度,起码有一个字符(整个看做单个字符)

select * from info where address like 's%_';

子查询

内查询,嵌套查询,select里面又套了个select,嵌套的select才是子查询

查询顺序是,先执行子查询的语句,然后外部的查询语句再根据子条件的结果进行过滤查找.

子查询可以是多个表,也可是同一张表.

关联语法

in和not in

#select (select)

#查询的是info表内,子查询中分数大于80的id,他们对应的id,名字,成绩

select id,name,score from info where id in (select id from info where score >=80);

##查询的是info表内,子查询中不是分数大于80的id,他们对应的id,名字,成绩

select id,name,score from info where id not in (select id from info where score >=80);

#以另一张表的查询做为子查询

select id ,name,score from info where id not in (select id from test where score>=80);

#以其他表的id=2的为条件,修改info表id=2的对应socre

update info set score=80 where id in(select id from test where id =2);

exists

判断子查询的结果是否为空,不为空返回一个true,空就是返回false

#只有子查询中的条件成立了,才会执行外查询的统计

select count(*) from info where exists (select id from test where score > 80);

#查询分数,如果分数小于50的,则统计info的字段数

select count(*) from info where exists (select id from test where score < 50);

子查询中多表查询和别名

不要超过三张

#info表和test表,这两张表id部分相同,然后根据拒绝id相同的部分,查询info表的id的值

select a.id from info a where a.id not in (select b.id from test b where a.id=b.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) from info a where a.id in (select b.id from test b where a.id=b.id);

mysql的视图

视图是一个虚拟表

视图可以简化复杂的查询,可以隐藏复杂的细节,访问数据更安全

是图标是一个多表数据的集合体.

视图和表之间的区别

1.存储方式,表示实际的数据行,视图不存储数据,仅仅是一个查询结果的虚拟表

2.数据更新,更新表可以直接更新视图表的数据

3.占用空间,表实际占用空间,视图表不占用空间,只是一个动态结果的展示.

4.视图表的数据可能是一张表的部分查询数据,也可能是多个表的一部分查询数据

查询当前数据库中的视图表

在5.5之前,视图表是只读的

视图表就是查询语句的别名,有了视图表可以简化查询的语句.

表的权限是不一样的,库的权限是有控制的,所以查询试图表的权限相对低,既可以保证原表的数据安全,也简化了查询的过程.

create view test2 as select * from info where score > 80;

#执行下面的语句时,就等于select * from info where score >=80;

select * from test2;

show full tables in xy102 where table_type like 'VIEW';

#同样没有继承表结构

desc test2;

#创建一张视图表,包含id name address 从info和test当中name值相同的部分创建

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);

#删除视图表;

drop view test2;

连接查询

两张表或者多个表的记录结合起来,基于这些表共同的字段,进行数据的拼接.

首先,要确定一个主表做为结果集,然后把其他表的行,有选择性的选定到主表的结果上

内连接

两张表之间或者多张表之间符合条件的数据记录的集合,

语法: 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.b_id,b.b_name from test1 a inner join test2 b on a.a_name=b.b_name;

内连接取两个表或者多个表之间的交集

左连接

又叫左外连接,left jion,left outer join

#左连接以左表为基础,接收左表的所有行,以左表的记录和右表的记录进行匹配

#匹配左表的所有,以及右表中符合条件的行,不符合的显示null

#以左表为基准,右表会显示出和左表重合的值,不重合的显示null

#以比较条件为标准,展示结果.两个表相同的部分展示出来,做拼接,不同的记过显示null.

select * from test1 a left join test2 b on a.a_name=b.b_name;

右连接

又叫右外连接,right jion,right outer join

#右连接以右表为基础,接收右表的所有行,以右表记录和左表的记录进行匹配

#匹配右表的所有,以及左表中符合条件的行,不符合的显示null

练习

需求：两张表

第一张表：记录学生的学号，所属专业，课程，姓名 成绩 性别

第二张表：记录学生的学号，手机 家庭地址，兴趣爱好，性别

编写一个查询来查找具有最高分数的学生。

找出至少有两门课程成绩及格的学生。

查找每个系的学生人数。

计算每个系的学生平均分数。

获取至少同时选修了一门与 'xxx' 相同课程的学生。

找出具有重复名字的学生。

查找在所有课程中都取得了及格分数的学生。

找出每门课程的平均分数，并按照平均分数降序排列。

查找学生选课数量超过平均选课数量的学生信息。

左连接查询 查学号

右连接查询 查学号

内连接查询 查学号

drop TABLE studentinfo;

drop TABLE studentscores;

CREATE TABLE studentscores (

id INT(5)NOT NULL,

major CHAR(5) NOT NULL,

course CHAR(5) NOT NULL,

name CHAR(5) NOT NULL,

score decimal(5,2) NOT NULL,

sex CHAR(5) NOT NULL

);

CREATE TABLE studentinfo(

id INT(5)NOT NULL,

phone INT(11) NOT NULL,

address VARCHAR(15) NOT NULL,

hobbid CHAR(5) NOT NULL,

sex CHAR(5) NOT NULL

);

#1查找具有最高分数的学生：

select * FROM studentscores WHERE score = ( SELECT MAX(score) FROM studentscores );

#2找出至少有两门课程成绩及格的学生：

SELECT id,name FROM studentscores WHERE score >60 GROUP BY id,name HAVING count(score) >=2;

#3查找每个系的学生人数

SELECT major, COUNT(major)人数 FROM studentscores GROUP BY major;

#4计算每个系的学生平均分数

SELECT major,AVG(score)平均分 FROM studentscores GROUP BY major;

#5获取至少同时选修了一门与 'xxx' 相同课程的学生：

SELECT DISTINCT s1.* FROM studentscores s1 JOIN studentscores s2 ON s1.id != s2.id AND s1.course = s2.course WHERE s2.name = '土匪丁';

#6找出具有重复名字的学生：

SELECT * FROM studentscores WHERE name IN (SELECT name FROM studentscores GROUP BY name HAVING COUNT(*) > 1);

#7查找在所有课程中都取得了及格分数的学生：

select distinct id,name from studentscores where id not in (select id from studentscores where score < 60);

#8找出每门课程的平均分数，并按照平均分数降序排列：

SELECT course, AVG(score) FROM studentscores GROUP BY course ORDER BY score DESC;

#9查找学生选课数量超过平均选课数量的学生信息：

#10左连接查询（查学号）：

SELECT s1.id, s1.name, s2.phone, s2.address FROM studentscores s1 LEFT JOIN studentinfo s2 ON s1.id = s2.id;

#11右连接查询（查学号）：

SELECT s1.id, s1.name, s2.phone, s2.address FROM studentinfo s2 RIGHT JOIN studentscores s1 ON s1.id = s2.id;

#12内连接查询（查学号）：

SELECT s1.id, s1.name, s2.phone, s2.address FROM studentscores s1 INNER JOIN studentinfo s2 ON s1.id = s2.id;

INSERT INTO studentscores VALUES (10001,'计算机','编程','路人甲',90,'女');

INSERT INTO studentscores VALUES (10002,'学习机','数学','炮灰乙',80,'男');

INSERT INTO studentscores VALUES (10003,'挖掘机','土木','流氓丙',100,'男');

INSERT INTO studentscores VALUES (10004,'挖掘机','土木','土匪丁',120,'男');

INSERT INTO studentscores VALUES (10005,'挖掘机','土木','土匪娄',10,'男');

INSERT INTO studentscores VALUES (10001,'计算机','汇编','路人甲',80,'女');

INSERT INTO studentinfo VALUES (10001,111111111,'路人大街一号','逛街','女');

INSERT INTO studentinfo VALUES (10002,222222222,'炮灰大街一号','群演','男');

INSERT INTO studentinfo VALUES (10003,333333333,'流氓大街一号','夜跑','男');

INSERT INTO studentinfo VALUES (10004,444444444,'土匪大街一号','强拆','男');

INSERT INTO studentinfo VALUES (10005,555555555,'土匪大街一号','强拆','男');