mysql03-SQL应用
SQL 基础应用
1.什么是SQL
关系型数据库当中通用的查询语言。全名：结构化查询语言。
2.SQL标准 （ANSI/ISO）
SQL-89
SQL-92
SQL-99
SQL-03
3.SQL常用分类
DDL ： 数据定义语言
DCL ： 数据控制语言
DML ： 数据操作语言
4.SQL_MODE
5.7+之后采用的是严格模式
作用：为了让我们SQL在执行时更加严谨、有意义、符合常识、逻辑、符合科学等
查看SQL_MODE:
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
设置SQL_MODE:
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5.字符集和校对规则
5.1字符集
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)
mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /data/app/mysql-5.7.28-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.00 sec)
utf8 和 utf8mb4 区别？
例如：
utf8不完整，emoji表情字符是不支持，utf8mb4是支持的。
根本原因是，utf8 字符最大长度为3字节，utf8mb4是4字节。
5.2校对规则（排序规则）
mysql> show collation;
作用： 影响到了字符串的排序。
6.数据类型
作用：约束存储的数据更加有意义，符合对于这个列的定义
6.1数据类型
| 类型 | 字节量 | 范围 |
|---|---|---|
| tinyint | 1 | 0-255 -127~128 |
| int | 4 | 0~2^32-1 -231~231-1 |
6.2字符串类型
char(10)
定长类型的字符串类型。最多存储10个字符。如果存了5个,剩余空间用空格填充
varchar(10)
变长类型的字符串类型。最多存储10个字符。如果存了5个,按需分配存储空间，另外需要1-2字节，存储字符长度。
怎么选择：一般情况下 变长字符串就用varchar，固定长度一般采用char类型
enum() ： 枚举类型
应用场景： 列中的数据，有限个数的值的时候，并且是有规律。
6.3 时间日期
DATETIME
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响
7.约束和其他表属性
Primary key ： 主键约束 ，要求设置为主键的列，储值时，非空且唯一。每张表只有一个主键。
not null : 非空约束，必须录入值
unique key ： 唯一约束，不能重复值
unsigned ： 数字类型约束，无符号。
default ： 设置默认值，一般配合not null 使用
auto_increment ： 针对数字列，自动增长，一般配合主键
comment ： 列或者表进行注释
创建库
mysql> create database test1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> create database wordpress1;
Query OK, 1 row affected (0.00 sec)
查询库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
| wordpress1 |
| xiaolai |
| xuexiao |
+--------------------+
9 rows in set (0.00 sec)
mysql> show create database test1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database wordpress1;
+------------+-----------------------------------------------------------------------+
| Database | Create Database |
+------------+-----------------------------------------------------------------------+
| wordpress1 | CREATE DATABASE `wordpress1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
修改库
mysql> show create database wordpress1;
+------------+-----------------------------------------------------------------------+
| Database | Create Database |
+------------+-----------------------------------------------------------------------+
| wordpress1 | CREATE DATABASE `wordpress1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database wordpress1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show create database wordpress1;
+------------+------------------------------------------------------------------------+
| Database | Create Database |
+------------+------------------------------------------------------------------------+
| wordpress1 | CREATE DATABASE `wordpress1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除库（生产环境不要用此命令）
mysql> drop database wordpress1;
Query OK, 0 rows affected (0.02 sec)
mysql> drop database test1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| xiaolai |
| xuexiao |
+--------------------+
7 rows in set (0.00 sec)
建表
USE test;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(64) 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 COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
查表定义
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu |
+----------------+
1 row in set (0.00 sec)
mysql> show create table stu;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu | CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(64) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
`intime` datetime NOT NULL COMMENT '入学时间',
`tel` varchar(64) NOT NULL COMMENT '手机号',
PRIMARY KEY (`id`),
UNIQUE KEY `telnum` (`tel`),
UNIQUE KEY `telnum_2` (`tel`),
UNIQUE KEY `telnum_3` (`tel`),
UNIQUE KEY `tel` (`tel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc stu;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | NULL | |
| tel | varchar(64) | NO | UNI | NULL | |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
修改表定义
-- 添加和删除字段
-- 1. 在表中添加telnum char(11) not null unique key comment '手机号'
mysql> alter table stu add column telnum char(11) not null unique key comment '手机号';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | NULL | |
| tel | varchar(64) | NO | UNI | NULL | |
| telnum | char(11) | NO | UNI | NULL | |
+--------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
-- 2. 在sname后添加a列
mysql> alter table stu add column a int not null comment '测试列' after sname;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 3. 在第一列前添加b列
mysql> alter table stu add column b int not null comment '测试列' first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 4. 删除添加的a,b列
mysql> alter table stu drop column a;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table stu drop column b;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 5. 修改数据类型
mysql> alter table stu modify telnum varchar(20) not null unique key comment '手机号';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> alter table stu modify telnum varchar(30) not null unique key comment '手机号';
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 1
-- 6. 修改列名及数据类型
mysql> alter table stu change telnum teg varchar(64) not null unique key comment '手机号';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> alter table stu change telnum teg varchar(64) not null unique key comment '手机号';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc stu;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | NULL | |
| tel | varchar(64) | NO | UNI | NULL | |
| teg | varchar(64) | NO | UNI | NULL | |
+--------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
删除表
mysql> drop table stu;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
DDL语句开发规范
库： CREATE DATABASE test CHARSET utf8mb4;
1. 库名要与业务有关
2. 库名不使用大写字母、数字开头。
3. 不要使用内置关键字
4. 建库要指定字符集。
5. 生产中禁止使用删库操作。
表：
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(64) 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 COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
1. 表名：与业务有关,例如：wp_users，不使用大写字母、数字开头，不要太长（16以下）
2. 设置存储引擎、字符集、表注释
3. 表名、列名要使用内置关键字
4. 列名要有意义，长度（16以下）
5. 数据类型：合适的、足够的、简短
6. 每个表要有主键，一般是自增长、无关列数字列。
7. 每个列尽量是not null ，可以配合default
8. 每个列要有注释
9. 修改定义的操作，要在业务不繁忙期间去做。如果紧急可以使用pt-osc 。
