可怜人意，薄于云水，佳会更难重。

——晏几道《少年游》

​

SQL基础知识

什么是SQL ?

Structured Query Language 结构化查询语言

第一部分 SQL基础

1.1 SQL的作用

  1. 是一种所有关系型数据库的查询规范，不同的数据库都支持。
  2. 通用的数据库操作语言，可以用在不同的数据库中。
  3. 不同的数据库 SQL 语句有一些区别
1
2
3
4
5
6
graph TD;
MySQL特有-->SQL规范相当于普通话
Oracle特有-->SQL规范相当于普通话
MySQL的方言-->MySQL特有
Oracle方言-->Oracle特有

1.2 SQL语句的分类

  1. Data Definition Language (DDL 数据定义语言) 如：建库，建表
    -

  2. Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改

  3. Data Query Language(DQL 数据查询语言)，如：对表中的查询操作

  4. Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

1.3 MySQL语法

  1. 每条语句以分号结尾，如果在 SQLyog 或者Navicat中不是必须加的。
  2. SQL 中不区分大小写，关键字中认为大写和小写是一样的。
  3. 三种注释：
    -  **--空格**        单行注释    
   -  **/\* \*/**          多行注释
           -    **#**               mysql特有的注释，单行注释

第二部分 DDL数据库定义语言

create , drop , show , alter , select , use

按 tab 键可以自动补全关键字

一、对数据库的操作

2.1.1 创建数据库create

1
2
3
4
5
6
7
8
9
10
三种创建数据库的方式：

-- 直接创建
create database 数据库名;

-- 判断数据库是否已经存在，不存在创建
create database if not exists 数据库名;

-- 创建指定字符集的数据库
create database 数据库名 character set 字符集;

2.1.2 查看数据库show

1
2
3
4
5
--	查看所有的数据库 
show databases;

--	查看某个数据库的定义信息 
show create database 数据库名;

2.1.3 修改数据库alter

1
2
3
4
alert database 数据库名 default character set 字符集;

-- default 可以省略
alert database 数据库名  character set 字符集;

2.1.4 删除数据库drop

1
drop database 数据库名;

2.1.5 查看正在使用的数据库

1
2
-- 查看正在使用的数据库
select database();

二、对表的操作

前提先使用某个数据库

use 数据库名;

2.2.1 创建表create

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table 表名 (

字段名 1 字段类型 1,

字段名 2 字段类型 2

);

-- 创建 student 表包含 id,name,birthday 字段
create table student (

    id int,    -- 整数
    
    name varchar(20),    -- 字符串
    
    birthday date -- 生日，最后没有逗号
    
);

MySQL常使用的数据类型如下：

类型 描述
int 整型
double 浮点型
varchar 字符串型
date 日期类型，格式为yyyy-MM-dd,只有年月日，没有时分秒

详细的数据类型如下
分类 类型名称 类型说明
整数 tinyInt 微整型：很小的整数(占 8 位二进制) byte
smallint 小整型：小的整数(占 16 位二进制) short
mediumint 中整型：中等长度的整数(占 24 位二进制) 3个字节
int(integer) 整型：整数类型(占 32 位二进制) 4个体字节
小数 float 单精度浮点数，占 4 个字节
double 双精度浮点数，占 8 个字节
time 表示时间类型
日期 date 表示日期类型
datetime 同时可以表示日期和时间类型
字符串 char(m) 固定长度的字符串，无论使用几个字符都占满全部，M 为 0~255 之间的整数
varchar(m) 可变长度的字符串，使用几个字符就占用几个，M 为 0~65535 之间的整数
大二进制 tinyblob 允许长度 0~255 字节
blob 允许长度 0~65535 字节
mediumblob 允许长度 0~167772150 字节
longblob 允许长度 0~4294967295 字节
大文本 tinytext 允许长度 0~255 字节
text 允许长度 0~65535 字节
mediumtext 允许长度 0~167772150 字节
longtext 允许长度 0~4294967295 字节

2.2.2 查看表show&&desc

1
2
3
4
5
6
7
8
-- 查看某个数据库中的所有表
show tables;

-- 查看表结构
desc 表名;

-- 查看创建表的 SQL 语句
show create table 表名;

2.2.3 快速创建一个表结构相同的表like

1
create table 新表名 like 旧表名;

2.2.4 删除表drop

1
2
3
4
5
6
7
--直接删除表
drop table 表名;

-- 判断表是否存在，如果存在则删除表
drop table if exists 表名;

-- 与直接删除的区别: 如果表不存在，不删除，存在则删除

2.2.5 修改表结构alter

1
2
3
4
5
6
7
8
9
10
11
-- 添加新字段add 
alter table 表名 add 列名 类型;

-- 修改字段类型modify
alter table 表名 modify 字段名 新的类型;
-- 演示: 将student表中的address字段的改成 varchar(100)
alter table student modify address varchar(100);

-- 修改字段名change
alter table 表名 change 旧列名 新列名 类型;

1
2
3
4
5
6
7
8
9
10
11
12
-- 删除字段
alter table 表名 drop 列名;


-- 修改表名rename  将名字为boy的表，改名为gril表;
rename table boy to girl;

-- 修改字符集 character set
alter table 表名 character set 字符集;

-- 将gril表的编码修改成 gbk
alter table gril character set gbk;

第三部分 DML数据操纵语言

inset , update , where , delete , truncate

对表中的数据进行增，删，改操作

3.1 插入记录insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
insert into 表名 （字段名...) values(字段值...);

insert into 表名：-- 表示表示往哪张表中添加数据  into可省略
(字段名 1, 字段名 2, …)：-- 要给哪些字段设置值    可省略
values(值 1, 值 2, …)：-- 设置具体的值


-- 插入全部字段(两种方式)
insert into 表名 (字段名 1, 字段名 2, 字段名 3...) values (值 1, 值 2, 值 3...);
insert into values (值 1, 值 2, 值 3...);


-- 插入部分数据  没有添加数据的字段会使用 NULL
insert into 表名 (字段名 1, 字段名 2, ...) values (值 1, 值 2, ...);

3.2 更新表记录update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
update 表名 set 字段名=值 [where 条件表达式];

update: -- 需要更新的表名
set: -- 修改的列值
where: -- 符合条件的记录才更新

/*
同时更新一个或多个字段。
可以在 WHERE 子句中指定任何条件。
*/


