引言

最近在学数据库系统概论，这里想整理一下学习的SQL语言

安装

这个网上教程很多建议自己百度

我是之前为了本地搭建靶场在虚拟机里装过phpstudy(一个配置网站环境的软件)，就直接用phpstudy了，非常省心省力，还能切换MySQL版本，也可以用phpmyadmin(web页面的MySQL管理工具)，总之爽的一批

这里讲一下用phpstudy怎么操作

在小皮官网下载windows版的，根据你电脑下载是64位还是32位

我这里是放在虚拟机里的，方便做实验搭环境什么的

解压下根据说明无脑安装

打开来是这个样子

左侧选中数据库，这里可以修改数据库密码什么的

然后在软件管理里面找到phpMyAdmin，然后安装，这个是一个web界面的MySQL管理工具

选中路径后点击确认就行了

安装后在首页启动Apache和MySQL

然后打开浏览器，地址栏输入http://localhost/phpMyAdmin4.8.5/

如果只有logo没有登录框的话

进入php安装目录\WWW\phpMyAdmin4.8.5\libraries\classes\Plugins\Auth\AuthenticationCookie.php

ctrl+f搜索所有的hide然后删掉hide就可以了

输入你数据库的用户名和密码(初始用户名密码都是root)，这个在phpstudy的数据库里面有写，进去后就是图形化界面了

命令行界面的话

打开phpstudy安装目录\Extensions\MySQL5.7.26\bin文件夹

然后在上面地址栏输入cmd，就能进入当前目录的命令行了

输入mysql -u[用户名] -p[密码]，注意-u[用户名]和-p[密码]之间不要有空格，比如mysql -uroot -proot，然后按下回车进入mysql

常用的一些语句

create database [库名]; 创建数据库

show databases; 查看所有库

drop database [库名]; 删除库

use [库名]; 使用数据库

create table [表名] (

[字段名1] [数据类型](长度),

[字段名2] [数据类型](长度),

......

[字段名n] [数据类型](长度)

); 创建表

show tables; 查看所有表

alter table [原表名] rename [现表名];修改表名

drop table [表名];删除表

数据类型	解释	长度
int	整型	11,可以不写,默认11
varchar	字符串	0-255
char	字符串	0-255
double	浮点型	(5,2)总长5位,其中包含两位小数,999.99
date	日期	没有长度
datetime	日期时间	没有长度
timestamp	时间戳	没有长度

select [列名] from [表名] where [条件] 查询语句

select * from [表名] 查询表内所有数据

desc [表名]; 查看表的结构/查看列
alter table [表名] add [column] [列名] [数据类型]; 增加列
alter table [表名] drop [column] [列名]; 删除列
alter table [表名] change column [原列名] [现列名] [现数据类型]; 修改列

数据查询

单表查询

select [列名] from [表名]; 查询某表中某列的数据
select * from [表名]; 查询某表中的所有数据

注1：[列表达式]不仅仅可以是表中的属性列，也可以是经过计算的表达式，如：select name,age+18 from students; 从学生表中查询名字和18年后的年龄

注2：[列表达式]可以指定别名，这个在列名特别复杂或者列表达式是个经过计算的表达式时特别有用，如：select name,birthday birth from students; 从学生表中查询名字和生日(别名指定为birth)

select [all/distinct] [列名] from [表名]; 有时候查询出来的结果有重复项，这个时候可以使用distinct来消除重复项，如果没有指定disctinct，则默认为all，保留查询结果的重复项

where子句

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;not+上述运算符
确定范围	between and,not between and
确定集合	in,not in
字符匹配	like,not like
空值	is null,is not null
多重条件(逻辑运算)	and,or,not

比较大小

where [列名] [谓词] [对象]
例如：select name,age from students where age>18; 查询年龄大于18的学生姓名和年龄
比较大小的表达式非常简单，这里不再赘述

确定范围

(not) between [范围下限(即低值)] and [范围上限(即高值)]
例如：select name,age from students where age between 18 and 20; 查询年龄在18~20岁的学生姓名和年龄

确定集合

where [列名] (not) in ('元素1','元素2','元素3')
例如：select name from student where dept in ('cs','ma','is'); 查询计算机科学系(cs)，数学系(ma)，信息系(is)的学生姓名

字符匹配

where [列名] (not) like '匹配字符'

