1.SQL介紹
結(jié)構(gòu)化查詢語(yǔ)言
SQL標(biāo)準(zhǔn)：SQL 92 SQL 99
5.7以后符合SQL92標(biāo)準(zhǔn)的嚴(yán)格模式
通過(guò)sql_mode控制住
1.1SQL作用
SQL用來(lái)管理和操作MySQL內(nèi)部的對(duì)象
對(duì)象即庫(kù)和表
庫(kù)：庫(kù)名炒瘸，屬性
表：表名，表屬性耀鸦，列柬批，記錄啸澡，列屬性和約束
1.2 SQL語(yǔ)句的類型
DDL(data definition language)：數(shù)據(jù)定義語(yǔ)言
DCL(data control language)：數(shù)據(jù)控制語(yǔ)言
DML(data manipulation language)：數(shù)據(jù)操作語(yǔ)言
DQL(data query language)：數(shù)據(jù)查詢語(yǔ)言
1.3 數(shù)據(jù)類型
1.3.1 作用
控制數(shù)據(jù)的規(guī)范性，讓數(shù)據(jù)有具體含義氮帐，在列上進(jìn)行控制
1.3.2種類
字符串
char
char(20)
定長(zhǎng)長(zhǎng)度嗅虏，上限20
存儲(chǔ)數(shù)據(jù)時(shí)，一次性提供20字符長(zhǎng)度的存儲(chǔ)空間上沐，磁盤空間固定皮服，如果數(shù)據(jù)未占滿20位，則剩余由空格填充
varchar
varchar(20)
可變長(zhǎng)度的字符串類型
存數(shù)據(jù)時(shí)奄容，按需進(jìn)行存儲(chǔ)空間分配冰更。
存數(shù)據(jù)時(shí)， 首先進(jìn)行字符串長(zhǎng)度判斷昂勒，
比如需存儲(chǔ)的字符串是10個(gè)字符蜀细，只會(huì)分配10個(gè)字符長(zhǎng)度的存儲(chǔ)空間，并且會(huì)用單獨(dú)占用一個(gè)字符長(zhǎng)度來(lái)記錄此次數(shù)據(jù)的字符長(zhǎng)度
字符長(zhǎng)度超過(guò)255之后戈盈，需要兩個(gè)字節(jié)長(zhǎng)度記錄字符長(zhǎng)度
enum
enum('monday','tuesday','sunday',........)
枚舉
枚舉是列出某些有窮序列集的所有成員的程序奠衔，或者是一種特定類型對(duì)象的計(jì)數(shù)。這兩種類型經(jīng)常（但不總是）重疊塘娶。
數(shù)據(jù)行較多時(shí)归斤，會(huì)影響到索引的應(yīng)用
注意：數(shù)字類禁止使用enum類型！刁岸！
image
數(shù)字
tinyint ： -128~127
int ：-2^31~2^31-1
說(shuō)明：手機(jī)號(hào)是無(wú)法存儲(chǔ)到int的脏里。一般是使用char類型來(lái)存儲(chǔ)收集號(hào)
image
時(shí)間
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會(huì)受到時(shí)區(qū)的影響
image
二進(jìn)制
#有但是此處不研究
image.png
面試題：
1.char和varchar的區(qū)別
char | varchar
255 | 65535
定長(zhǎng) | 變長(zhǎng)
固定存儲(chǔ)空間|按需分配
2.char和varchar的選擇
(1)char類型迫横，固定長(zhǎng)度的字符串列，比如手機(jī)號(hào)酝碳，身份證矾踱，銀行卡號(hào)，性別等
(2)varchar類型疏哗，不確定長(zhǎng)度的字符串呛讲，可以使用
懸念：為什么影響索引高度？
2.表屬性
存儲(chǔ)引擎: engine=InnoDB
(5.1版本為MyISAM)
字符集：charset=utf8mb4
utf8 中文占3字節(jié)
utf8mb4 中文占4字節(jié)
utf8mb4是utf8的嚴(yán)格超集
utf8mb4支持emoji
排序規(guī)則（校對(duì)規(guī)則）collation
主要是針對(duì)英文字符串大小寫(xiě)問(wèn)題
3.列的屬性和約束
<1>PK
主鍵：（Primary key）
概念 :
主關(guān)鍵字（主鍵返奉，primary key）是被挑選出來(lái)贝搁，作表的行的惟一標(biāo)識(shí)的候選關(guān)鍵字。一個(gè)表只有一個(gè)主關(guān)鍵字芽偏。
主關(guān)鍵字又可以稱為主鍵雷逆。主鍵可以由一個(gè)字段，也可以由多個(gè)字段組成哮针，分別成為單字段主鍵或多字段主鍵。
作用：
1）保證實(shí)體的完整性;
2）加快數(shù)據(jù)庫(kù)的操作速度
特點(diǎn)：
1) 一個(gè)表中只能有一個(gè)主鍵。如果在其他字段上建立主鍵十厢，則原來(lái)的主鍵就會(huì)取消等太。在ACCESS中，雖然主鍵不是必需的蛮放，但最好為每個(gè)表都設(shè)置一個(gè)主鍵缩抡。
2）主鍵的值不可重復(fù)，也不可為空（NULL）包颁。
3)主鍵值不能被重用
提示：雖然并不總是需要主鍵瞻想，但是多數(shù)數(shù)據(jù)庫(kù)設(shè)計(jì)者都會(huì)保證他們創(chuàng)建的每個(gè)表具有一個(gè)主鍵，以便以后的數(shù)據(jù)庫(kù)的操作和管理娩嚼。
數(shù)字列蘑险，整數(shù)列，無(wú)關(guān)列岳悟，自增的,聚集索引列
是一種約束佃迄，也是一種索引類型，在一個(gè)表中只能有一個(gè)主鍵贵少。
<2>非空 Not NULL
我們建議呵俏，對(duì)于普通列來(lái)講，盡量設(shè)置not null
默認(rèn)值（default）：數(shù)字列的默認(rèn)值使用0滔灶，字符串類型的設(shè)置為null字符串或者nil
<3>唯一 unique
不能重復(fù)
###<4>索引 key
<4>default 默認(rèn)
<5>自增 auto_increment
針對(duì)數(shù)字列
可以設(shè)置起點(diǎn)和偏移量
<6>無(wú)符號(hào) unsigned
針對(duì)數(shù)字列普碎，必須跟在定義數(shù)據(jù)類型后面
<7>注釋 comment
5.SQL語(yǔ)句應(yīng)用
DDL 數(shù)據(jù)定義語(yǔ)言
建表建庫(kù)規(guī)范
1.庫(kù)名，表名必須是小寫(xiě)
為啥录平？開(kāi)發(fā)和生產(chǎn)平臺(tái)可能會(huì)出問(wèn)題
2.不能以數(shù)字開(kāi)頭
3.不支持 - 支持 _
4.內(nèi)部函數(shù)名不能使用
5.名字和業(yè)務(wù)功能有關(guān)（his麻车，）
庫(kù)
建庫(kù)
mysql> create database xxxx charset utf8mb4;
Query OK, 1 row affected (0.05 sec)
mysql> show create database xxxx;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| xxxx | CREATE DATABASE `xxxx` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database oldguo1;
Query OK, 1 row affected (0.01 sec)
mysql> show create database oldguo1;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| oldguo1 | CREATE DATABASE `oldguo1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldguo1 |
| performance_schema |
| sys |
| world |
| xxxx |
| zhihu |
+--------------------+
8 rows in set (0.00 sec)
改庫(kù)
mysql> show create database oldguo1;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| oldguo1 | CREATE DATABASE `oldguo1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database oldguo1 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database oldguo1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| oldguo1 | CREATE DATABASE `oldguo1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
刪庫(kù)(生產(chǎn)禁用)
mysql> drop database oldguo1;
Query OK, 0 rows affected (0.05 sec)
表
建表
create table oldguo (
ID int not null primary key AUTO_INCREMENT comment '學(xué)號(hào)',
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;
改表
1.改表結(jié)構(gòu)
例子：
添加列：
--在上表中添加手機(jī)號(hào)列 +86
alter table stu add telnum char(14) not null unique default +86 comment '手機(jī)號(hào)';
添加狀態(tài)列
alter table stu add state tinyint unsigned not null default 1 comment '狀態(tài)列';
刪除列
alter table stu drop state;
查看列信息：
desc stu;
在name行后面加QQ列
alter table stu add qq varchar(20) unique not null comment 'QQ' after name;
desc stu;
在name行之前加wechat列
alter table stu add wechat varchar(20) unique not null comment '微信' after id;
在第一列加
alter table stu add sid varchar(20) unique not null comment 'sid' FIRST;
修改某一列屬性modify
alter table stu modify name char(20) not null comment '名字';
刪除以上添加的列（不代表生產(chǎn)操作）
切到oldboy
USE oldboy;
查看表列信息
DESC oldguo;
查看建表語(yǔ)句
SHOW CREATE TABLE
建個(gè)一模一樣的表
CREATE TABLE oguo LIKE oldguo;
從數(shù)據(jù)庫(kù)找表
mysql> SELECT table_schema ,table_name FROM information_schema.tables WHERE table_name='stu';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| xxxx | stu |
+--------------+------------+
1 row in set (0.00 sec)
mysql>
online-ddl:pt-osc
刪表
alter table stu drop xxxx列名;
DML數(shù)據(jù)操作語(yǔ)言
insert插入
最簡(jiǎn)單的插入數(shù)據(jù)方法
INSERT INTO stu VALUES(1,'zyl','1234','18');
最規(guī)范的插入數(shù)據(jù)方法
insert into stu(name,qq,age) VALUES('zyl','123456','12');
查看表數(shù)據(jù)（不代表生產(chǎn)操作）
mysql> select * from stu;
+----+------+--------+-----+
| id | name | qq | age |
+----+------+--------+-----+
| 1 | zyl | 1234 | 18 |
| 2 | zyl | 123456 | 12 |
+----+------+--------+-----+
2 rows in set (0.00 sec)
<*>書(shū)寫(xiě)規(guī)范：字符串加‘’，數(shù)字不加萄涯，根據(jù)定義類型進(jìn)行區(qū)分
<*>報(bào)錯(cuò)分析：列定義有unique绪氛，重復(fù)插入數(shù)據(jù)會(huì)報(bào)下列錯(cuò)誤。
[SQL] insert into stu(name,qq,age) VALUES('ss','123459',16);
[Err] 1062 - Duplicate entry '123459' for key 'qq'
update（操作有分享）
替換第二行的qq號(hào)碼
update stu set qq='123' where id=2;
delete
需求1：將一個(gè)大表清空
delete from stu;
delete from stu where id=2;
物理層次刪除涝影，刪除區(qū)
truncate table stu;
面試題：delete和truncate區(qū)別
1.delete是邏輯刪除枣察，逐行刪除，而且不會(huì)降低自增長(zhǎng)的起始值燃逻，
效率很低序目，碎片很多，會(huì) 影響到性能
2.truncate屬于物理刪除伯襟，原理是將表段中的區(qū)進(jìn)行清空猿涨，不會(huì)產(chǎn)生碎片，且性能較高姆怪，
但是truncate只能進(jìn)行表的全部清空
需求：使用update替代delete叛赚，進(jìn)行偽刪除
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è)務(wù)語(yǔ)句修改
SELECT * FROM oldguo ;
改為
SELECT * FROM oldguo WHERE state=0;
DQL
select
show
