MySQL数据库 第二章 SQL语句

SQL是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

一、SQL语句分类

  • DCL(Data Control Language)：数据控制语言，用来定义访问权限和安全级别。（不太重要，一般用navicat图形化界面来操作）

  • DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构。

  • DML(Data Manipulation Language)：数据操作语言，用来定义数据库记录：增、删、改表记录。

  • DQL(Data Query Language)：数据查询语言，用来查询记录。也是本章学习的重点。（！！！很重要）

二、DCL(数据控制语言)语法学习

DCL(Data Control Language)：数据控制语言，用来定义访问权限和安全级别。DCL语言可用navicat软件来进行图形化界面操作。

tips:！！！该节内容不是很重要

1、创建用户、修改密码、删除用户

（1）创建用户

创建一个用户，让该用户只能在指定ip地址或任意ip地址上来登录mysql

1
2
3
4
-- 创建用户
-- 指定ip，语法：create user '用户名'@'ip地址' identified by '密码';
-- 任意ip，ip地址段用‘%’来表示，例如
create user 'ariesfun'@'%' identified by 'root';

（2）修改密码

1
2
3
4
-- 修改密码，8.0版本的mysql直接赋值新密码就行
-- 注意:若创建用户后直接连接mysql了，修改密码后需用新密码重新连接，再进行后面的测试
-- 语法：set password for '用户名'@'ip地址' = '新密码';
set password for 'ariesfun'@'%' = '123456';
1
2
-- 以上是用sql语句来操作，如果直接修改表，也是可以创建用户和修改密码的，但是要在mysql数据库下的user表修改，改完后需要刷新权限后才能生效，刷新语句如下
flush privileges;-- 在本地数据库下刷新

（3）删除用户

1
2
3
-- 删除用户
-- 语法：drop user '用户名'@'ip地址';
drop user 'ariesfun'@'%';

2、给用户授予权限、撤销权限，查看权限

给【指定用户】在【指定数据库】上赋予【指定权限】，权限有很多，常用的有以下几个：

  • create：可以创建数据库
  • insert：可以插入数据
  • delete：可以删除数据
  • select：可以查询数据
  • update：可以更新数据

（1）授权

1
2
3
4
-- 授权（包含所有权限或指定权限）
-- 语法 ：grant 权限1,权限2,... on `数据库名`.`某表` to '用户名'@'ip地址';
-- 注意：这里数据库名和表名用了撇符号``（按键在键盘左上角,用''单引号也可以），这样写的目的是防止将名称识别成关键字,造成sql语句执行失败
grant all on `student`.`user` to 'ariesfun'@'%';
1
2
-- 授权所有的表，用"*"
grant all on `student`.* to 'ariesfun'@'%';
1
2
-- 授予指定权限
grant create,insert,delete,select,update on `student`.`user` to 'ariesfun'@'%';

（2）撤销权限

1
2
3
4
-- 撤销权限（包含所有权限或指定权限）
-- 语法：revoke 权限1,权限2,... on '数据库名'.'某表' from '用户名'@'ip地址';
revoke all on `student`.`user` from 'ariesfun'@'%';
revoke create,insert,delete,select,update on `student`.`user` from 'ariesfun'@'%';

（3）查看权限

1
2
3
-- 查看授权
-- 语法：show grants for 用户名 或 '用户名'@'ip地址';
show grants for ariesfun;

3、操作实例

（1）注意事项

每条语句选中后单独执行，且输出信息为”OK”说明操作成功
Snipaste_2022-10-04_16-23-35

新建一个test连接测试DCL语言用，先点测试连接，成功后再点击确定
Snipaste_2022-10-04_16-18-51

查看数据库中表的权限
Snipaste_2022-10-04_16-54-44

（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
-- 测试用，以下每一行语句按顺序单独执行一次

-- 创建用户并更改密码
create user 'ariesfun'@'%' identified by 'root';
set password for 'ariesfun'@'%' = '123456';

