SQL语法

一、DDL：操作数据库/表

（对库database：增 删 查（没有改））

1. 查库：

   show databases;
   

2. 创建库：

   create database 库名;   
   

   2. 1 如果不存在则创建:

      create database if not exists 库名；
      

3. 删除库：

   drop database 库名； 
   

   3. 1 如果存在则删除：

      drop database if exists 库名；
      

4. 使用数据库（进入数据库）

use 库名；

​ 查看当前使用的数据库:

  select database() ;

–DDL操作表（对表（table）：增create 删retrieve 改update 查desc）crud

1. 查表：

show tables;

1. 1查表结构：

   desc 表名；
   

2. 创建表：

create table 表名(
	字段名1  数据类型1,
	字段名2  数据类型2,
	...    
	字段名n  数据类型n
);

练习：

create table tb_user(
	id int,  #属性在前面，类型在后面（和Java语法相反）
	username varchar(20),
	password varchar(30)
);

3. sql数据类型：

数值：

tinyint : 小整数型，占一个字节 
int ： 大整数类型，占四个字节 
		eg ： age int 
double ： 浮点类型 
		使用格式： 字段名 double(总长度,小数点后保留的位数) 
		eg ： score double(5,2) 意思是总长度为5，少数点后保留2位数

日期：

date ： 日期值。只包含年月日 
		eg ：birthday date
datetime ： 混合日期和时间值。包含年月日时分秒

字符串：

char ： 定长字符串。 
	优点： 存储性能高 
	缺点： 浪费空间 
	eg ： name char(10) 如果存储的数据字符个数不足10个，也会占10个的空间
    比如存储张三2个字符的，会占到10个空间，因为多余的8个空间会用空格代替，会浪费空间

varchar ： 变长字符串。 
	优点： 节约空间 
	缺点： 存储性能底 
	eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

练习：

需求： 设计一张学生表，请注重数据类型、长度的合理性 
	1. 编号 
	2. 姓名，姓名最长不超过10个汉字 
	3. 性别，因为取值只有两种可能，因此最多一个汉字 
	4. 生日，取值为年月日 
	5. 入学成绩，小数点后保留两位 
	6. 邮件地址，最大长度不超过 64 
	7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符 
	8. 学生状态（用数字表示，正常、休学、毕业...）

解：

create tbale student(
	id int,
	name varchar(10),
	gender char(1),
	birthday date,
	score double(5,2),
	email varchar(15),
	tel varchar(15),
	status tinyint
);

4. 删除表 （drop：删除）

删除表：

drop table 表名;

删除表时判断表是否存在:

drop table if exists 表名;

5.修改表（alter）

修改表名：

alter table 表名 rename to 新的表名;

	例子：将表名student改为stu
	alter table student rename to stu;

添加一列：

alter atble 表名 add 列名 数据类型;

	例子：给stu表添加列address,该字段类型是varchar(50)
	alter table stu add address varchar(50);

修改列的数据类型：

alter table 表名 modify 列名 新数据类型;
	
	例子：给stu表中的address字段的类型改为char(50)
	alter table stu modify address char(50);

修改列名和数据类型：

alter table 表名 change 列名 新列名 新数据类型;

	例子：将stu表中的address字段名改为addr，类型改为varchar(50);
	alter table stu change address addr varchar(50);

删除列：（有点特殊）

alter table 表名 drop 列名;

	例子：将stu表中的addr字段删除
	alter table stu drop addr;

二、DML 操作数据（对数据：增删改）

1. 添加数据（insert into）

1.1 给指定的列 添加数据（insert：插入）

insert into 表名(列名1，列名2， ...) values(值1，值2，...);

-- 例子：
-- 给指定的列添加数据
insert into stu(id , name) values(1,'张三');

1.2 给全部的列 添加数据

insert into 表名 values(值1，值2，...);

