##if函数 if else的效果 SELECT IF(10<5,'大','小'); [小] ##case函数 1、switch case的效果 (等值) ## case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; ## when 常量2 then 要显示的值2或语句2; ## else 要显示的值n或语句n; ## end 2、类似于多重if (区间判断) ## case ## when 条件1 then 要显示的值1或语句1 ## when 条件2 then 要显示的值2或语句2 ## ... ## else 要显示的值n或语句n ## end 》》》案例1：查询员工的工资 如果部门号=30，显示的工资为1.1倍 如果部门号=40，显示的工资为1.2倍 如果部门号=50，显示的工资为1.3倍 其他部门，显示的工资为原工资 SELECT salary 原始工资,department_id, CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工资 FROM employees; [表达式之间不能加分号！！！语句之间需要] 》》》案例2：查询出员工的工资情况 如果工资>20000,显示A级别 如果工资>15000,显示B级别 如果工资>10000,显示C级别 否则，显示D级别 SELECT salary, CASE WHEN salary > 20000 THEN 'A' WHEN salary > 15000 THEN 'B' WHEN salary > 10000 THEN 'C' ELSE 'D' END AS 工资级别 FROM employees;
##添加分组后筛选 SELECT 分组函数,列（要求出现在group by的后面） FROM 表 WHERE 筛选条件 GROUP BY 分组的列表 ORDER BY 子句 ##特点： 1、分组查询中的筛选条件分为两类 数据源 位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果集 group by子句的后面 having >>> a)、分组函数做条件肯定要放在having子句中 b)、能用分组前筛选的，优先考虑分组前筛选 2、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求） 也支持表达式或函数（用的较少） 3、可以添加排序（排序放在整个分组查询的最后） 》》》案例：查询哪个部门的员工个数>2 SELECT COUNT(*),department_id FROM employees GROUP BY department_id Having COUNT(*)>2 按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些 SELECT COUNT(*) c, LENGTH(last_name) len_name FROM employees GROUP BY len_name HAVING c>5; ##按多个字段分组 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id; ##添加排序 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id ORDER BY AVG(salary) DESC;
连接查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
##内连接 交集部分 SELECT 查询列表 FROM 表1 别名 INNER JOIN 表2 别名 ON 连接条件; ##外连接 外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录 全外连接=内连接的结果+表1中有但表2没有的+表2有但表1没有的 ##左外连接 left join 左边的是主表 SELECT b.name bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id; ##右外连接 right join 右边的是主表 SELECT b.name bo.* FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id;
##交叉连接 两个表做笛卡尔乘积 SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;
##WHERE或HAVING后面 ###标量子查询（单行子查询） 1、查询谁的工资比Tom高 SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name = 'Tom'); 2、查询job_id和141号员工相同，salary比143号员工多的员工 姓名、job_id和工资 SELECT last_name,job_id,salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employees_id=141) AND salary > (SELECT salary FROM employees WHERE employees_id =13); ###列子查询（多行子查询） 1、查询localtion_id是1400或1700的部门中所有员工姓名 SELECT last_name FROM employees WHERE department_id IN( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ); 2、查询其它工种中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < ANY( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'); 或 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < ( SELECT DISTINCT MAX(salary) FROM employees WHERE job_id = 'IT_PROG'); ###行子查询（多列多行） 查询员工编号最小并且工资最高的员工信息 SELECT * FROM employees WHERE employee_id = (SELECT MIN(employee_id) FROM employees) AND salary = (SELECT MAX(salary) FROM employees); 或 SELECT * FROM employees WHERE (employee_id,salary) = ( SELECT MIN(employee_id),MAX(salary) FROM employees); ##exists后面（相关子查询） ##exists(完整的查询语句) ##结果：1或0 》》》案例：查询有员工名的部门名 SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.department_id = e.department_id); 或 SELECT department_name FROM departments d WHERE d.department_id IN ( SELECT department_id FROM employees); 》》》**案例：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资 SELECT employee_id,last_name,salary e.department_id FROM employees e INNER JOIN ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) ag_dep ON e.department_id = ag_dep.department_id WHERE salary > ag_dept.ag;
》》》案例1：查询前5条员工信息 SELECT * FROM employees LIMIT(0,5); 案例2：查询第11条——第25条数据 SELECT * FROM employees LIMIT(10,15); 案例3：有奖金的员工信息，并且工资较高的前10名显示出来 SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;
》》》案例：查询部门编号>90或邮箱包含a的员工信息 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90; 或 SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id > 90;
三、数据操作语言（DML）
插入语句
1 2 3 4 5 6 7 8 9 10 11 12
INSERT INTO 表名 (列名，列名,...) VALUES (值1,值2,...) INSERT INTO 表名 SET 列名 = 值,列名 = 值,...
##1、插入的值的类型要与列的类型一致或兼容 ##2、不可以为NULL的列必须插入值，可以为NULL的列插入值可以为NULL或将列名去掉 ##3、列数和值的个数必须一致 ##4、可以省略列名，默认所有列，列的顺序和表中列的顺序一致 》》》语句示例： INSERT INTO stuinfo (id,name,sex,phone) VALUES (12,'唐艺昕','女',NULL); INSERT INTO stuinfo (id,name,sex) VALUES (12,'唐艺昕','女'); INSERT INTO stuinfo SET id = 19,name = '张飞',phone = '999';
》》》语句示例： UPDATE stuinfo SET phone = '232343242' WHERE name LIKE '唐%'; UPDATE stuinfo SET phone = '232343242',age = 20 WHERE name LIKE '唐%'; UPDATE stuinfo stu INNER JOIN course c ON stu.id = c.id SET stu.phone = '23414' WHERE stu.name = '张无忌';
###多表删除 DELETE 表1的别名,表2的别名 FROM 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 WHERE 筛选条件; ##方式二： ###整个表的数据都删除 TRUNCATE TABLE 表名; ##特点： ##1、如果要删除的表中有自增长列 ##用DELETE删除后，再插入数据，自增长列的值从断点开始 ##用TRUNCATE删除后，再插入数据，自增长列的值从1开始 ##2、TRUNCATE删除没有返回值（影响行数），DELETE删除有返回值 ##3、TRUNCATE删除不能回滚，DELETE删除可以回滚 》》》语句示例： DELETE FROM stuinfo WHERE phone LIKE '%9'; DELETE FROM stu FROM stuinfo stu INNER JOIN course c ON stu.id = c.stuid; WHERE stu.name = '张无忌'; TRUNCATE TABLE stuinfo;
四、数据定义语言（DDL）
库的管理
1 2 3 4 5 6 7 8 9 10 11
## CREATE 创建数据库 CREATE DATEBASE 库名; CREATE DATEBASE IF NOT EXISTS 库名; (如果库已存在则不创建，如果库不存在则创建) ## ALTER 修改数据库 不建议修改 ##可修改库的字符集 ALTER DATEBASE 库名 CHARACTER SET gbk; ## DROP 删除数据库 DROP DATABASE 库名; DROP DATABASE IF EXISTS 库名; (如果存在则删除)
##创建表时添加列级约束 CREATE TABLE stuinfo( id INT PRIMARY KEY, #主键 stuName VARCHAR(20) NOT NULL, #非空 sex CHAR(1) CHECK(gender = '男' OR gender = '女'), #检查 seat INT UNIQUE, #唯一 age INT DEFAULT 18, #默认约束 majorId INT REFERENCES major(id) #外键 ); CREATE TABLE major( id INT PRIMARY KEY, marjorName VARCHAR(20) );
##通用的写法 CREATE TABLE IF NOT EXISTS stuinfo( id INT PRIMARY KEY, stuname VARCHAR(20) NOT NULL, sex CHAR(1), age INT DEFAULT 18, seat INT UNIQUE, majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) );
##修改表时添加约束 ##添加列级约束 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束; ##添加表级约束 ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】约束类型 (字段名) 【外键的引用】; 【】可省略
##1、添加非空约束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL; ##2、添加默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18; ##3、添加主键 ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY; 或 ALTER TABLE stuinfo ADD PRIMARY KEY(id);
##4、添加唯一键 ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE; 或 ALTER TABLE stuinfo ADD UNIQUE(seat);
##5、添加外键 ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id); ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); #添加名字
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
##删除表时删除约束
##1、删除非空约束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL; ##2、删除默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT; ##3、删除主键 ALTER TABLE stuinfo DROP PRIMARY KEY;
##4、删除唯一键 ALTER TABLE stuinfo DROP INDEX seat;
##5、删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
##创建表时设置标识列 CREATE TABLE tab_identity( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) ); ##修改自增步长 SET auto_increment_increment = 3; ##特点： ##1、标识列不必须和主键搭配，但要求是一个key ##2、一个表中只能有一个标识列 ##3、标识列仅支持数值型（int float double） ##修改表时设置标识列 ALTER TABLE tab_indentity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT; ##修改表时删除标识列 ALTER TABLE tab_indentity MODIFY COLUMN id INT;
##savepoint保存点的使用 SET autocommit = 0; START TRANSACTION; DELETE FROM account WHERE id = 25; SAVEPOINT a; #设置保存点 DELETE FROM account WHERE id = 28; ROLLBACK TO a; #回滚到保存点
##创建视图 CREATE view 视图名 AS 查询语句; ##视图的好处 1、重用sql; 2、简化复杂的sql操作，不必知道它的查询细节; 3、保护数据，提高安全性
##视图的修改 CREATE OR REPLACE VIEW 视图名 AS 查询语句; 或 ALTER VIEW 视图名 AS 查询语句;
##删除视图 DROP VIEW 视图名，视图名，...; ##查看视图 DESC 视图名; 或 SHOW CREATE VIEW 视图名;
》》》案例：查询姓张的学生名和专业名 SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.majorid = m.id WHERE s.stuname LIKE '张%'; #创建视图 CREATE VIEW v1 AS SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.majorid = m.id; ##再次查询可直接使用视图 SELECT * FROM v1 WHERE stuname LIKE '张%';
#1、包含以下关键字的sql语句：分组函数、distinct、group by、having、union、union all #2、常量视图 CREATE OR REPLACE VIEW myv1 AS SELECT 'Tom' NAME; >>> UPDATE myv1 SET NAME = 'john'; [无法执行] #3、select中包含子查询 CREATE OR REPLACE VIEW myv1 AS SELECT(SELECT MAX(salary) FROM employees) 最高工资; >>> UPDATE myv1 SET 最高工资=10000; [无法执行] #4、join CREATE OR REPLACE VIEW myv1 AS SELECT last_name,department_name,FROM employees e JOIN departments d ON e.department_id = d.department_id; >>> UPDATE myv1 SET last_name = '张飞' WHERE last_name = '张无忌'; [可以执行] >>> INSERT INTO myv1 VALUES ('张三丰','dsdsadsadfa'); [无法执行] #5、from一个不能更新的视图 CREATE OR REPLACE VIEW myv1 AS SELECT * FROM myv3; >>> UPDATE myv1 SET 最高工资 = 10000 WHERE department_id = 50; [无法执行] #6、where子句的子查询引用了from子句中的表 CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email,salary FROM employees WHERE employee_id IN ( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL ); >>> UPDATE myv1 SET salary = 10000 WHERE last_name = '张无忌'; [无法执行]
#1、声明 DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值; #2、赋值 #方式一：通过SET或SELECT SET 局部变量名 = 值; SET 局部变量名:= 值; SELECT @局部变量名:= 值; #方式二：通过SELECT INTO SELECT 字段 INTO 局部变量名 FROM 表;
#3、使用 SELECT 局部变量名;
>>>局部变量作用域： 仅仅在定义它的begin end中有效 应用在begin end中有效
对比用户变量和局部变量
作用域
定义和使用的位置
语法
用户变量
当前会话
会话中的任何地方
必须加@符号，不用限定类型
局部变量
begin end中
只能在begin end中，且为第一句话
一般不用加@符号，需要限定类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
》》》案例：声明两个变量并赋初始值，求和并打印 #用户变量 SET @m=1; SET @n=2; SET @sum=@m+@n; SELECT @sum; #局部变量 DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 2; DECLARE SUM INT; SET SUM = m+n; SELECT SUM; [报错，只能在 begin end 中声明使用]
##创建语法 CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体（一组合法的SQL语句） END; #注意： 1、参数列表包含三部分 参数模式 参数名 参数类型 》》》举例： IN stuname VARCHAR(20) #参数模式： IN:该参数可以作为输入，也就是该参数需要调用方传入值 OUT:该参数可以作为输出，也就是该参数可以作为返回值 INOUT:该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值 2、如果存储过程体仅仅只有一句话， BEGIN END 可以省略 存储过程体中的每条SQL语句的结尾要求必须加分号 存储过程的结尾可以使用 DELIMITER 重新设置 》》》语法; DELIMITER 结束标记; 例如： DELIMITER $ ##调用语法 CALL 存储过程名(实参列表);
##空参列表 》》》案例：插入到admin表中五条记录 (需要在dos窗口) DELIMITER $ CREATE PROCEDURE myp1 () BEGIN INSERT INTO admin(username,password) VALUES('Tom','0000'),('john','1111'),('lily','2222'),('york','3333'),('rock','4444'); END $ #调用 CALL myp1()$ ##创建带IN模式参数的存储过程 》》》案例1：创建存储过程实现 根据女神名，查询对应的男神信息 CREATE PROCEDURE myp2 (IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END $ #调用 CALL myp2('唐艺昕')$ 》》》案例2：创建存储过程实现 用户是否登录成功 CREATE PROCEDURE myp3 (IN userame VARCHAR(20),IN PASSWORD VARCHAR(20)) BEGIN DECLARE result VARCHAR(20) DEFAULT ''; #声明并初始化 SELECT COUNT(*) INTO result #赋值 FROM admin WHERE admin.username = username AND admin.password = PASSWORD; SELECT result; #使用 END $ #调用 CALL myp3('张飞','8888')$ ##创建带out模式的存储过程 》》》案例1：根据女神名，返回对应的男神名 CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyName INTO boyName FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END $ #调用 CALL myp5('小昭',@bName)$ SELECT @bName$ 》》》案例2：根据女神名，返回对应的男神名和男神魅力值 CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT) BEGIN SELECT bo.boyName,bo.userCP INTO boyName, userCP FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END $ #调用 CALL myp6('小昭',@bName,@userCP)$ ##创建带inout模式参数的存储过程 》》》案例1：传入A和B两个值，最终A和B都翻倍返回 CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT) BEGIN SET a=a*2; SET b=b*2; END $ #调用(不能直接调用，需要先创建两个用户变量) SET @m=10$ SET @n=20$ CALL myp8(@m,@n)$ SELECT @m,@n$
1 2 3 4 5
##存储过程的删除 DROP PROCEDURE 存储过程名; ##查看存储过程的信息 SHOW CREATE PROCEDURE 存储过程名;
##创建语法 CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型; BEGIN 函数体 END #注意： 1、参数列表包含两部分： 参数名 参数类型 2、函数体： 肯定会有return语句，如果没有会报错 如果return语句没有放在函数体的最后也不报错，但不建议 3、函数体中仅有一句话，则可以省略 BEGIN END 4、使用 delimiter 语句作为设置结束标记 ##调用 SELECT 函数名(参数列表) ##无参有返回 》》》案例1：返回公司的员工个数 CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0; #定义变量 SELECT COUNT(*) INTO c #赋值 FROM employees; RETURN c; END $ #查看 SELECT myf1()$ ##有参有返回 》》》案例1：根据员工名。返回他的工资 CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE BEGIN SET @sal=0; #定义用户变量 SELECT salary INTO @sal #赋值 FROM employees WHERE last_name = empName; RETURN @sal; END $ #查看 SELECT myf2('lily') $
》》》案例2：根据部门名，返回该部门的平均工资 CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE sal DOUBLE; SELECT AVG(salary) INTO sal FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = deptName; RETURN sal; END $ #查看 SELECT myf3('IT')$ ##查看函数 SHOW CREATE FUNCTION myf3; ##删除函数 DROP FUNCTION myf3;
##case结构 情况1：类似于Java中的switch语句，一般用于实现等值判断 情况2：类似于Java中的多重if语句，一般用于实现区间判断 #语法： 情况1： case 变量|表达式|字段 when 要判断的值 then 返回的值1或语句1; when 要判断的值 then 返回的值2或语句2; ... else 要返回的值n或语句n; end case; 情况2： case when 要判断的条件1 then 返回的值1或语句1; when 要判断的条件2 then 返回的值2或语句2; ... else 要返回的值n或语句; end case;
#特点： 1、可以作为表达式，嵌套在其他语句中使用，可以放在任何地方， BEGIN END 中或 BEGIN END 外面 可以作为独立的语句去使用，只能放在 BEGIN END 中 2、如果 when 中的值满足或条件成立，则执行对应的 then 后面的语句，并且结束 case 如果都不满足，则执行 else 中的语句或值 3、 else 可以省略，如果 else 省略了，并且所有 when 条件下都不满足，则返回 NULL ##if结构 实现多重分支 #语法： if 条件1 then 语句1; elseif 条件2 then语句2； ... 【 else 语句n】 end if; #应用： 只能应用在 begin end 中
##分类 while、 loop、 repeat #循环控制 iterate类似于 continue,继续，结束本次循环，继续下一次 leave类似于 break,跳出，结束当前所在循环 ##while #语法： 【标签:】 while 循环条件 do 循环体; end while 【标签】; ##loop #语法 【标签:】 loop 循环体; end loop 【标签】; ##repeat #语法： 【标签:】 repeat 循环体; until 结束循环的条件 end repeat 【标签】;
十三、存储引擎
InnoDB
MyISAM
主外键
支持
不支持
事务
支持
不支持
行表锁
行锁，操作时只锁某一行，不对其他行有影响 适合高并发操作
表锁，即使操作一条记录也会锁住整个表 不适合高并发操作
缓存
不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
只缓存索引，不缓存真实数据
表空间
大
小
关注点
事务
性能
默认安装
是
是
十四、索引
1 2 3 4 5 6 7 8 9 10
##性能下降SQL慢，执行时间长，等待时间长
#原因： 1、查询语句写的烂 2、索引失效 【索引分为单值索引和复合索引】 CREATE INDEX idx_user_name on user(name) #索引名一般为 idx_表名_需要建索引的字段名 CREATE INDEX idx_user_nameEmail on user(name,email) 3、关联查询太多 join (设计缺陷或不得已的需求) 4、服务器调优及各个参数设置(缓冲、线程数等)
什么是索引
1 2 3 4 5 6 7 8 9 10
##索引是什么 索引是一种排好序的快速查找数据结构 【索引会影响到 where 后面的筛选条件和 order by 后面的排序】 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据； 这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是#索引 索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上