【SQL语句大全】SQL Sever、MySQL通用

最新推荐文章于 2024-05-18 21:04:07 发布

BUG_GUB

最新推荐文章于 2024-05-18 21:04:07 发布

阅读量3.4k

点赞数 9

分类专栏：笔记本文章标签： sql mysql SQL Sever sqlserver 数据库

本文链接：https://blog.csdn.net/qq_45634592/article/details/123675094

版权

笔记本专栏收录该内容

10 篇文章 4 订阅

订阅专栏

文章目录

表相关语句
表操作相关语句
视图相关语句
常用函数

表相关语句

数据类型

在这里插入图片描述

完整性约束

1. 创建表

/* 在列级完整性约束处定义主码约束 */
create table student (
	sno varchar(7) PRIMARY KEY COMMENT '学号',
	sname varchar(10) NOT NULL COMMENT '姓名',
	sex varchar(2) CHECK (sex IN ('男','女')) COMMENT '性别',
	age int COMMENT '年龄',
	sdept varchar(20)  COMMENT '所在系'
)

/* 在表级完整性约束处定义主码约束、外码约束 */
create table course (
	cno varchar(6) NOT NULL COMMENT '课程号',
	cname varchar(20) NOT NULL COMMENT '课程名',
	credit int COMMENT '学号' COMMENT '学分',
	semester int COMMENT '学期',
	PRIMARY KEY(cno)
)

create table score (
	sno varchar(7) NOT NULL COMMENT '学号',
	cno varchar(6) NOT NULL COMMENT '课程号',
	grade int  COMMENT '成绩',
	PRIMARY KEY(sno, cno),
	FOREIGN KEY(sno) REFERENCES student(sno),
	FOREIGN KEY(cno) REFERENCES course(cno),
	CHECK (grade >= 0)
)

COMMENT [ˈkɒment]：注释的含义。给字段设置备注。可省略不写。

REFERENCES [ˈrefrəns]：含义参照，用于指出外码参照的字段。

2. 删除表

drop table student

3. 修改表

1. 修改列定义

/* 将student表中的sname字段类型修改为char(20)*/
alter table student
	alter COLUMN sname char(20)

alter [ˈɔːltə®]：修改的含义

2. 添加新列

/* 为student表中添加一个spec字段，类型为char(10)，允许空 */
alter table student
	add spec char(10) NULL

3. 删除列

/* 删除前面student表中新添加的字段spec*/
alter table student
	drop COLUMN spec

4. 添加约束

/* 添加主键约束 */
alter table student
	add CONSTRAINT PK_STUDENT PRIMARY KEY (sno)
	
/* 添加UNIQUE约束(不能有重复值约束) */
alter table student
	add UNIQUE (sno)
	
/* 添加外键约束 */
alter table score
	add FOREIGN KEY (cno) REFERENCES course (cno)
	
/* 添加DEFAULT约束(默认值约束) */
alter table student
	add DEFAULT '男' FOR sex
	
/* 添加CHECK约束(限制列取值约束) */
alter table student
	add CHECK (sex IN('男','女'))

CONSTRAINT [kənˈstreɪnt]：约束的含义，用于指定约束名。可以省略。

5. 删除约束

/* 删除前面student表中定义的PK_STUDENT约束 */
alter table student
	drop CONSTRAINT PK_STUDENT

/* 删除student表中的主键约束 */
alter table student
	drop PRIMARY KEY

表操作相关语句

1. 插入语句

/* 插入一条完整的数据(顺序必须与表中一致) */
insert into student values ('s000001','张三','男',31,'计算机系')

/* 插入部分值 */
insert into student (sno,sname,age) values ('s000001','张三',31)

2. 删除语句

delete from student
[where]

/* 如果没有where子语句，则整张表记录都会被删除 */

3. 修改语句

update student set age=age+1
[where]

/* 如果没有where子语句，则整张表记录都会被修改 */

4. 查询语句

1. 语法规则及执行流程

select sno,sname,sex,age,sdept from student
[where]			/* 查询条件 */
[group by]		/* 分组依据 */
[having]		/* 组提取条件 */
[order by]		/* 排序依据 */

禁止使用select *：为了尽量达到数据独立性中的逻辑独立性。

执行过程为：

执行where子句，初步筛选；
执行group by子句，进行分组；
执行聚合函数（如果有），每组产生一个统计值；
执行having子句，对分组后的数据进一步筛选；
执行order by子句，对整个结果进行排序。

2. 单表查询

1. 查询时增加常量列

select sname,'年龄',sage from student
/* 查询的结果会增加一列，列名为年龄，值均为年龄 */

2. 指定列、表别名

