MySQL 初级
MySQL 数据库
关系型数据库：
建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
SQL 语法
-
SQL 通用语法
-
SQL 语句可以单行或多行书写，以分号结尾
-
SQL语句可以使用空格/缩进来增强语句的可读性。
-
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
-
注释：
-
单行注释： -- 注释内容或 # 注释内容( MySQL 特有)
-
多行注释： /* 注释内容 */
-
-
-
SQL 分类
-
DDL: 数据定义语言，用来定义数据库对象(数据库，表，字段)。
-
数据库操作
-
查询
# 查询所有数据库 SHOW DATABASES; # 拆线呢当前数据库 SELECT DATABASE();
-
创建
# 判断是否存在 设置字符集 设置排序规则 CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; # 注意: # 字符集 UTF8 存储字节的长度是 3 个字节 # 字符集 UTF8MB4 存储长度占 4 个字节
-
删除
DROP DATABASES [IF EXISTS] 数据库名;
-
使用
USE 数据库名;
-
表操作
-
查询
# 查询当前数据库所有表 SHOW TABLES; # 查询表结构 DESC 表名; # 查询指定表的建表语句 SHOW CREATETABLE 表名;
-
创建
CREATE TABLE 表名 ( 字段1 字段1类型 [COMMENT 字段1注释], 字段2 字段2类型 [COMMENT 字段2注释], ······ 字段n 字段n类型 [COMMENT 字段n注释] # 最后字段没有分号 )[COMMENT 表注释]; -
数据类型
-
数值类型
类型 大小 有符号 ( SIGNED ) 范围 无符号 ( UNSIGNED ) 描述 TINYINT 1 byte ( 0128，127 ) ( 0，255) 小整数值 SMALLINT 2 bytes ( -32768，32767) ( 0，65535) 大整数值 MEDIUMINT 3 bytes ( -8388608，8388607) ( 0，16777215) 大整数值 INT 或 INTEGER 4 bytes ( -2147482648，2147482647) ( 0，4294967295) 大整数值 BIGINT 8 bytes (-2^63，2^64 - 1) ( 0，2^64 ) 极大整数值 FLOAT 4 bytes 单精度浮点数值 DOUBLE 8 bytes 双精度浮点数值 DECIMAL 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 小数值( 精确定点数 ) -
字符串类型
类型 大小 描述 CHAR 0 ~ 255 bytes 定长字符串：CHAR(10) 会固定占用10个字符，性能好 VARCHAR 0 ~ 65535 bytes 不定长字符串：VEARCHAR(10) 可变长字符串，性能较差 TINYBLOB 0 ~ 255 bytes 不超过 255 个字符的二进制数据 TINYTEXT 0 ~ 255 bytes 短文本字符串 BLOB 0 ~ 65535 bytes 二进制形式的长文本数据 TEXT 0 ~ 65535 bytes 长文本数据 MEDIUMBLOB 0 ~ 16777215 bytes 二进制形式的中等长文本数据 MEDIUMTEXT 0 ~ 16777215 bytes 中等长文本数据 LONGBLOB 0 ~ 4294967295 bytes 二进制形式的极大文本数据 LONGTEXT 0 ~ 4294967295 bytes 极大文本数据 注意：二进制数据如音频，视频，软件安装包等，开发中会很少这样做，原因性能不高，不方便管理，对于这类二进制数据，会采用专门的文件服务器进行存储
例如123.45精度为5，标度为2
age TINYINT UNSIGNED;
score double(4,1);4代表分数最高等级100.0,1代表小数位
-
日期格式
类型 大小 格式 描述 DATE 3 YYYY-MM-DD 日期值 TIME 3 HH : MM : SS 时间值或持续时间 YEAR 1 YYYY 年份值 DATETIME 8 YYYY-MM-SS HH : MM : SS 混合日期和时间值 TIMESTAMP 4 YYYY-MM-SS HH : MM : SS 混合日期和时间值，时间戳 实践：设计一张员工信息表
-
编号 ( 纯数字 )
-
员工工号 ( 字符串类型，长度不超过 10 位 )
-
员工姓名 ( 字符串类型，长度不超过 10 位 )
-
性别 ( 男 / 女，存储一个汉字 )
-
年龄 ( 正常人年龄，不可能存储负数 )
-
身份证号 ( 二代身份证好均为 18 位，身份证中有 X 这样的字符串 )
-
入职时间 ( 取值年月日即可 )
CREATE TABLE emp ( id INT COMMENT '编号', workno VARCHAR(10) COMMENT '员工工号', name VARCHAR(10) COMMENT '姓名', gender CHAR(1) COMMENT '性别', age TINYINT UNSIGNED COMMENT '年龄', id_card CHAR(18) COMMENT '身份证号' entry_date date COMMENT '入职时间' ) COMMENT '员工表'; -
-
修改
# 添加字段 ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]; # 修改数据类型 ALTER TABLE 表名 MODIFFY 字段名 新数据类型(长度) [FIRST/AFTER] [字段名]; # 修改数据设置默认值 ALTER TABLE 表名 ALTER 字段名 SET DEFAULT; # 修改字段名和字段类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型(长度) [COMMENT 注释] [约束]; # 删除字段 ALTER TABLE 表名 DROP 字段名 # 修改表名 ALTER TABLE RENAME TO 新表名
-
删除
# 删除表 DROP TABLE [IF EXISTS] 表名 # 删除指定表，并重新创建该表 TRUNCATE TABLE 表名;
-
图形化界面 ( 我选用的是 Navicat )
-
-
DML: 数据操作语言，用来对数据库表中的数据进行增删改。
-
添加数据 ( INSERT )
# 给指定字段添加数据 INSERT INTO 表名(字段1, 字段2, ···) VALUES(值1, 值2, ···); # 给全部字段添加数据 INSERT INTO 表名 VALUES(值1, 值2, ···); # 批量添加数据 INSERT INTO 表名(字段1, 字段2, ···) VALUES(值1, 值2, ···), VALUES(值1, 值2, ···), VALUES(值1, 值2, ···); -
修改数据 ( UPDATE )
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ··· [WHERE 条件];
-
删除数据 ( DELETE )
DELETE FROM 表名 [WHERE 条件];
-
-
DQL: 数据查询语言，用来查询数据库中表的记录。
# 语法结构 ( 编写顺序 ) SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 条件列表 HAVING 分组后条件列表 ORDER BY 排序字段刘表 LIMIT 分页参数-
基本查询
-
基本查询
# 查询多个字段 SELECT 字段1, 字段2, 字段3, ··· FROM 表名; SELECT * FROM 表名; # 设置别名 SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ··· FROM 表名; # 去除重复记录 SELECT DISTINCT 字段列表 FROM 表名
-
条件查询
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN ··· AND ··· 在某个范围之内( 含最大，最小 ) IN ( ··· ) 在 IN 之后的列表中的值，多选一 LIKE 占位符 模糊匹配 ( _匹配单个字符，% 匹配任意个字符 ) IS NULL 是 NULL 逻辑运算符 功能 AND 或 && 并且 OR 或 || 或者 NOT 或 ! 非 -
聚合函数 ( 作用于表单的某一列 )
函数 功能 COUNT 统计数量 MAX 最大值 MIN 最小值 AVG 平均值 SUM 求和 SELECT 聚合函数(字段列表) FROM 表名
注意: NULL 不参与聚合函数运算
-
分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]
WHERE 和 HAVING 的区别：
-
执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
-
判断条件不同: where不能对聚合函数进行判断，而having可以。
注意：
-
执行顺序: where >聚合函数>having .
-
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
-
排序查询
# 支持多字段排序 # 如果第一个字段相同, 则根据第二个字段排序 # 升序 ASC # 降序 DESC SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
-
分页查询
SELECT 字段列表 FROM LIMIT 起始索引, 查询记录数;
注意：
-
起始索引从 0 开始，起始索引 = ( 查询页码 - 1) * 每页显示记录数。
-
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
-
如果查询的是第一页数据，起始索引可以省略，直接简写为 LIMIT 10。
-
执行顺序，其中编写顺序limit最后
# 语法结构 FROM 表名列表 WHERE 条件列表 GROUP BY 条件列表 HAVING 分组后条件列表 SELECT 字段列表 ORDER BY 排序字段刘表 LIMIT 分页参数 -
-
-
DCL: 数据控制语言，用来创建数据库用户、控制数据库的访问权限。
-
用户管理
# 查询用户 USE mysql; SELECT * FROM user; # 创建用户 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; # 修改用户密码 ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; # 删除用户 DROP USER '用户名'@'主机名';
注意：
-
主机名可以使用%通配。
-
这类MySQL开发人员操作的比较少，主要是DBA ( Database Administrator数据库管理员）使用。
-
-
权限管理
权限 描述 ALL，ALL PRIVILEGES 所有权限 SELECT 查询权限 INSERT 插入权限 UPDATE 修改权限 DELETE 删除权限 ALTER 修改表 DROP 删除数据库/表/试图 CREATE 创建数据库/表 # 查询权限 SHOW GRANTS FOR '用户名'@'主机名' # 授予权限 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名' # 撤销权限 REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'
-
-
SQL 函数
函数 是指一段可以直接被另一段程序调用的程序或代码。
-
字符串函数
函数 描述 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个长度的字符串 SELECT 函数(参数);
SELECT LPAD('xx', 5, '-'); # '---xx' SELECT RPAD('xx', 5, '-'); # 'xx---' -
数值函数
函数 描述 CEIL( x ) 向上取整 FLOOR( x ) 向下取整 MOD( x ) 返回 x / y 的模 RAND() 返回 0 ~ 1 内的随机数 ROUND( x，y ) 求参数 x 的四舍五入的值，保留 y 位小数 SELECT 函数(参数);
实践：通过数据库的函数，生成一个六位数的随机验证码。
SELECT LPAD(ROUND(RAND() * 1000000), 6, '0');
-
日期函数
函数 描述 CURDATE() 返回当前日期 CURTIME() 返回当前时间 NOW() 返回当前日期和时间 YEAR() 返回指定 date 得年份 MONTH() 返回指定 date 得月份 DAY() 返回指定 date 得日期 DATE_ADD( date，INTEREAL expr type ) 返回一个日期 / 时间值加上一个时间间隔 expr 后的时间值 DATEDIFF( date1，date2 ) 返回一个日期 / 时间值加上一个时间间隔 expr 后的时间值 | -
流程函数
函数 描述 IF( value, t, f ) 如果 value 为 true，则返回 t， 否则返回 f IFNULL( value1, value2 ) 如果 value1 不为空，返回 value1， 否则返回value2 CASE WHEN [val1] THEN [res] ··· ELSE [ default ] END 如果 value1 为 true，返回 res1，否则返回 default 默认值 CASE [ expr ] WHEN [val1] THEN [res1] ··· ELSE [default] END 如果 expr 的值等于 val1， 返回 res1， ··· 否则返回 default 默认值
SQL 约束
-
非空约束 - 限制诚字段的数据不能为null - NOT NULL
-
唯一约束 - 保证该字段的所有数据都是唯一、不重复的 - UNIQUE
-
主键约束 - 主键是一行数据的唯一标识，要求非空且唯一 - PRIMARY KEY
-
默认约束 - 保存数据时，如果未指定该字段的值，则采用默认值 - DEFAULT
-
检查约束( 8.0.16 版本之后 ) - 保证字段值满足某一个条件 - CHECK
-
外键约束 - 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 - FOREIGN KEY
SQL 多表查询
-
多表查询概述
-
多表查询查出来的对象是一个笛卡尔积。
-
消除无效的4笛卡尔积就能得到我们想要的数据。
-
内连接
-
内连接查询的是 A，B 表交集的数据
-
隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ···;
-
显示外连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;
区别：
相对而言，隐式连接好理解好书写，语法简单，担心的点较少。但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显
-
外连接
-
左外连接
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ···;
-
右外连接
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ···;
-
-
自链接
自连接查询，可以是内连接查询,也可以是外连接查询。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ···;
-
联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ··· UNION [ALL] SELECT 字段列表 FROM 表B ···;
注意：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
-
子查询
-
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
-
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。
-
-
根据子查询结果不同，分类
-
标量子查询 ( 子查询结果为单个值 )
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符：= 、<> 、> 、>= 、< 、<=
SELECT * FROM 表A FROM 字段名1 = (SELECT 字段名1 FROM 表B WHERE 字段名2 = 'xxx');
-
列子查询 ( 子查询结果为一列 )
子查询返回的结果是一列（可以是多行)，这种子查询称为列子查询。
常用的操作符: IN 、NOT IN 、ANY 、SOME、ALL
SELECT * FROM 表A WHERE 字段名1 IN (SELECT 字段名1 FROM 表B WHERE 字段名2 = 'xxx');
ALL：相当于 MAX，取上线
ANY，SOME：相当于 MIN，取下线
-
行子查询 ( 子查询结果为一行 )
子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '张无忌');
-
表子查询 ( 子查询结果为多行多列 )
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id ;
-
-
根据子查询位置
-
WHERE 之后
-
FROM 之后
-
SELECT 之后
-
-
-
多表查询案列
SQL 事务
-
事务简介
-
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
-
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
-
-
事务操作
-
查看 / 设置事务的提交方式 ( 第一种方式 )
SELECT @@autocommit; SET @@autocommit = 0;
-
提交事务
COMMIT;
-
回滚事务
ROLLBACK;
-
开启事务 ( 第二种方式 )
START TRANSACTION 或 BEGIN; # 手动控制事务
-
-
事务的四大特性ACID
-
原子性( Atomicity)
-
事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
-
-
一致性(Consistency)
-
事务完成时，必须使所有的数据都保持一致状态。
-
-
隔离性(lsolation)
-
数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
-
-
持久性(Durability)
-
事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
-
-
-
事务的并发问题
问题 描述 脏读 一个事务读到另外一个事务还没有提交的数据 不可重复读 一个事务先后读到同一条记录，但两次读取的数据不同，称之为不可重复读 幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行的数据已经存在，好像出现了 "幻影" ( 解决幻读是给事务加了锁 ) -
事务的隔离级别
隔离级别 脏读 不可重复度 幻读 Read uncommitted √ √ √ Read committed（Oracle默认） × √ √ Repeatable Read ( Mysql 默认 ) - 可重复读 × × √ Serializable × × × 注意：事务隔离级别越高，数据越安全，但是性能越低。
# 查看事务的隔离级别 SELECT @@TRANSACTION_ISOLATION; # 设置事务的隔离级别 SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