%(通配符百分号)代表任意长度(可为0)的字符串。比如a%b就是以a为开头以b为结尾的字符串

_(通配符下划线)代表任意单个字符。比如a_b就是以a开头以b结尾的长度为3的任意字符串

\(反斜杠)代表转义符。比如%是通配符，%就只是单纯的百分号了

例如：select name from student where name like '王%'; 查询姓王的学生
select name from student where name like '王_'; 查询姓王的名字两个字的学生

~~(个人觉得正则表达式regexp更好用)~~

涉及空值的查询

where [列名] is (not) null

例如：select name from students where age is null 查询所有未知年龄的学生姓名

多重条件查询

where [查询条件] and/or/not [查询条件] 查询多重条件，与(and)，或(or)，非(not)

例如：select name,age from students where age>18 and age<20 查询18-20岁的学生姓名和年龄

order by 子句

order by [列名] asc/desc

order by 子句可以将查询结果按照一个或多个列排序，asc为升序，desc为降序

例如：select name,age from students order by age desc 查询学生的姓名年龄并按照年龄降序排列

select * from students order by age asc,sno desc 查询学生信息并按照年龄升序，学号降序排列

聚集函数

聚集函数	含义
count(*)	统计元组个数
count(distinct/all [列名])	统计一列中值的个数
sum(distinct/all [列名])	求和(必须是数值型)
avg(distinct/all [列名])	求平均值(必须是数值型)
max(distinct/all [列名])	求最大值
min(distinct/all [列名])	求最小值

注：distinct:重复值去掉 all:保留重复不写默认为all

例如：select count(*) from students 查询学生总人数

group by 子句

group by [列名]

group by 子句可以将查询结果按照一列或者多列的值进行分组，值相等的为一组。目的是为了细分聚焦函数的作用对象，原本不用group by 子句聚焦函数只能对所有的数据进行作用，用了group by 子句后聚集函数能对每一组分别进行作用

例如：select age,count(name) from students group by age 根据年龄对学生进行分组并输出不同年龄的学生的数量

如果在分组后还要按某些条件对查询结果进行筛选，只输出满足条件的结果，可以使用having短语来指定筛选条件

例如：select age,count(name) from students group by age having count(name) > 10

根据年龄对学生进行分组并输出人数大于10的年龄的学生人数

注：where子句和having短语的区别是作用对象不同。where子句是对列信息进行筛选，having是对group by子句分出的组进行筛选。且where子句里不能使用聚集函数作为表达式的

连接查询

前面的都是针对单独一个表的查，如果一个查询同时涉及到两个以上的表，则称之为连接查询。

等值(使用=)与非等值(使用其他运算符)连接查询

where [表名1].[列名1] [运算符] [表名2].[列名2]

例如：select students.*,course.* from students,cource where students.sno=cource.sno 查询学生信息以及对应的课程信息

注：select和where子句后都有表名前缀，这是为了防止不同的表之间有列名相同的列，如果列名在参与连接查询的表中是唯一的，就可以省略表名前缀

where子句可以在连接查询的同时加上筛选条件

例如：select students.*,course.* from students,cource where students.sno=cource.sno and students.sno='2' 查询学号为2的学生信息及对应的课程信息

自身查询

连接查询不仅可以在不同表之间连接，也可以是一个表与自己进行连接，称为表的自身连接

比如说下面这个例子

在course表中，有每门课的先修课信息(就是说你想点一个进阶技能，必须先点基础技能)，但是咱们想要查先修课的先修课(基础技能的基础技能)，但只有单表查询没法查，就可以靠自身查询，给course表取两个别名以示区分，first表和second表(cno是课程号，cpno是先修课的课程号)

select first.cno,second.cpno from course first,course second where first.cpno=second.cno;

多表查询

连接操作除了两个表连接，自己和自己连接外，还可以两个表以上连接，上不封顶，只要你逻辑能理顺

students表是学生信息表，course表是课程信息表，sc表是课程成绩表

select students.name,course.cname,sc.grade from students,course,sc where students.sno=sc.sno and course.cno=sc.cno 查询学生姓名，课程名和成绩

嵌套查询

SQL语言中，一个select-from-where语句成为一个查询块。将一个查询块套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询

例如：select name from students where sno in (select sno from sc where cno='2');

父查询子查询

查询课程号为2学生的姓名

