一、MySQL数据库概述
1.1 基本概念
(1)数据库是什么?
- 存储数据的地方
- DB:数据库(Database)
(2)为什么使用数据库?
- 因为应用程序产生的数据是在内存中的,如果程序退出或者是断电了,则数据就会消失,使用数据库是为了能够永久保存数据,当然这里指的是非内存数据库。
(3)用普通文件如Excel行不行?
- 把数据写入到硬盘上的文件中,当然可以实现持久化的目标,但是当数据量非常大,并且拥有多种不同的数据格式时普通文件不利于后期的检索和管理,也无法实现数据多人共享。
(4)MySQL、Oracle、SqlServer是什么?
- 这都是数据库管理系统(DBMS,Database Management System),是一种操作和管理数据库的大型软件。
(5)SQL是什么?
- SQL是结构化查询语言(Structure Query Language),专门用来操作/访问关系型数据库的通用语言
1.2 MySQL数据库
(1)什么是关系型数据库和非关系型数据库
- MySQL、Oracle、SqlServer等是关系型数据库管理系统
- MongoDB、Redis、Elasticsearch等是非关系型数据库管理系统
- 关系型数据库采用关系模型来组织书数据,简单来说,关系模型就是指二维表格模型,类似于Excel工作表,非关系型数据库可以看成传统关系型数据库的功能阉割版本,基于键值对存储数据,通过减少很少用的功能来提高性能。
(2)MySQL的优点:
- 可移植性:MySQL数据库几乎支持所有的操作系统,如Linux、Solaris、FreeBSD、Mac和Windows
- 免费:MySQL的社区版完全免费,一般中小型网站的开发都选择MySQL作为网站数据库
- 开源:2000年MySQL公布了自己的源代码,并采用了GPL许可协议,正式进入开源的世界,开源意味着可以让更多人审阅和贡献源代码,可以吸纳更多优秀人才的代码结果。
- 关系型数据库:MySQL可以利用标准SQL语法进行查询和操作
-
速度快、体积小、容易使用:与其他大型数据库的设置和管理相比,其复杂程度较低,易于学习。MySQL的早期版本(主要使用的是 MyISAM 引擎)在高并发下显得有些力不从心,随着版本的升级优化(主要使用的是InnoDB 引擎),在实践中也证明了高压力下的可用性。从 2009 年开始,阿里的“ 去 IOE” 备受关注,淘宝 DBA 团队再次从 Oracle 转向 MySQL ,其他使用 MySQL 数据库的公司还有Facebook、 Twitter 、 YouTube 、百度、腾讯、去哪儿、魅族等等,自此, MySQL 在市场上占据了很大的份额。
-
安全性和连接性:十分灵活和安全的权限和密码系统,允许基于主机的验证。连接到服务器时,所有的密码传输均采用加密形式,从而保证了密码安全。由于MySQL 是网络化的,因此可以在因特网上的任何地方访问,提高数据共享的效率。
-
丰富的接口:提供了用于C 、 C++ 、 Java 、 PHP 、 Python 、 Ruby 和 Eiffel 、 Perl 等语言的 API 。 灵活: MySQL 并不完美,但是却足够灵活,能够适应高要求的环境。同时, MySQL 既可以嵌入到 应用程序中,也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统、在线事务处理系统 等各种应用类型。
二、SQL语言
2.1 SQL的分类
- DDL语句:数据定义语句(Data Define Language),用来定义数据库对象
- DML语句:数据操纵语句,例如:增(insert),删(delete),改(update)
- DQL语句:数据查询语句,查询语句使用的非常的频繁,用来查询数据库中表的记录
- DCL语句:数据控制语句,例如:grant,commit,rollback等,用来创建数据库用户、控制数据库的访问权限
- DPL语句:事务处理语句
- CCL语句:指针控制语句
- 其他语句:USE语句,SHOW语句,SET语句等。这类的官方文档中一般称为命令。
2.2 SQL通用语法
- mysql的sql语法不区分大小写,关键字建议使用大写
- 建议不要使用mysql的关键字等来作为表名、字段名、数据库名等,如果不小心使用,请在SQL语句中使用`(飘号)引起来
- 命名时:尽量使用26个英文字母大小写,数字0-9,下划线,不要使用其他符号
- 数据库和表名、字段名等对象名中间不要包含空格
- SQL语句可以使用空格/缩进来增强语句的可读性。
- 同一个mysql软件中,数据库不能同名,同一个库中,表不能重名,同一个表中,字段不能重名
2.3 SQL脚本中如何加注释
- 单行注释:#注释内容(mysql特有的)
- 单行注释:--空格注释内容 其中--后面的空格必须有
- 多行注释:/* 注释内容 */
三、DDL语句
3.1 查询
(1)查询所有数据库:SHOW DATABASES;
(2)查询当前数据库:SHOW DATABASE();
(3)查询当前数据库所有表:SHOW TABLES;
(4)查询表结构:DESC 表名;
(5)查询指定表的建表语句:SHOW CREATE TABLE 表名;
3.2 创建
(1)创建数据库:CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHAREST 字符集] [COLLATE 排序规则];
(2)创建表:CREATE TABLE 表名(字段1 字段1类型 [COMMENT 字段1注释])[COMMENT 表注释];
数据类型:
分类 | 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
数值类型 | TINYINT | 1 byte | (-128,127) | (0,255) | tiny整数值 |
SMALLINT | 2 bytes | (-32768,32767) | (0,65535) | small整数值 | |
MEDIUMINT | 3 bytes | (-8388608,8388607) | (0,16777215) | medium整数值 | |
INT或INTEGER | 4 bytes | (-2147483648,2147483647) | (0,4294967295) | 整数值 | |
BIGINT | 8 bites | (-2^63,2^63-1) | (0,2^64-1) | big整数值 | |
FLOAT | 4 bites | (-3.4022823466 E+38,3.402823466351 E+38) | 0和(1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 | |
DOUBLE | 8 bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0和(2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 |
分类 | 类型 | 大小 | 描述 |
字符串类型 | CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 | |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 | |
TINYTEXT | 0-255 bytes | 短文本字符串 | |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 | |
TEXT | 0-65 535 bytes | 长文本数据 | |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 | |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 | |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 | |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
分类 | 类型 | 大小 | 范围 | 格式 | 描述 |
日期类型 | DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 | |
YEAR | 1 | 1901 至 2155 | YYYY | 年份值 | |
TATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 | |
TIMESTAMP | 4 | 1970-01 01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
3.3 修改与删除
(1)删除数据库:DROP DATABASE [IF EXISTS] 数据库名;
(2)表中添加字段:ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
(3)表中修改数据类型:ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
(4)表中修改字段名和字段类型:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
(5)表中删除字段:ALTER TABLE 表名 DROP 字段名;
(6)修改表名:ALTER TABLE 表名 RENAME TO 新表名;
(7)删除表:DROP TABLE [IF EXISTS] 表名;
(8)删除指定表,并重新创建该表(表中数据会被删除):TRUNCATE TABLE 表名;
3.4 使用
(1)使用数据库:USE 数据库名;
四、DML语句
4.1 添加数据(INSERT)
(1)给指定字段添加数据:INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...)
(2)给全部字段添加数据:INSERT INTO 表名 VALUES (值1,值2,...)
(3)批量添加数据:INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...),(值1,值2,...)(值1,值2,...);
或者:INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...)(值1,值2,...);
注意:
- 插入数据时,指定字段顺序需要与值的顺序是一一对应的
- 字符串和日期类型数据应该包含在引号中
- 插入的数据大小,应该在字段的规定范围内
4.2 修改数据(UPDATE)
(1)修改数据:UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];
4.3 删除数据(DELETE)
(1)删除数据:DELETE FROM 表名 [WHERE 条件]
注意:
- DELETE语句的条件可以有也可以没有,如果没有条件,则会删除整张表的所有数据
- DELETE语句不能删除某一个字段的值(可以使用UPDATE)
五、DQL语句
查询关键字:SELECT
基础语法:SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
5.1 基本查询
(1)查询多个字段
SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;(查询所有字段)
(2)设置别名
SELECT 字段1 [AS 别名1],字段2[AS 别名2]... FROM 表名;
(3)去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
5.2 条件查询(WHERE)
SELECT 字段列表 FROM 表名 WHERE 条件列表;
- 条件可以使用比较运算符和逻辑运算符
- 比较运算符:>、>=、<、<=、=、<>或!=(不等于)、BETWEEN...AND...(在某个范围之内,含最小最大值)、IN(在in之后的列表中的值,多选一)、LIKE 占位符(模糊匹配,_匹配单个字符,%匹配任意个字符)、IS NULL(是NULL)
- 逻辑运算符:AND或&&(与)、OR或||(或)、NOT或!(非)
5.3 聚合函数(count统计数量、max最大值、min最小值、avg平均值、sum求和)
SELECT 聚合函数(字段列表) FROM 表名;
- NULL值不参与聚合函数的运算
5.4 分组查询(GROUP BY)
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]
注意:WHERE 和 HAVING 的区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件的不参与分组,而having是分组之后对结果进行过滤
- 判断条件不同:where不能对聚合函数进行判断,而having可以
5.5 排序查询(ORDER BY)
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
注意:排序方式
- ASC:升序(默认值)
- DESC:降序
5.6 分页查询(LIMIT)
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
- 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10
六、DCL语句
6.1 管理用户
(1)查询用户
USE MySQL;
SELECT * FROM user;
(2)创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
(3)修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
(4)删除用户
DROP USER '用户名'@'主机名';
- 主机名可以使用%通配
- 这类SQL语句开发人员操作的比较少,主要是DBA(数据库管理员)使用
6.2 权限控制
权限列表:
权限 | 说明 |
ALL,ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
(1)查询权限:SHOW GRANTS FOR '用户名'@'主机名';
(2)授予权限:GRANT 权限列表 ON 数据库名.表名 TO '用户名'@‘主机名’;
(3)撤销权限:REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
七、函数
7.1 字符串函数
函数 | 功能 |
CONCAT(S1,S2,...Sn) | 字符串拼接,将S1,S2,...Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回字符串str从start位置起的len个长度的字符串 |
7.2 数值函数
函数 | 功能 |
GEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
7.3 日期函数
函数 | 功能 |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
7.4 流程函数
函数 | 功能 |
IF(value,t,f) | 如果value为true,返回t,否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1] ... ELSE [default] END | 如果val1为true,返回res1,...否则返回default默认值 |
CASE [expr] WHEN [val1] THEN [res1] ...ELSE [default] END | 如果expr的值等于val1,返回res1,...否则返回default默认值 |
八、约束
概述:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
8.1 约束分类:
约束 | 描述 | 关键字 |
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一的、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束 | 保证字段满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
案例:
建表需求结构
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
id | ID唯一标识 | int | 主键,并且自动增长 | PRIMARY KEY, AUTO_INCREMENT |
name | 姓名 | varchar(10) | 不为空,并且唯一 | NOT NULL,UNIQUE |
age | 年龄 | int | 大于0,并且小于等于120 | CHECK |
status | 状态 | char(1) | 如果没有指定该值,默认为1 | DEFAULT |
gender | 性别 | char(1) | 无 |
代码
8.2 外键约束
(1)添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
(2)删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
(3)删除/更新行为:
行为 | 说明 |
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许去null)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(lnnodb不支持) |
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名)ON UPDATE [CASCADE] ON DELETE [CASCADE]
九、多表查询
概述:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合 A集合和B集合的所有组合情况(在多表查询中,需要消除无效的笛卡尔积) select * from [emp,dept] where [emp.dept_id = dept.id] (加上where后面的条件保证各行数据是一一对应的)
多表查询分类:内连接、外连接、自连接(三个连接查询)、子查询
9.1 多表关系
(1)一对多 实现:在多的一方建立外键,指向一的一方主键
(2)多对多 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方的主键
(3)一对一 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
9.2 内连接
相当于查询A、B交集部分数据
语法:
隐式内连接:SELECT 字段列表 FROM 表1,表2 WHERE 条件 ... ;
显式内连接:SELECT 字段列表 FROM 表1[INNER] JOIN 表2 ON 连接条件 ... ;
9.3 外连接
外连接分为左外连接(查询左表所有数据包含两张表交集部分数据)和右外连接(查询右表所有数据包含两张表交集部分数据)
语法:
左外连接:SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ... ;
右外连接:SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ... ;
9.4 自连接
当前表与自身的链接查询,自连接必须使用别名,自连接查询可以是内连接查询,也可以是外连接查询。
语法:SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
9.5 子查询
概述:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
语法:SELECT * FROM 表1 WHERE column1 = (SELECT column1 FROM 表2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT 的任何一个
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值),常用操作符:=、<>、>、>=、<、<=
- 列子查询(子查询结果为一列),常用操作符:IN、NOT IN、ANY、SOME、ALL
- 行子查询(子查询结果为一行),常用操作符:=、<>、IN、NOT IN
- 表子查询(子查询结果为多行多列),常用操作符:IN
9.6 联合查询 UNION,UNION ALL
概述:对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
语法:SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ... ;
- 对于联合查询多张表的列数必须一致,字段类型也必须保持一致
- UNION ALL 会将全部的数据直接合并在一起,UNION会对合并之后的数据去重
十、事务
概述:事务是一组操作的集合,它是不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。SQL语句每一行命令执行时都会自动提交事务。
10.1 事务操作
(1)查看事务提交方式:SELECT @autocommit;
(2)设置事务提交方式:SET @autocommit = 0;(设置为手动提交,默认值为1自动提交)
(3)提交事务:COMMIT;
(4)回滚事务:ROLLBACK;
(5)开启事务:START TRANSACTION 或 BEGIN
10.2 事务四大特性
- 原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性:事务完成时,必须使所有数据都保持一致状态
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部操作影响的独立环境下运行
- 持久性:事务一旦提交或回滚,它对数据中的数据的改变就是永久的
10.3 并发事务问题
- 脏读:一个事务读到另外一个事务还没有提交的数据。
- 不可重读读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复度。
- 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻读”。
10.4 事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
事务隔离级别从上到下越来越高,数据安全性越高,但是性能逐渐降低。
(1)查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
(2)设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}