SQL 基礎應用
1.什么是SQL
關系型數(shù)據(jù)庫當中通用的查詢語言。全名：結構化查詢語言。
2.SQL標準 （ANSI/ISO）
SQL-89
SQL-92
SQL-99
SQL-03
3.SQL常用分類
DDL ： 數(shù)據(jù)定義語言
DCL ： 數(shù)據(jù)控制語言
DML ： 數(shù)據(jù)操作語言
4.SQL_MODE
5.7+之后采用的是嚴格模式
作用：為了讓我們SQL在執(zhí)行時更加嚴謹、有意義、符合常識、邏輯、符合科學等
查看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.字符集和校對規(guī)則
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 區(qū)別？
例如：
utf8不完整，emoji表情字符是不支持，utf8mb4是支持的。
根本原因是，utf8 字符最大長度為3字節(jié)，utf8mb4是4字節(jié)。
5.2校對規(guī)則（排序規(guī)則）
mysql> show collation;
作用： 影響到了字符串的排序。
6.數(shù)據(jù)類型
作用：約束存儲的數(shù)據(jù)更加有意義，符合對于這個列的定義
6.1數(shù)據(jù)類型
| 類型 | 字節(jié)量 | 范圍 |
|---|---|---|
| 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字節(jié)，存儲字符長度。
怎么選擇：一般情況下 變長字符串就用varchar，固定長度一般采用char類型
enum() ： 枚舉類型
應用場景： 列中的數(shù)據(jù)，有限個數(shù)的值的時候，并且是有規(guī)律。
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會受到時區(qū)的影響
7.約束和其他表屬性
Primary key ： 主鍵約束 ，要求設置為主鍵的列，儲值時，非空且唯一。每張表只有一個主鍵。
not null : 非空約束，必須錄入值
unique key ： 唯一約束，不能重復值
unsigned ： 數(shù)字類型約束，無符號。
default ： 設置默認值，一般配合not null 使用
auto_increment ： 針對數(shù)字列，自動增長，一般配合主鍵
comment ： 列或者表進行注釋
創(chuàng)建庫
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)
刪除庫（生產環(huán)境不要用此命令）
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. 修改數(shù)據(jù)類型
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. 修改列名及數(shù)據(jù)類型
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語句開發(fā)規(guī)范
庫： CREATE DATABASE test CHARSET utf8mb4;
1. 庫名要與業(yè)務有關
2. 庫名不使用大寫字母、數(shù)字開頭。
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. 表名：與業(yè)務有關,例如：wp_users，不使用大寫字母、數(shù)字開頭，不要太長（16以下）
2. 設置存儲引擎、字符集、表注釋
3. 表名、列名要使用內置關鍵字
4. 列名要有意義，長度（16以下）
5. 數(shù)據(jù)類型：合適的、足夠的、簡短
6. 每個表要有主鍵，一般是自增長、無關列數(shù)字列。
7. 每個列盡量是not null ，可以配合default
8. 每個列要有注釋
9. 修改定義的操作，要在業(yè)務不繁忙期間去做。如果緊急可以使用pt-osc 。
