文章目录
结构化查询语言 - SQL
Structured Query Language - 结构化查询语言
警告
- 数据库中所有字符串类型必须使用 单引号 ,不能使用 双引号 ;日期类型也要使用 单引号
内容定义
定义 | 解释 |
---|---|
db_name | 数据库名 |
tab_name | 数据表名 |
tab_name_new | 新数据表名 |
charset_name | 字符集名 |
field_name | 字段名 |
field_name_new | 新字段名 |
field_type | 字段类型 |
ct_name | 约束名 |
ct_type | 约束类型:PRIMARY KEY、UNIQUE |
ip_addr | ip地址 |
field_list | 字段列表,包括* |
数据定义语言 - DDL
Data Definition Language - 数据定义语言
数据库
基本操作
-
查询数据库
SHOW DATABASE;
-
切换数据库
USE db_name;
-
新建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [CHARSET=utf8];
-
删除数据库
DROP DATABASE [IF EXISTS] db_name;
-
修改数据库
ALTER DATABASE db_name CHARACRER SET utf8;
数据表
数据类型
分类 | 数据类型 | 含义(有符号) | 备注 |
整形 | tinyint(m) | 1个字节 范围(-128~127) | m 显示位宽,不影响数据范围 |
mediumint(m) | 2个字节 范围(-32768~32767) | ||
smallint(m) | 3个字节 范围(-8388608~8388607) | ||
int(m) | 4个字节 范围(-2147483648~2147483647) | ||
bigint(m) | 8个字节 范围(+-9.22*10的18次方) | ||
浮点型 | float(m,d) | 单精度浮点型 8位精度(4字节) m总位数,d小数位 | m,d 显示位宽,不影响数据范围 |
double(m,d) | 双精度浮点型 16位精度(8字节) m总位数,d小数位 | ||
float(m,d) | 单精度浮点型 8位精度(4字节) m总位数,d小数位 | ||
字符串 | char(n) | 固定长度,最多255个字符 | n 数据长度 |
varchar(n) | 固定长度,最多65535个字符 | ||
tinytext | 可变长度,最多255个字符 | ||
text | 可变长度,最多65535个字符 | ||
mediumtext | 可变长度,最多2的24次方-1个字符 | ||
longtext | 可变长度,最多2的32次方-1个字符 | ||
二进制 | _BLOB | 存储的数据只能整体读出 | - |
_TEXT | 可以指定字符集,英文存储区分大小写 | ||
日期时间 | date | 日期 '2008-12-2' | - |
time | 时间 '12:25:36' | ||
datetime | 日期时间 '2008-12-2 22:06:44' | ||
timestamp | 自动存储记录修改时间 |
属性关键字
关键字 | 含义 |
---|---|
NULL | 数据列可包含NULL值 |
ZEROFILL | 零填充 |
DEFAULT | 默认值 |
UNSIGNED | 无符号 |
NOT NULL | 非空约束,数据列不允许包含NULL值 |
PRIMARY KEY | 主键约束,非空、唯一、被引用(外键) |
AUTO_INCREMENT | 自增约束,自动递增,适用于整数类型 |
UNIQUE | 唯一约束,唯一、不可重复,可为NULL |
FOREIGN KEY | 外键约束,必须是引用一张表的主键,可重复,可为空,可引用多个 |
CHARACTER SET charset_name | 指定一个字符集 |
基本操作
-
查看当前数据库所有表
SHOW TABLES;
-
查看表索引
SHOW INDEX FROM tab_name;
-
创建表
CREATE TABLE tab_name ( field_name field_type [ props ], ... [[CONSTRAINT ct_name] ct_type field_name ] );
-
查看创建表的SQL语句
SHOW CREATE TABLE tab_name;
-
查看表结构
DESC tab_name;
-
删除表
DROP TABLE tab_name;
-
清空数据
TRUNCATE TABLE tab_name; # 先DROP,再CREATE,无法回滚
-
修改表 - ALTER TABLE
-
增加新字段
ALTER TABLE tab_name ADD( field_name field_type [props], );
-
修改字段类型
ALTER TABLE tab_name MODIFY field_name field_type;
-
修改字段定义
ALTER TABLE tab_name CHANGE field_name field_name_new field_type [ props ];
-
删除字段
ALTER TABLE tab_name DROP field_name;
-
修改表名
ALTER TABLE tab_name RENAME TO tab_name_new;
-
添加字段约束
ALTER TABLE tab_name ADD CONSTRAINT ct_name PRIMARY KEY( field_name ); # 添加主键约束 ALTER TABLE tab_name ADD CONSTRAINT ct_name UNIQUE( field_name ); # 添加唯一约束 ALTER TABLE tab_name ADD CONSTRAINT ct_name CHECK( field_name > 0 ); # 添加范围约束 ALTER TABLE tab_name ADD CONSTRAINT ct_name DEFAULT value FOR field_name; # 添加默认约束
-
约束操作
-
**PRIMARY KEY ** - 主键
# 建表时指定字段 主键 属性 CREATE TABLE tab_name( field_name field_type PRIMARY KEY, ... ); # 建表时通过 主键约束 指定字段 CREATE TABLE tab_name( field_name field_type, ... PRIMARY KEY( field_name ) ); # 修改表指定主键 ALTER TABLE tab_name ADD PRIMARY KEY( field_name ); # 修改表字段定义 ALTER TABLE tab_name CHANGE field_name field_name_new field_type PRIMARY KEY; # 删除主键 ALTER TABLE tab_name DROP PRIMARY KEY;
-
AUTO_INCREMENT - 自增
# 建表时指定字段 自增 属性 CREATE TABLE tab_name( field_name field_type PRIMARY KEY AUTO_INCREMENT, ... ); # 修改表字段定义时设置自增 ALTER TABLE tab_name ADD PRIMARY KEY( field_name ); # 删除自增 ALTER TABLE tab_name DROP PRIMARY KEY;
-
UNIQUE - 唯一
# 建表时指定字段 唯一 属性 CREATE TABLE tab_name( field_name field_type UNIQUE, ... ); # 建表时指定字段 联合唯一 属性 CREATE TABLE tab_name( field_name1 field_type, field_name2 field_type, ... UNIQUE KEY (field_name1, field_name2) ); # 修改表字段定义时设置自增 ALTER TABLE tab_name ADD PRIMARY KEY( field_name ); # 删除自增 SHOW CREATE TABLE tab_name; # 产看
数据库操作语言 - DML
Data Manipulation Language - 数据库操作语言
条件表达式运算符
条件表达式运算符 | 含义 |
---|---|
= | 等于 |
!= | 不等于 |
<> | 不等于,不包含 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN m AND n | [m,n] 左右闭区间; 字符串比较是从左到右按位按照其ASCII值来进行比较 |
IN(o,p,q) | 字段值为 o,p,q 中任一为真 |
NOT 条件表达式 | 非,取反 |
条件表达式 AND 条件表达式 | 与,两条真则真 |
条件表达式 OR 条件表达式 | 或,任一真为真 |
IS NULL | 字段内容为 NULL 则真 |
IS NOT NULL | 字段内容非 NULL 则真 |
LIKE | _:占位符;%:通配符 |
基本操作
-
插入数据 - INSERT INTO
-
插入所有字段数据
INSERT INTO tab_name VALUES( value1, value2, ... );
-
插入部分字段数据
INSERT INTO tab_name(field_name1, field_name2) VALUES(value1, value2);
-
-
更新数据 - UPDATE
UPDATE tab_name SET field_name=value,... WHERE 条件表达式
-
删除数据 - DELETE FROM
DELETE FROM tab_name [WHERE 条件表达式]
数据控制语言 - DCL
Data Control Language - 数据控制语言
用户管理
约定
- 一个项目创建一个用户,一个项目对应的数据库只有一个
基本操作
-
创建用户
CREATE USER user_name@ip_addr IDENTIFIED BY 'pass_wd'; # ip_addr -> '%' 为任意ip_addr
-
删除用户
DROP USER user_name@ip_addr
权限管理
权限表
Privilege | Grant Table Column | Context |
---|---|---|
ALL [PRIVILEGES] | Synonym for “all privileges” | Server administration |
ALTER | Alter_priv | Tables |
ALTER ROUTINE | Alter_routine_priv | Stored routines |
CREATE | Create_priv | Databases, tables, or indexes |
CREATE ROUTINE | Create_routine_priv | Stored routines |
CREATE TABLESPACE | Create_tablespace_priv | Server administration |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | Tables |
CREATE USER | Create_user_priv | Server administration |
CREATE VIEW | Create_view_priv | Views |
DELETE | Delete_priv | Tables |
DROP | Drop_priv | Databases, tables, or views |
EVENT | Event_priv | Databases |
EXECUTE | Execute_priv | Stored routines |
FILE | File_priv | File access on server host |
GRANT OPTION | Grant_priv | Databases, tables, or stored routines |
INDEX | Index_priv | Tables |
INSERT | Insert_priv | Tables or columns |
LOCK TABLES | Lock_tables_priv | Databases |
PROCESS | Process_priv | Server administration |
PROXY | See proxies_priv table | Server administration |
REFERENCES | References_priv | Databases or tables |
RELOAD | Reload_priv | Server administration |
REPLICATION CLIENT | Repl_client_priv | Server administration |
REPLICATION SLAVE | Repl_slave_priv | Server administration |
SELECT | Select_priv | Tables or columns |
SHOW DATABASES | Show_db_priv | Server administration |
SHOW VIEW | Show_view_priv | Views |
SHUTDOWN | Shutdown_priv | Server administration |
SUPER | Super_priv | Server administration |
TRIGGER | Trigger_priv | Tables |
UPDATE | Update_priv | Tables or columns |
USAGE | Synonym for “no privileges” | Server administration |
权限操作
-
查看用户权限
SHOW GRANT FOR user_name@ip_addr;
-
授予权限
GRANT 权限清单 ON db_name.tab_name TO user_name@ip_addr; # db_name.* -> 数据库 db_name 下所有表
-
撤销权限
REVOKE 权限清单 ON db_name.tab_name FROM user_name@ip_addr;
-
刷新权限
flush privileges;
数据查询语言 - DQL
Data Query Language - 数据查询语言
基本查询
SELECT *
FROM tab_name
WHERE 条件表达式
排序 - ORDER BY
SELECT *
FROM tab_name
WHERE 条件表达式
ORDER BY field_name [ASC | DESC] [, field_name [ASC | DESC] ... ]
# ASC 升序, DESC 降序
# 如果 排序字段出现相等的值,则该部分按后续排序规则进行排序
字段控制
-
完全重复的记录只显示一次
SELECT DISTINCT field_list FROM tab_name; # 当查询的记录出现完全一致时,只显示一次,多数出现在只查询部分列的场景
-
字段运算
-
字段类型为 数量类型 时可进行 四则运算
SELECT field_name + [-,*,/] value FROM tab_name
-
字段类型为 字符串类型 时可进行 连接运算
SELECT CONCAT(str,str,...) FROM tab_name # 可将合适位置的 str 换成 字段名 进行字符串拼接
-
NULL 值转换
SELECT IFNULL(field_name,value) FROM tab_name; # 将字段值为NUll的用value替换
-
给字段起别名
SELECT field_name as field_name_new FROM tab_name; # field_name 可为 SELECT 函数产生的列名; # field_name field_name_new <=> field_name as field_name_new
-
-
聚合函数
-
COUNT( ) - 计数
# 返回表中总行数,不忽略 NULL,COUNT(1)效果类似 SELECT COUNT(*) FROM tab_name; # 返回字段除值为 NULL 外的总行数 SELECT COUNT( field_name ) FROM tab_name; # 返回字段值不重复,且忽略 NULL的总行数 SELECT COUNT( DISTINCT field_name ) FROM tab_name;
-
MAX( field_name ) - 字段最大值
SELECT MAX( field_name ) FROM tab_name
-
MIN( field_name ) - 字段最小值
SELECT MIN( field_name ) FROM tab_name
-
SUM( field_name ) - 字段和
SELECT SUN( field_name ) FROM tab_name #忽略 NULL,NULL + 1 = NULL
-
AVG( field_name ) - 字段平均值
SELECT AVG( field_name ) FROM tab_name; #忽略 NULL 行
-
分组查询 - GROUP BY
SELECT field_name [, 聚合函数 ]
FROM tab_name
WHERE 条件表达式
GROUP BY field_name
# 查询的字段应该和分组字段保持一致,可组合的列名为聚合函数,在逻辑层面才有意义
LIMIT - MySQL方言
SELECT field_list
FROM tab_name
WHERE 条件表达式
LIMIT m,n
# 查询结果从下标m开始,取n行,类似分页效果
多表查询
-
合并结果集
对两个结果集进行并集操作
-
SELECT 语句列的数量、顺序必须相同,字段的类型也要求相似( 合并的是 SELECT 的结果集合 );
-
结果集的列名为第一个SELECT语句中的字段名;
-
可带 WHERE 和 ORDER BY 关键字:
SELECT field_name FROM tab_name UNION SELECT field_name FROM tab_name WHERE 条件表达式 ORDER BY field_name # UNION 不包括重复行,相当于 DISTINCT 属性 # UNION ALL 包括重复的记录
-
-
连接查询
-
内连接
只显示满足连接条件的结果集
# 标准 - INNER JOIN SELECT field_list FROM tab_name_1 alias_name_1 INNER JOIN tab_name_2 alias_name_2 ON alias_name_1.field_name = alias_name_2.field_name # 自然 - NATURAL JOIN (自动寻找两张表相同的字段,筛选笛卡儿积。) SELECT field_list FROM tab_name_1 alias_name_1 NATURAL JOIN tab_name_2 alias_name_2 # MySQL 方言,使用 "," 连接表,通过 WHERE 筛选条件 SELECT field_name FROM tab_name_1, tab_name_2 WHERE 条件表达式
-
外连接
一主一从,当主表存在不满足连接条件的记录,用NULL补全从表显示
# LEFT OUTER JOIN - 左表为主表,左表存在右表不存在的记录时,右表查询字段用NULL补全 SELECT field_list FROM tab_name_1 LEFT OUTER JOIN tab_name_2 ON alias_name_1.field_name = alias_name_2.field_name # RIGHT OUTER JOIN - 右表为主表,左表存在右表不存在的记录时,左表查询字段用NULL补全 SELECT field_list FROM tab_name_1 RIGHT OUTER JOIN tab_name_2 ON alias_name_1.field_name = alias_name_2.field_name
-
自然连接
# 内连接 NATURAL JOIN # 外连接 NATURAL LEFT OUTER JOIN NATURAL RIGHT OUTER JOIN
-
-
子查询
查询中有查询
-
WHERE 后作为条件
-
单行单列
SELECT field_list FROM tab_name WHERE field_name [ =, >, <, >=, <=, != ] ( SELECT ... FROM ... WHERE... )
-
多行单列
SELECT field_list FROM tab_name WHERE field_name [ IN, ALL, ANY ] ( SELECT ... FROM ... WHERE... )
-
单行多列
SELECT field_list FROM tab_name WHERE field_name IN( SELECT ... FROM ... WHERE... )
-
-
FROM 后作为数据表,需要使用别名
-
多行多列
SELECT alias.field_list FROM ( SELECT ... FROM ... WHERE... ) alias WHERE 条件表达式
-
-
概念模型
序号 | 概念 | 理解 |
---|---|---|
1 | 一对多 | 一张表的一个字段的值取值范围是另一张表的一个字段值 |
2 | 多对多 | 两张表相互存在一对多关系,通过中间表建立关系 |
3 | 一对一 | 副表的主键的取值范围是另一张表的主键(唯一,非空,被引用) |
维护
编码
-
查看数据库编码
SHOW VARIABLES LIKE 'char%';
Variable_name Value 标注 character_set_client utf8mb4 所有客户端发过来的数据,都以 Value 设定的字符集存储 character_set_connection utf8mb4 character_set_database utf8mb4 character_set_filesystem binary character_set_results utf8mb4 服务器把所有查询结果以 Value 设定的字符集发给客户端 character_set_server utf8mb4 character_set_system utf8mb3 character_sets_dir /usr/share/mysql/charsets/ -
临时改编码
set character_set_client=gbk; set character_set_results=gbk; # 只在当前窗口有效
-
永久改编码
[mysql] default-character-set=gbk; #同时设定 client,results,connection 字符集
备份与恢复
- 备份
# 通过 shell命令 备份 mysqldump -uroot -p123 db_name > sql_path
- 恢复
# 通过 shell命令 恢复 mysql -uroot -p123 db_name < sql_path # 通过 MySQL客户端 恢复 MySQL > USE db_name; SOURCE sql_path;