Mysql必知必会

常用Sql：

<1>增：

1.新增数据

INSERT INTO sys_code_value(code_type,code_name,code_name_trans_id) VALUES(10060,'开|关',2945);

2.新增字段

ALTER TABLE `addonarticle17` ADD tags VARCHAR(255) DEFAULT '1'

意思是：修改 addonarticle17 表结构，增加 tags 字段，设置为 VARCHAR(255) 数据类型，默认值为 “1”。

3.批量新增：

INSERT INTO test_user (name, age, sex) VALUES

('张三', 18, '男'),

('赵四', 17, '女'),

('刘五', 16, '男'),

('周七', 19, '女'); 

 

<2>删：

①删除整张表（包含表的结构属性和索引）：

DROP TABLE power_device_point

②删除整张表数据（删除内容、释放空间但不删除定义；truncate 不能删除行数据,要删就要把表清空）：

truncate table power_device_point

③删除表数据：（删除内容不删除定义，不释放空间-->一行一行地删，效率低）：

DELETE FROM power_device_point WHERE point_name ="开关"

 

<3>改：

UPDATE sys_code_value SET code_value ='AFCI' WHERE code_id=11191

多个字段修改：

update table set a = 1, b= 2, c =3

 

<4>查：

SELECT * FROM power_device_point WHERE point_name ="开关"

连接查询：

①内连接：（返回两个表的交集部分）

SELECT * FROM a_table a inner join b_table b on a.a_id = b.b_id

②左连接：（全称是左外连接，是外连接中的一种； 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录）

SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id

③右连接：（全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。）

SELECT  * FROM a_table a right join b_table b on a.a_id = b.b_id

④全连接：（左外连接和右外连接的结果合并，其实就是左外链接和右外连接之和，其中不去除重复的使用union all，去除重复的使用union）

SELECT * FROM A LEFT OUTER JOIN B ON A.A_ID=B.A_ID 　UNION SELECT * FROM A RIGHT OUTER JOIN B ON A.A_ID=B.A_ID

⑤自连接：（把一个表作为两个表使用）

SELECT b.* from shopping a,shopping b where a.name='惠惠' AND a.price order by b.id

（表shopping a查询出惠惠对应的价格，小于shopping b中其他的数据）

 

⑥子查询：

①SELECT * FROM emp e WHERE e.sal=(SELECT MIN(sal) FROM emp)

 

②SELECT * FROM emp e WHERE e.job=( SELECT job FROM emp WHERE ename='ALLEN') AND e.sal>( SELECT sal FROM emp WHERE empno=7521)

 

更多子查询实例：https://www.cnblogs.com/fzxey/p/10896244.html

 

分组查询（group_by）

基本语法格式如下：

GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

• 属性名：是指按照该字段的值进行分组。
• HAVING 条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示。
• WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和

 

单独使用GROUP BY关键字进行分组

单独使用GROUP BY关键字，查询结果只显示一个分组的第一条记录

SELECT * FROM employee GROUP BY sex;

只显示每个分组的一条记录，意义不大，因此一般在使用集合函数时才使用GROUP BY关键字

 

GROUP BY关键字与GROUP_CONCAT()函数一起使用

一起使用时，每个分组中指定的字段值会全部显示出来

SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;

使用GROUP_CONCAT()函数可以很好的把分组情况表示出来

 

GROUP BY关键字与集合函数一起使用

与集合函数一起使用时，可以通过集合函数计算分组中的总记录、最大值、最小值等

SELECT sex,COUNT(sex) FROM employee GROUP BY sex;

聚合函数

COUNT()函数：用于统计记录的条数。 SUM()函数：用于计算字段的值的总和。 AVG()函数：用于计算字段的值的平均值。 MAX()函数：用于查询字段的最大值。 MIN()函数：用于查询字段的最小值

 

GROUP BY关键字与HAVING一起使用

使用GROUP BY关键字时，如果加上“HAVING 条件表达式”，则可以限制输出的结果。只有符合条件表达式的结果才会显示

SELECT sex,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=3;

“HAVING 条件表达式”可以限制查询结果的显示情况。

 

注意：

“HAVING 条件表达式”与“WHERE 条件表达式”都用于限制显示。但是，两者起作用的地方不同。

• WHERE 条件表达式：作用于表或者视图，是表和视图的查询条件。
• HAVING 条件表达式：作用于分组后的记录，用于选择符合条件的组

 

按照多个字段进行分组

SELECT * FROM employee GROUP BY d_id,sex;

分组过程中，先按照d_id字段进行分组，遇到d_id字段的值相等的情况时，再把d_id值相等的记录按照sex字段进行分组。

 

GROUP BY关键字与WITH ROLLUP一起使用

使用WITH ROLLUP时，将会在所有记录的最后加上一条记录。这条记录是上面所有记录的总和

SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex WITH ROLLUP;

GROUP_CONCAT(name)显示了每个分组的name字段的值。同时，最后一条记录的GROUP_CONCAT(name)列的值正好是上面分组name取值的总和

 

排序查询（Order By）

在MySQL中的Order By 有2种排序实现方式

• 利用有序索引获取有序数据
• 文件排序

 

使用order by，一般是用来，依照查询结果的某一列（或多列）属性，进行排序（升序：ASC；降序：DESC；默认为升序）。

当排序列含空值时：

