mysql 知识点总结

1 mysql8.0版本，修改数据库密码：

alter user 'root'@'localhost' identified by '新密码'; https://www.cnblogs.com/dahuzuidai/p/14870324.html

5.7版本的http://c.biancheng.net/view/7152.html

2 将 .sql文件导入数据库

https://blog.csdn.net/weixin_37887248/article/details/80897230

source sql文件的路径（注意你的文件路径要是复制来的，要将"\"全部换成“/”）

3 SHOW VARIABLES LIKE 'char%'; 查看编码

4 status; 查看当前数据库信息；

一基础

sql语句不区分大小写，数据库表名，列名是否区分大小写，依赖dbms以及配置；

1 进入数据库

mysql -h ipaddress -u username -p password

2 修改数据库密码

alter user 'root'@'localhost' identified by '新密码';

3 创建数据库并使用

DROP DATABASE IF EXISTS test;

create database test;

use test;

4 查看数据库

show databases;

5 关于注释

## 单行注释 --单行注释 /* 多行注释 */

二创建表和删除表

drop table if exists mytable;

create table mytable2(
-> id int not null auto_increment,
-> name varchar(30) not null,
-> sex int not null default 1,
-> date_time DATE NULL,
-> PRIMARY KEY(`id`));

DROP TABLE mytable;

三修改表结构

添加列

ALTER TABLE mytable

ADD col CHAR(20);

删除列

ALTER TABLE mytable

DROP COLUMN col;

修改列:

给id列增加auto_crement属性，这里只能用change

ALTER TABLE mytable

CHANGE id id int not null auto_increment;

修改数据类型，用modify即可

alter table mytable modify id varchar(10);

区别 change修改范围比较大（可以改名字），modify修改范围比较小

四查看表结构

DESC mytable;

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | NO   |     | NULL    |       |
| sex       | int         | NO   |     | 1       |       |
| date_time | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

查看某一列的结构

DESC mytable  name;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

五插入数据

普通插入

插入单行
INSERT INTO mytable(id,name)
VALUES(1,"liming");
插入多行
insert into mytable(name,sex)
values
("xiaoxiao",0),
("linlin",1);

插入检索出来的数据

INSERT INTO mytable(id,name)

SELECT id,name

FROM mytable2;

将一个表的内容插入到一个新表

CREATE TABLE newtable AS

SELECT * FROM mytable;

六更新

UPDATE mytable

SET name="黎明"

WHERE id=1;

七删除

删除指定的行的数据，不释放空间，删了id=1,下一个id从2开始，因为id=1的空间没释放，

DELETE FROM mytable

WHERE id = 1;

清空表数据，删除所有的行；释放空间，下一个id从1开始

TRUNCATE TABLE mytable;

注：使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

八查询

distinct

去重，重复的值，只出现一次，作用于所有的列，所有的值都相同才算相同。

SELECT DISTINCT col1,col2

FROM mytable;

limit

限制返回的行数，可以有两个参数，第一个参数为起始行，从0开始；第二个参数为返回的总行数。

返回前五行

SELECT *

FROM mytable

LIMIT 5;

SELECT *

FROM mytable

LIMIT 0,5;

返回3-5行

SELECT *

FROM mytable

LIMIT 2,3;

九排序

ASC 升序，默认；DESC降序；

可以按多个列进行排序，并且为每个列指定不同的排序方式

SELECT *

FROM mytable

ORDER BY col1 DESC, col2 ASC;

十过滤

1） WHERE 子句

SELECT *

FROM mytable

WHERE id =1;

where子句常用操作符

等于：=

小于：<

大于：>

不等于： <> 或 !=

小于等于：<= 或 !>

大于等于：>= 或 !<

在两者之间： BETWEEN.....AND

为NULL 值： IS NULL

AND 和 OR 用于连接多个过滤条件时，优先处理AND

IN 操作符用于匹配一组值，其后可以接一个SELECT子句，从而匹配子查询得到一组值。

NOT 操作符用于否定一个条件

2）通配符

通配符用在过滤语句中，只能用于文本字段

%匹配>=0个任意字符；

_匹配==1个任意字符；

[ ] 匹配字符集合，[ab]匹配a或b, ^可以对集合进行否定，[^ab]匹配除了ab意外的字符

使用LIKE 来进行通配符匹配

SELECT *

FROM mytable

WHERE name LIKE '[^ab]%' 匹配不以ab开头的任意文本

十一计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT a +b AS total

FROM mytable;

CONCAT() 用于连接两个字段；TRIM()可以去除字段的首尾空格

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

十二函数

各个DBMS的函数都不是相同的，以下只是MySql函数

汇总函数

AVG() 返回某列的平均值；COUNT() 返回某列的行数；MAX() 返回某列中的最大值；MIN() 返回某列的最小值；SUM()返回某列之和

AVG()忽略NULL行

SELECT AVG(DISTINCT col) AS avg_col

FROM mytable;

文本处理函数

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值

SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和时间处理函数

数值处理函数

十三分组 group by

