上集回顧:
1. 字符集
show charset;
utf8 : 3個字節(jié)
utf8mb4 : 4個字節(jié) , 支持emoji
####2. 校對規(guī)則
show collation;
_ci
_bin
3. 數(shù)據(jù)類型
tinyint : 1字節(jié)
int : 4字節(jié)
bigint : 8字節(jié)
char(64) : 固定長度
varchar(64) : 變化長度
enum(64) : 枚舉類型
enum('bj','sh','tj','hb')
1 2 3
datetime
timestamp
1. 約束
1.1 PrimaryKey(PK): 主鍵
特點: 唯一+非空,一張表中只能有一個主鍵約束.一般是一個數(shù)字列.最好是無意義的.
1.2 NOT NULL 非空
特點: 不能為空,我們建議業(yè)務(wù)關(guān)鍵列(索引列),盡量設(shè)置成非空.
1.3 UNIQUE 唯一約束
特點: 不能有重復(fù)值.一般像手機號,身份證號,qq,郵箱.....
1.4 unsigned 數(shù)字列無符號.
特點: 必須要加載數(shù)字列后,表示數(shù)字無負數(shù).一般適用于年齡....
2. 其他屬性
2.1 AUTO_INCREMENT 自增長
特點: 適用于ID主鍵列.
2.2 DEFAULT 默認值
特點: 使用在NOT NULL 列中,不填寫值時,自動生成默認值。
2.3 COMMENT 注釋
特點: 建議每個列都有一個注釋.
10張表: -----> 每個表每個列加注釋.
3. DCL 數(shù)據(jù)控制語言
grant 授權(quán)
revoke 回收權(quán)限
4. DDL應(yīng)用
4.1 庫
4.1.1 增
CREATE DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;
4.1.2 刪 (危險,不代表生產(chǎn)操作.)
DROP DATABASE oldguo;
4.1.3 改
ALTER DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;
4.1.4 查(DQL)
mysql> show databases;
mysql> show create database oldboy;
4.1.5 規(guī)范
(1) 庫名要和業(yè)務(wù)有關(guān)
(2) 庫名不能有大寫字母,可以有小寫字符,數(shù)字,特殊符號.
為什么?
CREATE DATABASE OLDGUO CHARSET utf8mb4 COLLATE utf8mb4_bin;
(3) 庫名不能是數(shù)字開頭
(4) 庫名不能是預(yù)留字符
(5) 庫名不要超過18個字符.
(6) 必須要設(shè)置字符集.盡量是utf8mb4.
(7) 收回所有用戶的DROP權(quán)限.
4.2 表定義
4.2.1 增
學(xué)生表:student
drop table student;
CREATE TABLE `student` (
`xid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
`xname` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
`xage` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年齡',
`xsex` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性別',
`xtel` char(14) COLLATE utf8mb4_bin NOT NULL COMMENT '手機號',
`xcard` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份證號',
`xaddr` enum('北京市','上海市','深圳市','山東省','甘肅省','河北省','山西省','河南省','遼寧省','吉林省','黑龍江省','內(nèi)蒙古自治區(qū)','新疆維吾爾自治區(qū)','四川省','陜西省','江蘇省','福建省','湖北省','廣東省','廣西省') COLLATE utf8mb4_bin NOT NULL DEFAULT '北京市' COMMENT '地區(qū)',
`xdate` datetime DEFAULT NULL COMMENT '入學(xué)時間',
PRIMARY KEY (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;
INSERT INTO student(xid, xname , xage ,xsex , xtel , xcard ,xaddr ,xdate)
VALUES
(1 , '張三' , 11 , 'm' , '110' , '660', '北京市', '2019-01-01'),
(2 , '馬六' , 13 , 'm' , '111' , '661', '上海市', '2019-01-01'),
(3 , '李四' , 14 , 'm' , '112' , '662', '北京市', '2019-01-01'),
(4 , '王五' , 17 , 'm' , '113' , '663', '山東省', '2019-01-01'),
(5 , '鐵錘' , 18 , 'f' , '114' , '664', '河南省', '2019-01-01'),
(6 , '鋼蛋' , 13 , 'f' , '115' , '665', '河北省', '2019-01-01'),
(7 , '孫悟空' , 19 , 'm' , '116' , '666', '山西省', '2019-01-01'),
(8 , '豬八戒' , 21 , 'm' , '117' , '667', '河北省', '2019-01-01'),
(9 , '唐僧' ,23 , 'm' , '118' , '668' , '吉林省', '2019-01-01'),
(10, '沙僧' ,31 , 'm' , '120' , '669' , '遼寧省', '2019-01-01'),
(11, '白龍馬' ,26 , 'm' , '119' , '670' , '廣西省', '2019-01-01') ,
(12, '牛魔王' ,19 , 'm' , '121' , '671' , '四川省', '2019-01-01'),
(13, '張無忌' ,20 , 'm' , '122' , '672' , '福建省', '2019-01-01'),
(14, '趙敏' ,28 , 'f' , '123' , '673' , '廣東省', '2019-01-01'),
(15, '郭靖' ,29 , 'm' , '124' , '674' , '甘肅省', '2019-01-01'),
(16, '黃蓉' ,17 , 'f' , '125' , '675' , '深圳市', '2019-01-01'),
(17, '小龍女' ,22 , 'f' , '126' , '766' , '黑龍江省', '2019-01-01'),
(18, '楊過' ,33 , 'm' , '127' , '777' , '新疆維吾爾自治區(qū)', '2019-01-01'),
(19, '歐陽峰' ,25 , 'm' , '128' , '888' , '內(nèi)蒙古自治區(qū)', '2019-01-01'),
(20, '小沈陽' ,23 , 'm' , '129' , '999' , '陜西省', '2019-01-01');
SELECT * FROM student;
# 課程表:course
drop table course;
CREATE TABLE course (
cid INT NOT NULL PRIMARY KEY COMMENT '課程編號',
cname VARCHAR(64) NOT NULL COMMENT '課程名稱',
tid CHAR(5) NOT NULL COMMENT '講師名',
cprice INT NOT NULL COMMENT '課程價格'
)ENGINE=INNODB CHARSET=utf8mb4;
insert into course(cid, cname , tid ,cprice )
values
(1001, 'linux' ,'t0001' ,19800),
(1002, 'python' ,'t0002' ,21800),
(1003, 'golang' ,'t0003' ,16000),
(1004, 'DBA' ,'t0004' ,15000),
(1005, 'safe' ,'t0005' ,17800);
# 教師表 : teacher
CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT '教師編號',
tname VARCHAR(64) NOT NULL COMMENT '教師姓名',
tage TINYINT NOT NULL DEFAULT 99 COMMENT '教師年齡',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT '教師性別',
tyear TINYINT NOT NULL DEFAULT 3 COMMENT '工作年限',
txl VARCHAR(64) NOT NULL DEFAULT '本科' COMMENT '學(xué)歷',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT '級別:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;
# 成績表 : score
DROP TABLE score;
CREATE TABLE score (
xid INT NOT NULL COMMENT '學(xué)生編號',
cid INT NOT NULL COMMENT '課程編號',
score INT NOT NULL DEFAULT 0 COMMENT '課程分數(shù)',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT '是否缺考:1缺考,0未缺考'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO score(xid , cid , score ,quekao)
VALUES
(1 ,1001 ,80 ,0),
(1 ,1002 ,70 ,0),
(2 ,1001 ,0 ,1),
(2 ,1003 ,90 ,0),
(3 ,1004 ,80 ,0),
(4 ,1004 ,100 ,0),
(5 ,1005 ,60 ,0),
(4 ,1005 ,30 ,0),
(5 ,1002 ,60 ,0),
(6 ,1002 ,45 ,0),
(7 ,1003 ,67 ,0),
(7 ,1004 ,98 ,0),
(8 ,1004 ,76 ,0),
(9 ,1001 ,80 ,0),
(10 ,1002 ,99 ,0),
(11 ,1003 ,40 ,0),
(11 ,1004 ,50 ,0),
(12 ,1005 ,0 ,1),
(12 ,1003 ,90 ,0),
(13 ,1001 ,30 ,0),
(14 ,1002 ,100 ,0),
(15 ,1003 ,60 ,0),
(14 ,1004 ,30 ,0),
(15 ,1005 ,60 ,0),
(16 ,1002 ,45 ,0),
(17 ,1003 ,67 ,0),
(17 ,1004 ,98 ,0),
(18 ,1004 ,76 ,0),
(19 ,1005 ,75 ,0),
(20 ,1002 ,68 ,0);
======================================
建表規(guī)范: *****
- 表名: 不能大寫字母,和業(yè)務(wù)有關(guān),不能數(shù)字開頭,長度控制在18字符以內(nèi),不能和關(guān)鍵字同名.
- 要設(shè)置存儲引擎類型:INNODB,要設(shè)置字符集
- 列名要有意義
- 合適的,完整的,簡短的數(shù)據(jù)類型.(會影響到索引的性能)
- 每個表要有主鍵,實在不知道怎么設(shè)置,也要找一個無關(guān)的自增長列設(shè)置為主鍵.
- 盡量每個列都有not null (特別是將來要做為索引的列)
- 每個列要有注釋信息
======================================
小擴展:
mysql> create table teacher_bak like teacher;
4.2.2 刪(危險! 謹慎操作!)
mysql> drop table teacher_bak;
mysql> truncate table teacher;
面試: 請你說明 drop table truncate delete table 區(qū)別 ?
drop table : 表結(jié)構(gòu)+數(shù)據(jù)(物理性刪除)
truncate table : 數(shù)據(jù)(清空數(shù)據(jù)頁)
delete from table: 清空數(shù)據(jù)行(逐行刪除)
4.2.3 改 *****
(1) 添加列
DESC xuesheng;
ALTER TABLE xuesheng ADD xqq BIGINT NOT NULL UNIQUE COMMENT 'qq號';
ALTER TABLE xuesheng ADD wechat BIGINT NOT NULL UNIQUE COMMENT '微信號' AFTER xtel;
ALTER TABLE xuesheng ADD mail BIGINT NOT NULL UNIQUE COMMENT '郵箱' FIRST;
(2) 刪除列
ALTER TABLE xuesheng DROP mail;
ALTER TABLE xuesheng DROP xqq;
ALTER TABLE xuesheng DROP wechat;
ALTER TABLE xuesheng DROP mail;
(3) 修改
- 修改表名:
ALTER TABLE xuesheng RENAME TO student;
面試題:
上億行的數(shù)據(jù)規(guī)劃:
1. 按月歸檔表
2. 沒用的歷史表進行挪走或刪除
pt-archiver 自己擴展 *****
2. 修改某一列的屬性信息
DESC student;
ALTER TABLE student MODIFY xname VARCHAR(128) NOT NULL COMMENT '姓名';
3. 修改列名和屬性
ALTER TABLE student CHANGE xsex xgender CHAR(2) NOT NULL DEFAULT 'm' COMMENT '性別';
注意:
執(zhí)行alter語句,都是需要進行鎖表操作的,此時只能發(fā)生查詢操作,不能做修改操作.
我們建議,alter語句,盡量在業(yè)務(wù)不繁忙期間發(fā)生.如果非得線上操作,建議使用pt-osc工具進行.
4.2.4 查
show tables ;
desc teacher;
show create table xx;
5. DML 數(shù)據(jù)操作語言
5.1 insert 插入數(shù)據(jù)
# 學(xué)生表:student
drop table student;
CREATE TABLE `student` (
`xid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
`xname` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
`xage` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年齡',
`xsex` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性別',
`xtel` char(14) COLLATE utf8mb4_bin NOT NULL COMMENT '手機號',
`xcard` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份證號',
`xaddr` enum('北京市','上海市','深圳市','山東省','甘肅省','河北省','山西省','河南省','遼寧省','吉林省','黑龍江省','內(nèi)蒙古自治區(qū)','新疆維吾爾自治區(qū)','四川省','陜西省','江蘇省','福建省','湖北省','廣東省','廣西省') COLLATE utf8mb4_bin NOT NULL DEFAULT '北京市' COMMENT '地區(qū)',
`xdate` datetime DEFAULT NULL COMMENT '入學(xué)時間',
PRIMARY KEY (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;
INSERT INTO student(xid, xname , xage ,xsex , xtel , xcard ,xaddr ,xdate)
VALUES
(1 , '張三' , 11 , 'm' , '110' , '660', '北京市', '2019-01-01'),
(2 , '馬六' , 13 , 'm' , '111' , '661', '上海市', '2019-01-01'),
(3 , '李四' , 14 , 'm' , '112' , '662', '北京市', '2019-01-01'),
(4 , '王五' , 17 , 'm' , '113' , '663', '山東省', '2019-01-01'),
(5 , '鐵錘' , 18 , 'f' , '114' , '664', '河南省', '2019-01-01'),
(6 , '鋼蛋' , 13 , 'f' , '115' , '665', '河北省', '2019-01-01'),
(7 , '孫悟空' , 19 , 'm' , '116' , '666', '山西省', '2019-01-01'),
(8 , '豬八戒' , 21 , 'm' , '117' , '667', '河北省', '2019-01-01'),
(9 , '唐僧' ,23 , 'm' , '118' , '668' , '吉林省', '2019-01-01'),
(10, '沙僧' ,31 , 'm' , '120' , '669' , '遼寧省', '2019-01-01'),
(11, '白龍馬' ,26 , 'm' , '119' , '670' , '廣西省', '2019-01-01') ,
(12, '牛魔王' ,19 , 'm' , '121' , '671' , '四川省', '2019-01-01'),
(13, '張無忌' ,20 , 'm' , '122' , '672' , '福建省', '2019-01-01'),
(14, '趙敏' ,28 , 'f' , '123' , '673' , '廣東省', '2019-01-01'),
(15, '郭靖' ,29 , 'm' , '124' , '674' , '甘肅省', '2019-01-01'),
(16, '黃蓉' ,17 , 'f' , '125' , '675' , '深圳市', '2019-01-01'),
(17, '小龍女' ,22 , 'f' , '126' , '766' , '黑龍江省', '2019-01-01'),
(18, '楊過' ,33 , 'm' , '127' , '777' , '新疆維吾爾自治區(qū)', '2019-01-01'),
(19, '歐陽峰' ,25 , 'm' , '128' , '888' , '內(nèi)蒙古自治區(qū)', '2019-01-01'),
(20, '小沈陽' ,23 , 'm' , '129' , '999' , '陜西省', '2019-01-01');
SELECT * FROM student;
課程表:course
drop table course;
CREATE TABLE course (
cid INT NOT NULL PRIMARY KEY COMMENT '課程編號',
cname VARCHAR(64) NOT NULL COMMENT '課程名稱',
tid CHAR(5) NOT NULL COMMENT '講師名',
cprice INT NOT NULL COMMENT '課程價格'
)ENGINE=INNODB CHARSET=utf8mb4;
insert into course(cid, cname , tid ,cprice )
values
(1001, 'linux' ,'t0001' ,19800),
(1002, 'python' ,'t0002' ,21800),
(1003, 'golang' ,'t0003' ,16000),
(1004, 'DBA' ,'t0004' ,15000),
(1005, 'safe' ,'t0005' ,17800);
教師表 : teacher
CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT '教師編號',
tname VARCHAR(64) NOT NULL COMMENT '教師姓名',
tage TINYINT NOT NULL DEFAULT 99 COMMENT '教師年齡',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT '教師性別',
tyear TINYINT NOT NULL DEFAULT 3 COMMENT '工作年限',
txl VARCHAR(64) NOT NULL DEFAULT '本科' COMMENT '學(xué)歷',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT '級別:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;
成績表 : score
DROP TABLE score;
CREATE TABLE score (
xid INT NOT NULL COMMENT '學(xué)生編號',
cid INT NOT NULL COMMENT '課程編號',
score INT NOT NULL DEFAULT 0 COMMENT '課程分數(shù)',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT '是否缺考:1缺考,0未缺考'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO score(xid , cid , score ,quekao)
VALUES
(1 ,1001 ,80 ,0),
(1 ,1002 ,70 ,0),
(2 ,1001 ,0 ,1),
(2 ,1003 ,90 ,0),
(3 ,1004 ,80 ,0),
(4 ,1004 ,100 ,0),
(5 ,1005 ,60 ,0),
(4 ,1005 ,30 ,0),
(5 ,1002 ,60 ,0),
(6 ,1002 ,45 ,0),
(7 ,1003 ,67 ,0),
(7 ,1004 ,98 ,0),
(8 ,1004 ,76 ,0),
(9 ,1001 ,80 ,0),
(10 ,1002 ,99 ,0),
(11 ,1003 ,40 ,0),
(11 ,1004 ,50 ,0),
(12 ,1005 ,0 ,1),
(12 ,1003 ,90 ,0),
(13 ,1001 ,30 ,0),
(14 ,1002 ,100 ,0),
(15 ,1003 ,60 ,0),
(14 ,1004 ,30 ,0),
(15 ,1005 ,60 ,0),
(16 ,1002 ,45 ,0),
(17 ,1003 ,67 ,0),
(17 ,1004 ,98 ,0),
(18 ,1004 ,76 ,0),
(19 ,1005 ,75 ,0),
(20 ,1002 ,68 ,0);
====================
5.2 update
SELECT * FROM student;
UPDATE student SET xname='王鋼蛋' WHERE xid=6;
UPDATE student SET xname='李鐵錘' WHERE xid=5;
5.3 delete
INSERT INTO student VALUES(21,'王二麻子',22,'f','921','345','上海市','2020-01-01');
DELETE FROM student WHERE xid=21;
擴展:
偽刪除
(1) 添加狀態(tài)列 is_del (1代表刪除,0代表有效)
ALTER TABLE student ADD is_del TINYINT NOT NULL DEFAULT 0 COMMENT '1代表刪除,0代表有效';
SELECT * FROM student;
(2) delete ---> update
原語句:
delete from student where xid=20;
改為 :
update student set is_del=1 where xid=20;
(3) 更改業(yè)務(wù)查詢方法
原語句:
SELECT * FROM student;
改為:
SELECT * FROM student where is_del=0;
====================
6. DQL
select
1.2 SELECT 配合其他子句使用 *****
1.2.1 子句列表介紹
FROM -- 查詢對象(表,視圖)
WHERE -- 過濾子句(grep)
GROUP BY -- 分組子句(統(tǒng)計分析類)
ORDER BY -- 排序子句
HAVING -- 后過濾子句
LIMIT -- 限制子句(分頁子句)
