3.1 关系型数据库的常见组件
- 数据库:database
- 表:table 行:row 列:column
- 索引:index
- 视图:view
- 存储过程:proccedure
- 存储函数:function
- 触发器:trigger
- 事件调度器:event scheduler,任务计划
- 用户:user
- 权限:privilege
3.2 SQL语言的兴起与语法标准
SQL(Structured Query Language)结构化查询语言是对IBM公司San Jose,California研究室的埃德加科德的关系模型的第一个商业化语言实现,这一模型在其1970年的一篇具有影响力的论文《一个对于大型共享数据库的关系模型》中被描述。尽管SQL并非完全按照科德的关系模型设计,但其依然成为最广泛运用的数据库语言
1970年初,由埃德加科德发表将资料组成表格的应用原则(Codd’s Relational Algebra)
1974年,同一实验室的D.D.Chamberlin和R.F.Boyce对Codd’s Relational Algebra在研究关系数据库管理系统System R中,研制出一套规范语言-SEQUEL(Structured English Query Language)
1976年11月的IBM Journal of R&D 公布新版本的SQL(SEQUEL/2)。1980年改名为SQL
1979年ORACLE公司首先提供商用的SQL,IBM公司在DB2和SQL/DS数据库系统中也实现了SQL
1986年10月,美国国家标准学会ANSI采用SQL作为关系数据库管理系统的标准语言( ANSI X3.135-1989 )
1987年成为国际标准化组织(ISO)采纳为国际标准
1989年,美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言,称为ANSI SQL 89
后续SQL标准经过了一系列的增订,加入了大量的新特性,有各种版本:ANSI SQL,SQL-1986,SQL-1989,SQL-1992,SQL-1999,SQL-2003,SQL-2008,SQL-2011
目前,所有主要的关系数据库管理系统支持某些形式的SQL,大部分数据库至少遵守ANSI SQL89标准
虽然有这一标准的存在,但大部分的SQL代码在不同的数据库系统中并不具有完全的跨平台行
业内标准微软和Sybase的T-SQL,Oracle的PL/SQL
3.2.1 SQL 语言规范
在数据库系统中,SQL语句不区分大小写,建议用大写
SQL语句可单行或多行书写,默认以“;”结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行,便于编辑,提高可读性
注释:
- SQL标准
#单行注释,注意有空格
-- 注释内容
#多行注释
/*注释内容
注释内容
注释内容*/
- MySQL注释:
#注释内容
3.2.2 数据库对象和命名
数据库的组件(对象):
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则:
- 必须以字母开头,可包括数字和三个特殊字符(#_$)
- 不要使用MySQL的保留字
3.2.3 SQL语句分类
- DDL:Data Defination Language 数据定义语言
- CREATE,DROP,ALTER
- DML:Data Manipulation Language 数据操纵语言
- INSERT,DELETE,UPDATE
- 软件开发:CURD
- DQL:Data Query Language 数据库查询语言
- SELECT
- DCL:Data Control Language 数据库控制语言
- GRANT,REVOKE
- TCL:Transactionontral Language 事务控制语言
- COMMIT,ROLLBACK,SAVEPOINT
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开始默认字符集已经为utf8m64
查看支持所有字符集:
mysql> SHOW CHARSET;
mysql> SHOW CHARACTER SET;
查看支持所有排序规则:
SHOW COLLATION;
#注意
utf8_general_ci不区分大小写
utf8_bin 区分大小写
查看当前使用的排序规则
SHOW VARIABLES LIKE 'collation%';
设置服务器默认的字符集:
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
设置mysql客户端默认的字符集
#只针对mysql客户端
vim /etc/my.cnf
[mysql]
default-character-set=utfmb4
#所有客户端
vim /etc/my.cnf
[client]
default-character-set=utf8mb4
范例:字符集和相关文件
mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
root@ubuntu-2010:~# ll /apps/mysql/share/charsets/
total 248
drwxr-xr-x 2 root root 4096 Sep 30 05:50 ./
drwxr-xr-x 28 root root 4096 Sep 30 05:50 ../
-rw-r--r-- 1 root root 5965 Mar 26 2021 armscii8.xml
-rw-r--r-- 1 root root 5951 Mar 26 2021 ascii.xml
-rw-r--r-- 1 root root 8680 Mar 26 2021 cp1250.xml
-rw-r--r-- 1 root root 8762 Mar 26 2021 cp1251.xml
-rw-r--r-- 1 root root 6014 Mar 26 2021 cp1256.xml
-rw-r--r-- 1 root root 9347 Mar 26 2021 cp1257.xml
-rw-r--r-- 1 root root 5951 Mar 26 2021 cp850.xml
-rw-r--r-- 1 root root 5967 Mar 26 2021 cp852.xml
-rw-r--r-- 1 root root 6058 Mar 26 2021 cp866.xml
-rw-r--r-- 1 root root 6974 Mar 26 2021 dec8.xml
-rw-r--r-- 1 root root 5961 Mar 26 2021 geostd8.xml
-rw-r--r-- 1 root root 6173 Mar 26 2021 greek.xml
-rw-r--r-- 1 root root 5956 Mar 26 2021 hebrew.xml
-rw-r--r-- 1 root root 5947 Mar 26 2021 hp8.xml
-rw-r--r-- 1 root root 19427 Mar 26 2021 Index.xml
-rw-r--r-- 1 root root 5974 Mar 26 2021 keybcs2.xml
-rw-r--r-- 1 root root 5955 Mar 26 2021 koi8r.xml
-rw-r--r-- 1 root root 6977 Mar 26 2021 koi8u.xml
-rw-r--r-- 1 root root 10255 Mar 26 2021 latin1.xml
-rw-r--r-- 1 root root 7677 Mar 26 2021 latin2.xml
-rw-r--r-- 1 root root 5954 Mar 26 2021 latin5.xml
-rw-r--r-- 1 root root 7883 Mar 26 2021 latin7.xml
-rw-r--r-- 1 root root 8492 Mar 26 2021 macce.xml
-rw-r--r-- 1 root root 8503 Mar 26 2021 macroman.xml
-rw-r--r-- 1 root root 1749 Mar 26 2021 README
-rw-r--r-- 1 root root 6975 Mar 26 2021 swe7.xml
查看当前字符集的使用情况
mysql> show variables like 'character%';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /apps/mysql/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)
3.3 管理数据库
3.3.1 创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name';
COLLATE 'collate name';
范例:
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
root@ubuntu-2010:~# cat /data/mysql/db1/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
mysql> CREATE DATABASE db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> CREATE DATABASE IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wornings' at line 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
示例:指定字符集创建新数据库
mysql> CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE db2;
+----------+-------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@ubuntu-2010:~# cat /data/mysql/db2/db.opt
default-character-set=utf8
default-collation=utf8_bin
3.3.2 修改数据库
ALTER DATABASE DB_NAME character set utf8 collate utf8_bin;
示例:
mysql> ALTER DATABASE db1 character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE db1;
+----------+-------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@ubuntu-2010:~# cat /data/mysql/db1/db.opt
default-character-set=utf8
default-collation=utf8_bin
3.3.3 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
mysql> DROP DATABASE db1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
root@ubuntu-2010:~# ls /data/mysql/db1
ls: cannot access '/data/mysql/db1': No such file or directory
3.3.4 查看数据库列表
SHOW DATABASES;
3.4 数据类型
数据:
- 数据长什么样
- 数据需要多少空间来存放
数据类型
- 系统内置数据类型
- 用户定义数据类型
MySQL支持多种内置数据类型
- 数值类型
- 日期/时间类型
- 字符串(字符)类型
数据类型参考链接:https://dev.mysql.com/doc/refman/8.0/en/data-types.html

