3-SQL 语言 -DDL,DML

本贴最后更新于 1372 天前,其中的信息可能已经时移世改

3 SQL 语言

3.1 关系型数据库的常见组件

  • 数据库:database
  • 表:table,行:row 列:column
  • 索引:index
  • 视图:view
  • 存储过程:procedure
  • 存储函数:function
  • 触发器:trigger
  • 事件调度器:event scheduler,任务计划
  • 用户:user
  • 权限:privilege

3.2 SQL 语言的兴起与语法标准

目前,所有主要的关系数据库管理系统支持某些形式的 SQL,大部分数据库至少遵守 ANSI SQL89 标准,虽然有这一标准的存在,但大部分的 SQL 代码在不同的数据库系统中并不具有完全的跨平台性业内标准

微软和 Sybase 的 T-SQL,Oracle 的 PL/SQL

3.2.1 SQL 语言规范

在数据库系统中,SQL 语句不区分大小写,建议用大写

SQL 语句可单行或多行书写,默认以 " ; " 结尾

关键词不能跨多行或简写

用空格和 TAB 缩进来提高语句的可读性

子句通常位于独立行,便于编辑,提高可读性

注释:

  • SQL 标准:
#单行注释,注意有空格
-- 注释内容 
 
#多行注释
/*注释内容
注释内容
注释内容*/
  • MySQL 注释:
# 注释内容

3.2.2 数据库对象和命名