把具有相同值的行放在同一组

可以对同一分组的数据使用汇总函数处理，求平均求个数等

自从mysql8.0开始，group by 不再支持隐式排序，之前的版本也不推使用荐隐式排序

mysql> select name,sex,count(sex) from mytable group by sex;
+--------+-----+------------+
| name   | sex | count(sex) |
+--------+-----+------------+
| liming |   1 |          2 |
| lala   |   0 |          1 |
+--------+-----+------------+
mysql> select sex,count(*) as num from mytable group by sex;
+-----+-----+
| sex | num |
+-----+-----+
|   1 |   2 |
|   0 |   1 |
+-----+-----+

group by 显示排序

mysql> select sex,count(*) as num from mytable group by sex order by num asc;
+-----+-----+
| sex | num |
+-----+-----+
|   0 |   1 |
|   1 |   2 |
+-----+-----+

where 过滤行，having 过滤分组，行过滤先于分组过滤

+----+----------+-----+-----------+
| id | name     | sex | date_time |
+----+----------+-----+-----------+
|  1 | liming   |   1 | NULL      |
|  2 | lala     |   0 | NULL      |
|  3 | wuge     |   1 | NULL      |
|  4 | xiaoxiao |   0 | NULL      |
|  5 | linlin   |   1 | NULL      |
+----+----------+-----+-----------+
 select id,name,sex,count(*) as num
    -> from mytable
    -> where id>1
    -> group by sex
    -> having sex=1;
+----+------+-----+-----+
| id | name | sex | num |
+----+------+-----+-----+
|  3 | wuge |   1 |   2 |
+----+------+-----+-----+

order by 在 group by之后，在having之后 where 在 group by 之前

null的行会单独分为一组

除了汇总字段，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；？？？？？？

十四子查询

子查询中只能返回一个字段数据

select *

from mytable1

where id in (select id from mytable);

十五连接

内连接 INNER JOIN（JOIN)

隐性连接：使用where语句，不怎么用了。
 where语句将两个表联系起来，where 和内连接得到的结果一样
 SELECT *
    -> FROM mytable,newtable
    -> WHERE mytable.id = newtable.id;
+----+----------+-----+-----------+----+--------+-----+-----------+
| id | name     | sex | date_time | id | name   | sex | date_time |
+----+----------+-----+-----------+----+--------+-----+-----------+
|  1 | liming   |   1 | NULL      |  1 | liming |   1 | NULL      |
|  2 | lala     |   0 | NULL      |  2 | haha   |   0 | NULL      |
|  3 | wuge     |   1 | NULL      |  3 | 五类   |   1 | NULL      |
|  4 | xiaoxiao |   0 | NULL      |  4 | 大力   |   0 | NULL      |
+----+----------+-----+-----------+----+--------+-----+-----------+
 显性连接：INNER JOIN、LEFT JOIN 、RIGHT JOIN、FULL JOIN
 
 内连接： INNER JOIN 或者简写 JOIN
 SELECT *
    -> FROM mytable
    -> INNER JOIN newtable
    -> ON mytable.id = newtable.id;
+----+----------+-----+-----------+----+--------+-----+-----------+
| id | name     | sex | date_time | id | name   | sex | date_time |
+----+----------+-----+-----------+----+--------+-----+-----------+
|  1 | liming   |   1 | NULL      |  1 | liming |   1 | NULL      |
|  2 | lala     |   0 | NULL      |  2 | haha   |   0 | NULL      |
|  3 | wuge     |   1 | NULL      |  3 | 五类   |   1 | NULL      |
|  4 | xiaoxiao |   0 | NULL      |  4 | 大力   |   0 | NULL      |
+----+----------+-----+-----------+----+--------+-----+-----------+
左连接：左表为参照进行连接，从左表返回所有的行，即使和右表不匹配
 SELECT a.id,a.name,b.id,b.name
    -> FROM mytable AS a
    -> LEFT JOIN newtable AS b
    -> ON a.id = b.id;
+----+----------+------+--------+
| id | name     | id   | name   |
+----+----------+------+--------+
|  1 | liming   |    1 | liming |
|  2 | lala     |    2 | haha   |
|  3 | wuge     |    3 | 五类   |
|  4 | xiaoxiao |    4 | 大力   |
|  5 | linlin   | NULL | NULL   |
+----+----------+------+--------+
右连接：从右表返回所有的行，即使和左表不匹配。
 SELECT a.id,a.name,b.id,b.name
    -> FROM mytable AS a
    -> RIGHT JOIN newtable AS b
    -> ON a.id = b.id;
+------+----------+----+--------+
| id   | name     | id | name   |
+------+----------+----+--------+
|    1 | liming   |  1 | liming |
|    2 | lala     |  2 | haha   |
|    3 | wuge     |  3 | 五类   |
|    4 | xiaoxiao |  4 | 大力   |
| NULL | NULL     |  6 | 哈哈   |
+------+----------+----+--------+
全连接：左右两表中所有的行都返回，两边不匹配的null补齐。

十六组合查询 UNION

