SQL 基礎應用及information_schema
1.SQL（結構化查詢語句）介紹
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 二進制
- 表屬性
存儲引擎 ：engine = InnoDB
字符集 ：charset = utf8mb4
utf8 中文 三個字節(jié)長度
utf8mb4 中文 四個字節(jié)長度 才是真正的utf8
支持emoji字符
排序規(guī)則（校對規(guī)則） collation
針對英文字符串大小寫問題
- 列的屬性和約束
6.1 主鍵： primary key (PK)
說明：
唯一
非空
數(shù)字列，整數(shù)列，無關列，自增的.
聚集索引列？
是一種約束，也是一種索引類型，在一張表中只能有一個主鍵。
6.2 非空： Not NULL
說明：
我們建議，對于普通列來講，盡量設置not null
默認值 default ： 數(shù)字列的默認值使用0 ,字符串類型，設置為一個nil null
6.3 唯一：unique
不能重復
6.4 自增 auto_increment
針對數(shù)字列，自動生成順序值
6.5 無符號 unsigned
針對數(shù)字列
6.6 注釋 comment
- SQL語句應用
7.1 DDL：數(shù)據(jù)定義語言
7.1.1 庫
（1）建庫
mysql> create database oldguo charset utf8mb4;
mysql> show databases;
mysql> show create database oldguo;
（2）改庫
mysql> alter database oldguo1 charset utf8mb4;
（3）刪庫
mysql> drop database oldguo1;
7.1.2 表
（0）建表建庫規(guī)范：
1、庫名和表名是小寫字母
為啥？
開發(fā)和生產(chǎn)平臺可能會出現(xiàn)問題。
2、不能以數(shù)字開頭
3、不支持- 支持_
4、內(nèi)部函數(shù)名不能使用
5、名字和業(yè)務功能有關（his,jf,yz,oss,erp,crm...）
（1）建表
create table oldguo (
ID int not null primary key AUTO_INCREMENT comment '學號',
name varchar(255) not null comment '姓名',
age tinyint unsigned not null default 0 comment '年齡',
gender enum('m','f','n') NOT null default 'n' comment '性別'
)charset=utf8mb4 engine=innodb;
（2）改表
1. 改表結構
-- 例子：
-- 在上表中添加一個手機號列15801332370.（重點*****）
-- alter table oldguo add telnum char(11) not null unique comment '手機號';
-- 練習：
-- 添加一個狀態(tài)列
ALTER TABLE oldguo ADD state TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態(tài)列';
-- 查看列的信息
DESC oldguo;
-- 刪除state列(不代表生產(chǎn)操作)
ALTER TABLE oldguo DROP state;
-- online-DDL : pt-osc (自己研究下***)
-- 在name后添加 qq 列 varchar(255)
ALTER TABLE oldguo ADD qq VARCHAR(255) NOT NULL UNIQUE COMMENT 'qq' AFTER NAME;
-- 練習 在name 之前添加wechat列
ALTER TABLE oldguo ADD wechat VARCHAR(255) NOT NULL UNIQUE COMMENT '微信' AFTER ID;
-- 在首列上添加 學號列：sid(linux58_00001)
ALTER TABLE oldguo ADD sid VARCHAR(255) NOT NULL UNIQUE COMMENT '學生號' FIRST;
-- 修改name數(shù)據(jù)類型的屬性
ALTER TABLE oldguo MODIFY NAME VARCHAR(128) NOT NULL ;
DESC oldguo;
-- 將gender 改為 gg 數(shù)據(jù)類型改為 CHAR 類型
ALTER TABLE oldguo CHANGE gender gg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC oldguo;
7.2 DML 數(shù)據(jù)操作語言
7.2.1 INSERT
--- 最簡單的方法插入數(shù)據(jù)
DESC oldguo;
INSERT INTO oldguo VALUES(1,'oldguo','22654481',18);
--- 最規(guī)范的方法插入數(shù)據(jù)(重點記憶)
INSERT INTO oldguo(NAME,qq,age) VALUES ('oldboy','74110',49);
--- 查看表數(shù)據(jù)（不代表生產(chǎn)操作）
SELECT * FROM oldguo;
7.2.2 UPDATE (注意謹慎操作?。。。?
UPDATE oldguo SET qq='123456' WHERE id=5 ;
7.2.3 DELETE (注意謹慎操作?。。?！)
DELETE FROM oldguo WHERE id=5;
7.2.4 生產(chǎn)需求：將一個大表全部數(shù)據(jù)清空
DELETE FROM oldguo;
TRUNCATE TABLE oldguo;
DELETE 和 TRUNCATE 區(qū)別
1. DELETE 邏輯逐行刪除，不會降低自增長的起始值。
效率很低，碎片較多，會影響到性能
2. TRUNCATE ，屬于物理刪除，將表段中的區(qū)進行清空，不會產(chǎn)生碎片。性能較高。
7.2.5 生產(chǎn)需求：使用update替代delete，進行偽刪除
1. 添加狀態(tài)列state (0代表存在，1代表刪除)
ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;
2. 使用update模擬delete
DELETE FROM oldguo WHERE id=6;
替換為
UPDATE oldguo SET state=1 WHERE id=6;
SELECT * FROM oldguo ;
3. 業(yè)務語句修改
SELECT * FROM oldguo ;
改為
SELECT * FROM oldguo WHERE state=0;
