DDL 数据库定义语言

DML 数据库操作语言

DQL 数据库查询语言

DCL 数据库控制语言

SQL语句中的关键字不区分大小写

操作数据库

1.创建数据库

1
2
create database 数据库名
create database if not exists 数据库名[判断条件]

2. 删除数据库

1
drop database if exists 数据库名[如果存在]

3. 使用数据库

1
2
-- 如果表名或字段名是一个特殊字符，需要带上``
use 数据库名

4.查看数据库

1
2
show databases
--查看所有的数据库

可以对比sqlyog的可视化操作

2.2数据的数据类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小 3个字节
  • int 4个字节 最常用
  • bigint 较大的数据 8个字节
  • float 浮点数 4
  • double 浮点数 8
  • decimal 字符串形式的浮点数 金融计算的时候 一般使用decimal

字符串

  • char 字符串固定大小 0-255
  • varchar 可变字符串 0-65535 最常用
  • tinytext 微型文本 2^8 - 1
  • text 文本串 2^16 - 1 保存大文本

时间日期

  • date YYYY-MM-DD 日期
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳 1970.1.1 到当前的毫秒数
  • year 年份表示

null

  • 空值
  • 运算结果一定为null 计算无意义 不推荐

2.3 数据库的字段属性

Unsigned

  • 无符号的证输
  • 该列不能声明为负数

zerofill

  • 0填充的
  • 不足的位数，使用0来填充 int(3) 5=> 005

autoincrement

  • 通常理解为自增，自动在上一条记录的基础上+1
  • 通常用来设置唯一的主键 index 必须是整数类型
  • 可以设置自增的起始值和步幅

Null ，not Null

  • 设置为not null 新增数据时，不填会报错
  • 不填写值默认就是null

默认

  • 设置默认值
1
2
3
4
5
6
7
8
/*
每一个表都必须存在以下五个字段
id 主键
`verson` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/

2.4 创建数据库表

1
2
3
4
5
6
7
8
9
10
11
12
13
-- auto_increment 自增
-- primary key 主键 一般一个表只有一个唯一主键

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)	
) ENGINE = INNODB DEFAULT CHARSET=utf8

常用sql

1
2
3
SHOW CREATE DATABASE school -- 查看数据库的定义语句
SHOW CREATE TABLE student -- 查看表的定义语句
DESC student -- 显示表的结构

2.5数据库引擎

1
2
3
4
5
6
-- 关于数据库引擎

/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍

常规使用：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性较高，支持事务处理，多表多用户操作

在物理空间存在的位置

所有数据库文件都存在data目录下

本质还是文件的存储，一个文件夹对应一个数据库

MySQL 引擎在物理文件上的区别

  • INNODB 在数据库表中只有*.frm文件，以及上级目录下的ibdata1文件
  • MYISAM 对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件 （index）

设置数据库表的字符集编码

1
CHARSET=utf8

MySQL 的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

1
character-set-server=utf8

2.6 修改删除表

修改

1
2
3
4
5
6
7
8
9
10
11
-- 修改表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段（重命名，修改约束）
ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE student1 CHANGE age age1 INT(1) -- 修改字段名


-- 删除表的字段
ALTER TABLE student1 DROP age1

删除

1
2
-- 删除表
DROP TABLE IF EXISTS student1

注意点：

  • ``字段名使用该符号包裹，

  • 注释 – /* */

  • sql 对大小写不敏感

  • 所有符号全部为英文

3.MySQL 数据管理

3.1 外键（了解即可）

方式一：在创建表的时候，增加约束（麻烦，较复杂）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE IF NOT EXISTS `grade` (
	`gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级id', 
	`gradename` VARCHAR(50) DEFAULT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`),
	KEY `FK_gradeid` (`gradeid`)
) ENGINE = INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键 key
-- 给这个外键添加约束 （执行引用） reference 引用

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`gradeid` VARCHAR(20) NOT NULL AUTO_INCREMENT COMMENT '年级id', 
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE = INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），在删除被引用的表（主表）

