MySQL-Note

MySQL使用cmd连接

mysql [-h 127.0.0.1] [-p 3306] -u root -p

需提前配置环境变量

关系型数据库(RDBMS)

建立在关系模型的基础上，有多张互相连接的二维表组成的数据库。

特点：

使用表储存数据，格式统一，便于维护
使用SQL语言操作，标准统一，使用方便

SQL

通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议大写。
注释：
- 单行注释：--注释内容或 #注释内容(MySQL特有)
- 多行注释：/* 注释内容 */

SQL分类

DDL-数据库操作

查询

查询所有数据库

show DATABASES;

查询当前数据库

select DATABASE();

创建

create DATABASE [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

如果不存在就创建，如果存在则不执行任何操作；默认字符集建议使用utf8mb4。

删除

drop DATABASE [if exists] 数据库名；

使用

use 数据库名;

表操作-查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show create table 表明;

表操作-创建

create table 表名(
    字段1 字段1类型[comment 字段1注释]，
    字段2 字段2类型[comment 字段2注释]，
    字段3 字段3类型[comment 字段3注释]，
    ......
    字段n 字段n类型[comment 字段n注释]
)[comment 表注释];

注意：最后一个字段后面没有逗号。

数据类型

数值类型

类型	大小（byte）	描述
Tintint	1	小整数值
Smallint	2	大整数值
mediumint	3	大整数值
Int or Integer	4	大整数值
Bigint	8	极大整数值
Float	4	单精度浮点数值
Double	8	双精度浮点数值
Decimal		小数值（精确定点数）

无符号可以附加unsigned。

double定义时需指定整体位数以及小数位数，如double(4,1)；即整体四位，小数位一位。

字符串类型

类型	大小（byte）	描述
Char	0-255	定长字符串
Varchar	0-65535	变长字符串
Tinyblob	0-255	不超过255个字符的二进制数据
Tinytext	0-255	短文本字符串
Blob	0-65535	二进制形式的的长文本数据
Text	0-65535	长文本数据
Mediumblob	0-16777215	二进制形式的中等长度文本数据
Mediumtext	0-16777215	中等长度文本数据
Longblob	0-4294967295	二进制形式的直达文本数据
Longtext	0-4294967295	极大文本数据

注意：如使用char(10)，即便定义一个字符，也会占用十个字符的空间，空余的位置用空格补全；

使用varchar，则会灵活变通所占空间大小。

类型	大小	范围	格式	描述
Data	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
Time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
Year	1	1901 至 2155	YYYY	年份值
Datatime	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
Timestamp	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

表操作-修改

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

删除字段

alter table 表名 drop 字段名;

修改表名

alter table 表名 rename to 新表名;

表操作-删除

删除表

drop table [if exists] 表名;

删除指定表，并重新创建该表

truncate table 表名;

DML-增删改查

添加数据

给指定字段添加数据

insert into 表名(字段名1，字段名2,...) values(值1,值2，...);

给全部字段添加数据

insert into 表名 values(值1,值2，...);

批量添加数据

insert into 表名(字段名1，字段名2,...) values(值1,值2，...),(值1,值2，...),(值1,值2，...);

insert into 表名 values(值1,值2，...),(值1,值2，...),(值1,值2，...);

注意：字符串和日期型数据应该包含在引号中。

修改数据

update 表名 set 字段名1 = 值1,字段名2 = 值2,...[where 条件];

删除数据

delete from 表名 [where 条件];

DQL-查询

语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序后字段列表
limit
	分页参数

基本查询

查询多个字段

select 字段1,字段2,字段3,... from 表名;

select * from 表名;

设置别名

select 字段1[as 别名1],字段2[as 别名2] ... from 表名;

去除重复记录

select distinct 字段列表 from 表名;

条件查询

select 字段列表 from 表名 where 条件列表;

条件

比较运算符	功能
<> 或 !=	不等于
between...and...	在某个范围之内（含最小、最大值）
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
is NULL	是NULL
and 或 &&	并且（多个条件同时成立）
or 或 \|\|	或者（多个条件任意一个成立）
not 或 !	非，不是

聚合函数

常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

使用方法

select 聚合函数(字段列表) from 表名;

注意：NULL值不参与计算

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

where和having的区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

执行顺序：where > 聚合函数 > having。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

select 字段列表 from 表名 order by 字段1 排序方法1，字段2 序方法2;

排序方法

ASC：升序（默认值）
DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，其实索引可以省略，直接简写为limit 10。

执行顺序

from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
select
	字段列表
order by
	排序字段列表
limit
	分页参数

DCL-控制

管理用户

查询用户

use mysql;
select * from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

如要该账户仅在当前主机访问数据库，主机名为'localhost'；

如使其可在任意主机访问数据库，主机名使用%进行通配。

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户

drop user '用户名'@'主机名';

权限控制

查询权限

show grants for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

remove 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

多个权限之间，使用逗号间隔。
授权时，数据库名和表名都可以使用*进行通配，代表所有。

qingmuy / mysql-note Goto Github PK

mysql-note's Introduction