-- 例子
-- 给所有的列添加数据
insert into stu(id,name,sex,birthday,score,eamil,tel,status) values(1,'张三','男','2000-01-01',99.99,'ak@qq.com','12312312312',1)
dd
-- 简写 : 给所有列添加数据，列名的列表可以省略的
insert into stu (1,'张三','男','2000-01-01',99.99,'ak@qq.com','12312312312',1)
                 

1.3 批量添加数据

insert into 表名(列名1，列名2， ...) values(值1，值2，...)，(值1，值2，...)，(值1，值2，...)...;
insert into 表名 values(值1，值2),(值1，值2，...)，(值1，值2，...)..;

-- 例子
-- 批量添加数据
insert into stu values
	(1,'张三','男','2000-01-01',99.99,'ak@qq.com','12312312312',1),
	(1,'张三','男','2000-01-01',99.99,'ak@qq.com','12312312312',1),
	(1,'张三','男','2000-01-01',99.99,'ak@qq.com','12312312312',1);

2.修改数据（update）

update 表名 set 列名1=值1, 列名2=值2, ...  where 条件;

-- 注意：如果不加条件，则将所有数据都修改！

练习：
-- 将张三的性别改为女
update stu set sex='女' where name='张三';

-- 将李四的生日改为1999-12-12、分数改为99.99
update stu set birthday='1999-12-12', score=99.99 where name='张三';

-- 把班级的分数都全部改为60
update set set score=60;

3.删除数据（delete）

delete from 表名 where 条件;

练习：
-- 删除张三的记录
delete from stu where name='张三';

-- 删除所有数据
delete from stu;

三、DQL：操作数据

1.查询语法

• 基础查询
• 条件查询（where）
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit）

select
	字段列表
from 
	表名列表
where
	条件列表
group by
	分组列表
having
	分组后条件
order by
	排序字段
limit 
	分页限定

1.1 基础查询（select from）

• 查询多个字段：

select 字段列表 from 表名;
select * from 表名; -- 查询所有数据

-- 练习
-- 查询name ，age 两列
select name,age from stu;

-- 查询所有数据
select * from stu;

• 去除重复记录

  select distinct 字段列表 from 表名;
  
  -- 练习
  -- 查询地址，但是地址有很多重复的，要去重复
  select distinct address from stu;
  

• 取别名

as

1.2 条件查询（** where）

select 字段列表 from 表名 where 条件列表;

条件列表可以用下面的运算符：

练习：
-- 查询年龄大于等于20岁的学员信息
select * from stu where age>=20;

-- 查询年龄大于等于20岁并且年龄小于等于30岁的学员信息
select * from stu where age between 20 and 30;

-- 查询年龄不等于18岁的学员信息
select * from stu !=18;

-- 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁 的学员信息
select * from stu where age age=18 or age=20 or age=22;
或者4 
select * from stu where age in(18,20,22);

-- 查询英语成绩为null的学员信息
select * from stu where english is null;   -- 而不是english = null

• 模糊查询like

  _ : 代表单个字符 % : 代表任意字符(0~n)

-- 查询姓马的学生信息
select * from stu where name like '马%';

-- 查询第二个字是'花'的学生
select * from stu where name like '_花%';

-- 查询名字中包含'德'的学生
select * from stu where name like '%德%';

1.3 排序查询（order by）

select 字段列表 from 表名 order by 排序字段名1 排序方式, 排序字段名2 排序方式2 ...;

Asc ：升序（默认）（不写排序方式，也是默认了）

Desc：降序

​ 注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

-- 练习
-- 查询学生信息，按照年龄升序排列
select * from stu order by age;

-- 查询学生信息，按照数学成绩降序排列
select * from stu order by math desc;

-- 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc, englis asc;

1.4 分组查询 group by

先得知道 聚合函数

语法：

select  聚合函数(列名)  from  表名;

注意：null值不参与 所有聚合函数运算

练习：

-- 统计班级一共有多少个学生
select count(*) from stu;

-- 查询数学成绩的最高分
select max(math) from stu;

-- 查询数学成绩的最低分
select min(math) from stu;

-- 查询数学成绩的平均分
select avg(math) from stu;