把两个查询组合起来，要组合的查询，必须包含相同的列，表达式和聚合函数

默认去除重复的行，如需保留重复行，使用 UNION ALL

只能包含一个ORDER BY子句，且必须位于最后

SELECT E_Name FROM Employees_China
UNION SELECT E_Name FROM Employees_USA

十七视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。只是包含一组查询语句，在使用视图的时候，这组查询语句能够动态的检索数据。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

SELECT concat_col 
FROM myview;

重用SQL语句

简化复杂的SQL操作，

只使用表的部分而不是整张表

通过给用户访问视图的权限，保护数据的安全性

更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

十八存储过程

存储过程可以看作是一系列SQL操作的批处理

使用存储过程的好处：简单，安全，高性能

通过把处理封装在存储过程中，简化复杂的操作，防止出错，保证安全
代码复用，就像程序里边的方法，使用的时候直接调用，不需要重复编写。
提高性能，存储过程是预先编译，因此比单独使用SQL语句要快

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

存储过程包含 in out 和 inout 三种参数

给变量赋值都要用 select ……into……语句，每次只能给一个变量赋值，不支持集合操作

delimiter $  #这里是更改一下Mysql的分隔符，防止和存储过程内的;分隔符混淆

CREATE PROCEDURE myprocedure(out ret int)
	BEGIN
    	DECLARE y int;
        SELECT sum(col1)
        FROM mytable
        INTO y;
        SELECT y*y INTO ret;
    END $
    
DELIMITER; #这里再把分隔符改回来


#调用存储过程
CALL myprocedure(@ret);
SELECT @ret;

十九游标

Mysql中的游标只能用于存储过程和函数

游标是select语句检索出来的结果集，存储再Mysql服务器上

游标可以在检索出来的行中，根据需要前进或后退

使用游标

声明：这个过程没有检索数据，只是定义要使用的select语句

打开游标：这个过程用定义的select语句把数据检索出来

调用游标：对于填有数据的游标，根据需要取出各行

结束：结束使用游标，必须关闭

CREATE PROCEDURE myprocedure()
BEGIN
	--声明一个局部变量
    DECLARE o INT;
    --声明游标
	DECLARE ordernums CURSOR
    FOR
    SELECT order_num FROM orders;
    --打开游标
    OPEN ordernums;
    --使用游标
    FETCH ordernums INTO o;
    --关闭游标
    CLOSE ordernums;
END;
# 循环 检索数据
CREATE PROCEDURE myprocedure()
BEGIN
	--声明一个局部变量
    DECLARE o INT;
    --声明游标
	DECLARE ordernums CURSOR
    FOR
    SELECT order_num FROM orders;
    --声明一个句柄，循环结束标志,当SQLSTATE=‘02000’时，SET done=1;'02000'是一个不存在的条件，当没有行可以循环的时候，SQLSTATE=‘02000’
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    --打开游标
    OPEN ordernums;
    --循环检索数据
    REPEAT
    	FETCH ordernums INTO o;
    UNTIL done END REPEAT; --done为1时结束循环
    --关闭游标
    CLOSE ordernums;
END;
      
# 先声明局部变量，再声明游标，最后声明句柄。

二十触发器

触发器在执行 DELETE 、INSERT、UPDATE时出现

触发器必须指定在语句执行之前还是之后之心给，之前执行用BEFORE，之后执行用AFTER。

BEFORE用于数据验证和净化，AFTER用于审计跟踪，把修改情况记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

二十一数据类型

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT(size,d) DOUBLE(size,d) DECIMAL(size,d) 在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

字符串

主要是CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。

时间和日期

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS ，与时区无关注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()	2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高

参考资料：CS-Notes

https://www.w3school.com.cn/sql/sql_datatypes.asp

相关阅读:
php7与其他版本共存
 centos源码安装mysql5.7
禁用composer update命令
 lumen怎么得到当前Uri的控制器、Action、路由规则
 配置lumen的log为daily模式
 laravel如何打印orm封装的sql语句
 nginx 重写URL尾部斜杠
 Laravel的Nginx重写规则--让路由支持末尾加斜线
 laravel redis存数组并设置过期时间
 openresty
原文地址：https://www.cnblogs.com/wsnan/p/15602042.html

mysql 知识点总结

一 基础

二 创建表和删除表

三 修改表结构

四 查看表结构

五 插入数据

六 更新

七 删除

八 查询

九 排序

十 过滤

十一 计算字段

十二 函数

十三 分组 group by

十四 子查询

十五 连接

十六 组合查询 UNION

十七 视图

十八 存储过程

十九 游标

二十 触发器

二十一 数据类型

整型

浮点数

字符串

时间和日期

参考资料：CS-Notes

一基础

二创建表和删除表

三修改表结构

四查看表结构

五插入数据

六更新

七删除

八查询

九排序

十过滤

十一计算字段

十二函数

十三分组 group by

十四子查询

十五连接

十六组合查询 UNION

十七视图

十八存储过程

十九游标

二十触发器

二十一数据类型