成功不易，加倍努力！
1 SQL语言
1.1 关系型数据库的常见组件
- 数据库：database
- 表：table，行：row 列：column
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler，任务计划
1.2 SQL语言的兴起与语法标准
20世纪70年代，IBM开发出SQL，用于DB2
1981年，IBM推出SQL/DS数据库
业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，国际标准化组织(ISO) 把ANSI(美国国家标准化组织) SQL作为国际标准
SQL：ANSI SQL，SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003，SQL-2008, SQL-2011
3.2.1 SQL语言规范
- 在数据库系统中，SQL语句不区分大小写，建议用大写
- SQL语句可单行或多行书写，以“;”结尾
- 关键词不能跨多行或简写
- 用空格和缩进来提高语句的可读性
- 子句通常位于独立行，便于编辑，提高可读性
注释：
-
SQL标准：
– 注释内容 单行注释，注意有空格
/*注释内容*/ 多行注释 -
MySQL注释：
# 注释内容
3.2.2 数据库对象和命名
数据库的组件(对象)：
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
- 必须以字母开头，可包括数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字
- 同一database(Schema)下的对象不能同名
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，COMMIT，ROLLBACK
软件开发：CRUD
运维人员：查询居多
3.2.4 SQL语句构成
关健字Keyword组成子句clause，多条clause组成语句
示例：
SELECT * #SELECT子句
FROM products #FROM子句
WHERE price>400 #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
查看支持所有字符集：
SHOW CHARACTER SET;
范例：字符集和相关文件
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
[root@centos8 ~]#ll /usr/share/mysql/charsets/
total 240
-rw-r--r-- 1 root root 5939 Jun 25 2019 armscii8.xml
-rw-r--r-- 1 root root 5925 Jun 25 2019 ascii.xml
-rw-r--r-- 1 root root 8654 Jun 25 2019 cp1250.xml
-rw-r--r-- 1 root root 8783 Jun 25 2019 cp1251.xml
-rw-r--r-- 1 root root 5982 Jun 25 2019 cp1256.xml
-rw-r--r-- 1 root root 9315 Jun 25 2019 cp1257.xml
-rw-r--r-- 1 root root 5919 Jun 25 2019 cp850.xml
-rw-r--r-- 1 root root 5941 Jun 25 2019 cp852.xml
-rw-r--r-- 1 root root 6026 Jun 25 2019 cp866.xml
-rw-r--r-- 1 root root 6942 Jun 25 2019 dec8.xml
-rw-r--r-- 1 root root 5929 Jun 25 2019 geostd8.xml
-rw-r--r-- 1 root root 6141 Jun 25 2019 greek.xml
-rw-r--r-- 1 root root 5930 Jun 25 2019 hebrew.xml
-rw-r--r-- 1 root root 5915 Jun 25 2019 hp8.xml
-rw-r--r-- 1 root root 19495 Jun 25 2019 Index.xml
-rw-r--r-- 1 root root 5942 Jun 25 2019 keybcs2.xml
-rw-r--r-- 1 root root 5923 Jun 25 2019 koi8r.xml
-rw-r--r-- 1 root root 6945 Jun 25 2019 koi8u.xml
-rw-r--r-- 1 root root 10229 Jun 25 2019 latin1.xml
-rw-r--r-- 1 root root 7651 Jun 25 2019 latin2.xml
-rw-r--r-- 1 root root 5928 Jun 25 2019 latin5.xml
-rw-r--r-- 1 root root 7851 Jun 25 2019 latin7.xml
-rw-r--r-- 1 root root 8460 Jun 25 2019 macce.xml
-rw-r--r-- 1 root root 8471 Jun 25 2019 macroman.xml
-rw-r--r-- 1 root root 1749 Jun 25 2019 README
-rw-r--r-- 1 root root 6943 Jun 25 2019 swe7.xml
查看当前字符集的使用情况
MariaDB [mysql]> show variables like 'character%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
查看支持所有排序规则：
SHOW COLLATION;
查看当前使用的排序规则
SHOW VARIABLES LIKE 'collation%';
设置服务器默认的字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
设置mysql客户端默认的字符集
vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
范例：Mariadb10.3 默认的字符集和排序规则
MariaDB [mysql]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.3.17-MariaDB |
+-----------------+
1 row in set (0.000 sec)
MariaDB [mysql]> show variables like 'character%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
MariaDB [mysql]> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.001 sec)
范例：MySQL 8.0 默认的字符集和排序规则
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)
2 管理数据库
2.1 创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
范例：
MariaDB [mysql]> create database db1;
Query OK, 1 row affected (0.000 sec)
MariaDB [mysql]> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.000 sec)
[root@centos8 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
MariaDB [(none)]> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
MariaDB [(none)]> create database IF NOT EXISTS db1;
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [(none)]> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Note | 1007 | Can't create database 'db1'; database exists |
+-------+------+----------------------------------------------+
1 row in set (0.000 sec)
范例：指定字符集创建新数据库
MariaDB [(none)]> create database IF NOT EXISTS db2 CHARACTER SET 'utf8';
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> SHOW CREATE DATABASE db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
[root@centos8 ~]#cat /mysql/3306/data/db2/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
2.2 修改数据库
ALTER DATABASE DB_NAME character set utf8;
范例：
MariaDB [(none)]> ALTER DATABASE db1 character set utf8;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.000 sec)
[root@centos8 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
2.3 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
MariaDB [(none)]> drop database db1;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
[root@centos8 ~]#ls /var/lib/mysql/
aria_log.00000001 ib_buffer_pool ib_logfile0 ibtmp1 mysql
mysql_upgrade_info tc.log
aria_log_control ibdata1 ib_logfile1 multi-master.info mysql.sock
performance_schema
2.4 查看数据库列表
SHOW DATABASES;
范例：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
3 数据类型
数据类型：
- 数据长什么样
- 数据需要多少空间来存放
数据类型
- 系统内置数据类型
- 用户定义数据类型
MySQL支持多种内置数据类型
- 数值类型
- 日期/时间类型
- 字符串(字符)类型
数据类型参考链接
https://dev.mysql.com/doc/refman/8.0/en/data-types.html
选择正确的数据类型对于获得高性能至关重要，三大原则：
- 更小的通常更好，尽量使用可正确存储数据的最小数据类型
- 简单就好，简单数据类型的操作通常需要更少的CPU周期
- 尽量避免NULL，包含为NULL的列，对MySQL更难优化
3.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.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.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 字符串(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
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
|---|---|---|---|---|
| ‘’ | ’ ’ | 4 bytes | ‘’ | 1 byte |
| ‘ab’ | 'ab ’ | 4 bytes | ‘ab’ | 3 bytes |
| ‘abcd’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
| ‘abcdefgh’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
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.5 二进制数据BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集
3.6 日期时间类型
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
3.7 修饰符
适用所有类型的修饰符：
NULL 数据列可包含NULL值，默认值
NOT NULL 数据列不允许包含NULL值，*为必填选项
DEFAULT 默认值
PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name 指定一个字符集
适用数值型的修饰符：
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
范例：关于AUTO_INCREMENT
MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.001 sec)
# auto_increment_offset 定义初始值
# auto_increment_increment 定义步进
范例：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table t1(id int unsigned auto_increment primary key)
auto_increment = 4294967294;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
mysql> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
MariaDB [testdb]> insert t1 value(null);
ERROR 167 (22003): Out of range value for column 'id' at row 1
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
