一MySQL概述数据库(DataBase DB): 存储数据的仓库数据是有组织的进行存储数据库管理系统(DataBase Management Sysstem DBMS): 操纵和管理数据库的大型软件SQL(Structured Query Language): 操作关系型数据库的编程语言定义了一套操作关系型数据库统一标准主流的关系型数据库管理系统OracleMySQLSQL ServerPostgreSQL ...关系型数据库RDBMS): 建立在关系模型基础上由多张相互连接的二维表组成的数据库。特点使用表存储数据格式统一便于维护使用SQL语言操作标准统一使用方便二SQL2.1 SQL通用语法SQL语句可以单行或多行书写以分号结尾。SQL语句可以使用空格/缩进来增强语句的可读性。MySQL数据库的SQL语句不区分大小写关键字建议使用大写。注释单行注释-- 注释内容 或 #注释内容(MySQL特有)多行注释/*注释内容*/2.2 SQL分类DDL(Data Definition Language): 数据定义语言用来定义数据库对象数据库表字段DML(Data Manipulation Language): 数据操作语言用来对数据库表中的数据进行增删改DQL(Data Query Language): 数据查询语言用来查询数据库中表的记录DCL(Data Control Language): 数据控制语言用来创建数据库用户、控制数据库的访问权限2.2.1 DDL1. 数据库操作(1) 查询查面所有数据库SHOW DATABASES;查询当前数据库SELECT DATABASE;(2) 创建CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则(3) 删除DROP DATABASE [IF EXISTS]数据库名(4) 使用USE 数据库名2. 表操作(1) 查询查询当前数据库所有表SHOW TABLES;查询表结构DESC 表名查询指定表的建表语句SHOW CREATE TABLE 表名(2) 创建CREATE TABLE 表名字段1 字段1类型[COMMENM 字段1注释],字段2 字股2类型[COMMENT 字段2注释],......字段n 字段n类型[COMMENT 字段n注释]) [COMMENT 表注释]注意[...]为可选参数最后一个字段后面没有逗号(3) 修改添加字段ALTER TABLE 表名 ADD 字段名 类型长度[COMMENT 注释] [约束];修改数据类型ALTER TABLE 表名 MODIFY 字段名 新数据类型长度修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [COMMENI 注释][约束];修改表名ALTER TABLE 表名 RENAME TO 新表名;删除字段ALTER TABLE 表名 DROP 字段名删除表DROP TABLE[IF EXISTS] 表名;删除指定表并重新创建该表TRUNCATE TABLE 表名3. 数据类型1整数类型TINYINT1字节小整数范围-128 ~ 127有符号或 0 ~ 255无符号SMALLINT2字节大整数范围-32,768 ~ 32,767有符号或 0 ~ 65,535无符号MEDIUMINT3字节大整数范围-8,388,608 ~ 8,388,607有符号或 0 ~ 16,777,215无符号INT或INTEGER4字节大整数范围-2,147,483,648 ~ 2,147,483,647有符号或 0 ~ 4,294,967,295无符号BIGINT8字节极大整数范围-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807有符号或 0 ~ 18,446,744,073,709,551,615无符号2浮点数类型FLOAT4字节单精度浮点数范围-3.402823466 E38 ~ 3.402823466351 E38有符号或 0 和 1.175494351 E-38 ~ 3.402823466 E38近似值DOUBLE8字节双精度浮点数范围-1.7976931348623157 E308 ~ 1.7976931348623157 E308 或 0 和 2.2250738585072014 E-308 ~ 1.7976931348623157 E308近似值DECIMAL(M, D)精确值M 是总位数D 是小数位数。例如DECIMAL(5, 2)可以存储 123.453字符串类型CHAR(N)固定长度字符串最多 255 个字符VARCHAR(N)可变长度字符串最多 65,535 个字符TINYBLOB不超过 255 个字符的二进制数据TINYTEXT短文本字符串最多 255 个字符BLOB二进制形式的长文本数据最多 65,535 个字符TEXT长文本数据最多65,535个字符MEDIUMBLOB二进制形式的中等长度文本数据最多 16,777,215 个字符MEDIUMTEXT中等长度文本数据最多 16,777,215 个字符LONGBLOB二进制形式的极大文本数据最多 4,294,967,295 个字符LONGTEXT极大文本数据最多 4,294,967,295 个字符4日期时间类型DATE日期值3字节格式为YYYY-MM-DD范围是1000-01-01 至 9999-12-31TIME时间值或持续时间3字节格式为HH:MM:SS范围是-838:59:59 至 838:59:59YEAR年份值1字节格式为YYYY范围是1901 至 2155DATETIME混合日期和时间值8字节格式为YYYY-MM-DD HH:MM:SS范围是1000-01-01 00:00:00 至 9999-12-31 23:59:59TIMESTAMP混合日期和时间值时间戳4字节格式为YYYY-MM-DD HH:MM:SS范围是1970-01-01 00:00:01 至 2038-01-19 03:14:072.2.2 DML1. 添加数据给指定字段添加数据INSERT INTO 表名 字段名1, 字段名2, ..) VALUES 值1, 值2, ..)给全部字段添加数据INSERT INTO 表名 VALUES 值1, 值2, ..)批量添加数据INSERT INTO 表名字段名1, 字段名2. ..) VALUES 值1, 值2, .),值1, 值2. ..),值1, 值2. ..);INSERT INTO 表名 VALUES值1值2, .…),值1, 值2, ..),值1, 值2...)注意插入数据时指定的字段顺序需要与值的顺序是一一对应的。字符串和日期型数据应该包含在引号中。插入的数据大小应该在字段的规定范围内。2. 修改数据UPDATE 表名 SET 字段名1值1 字段名2值2....[WHERE 条件]注意修改语句的条件可以有也可以没有如果没有条件则会修改整张表的所有数据。3. 删除数据DELETE FROM 表名 [WHERE 条件]注意DELETE语句的条件可以有也可以没有如果没有条件则会修改整张表的所有数据。DELETE语句不能删除某一个字段的值可以使用UPDATE2.2.3 DQL主要用于查询语法SELECT字段列表FROM表名列表WHERE条件列表GROUP BY分组字段列表HAVING分组后条件列表ORDED BY排序字段列表LIMIT分页参数1. 基本查询查询多个字段SELECT 字段1, 字段2, 字段3... FROM 表名SELECT * FROM 表名设置别名SELECT 字段1 [AS 别名1]字段2 [AS 别名2].... FROM 表名去除重复记录SELECT DISTINCT 字段列表 FROM 表名2. 条件查询语法SELECT 字段列表 FROM 表名 WHERE 条件列表;条件 大于大于等于小于小于等于等于 或 !不等于BETWEEN ... AND ...在某个范围之间含最小最大值IN(...)在in之后的列表中的值多选一LIKE 占位符模糊匹配_匹配单个字符%匹配任意字符IS NULL是NULLAND 或 并且OR 或 ||或者NOT 或 !不是3. 聚合函数(1) 介绍将一列数据作为一个整体进行纵向计算。(2) 常见的聚合函数count统计数量max最大值min最小值avg平均值sum求和(3) 语法SELECT 聚合函数字段列表FROM 表名4. 分组查询(1) 语法SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后的过滤条件];(2) WHERE和HAVING的区别执行时机不同执行时机不同WHERE是分组之前进行过滤不满足WHERE条件不参与分组而HAVING是分组之后对结果进行过滤判断条件不同WHERE不能对聚合函数进行判断而HAVING可以注执行顺序: where 聚合函数 having分组后查询的字段一般为聚合函数和分组字段查询其他字段与任何意义。5. 排序查询语法SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1字段2 排序方式2排序方式ASC升序排列默认值DESC降序排列如果是多字段排序当第一个字段值相同时才会根据第二个字段进行排序6. 分页查询语法SELECT 字段列表 FROM 表名 LIMIT 起始索引查询纪录数注意起始索引从0开始起始索引 查询页码 - 1* 每页显示记录数。分页查询是数据库的方言不同的数据库有不同的实现MySQL中是LIMIT。如果查询的是第一页数据起始索引可以省略直接简写为 limit 10。7. 执行顺序FROM ----- WHERE ----- GROUP BY ----- HAVING ----- SELECT ----- ORDER BY ----- LIMIT2.2.4 DCL1. 管理用户查询用户USE mysql;SELECT * FROM user;创建用户CREATE USER 用户名主机名 IDENTIFIED BY 密码修改用户密码ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 新密码;删除用户DROP USER 用户名主机名;主机名可以用%通配表示所有主机localhost代表当前主机2. 权限控制ALL,ALL PRIVILEGES所有权限SELECT查询数据INSERT插入数据UPDATE更新数据DELETE删除数据ALTER修改表DROP删除数据库/表/视图CREATE创建数据库/表查询权限SHOW GRANTS FOR 用户名主机名;授予权限GRANT 权限列表 ON 数据库名表名 TO 用户名主机名;撤销权限REVOKE 权限列表 ON 数据库名表名 FROM 用户名主机名;注意多个权限之间使用逗号分隔授权时数据库名和表名可以使用*进行通配代表所有。三函数函数指一段可以直接被另一段程序调用的程序或代码。3.1 字符串函数MySQL中内置了很多字符串函数常用的如下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从strat位置起的len个长度的字符串语法select 函数参数3.2 数值函数常见的数值函数CEIL(x)向上取整FIOOR(x)向下取整MOD(x,y)返回x/y的模RAND()返回0~1内的随机数ROUND(x,y)求参数x的四舍五入的值保留y位小数3.3 日期函数常见的日期函数CURDATE()返回当前日期CURTIME()返回当前时间NOW()返回当前日期和时间YEAR(date)获取指定date的年份MONTH(date)获取指定date的月份DAY(date)获取指定date的日期DATE_ADD(date,INTERAVL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间间隔DATEDIFF(date1,date2)返回起始时间date1 和结束时间date2之间的天数DATE_ADD(date,INTERVAL expr type)函数细节expr如果是正数表示从时间date向后推如果是负数就表示往前推如果是0就表示是date本身type常用的类型YEAR(年)、MONTH(月)、DAY(天)、HOUR(小时)、MINUTE(分钟)、SECOND(秒)3.4 流程函数IF(value, t, f)如果value为true则返回t否则返回fIFNULL(value1, value2)如果value1不为空则返回value1否则返回value2CASE WHEN [val1] THEN [res1] ... ELSE [default] END如果val1为true返回res1...否则返回default默认值CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END如果expr的值等于val1返回res1...否则返回default默认值四约束4.1 基础概念概念约束是作用于表中字段上的规则用于限制存储在表中的数据。目的是保证数据库中数据的正确、有效性和完整性分类非空约束限制该字段的数据不能为null NOT NULL关键字唯一约束保证该字段的所有数据都是唯一、不重复的 UNIQUE关键字主键约束主键是一行数据的唯一标识要求非空且唯一 PRIMARY KEY关键字默认约束保存数据时如果未指定该字段的值则采用默认值 DEFAULT关键字检查约束8.0.16版本后保证字段值满足某一个条件 CHECK关键字自动增长添加数据时如果没有为字段设置值会自动增长1个单位 AUTO_INCREMENT关键字外键约束用来让两张图的数据之间建立连接保证数据的一致性和完整性 FOREIGN KEY关键字注意约束是作用于表中字段上的可以在创建表/修改表的时候添加约束例create table user( id int primary key auto_increment comment 主键, name varchar(10) not null unique comment 姓名, age int check(age 0 age 120) comment 年龄, status char(1) default 1 comment 状态, gender char(1) comment 性别 );4.2 外键约束4.2.1 语法添加外键CREATE TABLE 表名( 字段名 字段类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) );ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);删除外键ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;4.2.2 删除/更新行为NO ACTION当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则不允许删除/更新与RESTRICT一致RESTRICT当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则不允许删除/更新与NO ACTION一致CASCADE当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则也删除/更新外键在子表中的记录SET NULL当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则设置子表中该外键值为null要求该外键允许为nullSET DEFAULT父表有变更时子表将外键设为一个默认值Innodb不支持语法ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;五多表查询5.1 多表关系概念项目开发中在进行数据库表结构设计时会根据业务需求及业务模块之间的关系分析并设计表结构由于业务之间相互关联所以各个表结构也存在着各类联系基本分为三种一对多多对一多对多一对一5.1.1一对多多对一案例部门与员工关系一个部门对应多个员工一个员工对应一个部门实现在多的一方建立外键指向一的一方的主键5.1.2 多对多案例学生与课程关系一个学生可以选多门课程一门课程也可以供多个学生选修实现建立第三张中间表中间表至少包含两个外键分别关联两方主5.1.3 一对一案例用户与用户详情关系一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率实现在任意一方加入外键关联另外一方的主键并且设置外键为唯一的UNIQUE5.2 多表查询概念指从多张表中查询数据笛卡尔积笛卡尔乘积是指在数学中两个集合 A集合和 B集合的所有组合情况。多表查询时需要消除无效的笛卡尔积假设A集合中有n个数据B集合中有m个数据那么笛卡尔积就等于nm。语法select * from 表1, 表2;一般笛卡尔积是没有意义的我们会加上WHERE条件筛选分类1. 连接查询(1)内连接相当于查询AB交集部分数据。(2)外连接左外连接查询左表所有数据及两张表交集部分数据。右外连接查询右表所有数据及两张表交集部分数据。(3)自连接当前表与自身的连接查询自连接必须使用表别名。2. 子查询5.3 内连接隐式内连接SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;显式内连接SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;如果已经为表起了别名执行顺序之后的语句只能通过表的别名访问表不能通过表名访问表隐式内连接和显式内连接仅仅是代码上的区别功能上完全一致5.4 外连接左外连接SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;相当于查询表1左表的所有数据包含表1和表2交集部分数据右外连接SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;左外连接可以查询到左边表的null字段右外连接可以查询到右边表的null字段5.5 自连接语法SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;自连接查询可以是内连接查询也可以是外连接查询5.6 联合查询 UNION,UNION ALL把多次查询的结果合并形成一个新的查询集语法SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ...UNION ALL 会有重复结果UNION 不会联合查询多张表的查询列要一致否则会出错联合查询比使用or效率高不会使索引失效5.7 子查询SQL语句中嵌套SELECT语句称谓嵌套查询又称子查询。语法SELECT * FROM t1 WHERE 列名 ( SELECT 列名 FROM t2);子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个5.7.1 子查询分类1. 根据子查询结果可以分为标量子查询子查询结果为单个值列子查询子查询结果为一列行子查询子查询结果为一行表子查询子查询结果为多行多列2. 根据子查询位置可分为WHERE 之后FROM 之后SELECT 之后5.7.2 标量子查询子查询返回的结果是单个值数字、字符串、日期等常用操作符 5.7.3 列子查询返回的结果是一列可以是多行常用操作符IN在指定的集合范围内多选一NOT IN不在指定的集合范围内ANY子查询返回列表中有任意一个满足即可SOME与ANY等同使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足5.7.4 行子查询返回的结果是一行可以是多列常用操作符 IN NOT IN5.7.5 表子查询返回的结果是多行多列常用操作符IN六事务事务是一组操作的集合事务会把所有操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。6.1 事务操作6.1.1查看/设置事务提交方式SELECT AUTOCOMMIT; SET AUTOCOMMIT 0;提交事务COMMIT;回滚事务ROLLBACK;6.1.2开始事务START TRANSACTION 或 BEGIN [TRANSACTION];提交事务COMMIT;回滚事务ROLLBACK;6.2 事务四大特性ACID原子性(Atomicity)事务是不可分割的最小操作单元要么全部成功要么全部失败一致性(Consistency)事务完成时必须使所有数据都保持一致状态隔离性(Isolation)数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行持久性(Durability)事务一旦提交或回滚它对数据库中的数据的改变就是永久的6.3 并发事务问题脏读一个事务读到另一个事务还没提交的数据不可重复读一个事务先后读取同一条记录但两次读取的数据不同幻读一个事务按照条件查询数据时没有对应的数据行但是再插入数据时又发现这行数据已经存在6.4 事务隔离级别隔离级别脏读不可重复读幻读Read uncommitted√√√Read committed×√√Repeatable Read(默认)××√Serializable×××查看事务隔离级别SELECT TRANSACTION_ISOLATION;设置事务隔离级别SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};SESSION 是会话级别表示只针对当前会话有效GLOBAL 表示对所有会话有效事务隔离级别越高数据安全性越安全但是性能越低Read uncommitted事务隔离级别最低Serializable事务隔离级别最高