MySQL

尾翼稳定脱壳穿甲弹
156 阅读29分钟

数据库操作

创建数据库

CREATE DATABASE `数据库名称`;-- 创建默认数据库，不指定字符集和校验规则
-- 默认字符集是utf8，默认校对规则是utf8_general_ci(不区分大小写)

CREATE DATABASE `数据库名称` CHARACTER SET utf8 COLLATE utf8_bin; 
-- 创建数据库，自定义字符集utf8和校验规则utf8_bin(区分大小写)

指定数据库的字符集和校对规则，可以在表未指定时，以数据库的字符集和校对规则为准

删除数据库

DROP DATABASE `数据库名称`;


DROP DATABASE IF EXISTS `数据库名称`; -- 若要删除的数据库不存在不会报错

显示数据库

SHOW DATABASES; -- 显示所有数据库


USE `数据库名称`; -- 使用指定的数据库


SELECT DATABASE(); -- 显示当前使用的是哪个数据库


SHOW CREATE DATABASE `数据库名称`; -- 显示数据库创建语句

备份和恢复数据库

备份和恢复数据库

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库3 > 路径+文件名.sql -- 备份数据库
-- 备份数据库需要在DOS命令行里执行

source 路径+文件名.sql --恢复数据库
-- 恢复数据库需要在mysql命令行里执行
-- 或者将sql文件中的语句全选复制到可视化工具中的查询中执行，恢复数据库

备份和恢复数据库中的某些表

mysqldump -u 用户名 -p 数据库名称 表1 表2 表3 > 路径+文件名.sql -- 备份表，在DOS命令行里执行

source 路径+文件名.sql --恢复表，在mysql命令行执行

表操作

创建表

CREATE TABLE `表名`(
    `字段1` 字段1类型,
    `字段2` 字段2类型,
    `字段3` 字段3类型
)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 存储引擎;
-- 不指定字符集和校对规则就以数据库的为准
-- 字段类型后加UNSIGNED表示无符号
-- 时间戳类型加NOT NULL DEFAULT CURRENT_TIMESTAMP
--            ON UPDATE CURRENT_TIMESTAMP可以自动填当前时间戳

CREATE TABLE 表名1 LIKE 表名2 -- 假如现已有一张表2,可以创建一张有相同结构的空表1

字段类型常用的有：

整型：
int (4个字节)

小数类型：
double (8个字节)
decimal(M,D) (可变长度，M为长度，D为小数点后保留位数)

文本类型：
char (0-255个字符)
varchar (0-65532个字节) #varchar(4) 4表示字符数 varchar是可变长度的
text

日期类型：
datetime (8个字节)
timestamp (4个字节)

修改表

DESC `表名`; -- 查看表结构

RENAME TABLE `表名` TO `新表名`; -- 修改表名

ALTER TABLE `表名` CHARACTER SET 字符集; -- 修改表的字符集

-- 添加多个字段
ALTER TABLE `表名` ADD (
    `字段1` 字段1类型,
    `字段2` 字段2类型,
    `字段3` 字段3类型 -- 字段类型后可以加NOT NULL DEFAULT '' 指定非空和默认
);

-- 在指定字段后添加新字段
ALTER TABLE `表名` ADD `字段名` 字段类型 AFTER `指定字段名`;

-- 修改字段类型
ALTER TABLE `表名` MODIFY `字段名` 字段类型;

-- 修改字段名
ALTER TABLE `表名` CHANGE `原字段名` `新字段名` 新字段类型;

-- 删除字段
ALTER TABLE `表名` DROP `字段名`;

记录操作

增 INSERT

INSERT INTO `表名` (字段1,字段2,字段3) VALUES (值1,值2,值3);
-- 插入顺序需一一对应
-- 插入对应的值数据类型需对应，否则报错
-- 插入的值长度也需符合要求
-- 字符和日期类型需要用单引号包起来
-- 可以插入空值，前提是字段允许插入空值

INSERT INTO `表名` (字段1,字段2,字段3) VALUES (值1,值2,值3),(值1,值2,值3),(值1,值2,值3); -- 一次添加多条记录

