MySQL SQL 基础

SQL 是一种结构化查询语言，是关系型数据库通用的命令，遵循统一标准（SQL92 标准）。

一、DDL（数据定义语言）

1. 库定义

（1）建库

在 Unix 下，数据库名和表名区分大小写（因为库和表对应系统的目录或文件，而系统的目录和文件名区分大小写）。

create database zabbix charset utf8mb4 collate utf8mb4_bin;

（2）修改字符集

字符集只能从小往大改，如 utf8 -> utf8mb4。

alter database test charset utf8mb4;

（3）查看库

show databases;  -- 查看所有数据库
show create database zabbix;  -- 查看指定数据库的创建语句

（4）删库

drop database zabbix;

（5）建库规范

库名使用小写
库名不数字开头
库名不能是数据库内部关键字
建库必须设置字符集

2. 表定义

（1）建表

create table test.student (
 id     int               primary key auto_increment  comment '学号'
,sname  varchar(255)      not null                    comment '姓名'
,age    tinyint           unsigned not null default 0 comment '年龄'
,gender enum('m','f','n') not null default 'n'        comment '性别'
,intime datetime          not null default now()      comment '入学时间'
) engine innodb charset utf8mb4
;

（2）查看表

show tables;  -- 查看当前库下所有表
show create table student;  -- 查看指定表的创建语句
desc student;  -- 查看指定表的结构

（3）复制表结构

create table test.stu like test.student;

（4）修改表

增加列（Alter 增加列会导致锁表）

alter table test.student add qq varchar(20) not null comment 'QQ号' after gender;

修改数据类型

alter table test.student modify qq varchar(64) not null comment 'QQ号' after gender;

-- change 可同时修改数据类型、字段名、字段顺序；修改顺序时，放第一列用 first，放某列后用 after。
alter table test.student change gender sex char(1) not null DEFAULT 'n' COMMENT '性别' first;

删除列
```
alter table test.student drop qq;
```

（5）删表

drop table stu;

（6）建表规范

表名
- 以小写字母开头，不能数字开头
- 不能是保留字符
- 使用和业务相关的表名，见名知义
数据类型
- 选择合适的数据类型和长度
- 每列设置为 not null + default
- 主键列尽量是无关数字列，最好自增
- 数字用 0 填充，字符使用有效字符
- enum 类型只能保存字符串，不能保存数字
存储引擎和字符集
- 表必须设置存储引擎和字符集

二、DML（数据操作语言）

1. insert（插入数据）

插入单行

-- insert t1 values(2, 'jim'); 这种简化写法不规范，不推荐。
insert into t1(id, name) values(1, 'tom');

插入多行

insert into t1(id, name) values(3,'tom'),(4,'jim');

2. update（更新数据）

update t1 set name = 'smith' where id = '3';

3. delete（删除数据）

delete from t1 where id = 4;

4. delete 与 truncate 区别

truncate：物理删除，直接释放空间
delete：逻辑删除，空间不会立即释放，易产生碎片

长期使用 delete 的表会产生大量碎片，可通过逻辑导出数据、truncate 表、再导入数据的方式消除碎片。

三、DCL（数据控制语言）

1. 授予权限

语法：grant 权限 on 作用目标 to 用户 identified by 密码 with grant option;

grant all on test.* to 'yingzai'@'172.16.0.%' identified by 'yingzai';
grant select,insert,update,delete,create on test.* to 'yingzai'@'172.16.0.%' identified by 'yingzai' with grant option;

2. 查看权限

show grants for yingzai@'172.16.0.%';

3. 收回权限

revoke grant option on test.* from yingzai@'172.16.0.%';

四、DQL（数据查询语言）

1. select 单独使用

查看参数/变量

select @@port;
show variables like 'port%';

查看函数

select database();  -- 查看当前数据库
-- 更多信息可查看帮助 help information functions;

2. 通用语法