-- 不带条件修改数据
update 表名 set 字段名=值;	-- 修改所有的行


-- 带条件修改数据
update 表名 set 字段名=值 where 字段名=值;

1
2
3
4
5
6
7
-- 具体操作：

-- 不带条件修改数据，将所有的性别改成女 
update student set sex = '女';

-- 带条件修改数据，将 id 号为 2 的学生性别改成男
update student set sex='男' where id=2;

3.3 删除表记录delete&&truncate

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
delete from 表名 [where 条件表达式]
/*
如果没有指定 where 子句，MySQL该表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件 
*/

-- 不带条件删除数据
delete from 表名;

-- 带条件删除数据
delete from 表名 where 字段名=值;

-- 使用 truncate 删除表中所有记录 
truncate table 表名;

-- truncate 和 delete 的区别：
truncate 相当于删除表的结构，再创建一张表。

第四部分 DQL数据查询语言

select , show , as , distinct

查询不会对数据库中的数据进行修改.只是一种显示数据的方式

一、普通查询

1
2
3
4
5
6
select 字段名 from 表名 [where 条件表达式]
select -- 命令可以读取一行或者多行记录。
/*
可以使用星号（*）来代替其他字段，SELECT 语句会返回表的所有字段数据
可以使用 where 语句来包含任何条件。
*/

4.1.1 查询所有行和列的数据select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 使用*表示所有字段
select * from 表名;

-- 查询所有的学生：
select * from student;

/*
询指定字段
查询指定列的数据,多个列之间以逗号分隔
*/
select 字段名 1, 字段名 2, 字段名 3, ... from 表名;

-- 具体操作: 查询 student 表中的 name 和 age 列
select name,age from student;

4.1.2 指定列的别名进行查询as

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 使用别名的好处： 显示的时候使用新的名字，并不修改表的结构。
-- 用于多表查询操作

-- 对列指定别名
select 字段名 1 as 别名, 字段名 2 as 别名... from 表名;

-- 对列和表同时指定别名
select 字段名 1 as 别名, 字段名 2 as 别名... from 表名 as 表别名;

具体操作：
-- 使用别名
select name as 姓名,age as 年龄 from student;

-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st

4.1.3 过滤重复的值distinct

1
2
3
4
5
6
7
8
9
-- 查询指定列并且结果不出现重复数据 
select distinct 字段名 from 表名;

-- 演示：
-- 查询学生来至于哪些地方 
select address from student;

-- 去掉重复的记录select 
distinct address from student;

二、条件查询

为什么要条件查询?

如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤。

4.2.1 条件查询的语法

流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