-- 创建数据库
create database student;
show databases;
-- 新建表，为了方便这里先用navicat软件，在本地连接中的student中新建一张user表和一张score表（后面章节会有基本表的定义语句）

-- 进行权限操作,授权和撤权成对使用
grant all on `student`.`user` to 'ariesfun'@'%';
revoke all on `student`.`user` from 'ariesfun'@'%';

grant create,insert,delete,select,update on `student`.`user` to 'ariesfun'@'%';
show grants for ariesfun;-- 查看一下授权情况
-- 运行完上面的语句，test连接中的student数据库中又出现了user表
revoke create,insert,delete,select,update on `student`.`user` from 'ariesfun'@'%';

grant all on `student`.* to 'ariesfun'@'%';
show grants for ariesfun;
-- test连接中的student数据库中又会出现user表和score表两张表
revoke all on `student`.* from 'ariesfun'@'%';

-- 最后删除刚创建的用户，再次连接test就会操作失败
drop user 'ariesfun'@'%';

三、DDL(数据定义语言)

DDL语言主要是用于定义或改变表（TABLE）结构，主要的命令有CREATE、ALTER、DROP等

1、创建表的语法

1
2
3
4
5
6
7
8
9
10
11
create table 表名 (

字段名1(即列名) 类型(宽度) 约束条件,

字段名2 类型(宽度) 约束条件,

字段名3 类型(宽度) 约束条件,
    
.......
    
);

在关系型数据库中，我们需要设定表名、列名，同时还需要指定数据类型（常用的：整型、浮点型、字符串型）。

2、常用的数据类型

整型

MySQL数据类型 含义（默认有符号）
tinyint 1字节，范围（-128~127）
smallint 2字节，范围（-32768~32767）
mediumint 3字节，范围（-8388608~8388607）
int 4字节，范围（-2147483648~2147483647）
bigint 8字节，范围（+-9.22 * 10^18^）

在整型中我们默认使用的都是【有符号】的，当然了，我们也可以加上unsigned关键字，定义成【无符号】的类型，那么对应的取值范围就会发生改变：

如：tinyint unsigned的取值范围为0~255，

宽度n的作用需要配合zerofill进行使用( zerofill指0填充)： 如：int(4) unsigned zerofill ，查询结果: 0001 0002 1000

浮点型

MySQL数据类型 含义
float(m, d) 4字节，单精度浮点型，m总长度，d小数位
double(m, d) 8字节，双精度浮点型，m总长度，d小数位（可用来表示很大或很小的数）
decimal(m, d) decimal是存储为字符串的浮点数(无误差且精度高，可用来表示余额)，对应Java中的Bigdecimal

我们用一个例子来判断小括号内参数的含义，针对数据类型为float(5, 3)的列，做了以下试验：

  • 插入123.45678，最后查询得到的结果为99.999（会取满足条件的最大值）；
  • 插入12.34567，最后查询结果为12.346（会有四舍五入）；

所以，在使用浮点型的时候，还是要注意陷阱，要以插入数据库中的实际结果为准。

字符串型

MySQL数据类型 含义
char(n)，n指字符个数 固定长度，最多255个字节（字符）
varchar(n) 可变长度，最大容量65535个字节
tinytext 可变长度，最大容量255个字节
text 可变长度，最大容量65535个字节
mediumtext 可变长度，最大容量2^24^-1个字节，16MB
longtext 可变长度，最大容量2^32^-1个字节， 4GB

（1）char和varchar两类型的区别（这两个也最常用）：

  • char类型是【定长】的类型，即当定义的是char(10)，输入的是”abc”这三个字符时，它们占的空间一样是10个字符，包括7个空字节。当输入的字符长度超过指定的数时，char会截取超出的字符。而且，当存储char值时，MySQL会自动删除输入字符串末尾的空格。
  • char类型适合存储很短的、一般固定长度的字符串。例如，用char(1)来定义性别；char类型非常适合存储密码的MD5值（将不定长的变为定长的值），因为MD5是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。
  • varchar(n)类型用于存储【可变长】的类型，且长度最大为n个字符的可变长度字符数据。比如varchar(10), 输入abc三个字符，那么实际存储大小为3个字节。除此之外，varchar还需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示。n表示的是最大的长度。
  • char类型每次修改的数据长度相同，效率更高；varchar类型每次修改的数据长度不同，效率更低。