select * from t1 limit 5;  -- 查询 t1 表的前 5 行数据

3. 聚合函数：group_concat

（1）语法

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

（2）举例

mysql> select group_concat(name order by name desc separator ' ') from t1 group by id;
+-----------------------------------------------------+
| group_concat(name order by name desc separator ' ') |
+-----------------------------------------------------+
| tom                                                 |
| jim                                                 |
| tom smith                                           |
| jim                                                 |
+-----------------------------------------------------+

4. 字符串函数：concat

（1）语法

concat(str1, str2,...)  -- 拼接多个字符串

（2）举例

mysql> select concat(name, '\'s id is ', id)  from t1;
+--------------------------------+
| concat(name, '\'s id is ', id) |
+--------------------------------+
| tom's id is 1                  |
| jim's id is 2                  |
| smith's id is 3                |
+--------------------------------+

5. limit（限制查询结果行数）

（1）语法

limit m,n：跳过前 m 行，显示 n 行（即显示 m+1 到 m+n 行）
limit m：显示前 m 行
limit n offset m：等同于 limit m,n，跳过前 m 行，显示 n 行

（2）举例

limit 3;  -- 显示前 3 行
limit 3,4;  -- 跳过前 3 行，显示 4 行（即第 4 到第 7 行）
limit 4 offset 3;  -- 等同于 limit 3,4，显示第 4 到第 7 行

6. 合集（合并查询结果）

union all  -- 合并结果，不去重、不排序
union   -- 合并结果，去重且排序

五、元数据查询

数据库的元数据存储在基表中，可通过 information_schema 库中的专用视图查询。

1. tables 表（存储表的元数据）

mysql> use information_schema ;
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

2. 统计某张表占用空间大小

计算公式：平均行长度 × 行数 + 索引长度

mysql> select AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH from tables where table_name = 't1';
+--------------------------------------------+
| AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH |
+--------------------------------------------+
|                                      16380 |
+--------------------------------------------+

六、show 命令（封装好的元数据查询命令）

查看表相关：
- show tables;（查看表名）
- show create table t1;（查看建表语句）
- show index from t1;（查看表索引）
查看数据库相关：
- show databases;（查看数据库名）
- show create database test;（查看建库语句）
查看连接情况：show processlist;
查看字符集和校对规则：
- show charset;（字符集）
- show collation;（校对规则）
查看权限：show grants for yingzai@'172.16.0.%';
查看参数信息：show variables like '%port%';
查看存储引擎：
- show engines;（支持的存储引擎）
- show engine innodb status\G;（innodb 详细状态）
查看日志相关：
- show binary logs;（二进制日志列表）
- show binlog events in 't1';（二进制日志事件）
- show master status;（当前库二进制日志信息）
- show relaylog events in '';（中继日志事件）
查看从库状态：show slave status\G;
查看数据库整体状态：show status like '%log%';

红炉点雪

MySQL SQL 基础

MySQL SQL 基础

一、DDL（数据定义语言）

1. 库定义

（1）建库

（2）修改字符集

（3）查看库

（4）删库

（5）建库规范

2. 表定义

（1）建表

（2）查看表

（3）复制表结构

（4）修改表

（5）删表

（6）建表规范

二、DML（数据操作语言）

1. insert（插入数据）

2. update（更新数据）

3. delete（删除数据）

4. delete 与 truncate 区别

三、DCL（数据控制语言）

1. 授予权限

2. 查看权限

3. 收回权限

四、DQL（数据查询语言）

1. select 单独使用

2. 通用语法

3. 聚合函数：group_concat

（1）语法

（2）举例

4. 字符串函数：concat

（1）语法

（2）举例

5. limit（限制查询结果行数）

（1）语法

（2）举例

6. 合集（合并查询结果）

五、元数据查询

1. tables 表（存储表的元数据）

2. 统计某张表占用空间大小

六、show 命令（封装好的元数据查询命令）