1
select 字段名 from 表名 where 条件

比较运算符

比较运算符 说明
>、<、<=、>=、=、<> <>在 SQL 中表示不等于，在 mysql 中也可以使用!=没有==
between…and 在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾
in(集合) 集合表示多个值，使用逗号分隔
like ‘张%’ 模糊查询
is null 查询某一列为 null 的值，注：不能写=null

逻辑运算符

or 或 || 或
and 或 **&&**与 SQL 中建议使用前者，后者并不通用。
not 或 ! 非

MySQL 通配符

通配符 说明
% 匹配任意多个字符串
_ 匹配一个字符

4.2.2 条件查询演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 准备数据
-- 创建一个student表，包含如下列：
create table student ( 
    
id int, -- 编号

name varchar(20), -- 姓名 

age int, -- 年龄

sex varchar(5), -- 性别 

address varchar(100), -- 地址 
    
math int, -- 数学

english int -- 英语成绩

);

insert into student values
(1,'上官婉儿',17,'女','sky',99,98),
(2,'小妲己',20,'女','郑州',121,98),
(3,'喵喵',17,'女','云南',69,94),
(4,'小乔',16,'女','南京',59,66),
(5,'鲁班七号',21,'男','北京',250,250),
(6,'dd',18,'男','上海',99,66);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 具体操作-比较运算符

-- 查询 math 分数大于 80 分的学生
select * from student where math>80;

-- 查询 english 分数小于或等于 80 分的学生 
select * from student where english <=80;

-- 查询 age 等于 20 岁的学生
select * from student where age = 20;

-- 查询 age 不等于 20 岁的学生，注：不等于有两种写法
select * from student where age <> 20;
select * from student where age != 20;





--具体操作-逻辑运算符

-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足) 
select * from student where age>35 and sex='男';

-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足) 
select * from student3 where age>35 or sex='男';

--查询 id 是 1 或 3 或 5 的学生
select * from student3 where id=1 or id=3 or id=5;

in关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
select 字段名 from 表名 where 字段 in (数据 1, 数据 2...);

-- in里面的每个数据都会作为一次条件，只要满足条件的就会显示



-- 具体操作：

-- 查询 id 是 1 或 3 或 5 的学生
select * from student where id in(1,3,5);

-- 查询 id 不是 1 或 3 或 5 的学生
select * from student where id not in(1,3,5);

范围查询 

1
2
3
4
5
6
7
8
9
10
/*
between 值 1 and 值 2
表示从值 1 到值 2 范围，包头又包尾

比如：age BETWEEN 80 AND 100 
相当于： age>=80 && age<=100
*/

-- 查询 english 成绩大于等于 75，且小于等于 90 的学生
select * from student where english between 75 and 90;

like 关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- like 表示模糊查询
select * from 表名 where 字段名 like '通配符字符串';


-- 查询姓小的学生
select * from student where name like '小%';

select * from student where name like '小';

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

-- 查询姓小，且姓名有两个字的学生
select * from student3 where name like '小_';

第五部分 数据控制语言

grant , revoke

我们学习中默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是在一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

5.1 用户和数据库

数据库：数据库是一种服务,帮忙存储与管理数据的守护进程。

用户：实质是在数据库名下的,是为了赋予一种不同用户对同一数据库的操作的权限(比如有些表你看不到,有些数据库你不能改)；

5.2 创建用户

创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过了加密。

1
2
3
4
5
6
7
8
9
10
11
create user '用户名'@'主机名' identified by '密码';
-- 用户名	将创建的用户名
-- 主机名	指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
-- 密码 	该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

-- 演示：
-- 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
create user 'user1'@'localhost' identified by '123';`

-- 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '123';

5.3 给用户授权grant

用户创建之后，没什么权限！需要给用户授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
grant 权限 on 数据库.表名 to '用户名'@'主机名';

-- grant...on...to 授权关键字
-- 权限 授予用户的权限，如 create , alter , select , insert ,update 等。如果要授予所有的权限则使用 ALL
-- 数据库.表名 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
-- '用户名'@'主机名' 给哪个用户授权，注意：有 2 对单引号


-- 演示
-- 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';

-- 注：用户名和主机名要与上面创建的相同，要加单引号。

--给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';

5.4 撤销授权revoke

1
2
3
4
revoke 权限 on 数据库.表名 from 'user1'@'localhost';

-- 撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';

5.5 查看权限

注：usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。

1
show grants for '用户名'@'主机名';

5.6 删除用户

1
drop user '用户名'@'主机名';