Mysql子查询及索引

建库

CREATE DATABASE IF NOT EXISTS day14 DEFAULT CHARSET UTF8;

指定库

USE day14;

建表

CREATE TABLE IF NOT EXISTS emp
(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(30),
salary VARCHAR(20),
dept_id INT,
manager_id INT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS empInfo
(
empno INT PRIMARY KEY AUTO_INCREMENT=1001,
ename VARCHAR(30),
job VARCHAR(20),
mjr INT,
hiredate DATE,
sal DOUBLE,
comm DOUBLE,
deptno INT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO empInfo (ename,job,mjr,hiredate,sal,comm,deptno)
VALUES
('甘宁','文员',1013,'2000-12-17',8000.00,NULL,20),
('黛绮丝','销售员',1006,'2001-02-20',16000.00,3000.00,20),
('殷天正','销售员',1006,'2001-02-22',12500.00,5000.00,30),
('刘备','经理',1009,'2001-04-02',29750.00,NULL,20),
('谢逊','销售员',1006,'2001-09-28',12500.00,14000.00,30),
('关羽','经理',1009,'2001-05-01',28500.00,NULL,30),
('张飞','经理',1009,'2001-09-01',24500.00,NULL,10),
('诸葛亮','分析师',1004,'2007-04-19',30000.00,NULL,20),
('张无忌','董事长',NULL,'2001-11-17',50000.00,NULL,10),
('韦一笑','销售员',1006,'2001-09-08',15000.00,0.00,30),
('周泰','文员',1008,'2007-05-23',11000.00,NULL,30),
('程普','文员',1006,'2001-12-03',9500.00,NULL,30),
('庞统','分析师',1004,'2001-12-03',30000.00,NULL,20),
('黄盖','文员',1007,'2002-01-23',13000.00,NULL,10),
('张三','保洁员',1001,'2013-05-01',80000.00,5000.00,50);

插入数据

INSERT INTO emp(emp_name,salary,dept_id,manager_id)
VALUES
("tom",15000,1,NULL),
("lucy",16000,2,NULL),
("morty",14000,5,NULL),
("张建国",4000,1,1),
("李华",6000,1,1),
("孙思妙",3000,1,1),
("王强",6000,2,2),
("周杰",5000,2,2),
("曹光思",7000,5,3),
("刘毅真",8000,5,3),
("孙岩",5000,5,3);

子查询

点击查看代码

SELECT emp_name,salary FROM emp WHERE salary >= 
(SELECT salary FROM emp WHERE emp_name="王强") AND emp_name!="王强";

SELECT * FROM emp WHERE dept_id=
(SELECT dept_id FROM emp WHERE emp_name='张建国')
AND salary >
(SELECT salary FROM emp WHERE emp_name='孙岩');

子查询,多值

点击查看代码

SELECT * FROM emp WHERE dept_id IN
(SELECT dept_id FROM emp WHERE emp_name LIKE '孙%');

#表子查询,子查询做'临时结果表'

SELECT e.* FROM (SELECT * FROM emp WHERE salary > 5000 AND dept_id > 2) e;

索引优化

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;

USE info;

创建表

CREATE TABLE IF NOT EXISTS emp
(emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
salary INT,
dept_id INT,
manager_id INT
);

直接添加索引

点击查看代码

CREATE INDEX index_emp_name ON emp(emp_name);
SELECT * FROM emp WHERE emp_name = "liu";

修改表时添加索引

ALTER TABLE emp ADD INDEX index_salary (salary);

创建表时添加索引

删除索引

DROP INDEX index_salary ON emp;
DROP INDEX index_emp_name ON emp;
DROP INDEX index_title ON article;

点击查看代码

CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20) COMMENT '标题',
content VARCHAR(100) COMMENT '内容',
dt	DATE,
INDEX index_title (title)
);

创建唯一索引

DROP INDEX index_title_content ON article;

CREATE INDEX index_title_content ON article(title,content);

ALTER TABLE article ADD INDEX index_title_content (title,content);

DROP TABLE article;

点击查看代码

CREATE UNIQUE INDEX index_emp_name ON emp(emp_name);
ALTER TABLE emp ADD UNIQUE INDEX index_salary (salary);


CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20) COMMENT '标题',
content VARCHAR(100) COMMENT '内容',
dt	DATE,
UNIQUE INDEX index_title (title)
);

创建组合索引

点击查看代码

CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20) COMMENT '标题',
content VARCHAR(100) COMMENT '内容',
dt	DATE,
INDEX index_title_content (title,content)
);

SELECT * FROM article WHERE title = "冬奥会" AND content = "金牌数量";