MySQL高級部分-建表語句

B站尚硅谷
MySQL周陽老師的建表語句

第13節(jié)相關(guān)SQL語句：

CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(20) DEFAULT NULL, 
`deptId` int(11) DEFAULT NULL, 
PRIMARY KEY (`id`) , 
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT, 
`deptName` varchar(30) DEFAULT NULL,
 `locAdd` varchar(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

第31節(jié)-單表分析

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

開始優(yōu)化

explain select id from article where category_id = 1 and  comments>1 order by views desc limit 1;

未使用索引之前：

image.png

第一次優(yōu)化：創(chuàng)建索引

create index idx_article_ccv on article(category_id,comments,views);

使用索引之后：

image.png

如果將范圍改為等于的情況下：

image.png

刪除索引

drop index idx_article_ccv on article;

重新創(chuàng)建索引

create index idx_category_view on article (category_id,views);

image.png

P32 兩表

-- 商品類別表
CREATE TABLE IF NOT EXISTS class (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS book (
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY ( bookid)
);


INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

分析

explain select * from class left join book on class.card = book.card;

image.png

創(chuàng)建book的索引

create index idx_b_card on book(card);

image.png

drop index idx_b_card on book;

創(chuàng)建class表的索引

create index idx_c_card on class;

image.png

索引需要加在被關(guān)聯(lián)表中

第33節(jié)-三表

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

分析語句：

explain select * from class left join phone on class.card = phone.card  left join book on book.card = class.card;

創(chuàng)建索引

create index idx_p_card on phone(card);
create index idx_b_card on book(card);

image.png

第34節(jié)

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年齡',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'職位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入職時間'
)CHARSET utf8 COMMENT'員工記錄表';


INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

SELECT  * FROM staffs;
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);

索引失效分析

explain select * from staffs where name='July';

image.png

explain select * from staffs where name='July' and age=25;

image.png

explain select * from staffs where name='July' and age=25 and pos='dev';

image.png

explain select * from staffs where age=25 and pos='dev';

image.png

第40節(jié)

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO tbl_user(NAME ,age,email) VALUES('1aa1',21 ,'b@163.com');
INSERT INTO tbl_user(NAME ,age,email) VALUES('2aa2' ,222,'a@163.com');
INSERT INTO tbl_user(NAME ,age,email) VALUES('3aa3' ,265, 'c@163.com');
INSERT INTO tbl_user(NAME, age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME, age,email) VALUES('aa',121,'e@163.com');

44節(jié)

create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

50節(jié)

create database bigData;
use bigData;
#1建表dept
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT '',
loc VARCHAR(13) NOT NULL DEFAULT ''
) ENGINE=INNODB DEFAULT CHARSET=GBK ;

#員工表
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, 
ename VARCHAR(20) NOT NULL DEFAULT '', 
job VARCHAR(9) NOT NULL DEFAULT '',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
hiredate DATE NOT NULL,
sal DECIMAL(7,2) NOT NULL,
comm DECIMAL(7,2) NOT NULL,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
)ENGINE=INNODB DEFAULT CHARSET=GBK ;


show variables like 'log_bin_trust_function_creators';


DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqr stuvwxyzABCDEFJHIJKIMNOPQRSTUVWXYZ' ;
DECLARE return_str VARCHAR(255) DEFAULT '' ;
DECLARE i INT DEFAULT 0;
WHILE i<n DO
SET return_str =CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND() *52) ,1)) ; 
SET i = i+1;
END WHILE;
RETURN return_str;
END $$

DELIMITER $$
CREATE FUNCTION
rand_num ( )
RETURNS INT (5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(100+RAND() *10);
RETURN i ;
END $$

存儲過程-插入員工表數(shù)據(jù)
delimiter $$ 
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

存儲過程-插入部門表數(shù)據(jù)
delimiter $$ 
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$

調(diào)用存儲過程

delimiter ;
call insert_dept(100,10);

第57節(jié)

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;
insert into test_innodb_lock values(1,'b2'); 
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4, '4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');
create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

最后編輯于：2020.12.22 15:13:44

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市若治，隨后出現(xiàn)的幾起案子动雹，更是在濱河造成了極大的恐慌皱卓，老刑警劉巖棋嘲，帶你破解...
沈念sama閱讀 221,635評論 6贊 515
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件茴她，死亡現(xiàn)場離奇詭異珊膜，居然都是意外死亡吗跋，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,543評論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門哨颂，熙熙樓的掌柜王于貴愁眉苦臉地迎上來喷市，“玉大人，你說我怎么就攤上這事威恼∑沸眨” “怎么了？”我有些...
開封第一講書人閱讀 168,083評論 0贊 360
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵沃测，是天一觀的道長缭黔。經(jīng)常有香客問我，道長蒂破，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 59,640評論 1贊 296
?港島之戀（遺憾婚禮）
正文為了忘掉前任别渔，我火速辦了婚禮附迷，結(jié)果婚禮上惧互，老公的妹妹穿的比我還像新娘。我一直安慰自己喇伯，他們只是感情好喊儡，可當我...
茶點故事閱讀 68,640評論 6贊 397
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著稻据，像睡著了一般艾猜。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上捻悯，一...
開封第一講書人閱讀 52,262評論 1贊 308
城市分裂傳說
那天匆赃，我揣著相機與錄音，去河邊找鬼今缚。笑死算柳，一個胖子當著我的面吹牛，可吹牛的內(nèi)容都是我干的姓言。我是一名探鬼主播瞬项，決...
沈念sama閱讀 40,833評論 3贊 421
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼何荚！你這毒婦竟也來了囱淋？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 39,736評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤餐塘，失蹤者是張志新（化名）和其女友劉穎绎橘，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體唠倦，經(jīng)...
沈念sama閱讀 46,280評論 1贊 319
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡称鳞，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 38,369評論 3贊 340
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了稠鼻。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片冈止。...
茶點故事閱讀 40,503評論 1贊 352
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖候齿，靈堂內(nèi)的尸體忽然破棺而出熙暴，到底是詐尸還是另有隱情，我是刑警寧澤慌盯，帶...
沈念sama閱讀 36,185評論 5贊 350
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布周霉，位于F島的核電站，受9級特大地震影響亚皂，放射性物質(zhì)發(fā)生泄漏俱箱。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 41,870評論 3贊 333
男人毒藥：我在死后第九天來索命
文/蒙蒙一灭必、第九天我趴在偏房一處隱蔽的房頂上張望狞谱。院中可真熱鬧乃摹，春花似錦、人聲如沸跟衅。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,340評論 0贊 24
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽伶跷。三九已至掰读，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間叭莫，已是汗流浹背蹈集。一陣腳步聲響...
開封第一講書人閱讀 33,460評論 1贊 272
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留食寡，地道東北人雾狈。一個月前我還...
沈念sama閱讀 48,909評論 3贊 376
代替公主和親
正文我出身青樓，卻偏偏與公主長得像抵皱，于是被迫代替她去往敵國和親善榛。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 45,512評論 2贊 359

MySQL高級部分-建表語句

第13節(jié)相關(guān)SQL語句：

第31節(jié)-單表分析

P32 兩表

第33節(jié)-三表

第34節(jié)

第40節(jié)

44節(jié)

50節(jié)

第57節(jié)

推薦閱讀更多精彩內(nèi)容