数据库的组件(对象):

  • 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则:

  • 必须以字母开头,后续可以包括字母,数字和三个特殊字符(# _ $)
  • 不要使用 MySQL 的保留字

3.2.3 SQL 语句分类

  • DDL: Data Defination Language 数据定义语言
    • CREATE,DROP,ALTER
  • DML: Data Manipulation Language 数据操纵语言
    • INSERT,DELETE,UPDATE
  • DQL:Data Query Language 数据查询语言
    • SELECT
  • DCL:Data Control Language 数据控制语言
    • GRANT,REVOKE
  • 软件开发:CRUD

3.2.4 SQL 语句构成

关健字 Keyword 组成子句 clause,多条 clause 组成语句

示例:

SELECT *                 #SELECT子句
FROM products             #FROM子句
WHERE price>666         #WHERE子句

说明:一组 SQL 语句由三个子句构成,SELECT,FROM 和 WHERE 是关键字

获取 SQL 命令使用帮助:

官方帮助:https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

mysql> HELP KEYWORD

3.2.5 字符集和排序

早期 MySQL 版本默认为 latin1,从 MySQL8.0 开始默认字符集已经为 utf8mb4

查看支持所有字符集:

11:37:27(root@localhost) [(none)]> show character set;
#特别注意mysql中utf8并不是真正的uft8
正确的utf8最大应该占4个字节而mysql中utf8占3个字节,所以会导致一些生僻字显示不了
如要使用utf8应使用utf8mb4
utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3

查看支持所有排序规则:

11:37:42(root@localhost) [(none)]> show collation;

查看当前使用的排序规则

11:38:29(root@localhost) [(none)]> show variables like 'collation%';

设置服务器默认的字符集

[11:44:27 root@centos8 ~]#vim /etc/my.cnf
[mysqld]
character-set-server=utf8

设置客户端默认的字符集

#针对mysql客户端
[mysql]
default-character-set=utf8
#针对所有mysql客户端
[client]
default-character-set=utf8

范例:字符集和相关文件

11:47:48(root@localhost) [(none)]> show character set;

[11:48:20 root@centos8 ~]#ll /usr/share/mysql/charsets/

查看当前字符集的使用情况

11:49:01(root@localhost) [(none)]> show variables like 'character%';

3.3 管理数据库

3.3.1 创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';

范例:

范例: 创建数据库指定字符集,并且指定排序规则

12:00:16(root@localhost) [(none)]> create database zabbix character set utf8 collate utf8_bin;

3.3.2 修改数据库

ALTER DATABASE DB_NAME character set utf8;

范例:

3.3.3 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

范例:

12:05:30(root@localhost) [(none)]> drop database db1;
Query OK, 0 rows affected (0.01 sec)

12:06:41(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.01 sec)
[12:07:11 root@centos8 ~]#tree /var/lib/mysql -d
/var/lib/mysql
├── db2
├── #innodb_temp
├── mysql
├── performance_schema
├── sys
└── zabbix

3.3.4 查看数据库列表

SHOW DATABASES;

范例:

12:07:58(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

3.4 数据类型

数据类型:

  • 数据长什么样
  • 数据需要多少空间来存放

数据类型

  • 系统内置数据类型
  • 用户定义数据类型

MySQL 支持多种内置数据类型

  • 数值类型
  • 日期/时间类型
  • 字符串(字符)类型

数据类型参考链接

MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types

clipboard.png

选择正确的数据类型对于获得高性能至关重要,三大原则:

  1. 更小的通常更好,尽量使用可正确存储数据的最小数据类型
  2. 简单就好,简单数据类型的操作通常需要更少的 CPU 周期
  3. 尽量避免 NULL,包含为 NULL 的列,对 MySQL 更难优化

3.4.1 整数型

tinyint(m) 1 个字节 范围(-128~127)

smallint(m) 2 个字节 范围(-32768~32767)

mediumint(m) 3 个字节 范围(-8388608~8388607)

int(m) 4 个字节 范围(-2147483648~2147483647)

bigint(m) 8 个字节 范围(+-9.22*10 的 18 次方)

上述数据类型,如果加修饰符 unsigned 后,则最大值翻倍

如:tinyint unsigned 的取值范围为(0~255)

int(m)里的 m 是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围,规定了 MySQL 的一些交互工具(例如 MySQL 命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和 Int(20)是相同的

BOOL,BOOLEAN:布尔型,是 TINYINT(1)的同义词。zero 值被视为假,非 zero 值视为真

3.4.2 浮点型(float 和 double),近似值

float(m,d) 单精度浮点型 8 位精度(4 字节) m 总个数,d 小数位

double(m,d) 双精度浮点型 16 位精度(8 字节) m 总个数,d 小数位

设一个字段定义为 float(6,3),如果插入一个数 123.45678,实际数据库里存的是 123.457,但总个数还以实际为准,即 6 位

3.4.3 定点数

在数据库中存放的是精确值,存为十进制

decimal(m,d) 参数 m<65 是总个数,d<30 且 d<m 是小数位

MySQL5.0 和更高版本将数字打包保存到一个二进制字符串中(每 4 个字节存 9 个数字)。

例如: decimal(18,9)小数点两边将各存储 9 个数字,一共使用 9 个字节:其中,小数点前的 9 个数字用 4 个字节,小数点后的 9 个数字用 4 个字节,小数点本身占 1 个字节

浮点类型在存储同样范围的值时,通常比 decimal 使用更少的空间。float 使用 4 个字节存储。double 占用 8 个字节

因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用 decimal,例如存储财务数据。但在数据量比较大的时候,可以考虑使用 bigint 代替 decimal

3.4.4 字符串(char,varchar,text)

char(n) 固定长度,最多 255 个字符,注意不是字节

varchar(n) 可变长度,最多 65535 个字符

tinytext 可变长度,最多 255 个字符

text 可变长度,最多 65535 个字符

mediumtext 可变长度,最多 2 的 24 次方-1 个字符

longtext 可变长度,最多 2 的 32 次方-1 个字符

BINARY(M) 固定长度,可存二进制或字符,长度为 0-M 字节

VARBINARY(M) 可变长度,可存二进制或字符,允许长度为 0-M 字节

内建类型:ENUM 枚举, SET 集合

char 和 varchar 的比较:

参考:https://dev.mysql.com/doc/refman/8.0/en/char.html

clipboard.png

  1. char(n) 若存入字符数小于 n,则以空格补于其后,查询之时再将空格去掉,所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此
  2. char(n) 固定长度,char(4)不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数 +1 个字节(n< n>255),所以 varchar(4),存入 3 个字符将占用 4 个字节
  3. char 类型的字符串检索速度要比 varchar 类型的快

varchar 和 text:

  1. varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数 +1 个字节(n< n>255),text 是实际字符数 +2 个字节。
  2. text 类型不能有默认值
  3. varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text

3.4.5 二进制数据 BLOB

BLOB 和 text 存储方式不同,TEXT 以文本方式存储,英文存储区分大小写,而 Blob 以二进制方式存储,不分大小写

BLOB 存储的数据只能整体读出

TEXT 可以指定字符集,BLOB 不用指定字符集

3.4.6 日期时间类型

date 日期 '2008-12-2'

time 时间 '12:25:36'

datetime 日期时间 '2008-12-2 22:06:44'

timestamp 自动存储记录修改时间

YEAR(2), YEAR(4):年份

timestamp 字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间

3.4.7 修饰符

NULL 数据列可包含 NULL 值,默认值

NOT NULL 数据列不允许包含 NULL 值,相当于网站注册表中的 * 为必填选项

DEFAULT 默认值

PRIMARY KEY 主键,所有记录中此字段的值不能重复,且不能为 NULL

UNIQUE KEY 唯一键,所有记录中此字段的值不能重复,但可以为 NULL

CHARACTER SET name 指定一个字符集

适用数值型的修饰符:

AUTO_INCREMENT 自动递增,适用于整数类型

UNSIGNED 无符号

范例:AUTO_INCREMENT

02:27:31(root@localhost) [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)

02:27:46(root@localhost) [(none)]> use test
Database changed
02:27:54(root@localhost) [test]> create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.02 sec)
02:31:43(root@localhost) [test]> show table status from test like "t1" \G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4294967294
Create_time: 2021-01-31 14:29:48
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

02:33:15(root@localhost) [test]> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

02:33:19(root@localhost) [test]> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

02:33:32(root@localhost) [test]> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't1.PRIMARY'
#上面表的数据类型无法存放所有数据,修改过数据类型实现自增长数据的增加
02:33:42(root@localhost) [test]> alter table t1 modify id bigint auto_increment;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

02:35:02(root@localhost) [test]> desc t1;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | bigint | NO   | PRI | NULL    | auto_increment |
+-------+--------+------+-----+---------+----------------+
1 row in set (0.01 sec)

02:35:12(root@localhost) [test]> insert t1 values(null);
Query OK, 1 row affected (0.00 sec)

02:35:34(root@localhost) [test]> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
| 4294967296 |
+------------+
3 rows in set (0.00 sec)

3.5 DDL 语句

表:二维关系

设计表:遵循规范

定义:字段,索引

  • 字段:字段名,字段数据类型,修饰符
  • 约束,索引:应该创建在经常用作查询条件的字段上

3.5.1 创建表

创建表:

CREATE TABLE

获取帮助:

HELP CREATE TABLE

创建表的方法

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符,
...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项:
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意:

  • Storage Engine 是指表类型,也即在表创建时指明其使用的存储引擎
  • 同一库中不同表可以使用不同的存储引擎
  • 同一个库中表建议要使用同一种存储引擎类型

范例:创建表

2:46:55(root@localhost) [db1]> create table student ( id int unsigned auto_increment primary key, name varchar(20) not null, age tinyint unsigned, gender enum('M','F') default 'M' )ENGINE=InnoDB auto_increment=10 default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
#id字段以10初始值

02:46:57(root@localhost) [db1]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

02:48:08(root@localhost) [db1]> insert student (name,age) values('xiaoming',20);
Query OK, 1 row affected (0.00 sec)

02:48:20(root@localhost) [db1]> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
+----+----------+------+--------+
1 row in set (0.01 sec)
02:48:35(root@localhost) [db1]> insert student (name,age,gender) values('xiaohong',18,'f');
Query OK, 1 row affected (0.00 sec)

02:49:35(root@localhost) [db1]> select *from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
| 11 | xiaohong |   18 | F      |
+----+----------+------+--------+
2 rows in set (0.00 sec)

范例:auto_increment 属性

02:49:49(root@localhost) [db1]> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.01 sec)
#修改默认起始数
02:50:47(root@localhost) [db1]> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
#修改默认步长
02:51:34(root@localhost) [db1]> set @@auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

02:51:46(root@localhost) [db1]> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 3     |
+--------------------------+-------+
2 rows in set (0.00 sec)

范例:时间类型

02:53:11(root@localhost) [db1]> create table testdate (id int auto_increment primary key,date timestamp default current_timestamp not null);
Query OK, 0 rows affected (0.02 sec)
02:55:42(root@localhost) [db1]> insert testdate () values();
Query OK, 1 row affected (0.00 sec)

02:55:49(root@localhost) [db1]> insert testdate () values();
Query OK, 1 row affected (0.00 sec)

02:55:50(root@localhost) [db1]> insert testdate () values();
Query OK, 1 row affected (0.00 sec)

02:55:51(root@localhost) [db1]> select * from testdate;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2021-01-31 14:55:49 |
|  2 | 2021-01-31 14:55:50 |
|  3 | 2021-01-31 14:55:51 |
+----+---------------------+
3 rows in set (0.00 sec)

(2) 通过查询现存表创建;新表会被直接插入查询而来的数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   [(create_definition,...)]
[table_options]
[partition_options]   select_statement

范例:依据别的表创建新表并且把数据也插入,用于备份

02:58:57(root@localhost) [db1]> create table user select user,host from mysql.user;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

03:01:53(root@localhost) [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
| testdate      |
| user          |
+---------------+
3 rows in set (0.00 sec)

03:02:04(root@localhost) [db1]> desc user;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user  | char(32)  | NO   |     |         |       |
| host  | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

03:02:10(root@localhost) [db1]> select * from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

(3) 通过复制现存的表的表结构创建,但不复制数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE
old_tbl_name) }

范例:只复制表结构

03:02:22(root@localhost) [db1]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

03:04:00(root@localhost) [db1]> create table teacher like student;
Query OK, 0 rows affected (0.02 sec)

03:04:21(root@localhost) [db1]> desc teacher;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.5.2 表查看

查看表:

SHOW TABLES [FROM db_name]

查看表创建命令:

SHOW CREATE TABLE tbl_name

查看表结构:

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

查看表状态:

SHOW TABLE STATUS LIKE 'tbl_name'

查看支持的 engine 类型

SHOW ENGINES;

范例:查看表当前数据库表列表

03:04:28(root@localhost) [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
| teacher       |
| testdate      |
| user          |
+---------------+
4 rows in set (0.00 sec)

范例:查看表结构

03:07:32(root@localhost) [db1]> desc user;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user  | char(32)  | NO   |     |         |       |
| host  | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
03:07:43(root@localhost) [db1]> show columns from user;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user  | char(32)  | NO   |     |         |       |
| host  | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

范例:查看创建表命令

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    691 引用 • 535 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...