（2）varchar和text

  • text不能设置默认值，varchar可以。

  • text类型，包括（MEDIUMTEXT，LONGTEXT）也受单表 65535 最大行宽度限制，所以他支持溢出存储，只会存放前 768 字节在数据页中，而剩余的数据则会存储在溢出段中(磁盘上)。虽然 text 字段会把超过 768 字节的大部分数据溢出存放到硬盘其他空间，看上去是会更加增加磁盘压力。但从处理形态上来讲 varchar 大于 768 字节后，实质上存储和 text 差别不是太大了。因为超长的 varchar 也是会用到溢出存储，读取该行也是要去读硬盘然后加载到内存，基本认为是一样的。

  • 根据存储的实现：可以考虑用 varchar 替代 text，因为 varchar 存储更弹性，存储数据少的话性能更高。

  • 如果存储的数据大于64K，就必须使用到 mediumtext或longtext，因为 varchar 已经存不下了。

  • 如果 varchar(255+) 存储大于255字节后，和 text 在存储机制是一样的，性能也相差无几。

    详细解释可参考文章：MySQL 的 varchar 与 text 对比

日期和时间数据类型

MySQL数据类型 含义
date 3字节，日期，格式：2022-10-01
time 3字节，时间，格式：08:00:00
datetime 8字节，日期时间，格式：2022-10-01 08:00:00
timestamp 4字节，自动存储记录修改的时间
year 1字节，年份

3、建表约束

目前我们已经学会了如何创建表，但是一张表不止有一列，且数据库中的表不止有一个，建表约束说的就是，我们应该如何规范表中的数据以及表和表的关系。

（1）MySQL约束类型

约束名称 描述
NOT NULL 非空约束（不是 null）
UNIQUE 唯一约束，取值不允许重复
PRIMARY KEY 主键约束（主关键字），自带非空、唯一、索引约束属性
DEFAULT 默认值（缺省值）
FOREIGN KEY 外键约束（外关键字，会用到其他表信息）

（2）NOT NULL约束

非空约束指的是，如果我们要在这张表中添加数据，设定了非空约束的列必须要赋值，不能为空

1
2
3
4
5
6
7
8
9
10
-- 先创建一个书店的数据库
create database bookstore;	-- 用database或schema来创建的作用一样
-- 创建作者表
create table `bookstore`.`author`(
    aut_id int, 
    aut_name varchar(50) not null,	-- 这就是非空约束的定义
	gander char(1) default '男',
	country varchar(50),
	birthday datetime	-- 最后不用加逗号
);

（3）UNIQUE约束

唯一约束指的是，如果我们要在这张表中添加数据，设定了唯一约束的列中的值不能有重复，不能为空

  • 唯一约束，实现方法1（在表的定义最后施加）
1
2
3
4
5
6
7
8
9
10
-- 创建图书表
create table `bookstore`.`book`(	
-- 或者加上"if not"表示如果不存在就建该表,eg.create table if not `ydlclass`.`book`该语句跟上边语句作用一样
	book_id int primary,
	book_name varchar(50) not null,
	bar_code varchar(30) not null,
	aut_id int not null,
	foreign key (key_id) references author(aut_id)，-- 外键约束
    unique(bar_code)   -- 这就是唯一约束的定义
);-- sql语句单句执行时最后面可不用加";"
  • 实现方法2（在字段名定义的后面施加）
1
2
3
4
5
6
7
-- 创建图书表
create table `bookstore`.`book`(
	book_id int primary,
	book_name varchar(50) not null,
	bar_code varchar(30) not null unique,  -- 这就是唯一约束的定义
	aut_id int not null
)