-- 查询英语成绩的最低分
select min(english) from stu;  -- null值不参与查询

分组查询：

select  字段列表  from 表名 [where 分组前条件限定]  group by  分组字段名 [having 分组后条件过滤]; 

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

练习：

-- 查询男同学和女同学各自的数学平均分
select   sex, avg(math)   from stu group by sex;

-- 查询男同学和女同学各自的数学平均分，以及各自人数
select   sex, avg(math), count(*) from stu group by sex;


-- 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select   sex, avg(math), count(*) from stu where math>70 group by sex;

-- 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
select   sex, avg(math), count(*) from stu where math>70 group by sex 
having count(*)>2;

1.5 分页查询 limit

select 字段列表 from 表名 limit 起始索引，查询条目数

索引和数组一样，从0开始，第0个数，第1个数…

索引    id
0		1
1		2
2		3
3		4
4		5
5		6
6		7
7		8
8		9

-- 练习
-- 从0开始查询，查询3条数据
select * from stu limt 0,3;  -- 0号索引 往后查3位数  结果是1 2 3

-- 每页显示3条数据，查询第1页数据
select * from stu limt 0,3;   -- 0号索引 往后查3位数  结果是1 2 3

-- 每页显示3条数据，查询第2页数据  
select * from stu limt 3,3;  -- 3号索引 往后查3位数  结果是4 5 6

-- 每页显示3条数据，查询第3页数据
select * from stu limt 6,3; -- 6号索引 往后查3位数  结果是7 8 9

由此推出：

*起始索引=（当前页面 - 1）每页要显示的条数

SQL面试50题

53道题 目录

其中重点为：

1 / 2 / 5 / 6 / 7 / 10 / 11 / 12 / 13

15 / 17 / 18 / 19 / 22 / 23 / 25

31 / 35 / 36 / 40 / 41 / 42 / 45 / 46

超级重点 18和23、 22和25 、 41、46

为了方便学习，我将50道面试题进行了分类

1. 简单查询

查询姓“猴”的学生名单

1. 查询 姓 “孟” 老师的个数

select count(t_name) from teacher where t_name like '孟%';

2. 汇总分析【聚合函数、group by和where的相爱相杀】

select 字段列表 from 表名 [where 分组前条件限定] group 分组字段名 [having 分组后条件过滤]

1. 聚合函数sum / avg / count / max / min 经常与好基友group by搭配使用

2. 在使用group by时，select后面只能放

• 常数（如数字/字符/时间）
• 聚合函数
• 聚合键（也就是group by后面的列名）；

​ 因此，在使用group by时，千万不要在select后面放聚合键以外的列名！

3. where函数后面不能直接使用聚合函数！

4. where 和 having 区别：

   where 是分组之前进行限定，不满足where条件的，则不参与分组

   而having是分组之后对结果进行过滤

2. 查询 课程编号为 “0002” 的总成绩

-- 分解
1.查询成绩：              select * from score;
2.查询课程编号为0002的成绩: select * from score where c_id='002';
3.查询课程编号为0002的总成绩：select sum(s_score) from score where c_id='0002';

3. 查询 男生、女生人数

-- 分解
1.查询总人数：select count(*) from student;
2.查询男生、女生的总人数：select sex,count(*) from student group by sex;

4. 查询各科的最低分和最低分

-- 分解
1.查询分数：select * from score;
2.查询各科的分数:select c_id from score group by c_id;
3.查询各科的分数的最最低分，最低分：select c_id,  max(s_score), min(s_score) from score group by c_id;

4. 查询 课程编号为02（数学） 的总成绩【聚合函数】

select Cno,sum(score) from sc group by Cno having Cno='02';

5. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列【聚合函数】

select Cno,avg(score) as avg_score from sc 
group by Cno
order by avg_score desc,cno;

6. 求每门课程的学生人数 【聚合函数】

select Cno,count(Sno) from student group by Sno;

7. 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

select 
c_id,
max(s_score) as 最高分,
min(s_score) as 最低分 
from score 
group by c_id

扩展 (超级重点)