INSERT INTO `表名` VALUES (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
-- 当添加的记录包含所有字段时省略字段名直接添加

-- 当某个字段没写NOT NULL时，不指定值默认是空NULL

删 DELETE

DELETE FROM `表名` WHERE `字段名` = 字段值; -- 按条件删除

DELETE FROM `表名`; -- 删除所有记录  **慎用**

-- 使用DELETE语句只能删除记录，不能删除表，表需要用DROP删除

改 UPDATE

UPDATE `表名` SET `字段名` = 字段值 WHERE `字段名` = 字段值; -- 按条件找到某条记录，更改某个字段的值

UPDATE `表名` SET `字段名1` = 字段值1,`字段名2` = 字段值2,`字段名3` = 字段值3 WHERE `字段名` = 字段值; -- 修改多个字段的值

UPDATE `表名` SET `字段名` = 字段值; -- 更改所有记录的某个字段的值

查 SELECT

SELECT * FROM `表名`; -- 查询表中所有字段

SELECT DISTINCT * FROM `表名`; -- 加个DISTINCT关键字可以过滤重复记录，去重(每个字段都相同的记录)

SELECT `字段1`,`字段2`,`字段3` FROM `表名`; -- 查询指定字段

-- 字段可以参加运算
-- 大于>  小于<  大于等于>=  小于等于<=  等于=  不等于<> !=(两种都行)
-- BETWEEN ... AND ... 显示某一区间的值(闭区间)
-- IN(xx,xx,xx) 如IN(100,200,300)表示在该集合列表中的值
-- LIKE 'xxx'  模糊查询，如 `name` LIKE '王%' 表示在name字段中找姓王的(王开头)
-- NOT LIKE ''  模糊查询
-- IS NULL 判断是否为空
-- AND 相当于&&
-- OR 相当于||
-- NOT 相当于!

SELECT `字段1`,(`字段2` + `字段3`) FROM `表名`; -- 结果展示两列，| 字段1 | (字段1+字段2) |

SELECT `字段1` AS `别名1`,(`字段2` + `字段3`) AS `别名2` FROM `表名`; -- 给结果的字段起个别名，显示为| 别名1 | 别名2 |

SELECT * FROM `表名` WHERE 条件运算 -- 找出符合条件的记录

-- 排序
SELECT `字段1` AS `别名1`,(`字段2` + `字段3`) AS `别名2` FROM `表名` WHERE 条件运算 ORDER BY `别名2` DESC;
-- 查询语句后使用ORDER BY `字段名` ASC或DESC 可将查询结果进行排序输出
-- ASC升序(默认)  DESC降序
-- ORDER BY 也可以加别名,根据别名排序

函数

COUNT: 返回查询的结果有多少行

-- COUNT 函数:返回查询的结果有多少行
SELECT COUNT(*) FROM `表名`; -- 查看有多少条记录
SELECT COUNT(*) FROM `表名` WHERE 条件; -- 查看有多少条满足条件的记录

SELECT COUNT(`字段名`) FROM `表名`; -- 和COUNT(*)的区别:会排除NULL的情况,返回非空的记录个数
SELECT COUNT(`字段名`) FROM `表名` WHERE 条件; -- 返回非空且满足条件的记录个数

SUM: 返回多个字段的和

SELECT SUM(字段1+字段2+字段3) FROM `表名` WHERE 条件; -- 返回满足WHERE条件的记录的多个字段值的和,这里字段须是数字类型否则报错, 去掉WHERE后面的条件就是所有记录的指定字段值的和

AVG: 求平均值

SELECT AVG(字段1+字段2+字段3) FROM `表名` WHERE 条件;

MAX和MIN: 求最大最小值

SELECT MAX(字段名) FROM `表名` WHERE 条件; -- 返回满足条件的记录里,指定字段的最大值

分组

将指定字段值相同的多条记录分到同一组,可以求出组的平均值,和,最大最小值等

SELECT 字段一,字段二,字段三 FROM 表名 GROUP BY 字段名;

-- having后加条件可将结果再进行过滤
SELECT 字段一,字段二,字段三 FROM 表名 GROUP BY 字段名 HAVING 条件;

字符串函数

-- 以下字符串函数里面既可以是字段名,也可以是字符串,部分除外

SELECT CHARSET(字段名) -- 返回字符串的字符集
-- 如: SELECT CHARSET(字段名) FROM 表名;

SELECT CONCAT(字符串1,字符串2,字符串3,...) -- 拼接多个字符串
-- 如: SELECT CONCAT(字段1,'+',字段2) FROM EMP;

SELECT INSTR(字符串1,字符串2) -- 返回字符串2在字符串1中出现的位置,没有就返回0

SELECT UCASE(字符串) -- 转换成大写

SELECT LCASE(字符串) -- 转换成小写

SELECT LEFT(字段名,长度) -- 将指定字段的值从左边起取指定长度个字符
-- 如: SELECT LEFT(字段名,3) FROM 表名;

SELECT RIGHT(字段名,长度) -- 将指定字段的值从右边起取指定长度个字符

SELECT LENGTH(字段名) FROM 表名 -- 按字节返回字段值的长度

SELECT REPLACE(字段名,被替换的字符串,目标字符串) -- 替换字符串

SELECT STRCMP(字符串1,字符串2) -- 逐个字符比较两个字符串的大小(不是长度),一样大返回0,前者大于后者返回-1,反之返回1

SELECT SUBSTRING(字段名,开始索引,结束索引) -- 截取指定字段的指定部分,注意索引从1开始

SELECT SUBSTRING(字段名,开始索引) -- 只有开始索引会截取开始索引到末尾结束的字符串

SELECT TRIM(字符串) -- 去掉字符串两端的空格

SELECT LTRIM(字符串) -- 去掉字符串左边空格

SELECT RTRIM(字符串) -- 去掉字符串右边空格

数学函数

SELECT ABS(数字) -- 求绝对值

SELECT BIN(数字) -- 10进制转2进制

SELECT CEILING(数字) -- 向上取整

SELECT CONV(数字,原进制数字,目标进制数字) -- 进制转换
-- 如 SELECT CONV(8,16,10) 表示将16进制的8转换成10进制数

SELECT FLOOR(数字) -- 向下取整

SELECT FORMAT(小数,保留数字位) -- 四舍五入保留小数的指定位数
-- 如 SELECT FORMAT(123.123123,2) 表示保留2位小数

SELECT HEX(数字) -- 转16进制

SELECT LEAST(数字1,数字2,数字3) -- 求一堆数字的最小值

SELECT MOD(数字1,数字2) -- 求余数
-- 如 SELECT MOD(10,3) 的结果位1

SELECT RAND(种子数) -- 返回随机数

日期函数

SELECT CURRENT_DATE() -- 返回当前日期

SELECT CURRENT_TIME() -- 返回当前时间

SELECT CURRENT_TIMESTAMP() -- 返回当前时间戳(年月日时分秒)
-- 如 INSERT INTO 表名 VALUES(1,'删除',CURRENT_TIMESTAMP()) 可将时间插入到表中

SELECT DATE(datetime) -- 返回datetime格式的时间的日期部分

SELECT DATE_ADD(date2,INTERVAL 数字 时间类型) -- 返回在查询的date2时间基础上增加了指定时间的结果 

SELECT DATE_SUB(date2,INTERVAL 数字 时间类型) -- 返回在查询的date2时间基础上减少了指定时间的结果

SELECT DATEDIFF(date1,date2) -- 返回两个日期的差(单位是天)前者减后者

SELECT TIMEDIFF(date1,date2) -- 两个时间相差多少小时多少分多少秒,返回的是时分秒

SELECT NOW() -- 当前时间

SELECT UNIX_TIMESTAMP() -- 返回时间戳(1970-1-1至现在的秒数)

SELECT FROM_UNIXTIME(UNIX时间戳,'%Y-%m-%d %H:%i:%s') -- 把一个unix格式的时间戳转换成指定格式,第二个参数格式固定 (意义:在开发中,可以存放一个整数来表示时间,通过该函数转换)

加密函数和系统函数

SELECT USER() -- 可查看登录到mysql有哪些用户,以及登录的IP地址

SELECT DATABASE() -- 显示当前使用的是哪个数据库

SELECT MD5(字符串) -- 为字符串算出一个MD5 32位长度的字符串,对用户密码加密

SELECT PASSWORD(字符串) -- 也是对用户密码加密

流程控制函数

SELECT IF(表达式1,表达式2,表达式3) -- 如果表达式1为真,执行2,否则执行3 (和三元运算符一样)

SELECT IFNULL(表达式1,表达式2) -- 如果表达式1不为空,返回表达式1,否则返回2

SELECT CASE WHEN 表达式1 THEN 表达式2 WHEN 表达式3 THEN 表达式4 ELSE 表达式5 END; -- 多重分支

查询增强

-- mysql中日期格式可以直接比较大小

-- 模糊查询 like
-- % 表示0到多个任意字符
-- _ 表示  1 个任意字符
SELECT * FROM 表名 WHERE 字段 LIKE 'xxx'; -- 表示模糊查询,用占位符模糊匹配

-- 排序后再排序
SELECT * FROM 表名 ORDER BY 字段1 ASC,字段2 DESC; -- 先按字段一升序,再按字段二降序

-- 分页查询
SELECT * FROM 表名 LIMIT 开始的索引,取的长度; -- limit后跟两个数字,第一个表示从第几条记录开始取,第二个数字表示取几条记录  这里索引从0开始

-- 如果SELECT语句同时包含GROUP BY, HAVING, LIMIT, ORDER BY则他们的顺序是
-- GROUP BY, HAVING, ORDER BY, LIMIT

MySQL中判断是否为NULL不能写成 = NULL ,得写成 IS NULL

多表查询

SELECT * FROM 表1,表2 -- 不加条件直接查询两个表会将表1的第一条记录分别和表2的所有记录拼接,以此类推
-- 所查出来的结果记录数是 表1的记录数 * 表2的记录数 ,返回的这样的结果叫 笛卡尔集
-- 所以多表查询的重点是进行过滤条件 where

SELECT * FROM 表1,表2 WHERE 表1.字段名 = 表2.字段名 AND 条件 -- 条件中可以 表名.字段名 表示哪个表中的哪个字段,多个条件用and连接

-- 多表查询的条件数不能少于 表的个数-1 ,否则会出现笛卡尔集
-- 如现有5张表进行多表查询,至少需要4个条件才可以

**自连接 :**将同一张表看成两张表进行连接查询

SELECT * FROM 表名 别名1,表名 别名2 WHERE 条件; -- 这里条件要用别名.字段,为了分清两个自己
-- 如 select boss.ename from emp a,emp boss where a.mgr = boss.empno and a.ename = 'smith'; 找出smith的boss,boss和他在同一张表内,smith的mgr指向boss的empno

子查询

子查询也叫嵌套查询, 在正常的SELECT查询中,在条件里再写一个SELECT查询,用括号包起来

-- 单行子查询,查询结果是一行
SELECT * FROM 表名 WHERE 条件 = (SELECT 字段名 FROM 表名 WHERE 条件)

-- 多行子查询(IN),查询结果是多行
SELECT * FROM 表名 WHERE 字段 IN (SELECT 字段名 FROM 表名 WHERE 条件)

子查询临时表

SELECT 语句的结果是个表,那就可以将该条语句填入到另一条SELECT语句的表名位置

SELECT 字段名 FROM (SELECT 字段名 FROM 表名 WHERE 条件) 别名1,表名2 别名2 WHERE 条件 -- 注意这里必须有别名1,及临时表后面必须有空格加别名

ALL和ANY

在多行子查询中可使用ALL和ANY关键字

-- 如
SELECT * FROM 表名 WHERE 字段名 > ALL (SELECT 字段名 FROM 表名 WHERE 条件) -- 大于所有
SELECT * FROM 表名 WHERE 字段名 > ANY (SELECT 字段名 FROM 表名 WHERE 条件) -- 大于其中一个

多列子查询

返回多个列的子查询

SELECT * FROM 表名 WHERE (字段1,字段2) = (子查询); -- 这里子查询的结果返回的是两个字段的的结果(即子查询为:SELECT 字段1,字段2 FROM...),这条语句结果为返回字段1和字段2分别和子查询的结果中的字段1字段2相等的记录

表名.* 表示表中所有字段

复制

INSERT INTO 表2 (字段1,字段2,字段3) SELECT 字段1,字段2,字段3 FROM 表2; -- 复制表1到另一张表2

INSERT INTO 表2 SELECT * FROM 表1 -- 复制表1到另一张表2(他俩有相同的表结构)

INSERT INTO 表名 SELECT * FROM 表名; -- 自我复制(执行每次多一倍记录,指数增长)
-- 为了测试sql语句的效率,需要海量数据,这时可以自我复制几次

去重

思路:

  1. 先创建一张临时表temp,表结构和要去重的表de_weight一样
  2. 通过DISTINCT关键字将de_weight处理后把记录复制到temp中
  3. 清除掉de_weight中的记录
  4. 把temp中的记录复制到de_weight中
  5. drop掉temp表
-- 先创建一张临时表temp,表结构和要去重的表de_weight一样
CREATE TABLE temp LIKE de_weight;

-- 通过DISTINCT关键字将de_weight处理后把记录复制到temp中
INSERT INTO temp SELECT DISTINCT * FROM de_weight;

-- 清除掉de_weight中的记录
DELETE FROM de_weight;

-- 把temp中的记录复制到de_weight中
INSERT INTO de_weight SELECT * FROM temp;

-- drop掉temp表
DROP TABLE temp;

合并查询

UNION ALL 将两个SELECT查询语句的结果合并(不会去重)

UNION 将两个SELECT查询语句的结果合并(自动去重)

SELECT * FROM 表名 WHERE 条件1 UNION ALL SELECT * FROM 表名 WHERE 条件2; -- 合并两个查询语句的结果(不去重)

SELECT * FROM 表名 WHERE 条件1 UNION SELECT * FROM 表名 WHERE 条件2; -- 合并两个查询语句的结果(去重)

外连接

现有左右两张表进行多表查询,查询的结果:

如果左侧的表完全显示我们就说是左外连接

如果右侧的表完全显示我们就说是右外连接

SELECT * FROM 左表 LEFT JOIN 右表 ON 条件; -- 左外连接

SELECT * FROM 左表 RIGHT JOIN 右表 ON 条件; -- 右外连接

-- 例子
exam
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |     8 |
+------+-------+

stu
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | tony |
+------+------+

-- SELECT * FROM exam LEFT JOIN stu ON exam.id = stu.id; 左外连接,表示查询的结果中包含exam中的所有记录,没有匹配上stu的字段用NULL填充
结果:
+------+-------+------+------+
| id   | grade | id   | name |
+------+-------+------+------+
|    1 |    56 |    1 | jack |
|    2 |    76 |    2 | tom  |
|   11 |     8 | NULL | NULL |
+------+-------+------+------+

约束

主键

primary key,用于唯一地标示表行的数据,当定义主键约束后该列不能重复,不能为空,创建表时在字段类型后加primary key可以定义主键,一般每张表都有一个主键

-- 创建主键
CREATE TABLE 表名 (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(32)
);

-- 复合主键(id和name都一样的两条记录第二条会插入失败)
CREATE TABLE 表名 (
    `id` INT,
    `name` VARCHAR(32),
    PRIMARY KEY(`id`,`name`)
);

-- 主键的添加和删除
ALTER TABLE 表名 ADD PRIMARY KEY (字段名); -- 添加主键

ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键

非空

not null,设置后想插入记录则必须为该字段提供数据,创建表时在字段类型后加not null可以定义非空

唯一

unique,设置后该字段所有值不能重复,创建表时在字段类型后加unique可以定义唯一

如果没有指定not null,则unique字段可有多个null,一张表可以有多个unique字段

如果一个字段是unique not null,则效果类似primary key

外键

如果要求的某个字段的值必须是在另一个表中有的值,即不能瞎写,得是真实存在的值,就可以给这个字段加一个外键约束

假如有一个学生表和班级表,一旦建立起外键约束(每个学生都对应存在的班级),直接删班级表中的班级,是删不掉的,除非没有学生在这个班,才能删除,其中班级表为主表,学生表为外键所在表,从表

  1. 主表的被指向的字段必须是主键约束primary key或者唯一约束unique

  2. 表的类型是InnoDB才支持外键

  3. 外键字段类型必须和主键字段类型一致,长度可以不一样

  4. 设置外键的字段值,必须在主键字段中出现过,或者将外键字段值设为null暂时不指定也行(前提是外键字段允许为null)

  5. 一旦建立主外键关系,数据就不能随便删除了

FOREIGN KEY (要加外键的字段) REFERENCES 主表名(主表里的字段) -- 加外键的关键字

-- 举例
-- 创建班级表(主表)
CREATE TABLE class (
	id INT PRIMARY KEY, -- 这里id将来被学生表里的班级id所指向,所以要用主键约束或唯一约束
    name VARCHAR(32) NOT NULL DEFAULT ''
);
-- 创建学生表(从表)
CREATE TABLE stu (
	id INT PRIMARY KEY,
    name VARCHAR(32) NOT NULL DEFAULT '',
    class_id INT, -- 这个班级id需要设置外键约束,将来插入记录就不能瞎写了,只能是class表里的
    FOREIGN KEY (class_id) REFERENCES class(id) -- 加外键约束
)

check

用来强制字段值必须满足指定条件,比如可以定义check约束,指定该字段的值只能在1000到2000内,则会报错

MySQL5.7暂时还不支持check约束,只做语法校验不生效,但oracle和sql server均支持

-- 示例
CREATE TABLE 表名(
	`id` INT PRIMARY KEY,
    `sal` DOUBLE CHECK ( sal > 1000 AND	sal < 2000 ) -- 约束在1000-2000内
);

自增长

给某个字段添加自增长后,当添加记录时,该字段会自己增长,如id

  1. 不设置默认从1开始,设置" ALTER TABLE 表名 AUTO_INCREMENT = 新的开始值 "后从自己设置的数字开始增加

  2. 一般来说,自增长和primary key配合使用,也可以和unique配合使用

  3. 自增长一般是整形,但也可以是小数类型,但很少这样用

  4. 一般来说,某字段设置自增长后就不要再手动添加数据了,让它自己加

CREATE TABLE 表名(
	`id` INT PRIMARY KEY AUTO_INCREMENT, -- 设置id为自增长
    `name` VARCHAR(32) NOT NULL DEFAULT ''
)

添加记录时,自增长的字段有以下处理方式

-- 假设字段1是自增长的

INSERT INTO 表名 (字段1,字段2,字段3) VALUES (NULL,字段值2,字段值3); -- 填NULL

INSERT INTO 表名 (字段2,字段3) VALUES (值2,值3); -- 跳过字段1

INSERT INTO 表名 VALUES (NULL,字段值2,字段值3); -- 填NULL

索引

创建索引会让搜索速度变快,但会使数据库文件变大,第一次创建索引也会占用时间

给哪个字段添加索引哪个字段上的搜索速度就会变快

没加索引时,会从头到尾进行全表扫描,加索引后会创建二叉树,左子树放值小的,右子树放值大的

索引查找快,是有代价的,首先建二叉树有时间和空间开销,其次对增删改语句有效率上的影响(会破坏树)

但在项目中,查找语句select是最多的(占90%),所以优点肯定是大于缺点的

  • 主键primary key自然就是索引,查询速度非常快,称为主键索引
  • unique自然也是索引,称为unique索引
  • 全文索引FULLTEXT,可以找比如文章里的关键词等,MySQL里的MyISAM存储引擎里有全文索引,但是不好用,实际中一般使用Solr和ElasticSearch(ES)全文搜索的框架

使用索引的注意事项

  • 较频繁作为查询条件的字段应该为其创建索引
  • 唯一性太差的字段不适合创建索引(如性别字段)
  • 更新非常频繁的字段不适合创建索引(如登录次数)
  • 不会出现在where子句中的字段不该创建索引
-- 普通索引的添加和删除
CREATE INDEX 索引名 ON 表名(字段名); -- 在指定表的指定字段上创建索引

CREATE UNIQUE INDEX 索引名 ON 表名(字段名); -- 添加unique索引

ALTER TABLE 表名 ADD INDEX 索引名 (字段名); -- 用alter添加索引

DROP INDEX 索引名 ON 表名; -- 删除索引

-- 主键索引的添加和删除
ALTER TABLE 表名 ADD PRIMARY KEY (字段名); -- 添加主键索引

ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键索引

-- 修改索引:先删除,再添加新索引

-- 查询该表是否有索引
SHOW INDEXES FROM 表名;
SHOW INDEX FROM 表名;
SHOW KEYS FROM 表名;
DESC 表名; -- 索引的Key字段有NUL标识

事务

事务用于保证数据的一致性,它由一组相关的增删改语句组成,该组的增删改语句要么全部成功,要么全部失败

如:转账就要用事务来处理,假如A要给B转账100,那么A-100和B+100这两个sql语句都要执行成功,看成一个整体,用以保证数据的一致性。

当执行事务操作时(增删改语句),MySQL会在表上加锁,防止其它用户改表的数据,这对用户来讲是非常重要的

-- MySQL数据库控制台事务的几个重要操作
START TRANSACTION -- 开始一个事务
SAVEPOINT 保存点名 -- 设置保存点
ROLLBACK TO 保存点名 -- 回退事务
ROLLBACK -- 回退全部事务
COMMIT -- 提交事务,所有操作生效,不能回退

回退事务 在介绍回退事务前,先介绍一下保存点(SAVEPOINT).保存点是事务中的点.用于取消部分事务,当结束事务时(COMMIT),会自动的删除该事务所定义的所有保存点.当执行回退事务时,通过指定保存点可以回退到指定的点

提交事务 使用COMMIT语句可以提交事务.当执行了COMMIT语句后,会确认事务的变化,结束事务,删除保存点,释放锁,数据生效.当使用COMMIT语句结束事务后，其它会话(其他连接)将可以查看到事务变化后的新数据(所有数据就正式生效)

细节

  1. 如果不开始事务,默认情况下,增删改操作是自动提交的，不能回滚

  2. 如果开始一个事务,你没有创建保存点,你可以执行ROLLBACK,默认就是回退到你事务开始的状态

  3. 你也可以在这个事务中(还没有提交时),创建多个保存点.比如:

    SAVEPOINT aaa; 执行增删改

    SAVEPOINT bbb;

  4. 你可以在事务没有提交前,选择回退到哪个保存点

  5. MySQL的事务机制需要innodb的存储引擎才可以使用,myisam不好使

  6. 开始一个事务用START TRANSACTION或SET AUTOCOMMIT = OFF

MySQL事务隔离级别

多个连接开启各自事务操作数据库中数据时,数据库系统要负责隔离操作,以保证各个连接在获取数据时的准确性,离开事务不要谈隔离级别,隔离级别定义了事务与事务之间的隔离程度

如果不考虑隔离性，可能会引发如下问题：

  • 脏读(dirty read) 当一个事务读取另一个事务尚未提交的改变时,产生脏读
  • 不可重复读(nonrepeatable read) 同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生不可重复读
  • 幻读(phantom read) 同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻读
MySQL隔离级别(4种)脏读不可重复读幻读加锁读
读未提交(Read uncommitted)√√√不加锁
读已提交(Read committed)×√√不加锁
可重复读(Repeatable read)×××不加锁
可串行化(Serializable)×××加锁

√可能出现,×不会出现,加锁:当一个事务没有提交,正在操作表时,另一个事务的语句就直接卡住了

查询隔离级别

SELECT @@tx_isolation; -- 查看当前会话的隔离级别

SELECT @@global.tx_isolation; -- 查看系统的隔离级别

设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 级别; -- 设置会话的隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL 级别; -- 设置会话的隔离级别

-- 默认隔离级别是Repeatable read,一般情况下不需要更改,可以满足大部分项目需求
-- 假如想更改默认隔离级别,可以在MySQL文件夹里的my.ini里加一条transaction-isolation = 级别

事务的ACID特性

  1. 原子性(Atomicity) 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 致性(Consistency) 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
  3. 隔离性(Isolation) 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  4. 特久性(Durability) 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

存储引擎

  1. MySQL表的类型由存储引擎(Storage Engines)决定,主要包括MyISAM,innoDB,Memory等
  2. MySQL里的表主要支持六种类型,分别是:CSV,Memory,ARCHIVE,MRG_MYISAM,MYISAM,InnoBDB
  3. 这六种又分为两类,一类是”事务安全型”(transaction-safe),比如InnoDB;其余都属于第二类,称为”非事务安全型”(non-transaction-safe),有mysiam和memory
  4. MySQL默认存储引擎是InnoDB,只有InnoDB支持事务和外键
  5. 存储引擎各有优点,实际中根据需求选存储引擎
    • 如果你的应用不需要事务,处理的只是基本的CRUD操作,那么MyISAM是不二选择,速度快
    • 如果需要支持事务,选择InnoDB
    • Memory存储引擎就是将数据存储在内存中,由于没有磁盘l/O的等待,速度极快.但由于是内存存储引擎,所做的任何修改在服务器重启后都将消失(经典用法:用户的在线状态)
SHOW ENGINES; -- 查看所有的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎; -- 修改存储引擎

重点介绍三种:

  1. MyISAM:不支持事务,也不支持外键,但其访问速度快,对事务完整性没有要求
  2. InnoDB存储引擎提供了具有提交,回滚和崩溃恢复能力的事务安全.但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
  3. MEMORY存储引擎使用存在内存中的内容来创建表,每个MEMORY表只实际对应一个磁盘文件.MEMORY类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH索引.但是一旦MySQL服务关闭,表中的数据就会丢失掉,表的结构还在,这种表可以存比如用户在线状态信息等

视图

视图是一个虚拟表,其内容由查询定义,同真实表一样,视图包含列,其数据来自对应的真实表(基表)

可以理解为视图是将你想让别人访问的字段摘出来,重新拼成一张表,他对视图的操作就相当于对基表的操作,基表的改变也会同步到视图,视图和基表是映射关系

CREATE VIEW 视图名 AS SELECT语句 -- 创建视图

ALTER VIEW 视图名 AS SELECT语句 -- 修改视图

SHOW CREATE VIEW 视图名; -- 展示当时是怎样创建的视图

DROP VIEW 视图名1,视图名2; -- 删除视图

DESC 视图名; -- 查看视图结构
  1. 创建视图后,到数据库文件夹看只有一个视图结构文件(视图名.frm)
  2. 视图和基表二者相互变化互相影响,数据都是基表的那一份
  3. 视图中可以再创建视图

视图的最佳实践

  1. 安全,一些数据表有着重要的信息,有些字段是保密的,不能让用户直接看到,这时就可以创建一个视图,在这张视图中只保留一部分字段.这样,用户就可以查询自己需要的字段,不能查看保密的字段
  2. 性能,关系数据库的数据常常会分表存储,使用外键建立这些表的之间关系,这时,数据库查询通常会用到连接(JOIN).这样做不但麻烦,效率相对也比较低,如果建立一个视图,将相关的表和字段组合在一起,就可以避免使用JOIN查询数据
  3. 灵活,如果系统中有一张旧的表,这张表由于设计的问题,即将被废弃,然而很多应用都是基于这张表,不易修改.这时就可以建立一张视图,视图中的数据直接映射到新建的表.这样,就可以少做很多改动,也达到了升级数据表的目的

MySQL管理

用户管理

MySQL系统中的用户都存在"mysql"数据库中的user表中,每条记录就是一个用户,不同的数据库用户,登录到DBMS后,根据相应的权限,可以操作的数据库和数据对像(表,视图,触发器)都不一样

  1. Host字段表示允许登录的位置,localhost表示只允许本机登录,也可以指定ip,让该用户只能从指定ip登录

  2. User字段为用户名

  3. authentication_string是密码,通过MySQL的password()函数加密之后得到的

-- 创建用户
CREATE USER '用户名'@'允许登录的位置' IDENTIFIED BY '密码'; -- 创建用户并指定密码

-- 删除用户
DROP USER '用户名'@'允许登录的位置'; -- 删除用户,注意是完整信息,用户名加位置

-- 修改自己的密码
SET PASSWORD = PASSWORD('密码');

-- 修改别人的密码
SET PASSWORD FOR '用户名'@'允许登录的位置' = PASSWORD('密码');

  1. 在创建用户的时候,如果不指定Host,则为%,%表示表示所有IP都有连接权限

    CREATE USER '用户名';

  2. 也可以指定为

    CREATE USER '用户名'@'192.168.1.%'; -- 表示192.168.1.*都可登录连接

  3. 在删除用户的时候,如果host不是%号,需要明确指定 '用户'@'host值'

权限管理

一般是root用户给其他用户授权

GRANT 权限列表 ON 库.对象名 TO '用户名'@'允许登录的位置'; -- 授权

-- 其中,权限列表中多个权限用逗号分开
-- 如GRANT SELECT,DELETE,CREATE ON ...
-- 或GRANT ALL ON ...

-- 其中, 库.对象名 如果写成*.*代表本系统中的所有数据库的所有对象(表,视图,存储过程)
-- 库.* 表示某个数据库中的所有数据对象(表,视图,存储过程)

GRANT 权限列表 ON 库.对象名 TO '用户名'@'允许登录的位置' IDENTIFIED BY '密码';
-- IDENTIFIED BY可以省略,也可以写出,写的话:
-- 如果用户存在,就是修改该用户的密码
-- 如果该用户不存在,就是创建该用户

-- 回收权限
REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'允许登录的位置';

-- 如果权限没有生效，可以执行下面命令(旧版本可能会用到)
FLUSH PRIVILEGES;
avatar
文章
阅读
粉丝