Mysql-SQL语句的使用二（超详细）

最新推荐文章于 2024-04-20 13:29:20 发布

艺术留白

最新推荐文章于 2024-04-20 13:29:20 发布

阅读量215

点赞数 1

分类专栏： JavaWeb Mysql 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/qq_35947021/article/details/127750836

版权

JavaWeb 同时被 2 个专栏收录

13 篇文章 3 订阅

订阅专栏

Mysql

9 篇文章 0 订阅

订阅专栏

目录
一、流程控制语句
 二、查询增强
 三、分页查询
 四、多表查询
 五、表复制
 六、合并查询
 七、外连接
 八、约束
 九、自增长
 十、索引
 十一、事务
 十二、存储引擎
 十三、视图
 十四、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语句的全部内容，篇幅有点长，如果文章中有错误，还请留言📫或者私信我🙏。

最后希望大家多多关注+点赞+收藏^_^，你们的鼓励是我不断前进的动力！！！
感谢感谢~~~🙏🙏🙏

艺术留白

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mysql-SQL语句的使用二（超详细）

MySQL的表类型由存储引擎(Storage Engines）决定,主要包括、Memory等。MySQL数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE、MRG、MYISAM、InnoBDB。这六种又分为两类，一类是事务安全型(transaction-safe)，比如:InnoDB;其余都属于第二类，称为非事务安全型。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。
复制链接

扫一扫