SQL基礎(chǔ)應用
1.SQL（結(jié)構(gòu)化查詢語句）介紹
SQL標準：SQL 92 SQL99
5.7版本后啟用SQL_Mode 嚴格模式
2.SQL作用
SQL 用來管理和操作MySQL內(nèi)部的對象
SQL對象：
庫：庫名亡呵，庫屬性
表：表名，表屬性率拒，列名倍谜，記錄迈螟，數(shù)據(jù)類型，列屬性和約束
3.SQL語句的類型
DDL：數(shù)據(jù)定義語言 data definition language
DCL：數(shù)據(jù)控制語言 data control language
DML：數(shù)據(jù)操作語言 data manipulation language
DQL：數(shù)據(jù)查詢語言 data query language
4.數(shù)據(jù)類型
4.1 作用：
控制數(shù)據(jù)的規(guī)范性尔崔，讓數(shù)據(jù)有具體含義答毫，在列上進行控制
4.2.種類
4.2.1 字符串
char(32)
定長長度為32的字符串。存儲數(shù)據(jù)時季春，一次性提供32字符長度的存儲空間洗搂，存不滿，用空格填充载弄。
varchar(32)：
可變長度的字符串類型蚕脏。存數(shù)據(jù)時，首先進行字符串長度判斷侦锯，按需分配存儲空間
會單獨占用一個字節(jié)來記錄此次的字符長度
超過255之后驼鞭，需要兩個字節(jié)長度記錄字符長度。
面試題：1. char 和varchar的區(qū)別尺碰？
（1） 255 65535
（2） 定長（固定存儲空間） 變長（按需)
2.char和varchar 如何選擇挣棕？
（1） char類型，固定長度的字符串列亲桥，比如手機號洛心，身份證號，銀行卡號题篷，性別等
（2） varchar類型词身，不確定長度的字符串，可以使用番枚。
懸念：
為什么呢法严？影響到索引的高度？
3.enum 枚舉類型
enum('bj','sh','sz','cq','hb',......)
數(shù)據(jù)行較多時葫笼，會影響到索引的應用
注意：數(shù)字類禁止使用enum類型
4.2.2數(shù)字
1.tinyint
2.int
4.2.3 時間
1.timestamp
2.datetime
4.2.4二進制
5.表屬性
存儲引擎 ：engine = InnoDB
字符集 ：charset = utf8mb4
utf8 中文 三個字節(jié)長度
utf8mb4 中文 四個字節(jié)長度 才是真正的utf8
支持emoji字符
排序規(guī)則（校對規(guī)則） collation
針對英文字符串大小寫問題
6.列的屬性和約束
6.1 主鍵： primary key (PK)
說明：
唯一
非空
數(shù)字列深啤，整數(shù)列，無關(guān)列路星，自增的.
6.2 非空： Not NULL
說明：
我們建議溯街，對于普通列來講，盡量設(shè)置not null
默認值 default ： 數(shù)字列的默認值使用0 ,字符串類型，設(shè)置為一個nil null
6.3 唯一：unique
不能重復
6.4 自增 auto_increment
針對數(shù)字列呈昔，自動生成順序值
6.5 無符號 unsigned
針對數(shù)字列
6.6 注釋 comment
SQL語句
DDL 數(shù)據(jù)定義語言
建庫
建庫建表規(guī)范
1.庫名挥等，表名必須要用小寫字母
2.不能以數(shù)字開頭，不支持-堤尾，支持_
3.名字和業(yè)務功能有關(guān)
create database song charset utf8mb4;
- show create database song;
- show databases;
修改庫
alter database song01 charset utf8mb4;
建表
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學號',
NAME VARCHAR(255) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年齡',
gender CHAR(4) NOT NULL COMMENT '性別'
)CHARSET=utf8mb4 ENGINE=INNODB;
#unsigned并不是列屬性肝劲，你可以把它當做列類型來看，因為他是限定數(shù)值型數(shù)據(jù)的哀峻，所以肯定要與列類型也就是本題中的int連用了
show create table student;
desc student;
create table student01 like student; #建一模一樣的空表
改表
#增加列
ALTER TABLE student ADD telnum CHAR(11) NOT NULL UNIQUE COMMENT '手機號';
#指定位置增加列涡相，在name 后加列telnum
ALTER TABLE student ADD telnum CHAR(11) NOT NULL UNIQUE COMMENT '手機號' AFTER NAME;
#刪除列
ALTER TABLE student DROP telnum;
#改列的屬性
ALTER TABLE student MODIFY NAME VARCHAR(128) NOT NULL;
#改列的名字及屬性
ALTER TABLE student CHANGE NAME sname VARCHAR(256) NOT NULL;
###刪除指定列
ALTER TABLE student DROP telnum;
DML 數(shù)據(jù)操作語言
insert
1, INSERT INTO student VALUES (1,'zs',12,'男');
2, INSERT INTO student(sname,age,gender) VALUES ('ls',33,'女'); #規(guī)范的插入數(shù)據(jù)
update
UPDATE student SET age=18 WHERE sname='ls';
delete
1. DELETE FROM student where id=1;
2. truncate table stu;
區(qū)別:
delete: DML操作, 是邏輯性質(zhì)刪除,逐行進行刪除,速度慢.
truncate: DDL操作,對與表段中的數(shù)據(jù)頁進行清空,速度快.
偽刪除：用update來替代delete哲泊，最終保證業(yè)務中查不到（select）即可
1.添加狀態(tài)列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;
2. UPDATE 替代 DELETE
UPDATE stu SET state=0 WHERE id=6;
3. 業(yè)務語句查詢
SELECT * FROM stu WHERE state=1;
show charset;查看支持的字符集
show collation;字符集校對規(guī)則
DQL 數(shù)據(jù)查詢語言
select
1. select 單獨使用
1.1 select @@xx;獲取參數(shù)信息 --mysql獨有
show variables;
show variables like '%por%';
select @@port; #查詢端口號
1.2 select 函數(shù)
select now();
select database();
1.3 SQL92標準的使用用法
1.3.1select 語法執(zhí)行順序（單表）
from子句==>
where子句==>
group by子句==>
select后執(zhí)行條件 ==>
having子句==>
order by==>
limit
單表查詢
SHOW TABLES FROM world;
DESC city;
SHOW CREATE TABLE city;
-where 配合等值查詢
查詢中國的城市信息
select * from city where CountryCode='CHN';
-where 配合不等值查詢 > < >= <= !=
查詢?nèi)丝跀?shù)小于1000的城市
select * from city where Population < 1000;
-where 配合模糊查詢
select * from city where CountryCode like 'C%'; # like語句中不要出現(xiàn)%在開頭的情況剩蟀，因為效率很低，不走索引
-where 配合邏輯連接符 and or (between..add.. ==>包含等于) in
查詢國家代碼是CHA和USA的城市
select * from city where CountryCode='CHN' or CountryCode='USA';
select * from city where CountryCode in('CHN','USA');
#建議使用以下語句切威，效率高
select * from city where CountryCode='CHN'
union all (不去重復數(shù)據(jù)）或者union(去重復數(shù)據(jù)）
select * from city where CountryCode='USA'
--group by配合聚合函數(shù)使用
常用聚合函數(shù)
max() ：最大值
min() ：最小值
avg() ：平均值
sum() ：總和
count() ：個數(shù)
group_concat() : 列轉(zhuǎn)行
統(tǒng)計每個國家的總?cè)丝跀?shù)
select CountryCode,SUM(Population) from city group by CountryCode;
統(tǒng)計每個國家的城市個數(shù)
select CountryCode,count(ID) from city group by CountryCode;
統(tǒng)計中國每個省的城市列表
select District,group_concat(name) from city where CountryCode='CHN' group by District;
--having
select District,sum(Population) from city where CountryCode='CHN' group by District having sum(Population)>100000;
--order by DESC 從大到小
select District,sum(Population) from city where CountryCode='CHN' group by District ORDER BY sum(Population) DESC;
--LIMIT LIMIT N ,M --->跳過N,顯示一共M行
--distinct：去重復
--as 別名
多表查詢
內(nèi)連接
查詢?nèi)丝跀?shù)量小于100人的國家名育特，城市名，國土面積
select country.`Name`,city.`Name`,country.SurfaceArea
from city join country
on city.CountryCode=country.`Code`
where city.Population<100
#注意：sql_mode=only_full_group_by
select course.cno,course.cname,sum(sc.score)
from course join sc
on course.cno = sc.cno
group by course.cname
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.course.cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
外連接
select country.`Name`,city.`Name`,country.SurfaceArea
from city left join country
on city.CountryCode=country.`Code`
and city.Population<100
information_schema.tables
use information_schema
desc tables;
TABLE_SCHEMA 表所在的庫
TABLE_NAME 表名
ENGINE 表的存儲引擎
TABLE_ROWS 表的行數(shù)
AVG_ROW_LENGTH 平均行長度
INDEX_LENGTH 索引的長度
--- 統(tǒng)計一下每個庫的真實數(shù)據(jù)量
每張表數(shù)據(jù)量=AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH
SELECT
SUM(AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.TABLES
--- information_schema.tables+CONCAT(),拼接命令
--- 使用方法舉例
mysql> SELECT CONCAT(USER,"@","'",HOST,"'") FROM mysql.user;
--- 生產(chǎn)需求1
mysqldump -uroot -p123 world city >/tmp/world_city.sql
--- 模仿以上命令先朦，對整個數(shù)據(jù)庫下的1000張表進行單獨備份缰冤，
--- 排除sys，performance喳魏，information_schema
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
FROM information_schema.tables
WHERE table_schema NOT IN('sys','performance','information_schema')
INTO OUTFILE '/tmp/bak.sh';
vim /etc/my.cnf
secure-file-priv=/tmp
/etc/init.d/mysqld restart
- show
show databases; 查看所有數(shù)據(jù)庫名
show tables; 查看當前庫下的表名
show tables from world; 查看world數(shù)據(jù)庫下的表名
show create database 查看建庫語句
show create table 查看建表語句
show grants for root@'localhost' 查看用戶權(quán)限信息
show charset 查看所有的字符集
show collation 查看校對規(guī)則
show full processlist 查看數(shù)據(jù)庫連接情況
show status 查看數(shù)據(jù)庫的整體狀態(tài)
show status like '%lock%' 模糊查看數(shù)據(jù)庫的整體狀態(tài)
show variables 查看數(shù)據(jù)庫所有變量情況
show variables like '%innodb%' 查看數(shù)據(jù)庫所有變量情況
show engines 查看所有支持存儲引擎
show engine innodb status 查看所有innodb存儲引擎狀態(tài)情況
show binary logs 查看二進制日志情況
show binlog events in 查看二進制日志事件
show relaylog events in 查看relay日志事件
show slave status 查看從庫狀態(tài)
show master status 查看數(shù)據(jù)庫binlog位置信息
show index from 查看表的索引情況
索引
索引分類
B樹索引 HASH索引 R樹
輔助索引（S) 聚集索引（C) 區(qū)別
聚集索引只能有一個,非空唯一,一般時主鍵
輔助索引,可以有多個,時配合聚集索引使用的
聚集索引葉子節(jié)點,就是磁盤的數(shù)據(jù)行存儲的數(shù)據(jù)頁
MySQL是根據(jù)聚集索引,組織存儲數(shù)據(jù),數(shù)據(jù)存儲時就是按照聚集索引的順序進行存儲數(shù)據(jù)
輔助索引,只會提取索引鍵值,進行自動排序生成B樹結(jié)構(gòu)
mysql壓力測試
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t100w where k2= 'NOGH'" --engine=innodb \
--number-of-queries=2000 -uroot -psongjianjiang123 --verbose
影響索引樹高度的因素
- 數(shù)據(jù)量級, 解決方法:分表,分庫,分布式
- 索引列值過長 , 解決方法:前綴索引
- 數(shù)據(jù)類型:
變長長度字符串,使用了char,解決方案:變長字符串使用varchar
enum類型的使用enum ('山東','河北','黑龍江','吉林','遼寧','陜西'......)
1 2 3
建索引
show index from t100w;
覆蓋索引(聯(lián)合索引)
Master [world]>alter table city add index idx_co_po(countrycode,population);
前綴索引
db01 [world]>alter table city add index idx_di(district(5));
注意：數(shù)字列不能用作前綴索引棉浸。
唯一索引
db01 [world]>alter table city add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'
執(zhí)行計劃
獲取執(zhí)行計劃 desc或者explain + 語句
mysql> desc select * from oldboy.t100w where k2= 'NOGH';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ref | idx_k2 | idx_k2 | 17 | const | 1053 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
8.2 執(zhí)行計劃分析
8.2.0 重點關(guān)注的信息
table: city ---->查詢操作的表 **
possible_keys: CountryCode,idx_co_po ---->可能會走的索引 **
key: CountryCode ---->真正走的索引 ***
type: ref ---->索引類型 *****
Extra: Using index condition ---->額外信息 *****
8.2.1 type詳解
從左到右性能依次變好.
ALL :
全表掃描,不走索引
例子:
- 查詢條件列,沒有索引
SELECT * FROM t_100w WHERE k2='780P'; - 查詢條件出現(xiàn)以下語句(輔助索引列)
USE world
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
注意:對于聚集索引列,使用以上語句,依然會走索引
DESC SELECT * FROM city WHERE id <> 10;
INDEX :
全索引掃描
查詢需要獲取整個索引樹種的值時:
DESC SELECT countrycode FROM city;聯(lián)合索引中,任何一個非最左列作為查詢條件時:
idx_a_b_c(a,b,c) ---> a ab abc
SELECT * FROM t1 WHERE b
SELECT * FROM t1 WHERE c
RANGE :
索引范圍掃描
輔助索引> < >= <= LIKE IN OR
主鍵 <> NOT IN
例子:
DESC SELECT * FROM city WHERE id<5;
DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');
注意:
1和2例子中,可以享受到B+樹的優(yōu)勢,但是3例子中是不能享受的.
所以,我們可以將3號列子改寫:
DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
ref:
非唯一性索引,等值查詢
DESC SELECT * FROM city WHERE countrycode='CHN';
eq_ref:
在多表連接時,連接條件使用了唯一索引(uk pK)
DESC SELECT b.name,a.name FROM city AS a
JOIN country AS b
ON a.countrycode=b.code
WHERE a.population <100;
DESC country
system,const :
唯一索引的等值查詢
DESC SELECT * FROM city WHERE id=10;
8.2.2 其他字段解釋
extra:
filesort ,文件排序.
SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population
ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population
結(jié)論:
1.當我們看到執(zhí)行計劃extra位置出現(xiàn)filesort,說明由文件排序出現(xiàn)
2.觀察需要排序(ORDER BY,GROUP BY ,DISTINCT )的條件,有沒有索引
- 根據(jù)子句的執(zhí)行順序,去創(chuàng)建聯(lián)合索引
索引優(yōu)化效果測試:
優(yōu)化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy'
--query="select * from oldboy.t_100w where k2='780P'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 701.743 seconds
Minimum number of seconds to run all queries: 701.743 seconds
Maximum number of seconds to run all queries: 701.743 seconds
Number of clients running queries: 100
Average number of queries per client: 20
優(yōu)化后:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.190 seconds
Minimum number of seconds to run all queries: 0.190 seconds
Maximum number of seconds to run all queries: 0.190 seconds
Number of clients running queries: 100
Average number of queries per client: 20
聯(lián)合索引:
SELECT * FROM t1 WHERE a= b=
我們建立聯(lián)合索引時:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);
以上的查詢不考慮索引的順序,優(yōu)化器會自動調(diào)整where的條件順序
注意: 索引,我們在這種情況下建索引時,需要考慮哪個列的唯一值更多,哪個放在索引左邊.如果出現(xiàn)where 條件中出現(xiàn)不等值查詢條件
DESC SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF';
我們建索引時:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
語句書寫時
DESC SELECT * FROM t_100w WHERE k2='DEEF' AND num <1000 ;如果查詢中出現(xiàn)多子句
我們要按照子句的執(zhí)行順序進行建立索引.