注：嵌套查询可以套娃，但子句不可以使用order by子句，order by子句只能对最终结果排序

带有IN谓词的子查询

由于在嵌套查询中，子查询的结果常常是一个集合，所以IN在嵌套查询中最经常使用

例如：select name from students where age in (select age from students where age<18); 查询18岁以下的学生的姓名

带有比较运算符的子查询

如果你可以确切的知道子查询的结果是单个值的时候，就可以使用>,>=,<,<=,=,!=等比较运算符

例如：select age from students where name = (select name from students where name = "小明"); 查询小明的年龄

带有ANY(SOME)或ALL谓词的子查询

子查询返回单值时可以用比较运算符，返回多值时要用ANY或者ALL，使用时需要搭配比较运算符

短句	含义
>any	大于子查询结果中的某个值
>all	大于子查询结果中的所有值
<any	小于子查询结果中的某个值
<all	大于子查询结果中的所有值
>=any	大于等于子查询结果中的某个值
>=all	大于等于子查询结果中的所有值
<=any	小于等于子查询结果中的某个值
<=all	小于等于子查询结果中的所有值
=any	等于子查询结果中的某个值
=all	等于子查询结果中的所有值
!=any	不等于子查询结果中的某个值
!=all	不等于子查询结果中的任何一个值

例如：select name from students where age<all (select age from students where name = "小明" or name = "小红"); 查询所有年龄小于小明和小红的学生

带有EXISTS谓词的子查询

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值”true”或者逻辑假值”false”

例如：select name from students where exists (select * from students where age = 18); 查询年龄18的学生姓名

注：使用EXISTS后，若子查询结果非空，则父查询的where子句返回真值，否则返回假值

使用NOT EXISTS后，若子查询结果为空，则父查询的where子句返回真值，否则返回假值

集合查询

多个select语句可进行集合操作，主要包括并操作UNION，交操作INTERSECT和差操作EXCEPT

例如：select * from security where id = ' -1' union select 1,2,3 --+ 经典的某sqlilabs的某SQL注入语句，查询id=-1的同时查询1,2,3

其他的语句也差不多

数据更新

插入数据

把要插入的数据插入指定列中，没有插入数据的列会自动赋空值null

insert into [表名] ([列名1], ... ,[列名n]) values ('[值1]',...,'[值n]')

例如：insert into students (name,age) values ('小明','18'); 往students表name,age列里插入数据小明，18

如果你能保证要插入所有列且所有列名和插入数据一一对应，那可以把列名省略，如果不是要插入所有列，那你得在不插入的位置写个null

例如：insert into students values ('小明','18'); 往students表里插入数据小明，18

insert into students values ('小明',null); 往students表里插入数据小明

也可以插入子查询结果

insert into [表名] ([列名],......) 子查询;

例如：insert into students1 (name,age) select name,age from students where age=18;把students表中年龄18的学生信息插入students1表中

修改数据

格式如下

update [表名] set [列名]='更改的值' where [条件];

单个修改

例如：update students set age='19' where name = "小明";在students表中更改小明的年龄改成19岁

多个修改

例如：update students set age=age+1;在students表中更改所有学生年龄+1

带子查询的修改

例如：update students set age=age+1 where age in (select age from students where age = 18);在students表中更改所有年龄18的学生年龄+1

删除数据

格式如下

delete from [表名] where [条件];

单个删除

例如：delete from students where age='18';删除students表中所有年龄为18的学生信息

多个删除

例如：delete from students; 删除students表中所有数据

带子查询的删除

例如：delete from students where age in (select age from students where age = '18');删除students表中所有年龄为18的学生信息

视图

视图可以认为是一个或者几个基本表或者视图中导出的表，但与基本表不同，是一种虚表，视图不存放数据，只存放数据的定义，数据仍存在原来的表中，有点类似于指针，所以原来的表里数据有变更，导出的视图里也会变更

建立视图

格式为create view [视图名] ([列名1],[列名2],...,[列名n]) as [子查询];

例如：create view students_info (name,age) as select name,age from students; 创建students表name,age的视图students_info

删除视图

格式为drop view [视图名] (cascade)

cascade级联删除语句可以把该视图和由它导出的视图一起删除

例如：drop view students_info

删除视图students_info

查询视图

查询语句跟基本表的语句差不多

更新视图