-- 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 

-- 第一步：聚合计算(用到了case when) 
-- 及格率=及格的人数/总人数
-- 解读：第7句，case when:当score表中的分数有一个及格，记为1，把分数sum就得到及格人数
select 
c_id,
count(*) as 选修人数,
max(s_score) as 最高分,
min(s_score) as 最低分,
avg(s_score) as 平均分,
sum(case when s_score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(*) as 中等率,  
sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(*) as 优良率,
sum(case when s_score>=90 then 1 else 0 end)/count(*) as 优秀率
from score
group by c_id;

-- 第二步：排序 （只在最后一行追加了一句）
select 
c_id,
count(*) as 选修人数,
max(s_score) as 最高分,
min(s_score) as 最低分,
avg(s_score) as 平均分,
sum(case when s_score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(*) as 中等率,  
sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(*) as 优良率,
sum(case when s_score>=90 then 1 else 0 end)/count(*) as 优秀率
from score
group by c_id
order by count(*) desc,c_id asc;

-- 第三步： 显示课程名字，即（成绩表）要关联 课表表
select 
x.* , cname  -- 要显示x中的所有结果 和 y表中的cname课程名字
from(第二步的所有结果) x  -- 把第二部的成绩表取别名为x
left join course y -- 把course表取别名为y
on x.c_id = y.c_id; -- 通过x表的c_id 与 y表的c_id 来建立连接

———>
最后答案:
select 
x.* , c_name  -- 要显示x中的所有结果 和 y表中的cname课程名字
from(select 
c_id,
count(*) as 选修人数,
max(s_score) as 最高分,
min(s_score) as 最低分,
avg(s_score) as 平均分,
sum(case when s_score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(*) as 中等率,  
sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(*) as 优良率,
sum(case when s_score>=90 then 1 else 0 end)/count(*) as 优秀率
from score
group by c_id
order by count(*) desc,c_id asc) x  -- 把第二部的成绩表取别名为x
left join course y -- 把course表取别名为y
on x.c_id = y.c_id; -- 通过x表的c_id 与 y表的c_id 来建立连接

8 . 查询每门课程被选修的学生数

select c_id,count(s_id)
from score
group by c_id;

9. 查询同名同姓的学生名单并统计同名人数

-- 查询同名同姓学生名单并统计同名人数
select 
s_name,count(*) as 同名同姓
from student
group by s_name
having count(*)>=2;

10. 查询不及格的课程 并 按课程号从大到小排列

select c_id
from student
where s_score<60
order by c_id desc;

11. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select c_id , avg(s_score) as 平均分
from score
group by c_id
order by 平均分 asc , c_id desc;

12. 检索课程编号为“0002”且分数小于60的学生学号，结果按分数降序排列

select s_id
from score 
where c_id='0002' and s_score<60
oreder by s_score desc;

13 .统计每门课程的学生选修人数(超过2人的课程才统计)

要求: 输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select c_id ,count(s_id) as 选修人数
from score 
group by c_id
having count(s_id)>2
order by 选修人数 desc , c_id asc;

14 . 查询两门以上不及格课程的同学的学号 及其 平均成绩

select  s_id , avg(s_score)
from score
where s_socre<60
group by s_id
having count(c_id)>2;

汇总分析:

15. 查询学生的总成绩 并 进行排名

select s_id , sum(s_score) as 总成绩
from score
group by s_id
oreder by sum(s_score) asc;

16. 查询平均成绩大于60分的学生的学号和平均成绩

select s_id, avg(s_score)
from score
group by s_id
having avg(s_score)>60;

复杂查询:

17. 查询所有课程成绩小于60分学生的学号、姓名

-- 第1步，查询小于60分的学生出来
select s_id
from score
where s_score<6;  -- 得出小于60分的学生的表a

-- 第2步，查询 学生表中 小于60分 的名单
select s_id , s_name
from student
where s_id in(小于60分的学生的表a);
-- 即
select s_id , s_name
from student
where s_id in(
	select s_id
	from score
	where s_score<6    
);