（4）DEFAULT约束

默认约束指的是，如果我们要在这张表中添加数据，如果为给设定了默认约束的列赋值，该列会自动填充默认值

1
2
3
4
5
6
7
8
-- 创建作者表
create table `bookstore`.`author`(
    aut_id int, 
    aut_name varchar(50) not null,
	gander char(1) default '男',	 -- 这就是默认约束
	country varchar(50),
	birthday datetime
);

（5）PRIMARY KEY约束

主键（PRIMARY KEY）的完整称呼是“主键约束”，是 MySQL中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。

主键分为单字段主键和多字段联合主键，本节将分别讲解这两种主键约束的创建、修改和删除。

使用主键应注意以下几点：

  • 每个表只能定义一个主键。
  • 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
  • 一个字段名只能在联合主键字段表中出现一次。
  • 【联合主键】不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

单个字段作为主键

1
2
3
4
5
6
7
8
9
-- 创建作者表
create table `bookstore`.`author`(
    aut_id int primary key,      -- 这就是主键约束 
    -- 主键约束也可用"primary key(aud_id)"来定义主键或联合主键，但要放在语句最后
    aut_name varchar(50) not null,
	gander char(1) default '男',
	country varchar(50),
	birthday datetime
);

联合主键：多个字段同时作为主键

1
2
3
4
5
6
7
8
9
-- 创建作者表
create table `bookstore`.`author`(
	`aut_id` int ,
	`aut_name` varchar(50) not null,
	`gander` char(1) default '男',
	`country` varchar(50),
	`brithday` datetime,
	primary key(aut_id,aut_name)   -- 这就是联合主键
);

（6）AUTO_INCREMENT约束

需要配合主键使用，有个这个约束，我们在向表中插入数据时，不需要额外关心主键的数据，它会自动帮我们维护一个递增的主键

1
2
3
4
5
6
7
-- 创建图书表
create table `bookstore`.`book`(	
		book_id int primary key auto_increment,	-- 这就是自动递增
		book_name varchar(50) not null,
		bar_code varchar(30) not null,
		aut_id int not null
)

（7）FOREIGN KEY约束

外键维护的表与表之间的关系，它规定了当前列的数据必须来源于一张其他表的某一列中的主键

外键会产生的效果

1、删除表时，如果不删除引用外键的表，被引用的表不能直接删除

2、外键的值必须来源于引用的表的主键字段

语法：

FOREIGN KEY (column list) REFERENCES [primary key table] (column list);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建作者表
create table `bookstore`.`author`(
    aut_id int primary key, 
    aut_name varchar(50) not null,
	gander char(1) default '男',
	country varchar(50),
	birthday datetime
);

-- 创建图书表
create table `bookstore`.`book`(
	book_id int primary key auto_increment,
	book_name varchar(50) not null,
	bar_code varchar(30) not null,
	aut_id int not null,
	foreign key (aut_id) references author(aut_id)	-- 这就是外键约束
);

4、对表的修改操作

查看当前数据库中所有表:SHOW TABLES;

1
2
-- 查看所有表
show tables;

查看表结构:DESC 表名;

1
2
-- 查看表结构
desc authors;

修改表有5个操作，但语法前缀都是一样的:ALTER TABLE 表名...（不重要）

  • 添加列（语法）：ALTER TABLE 表名 add (列名 列类型，... ，列名 列类型);

    1
    2
    -- 给author表添加列
    alter table author add (hobby varchar(20),address varchar(50));

  • 修改列类型： ALTER TABLE 表名 MODIFY 列名 列的新类型;

    1
    2
    -- 给author表修改列类型
    alter table author modify address varchar(100);

  • 列名称、列类型一起修改：ALTER TABLE 表名 CHANGE 原列名 新列名 列名类型;

    1
    2
    -- 改变列名和属性
    alter table author change address addr varchar(60);

  • 删除列：ALTER TABLE 表名 DROP 列名;

    1
    2
    -- 删除列
    alter table author drop addr;

  • 修改表名：ALTER TABLE 表名 RENAME TO 新表名

    1
    2
    -- 修改表名
    alter table author rename authors;

  • 删除表：ALTER TABLE 表名 DROP 列名;

    1
    2
    -- !!!删除表，需谨慎操作
    drop table if exists `user`;