select sname as '学生姓名',sage as '学生年龄' from student

/* 省略as关键字也可以 */
select sname '学生姓名',sage '学生年龄' from student

表的取别名方式相同，这里就不写例子了。唯一需要注意的是，表取了别名后就只能使用别名，不能使用原表名了。

3. 消除取值相同的行

select distinct sno from student

distinct：[dɪˈstɪŋkt] 意为不同的。语句中即为查询不同的，也就是删除相同的。

4. 常用查询条件

查询条件	谓词
比较	=、>、>=、<、<=、!=(或<>)
确定范围	between and、not between and
确定集合	in、not in
字符匹配	like、not like	_：匹配任意一个字符 %：匹配0个或多个字符 []：匹配[]中的任意一个字符 [^]：不匹配[]中的任意一个字符
空值	is null、is not null
多重条件	and、or	and优先级高于or

/* 查询年龄在10到20之间的学生信息 */
select sname,age from student
where age between 10 and 20

/* 查询在数学系、外语系、中文系的学生信息 */
select sname,age,sdept from student
where srpt in ('数学系','外语系','中文系')

/* 查询所有姓张的同学信息 */
select sname,age from student
where sname like '张%'

/* 查询所有系为空的学生信息 */
select sname,age from student
where sdept is null

/* 多重条件查询(and优先级高于or) */
select sname,age from student
where (age between 10 and 20) and sdept is null

5. 查询结果排序

desc：descending [dɪˈsendɪŋ] 降序

asc：ascending [əˈsendɪŋ] 升序

/* 查询结果按照学生年龄降序排列 */
select sname,age from student
order by age desc

6. 限制结果集

SQL Sever数据库中限制结果集使用的是 TOP 关键字。

MySQL数据库中使用的是 LIMIT 关键字。

Oracle数据库中使用的是 ROWNUM 关键字。

使用场景：

寻找最高或最低数据，一般会和order by一起使用
分页

SQL Sever

TOP	说明
TOP n	取查询结果的前n行。n为非负整数。
TOP n percent	取查询结果的前n%行。n为非负整数。
WITH TIES	将并列了的结果也显示出来。注意：必须和order by一起使用。

/* 查询student表中数据按照降序排序，并取前三行数据 */
select top 3 * from student
ORDER BY age DESC

MySQL

LIMIT	说明
LIMIT n	取查询结果的前n行。n为非负整数。
LIMIT n,m	取查询结果中第n行后的m行。n为非负整数，查询结果不包括第n行。

/* 查询student表中的数据按照降序排序，并取4~7行数据 */
select * from student
ORDER BY age DESC
limit 3,4

Oracle

ROWNUM是个伪列。功能是在每次查询时，返回结果集的顺序号，这个顺序号是在记录输出时才一步一步产生的，第一行显示为1，第二行为2，以此类推。

所以想要使用rownum就必须使用子查询，因为rownum列是在查询时生成的。

select b.*
from (select rownum rn,sname,age from student) b
where rn<=3

7. 使用聚合函数

聚合函数	说明
count(*)	统计表中元组的个数
count([distinct] <列名>)	统计列中非空列的个数，distinct表示不包括列的重复值。
sum(<列名>)	计算列值总和
avg(<列名>)	计算列值平均值
max(<列名>)	求列最大值
min(<列名>)	求列最小值

/* 统计成绩表中的成绩总数、平均成绩、最高成绩、最小成绩 */
select count(*),avg(grade),max(grade),min(grade) from score

注意：聚合函数不能出现在where子语句中。

select sno,cno,grade from score
where grade=max(grade)	/* 这种写法是错误的 */

/* 正确的思路应该是 */
select sno,cno,grade from score
where grade=(select max(grade) from score)

8. 分组统计

group by子句

注意：使用分组子语句时，查询列表中的每个列必须要么是分组依据列，要么是聚合函数，因为分组后每一组只返回一行结果。。

/* 统计每门课程选课的人数 */
select count(sno),cno from score
group by cno

实现过程：

对score表的数据按照cno的值进行分组，所有相同值的元组归为一组；
对每一组使用count函数进行计算，求得每一组中的人数。

having子句

having子句用于对分组后的结果数据再进行筛选，所以having子句一般与group by子句一起使用，并且因为having子句是对分组后的结果进行筛选的，所以having子句中的条件能写的很少，一般为函数或者某些简单的条件。

建议将分组操作之前的搜索条件放到where子句中，分组后的搜索条件放到having子句中。因为如果将分组前的搜索条件也放到having子句中，则参与分组的数据会很多；将分组之前的搜寻条件放到where子句中将会减少参与分组的数据量。