方式二 创建表以后添加约束

1
2
3
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

以上外键都是物理外键 数据库级别的外键，不建议使用（避免数据库过多造成困扰）

最佳实践

  • 数据库就是单纯的数据，只用来存数据，只有行和列
  • 想使用多张表的数据，想使用外键（程序去实现）

3.2 DML语言（全部记住）

数据库定义: 数据存储，数据管理

DML语言： 数据库操作语言

  • insert
  • update
  • delete

3.3 添加 insert

insert

1
2
3
4
5
6
7
-- 插入语句（添加）
-- 主键自增 可以省略，不写字段名会一一匹配
INSERT INTO `grade` (gradename) VALUES('大三')
-- 一般写插入语句一定要保证数据和字段一一对应
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')

INSERT INTO `student`(`name`,`pwd`) VALUES('张三','1234'),('李四','1234'),('王五','1234')

注意事项：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的，但是后面的值必须一一对应
  3. 同时插入多个值，需要使用括号，逗号隔开

3.4 修改 update

update 修改谁（条件） set 原来的值 = 新值

1
2
3
4
5
6
7
8
-- 修改学员的名字
-- 不指定条件，会改动所有的数据
UPDATE `student` SET `name` = '狂神' WHERE id = 1
-- 语法
-- update 表名 set column_name = value where 条件

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = '狂神',email='email' WHERE id = 1

条件：where 子句 运算符

操作符 含义 范围 结果
= 等于
<> 或 != 不等于
>
<
>=
<=
BETWEEN 2 AND 5 闭合区间[] [2,5]
AND &&
OR ||

注意：

  • column_name 是数据库的列
  • 条件 筛选的条件
  • value 是一个具体的值，也可以是变量
  • 多个设置的属性使用英文逗号隔开

3.5 删除 delete

delete

1
2
-- 删除数据
DELETE FROM `student` WHERE id = 1

TRUNCATE

完全清空一个数据库表，表的结构和索引约束不会变

1
2
-- 清空 student 表
TRUNCATE `student`

DELETE 和 TRUNCATE 的区别

  • 相同点：都能删除数据，不会删除表结构
  • 不同：
    • TRUNCATE 会重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务
1
2
3
4
5
6
7
8
9
10
11
12
-- 测试
CREATE TABLE `test` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO test (`coll`) VALUES('1'),('2'),('3')

DELETE FROM test -- 不会影响自增

TRUNCATE test -- 自增归零

delete 删除的问题，重启数据库现象，

  • innoDB 自增列会从1开始（存在内存中，断电即失）

  • MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）

4. DQL 查询数据

4.1 DQL

Data Query Language 数据查询语言

  • 所有的查询操作 select
  • 简单复杂的查询都能做
  • 数据库最核心的，最重要的语句
  • 使用频率最高的语句
1
2
3
4
5
6
7
8
9
10
11
-- 查询全部的学生
SELECT * FROM student

-- 查询指定的字段
SELECT `studentno`,`studentname` FROM student

-- 别名 给结果取一个名字 AS 可以给字段起别名 也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS A

-- 函数 Concat(a,b) 
SELECT CONCAT('姓名：',studentname) AS 新姓名 FROM student

有的时候，列名的语义化不是很好，就可以取别名，表也可以取别名

4.2 指定查询的条件

不重复的 DISTINCT

去除select结果中重复的数据

1
2
3
4
5
6
-- 查询有成绩的学员
SELECT * FROM result -- 查询所有的成绩
-- 查询有哪些学生参加了考试
SELECT studentno FROM result
-- 重复数据删除
SELECT DISTINCT studentno FROM result

数据库的列

1
2
3
4
5
6
7
-- 查看数据库版本
SELECT VERSION() -- 函数
SELECT 100*3-1 AS 计算结果 -- 用来计算 表达式
SELECT @@auto_increment_increment -- 查询自增的步长 变量