四、DML(数据操作语言)（！！！很重要）

该语言用来对表记录进行操作（增、删、改），不包含查询。

1、插入数据

1
2
3
4
-- 插入数据
insert into `authors` (aut_name,gander,country,birthday,hobby) values ('海伦·凯勒','女','漂亮国','1960-1-1','旅游'); 

insert into `authors` values ('余华','男','中国','1970-1-1','看书');

说明:

  1. 在数据库中所有的字符串类型，必须使用单引号。
  2. (列名1，列名2，列名3)可省略，表示按照表中的顺序插入。但不建议采取这种写法，因为降低了程序的可读性。

当然我们还可以批量插入：

1
2
-- 批量插入数据
insert into `authors` (aut_name,gander,country,birthday,hobby) values ('海伦·凯勒','女','漂亮国','1960-1-1','旅游'),('余华','男','中国','1970-1-1','看书'); -- 可批量插入

2、修改记录

修改某列的全部值：update 表名 set 列名1=列值1(，列名2=列值2)(, ... );

1
2
-- 修改数据
update `authors` set aut_name = '鲁迅',country='中国';

上边的语句会改表中所有的数据全部修改，因此我们可以使用where语句进行限制，

1
update `authors` set aut_name = '马云' where aut_id = 5;	-- 限制条件可以是其他逻辑运算

where是一个很关键的关键字，我们可以使用where关键字实现丰富的筛选，他很像我们的if语句，可以使用各种条件运算：

可使用的逻辑运算符如下：=、!=、<>、<、>、>=、<=、between...and、in(…)、is null、not、or、and，其中in(a,b,… )的用法表示集合，选中限制下的那几条数据。

  • where aut_id >1
  • where aut_id in (1,3,5) -- 修改指定的三条数据
  • where aut_id between 1 and 4 -- 修改从id从1到4的数据
  • where aut_id >1 and aut_name='xxx' --多条限制
  • where aut_name is null -- 修改为空的
  • where aut_name is not null -- 修改不为空的 
1
2
3
4
5
6
7
8
9
10
-- 字符串也使用"="比较，不是"=="也不是"equals"
update `authors` set aut_name = '赵四',
-- 使用in关键字
update `authors` set aut_name = '张三',country='中国' where aut_id in(5,7); -- 修改指定的两条数据
-- 限制条件可以是其他任何逻辑运算
update `authors` set aut_name = '王五',country='中国' where gander = '男' and country = '中国';
-- 使用between...and关键字
update `authors` set aut_name = '张伟',country='中国' where aut_id between 5 and 7;
-- 空值使用is null \  is not null
update `authors` set aut_name = '王五',country='中国' where birthday is not null;

3、删除数据

语法：delete from 表名 (where 条件);

1
2
-- !!!删除表,删除条件可以是任何的逻辑运算（in,between...）
delete from authors; -- delete是按行删，俗称删库跑路

不加where条件时会删除表中所有的记录，所以为了防止这种失误操作，很多数据库往往都会有备份。

1
2
-- 删除指定行的数据
delete from author where auth_id = 1;

还有一个关键字：truncate，truncate是DDL语言(数据定义语言)，操作立即生效，原数据不会放到rollback segment中，不能回滚。

1
2
-- 不可随意使用truncate
truncate table authors; -- truncate会直接删掉整张表，再重新建表
  • 当表被truncate后，表和索引的所占空间会恢复到初始大小，delete操作不会减少表和索引的所占空间。
  • truncate和delete只删除数据，drop则删除整个表（结构和数据）。
  • truncate速度快，效率高，可以理解为先把表删除了，再重新建立。
  • truncate和delete均不会使表结构及其列、约束、索引等发生改变。