/* 统计选课的人数大于20的课程 */
select count(sno),cno from score
group by cno
having count(sno)>20

3. 多表查询

在非ANSI标准中，连接操作是在where子句中执行的（即在where子句中指定表连接条件）；在ANSI SQL-92中，连接是在join子句中执行的。

这里都是用ANSI标准。

DBMS执行连接操作的过程：

取表1中的第一个元组；
从头开始扫描表2中满足连接条件的元组；
找到后将表1中的元组和表2中的元组合并起来形成结果表中的一个元组；
依次取表1中的第二、三、四、…个元组重复步骤1、2、3，直到表1中的全部元组都处理完毕。

1. 内连接

内连接：若两张表的相关字段满足连接条件，则从这两张表中提取数据并组合成新的记录。即DBMS执行连接操作的过程中只有满足条件(成功合并)的元组才会被显示出来。

select * from student inner join score on student.sno=score.sno
/* 或者可以不写inner */
select * from student join score on student.sno=score.sno

/* 
	注意：尽量不要使用select *，这里我懒写了才使用select *的。
	不过在实际开发中，可以先使用select *查询出结果，然后对照着结果修改查询语句，写自己需要的列。
*/

2. 自连接

自连接：在逻辑上将查询的表看作两张表，进行连接查询。使用自连接，必然会使用到别名。

/* 查询与张三在同一系的学生 */
select s2.sname,s2.sdept
from student s1
join student s2 on s1,sdept=s2=sdept
where s1.sname='张三' and s2.name!='张三'

3. 外连接

内连接中，只有满足连接条件的元组才能作为结果输出。而外连接不一样，外连接只限制一张表中的数据必须满足连接条件，另一张表的数据可以不满足连接条件，即不满足连接条件的元组也可以作为结果输出，空缺项用值为null。

左连接 left join

select student.sno,sname,sex,age,sdept,cno,grade
from student
left join score on student.sno=score.sno

理解1：DBMS执行连接操作的过程中满足条件(成功合并)的、不满足条件(未成功合并)的元组都会被显示出来。

理解2：拼积木。student表为主表，score为次表，以主表为骨架，将次表中的数据按照连接条件依次拼到主表上，这样主表中难免会有空缺的位置，则以null值代替，最终将整个结果显示出来

在这里插入图片描述

右连接 right join

select * from student right join  score on student.sno=score.sno

理解1：理解和左连接一样

理解2：理解和左连接一样，只不过主表现在是score，次表为student

5. 将查询结果保存到新表中

SQL Sever中语法

select sex,count(sex) as 'count_number' into newtable
from student
GROUP BY sex

MySQL中语法

CREATE TABLE newtable(
	select sex,count(sex) as 'count_number'
	from student
	GROUP BY sex
)

注意：在SQL Sever中使用函数必须取别名才能into到新表中，而MySQL中如果没有取别名则直接使用count(字段名)做列名。

6. CASE表达式

简单case表达式

/* 当所在系为数学系时，查询结果中显示math；
所在系为语文系时，查询结果中显示为chinese；
所在系为外语系时，查询结果为english */
select sno,sname,sex,age,
CASE sdept
	WHEN '数学系' THEN 'math'
	WHEN '语文系' THEN 'chinese'
	WHEN '外语系' THEN 'english'
END AS '所在系'
from student

搜索case表达式

简单表达式只能比较一个字段，且是与单值进行比较。

如果需要多个值进比较，且是与一个范围内的值进行多条件比较，就得使用搜索case表达式。

用搜索case表达式，前面的查询可以写作：

select sno,sname,sex,age,
CASE
	WHEN sdept='数学系' THEN 'math'
	WHEN sdept='语文系' THEN 'chinese'
	WHEN sdept='外语系' THEN 'english'
END AS '所在系'
from student

/* 当平均成绩>=90时，查询结果中显示好；
当80<=平均成绩<=89时，查询结果中显示比较好；
当70<=平均成绩<=79时，查询结果中显示一般；
当60<=平均成绩<=69时，查询结果中显示不太好；
当平均成绩<60时，查询结果中显示不及格；*/
select s.sname,avg(score.grade) as '平均分',
CASE
	WHEN avg(score.grade)>=90 THEN '好'
	WHEN avg(score.grade) between 80 and 89 THEN '比较好'
	WHEN avg(score.grade) BETWEEN 70 and 79 THEN '一般'
	WHEN avg(score.grade) BETWEEN 60 and 69 THEN '不太好'
	WHEN avg(score.grade)<60 THEN '不及格'
END AS '成绩情况'
from student s join score on s.sno=score.sno join course c on c.cno=score.cno
group by s.sname

