SQL语句大全（Mysql）

sql的意思为：结构化查询语言.

 

创建student表

create table student (

id int unsigned primary key auto_increment,

class int,

stu_id varchar(11),

stu_name varchar(20),

sex varchar(10),

age int,

height double(5,2),

department varchar(10),

technology varchar(10)

)

unsigned 的意思是“无符号”，可理解为该字段的数值必须是正数、primary key 设置 id 字段为表的主键、auto_increment设置该字段自动增长；

class为第 n 届、stu_id为学生学号、stu_name为学生姓名、sex为性别、age为年龄、height为身高、department为系别、technology为专业。

height double(5,2)的 5 代表height字段有5位数。包括小数点2位数，5减2等于3,3为身高位数。

也可以为字段设置‘不为空’或默认值，其语法分别为：

...

stu_id varchar(11) not null,

stu_name varchar(20) default '无名' ,

sex varchar(10) default null,

...

删除表

格式一：drop table 表名

drop table student

（如果表存在则删除，表不存在则报错）

格式二：drop table if existe 表名

drop table if existe student

（如果表存在则删除，表不存在不会报错）

有时候删除表语句会出现在创建表之前

drop table if existe student ;

create table student(

...

)

 

添加数据

⒈insert into 表名 values(...)

insert into student values(0,2020,'20200101001','张三','男',18,160,'计算机系','软件技术');
insert into student values(default,2020,'20200101002','李四','男',17,173,'计算机系','软件技术');
insert into student values(null,2020,'20200101003','吴五','男',19,175,'计算机系','软件技术');

第一个字段id是自增长，在添加数据时，可以用0、default和null代替，但不可以不写数值！数值类型要与字段一一对应，每个语句直接要用英文分号隔开，最后一句可以没有分号。

⒉插入单个或多个字段值的数据，insert into 表名(某字段) values(某数值)

insert into student(stu_id) values('20200101004');

insert into student(stu_id,stu_name) values('20200101005','陈一');

⒊一条语句插入多条数据

insert into student values(0,2020,'20200101006','卢欣','女',18,160,'计算机系','软件技术'),

(default,2020,'20200101007','刘飞','男',20,165,'计算机系','软件技术'),

(null,2020,'20200101008','梁佑','男',19,179,'计算机系','软件技术');

修改数据

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

修改学号为20200101007同学的身高为‘170’，年龄为18？

update student set height=170,age=18 where stu_id='20200101007'

删除数据

delete from 表名 where 条件

delete from student where stu_id='...'

查询数据

查询学生表所有数据：

select * from student

查询某些字段：

select class,stu_id,stu_name from student

给某些字段起别名：

select class as a,stu_id b,stu_name as 学生名字 from student

（在字段后加 as ‘别名’,也可以忽略as直接加别名）

去除字段中的重复的值：

select distinct sex from student

（只查出男和女两个数据）

⒈查询19岁以下的学生？

select * from student where age<19

（<,<=,>,>=,!= 不等于,<> 不等于）

⒉查询19岁以下的女同学的信息？

select * from student where age<19 and sex='女'

⒊查询男同学或计算机系的学生？

select * from student where sex='男' or department='计算机系'

⒋查询不是19岁的学生信息？

select * from student where not age='19'

模糊查询

查询姓张的学生信息？

select * from student where stu_name like '张%'

查询姓张且名为一/二个字的学生信息？

select * from student where stu_name like '张_'

select * from student where stu_name like '张__'

查询姓名以‘三’结尾的学生信息？

select * from student where stu_name like '%三'

查询姓名含‘四’的学生信息？

select * from student where stu_name like '%四%'

⒈查询年龄在18,19,21的女生信息？

select * from student where age in(18,19,21) and sex='女'

⒉查询年龄在19，20外的学生信息？

select * from student where not age between 19 and 20 

⒊查询有身高数据的学生信息？

select * from student where not height is null

排序查询

⒈查询所有学生信息，并按年龄和性别排序，年龄相同时，按年龄按降序排序？

select * from student order by age desc,sex

⒉查询所有学生信息，并按年龄和姓名排序，年龄相同时，姓名按中文首字母排序？

select * from student order by age,convert(stu_name  using gbk)

聚合函数

⒈统计学生人数？

select count(*) 学生人数 from student

⒉查询年龄最大的学生？

select max(age) 最大年龄 from student

⒊查询年龄最小的学生？

select min(age) 最小年龄 from student

⒋查询学生的平均年龄？

select avg(age) 平均年龄 from student

⒌求学生的合计的身高？

select sum(height) 合计身高 from student

查询分组

⒈查询2020届计算机系男女各多少人？

select class,department,sex,count(*)总数 from student where class=2020 and department='计算机系' group by sex

⒉查询2020届各个系别学生的平均年龄、最大年龄、最小年龄？

select class,department,max(age)最大年龄,min(age)最小年龄,avg(age)平均年龄,count(*)总数

from student

where class=2020

group by department

3.查询2020届各个系别学生的男女的平均身高、最高、最低信息？

select class,department,sex,max(height)最大身高,min(height)最小身高,avg(height)平均身高,count(*)总数 
from student 
where class=2020 
group by department,sex

⒋查询2020届男生总人数？

select count(*)2020届男生总人数 from student class=2020  group by sex having sex='男'

分页查询

⒈查询学生表的前3条的数据

select * from student limit 0,3

创建成绩表stu_score与课程表stu_course，并添加数据

create table stu_course(
course_num int unsigned primary key auto_increment,
name varchar(10)
)

insert into stu_course values(1,'数据库'),
(2,'计算机基础'),
(3,'Java'),
(4,'数据结构'),
(5,'JSP'),
(6,'SQL服务器')

