三、SQL语言（一）
mysql 数据库
三、SQL语言（一）
mysql 数据库

三、SQL语言（一）

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

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

  1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型
  2. 简单就好，简单数据类型的操作通常需要更少的CPU周期
  3. 尽量避免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

ValueCHAR(4)Storage RequiredVARCHAR(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.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'

2条评论

回复 知本知至 取消回复

您的邮箱地址不会被公开。 必填项已用 * 标注