©2020-2024 ioDraw All rights reserved
<>一、单表P209|P31
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'); select
* from article;
<>二、双表P210|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))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
<>三、三表P211|P33
CREATE TABLE IF NOT EXISTS `phone`( `phoneid` INT(10) UNSIGNED NOT NULL
AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`phoneid`) )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)));
<>四、P212|P34
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);
<>五、P218|P40
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');
<>六、P222|P44，索引面试题讲解
CREATE TABLE test03( id int primary key not null auto_increment, c1 char(10),
c2char(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');
select * from test03;
<>七、P226|P48
CREATE TABLE tblA( #id int primary key not null autp_increment, age int, birth
timestamp not null ); insert into tblA(age,birth) values(22,now()); insert into
tblA(age,birth) values(23,now()); insert into tblA(age,birth) values(24,now());
CREATE INDEX idx_A_ageBirth on tblA(age,birth); select * from tblA;
<>八、P228|P50
create database bigData; use bigData; CREATE TABLE `dept` ( `id` int(10)
unsigned NOT NULL AUTO_INCREMENT, `deptno` mediumint(8) unsigned NOT NULL
DEFAULT '0', `dname` varchar(20) NOT NULL DEFAULT '', `loc` varchar(13) NOT NULL
DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=GBK; CREATE TABLE
`emp` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `empno` mediumint(8)
unsigned NOT NULL DEFAULT '0',/*编号*/ `ename` varchar(20) NOT NULL DEFAULT '',
/*名字*/ `job` varchar(9) NOT NULL DEFAULT '',/*工作*/ `mgr` mediumint(8) unsigned
NOT NULL DEFAULT '0',/*上级编号*/ `hiredate` date NOT NULL,/*入职时间*/ `sal` decimal(7,
2) NOT NULL,/*薪水*/ `comm` decimal(7,2) NOT NULL,/*红利*/ `deptno` mediumint(8)
unsigned NOT NULL DEFAULT '0',/*部门编号*/ PRIMARY KEY (`id`) ) ENGINE=InnoDB
DEFAULT CHARSET=GBK; DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS
VARCHAR(255) BEGIN DECLARE char_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str
VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str
= CONCAT(return_str,SUBSTRING(char_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 $$ 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 $$ 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 $$
<>九、P232|P54
create table mylock( id int not null primary key auto_increment, name varchar(
20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(
name) values('b'); insert into mylock(name) values('c'); insert into mylock(name
) values('d'); insert into mylock(name) values('e'); select * from mylock;
<>十、P235|P57
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_lockvalues(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); select * from
test_innodb_lock;