更新语句跟基本表的语句差不多，但由于视图是虚表，所以视图的更新最终要转换成对基本表的更新。

但有一说一不同系统对视图的更新有更进一步的规定，不同系统实现方法上有差异导致这些规定也不仅相同。

授权管理

授权

使用grant语句

grant [权限] on table [表名] to [用户名]; 把对某表的某权限授予给某人

权限可以是增删查改，还有像all privileges(所有权限)

例如：grant select on table students to user1; 把查询students表的权限授予给user1

grant insert,select,update(name) on table students to user1,user2; 把对students表的插入，查询，更改名字的权限授予给user1和user2

注：对某列授权时一定要明确指出相应的列名

grant all privileges on table students to user1; 把所有权限授予给user1

用户名可以是所有用户public

例如：grant select on table students to public; 把对students表的查询权限授予给所有用户

通过with grant option可以让授权的用户把权限再授予别人

例如：grant select on table students to user1 with grant option; 把对students表的查询权限授予user1，并且允许user1再授予别人

收回权限

使用revoke语句

revoke [权限] on [表名] from [用户名]; 把某人对某表的某权限收回

收回权限的语句和授权差不多

例如：revoke select on table students from user1; 把user1对students表的查询权限收回

revoke select on table students from user1 cascade; 把user1以及user1授予出的权限一起收回

注：级联(cascade)能收回直接或间接从user1处获得的权限，不写默认为cascade

数据库角色

1.创建角色

语句为create role [角色名];

2.给角色授权

参考上面

3.将一个角色授予其他的角色或者用户

语句为grant [角色1],[角色2] to [角色3] with admin option;

角色3的权限就是授予他的全部角色(角色1，角色2)权限的总和，with admin option能把权限再授予出去

4.收回权限

参考上面

例如：

create role R1; 创建角色R1

grant all privileges on students to R1; 授予R1所有权限

grant R1 to 小明; 把R1授予小明

revoke R1 from 小明; 从小明那里收回R1的权限

数据库完整性

实体完整性

实体完整性在create table时使用primary key定义，即主键。定义时可以在定义列时(定义列语句的最后)定义，也可以在定义表时(定义表语句的最后)定义，但主键为多个列组合时只能在表语句最后定义。

定义主键后，用户插入，更改或者删除数据时会对完整性规则进行检查，包括如果主键值有重复就会拒绝插入或者更改，如果有空值就会拒绝插入或者修改

例如：定义students表，其中sno为主键

create table students(
sno char(10) primary key,
name char(10),
age int(11)
);

create table students(
sno char(10) primary key,
name char(10),
age char(10),
primary key(sno)
);

定义students表，其中sno,name为主键

create table students(
sno char(10),
name char(10),
age int(11),
primary key(sno,name)
);

参照完整性

参照完整性在create table时使用foreign key定义哪些列为外键，用references指定这些外键参照哪些表的主键。

例如：定义sc表，sno为主键，外键参照students的sno列

create table sc(
sno char(10),
grade char(10),
primary key (sno),
foreign key (sno) references students(sno)
);

用户定义的完整性

就是针对某一具体应用的数据需要满足某些要求，包括

1.列值非空(not null)

2.列值唯一(unique)

3.列值是否满足一个条件表达式，不满足则拒绝执行(check短语)

例如：定义students表，其中sno不为空

create table students (
sno char(10) not null,
name char(10),
age int(11)
);

定义students表，其中sno唯一

create table students (
sno char(10) unique,
name char(10),
age int(11)
);

定义students表，其中age>18

create table students (
sno char(10),
name char(10),
age int(11) check (age > 18)
);

完整性约束命名子句

sql语言提供了constraint用来对完整性约束条件进行命名，从而可以方便的增加或者删除一个完整性约束条件

格式为constraint [名字] [约束条件];

约束条件包括not null,unique,primary key,foreign key,check短语等

例如：

create table students (
sno char(10),
name char(10),
constraint C1 name not null,
age int(11),
constraint C2 check (age>18),
constraint pk primary key (sno),
constraint fk foreign key (sno) references sc(sno)
);

删除的语句格式为

alter table [表名] drop constraint [名字];

例如：alter table students drop constraint C1; 删除students表中命名为C1的约束条件

至于修改，那就先删掉原来的再增加新的约束条件。

引言

安装

分类