7. 查询结果的并交差运算

1. 并运算union

使用条件：

各select语句中查询的列数必须相同，且各列的语义也应当相同；
各select语句中查询的列的数据类型应当相同，或者可以相互转换。

/* 将张三和王五所选的课程及该课程学分全部查询出来 */
select c.cname,c.semester
    from student stu 
    join score sc on stu.sno=sc.sno 
    join course c on c.cno=sc.cno
    where stu.sname='李四'
union
select c.cname,c.semester
    from student stu 
    join score sc on stu.sno=sc.sno 
    join course c on c.cno=sc.cno
    where stu.sname='王五'

使用union会自动去掉重复的行数据，如果不想去除，则需使用union all

select c.cname,c.semester
    from student stu 
    join score sc on stu.sno=sc.sno 
    join course c on c.cno=sc.cno
    where stu.sname='李四'
union all
select c.cname,c.semester
    from student stu 
    join score sc on stu.sno=sc.sno 
    join course c on c.cno=sc.cno
    where stu.sname='王五'

2. 交运算

SQL Sever中使用INTERSECT关键字实现交运算。

使用条件：

各select语句中查询的列数必须相同，且各列的语义也应当相同；
各select语句中查询的列的数据类型应当相同，或者可以相互转换。

/* 查询李四和王五所选的相同的课程*/
select c.cname,c.semester
    from student stu 
    join score sc on stu.sno=sc.sno 
    join course c on c.cno=sc.cno
    where stu.sname='李四'
INTERSECT
select c.cname,c.semester
    from student stu 
    join score sc on stu.sno=sc.sno 
    join course c on c.cno=sc.cno
    where stu.sname='王五'

MySQL中使用子查询实现交运算。

/* 查询结果和上面的是一样的 */
select c.cname,c.semester
	from student stu 
	join score sc on stu.sno=sc.sno 
	join course c on c.cno=sc.cno
	where stu.sname='李四'
	AND c.cname in (
		select c.cname
			from student stu 
			join score sc on stu.sno=sc.sno 
			join course c on c.cno=sc.cno
			where stu.sname='王五'
    )

3. 差运算

SQL Sever中使用except实现差运算。

使用条件：

各select语句中查询的列数必须相同，且各列的语义也应当相同；
各select语句中查询的列的数据类型应当相同，或者可以相互转换。

/* 查询李四选了但是王五没选的课程 */
select c.cname,c.semester
	from student stu 
	join score sc on stu.sno=sc.sno 
	join course c on c.cno=sc.cno
	where stu.sname='李四'
except
select c.cname,c.semester
	from student stu 
	join score sc on stu.sno=sc.sno 
	join course c on c.cno=sc.cno
	where stu.sname='王五'

MySQL中使用子连接实现差运算。

/* 查询结果和上面的是一样的 */
select c.cname,c.semester
	from student stu 
	join score sc on stu.sno=sc.sno 
	join course c on c.cno=sc.cno
	where stu.sname='李四'
	AND c.cname not in (
		select c.cname
			from student stu 
			join score sc on stu.sno=sc.sno 
			join course c on c.cno=sc.cno
			where stu.sname='王五'
	)

视图相关语句

数据库中只存放视图的定义，而不存放视图中的数据，这些数据还在原来的数据表中。
视图是一个虚表。是查询语句产生的结果。
如果基本表中的数据发生了变化，则视图中查询出来的数据也会随之变化。
在没有使用函数的前提下，同样如果视图中的数据发生了变化，则对应的查询的表中的数据也会跟着变化。

1. 创建视图

create view <视图名>[(列名, ...)]
as
	select 语句

[(列名, …)]：可以指定也可以不指定，若不指定则默认会使用select语句中查询的列名作为列名。
注意：含分组信息的视图是不能用于修改数据，只能用于查询。

2. 修改视图

alter view <视图名> [(列名, ...)]
as
	select 语句

3. 删除视图

drop view <视图名>

常用函数

函数	说明
DATEDIFF(time1, time2)	会返回time1 - time2的值，即两个日期相隔的时间
CURDATE()	获取当前时间
date(time)	格式化日期time，只会保留年月日，具体时间将会被省略
count	统计
avg	求平均值
sum	求和
max	求最大值
min	求最小值

注意：在开发时，如果你要在mapper文件中使用SQL函数则函数部分必须得使用<![CDATA[里面写使用函数的句子]]>，将句子包裹起来。这是XML语法，因为如果写的 SQL 中有一些特殊的字符的话，在解析xml文件的时候会被转义，但我们不希望他被转义，所以我们要使用<![CDATA[ ]]>来解决。