-- 查询所有学员成绩 +1
SELECT studentno,studentresult+1 AS 提分后 FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…

4.3 where 条件子句

检索符合条件的数据

搜索的条件由一个或多个组合，返回结果是一个布尔值

逻辑运算符

运算符 语法 描述
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
not ！ not a !a 逻辑非

尽量使用英文字母

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT studentno,studentresult FROM result
-- 95-100分之间
SELECT studentno,studentresult FROM result WHERE studentresult > 95 AND studentresult < 100

-- 模糊查询（区间）
SELECT studentno,studentresult FROM result 
WHERE studentresult BETWEEN 95 AND 100

-- 学号1000 以外学生的成绩
SELECT studentno,studentresult FROM result 
WHERE studentno != 1000

SELECT studentno,studentresult FROM result 
WHERE NOT studentno = 1000

模糊查询：比较运算符

运算符 语法 描述
is null a is null 操作符为null结果为真
is not null a is not nul 操作符不为null结果为真
between a between b and c 若a在b和c之间为真
like a like b SQL 匹配
in a in (a1,a2,a3) 假设a在a1，a2，a3中某一个
，结果为真		 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查询姓王的同学
-- like   % 代表0到任意个字符  _ 一个字符
SELECT studentno,studentname FROM student WHERE studentname LIKE '王%'

-- 查询名称中有嘉字的
SELECT studentno,studentname FROM student WHERE studentname LIKE '%五%'

-- ======  in（具体的一个或多个值）  ===========
-- 查询1001，1002，1003 号学员
SELECT studentno,studentname FROM student WHERE studentno IN (1001,1002,1003)

-- 查询在北京的学生
SELECT studentno,studentname FROM student WHERE address IN ('北京')

-- ======  null not null  ===========
-- 查询地址为空的学生 null ''
SELECT studentno,studentname FROM student 
WHERE address = '' OR address IS NULL

4.4 联表查询

JOIN 对比

image-20210424085811613

image-20210424085914397

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
-- join on 连接查询
-- where 等值查询


SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
INNER JOIN result AS r 
WHERE s.studentno = r.studentno

-- right join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
RIGHT JOIN result r 
ON s.studentno = r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
RIGHT JOIN result r 
ON s.studentno = r.studentno

-- 查询缺考
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
LEFT JOIN result r 
ON s.studentno = r.studentno
WHERE studentresult IS NULL
操作 描述
inner join 表中至少有一行匹配，返回行
left join 即使右表不匹配，也会返回左表所有的
right join 即使左表不匹配，也会返回右表所有的

image-20210425153632811

1
2
3
4
5
6
SELECT s.studentno,studentname,studentresult,subjectname
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

image-20210425154337478

自连接

自己的表和自己连接 核心：一张表拆为两张一样的表

categroyid categroyname
2 信息技术
3 软件开发
5 美术设计

子类

pid categoryid categoryname
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 ps技术

操作：查询父类 对应子类的关系

父类 子类
信息技术 办公信息
软件开发 web开发
软件开发 数据库
美术设计 ps技术 
1
2
3
4
-- 查询父子信息
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.categoryid = b.pid

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查询学员所属的年级（学号，姓名，年级名称）
SELECT studentno ,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid

-- 查询科目所属的年级(科目名称，年级名称)
SELECT subjectName,gradename
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid


-- 查询 参加 数据库结构-1 考试的学生
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'

4.5 分页和排序

排序

1
2
3
4
5
6
7
8
9
10
11
-- ========= 分页和排序 ===========
-- 排序，升序 ASC 降序 DESC
-- 查询结果降序排序
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY StudentResult DESC

分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 分页 减小数据库压力 给人的体验更好
-- 分页每页只展示5条数据
-- limit 0,5 1-5  起始到结束
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY StudentResult DESC
LIMIT 0,5

-- 第一页 0-5
-- 第二页 5-5
-- 第三页 10-5
-- 第N页  (n-1)*pageSize

4.6 子查询