目录
一、流程控制语句
二、查询增强
三、分页查询
四、多表查询
五、表复制
六、合并查询
七、外连接
八、约束
九、自增长
十、索引
十一、事务
十二、存储引擎
十三、视图
十四、Mysql用户管理
十五、Mysql权限管理
十六、总结
这一节我们接着上一节的内容👇来继续讲解
文章名称1 | 文章链接 |
一、流程控制语句
函数 | 说明 |
---|---|
IF(expr1,expr2,expr3) | 如果expr1为True,则返回expr2,否则返回expr3 |
IFNULL(expr1,expr2) | 如果expr1不为空NULL,则返回expr1,否则返回expr2 |
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END | 如果expr1为True,则返回expr2,如果expr3为True,则返回expr4.否则返回expr5(类似于多重分支) |
二、查询增强
-- 查询表结构
DESC 表名;
-- 多重排序
SELECT 列名,...
FROM 表名
ORDER BY 列名1 升序/降序, 列名2 升序/降序, ...;
三、分页查询
-- 分页查询
SELECT 列名,...
FROM 表名
LIMIT start,rows;
- 表示从(start-1)开始,取rows行。
- 公式:每页显示记录数 * (第几页 - 1),每页显示记录数。
📌声明顺序
如果SELECT
语句同时包含GROUP BY
、HAVING
、LIMIT
、ORDER BY
,那么他们的声明顺序是GROUP BY
→HAVING
→ ORDER BY
→ LIMIT
。
四、多表查询
📌示例
SELECT ename, sal, emp.`deptno` -- 列不唯一的时候,需要注明表名
FROM emp,dept
WHERE emp.`deptno` = dept.`deptno`; -- 需要有过滤条件
💡说明:
- 多表查询的时候,如果没有过滤条件,就会出现
笛卡尔集错误
。- 多表查询的时候,过滤条件不能少于
表的个数-1
,否则就会出现笛卡尔集错误。- 当查询的
列不唯一
的时候,必须注明是查询哪个表的列。- 多表查询的时候,
WHERE
在GROUP BY
之前。
1. 多表查询-自连接
自连接
指的是在同一张表的连接查询。
📌示例
SELECT worker.`ename`, boss.`ename`
FROM `emp` worker, `emp` boss
WHERE worker.`mgr` = boss.`empno`;
如果两个相同的表进行查询,应该给表起别名
。
2. 多表查询-子查询
子查询
是指嵌入在其它sql语句中的select语句,也叫嵌套查询
。
📌示例
-- 多表查询-单行子查询
SELECT *
FROM `emp`
WHERE deptno = (
SELECT deptno
FROM `emp`
WHERE `ename` = 'SMITH'
); -- 括号内的SELECT查询语句的查询结果只有一行,所以叫单行子查询
-- 多表查询-多行子查询
SELECT *
FROM `emp`
WHERE job IN (
SELECT DISTINCT job
FROM `emp`
WHERE deptno = 10
); -- 括号内的SELECT查询语句的查询结果有多行,所以叫多行子查询
- 多行子查询用到了关键字
IN
。- 多行子查询也可以使用
any
和all
关键字进行查询。
any
:任意一个匹配就行。
all
:需要全部匹配。
3. 子查询-临时表
是将
子查询
当作一张临时表来使用。
📌示例
SELECT goods_id, temp.cat_id, goods_name, shop_price
FROM (SELECT cat_id, MAX(shop_price) AS max_price
FROM `ecs_goods`
GROUP BY `cat_id`
) temp, `ecs_goods`
WHERE temp.cat_id = ecs_goods.cat_id AND
temp.max_price = ecs_goods.shop_price;
需要注意:多表查询的时候,列名不唯一
的情况,需要注明其对应表名。
4. 子查询-多列子查询
📌示例
SELECT *
FROM `emp`
WHERE (deptno , job) = (
SELECT deptno, job
FROM `emp`
WHERE ename = 'ALLEN' -- 筛选的条件结果有多列
) AND `ename` != 'ALLEN';
-- (字段1,字段2,...) = (SELECT 字段1,字段2,... ) 其中字段要相互匹配
五、表复制
1. 复制表结构
CREATE TABLE 表名1 LIKE 表名2; -- 通过LIKE来给表1创建和表2相同的表结构
2. 复制表数据
INSERT INTO 表名1(列名1,列名2,...)
SELECT 列名1`,列名2`,...
FROM 表名2; -- 将表二中的查询到的数据(列名1...那些数据),插入到表1中对应的位置。
👆插入的时候是
列名1
中的数据对应插入到列名1
,其他的也一样。
3. 自我复制
INSERT INTO 表名1
SELECT * FROM 表名1; -- 将表名1中的数据复制到表名1中(相当于表名1中的数据自我复制)
4. 删除表中的重复数据
- 创建一个
临时表
。- 使用
DISTINCT
来查询,去除重复的数据。- 将
(2)
中处理的数据复制到临时表
中。- 删除原来的表,将临时表改名。
六、合并查询
1. UNION ALL
合并多个查询语句的查询结果,但
不会去重
。
📌示例
SELECT ename,sal,job
FROM emp
WHERE sal > 2500
UNION ALL
SELECT ename, sal ,job
FROM emp
WHERE job = 'MANAGER';
2. UNION
合并多个查询语句的查询结果,
会去重
。
📌示例
SELECT ename,sal,job
FROM emp
WHERE sal > 2500
UNION
SELECT ename, sal ,job
FROM emp
WHERE job = 'MANAGER';
💡说明:
- 多个查询语句合并的时候,必须保证它们的
列数必须相同
。(如果不相同,则会报错)- 合并查询的时候列应该保证
列顺序相同
。
七、外连接
1. 左外连接
SELECT 列名,...
FROM 表名1 LEFT JION 表名2
ON 过滤条件
如果左侧的表完全显示,则是
左外连接
。(不管左表的内容是否匹配)
2. 右外连接
SELECT 列名,...
FROM 表名1 RIGHT JION 表名2
ON 过滤条件
如果右侧的表完全显示,则是
右外连接
。(不管右表是否匹配)
八、约束
1. 约束-主键(primary key)
-- 方式一
CREATE TABLE 表名(
字段 字段类型 PRIMARY KEy,
...
);
-- 方式二
CREATE TABLE 表名(
字段 字段类型 PRIMARY KEy,
...
PRIMARY KEY(字段)
);
💡说明:
PRIMARY KEY
不能重复而且不能为NULL
。- 一张表最多只能有一个
主键
,但可以是复合主键。
✔补充
CREATE TABLE 表名(
字段 字段类型 PRIMARY KEy,
...
PRIMARY KEY(字段1,字段2)
);
使用DESC 表名
,可以通过查看表结构来查看PRIMARY KEY
的情况。
2. 约束-非空(not null)
CREATE TABLE 表名(
字段 字段类型 NOT NULL,
...
);
对应字段不能添加
NULL
。
3. 约束-唯一(unique)
CREATE TABLE 表名(
字段 字段类型 UNIQUE,
...
);
💡说明:
- 添加
UNIQUE
之后,该字段不能添加重复的值,但可以为NULL
。- 可以添加多个
UNIQUE
。
4. 约束-外键(foreign key)
用于定义主表
和从表
之间的关系:外键约束
要定义在从表上,主表则必须具有主键约束或者unique约束。当定义外键约束后,要求外键列数据必须在主表的主键列
存在,或是为null
。
FOREIGN KEY(字段) REFERENCES 主表名(主键名或unique字段名)
- 外键指向的表的字段,要求是
PRIMARY KEY
或者是UNIQUE
。- 表的类型是
innodb
,这样的表才支持外键
。- 外键字段的类型要和主键字段类型
一致
(长度可以不同)。- 外键字段的值,必须在主键字段中出现过,或者为
NULL
(为NULL的前提是外键字段允许为NULL)。- 一旦建立主外键的关系,数据
不能随意删除
。
5. 约束-check
用于强制数据必须满足的条件。
oracle
和sql server
均支持check
,但是mysql5.7
不支持check
,只做语法校验,但不会生效。
列名 类型 CHECK(check条件)
📌示例:
CREATE TABLE t8
(
`id` INT,
`name` VARCHAR(32) CHECK(`name` IN ('jack','tom'))
)
注意:mysql5.8开始支持check
九、自增长
自增长
即自动增长。
字段名 整型 AUTO_INCREMENT
-- 假设 字段1 为自增长
-- 方式一
INSERT INTO 表名(字段1,字段2,...)
VALUES(NULL,值,...) -- 将字段1对应的值设定为NULL,则默认从1开始自增长
-- 方式二
INSERT INTO 表名(字段2,...)
VALUES(值,...) -- 不给字段1添加值,默认从1开始自增长
-- 方式三
INSERT INTO 表名
VALUES(NULL,值,...) -- 和方式一差不多
💡说明:
- 一般来说自增长是和
PRIMARY KEY
配合使用。自增长
也可以单独使用(但需要配合一个UNIQUE)。自增长
修饰的字段为整型
(也可以为DOUBLE,很少这样使用)。- 自增长
默认从1开始
,也可以通过ALTER TABLE 表名 AUTO——INCREMENT = 新的开始值
来修改从哪个值开始。- 如果添加数据的时候,给自增长字段指定了值,则以指定的值为准。
- 自增都会从最大的那个编号开始自增。
十、索引
CREATE INDEX 索引名 ON 表名(列名);
1. 索引的原理
- 会形成一个索引的数据结构,比如二叉树。
- 索引的代价:
- 磁盘占用。
- 如果对表进行(插入、删除、修改),会对索引进行维护,对
速度
有影响。
2. 索引的类型
-
主键索引
-- 创建方式一: 创建表的时候添加PRIMARY KEY关键字 -- 创建方式二: ALTER TABLE 表名 ADD PRIMARY KEY (列名);
-
唯一索引
-- 创建方式一: CREATE UNIQUE INDEX 索引名 ON 表名 (列名); -- 创建方式二: 创建表的时候添加UNIQUE关键字
-
普通索引
-- 创建方式一: CREATE INDEX 索引名 ON 表名 (列名); -- 创建方式二: ALTER TABLE 表名 ADD INDEX 索引名 (列名);
-
全文索引
FULLTEXT
,适用于MyISAM
。一般开发,不适用Mysql自带的全文索引,而是使用:全文搜索Solr
和ElasticSearch(ES)
。
3. 查询索引
-- 方式一:
SHOW INDEX FROM 表名;
-- 方式二:
SHOW INDEXES FROM 表名;
-- 方式三:
SHOW KEYS FROM 表名;
-- 方式四:
DESC 表名;
4. 删除索引
DROP INDEX 索引名 ON 表名;
-- 删除主键索引
ALTER TABLE 表名 PRIMARY KEY;
🚫注意:
索引的修改:先删除,在添加
5. 创建索引的规则
较频繁
的作为查询条件字段,应该创建索引。唯一性太差
的字段不适合单独创建索引,即使频繁作为查询条件。更新非常频繁
的字段不适合创建索引。- 不会出现在
WHERE
子句中的字段不该创建索引。
十一、事务
事务
用于保证数据的一致性,它有一组相关的dml(插入、删除、修改)语句组成,该组的dml语句
要么全部成功,要么全部失败。
1. 事务和锁
当执行事务操作时(dml语句),mysql会在表上加`锁`,防止其他用户修改表的数据。mysql数据库控制台事务的几个重要操作。
start transaction
开始一个事务。savepoint
保存点名,设置保存点。rollback to
保存点名,回退事务。rollback
回退全部事务。commit
提交事务,所有的操作生效,不能回退。
📌示例:
CREATE TABLE `t1`
(
`id`INT,
`name` VARCHAR(23)
)
START TRANSACTION -- 开始一个事务
SAVEPOINT A -- 设置保存点A
INSERT INTO t1 VALUES(1,'KK');
SAVEPOINT B -- 设置保存点B
INSERT INTO t1 VALUES(1,'KK');
ROLLBACK TO 保存点名字 -- 此操作,会将表中的内容回滚到设置保存点的地方
ROLLBACK -- 此操作,会直接回滚到设置事务的地方
COMMIT -- 提交事务,执行此操作之后无法进行回滚
👆👆👆
- 使用
commit语句
可以提交事务。当执行了commit语句
后,会确认事务的变化、结束事务、删除保存点、释放锁,数据生效。当使用commit语句结束事务后,其它会话
将可以查看到事务变化后的新数据。- 如果不开始
事务
,默认情况下,dml操作是自动提交的,不能回滚
。- 如果开始一个事务,你没有创建
保存点
,你可以执行rollback
,默认就是回退到你事务开始的状态。- mysql的事务机制需要
innodb
的存储引擎,myisam
不好使。- 开始一个事务可以是
start transaction
,也可以是set autocommit=off
。
2. 隔离
事务隔离级别
:Mysql隔离级别定义了事务与事务之间的隔离程度。
Mysql隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
---|---|---|---|---|
读未提交(Read uncommitted) | √ | √ | √ | 不加锁 |
读已提交(Read committed) | × | √ | √ | 不加锁 |
可重复读(Repeatable read) | × | × | × | 不加锁 |
可串行化(Serializable) | × | × | × | 加锁 |
如果不考虑隔离性,可能会引发以下问题:
脏读
:当一个事务读取到另一个事务尚未提交
的改变(update,insert,delete)时,产生脏读。不可重复读
:同一查询在同一事物中多次进行,由于其他提交事务
所做的修改或删除
,每次返回不同的结果集,此时发生不可重复读。幻读
:同一查询在同一事务中多次进行,由于其他提交事务
所作的插入操作
,每次返回不同的结果集,此时发生幻读。
事务隔离相关的操作
1. 查看当会话隔离级别
-- Mysql 5.7 版本
SELECT @@tx_isolation;
-- Mysql 5.8 版本
SELECT @@transaction_isolation;
2. 查看系统当前隔离级别
-- Mysql 5.7 版本
SELECT @@global.tx_isolation;
-- Mysql 5.8 版本
SELECT @@global.transaction_isolation;
3. 设置当前会话隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 要设置的隔离级别;
4. 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 要设置的隔离级别;
Mysql
默认的事务隔离级别是repeatable read
,一般情况下,没有特殊要求,没有必要修改(因为该级别可以满足绝大部分项目需求)。
5. 全局修改
修改mysql.ini
配置文件,在最后加上
transaction-isolation
= 要设置的隔离级别
要设置的隔离级别有:READ-UNCOMMITTED
,READ-COMMITTED
,REPEATABLE-READ
,SERIALIZABLE
。
3. Mysql事务ACID
原子性(Atomicity)
:原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。一致性(Consistency)
:事务必须使数据库从一个一致性状态变换到另外一个一致性状态。隔离性(lsolation)
:事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。持久性(Durability)
:持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
十二、存储引擎
1. 基本介绍
- MySQL的表类型由
存储引擎(Storage Engines)
决定,主要包括MyISAM、innoDB
、Memory
等。 - MySQL数据表主要支持六种类型,分别是:
CSV
、Memory
、ARCHIVE
、MRG
、MYISAM
、InnoBDB
。 - 这六种又分为两类,一类是
事务安全型(transaction-safe)
,比如:InnoDB;其余都属于第二类,称为非事务安全型
。
2. 查看所有存储引擎
SHOW ENGINES;
3. 修改存储引擎
ALTER TABLE 表名 ENGINE = 引擎名;
4. 基本引擎介绍
MyISAM
不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求InnoDB
存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MylSAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。MEMORY
存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH索引。但是一旦服务关闭,表中的数据就会丢失掉,表的结构还在。
引擎 | 特点 |
---|---|
Innodb | 支持事务、支持外键、支持行级锁 |
myisam | 添加速度快、不支持事务和外键、支持表级锁 |
memory | 数据存储在内存中(关闭Mysql服务,数据丢失,但表结构还在)、执行速度很快(没有IO读写)、默认支持索引(Hash表) |
如何选择存储引擎❓
- 如果你的应用不需要事务,处理的只是基本的CRUD操作,那么
MylSAM
是不二选择,速度快 - 如果需要支持事务,选择
lnnoDB
。 Memory
存储引擎就是将数据存储在内存中,由于没有磁盘I./O的等待,速度极快。但由于是内存存储引擎,所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态)。
十三、视图
1. 简介
视图是一个虚拟表
,其内容由查询定义。同真实的表一样,视图包含列,其数据来自对应的真实表。视图是根据基表(可以是多个基表)来创建的,视图是虚拟的表。
2. 创建视图
CREATE VIEW 视图名
AS
SELECT 列名,...
FROM 表名;
3. 查看视图
DESC 视图名;
-- 查看视图的创建
SHOW CREATE VIEW 视图名;
4. 删除视图
DROP VIEW 视图名,...;
💡关于视图的说明:
- 创建视图后,在数据库里边,对应视图只有一个视图结构文件(后缀名为
.frm
)。- 视图的数据变化会影响
基表
,基表的数据变化也会影响到视图。- 视图中可以再使用视图。
5. 视图的特性
安全
。一些数据表有着重要的信息。有些字段是保密的,不能让用户直接看到。这时就可以创建一个视图,在这张视图中只保留一部分字段。这样,用户就可以查询自己需要的字段,不能查看保密的字段。性能
。关系数据库的数据常常会分表存储,使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦,效率相对也比较低。如果建立一个视图,将相关的表和字段组合在一起,就可以避免使用JOIN查询数据。灵活
。如果系统中有一张旧的表,这张表由于设计的问题,即将被废弃。然而,很多应用都是基于这张表,不易修改。这时就可以建立一张视图,视图中的数据直接映射到新建的表。这样,就可以少做很多改动,也达到了升级数据表的目的。
十四、Mysql用户管理
Mysql
中的用户,都存储在系统数据库Mysql中的user表中。
1. user表部分字段说明
host
:允许登录的“位置”。localhost
表示该用户只允许本机登录,也可以指定ip地址,比如:192.168.1.100。user
:用户名。authentication_string
:密码,是通过mysql的password()
函数加密之后的密码。
2. 创建用户
CREATE USER '用户名'@'允许登录位置' IDENTIDIED BY 密码;
不同的数据库用户,登录到
DBMS
后,根据相应的权限,可以操作的数据库和数据对象(表,视图,触发器)都不一样。
3. 删除用户
DROP USER '用户名'@'允许登录的地址';
4. 用户修改密码
-- 修改自己密码(5.7版本)
SET PASSWORD = PASSWORD('密码');
-- 5.8版本
ALTER USER '用户'@'登录位置' IDENTIFIED BY '密码';
-- 修改他人的密码(前提是需要有修改用户密码的权限)(5.7版本)
SET PASSWORD FOR '用户'@'登陆位置' = PASSWORD('密码');
-- 5.8版本
ALTER USER '用户'@'登录位置' IDENTIFIED BY '密码';
十五、Mysql权限管理
1. 赋予权限
GRANT 权限列表 ON 库.对象名 TO '用户名'@'登录位置' [IDENTIFIED BY '密码'];
- 权限列表,多个权限用逗号隔开。
GRANT SELECT ON ...
GRANT SELECT, DELETE, CREATE ON...
GRANT ALL ON...
//表示赋予该用户在该对象上的所有权限。
*.*
:代表本系统中苏哦有的数据库的所有对象(表、视图、存储过程)。库.*
:表示某个数据库中的苏哦有数据对象(表、视图、存储过程)。IDENTIFIED BY
可以省略,也可以写出。- 如果用户存在,就是修改该用户的密码。
- 如果该用户不存在,就是创建该用户。
2. 回收用户权限
REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登录位置';
3. 权限生效指令
-- 如果权限没有生效,可以执行下面命令
FLUSH PRIVILEGES;
4. 最后的说明
- 在创建用户的时候,如果不指定
Host
,则为%
。%表示是所有的IP都有连接权限。CREATE USER XXX;
CREATE USER 'XXX'@'192.168.1.%'
这表示XXX用户在192.168.1.*的ip都可以登录mysql。- 在删除用户的时候,如果
host
不是%
。需要明确指定’用户'@'host值'
。
十六、总结
以上就是所有关于
Mysql-SQL语句
的全部内容,篇幅有点长,如果文章
中有错误,还请留言📫或者私信我🙏。
最后希望大家多多 关注+点赞+收藏^_^,你们的鼓励是我不断前进的动力!!!
感谢感谢~~~🙏🙏🙏