文章目录
一、数据库的四大特性ACID:
1.原子性(atomicity,或称不可分割性):一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
2.一致性(consistency):在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
3.隔离性(isolation,又称独立性):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括未提交读(Read uncommitted)、提交读(read committed)、可重复读(repeatable read)和串行化(Serializable)。
4.持久性(durability):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
二、事务
在数据库系统中,一个事务是指:由一系列数据库操作组成的一个完整的逻辑过程。例如银行转帐,从原账户扣除金额,以及向目标账户添加金额,这两个数据库操作的总和,构成一个完整的逻辑过程,不可拆分。这个过程被称为一个事务,具有ACID特性。
三、终端安装数据库(Docker插件)
我们通过Docker软件来安装数据库。安装好Docker软件后,在终端执行以下命令:
1.安装命令
sudo docker run -itd -p 3306:3306 \
-e MYSQL_ALLOW_EMPTY_PASSWORD="root" \
--name mysql \
mysql \
--character-set-server=utf8 \
--collation-server=utf8_general_ci \
--default-authentication-plugin=mysql_native_password
2.检验安装
执行如下命令:
sudo docker ps
看到终端输出以下内容:
则表示MySQL安装完毕。
3.进入服务
执行如下命令:
sudo docker exec -it mysql bash
可以看到类似界面:
4.登录MySQL
执行如下命令:
mysql -uroot
可以看到类似这样的界面:
这时mysql>
后面是可以继续输入sql语句的。
5.创建数据库
此时可以输入SQL语句:
CREATE DATABASE database_name;
创建出名为database_name的数据库。
6.登出
执行如下命令:
\quit
7.退出服务
执行如下命令:
exit
8.执行SQL语句
举例如下:
mysql -h 192.168.0.1 -uroot -Dyoukedadb -e 'select * from timi_adc;'
- 192.168.0.1 是优课达平台 IP,固定的不用改
- -uroot 表示使用 root 用户
- -Dyoukedadb 表示操作的是第一章创建好的 youkedadb 库
- -e 表示执行后面单引号内的 SQL 语句
9.复杂 SQL语句
如果命令较长,可以执行包含 SQL 语句的 index.sql 文件。
执行文件:
mysql -h 192.168.0.1 -uroot -Dyoukedadb < index.sql;
- 192.168.0.1 是优课达平台 IP,固定的不用改
- -uroot 表示使用 root 用户
- -Dyoukedadb 表示操作的是第一章创建好的 youkedadb 库
10.备份数据:
执行如下命令:
mysqldump -h 192.168.0.1 -uroot youkedadb > youkedadb.sql
即可把整个 youkedadb 数据库中的数据导出。然后执行ls
11.恢复数据:
首先进入项目:
cd xxxx
然后执行:
mysql -h 192.168.0.1 -uroot -Dyoukedadb<youkedadb.sql
- 192.168.0.1 是优课达平台 IP,固定的不用改
- -uroot 表示使用 root 用户
- -Dyoukedadb 表示操作的是第一章创建好的 youkedadb 库
四、基础SQL语句
1.增删查改语句(CRUD)
CRUD指:创建(Create),读取(Read),更新(Update),删除(Delete)。
在SQL中,我们有对应的专业术语:
英文 | 中文 | SQL | HTTP |
---|---|---|---|
CREATE | 创建 | INSERT(插入) | POST |
READ | 读取 | SELECT(查询) | GET |
UPDATE | 更新 | UPDATE | POST |
UPDATE | 删除 | UPDATE | UPDATE |
1.1创建表格
在创建表格时我们提供以下属性:
- 表名
- 字段名
- 字段的数据类型
创建过程如下:
此表格包含用户id,昵称,手机号三个字段,同时需要记录创建时间和修改时间。
/*创建一个叫做user的表格*/
CREATE TABLE `user`(
/*创建字段:字段名+数据类型+长度+是否为NULL*/
`id` INT(10)NOT NULL,
`mobile` VARCHAR(11) NOT NULL,
`nickname` VARCHAR(40) NOT NULL,
`gmt_created` datetime ,
`gmt_modified` datetime NOT NULL,
/*约定主键*/
PRIMARY KEY ( `id` )
/*设置储存引擎和编码方式*/
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
主键拥有以下特点:
- 主键必须是已经约定的字段
- 主键不能为空
- 主键的值不能重复
- 逐渐的最大作用是标识,所以它最好由计算机生成,人工不干预主键生成后的值。
因此,有时我们会把INT类型的主键语句改为:
`id` INT UNSIGNED AUTO_INCREMENT
表示id会从1开始自增,第二个为2,第三个为3,以此类推。
1.2 删除表格
删除表格语法如下:
drop table table_name;
有时候也会这么写:
DROP TABLE IF EXISTS table_name;
table_name是指你要删除的表格名,IF EXISTS的意思是如果存在。因为删除表格是不可逆的,所以删除表格需要慎重。
1.3 插入语句(INSERT)
语法如下:
INSERT INTO table_name(field1,field2,...fieldN)
VALUES
(value1,value2,...valueN);
表明我们向指定表插入若干个字段,和它们对应的值。仍以user表为例,具体写法如下:
INSERT INTO
`user` (`id`, `mobile`, `nickname`, `gmt_created`)
VALUES
(1, '13426069530', '王帅', now());
- user是表名
- id,mobile等是字段名
- mobile的值是varchar,要用’ ’包含
- gmt_created是datetime类型,可用now()函数直接插入当前时间
插入语句的简化
- 如果主键设置为自增,则可以不插入对应数据
- 如果插入的是所有字段,可以省略字段名,但是类型必须全部一致,若非空值没有插入数据则会报错
INSERT INTO table_name
VALUES
(value1,value2,...valueN);
两种情况不可以同时简化。
批量插入数据
INSERT INTO table_name
VALUES
(value1,value2,...valueN),
(value1,value2,...valueN);
1.4 查询语句(SELECT)
语法如下:
SELECT field1,field2,.... FROM table_name;
表示我们要从指定表中查询指定的列信息。
以user表为例,代码如下:
SELECT
id,
mobile
FROM
user;
如果要查询所有字段,可以这么写:
SELECT * FROM timi_adc;
WHERE子句
当我们希望查询符合某种条件的子句时,就需要使用到限定语句。我们可以使用WHERE语句来限定条件,类似于程序语言中的if语句。
语法如下:
SELECT * FROM table_name WHERE condition;
condition是指条件,它和if语句一样,可以做简单的逻辑判断,我们查询到的数据时true。
Limit子句
通过Limit子句可以指定我们返回的行。语法如下:
SELECT * FROM table_name LIMIT parameter;
比如我想查询user表的第6-11行,我可以按照如下写法完成:
SELECT
*
FROM
user
LIMIT
5, 6;
第一个参数表示从第六行开始查,第二个参数表示一共查询6条记录。
Limit语句一般是配合分页使用的,比如我们在购物软件上,每一页都是由固定的商品数的。
如果我想查询前五行,我可以按照如下写法完成:
SELECT
*
FROM
user
LIMIT
5;
如果我想查询第五行,我可以按照如下写法完成:
SELECT
*
FROM
user
LIMIT
4, 1;
ORDER BY子句
我们通过ORDER BY子句实现对查询结果的排序。语法如下:
SELECT * FROM table_name ORDER BY field_name;
DESC关键字
ORDER BY语句的默认排序是正序排序,关键字为ASC(一般不写)。我们可以加上DESC关键字是排序变为逆序:
SELECT
*
FROM
user
ORDER BY
id DESC;
1.5更新/删除
更新语句UPDATE
语法如下:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE语句我们必须加入WHERE限定条件,否则的话UPDATE语句就会对整列起作用。
删除语句DELETE
语法如下:
DELETE FROM table_name [WHERE Clause]
由于删除语句是不可恢复的,所以我们务必要增加WHERE语句,否则将会删除整张表格的数据
我们来看一下DELETE语句的几种应用场景:
删除user表中id为4的行:
delete from user where id=4;
删除user表中所有id小于20的数据:
delete from `user` where id<20;
删除user表中的所有数据:
delete from user;
DELETE 语句只删除表中的数据,如果要删除表格,我们用之前学过的 DROP TABLE + 表名的语句
2.LIKE查询
之前学习的语句都是精准查询,而目前我们通过搜索引擎使用的都是模糊查询,LIKE子句可以达到类似模糊查询的效果。
语法如下:
SELECT * FROM table_name WHERE condition LIKE condition;
“%”
SQL LIKE 子句中,我们的使用百分号 %字符来表示任意字符,如果我们没有使用任何的%,那此时LIKE就相当于=
如我们想查询user表中名字带有“孙”字的人:
SELECT
*
FROM
user
WHERE
name LIKE '%孙%';
%位置不同会决定查询的结果不同。%孙%
表示这个字符串含孙,孙%
表示这个字符串以孙开头,%孙
表示这个字符串以孙结尾。
”_“
如果我们想要查询孙尚香的基础信息,可是我们忘了他姓啥了,就记得他叫X尚香,那么我们的语句可以这么写:
SELECT
*
FROM
user
WHERE
name LIKE '_尚香';
_和%的区别在于,假设有一个人叫做公孙尚香,那么我们使用此方法便查不到他。
3.AND&OR
AND&OR配合WHERE可以实现多条件查询。语法如下:
SELECT * FROM table_name WHERE conditionA AND/OR condtionB;
同时我们需要添加括号来完成判定的先后顺序,比如:
/*下列查询结果一定有公孙王帅的信息*/
SELECT
*
FROM
user
WHERE
(
name = '孙尚香'
or
name = '公孙离'
)
AND name = '公孙王帅'
/*下列查询结果不一定有公孙王帅的信息*/
SELECT
*
FROM
user
WHERE
name = '孙尚香'
or(
name = '公孙离'
AND
name = '公孙王帅')
4.IN/NOT IN
IN子句可以配合WHERE子句完成精准查询。语法如下:
SELECT * FROM table_name WHERE column IN (condtionA,condtionB);
即使我要查找的元素只有一个,括号也不能省略。
比如:
SELECT
*
FROM
user
WHERE
name IN ('孙尚香', '公孙离');
这句话等价于:
SELECT
*
FROM
user
WHERE
name = '孙尚香'
or
name = '公孙离'
NOT IN/NOT LIKE
如果我们想要查询孙尚香以外的所有人,我们可以这么写:
SELECT
*
FROM
user
WHERE
name NOT IN ('孙尚香');
NOT LIKE使用方法跟NOT IN 类似,比如我们要查询名字里不带孙的人:
SELECT
*
FROM
user
WHERE
hero_name NOT LIKE '%孙%';
5.NULL值的处理
null不等于空值,它占用了一定的存储空间。
MySQL为处理NULL值提供了三种运算符:
- IS NULL:当列的值为NULL时,运算符返回true。
- IS NOT NULL:当列的值不为NULL时,运算符返回true。
- <=>:当两个值都为NULL或者相等时,返回true。
语法如下:
SELECT field_name1,field_name2
FROM table_name
WHERE field_name2 IS NOT NULL/IS NULL;
6.CONCAT函数
我们通过CONCAT函数对字符串进行处理。语法如下:
SELECT column_name1,CONCAT(column_name2,str,column_name3),
column_name4 FROM table_name;
我们来分析一下这个语法:
- 首先这是一个查询语句,最基本的结构是 SELECT 列名 FROM 表名
- CONCAT函数可以拼接列名,也可以拼接字符串
- 在使用CONCAT函数的时候可以同时查询其他的列
- CONCAT函数的参数之间用英文
,
分隔
如我们想要查询id为1的人的名字时,我们可以用如下写法:
SELECT
concat( id, '号的名字是', name)
FROM
user;
这样可以使阅读性更好。
需要注意的是,如果拼接值中有NULL,则结果一律为NULL。
别名
我们可以优化拼接的结果。通过以下方式可以给查询结果起一个新的列名:
我想让我的查询结果列名为 “姓名”。
SELECT
concat( id, '号的名字是', name) as 姓名
FROM
user;
7.TRIM函数
数据库记录的是输入的数据,而用户输入数据往往不是我们预期的数据,我梦可以通过TRIM函数来清理数据:
TRIM (str)
TRIM()函数的语法非常简单,就是把需要去除空格的数据放在TRIM()函数的空格里面。比如我们的数据为:‘ 孙尚香 ’
我们通过如下方式便可以在显示时清楚空格:
SELECT
trim(name)
FROM
user
WHERE
name = '孙尚香';
trim()函数可以去掉查询结果中的空格,但是不会修改原数据,修改原数据需要配合UPDATE/DELETE语句使用
语法拓展
trim()函数也可以精准的去掉前面或者后面的空格,或者其他的字符,语法如下:
TRIM( BOTH|LEADING|TRAILING removed_str FROM str);
- TRIM函数可以加上LEADING来只除去前面的空格,或者加上TRAILING来只除去后面的空格,如果都不加,则默认是BOTH。
- TRIM函数可以删除指定的字符串内容,如果不加,则默认删除空格。
注意,如果加上前缀,即使我们只想要清楚空格,也需要加上FROM关键字。
五、多表查询
1. 左连接
语法如下:
SELECT
*
FROM
TableA LEFT JOIN
TableB
ON condition;
- JOIN 是关联查询的关键词,基础的结构是 TableA JOIN TableB,即表 A 和表 B 关联查询,LEFT 表示是左连接
- ON 是关联查询的条件,接下来我们会讲到
- 左连接就是返回左表的所有数据,即使右表没有匹配的数据(此时右表会以 NULL 的形式匹配数据)。
假设有如下三张表格:ykd_student,ykd_course,ykd_teacher:
在实际的应用中,学生表之外还有班级表和年级表,大学还会有专业表,这里为了简化,我们假设的场景是门派式的,一个长老(老师)负责教一门课,学生之间都是内门弟子,没有多余的概念(其实是为了偷懒哈哈)。
id | name | birthday | course_id | gender | gmt_created | gmt_modified |
---|---|---|---|---|---|---|
1 | 孙悟空 | 公元前 578 年六月初一 | 1 | 男 | NULL | NULL |
2 | 刘备 | 公元 161 年 7 月 16 日 | 3 | 男 | NULL | NULL |
3 | 妲己 | 公元前 1076 年七月初三 | 4 | 女 | NULL | NULL |
4 | 鲁班七号 | 公元 2016 年 11 月 26 日 | 2 | 男 | NULL | NULL |
id | name | teacher_id | gmt_created | gmt_modified |
---|---|---|---|---|
1 | 如何击杀脆皮 | 1 | NULL | NULL |
2 | 极限一换一 | 2 | NULL | NULL |
3 | 蹲草的艺术 | 3 | NULL | NULL |
4 | 瑶和明世隐谁才是辅助之王 | 4 | NULL | NULL |
id | name | gmt_created | gmt_modified |
---|---|---|---|
1 | 不知火舞 | NULL | NULL |
2 | 兰陵王 | NULL | NULL |
3 | 王昭君 | NULL | NULL |
4 | 蔡文姬 | NULL | NULL |
现在我们想要查询老师们分别上什么课(不管老师有没有安排课都要显示老师的信息),那么我们需要关联查询 ykd_teacher 和 ykd_course 两张表,假设我们以 ykd_teacher 为左表,左连接查询,关联条件为 ykd_teacher 表的主键和 ykd_course 表中的 teacher_id 字段相等,那么我们应该这么写 SQL:
SELECT
*
FROM
ykd_teacher
LEFT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;
对比上面的语法结构:
- ykd_teacher 是表 A,ykd_course 是表 B
- ykd_teacher.id 是指这张表的 id 字段,同理 ykd_course.teacher_id 是指这张表的 teacher_id 这个字段
- 我们的查询条件,让上面 2 个字段相等并匹配
细心的同学可以发现,ykd_course 表下面的 teacher_id 这个字段本身的含义就是 ykd_teacher 表中的 id 字段
表结构设计规范
互联网公司在使用 MySQL 在设计表结构时,需要遵从以下的规范:
- 表必须要有主键。
- 一个字段只表示一个含义。
- 总是包含两个日期字段:gmt_created(创建日期),gmt_modified(修改日期),且这两个字段不应该包含有额外的业务逻辑。
- MySQL 中,gmt_created、gmt_modified 使用 DATETIME 类型。
- 禁止使用复杂数据类型(数组,自定义类型等)。
- 禁止使用物理外键,使用逻辑外键
- 禁止物理删除,使用逻辑删除 is_deleted
逻辑删除(英语:logical deletion),又被称软删除、假删除,是一种数据库操作,使用标记将数据标为不可用,而不从数据库删除数据本身。使用适当的方法可恢复被删除的数据。在商业公司的实际开发中会使用,本课程暂不涉及。
2.右连接
右连接就是返回右表的所有数据,即使左表没有匹配的数据。语法如下
SELECT
*
FROM
TableA RIGHT JOIN
TableB
ON condition;
参考上一节的例子,现在我们想用右连接查询课程对应的老师(不管课程有没有对应的老师,都要展示课程信息),那么我们应该这么写SQL:
SELECT
*
FROM
ykd_teacher
RIGHT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;
我们可以看到,因为每一个id都有匹配的数据,所以右连接查询得到的数据和左连接是一样的。
在实际的应用中,完全匹配的情况比较少,所以左右连接查到的数据大部分是不一样的。
多表关联查询
在实际应用中,我们经常会进行三张表以上的连接。这种情况下,我们往往会选出来一张主表,以它为基准进行左右连接查询。语法如下:
SELECT
*
FROM
TableA
LEFT JOIN TableB ON conditionA
LEFT JOIN TableC ON conditionB;
比如我们对ykd_student,ykd_course,ykd_teacher这三张表进行关联查询,我们的应该这么写SQL:
SELECT
*
FROM
ykd_course
LEFT JOIN ykd_student ON ykd_student.course_id = ykd_course.id
LEFT JOIN ykd_teacher on ykd_course.teacher_id = ykd_teacher.id;
ABC多表查询的本质是A先和B关联查询,然后再和C进行关联查询,更多表格的场景中,原理也是一致的。
3.内连接
在实际应用中,做右连接不能满足所有的查询需要,这时候我们就需要用内连接。
假设现在有两张表,A是某班级表,B是参加考试表,如果我们需要查询A中参加B考试的学生(A、B同时符合某个条件),得到的数据是内部共有的数据,所以连接方式是内连接(INNER JOIN):
查询语句如下:
SELECT
*
FROM
Table_A
INNER JOIN Table_B
ON
Table_A.id = Table_B.student_id;
这里INNER可以省略,只写JOIN,效果是一样的,LEFT JOIN 和 RIGHT JOIN都属于外连接,和内连接对应。
我们可以看到,表格A中,学生id为1,2的同学参与了B考试,和查询结果符合
外连接的拓展
在前面两节我们学习了外连接,最后查询得到的数据是这样的:
或者是这样的:
在实际的应用中,有时候我们希望查询A中和B完全没有关系的数据,比如我们希望查询班级A中没有参与B考试的人员,类似这样:
结合上面的两张表,我们可以这么写查询语句:
SELECT
*
FROM
Table_A
LEFT JOIN Table_B ON Table_A.id = Table_B.student_id
WHERE
Table_B.student_id IS NULL;
我们可以看到,查询得到的结果不包含B表拥有的student_id。
UNION 关键字
在实际的应用场景中,我们有时候需要联合查询所有的内容,就像下图这样:
比如我们现在想要查询A表中所有的学生和B表中所有的学生,不管他们是否参与了考试,有没有成绩,那么我们应该这样写查询语句:
SELECT
*
FROM
Table_A
LEFT JOIN Table_B
ON
Table_A.id = Table_B.student_id
UNION DISTINCT
SELECT
*
FROM
Table_A
RIGHT JOIN Table_B ON Table_A.id=Table_B.student_id;
这句话相当于同时执行AB表的左右连接。
UNION关键字可以将两个查询语句的结果合并,并去除重复数据,UNION DISTINCT 和 UNION 的效果一样,所以我们一般直接使用UNION。
一般来说我们要求查询得到的数据是去重的,在极少数情况下我们需要展示不去重的数据,我们可以把DISTINCT关键词换为ALL关键词。