ASC：排序列为空值的元组最后显示。

DESC：排序列为空值的元组最先显示。

SELECT * FROM dept ORDER BY deptno DESC

 

多个列属性排序

选择多个列属性进行排序，然后排序的顺序是，从左到右，依次排序。

如果前面列属性有些是一样的话，会先排序相同的，再按后面的列属性排序。（前提一定要满足前面的属性排序，因为在前面的优先级高）

SELECT * FROM dept ORDER BY deptno,emp DESC SELECT * FROM dept ORDER BY deptno DESC,emp DESC

 

特殊用法举例：

SELECT * FROM test_1 WHERE id in(9,5,8)

说明：库内顺序5，8，9按照此种搜索方式结果显示5，8，9；怎样才能显示成9，5，8呢

SELECT * FROM test_1 WHERE id IN (9,5,8) ORDER BY field(id,9,5,8)

 

 

distinct()： 字段去重处理

 

 

------

视图

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。

视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

作用：

①简化复杂查询。

②只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

创建 --

CREATE VIEW production AS SELECT 语句；

查看 --

SHOW CREATE VIEW production

删除 --

DROP VIEW production

 

------

存储过程

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，提高数据处理的效率。

创建 --

CREATE PROCEDURE production(@total) --()内可跟参数 BEGIN SELECT 语句; END;

执行调用 --

CALL production(@total) ; --()与创建时参数数量一致

查询 --

SELECT @total;

删除 --

DROP PROCEDURE production;

检查 --

SHOW CREATE PROCEDURE production;

获取所有存储过程信息 --

SHOW PROCEDURE STATUS

 

------

游标：是一个存储在mysql服务器上的数据库查询，他不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中数据，游标只能用于存储过程（和函数）

游标是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

创建 --

CREATE PROCEDURE production() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;

打开 --

OPEN ordernumbers

关闭 --

CLOSE ordernumbers

使用游标数据 --（fetch）

略

 

------

触发器

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

创建 --

需要4条信息：①唯一的触发器名；②触发器关联的表；③触发器应该响应的活动（删、增、改）；④触发器何时执行（处理之前或之后）

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'product added'

说明：

CREATE TRIGGER newproduct：创建触发器newproduct；

AFTER INSERT：触发器在insert之后执行；

products：表名

FOR EACH ROW ：表示对每个插入行执行；

 

删除 --(触发器不能修改，如需修改则必须删了重建)

DROP TRIGGER newproduct

 

------

事务处理：可以用来维护数据库的完整性，它保证成批的Mysql操作要么完全执行，要么完全不执行（4个特性：原子性、一致性、隔离性、持久性）

MyISAM不支持事务，InnoDB支持事务

事务（transaction）：指一组SQL语句

回退（rollback）：指撤销指定SQL语句的过程

提交（commit）：指将未存储的SQL语句结果写入数据库表

保留点（savepoint）：指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）--支持回退部分事务处理

创建 --

START TRANSACTION

回退举例：（回滚start之后的所有操作）

START TRANSACTION; DELETE FROM ordertotols; ROLLBACK;

 

提交举例：

START TRANSACTION; DELETE FROM ordertotols_1 where order_num =100; DELETE FROM ordertotols_2 where order_num =100; COMMIT;

两个表之间有关联，事务处理能保证两条都能删除成功，不存在一个删成功一个删失败情况

 

保留点举例：（不需要全部回退，只需要回退到指定位置的场景）

START TRANSACTION; DELETE FROM ordertotols_1 where order_num =100; SAVEPOINT delete1 DELETE FROM ordertotols_2 where order_num =100; ROLLBACK TO delete1

注：保留点在事务处理完整（执行一条ROLLBACK或COMMIT）后自动释放，不需要用RELEASE SAVEPOINT明确释放保留点

 

更改默认的提交行为：

SET autocommit = 0;

不管有没有COMMIT语句，设置autocommit = 0 （假）指示Mysql不自动提交更改。（直至autocommit为真为止）

 

------

数据库安全管理

创建用户：CREATE USER admin IDENTIFIED BY 'password'; 删除用户：DROP USER admin; 指派权限：GRANT SELECT ON sungrow.* TO admin; (查的权限) 撤销权限：REVOKE SELECT ON sungrow.* FROM admin; 更换口令：SET PASSWORD FOR admin =Password('password');

注：grant 和revoke可在几个层次上控制访问权限

①整个服务器，使用GRANT ALL和REVOKE ALL

②整个数据库，使用ON database.*

③特定的表，使用ON database.table

④特定的列

⑤特定的存储过程

 

----

索引

mysql对索引的定义就是：索引是帮助高效获取数据的数据结构

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

 

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

 

优点：搜索快

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

 

mysql索引类型

Mysql目前主要有以下几种索引类型：FULLTEXT（全文索引），HASH，BTREE（B+树索引），RTREE（空间索引）。

 

索引种类

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索

 

 

数据库存储引擎innerDB和myIsam的区别

myIsam innerDB

索引类型 非聚集索引 聚集索引

索引底层结构 b+树 b+树

是否支持事务 否 是

是否支持表锁 是 是

是否支持行锁 否 是

是否支持外键 否 是

是否支持全文索引 是 是

是否支持hash索引 否 否

适用操作 大量select 大量insert、delete和update下

 

注：Memory存储引擎默认采用hash索引