MySQL笔记

山林牧歌
60 阅读7分钟

SQL语句

一、DDL语句-数据库操作

1、查询

查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();

2、创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则];

3、删除

DROP DATABASE [IF EXISTS] 数据库名;

4、使用

USE 数据库名;

二、DDL语句-表操作

1、查询

查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;

2、创建

CREATE TABLE 表名(
      字段1 字段1类型 [COMMENT 字段1注释],
      字段2 字段2类型 [COMMENT 字段2注释],
      字段3 字段3类型 [COMMENT 字段3注释],
      ……
      字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

3、修改

添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;

4、删除

删除表
DROP TABLE [IF EXISTS] 表名;
删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

三、DML语句

DML全称Data Manipulation Language（数据操作语言），用来对数据库表的数据记录进行增删改操作。

1、添加数据（INSERT）

给指定字段添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...);
给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...);
批量添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

2、修改数据（UPDATE）

UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,... [WHERE 条件];

修改语句的条件可以有，也可以没有

3、删除数据（DELETE）

DELETE FROM 表名 [WHERE 条件];

删除语句的条件可以有，也可以没有
删除语句不能删除某一个字段的值

四、DQL语句

DQL全称Data Query Language(数据查询语言)，用来查询数据库表的数据记录。

执行顺序
SELECT
      字段列表         4
FROM
      表名列表         1
WHERE
      条件列表         2
GROUP BY
      分组字段列表      3
HAVING
      分组后条件列表
ORDER BY
      排序字段列表      5
LIMIT
      分页参数         6

1、基础查询

查询多个字段
SELECT 字段1，字段2，字段3... FROM 表名;
SELECT * FROM 表名;
设置别名
SELECT 字段1 [AS 别名1]，字段2 [AS 别名2]... FROM 表名;
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

2、条件查询（WHERE）

SELECT 字段列表 FROM 表名 WHERE 条件列表;

比较运算符
BETWEEN...AND... 在某个范围之内（含最小、最大值）
IN(...) 在in之后的列表中的值，多选一
LIKE 占位符，模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL 是null

逻辑运算符
AND/&& 并且    OR/|| 或者     NOT/! 非，不是

3、聚合查询（count、max、min、avg、sum）

SELECT 聚会函数(字段列表) FROM 表名;

4、分组查询（GROUP BY）

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where是分组前过滤，having是分组后过滤

5、排序查询（ORDER BY）

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方法1,字段2 排序方法2;

ASC 升序（默认值）
DESC 降序
多字段排序，当第一个字段值相同时，会根据第二个字段值排序。

6、分页查询（LIMIT）

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示数
分页查询是数据库方言，MYSQL中是LIMIT
如果查询的是第一页数据，起始索引可以省略，简写为limit 10

五、DCL语句

DCL全称Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

1、管理用户

1、查询用户
USE mysql;
SELECT * FROM user;
2、创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '新密码';
3、修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
4、删除用户
DROP USER '用户名'@'主机名';

2、权限控制

1、查询权限
SHOW GRANTS FOR '用户名'@'主机名';
2、授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3、撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

六、函数

1、字符串函数

CONCAT(s1,s2,...sn)  字符串拼接，将s1,s2,...sn拼接成一个字符串
LOWER(str)  将字符串str全部转为小写
UPPER(str)  将字符串str全部转为大写
LPAD(str,n,pad)  左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)  右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)  去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)  返回从字符串str从start位置起的len个长度的字符串

2、数值函数

CEIL(x)  向上取整
FLOOR(x)  向下取整
MOD(x,y)  返回x/y的模
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数x的四舍五入的值，保留y位小数

3、日期函数

CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date,INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数

4、流程函数

IF(value,t,f) 如果value为true，返回t，否则返回f
IFNULL(value1,value2) 如果value1不为空，返回value1，否则返回value2

CASE WHEN [value1] THEN [res1] ... ELSE [default] END
如果value1为true，返回res1，否则返回default默认值

CASE [expr] WHEN [value1] THEN [res1] ... ELSE [default] END
如果expr的值为value1，返回res1，否则返回default默认值

七、约束

1、非空约束：NOT NULL
2、唯一约束：UNIQUE
3、主键约束：PRIMARY KEY （自增：AUTO_INCREMENT）
4、默认约束：DEFAULT
5、检查约束：CHECK
6、外键约束：FOREIGN KEY

八、多表查询

1、内连接

隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件 ...;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

2、外连接

左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

3、自连接

SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件 ...;
自连接查询，可以是内连接查询，也可以是外连接查询。

4、子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。 子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

（1）标量子查询（子查询结果为单个值）
     常用操作符：=、<>、>、>=、<、<=
（2）列子查询（子查询结果为一列，可以多行）
     IN   在指定的集合范围之内，多选一
     NOT  IN 不在指定的集合范围之内
     ANY  子查询返回列表中，有任意一个满足即可
     SOME 与ANY等同，使用SOME的地方可以使用ANY
     ALL  子查询返回列表的所有值都必须满足
（3）行子查询（子查询结果为一行，可以多列）
     常用操作符：=、<>、IN、NOT IN
（4）表子查询（子查询结果为多行多列）
     常用操作符：IN

5、联合查询

联合查询关键字-union, union all
union查询，就算把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

九、事务

1、事务四大特性

原子性、一致性、隔离性、持久性

2、并发事务问题

并发事务问题注释
脏读一个事务读到另外一个事务还没有提交的数据
不可重复读一个事务先后读取同一条记录，但是两次读取的数据不同
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在

3、事务隔离级别

隔离级别脏读不可重复读幻读
Read uncommitted✔✔✔
Read committed✘✔✔
Repeatable Read✘✘✔
Serializable✘✘✘
avatar
文章
阅读
粉丝