常用語句 · 知識管理

# 語句規范 - 關鍵字與函數名稱全部大寫 - 數據庫名稱,表名稱,字段名稱小寫 - SQL語句必須以分號結尾 sql: 基本的增刪改查，分頁查詢，范圍查詢，模糊搜索，多表聯查 # 語句 ## 基本常用語句查看當前版本 ``` SELECT VERSION(); ``` 查看當前用戶 ``` SELECT USER(); ``` 查看當前時間 ``` SELECT NOW(); ``` 顯示亂碼 ```sql set names gbk; set names utf-8; # 選擇正確的編碼就行了 ``` 查看變量 ```sql show variabl; show variable like "%mode%"; ``` 設置變量 ```sql set global variables var_name=var_value; # var_name 為變量名稱, var_value為設置的值 ``` 慢查詢 ```sql # 查看 show variables like '%slow_query_log%'; show variables like '%long_query_time%'; # 設置慢查詢開啟 set global slow_query_log='ON'; # 把慢查詢設置為1s, 查過1s的sql將被記錄下來 set global long_query_time=1; ``` 查看數據庫模式 ```sql show variables like '%sql_mode%'; ``` 神奇的desc/explain, c ```sql desc tb_name; desc select * from tb_name where id > 111 and sex=1 order by id desc; ``` # 數據庫 ## 連接數據庫 ``` mysql -uroot -p123456 -P3306 -h127.0.0.1 mysql -uroot -p123456 ``` 更多命令使用`mysql -?`查看, ## 查看所有數據庫 ``` SHOW DATABASES; ``` ## 創建數據庫 ``` CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name ``` 新建一個數據庫 ``` CREATE DATABSE t1; ``` 仍然是新建一個數據庫,加上`IF NOT EXISTS` ``` mysql> CREATE DATABSE IF NOT EXISTS t1; Query OK, 1 row affected, 1 warning (0.00 sec) ``` 這里有一個警告, `查看警告` ``` mysql> SHOW WARNINGS; +-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Note | 1007 | Can't create database 't1'; database exists | +-------+------+---------------------------------------------+ 1 row in set (0.00 sec) ``` 加上`編碼character set` ``` mysql> CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec) ``` ## 查看數據庫創建信息 ``` mysql> SHOW CREATE DATABASE t1; +----------+------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------+ | t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ## 修改數據庫 ### 編碼格式命令規范 ``` ALTER {DATABASE|SCHEMA} [sb_name] [DEFAULT] CHARACTER SET [=] charset_name ``` 實例 ``` mysql> ALTER DATABASE t1 CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec) ``` ## 刪除數據庫命令規范 ``` DROP {DATABASE|SCHEMA} [IF EXISTS] db_name ``` 實例 ``` mysql> DROP DATABASE IF EXISTS t1; Query OK, 0 rows affected (0.07 sec) ``` ## 打開數據庫命令規范 ``` USE db_name ``` 實例 ``` mysql> USE t2; Database changed ``` ## 查看打開的數據庫 ``` mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | t2 | +------------+ 1 row in set (0.00 sec) ``` # 數據表關系型數據庫是一張二維的表, 設計盡量滿足3范式數據表的創建不能脫離項目 ## 創建數據表語法規范 ``` CREATE TABLE [IF NOT EXISTS] table_name( column_name data_type, .... ) ``` 實例 ``` mysql> CREATE TABLE tb1 ( -> username VARCHAR(20), -> age TINYINT UNSIGNED, -> salary FLOAT(7,2) UNSIGNED -> ); Query OK, 0 rows affected (0.08 sec) ``` ## 查看數據表命令規范 ``` SHOW TABLES [FROM db_name] ``` 實例-查看當前數據庫的表 ``` mysql> SHOW tables; +--------------+ | Tables_in_t2 | +--------------+ | tb1 | +--------------+ 1 row in set (0.00 sec) ``` 實例-查看其他庫的表 ``` mysql> SHOW TABLES FROM test; Empty set (0.00 sec) ``` ## 刪除數據表 ``` DROP TABLE IF EXISTS [tb_name] TRUNCATE TABLE IF EXISTS [tb_name] DELETE TABLE [tb_name] ``` 實例 ``` DROP TABLE IF EXISTS `hd_adv`; TRUNCATE TABLE IF EXISTS `hd_adv`; DELETE TABLE `hd_adv`; ``` DROP刪除整個表 TRUNCATE 清空表, 速度快, 但是不可回滾操作 DELETE 清空表, 速度慢, 但是可回滾操作 ## 查看數據表結構命令規范 ``` SHOW COLUMNS FROM tb_name SHOW FULL COLUMNS FROM tb_name; desc / explain tb_name; ``` 實例 ``` mysql> SHOW COLUMNS FROM tb1; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | salary | float(7,2) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+ 3 rows in set (0.04 sec) ``` ## 查看DDL, 數據庫的創建信息 ```sql show create table tb_name; ``` ### 實例 ```sql mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `name` varchar(5) NOT NULL DEFAULT '', `sex` tinyint(3) unsigned NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ``` ## 修改數據表結構 ### 添加一列命令規范 ``` ALTER TABLE tb_name ADD col_name col_definition [FIRST|AFTER col2_name] ``` > 不加[FIRST|AFTER col2_name] 默認在最后一列加 > 實例 ``` mysql> show columns from tb5; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | sex | enum('1','2','3') | YES | | 3 | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.09 sec) mysql> ALTER TABLE tb5 ADD password VARCHAR(30) NOT NULL AFTER username; Query OK, 1 row affected (0.09 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM tb5; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | password | varchar(30) | NO | | NULL | | | sex | enum('1','2','3') | YES | | 3 | | +----------+----------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) ``` ### 添加多列命令規范 ``` ALTER TABLE tb_name ADD (col_name col_name col_definition, ...); ``` ### 修改表編碼修改默認的字符集 ``` alter table tb1 default character set utf8; ``` ``` mysql> ALTER TABLE tb1 CONVERT TO CHARACTER SET utf8; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ## 刪除列命令規范 ``` ALTER TABLE tb_name DROP col_name; ``` 實例 ``` mysql> ALTER TABLE tb5 DROP password; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 ``` ## 修改列名稱 ``` mysql> ALTER TABLE tb1 CHANGE username username0 VARCHAR(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ## 插入記錄命令規范 ``` INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...) ``` 實例 ``` mysql> INSERT tb1 VALUES('zxc', 50, 9527); Query OK, 1 row affected (0.02 sec) ``` 實例 ``` mysql> INSERT tb1(username, age) VALUES('Tom', 20); Query OK, 1 row affected (0.00 sec) ``` ## 索引 ### 查看索引 ```sql show index from tb_name; ``` ### 創建索引 ```sql ALTER TABLE tb_name ADD INDEX index_name (column list) ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list); ALTER TABLE tbl_name ADD UNIQUE index_name (column list); ``` ### 刪除索引 ```sql ALTER TABLE tb_name DROP INDEX index_name ; ALTER TABLE tbl_name DROP PRIMARY KEY index_name; ALTER TABLE tbl_name DROP UNIQUE index_name; ``` ### 重建索引 ```sql REPAIR TABLE tbl_name QUICK; ``` ## 查看記錄精簡版語法規范 ``` SELECT expr,... FROM tb_name ``` 實例 ``` mysql> SELECT * FROM tb1; +----------+------+---------+ | username | age | salary | +----------+------+---------+ | zxc | 50 | 9527.00 | | Tom | 20 | NULL | +----------+------+---------+ 2 rows in set (0.02 sec) ``` ## 記錄約束簡介 ## 非空約束創建一個新表 ``` mysql> CREATE TABLE tb2( -> username VARCHAR(20) NOT NULL, -> age TINYINT UNSIGNED -> ); Query OK, 0 rows affected (0.08 sec) ``` 查看表結構是否正確 ``` mysql> show columns from tb2; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) ``` 插入一個空值 ``` mysql> INSERT tb2 VALUES(NULL, 25); ERROR 1048 (23000): Column 'username' cannot be null ``` 設置為非空之后, 不能設置為null 但是這樣卻不會報錯,只會有警告 ``` mysql> INSERT tb2(age) VALUES(12); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1364 | Field 'username' doesn't have a default value | +---------+------+-----------------------------------------------+ 1 row in set (0.00 sec) ``` ### 增加非空約束 ``` mysql> ALTER TABLE tb1 MODIFY username VARCHAR(20) NOT NULL; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ## 主鍵約束 PRIMARY KEY 每張表只能存在一個主鍵為了保證記錄的唯一性主鍵自動為`NOT NULL` ``` mysql> CREATE TABLE tb3( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL -> ); Query OK, 0 rows affected (0.04 sec) ``` > 注意這里的`AUTO_INCREMENT`, 主鍵可以不是`AUTO_INCREMENT`, 但是`AUTO_INCREMENT`一定會是主鍵表結構 ``` mysql> show columns from tb3; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) ``` 表記錄 ``` mysql> insert tb3(username) VALUES('TOM'); Query OK, 1 row affected (0.01 sec) mysql> insert tb3(username) VALUES('Jaime'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb3; +----+----------+ | id | username | +----+----------+ | 1 | TOM | | 2 | Jaime | +----+----------+ 2 rows in set (0.00 sec) ``` ### 增加主鍵約束 ``` mysql> ALTER TABLE tb1 ADD PRIMARY KEY(id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tb1 CHANGE id id INT AUTO_INCREMENT; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` 或者合在一起 ``` mysql> ALTER TABLE tb1 CHANGE id id INT PRIMARY KEY AUTO_INCREMENT; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ### 刪除主鍵約束 ``` mysql> ALTER TABLE tb1 CHANGE id id INT; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tb1 DROP PRIMARY KEY; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ## 唯一性約束 UNIQUE KEY 仍然是保證記錄唯一性, 但是允許記錄為空(有且僅有一個，感覺有點怪怪的哈) ``` mysql> CREATE TABLE tb4( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL UNIQUE KEY, -> age TINYINT UNSIGNED -> ); Query OK, 0 rows affected (0.04 sec) ``` 查看表結構 ``` mysql> show columns from tb4; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) ``` 插入重復的記錄 ``` mysql> INSERT tb4(username, age) VALUES('Jaime', 24); Query OK, 1 row affected (0.00 sec) mysql> INSERT tb4(username, age) VALUES('Jaime', 25); ERROR 1062 (23000): Duplicate entry 'Jaime' for key 'username' ``` ### 添加唯一性約束 ``` mysql> ALTER TABLE `tb1` ADD unique(`username`); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ### 刪除唯一性約束只要設置唯一性約束，就會建立索引，刪除唯一性約束只需要刪除索引就行了 ``` mysql> ALTER TABLE tb1 DROP INDEX username; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ## 默認值約束默認值插入記錄時候如果沒有明確賦值, 則自動賦默認值 ``` mysql> CREATE TABLE tb5( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL UNIQUE KEY, -> sex ENUM('1', '2', '3') DEFAULT '3' -> ); Query OK, 0 rows affected (0.04 sec) ``` 表結構 ``` mysql> show columns from tb5; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | sex | enum('1','2','3') | YES | | 3 | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) ``` 插入記錄 ``` mysql> INSERT tb5(username) VALUES('Jaime'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb5; +----+----------+------+ | id | username | sex | +----+----------+------+ | 1 | Jaime | 3 | +----+----------+------+ 1 row in set (0.00 sec) ``` ## 更新記錄命令規范 ``` UPDATE tb_name SET field1=new-value1, field2=new-value2 [WHERE Clause] ``` 實例 ``` mysql> UPDATE tb1 SET username='Jaime' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` # 查詢記錄 ## ORDER BY 排序 ## GROUP BY 分組 ## LIMIT 分頁 ## 正則表達式以st開頭的記錄 ``` mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st'; ``` 包含某個字符串的記錄 ``` mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar'; ``` ## 判斷字段是否包含某個字符串 1. like ``` SELECT * FROM member WHERE email like "%@gmail.com%"; ``` 2.find_in_set(); ``` SELECT * FROM member WHERE find_in_set("@gmail.com", email); ``` # 多實例運行找到mysql安裝目錄(linux的etc目錄)下的my.conf或者my.ini 打開配置 ```ini [client] port=3307 [mysqld] port=3307 datadir="E:/db/" ``` 修改以上幾個配置, port和datadir就行了,把配置保存為`my3307.ini` 運行實例 ```ini mysqld --defaults-file=my3307.ini ``` # 導入/導出數據和結構就差一個-d參數 ## 導出結構把整個數據庫結構一起導出 ``` sql mysqldump.exe -uroot -proot -h192.168.1.99 -d cashier>e:\cashier.sql ``` 導出表(cashier.node表 cashier.order表) ``` sql mysqldump.exe -uroot -proot -h192.168.1.99 -d cashier node order>e:\no.sql ``` ## 導出表數據(包含結構) ``` sql mysqldump.exe -uroot -proot -h192.168.1.99 cashier>e:\cashier.sql ``` 導出表(cashier.node表 cashier.order表) ``` sql mysqldump.exe -uroot -proot -h192.168.1.99 cashier node order>e:\no.sql ``` ## 導入結構 ``` mysql -uroot -p123456 cashier< haidao.sql ``` ## 導入表數據這種速度極快, 極快, 我1kw的數據, 10來s左右就全部導入完了 ``` use test; # 切換到test庫 LOAD DATA local INFILE 'D:/xsu/Desktop/test mysql/insert.sql' INTO TABLE hd_order; # 導入數據 ``` ## 導入表結構和數據 ```sql use database_name; source /home/datafilename.sql ``` # 其他 ## CHANGE 和 MODIFY change可以更改的屬性包括名稱 mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; modify可以更改不包括名稱屬性 mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL; ## STRING 編程語言中能想到的字符串處理, sql一般都有