选择正确的数据类型对于获得高性能至关重要,三大原则:
- 更小的通常更好,尽量使用可正确存储数据的最小数据类型
- 简单就好,简单数据类型的操作通常需要更少的CPU周期
- 尽量避免NULL,包含为NULL的列,对MySQL更难优化
3.4.1 整数型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~23767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
binint(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和doubel),近似值
float(m,d)单精度浮点型8位精度(4字节)m总个数,d小数位,注意:小数点不占用总个数
doubel(m,d)双精度浮点型16位精度(8字节)m总个数,d小数位,注意:小数点不占用总个数
设定一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位
3.4.3 定点数
在数据库中存放的是精确值,存为十进制
格式decimal(m,d) 表示最多m位数字,其中d个小数,小数点不算在长度内
比如:DECIMAL(6,2)总共能存6位数字,末尾2位是小时,字段最大值9999.99(小数点不算在长度内)
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。
例如:decimal(18,9)小数点两边将各存储9个数字,一共使用9个字节:其中,小数点前的9个数字用4个字节,小数点后的9个数字用4个字节,小数点本身占一个字节
浮点类型在存储同样范围的值时,通常比decimal使用更少的空间。float使用4个字节存储。doubel占用8个字节
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal,例如存储财务数据。但在数据量比较大的时候,可以考虑使用bigint代替decimal
3.4.4 字符串(char,varchar,text)
char(n) 固定长度,最多255个字符
varchar(n) 可变长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
logtext 可变长度,最多2的32次方-1个字符
BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节
VARBIARY(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 |
- char(n)若存入字符小于n,则以空格补于其后,查询之时将空格去掉,所以char类型存储的字符串末尾不能有空格,varchar不限于此
- char(n)固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<n>255),所以varchar(4),存入3个字符将占用4个字节
- char类型的字符串检索速度要比varchar类型的快
varchar和text:
- varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<n>255),text是实际字符数+2个字节。
- text类型不能有默认值
- varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text
3.4.5 二进制数据BLOB
BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而BLOB以二进制方式存储,不去风大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集,BLOB不用指定字符集
3.4.6 日期时间类型
date 日期’2021-10-1′
time 时间’12:25:36′
datetime 日期时间’2021-10-1 10:00:45′
timestamp 自动存储记录修改时间
YEAR(2),YEAR(4):年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间
3.4.7 修饰符
适用于所有类型的修饰符:
NULL 数据列可包含NULL值,默认值
NOT NULL 数据列不允许包含NULL值,*为必填选项
DEFAULT 默认值
PRIMARY KEY 主键,所有记录中此字段的值不能重复,且不能为NULL
UNIQUE KEY 唯一键,所有记录中此字段的值不能重复,但可以为NULL
CHARACTER SET name 指定一个字符集
适用于数值型的修饰符:
UNSIGNED 无符号
AUTO_INCREMENT 自动递增,适用于整数类型,必须作用于某个key的字段,比如primary key
范例:关于AUTO_INCREMENT
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
#auto_increment_offset 定义初始值
#auto_increment_increment 定义不进
范例:
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1
Database changed
mysql> CREATE TABLE t1(id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.02 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.01 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'
大佬周太强了!全能啊
大佬太强了,仰望