create table stu_score(
id int unsigned primary key auto_increment,
course_num int,
stu_id varchar(11),
score double(5,2)
)

insert into stu_score values(0,1,'20200101001',80),
(0,2,'20200101001',90),
(0,3,'20200101001',100),
(0,4,'20200101001',85),
(0,5,'20200101001',77),
(0,6,'20200101001',88)

内链接查询

⒈查询学号为'20200101001'学生的学习成绩？

select * from student 
inner join stu_score on 
student.stu_id=stu_score.stu_id
inner join stu_course on
stu_score.course_num=stu_course.course_num
where student.stu_id='20200101001'

⒉查询2020届各系各专业的每个科目的平均成绩？

select class,department,technology,stu_course.name,avg(score)平均成绩 from student
inner join stu_score on
student.stu_id=stu_score.stu_id
inner join stu_course on 
stu_score.course_num=stu_course.course_num
where class=2020
group by stu_course.name
order by convert(department using gbk)

左连接查询

⒈查询所有学生的学生成绩，包括没有没有成绩的学生？

select class,student.stu_id,stu_name,sex,department,technology,stu_course.name,score from student
left join stu_score on
student.stu_id=stu_score.stu_id

⒉查询所有学生的学生成绩，包括没有成绩的学生，并显示课程名？

select class,student.stu_id,stu_name,sex,department,technology,stu_course.name,score from student
left join stu_score on
student.stu_id=stu_score.stu_id
left join stu_course on 
stu_score.course_num=stu_course.course_num

右连接查询

⒈查询所有课程的成绩，包括没有没有成绩的课程，包括学生信息？

select class,student.stu_id,stu_name,sex,department,technology,stu_course.name,score
from stu_score
right join stu_course on 
stu_course.course_num=stu_score.course_num
left join student on 
stu_score.stu_id=student.stu_id

⒉查询所有学生的学生成绩，包括没有没有成绩的学生，并显示课程名？

select class,student.stu_id,stu_name,sex,department,technology,stu_course.name,score
from student
right join stu_score on 
student.stu_id=stu_score.stu_id
left join stu_course on 
stu_score.course_num=stu_course.course_num

创建地域表area

create table areas(
aid int primary key,
title varchar(20),
pid int
)

aid为地域号，title为地域名，pid为上一级

insert into areas values(440000,'广东省',null),
(450000,'广西省',null),
(4401,'深圳市',440000),
(4402,'东莞市',440000),
(4403,'广州市',440000),
(4501,'桂林市',450000),
(4502,'南宁市',450000),
(001,'南山区',4401),
(002,'龙岗区',4401),
(003,'北区',4401)

自关联查询

⒈查询广东省所有的城市？

select * from areas as a,areas as b
where a.aid=b.pid and a.aid=440000

⒉查询广东深圳的镇区有哪些？

select * from areas as a,areas as b,areas as c
where a.aid=b.pid and b.pid=c.aid and c.aid='4401'

⒊查询广东所有的镇区？

select * from areas as a,areas as b,areas as c
where a.aid=b.pid and b.aid=c.pid and a.aid='440000'

子查询

⒈查询大于平均年龄的学生？

select * from student where age > (select avg(age) from student)

⒉查询王芬的（沟通技巧）成绩，只显示成绩？（标量 子查询，返回的结果是一行一列，也就是一个值）

select score from stu_score where stu_id=(select student.stu_id from student where stu_name='王芬')

select score from stu_score where stu_id=(select student.stu_id from student where stu_name='王芬') 
and course_num = (select course_num from stu_course where name='沟通技巧')

⒊查询20岁的学生的成绩，只显示成绩？（列 子查询，返回的结果是一列多行）

select score from stu_score where stu_id in(select student.stu_id from student where age=20)

⒋查询女生身高最高的学生信息？（行 子查询，返回的结果是一行多列）

select * from student 
where (stu_name,height)=(select stu_name,height 
from student 
where sex='女' 
order by height desc limit 1)

⒌查询数据库和计算机基础的学生成绩？（表 子查询，返回的结果是多行多列；以inner join 后的查询语句的返回结果作为数据源，性能比单纯的内连接查询要高）

select * from stu_score
inner join (select * from stu_course where name in('数据库','计算机基础')) as b on 
stu_score.course_num=b.course_num

数据分表

create table...select语句

⒈创建学生补考科目表，同时添加学生需要补考的科目，并将查询结果添加到新表中。

create table re_course_test(
id int unsigned primary key auto_increment,
stu_id varchar(11),
stu_name varchar(10),
course_NAME varchar(10)
)
select stu_id,course_num as course_NAME from stu_score where score<60

（要将course_num课程号的数据添加到re_course_test表的course_NAME中，需要在查询语句中的course_num字段as 新表的course_NAME才可以，否则新表多一个course_num的字段并显示成绩表的course_num字段的数据）

update...select 语句

⒈利用student学生表，将学生补考科目表的stu_name字段的信息按照学号对应填好？

update re_course_test 
inner join student on 
re_course_test.stu_id=student.stu_id
set re_course_test.stu_name=student.stu_name
where re_course_test.stu_id=student.stu_id

⒉利用stu_score学生成绩表，将学生补考科目表的course_NAME字段的信息修改为对应的课程名？

update re_course_test
inner join stu_course on 
re_course_test.course_NAME=stu_course.course_num
set re_course_test.course_NAME=stu_course.`name`

创建补考表

create table re_test(
id int unsigned primary key auto_increment,
stu_id varchar(11)
)

insert into....select 语句

⒈将不合格的学生ID添加到补考表中？

insert into re_test(stu_id) select stu_id from stu_score where score<60 group by stu_id