创建数据库
# 创建数据库并设置字符集与校队规则
mysql> create database if not exists svp
-> default character set utf8
-> default collate utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.13 sec)
创建表格
CREATE TABLE 表名称(属性1 数据类型 约束,属性2 数据类型 约束,…,INDEX 索引名(列名))ENGINE=INNODB DEFAULT CHARSET=utf8;
mysql> use svp;
Database changed
mysql> create table svp_1 (
-> id int(10) primary key,
-> name varchar(20) not null
-> )
-> engine=innodb default charset=utf8;
Query OK, 0 rows affected, 2 warnings (0.35 sec)
创建索引语句INDEX
CREATE INDEX 索引名 ON 表名(列名);
同时创建多个索引
CREATE INDEX 索引名 ON 表名(列名1，列名2，.....);
删除索引
DROP INDEX 索引名 ON 表名;
ALTER 修改结构
ALTER TABLE 表名 ADD 列名 数据类型; #新增列
ALTER TABLE 表名 MODIFY 列名 新数据类型; #修改属性数据类型
ALTER TABLE 表名 DROP 列名; #删除指定列
ALTER TABLE 表名 CHANGE 原属性名 新属性名 数据类型; #修改列名
DROP 删除
DROP DATABASE 数据库名; #删除数据库
DROP TABLE 表名; #删除表
INSERT 插入
INSERT INTO 表名(属性1,属性2,......) VALUES(value1,value2,......); #插入单行数据
INSERT INTO 表名(属性1,属性2,......) VALUES(value11,value12,......),(value21,value22,......),....; #插入多行数据
UPDATE 更新
UPDATE 表名 SET 列=VALUE WHERE 条件; #value为更新的值
DELETE 删除
用于删除表格中的行
DELETE FROM 表名 WHERE 条件;
SELECT 查询
SELECT 属性1，属性2,... FROM 表1,表2,...; #标准语法
SELECT * FROM 表名; #查询所有列
SELECT 属性1，属性2,... FROM 表名; #查询指定列
运算符+ - * /带入查询
SELECT 属性1+number，属性2,... FROM 表名;
空值 空值是指不可用，不知道，不适用的值 空值不等于零或空格 包含空值的算术运算结果为空
as 使用列的别名（中文别名使用双引号，英文别名可以不加）
SELECT 属性 AS "别名" FROM 表名; #查询到的结果中属性名显示为别名
distinct）重记录 去重
SELECT DISTINCT 列名 FROM 表名; #查询列名中所有不重复的值
where字句，限定查询
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1=20; #查询属性1等于20所有的数据
注意：在Where子句中字符串和日期要用单引号扩起来
对字符串不区分大小写，可以使用BINARY关键字设定字符串区分大小写
日期值是格式敏感的
在Where子句后面不能使用列的别名限定查询
where子句中比较运算符 =、 >、 >=、 < 、<=、 <>/ !=
SELECT 属性1，属性2,… FROM 表名 WHERE 属性1=、 >、 >=、 < 、<=、 <>/ !=20;
特殊比较运算符
between .. and .. 在两值之间 (包含两值)
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 BETWEEN 2000.00 AND 3000.00;
in(list) 匹配列出的值
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 IN(值1,值2);
like 匹配一个字符模式 查询条件可包含文字字符或数字 注意两个符号：% 、_ %可表示零或多个任意字符 _可表示一个字符
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 like '_a%'; #匹配第二个字符为a的所有values
is null 是空值
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 IS NULL; #查找属性1中值为空值的所有数据
逻辑运算符 and or not
and 或者&& 如果组合的条件都为真则返回真值（and前后的条件都必须同时满足）
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1=值1 AND 属性2=值2;
or 或者 || 如果组合的条件之一是真值,返回真值(满足or前后条件之一即可)
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1=值1 OR 属性2=值2;
not 或者！ 如果条件为假则返回真值(当不为xx的条件时返回结果)
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 NOT IN(值1,值2,....); #查询属性1不为值1,值2,....所有的值
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 NOT LIKE '%A%'; #查询所有属性1中不含字符串A的值
SELECT 属性1，属性2,... FROM 表名 WHERE 属性1 IS NOT NULL; #查询所有属性1不为空的值
order by子句
在select语句中最后的位置
asc：升序
desc：降序
语法：
ORDER BY 属性名 ASC/DESC
。。。
SELECT 属性1，属性2,... FROM 表名 ORDER BY 属性1 ASC;
SELECT 属性1，属性2,... FROM 表名 WHERE 属性2=值1 ORDER BY 属性1 ASC;
字符函数
UPPER('x') 将x中的字符转换为大写
LOWER('x') 将x中的字符转换为小写
SELECT 属性1,UPPER("属性2") FROM 表名; #将属性2中所有的字符转化为大写
LENGTH('x') 返回x的字符数
SELECT 属性1,LENGTH("属性2") FROM 表名; #返回属性2中所有值的字符数
IFNULL(X,VALUE) 如果x为空，返回value(value为自定义的值)，否则返回x
SELECT IFNULL(属性1,值1),属性2 FROM 表名; #如何属性1中的值存在空值(null)，则该值为值1
REPLACE(X,char1,char2) 将x中的char1替换为char2
SELECT 属性1,REPLACE(属性2,str1,str2) FROM 表名; #将属性2中的字符str1全部替换为str2 替换的值大小写必须与原表一致
SUBSTRING(X,START,[LENGTH]) 从x的start位置开始返回长度为length的子字符串
LPAD(X,LENGTH,CHAR) 从x的左边使用char将x补齐到长度为length
RPAD(X,LENGTH,CHAR) 从x的右边使用char将x补齐到长度为length
数字函数
FLOOR(X) 返回小于等于x的最大整数
CEILING(X) 返回大于等于x的最小整数
MOD(X,Y) 返回x除以y的余数
ROUND(X,Y) 返回对x精确到y位的结果
TRUNCATE(X,Y) 返回对x截取到y位的结果
注意：当y为正数时，截取到小数点后第y位
当y为负数时，截取到小数点前第y位
日期函数
CURDATE() 返回年月日
CURTIME() 返回几点几分
NOW() 返回当前具体年月日和几点钟
DATE_ADD(X,INTERVAL n f) 返回日期x加上间隔时间n的结果,f可以为 DAY/MONTH/YEAR
DATE_FORMAT(X,fmt) 返回日期x按指定格式fmt格式化后的结果
控制流函数
CASE
语法：case 属性 WHEN 值1 THEN 结果1 ......when 值n THEN 结果n END
SELECT 属性1,属性2 FROM 表名 CASE 属性1 WHEN 10 THEN "研发" WHEN 20 THEN "产品" END AS "部门名称";
注释：在返回的表格中增加一列，当属性1的值为10时，当前行的部门名称列下的值对应为"研发"，以此类推
IF
语法：if(条件，值1，值2) 如果条件成立，返回结果值1；否则，返回结果值2。（条件为真时返回值1，否则返回值2）
SELECT 属性1,IF(属性2 IS NULL,0,属性2) FROM 表名;
多表查询
语法：
SELECT 表1.列1,表2.列2 FROM 表1,表2 WHERE 连接条件（n张表联合查询至少需要n-1个连接条件）
#内连接-等值连接
表之间有相等值的列，主要用于有主外键关系的表
SELECT 表1.列1,表2.列2 FROM 表1,表2 WHERE 表1.列1 = 表2.列2; #列1与列2的值相同
#内连接-不等值连接
表之间没有相等的值
SELECT 表1.表1列名1,表1.表1列名2,表1.表1列名3,表2.表2列名1 FROM 表1,表2 WHERE 表1.表1列名3 BETWEEN 表2.losal AND 表2.`hisal`;
注释：当表1与表2不存在相同的列，但是表2中存在列的值包含了表1中某个列的值，此时可以使用不等值连接方式将表1与表2连接起来，使用比较运算符 BETWEEN .. AND .. 可以实现，具体情况具体分析，不是所有的表都能连接
#自连接 将一张表当成多张表使用
SELECT e.empno,e.ename AS "员工姓名",e.mgr,m.ename AS "上级领导姓名" FROM emp e,emp m WHERE e.mgr = m.empno;
注释：e表为员工表,m表为上级领导表
#外连接-左外连接 左边表显示出全部数据，右边表显示出匹配数据
语法：
SELECT 表1.列1,表2.列2 FROM 表1 LEFT JOIN 表2 ON 表1.列=表2.列2;
#右外连接-右边表显示出全部数据，左边表显示出匹配数据
语法：
SELECT 表1.列1,表2.列2 FROM 表1 RIGHT JOIN 表2 ON 表1.列1=表2.列2;
注意区分左表和右表
左边表为from后面的表，右边表为join后面的表;表的连接条件用on
组函数
COUNT() 返回指定列中非NULL值的个数
SUM() 返回指定列的所有值之和
AVG(COLUMN) 返回指定列的平均值
MAX(COLUMN) 返回指定列最大值
MIN(COLUMN) 返回指定列最小值
group by 子句
注意：1、如果在select后面有组函数，又有普通列，需要使用group by子句，并且所有的普通列必须都出现在group by子句中
2、group by子句中的普通列可以不在select后面
3、通过一定的规则将一个数据集划分为若干个小区域，然后对每个小区域进行数据处理
SELECT 属性1,AVG(属性2) AS "平均值" FROM 表名 GROUP BY 属性1;
注释：将属性1分配为若干个区域，求每个区域的平均值
having 子句
having子句用于限定分组查询结果
注意：不能使用where子句限定分组结果，where子句中不能使用组函数
特点：如果出现 HAVING 子句，一定会有 GROUP BY 子句
有 GROUP BY 子句，不一定会有 HAVING 子句
SELECT 属性1,AVG(属性2) AS "平均值" FROM 表名 GROUP BY 属性1 HAVING AVG(属性2)<值1;
注释：将属性1分配为若干个区域，求每个区域的平均值,同时只显示平均值小于值1的数据
子查询
子查询使用规则
子查询要用括号括起来
将子查询放在比较运算符的右边
子查询中不要加ORDER BY子句
对单行子查询使用单行运算符
对多行子查询使用多行运算符
演化规则如下：
SELECT 属性1 FROM 表名 WHERE 属性2 = "值1";
SELECT 属性1,属性2,属性3,属性4 FROM 表名 WHERE 属性1 = "值2";
SELECT 属性1,属性2,属性3,属性4 FROM 表名 WHERE 属性1 = (SELECT 属性1 FROM 表名 WHERE 属性2 = "值1");
LIMIT 分页查询
起始行号,返回行数
语法：sql语句 LIMIT 起始行(第一行的行号为0),返回行数量
sql语句结构顺序
SELECT 表1.列1,表2.列2,.... 查询指定列
FROM 表1,表2,... 指定从哪些表查询
WHERE 条件 限定查询结果
GROUP BY 列1,列2,... 分组查询
HAVING 条件 限定分组查询结果
ORDER BY 列1 ASC/DESC,列2,... 查询结果排序
LIMIT 起始行号,返回行数 返回限定行数的结果
存储过程
语法：
DELIMITER//
CREATE PROCEDURE 过程名(模式 形参1 数据类型,……) #过程名(模式 形参1 数据类型,……)为自定义内容
BEGIN
程序体; #程序体; 存储过程所做的事
END;//
DELIMITER;
注意：
(1)这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默
认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储
过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
(2)存储过程根据需要可能会有输入、输出、输入输出参数，多个参数用","分割开。
(3)过程体的开始与结束使用BEGIN与END进行标识。
1)形参模式：
in：该参数用于接收外部的值传入存储过程内部 in可以省略不写
out：该参数用于将存储过程内部处理结果传出存储过程
数据输出： SELECT 输出的对象;
调用存储过程： CALL 过程名(实参1,实参2,......)
补充知识
主键与外键
主键 primary key
能唯一的标识表中的每一行，这样的一列或多列称为表的主键，一个表只能有一个primary key约束，而且primary key约束的列不能接受空值，主键自带索引，并且一个表只能有一个主键约束。数据库主键，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。主键主要是用于其他表的外键关联，以及本记录的修改与删除
主键的作用：
用作标识列，区分其他字段
强制表的实体完整性
用于其他表的外键关联
用于记录的修改与删除
加速查询
创建主键约束
建表时创建主键
create table 表名(列名 数据类型 primary key,列名 数据类型 约束)
建表后添加主键
create table 表名(列名 数据类型);
alter table 表名 add primary key(列名);
建表后新加字段并设为主键：
create table 表名(列名 数据类型);
alter tabler 表名 add 列名 数据类型 primary key;
删除主键约束：
alter table 表名 drop primary key;
联合主键：用两个或两个以上的字段（两个列下面同一行组成的字段唯一）组成的主键，用这个主键包含的字段作为主键约束，这个组合在数据表中是唯一，且加了主键索引。
创建表时创建联合主键：
create table 表名(列名1 数据类型,列名2 数据类型,列名 数据类型,primary key(列名1,列名2));
已有的表添加联合主键：
create table Person(id int,province varchar(8),city varchar(8),country varchar(8));
alter table 表名 add 约束 联合主键名 primary key(列名,列名);
删除联合主键的主键约束，由于主键约束在表单中的唯一性，我们可以直接删除：
alter table 表名 drop primary key;
外键
为了满足关系型数据库的第三范式，降低数据冗余，在表与表之间需要满足响应的关系，可以为表和表之间设置外键。用于两个表的数据的数据连接，一个表可以有多个外键，MySQL中的innodb存储引擎支持外键。外键对应的是参照完整性，一个表的外键可以是空值，若不为空值，则每一个外键的值必须等于另一个表中的主键的某个值。定义一个外键后，不允许删除另一个表中具有关联关系的行；外键的主要作用是保持数据的一致性、完整性。一个主键表某一字段的和跟他有关联的外键表的某一字段有直接的关联。
对于有外键关联的两个表来说：
主表（父表）：主键所在的表
从表（字表）：外键所在的表
mysql设置外键约束字段选取：
父表必须是已经存在或者正在创建的表
父表主键不能包含空值，但外键可以有
外键列的数目必须和父表的主键中列的数目相同
从表外键列和主表主键列对应的列的数据类型相同
创建表时设置外键约束：
create table 子表名(列名1 数据类型,列名2 数据类型,foreign key(列名1) references 父表名(列名1));
在已有的表中设置外键约束：
alter table 子表名add foreign key(列名1) references 父表名(列名1);
注意：子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误。
删除外键约束：
有外键约束的列必须先删除外键，然后才能删除该列：
alter table 子表名 drop foreign key 外键名称;
外键的名字，我们可以自己设，也可以系统生成
查看表中的外键：
show create table 表名
索引
确认数据库的索引
explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了：
explain 完整的数据库语句
EXPLAIN列的解释：
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
extra列返回的描述的意义
Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
system 表只有一行：system表。这是const连接类型的特殊情况
const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
什么是索引
上班了，待补充
目录
第一章 create语句 1
1.1 创建database #数据库 1
1.2 创建表格table 1
第二章 插入数据（insert） 3
2.1 insert：插入数据 3
2.1.1 一次插入单行数据 4
2.1.2 一次性插入多行数据 4
2.2 update：更新数据 4
2.2.1 更新整张表指定列的数据 4
2.2.2更新指定行指定列的数据 4
2.2.3 delete：删除数据 4
2.3 删除指定行的数据 4
2.3.1 删除整张表的数据 4
第三章 select查询语句 4
3.1 select基本查询用法 4
3.1.1 查询所有列 5
3.1.2 查询指定列 5
3.1.3 算术运算（对数值型和日期型数据可用算数运算创建表达式）运算符+ - * / 5
3.1.4 空值 5
3.1.5 使用列的别名（中文别名使用双引号，英文别名可以不加） 5
3.1.6 重记录 （去重 distinct） 6
3.2 限定和排序 where子句 6
3.2.1 where子句 6
3.3 where子句中比较运算符 7
3.3.1 普通比较运算符 =、 >、 >=、 < 、<=、 <>/ != 7
3.3.2 特殊比较运算符 7
3.3.3 between … and … 在两值之间 (包含两值) 7
3.3.4 in(list) 匹配列出的值 7
3.3.5 like 匹配一个字符模式 7
3.3.6 is null 是空值 8
3.4 逻辑运算符 8
3.4.1 and 或者&& 如果组合的条件都为真则返回真值（and前后的条件都必须同时满足） 8
3.4.2 or 或者 || 如果组合的条件之一是真值,返回真值(满足or前后条件之一即可) 8
3.4.3 not 或者！ 如果条件为假则返回真值(当不为xx的条件时返回结果) 9
3.5 order by 子句 9
1)使用ORDER by 子句将记录排序 9
3.6 单行函数 11
3.6.1 字符函数 11
特别注意 替换的值大小写必须与原表一致 12
3.7 数字函数 14
1)floor(x) 返回小于等于x的最大整数 14
2)ceiling(x) 返回大于等于x的最小整数 14
3)mod(x,y) 返回x除以y的余数 14
4)round(x,y) 返回对x精确到y位的结果 14
5)truncate(x,y) 返回对x截取到y位的结果 14
3.8 日期函数 15
1）查询6月份入职的员工的姓名，工资，入职日期、职位、部门号 15
2）查询1981年下半年入职的员工的编号、姓名、职位、入职日期、部门号 16
3）查询在入职月倒数第三天入职的员工的姓名、职位、入职日期 16
3.9 控制流函数 16
3.9.1 case 16
3.9.2 if 16
第四章 多表查询 17
4.1 内连接 17
4.1.1等值连接 表之间有相等值的列，主要用于有主外键关系的表 17
4.1.2不等连接 表之间没有相等的值 17
4.1.3 自连接 将一张表当成多张表使用 18
4.2 外连接 18
1)左外连接 左边表显示出全部数据，右边表显示出匹配数据 18
2)右外连接 右边表显示出全部数据，左边表显示出匹配数据 18
1）查询20号部门的员工姓名、职位、工资、部门名称 18
2）查询奖金为空的员工的姓名、工资、奖金(显示为0)、工资等级、部门号、部门名称 18
3）查询员工姓名、部门号、部门名称，要求将没有员工的部门也显示出来 19
第五章 聚合函数（分组函数） 19
5.1组函数 19
- count(column) 19
2)sum(column) 返回指定列的所有值之和 19
3)avg(column) 返回指定列的平均值 19
4)max(column) 返回最大值 19
5.2 group by子句用于分组查询 19
2、group by子句中的普通列可以不在select后面 19
5.3多列分组 20
5.4 having子句 20
第六章 子查询 21
6.1 单行子查询 子查询返回一行记录 21
a)在子查询中使用分组函数 21
一、子查询 23
6.2多行子查询 子查询返回多行一列 23
1)in 等于列表中的任何值 24
2)any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true 24
3)all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true 24
6.3多列子查询 子查询返回多行多列 24
6.4分页查询 25
1、查询薪金(工资+奖金)比“ALLEN”多的所有员工 25
2、查询在部门销售部工作的员工姓名、工资、职位 25
3、使用子查询，找出哪个部门下没有员工 26
4、使用子查询，找出那些工资低于平均工资的员工姓名、工资、职位、部门号 26
5、使用子查询，找出那些工资低于其中任意一个部门的平均工资的员工 26
6、查询每个部门入职最晚的员工的部门编号、员工姓名及其工资的信息 26
7、查询每个部门的部门号、部门名称、部门人数(没有员工的部门人数显示为0) 26
8、查询高于自己部门平均工资的员工名字,部门号,工资 26
9、查询工资排名前三的员工信息 26
第七章 存储过程 26
7.1语法 26
1)形参模式： 26
2)调用存储过程： call 过程名(实参1,实参2,…) 27
7.2 用户定义变量 27
7.3 条件结构 29
7.3.1 if语句 29
7.4 case语句 31
7.5 循环结构 31
7.5.1 while循环 31
7.5.2 repeat 循环 32
7.6 循环控制语句 33
7.6.1 Leave 33
7.6.2 Iterate 33
7.6.3 Loop循环 条件表达式为真时，结束循环 34
第一章 create语句
1.1 创建database #数据库
语法：
create database 数据库名 character set ‘utf8’ collate ‘utf8_general_ci’;
[character set ‘utf8’ collate ‘utf8_general_ci’] #设置字符集及字符集校对规则
【例】创建database：exercise
create database exercise character set ‘utf8’ collate ‘utf8_general_ci’;
【练习】创建company数据库
create database company character set ‘utf8’ collate ‘utf8_general_ci’;
1.2 创建表格table
语法：
create table 表名(属性1 数据类型(长度) 约束,属性2 数据类型(长度) 约束,…,属性n 数据类型(长度) 约束) engine=innodb default charset=utf8;
engine=innodb default charset=utf8; #指定存储引擎innodb，默认字符集为utf8。
mysql中常见数据类型：数值型、日期型、字符型
数值型：整型、浮点型
类型 范围
tinyint 1字节,-128~127
smallint 2字节,-32 768~32 767
mediumint 3字节,-8 388 608~8 388 607
int 4字节,-2 147 483 648~2 147 483 647
bigint 8字节,-9 233 372 036 854 775 808~9 233 372 036 854 775 807
float(p,s) 4字节,p表示有效位数，s表示精度 600.011 float(6,3)
double(p,s) 8字节,p表示有效位数，s表示精度
decimal(p,s) 存储为字符串的浮点数
日期型：
类型 范围
date 3字节，日期，yyyy-mm-dd 2018-08-24
time 3字节，日期，hh-mm-ss 11:26:20
datetime 8字节，日期，yyyy-mm-dd hh-mm-ss 2018-08-06 11:26:20
字符型：
char(n) 固定长度字符串，最大为255个字符 效率更高
varchar(n) 可变长度字符串，最大为65535个字符 节约空间
sname char(10),sname的值为smith，此时在内存中同样占用10个字符的空间
sname varchar(10),sname的值为smith，此时在内存中占用5个字符的空间
约束：主键约束，外键约束，非空约束，唯一约束，检查约束，默认值
主键约束：primary key 表中的每一行都应该具有可以唯一标识自己的列而这个承担标识作用的列称为主键
外键约束：foreign key 外键用于关联两个表
注意：所有表的存储引擎必须为innodb类型
被引用表中，必须为被引用列创建索引
引用表中，必须为引用的列创建索引
非空约束：not null
唯一约束：unique
检查约束：check(条件) （mysql中不支持检查约束，可以使用，但是没有任何效果） check(sex in(‘男’,‘女’)) enum枚举
默认值：default
注意：同一列上有多个约束时，约束之间使用空格隔开
同一列上有默认值和其他约束时，默认值写在其他约束前面 default ‘男’ not null
【例】创建学院表collage
（学院编号xno 主键约束,
系名xname 非空约束，
学院地址loc）
#创建学院表collage
create table collage(
xno int(4) primary key, #主键约束
xname varchar(20) not null, #非空约束
loc varchar(20),
index collage_index(xname) #创建索引
)engine=innodb default charset=utf8;
创建学生表student
（学号sno 主键约束,
姓名sname 非空约束，
年龄age 非空约束，
性别gender 默认值“男”，检查约束（男，女）,
入学日期sdate，
系名xname 外键约束（引用学院表中的系名）,
年级grade 非空约束）
#创建学生表student
create table student(
sno int(5) primary key,
sname varchar(20) not null,
age int(2) not null check(age between 16 and 24),
gender char(2) default ‘男’ check(gender in(‘男’,‘女’)),
sdate date,
xname varchar(20),
index student_index(xname),
grade int(2) not null,
foreign key(xname) references collage(xname) on delete cascade on update cascade
#on delete cascade on update cascade 表示级联的删除和修改
)engine=innodb default charset=utf8;
- alter：用于在已有的表中添加、修改或删除列
1)添加：alter table 表名 add 列名 数据类型;
【例】在student表中增加tel列，数据类型为数值型，非空
alter table student add tel bigint(11) not null;
补充：增加索引语法
alter table 表名 add index 索引名(属性名);
2)修改属性数据类型：alter table 表名 modify 列名 新数据类型;
【例】将student表中的tel列，数据类型修改为varchar(11)
alter table student modify tel varchar(11);
3)删除：alter table 表名 drop 列名;
【例】将student表中的tel列删除
alter table student drop tel;
补充：
修改属性名
语法：alter table 表名 change 原属性名 新属性名 数据类型;
【例】将student表中的gender列修改为sex列
alter table student change gender sex char(2);
alter table student change sex gender char(2) default ‘男’;
3.drop：
删除database和table
删除库：drop database 数据库名;
【例】删除数据test22
drop database test22;
删除表：drop table 表名;
【例】删除test表
drop table test;
2).alter：用于在已有的表中添加、修改或删除列
a.添加：在表中新增列
alter table 表名 add 列名 数据类型;
【例】在student表中增加phone列，数据类型为bigint,非空约束
alter table student add phone bigint(11) not null;
【练习】在collage表中增加 teacher列，数据类型varchar(20)
alter table collage add teacher varchar(20);
b.修改：修改列的数据类型
alter table 表名 modify 列名 新数据类型;
【例】修改student表中phone列的数据类型为varchar(20)
alter table student modify phone varchar(20);
【练习】修改student表中phone列的数据类型为bigint(11)
alter table student modify phone bigint(11);
c.删除：删除表中的列
alter table 表名 drop 列名;
【例】将student表中的phone列删除
alter table student drop phone;
【练习】删除collage表中的teacher列
alter table collage drop teacher;
3).drop：删除database和table
a.删除库：
语法 drop database 数据库名;
【练习】创建一个数据库test5,然后再删除数据库test5
create database test5 character set ‘utf8’ collate ‘utf8_general_ci’;
drop database test5;
b.删除表：
语法 drop table 表名;
【练习】在数据库test4中创建一张表成绩表grade(学生编号sno 主键约束,学生姓名sname 非空,成绩grade 非空)
create table grade(
sno int(4) primary key,
sname varchar(20) not null,
grade int(2) not null
) engine=innodb default charset=utf8;
【练习】在grade表中增加phone列,数据类型为字符型
alter table grade add phone varchar(11);
【练习】在grade表中修改phone列数据类型为数值型
alter table grade modify phone bigint(11);
【练习】在grade表中删除增加的phone列
alter table grade drop phone;
【练习】删除grade表
drop table grade;
补充：
修改表中属性名
语法： alter table 表名 change 原属性名 新属性名 数据类型(长度);
【练习】修改student表中的sname名称为newsname 数据类型varchar(20) 非空
alter table student change sname newsname varchar(20) not null;
alter table student change newsname sname varchar(20) not null;
修改表名
语法： alter table 原表名 rename 新表名;
【例】将student表名修改为newstudent
alter table student rename newstudent;
【练习】将newstudent表名修改为student
alter table newstudent rename student;
【练习】创建数据库test6,
在数据库test6中创建表学生表(学号,姓名,年龄,性别(gender),出生年月,学院编号,年级)
学生表：student
学号：sno int(4) 主键
姓名：sname varchar(20)非空
年龄：age int(2)
性别：gender char(2) 取值(M/F)默认值’M’
出生年月:birthday date
学院编号：xno int(4) 外键，引用院系表中的学院编号
年级：grade varchar(10) 非空
创建表：院系(学院编号,系名,地址)
院系：collage
学院编号：xno int(4)主键
系名：cname varchar(20)非空
地址：loc varchar(20)
1.在学生表中增加phone列
2.将院系中的的age列数据类型修改为varchar(4)
3.将学生表中的phone列删除
4.删除学生表和院系表
5.删除数据库test6
第二章 插入数据（insert）
2.1 insert：插入数据
语法：insert into 表名(属性1,属性2,…) values(value1,value2,…);
注意：如果是向表的所有属性插入值，表名后的属性可以不写
2.1.1 一次插入单行数据
【例】向COLLAGE表中插入数据1001，中文系，成都
insert into collage(xno,xname,loc) values(1001,‘中文系’,‘成都’);
insert into collage values(1002,‘英语系’,‘成都’);
2.1.2 一次性插入多行数据
语法：insert into 表名(属性1,属性2,…) values(value11,value12,…),(value21,value22,…),…;
【例】向COLLAGE表中插入数据1001，中文系，成都
insert into collage(xno,xname,loc) values(1003,‘社会学’,‘成都’),(1004,‘商品学’,‘成都’),(1005,‘法律学’,‘成都’);
【练习】向student表插入表格中的数据
insert into student(sno,sname,age,gender,xname,grade) values(95004,‘王小明’,19,‘女’,‘社会学’,95),
(95006,‘黄大鹏’,20,‘男’,‘商品学’,95),
(95008,‘张文斌’,18,‘女’,‘法律学’,95);
insert into student(sdate) values(‘2017-09-01’);
2.2 update：更新数据
语法：update 表名 set 列=value where 条件; #value为更新的值
注意：如果省略where子句表示更新整张表的数据
2.2.1 更新整张表指定列的数据
【例】更新学生表中入学日期为’2017-09-01’
update student set sdate=‘2017-09-01’;
2.2.2更新指定行指定列的数据
【例】更新王小明的入学日期为’2016-09-01’
update student set sdate=‘2016-09-01’ where sno=95004;
2.2.3 delete：删除数据
语法：delete from 表名 where 条件;
注意：如果省略where子句表示删除整张表的数据
2.3 删除指定行的数据
【例】删除学生表中sno为0的数据
delete from student where sno=0;
2.3.1 删除整张表的数据
【例】删除学生表的所有数据
delete from student;
第三章 select查询语句
3.1 select基本查询用法
基本语法：select 属性1，属性2,… from 表1,表2,…;
3.1.1 查询所有列
【例】查询dept表的部门号，部门名称，部门地址
select * from dept;
【练习】查询员工表中所有员工的信息
select * from emp;
3.1.2 查询指定列
【例】查询学生表中学号，姓名，年龄
select sno,sname,age from student;
【练习】查询员工表中员工的编号，姓名，MGR，入职日期
select empno,ename,mgr,hiredate from emp;
【练习】查询部门表中部门号，部门名称
select deptno,dname from dept;
3.1.3 算术运算（对数值型和日期型数据可用算数运算创建表达式）运算符+ - * /
【例】查询员工表中员工编号，姓名，工资，以及工资+200
select empno,ename,sal,sal+200 from emp;
【练习】查询员工表中员工编号，姓名，工资，年薪（12个月工资）
select empno,ename,sal,sal*12 from emp;
【练习】查询员工表中员工的姓名，日薪（30天一个月）
select ename,sal/30 from emp;
3.1.4 空值
空值是指不可用，不知道，不适用的值
空值不等于零或空格
注：包含空值的算术运算结果为空
【例】查询员工表中员工姓名，工资，奖金，月收入（工资+资金）
select ename,sal,comm,sal+comm from emp;
3.1.5 使用列的别名（中文别名使用双引号，英文别名可以不加）
（1）使用 as （中文别名使用双引号，英文别名可以不加）
【例】查询员工表中员工姓名，工资，奖金，月收入（工资+资金）别名为月收入
select ename,sal,comm,sal+comm as “月收入” from emp;
【练习】查询员工表中员工编号，姓名，工资，年薪（12个月工资）别名为年薪
select empno,ename,sal,sal*12 as “年薪” from emp;
（2）直接在属性后面空格接别名
select empno,ename,sal,sal*12 “年薪” from emp;
3.1.6 重记录 （去重 distinct）
【例】查询员工表中有哪些部门号
select deptno from emp;
select distinct deptno from emp;
【练习】查询员工表中的MGR有哪些
Select distinct mgr from emp;
【练习】查询员工表中有哪些职位
Select distinct job from emp;
【练习题】
1、从dept表中查询所有数据
Select * from dept;
2、从emp表中查询员工编号，姓名，职位，部门号
Select empno,ename,job,deptno from emp;
3、从emp表中查询员工姓名，工资，奖金，以及年收入（工资+奖金的总和*12），给年收入列取别名”年收入”
Select ename,sal,ifnull(comm,0.00),(sal+ifnull(comm,0.00))*12 ‘年收入’ from emp;
3.2 限定和排序 where子句
3.2.1 where子句
限定查询返回的记录
注意：在Where子句中字符串和日期要用单引号扩起来
对字符串不区分大小写，可以使用BINARY关键字设定字符串区分大小写
日期值是格式敏感的
在Where子句后面不能使用列的别名限定查询
WHERE子句在 from 子句后
【例】查询20号部门员工的编号，姓名，工资，部门号
select empno,ename,sal,deptno from emp
where deptno = 20;
【练习】查询MGR为7698的员工的编号，姓名，MGR，工资
select empno,ename,mgr,sal from emp
where mgr = 7698;
【练习】查询职位为salesman的员工的姓名，职位，工资
select ename,job,sal from emp
where job = ‘SALESMAN’;
select ename,job,sal from emp
where job = ‘salesman’;
select ename,job,sal from emp
where binary job = ‘salesman’;
【例】查询员工表中入职日期为1981年2月22 号的员工的姓名，工资，入职日期
select ename,sal,hiredate from emp
where hiredate = ‘1981-02-22’;
3.3 where子句中比较运算符
3.3.1 普通比较运算符 =、 >、 >=、 < 、<=、 <>/ !=
【例】查询员工表中工资大于2500的员工的姓名，工资，部门号
select ename,sal,deptno from emp
where sal > 2500.00;
【练习】查询员工表中工资小于3000的员工的姓名，工资，部门号
select ename,sal,deptno from emp
where sal < 3000.00;
【练习】查询员工表中职位不为’SALESMAN’的员工的编号，姓名，职位，工资
select empno,ename,job,sal from emp
where job <> ‘SALESMAN’;
select empno,ename,job,sal from emp
where job != ‘SALESMAN’;
【练习】查询员工表中1981年5月1号后入职的员工的编号，姓名，职位，入职日期
select empno,ename,job,hiredate from emp
where hiredate > ‘1981-05-01’;
【练习】查询工资小于等于奖金的员工的姓名，工资，奖金
select ename,sal,comm from emp
where sal <= comm;
3.3.2 特殊比较运算符
3.3.3 between … and … 在两值之间 (包含两值)
【例】查询员工表中工资在[2000.00,3000.00]的员工的姓名，工资，部门号
select ename,sal,deptno from emp
where sal between 2000.00 and 3000.00;
3.3.4 in(list) 匹配列出的值
【例】查询员工表中10号和20号部门的员工的编号，姓名，部门号
select empno,ename,deptno from emp
where deptno in(10,20);
【练习】查询员工表中职位为’SALESMAN’和’CLERK’的员工的姓名，职位，部门号
select ename,job,deptno from emp
where job in(‘SALESMAN’,‘CLERK’);
3.3.5 like 匹配一个字符模式
使用LIKE运算符执行通配查询
查询条件可包含文字字符或数字
注意两个符号：% 、_
% 可表示零或多个任意字符
_ 可表示一个字符
【例】查询员工表中姓名中的第二个字符为’A’的员工的编号，姓名，工资
select empno,ename,sal from emp
where ename like ‘_A%’;
【练习】查询员工表中姓名中第三个字符为’A’的员工的编号，姓名，部门号
select empno,ename,sal from emp
where ename like ‘__A%’;
【练习】查询员工表中职位中包含字符’A’的员工的姓名，职位，工资，部门号
select ename,job,sal,deptno from emp
where job like ‘%A%’;
3.3.6 is null 是空值
【例】查询员工表中奖金为空的员工的姓名，工资，奖金
select ename,sal,comm from emp
where comm is null; 当奖金不为空时 where is not null
【练习】查询员工表中MGR为空的员工的姓名，工资，部门号
select ename,sal,deptno from emp
where mgr is null;
3.4 逻辑运算符
3.4.1 and 或者&& 如果组合的条件都为真则返回真值（and前后的条件都必须同时满足）
【例】查询员工表中20号部门职位为’SALESMAN’的员工的编号，姓名，职位，部门号
select empno,ename,job,deptno from emp
where deptno = 20
and job = ‘SALESMAN’;
【练习】查询员工表中30号部门工资大于等于1500的员工的编号，姓名，工资，部门号
select empno,ename,sal,deptno from emp
where deptno = 30
and sal >=1500.00;
select empno,ename,sal,deptno from emp
where deptno = 30
&& sal >=1500.00;
3.4.2 or 或者 || 如果组合的条件之一是真值,返回真值(满足or前后条件之一即可)
【例】查询员工表中职位为’SALESMAN’或者’CLERK’的员工的姓名，工资，职位
select ename,sal,job from emp
where job = ‘SALESMAN’
or job = ‘CLERK’;
select ename,sal,job from emp
where job = ‘SALESMAN’
|| job = ‘CLERK’;
【练习】查询员工表中MGR为7698或者7839或者7566的员工的姓名，工资，MGR
select ename,sal,mgr from emp
where mgr = 7698
or mgr = 7839
or mgr = 7566;
3.4.3 not 或者！ 如果条件为假则返回真值(当不为xx的条件时返回结果)
【例】查询员工表中职位不为’SALESMAN’,‘CLERK’,'MANAGER’的员工的姓名，职位
select ename,job from emp
where job not in(‘SALESMAN’,‘CLERK’,‘MANAGER’);
【例】查询员工表中姓名中不含字符’A’的员工的姓名，职位
select ename,job from emp
where ename not like ‘%A%’;
【例】查询员工表中奖金不为空的员工 的姓名，工资，奖金
select ename,sal,comm from emp
where comm is not null;
select ename,sal,comm from emp
where !(comm is null);
【例】查询员工表工资大于1500且职位’PRESIDENT’，或者职位为’SALESMAN’的员工的姓名，职位，工资
select ename,job,sal from emp
where job=‘SALESMAN’
or (job=‘PRESIDENT’
and sal>1500);
select ename,job,sal from emp
where sal>1500
and job =‘PRESIDENT’
or job = ‘SALESMAN’;
【例】查询员工表中年薪大于13000的员工的姓名，工资，年薪（12个月工资），取别名年薪
select ename,sal,sal12 as “年薪” from emp
where sal12 >13000;
3.5 order by 子句
order by 子句在SELECT语句的最后位置
1)使用ORDER by 子句将记录排序
asc: 升序,缺省
desc: 降序
a)单列排序
语法： order by 属性名 asc/desc;
【例】查询员工表中的员工编号，姓名，工资，查询结果按工资升序排序
select empno,ename,sal from emp
order by sal asc;
select empno,ename,sal from emp
order by sal;
【例】查询20号部门员工的姓名，工资，部门号，查询结果按工资降序排序
select ename,sal,deptno from emp
where deptno = 20
order by sal desc;
b)使用列的别名排序
【例】查询员工表中的员工的编号，姓名，工资，年薪（12个月工资），年薪别名为"年薪"，查询结果按年薪的降序排序
select empno,ename,sal,sal12 as “年薪” from emp
order by sal12 desc;
【练习】查询员工表中的员工的编号，姓名，工资，日薪（30天一个月），查询结果按日薪别名升序排序
Select empno,ename,sal,sal/30 ‘日薪’ from emp order by ；
使用别名时 order by 后面的不能加引号
c)可以使用多个列排序
语法： order by 属性1 asc/desc,属性2 asc/desc,…
【例】查询员工表中的员工的姓名，工资，部门号，查询结果按部门号升序，工资降序排序
select ename,deptno,sal from emp
order by deptno asc,sal desc;
【练习】查询员工表中部门号为20或30的员工的姓名，部门号，工资，入职日期，查询结果按部门号降序，入职日期升序排序
select ename,deptno,sal,hiredate from emp
where deptno in(20,30)
order by deptno desc,hiredate;
select ename,deptno,sal,hiredate from emp
where deptno = 20
or deptno = 30
order by deptno desc,hiredate;
【练习题】
1.查询emp表，显示薪水大于2000，且工作类别是MANAGER的员工的姓名，职位，工资，部门号
Select ename,job,sal,deptno from emp where sal > 2000 and job = ‘manager’;
2.查询emp表，显示年薪大于30000，工作类别不是MANAGER的员工的姓名，职位，工资，部门号
Select ename,job,sal,deptno from emp where job != ‘manager’;
3.查询emp表， 显示薪水在1500到3000之间，且工作类别以“M”开头的员工的姓名，职位，工资，部门号
select ename,job,sal,deptno from emp where sal between 1500 and 3000 and job like ‘m%’;
4.查询emp表，显示奖金金为空并且部门号为20或30的员工的姓名，职位，工资，奖金，部门号
select ename,job,sal,ifnull(comm,0.00),deptno from emp where comm is null and deptno in(20,30);
5.查询emp表，显示奖金不为空或者部门号为20的员工的姓名，职位，工资，奖金，部门号，要求按照工资降序排列
select ename,job,sal,ifnull(comm,0.00),deptno from emp where comm is not null and deptno not in(10,40) order by ename;
6. 查询emp表，显示年薪大于30000工作类别不是MANAGER，且部门号不是10或40的员工的姓名，职位，工资，部门号，要求按照雇员姓名进行排列
select ename,job,sal,deptno from emp where sal > 3000 and job != ‘manager’ order by ename;
3.6 单行函数
3.6.1 字符函数
1)upper(‘x’) 将x中的字符转换为大写
lower(‘x’) 将x中的字符转换为小写
【例】返回’abcdefg’大写
select upper(‘abcdefg’) from dual;
select lower(‘ABCDEFG’) from dual;
【例】查询员工表中员工的编号，姓名，职位，姓名和职位返回结果为小写
select empno,lower(ename),lower(job) from emp;
2)length(‘x’) 返回x的字符数
【例】返回’abcdefghijklmn’的长度
select length(‘abcdefghijklmn’) from dual;
【例】查询员工表中员工编号，姓名，并返回姓名的长度
select empno,ename,length(ename) from emp;
【例】查询员工表中员工姓名为5个字符的员工编号，姓名
select empno,ename from emp
where length(ename) = 5;
【练习】查询员工表中员工的姓名，职位，并返回姓名和职位的长度
【练习】查询员工表中姓名为5个字符且职位为7个字符的员工的编号，姓名，职位，并返回职位的长度
- ifnull(x,value) 如果x为空，返回value(value为自定义的值)，否则返回x
【例】查询员工表中员工的编号，姓名，工资，奖金，奖金为空时返回为0
select empno,ename,sal,ifnull(comm,0.00) from emp;
【练习】查询员工表中的员工的编号，姓名，工资，奖金，月收入（奖金+工资），月收入取别名
select empno,ename,sal,comm,ifnull(sal+comm,sal) as “月收入” from emp;
select empno,ename,sal,comm,sal+ifnull(comm,0.00) as “月收入” from emp;
【练习】查询员工表员工的姓名，MGR，MGR为空时显示为员工自己的编号
select ename,empno,ifnull(mgr,empno) from emp;
4)replace(x,char1,char2) 将x中的char1替换为char2
特别注意 替换的值大小写必须与原表一致
【例】查询员工表中员工的编号，姓名，工资，将员工姓名中的’A’替换为’Z’
select empno,ename,replace(ename,‘A’,‘Z’),sal from emp;
【练习】查询部门表的部门号，部门名称，返回结果中将部门名称中的’A’替换为’K’
select deptno,dname,replace(dname,‘A’,‘K’) from dept;
5)substring(x,start,[length]) 从x的start位置开始返回长度为length的子字符串
【例】从’abcdefghijklmn’中返回字符串’ghi’
select substring(‘abcdefghijklmn’,7,3) from dual;
注意：当start为正数时，从左边开始数第start位置返回子字符串
当start为负数时，从右边开始数第start位置返回子字符串
select substring(‘abcdefghijklmn’,-8,3) from dual;
【练习】从’abcdefghijklmn’中返回字符串’jkl’
6)lpad(x,length,char) 从x的左边使用char将x补齐到长度为length
rpad(x,length,char) 从x的右边使用char将x补齐到长度为length
【例】用将字符串’abcde’从左边补齐长度为10的字符串
select lpad(‘abcde’,10,'‘) from dual;
【例】用将字符串’abcde’从右边补齐长度为10的字符串
select rpad(‘abcde’,10,'’) from dual;
补充：函数嵌套
一个函数使用另一个函数的结果
【例】用将字符串’abcde’从补齐长度为’abcde’字符串
select lpad(rpad(‘abcde’,10,'‘),15,’*') from dual;
【练习】查询员工表，使用员工姓名的第一个字符，将员工姓名从左边补齐到长度为10的字符串，返回结果包含员工姓名，工资
select ename,sal,lpad(ename,10,substring(ename,1,1)) from emp;
7)left(x,length) 从x的左边返回length个字符
right(x,length) 从x的右边返回length个字符
【例】查询员工表中员工的姓名，工资，返回员工姓名的第一字符
select ename,sal,left(ename,1) from emp;
select ename,sal,right(ename,1) from emp;
【练习】查询员工表，使用员工姓名的最后一个字符，将员工姓名从左边补齐到长度为10的字符串，返回结果包含员工姓名，工资
#员工姓名的最后一个字符
select ename,right(ename,1) from emp;
select ename,lpad(ename,10,right(ename,1)) from emp;
8)concat(str1,str2) 将str1和str2连接
【例】将字符’ABCDE’和’HIJKLMN’连接显示为’ABCDEHIJKLMN’
select concat(‘ABCDE’,‘HIJKLMN’) from dual;
【练习】查询员工表中员工的编号，姓名，工资，返回显示为如：‘7369编号的员工工资为800.00’
select empno,ename,sal,concat(concat(empno,‘编号的员工工资为’),sal) from emp;
select empno,ename,sal,concat(empno,‘编号的员工工资为’,sal) from emp;
- instr(str,char) 返回char在str中第一次出现的位置
【例】查询员工表中员工姓名中’A’第一次出现的位置
select ename,instr(ename,‘A’) from emp;
【练习】查询员工表中员工姓名不包含’A’的员工的编号，姓名，工资
like
select empno,ename,sal from emp
where ename not like ‘%A%’;
instr(str,char)
select empno,ename,sal from emp
where instr(ename,‘A’) = 0;
【练习题】
1、在’hello’的左右两边各添加5个’‘,返回’hello’（两种方法实现）
select lpad(rpad(‘hello’,10,'‘),15,’*‘) from dual;
select concat(’‘,‘hello’,’') from dual;
2、从字符串’abcdefghijklmn’中返回后三位字符，并转换为大写
#从字符串中返回后位字符
select right(‘abcdefghijklmn’,3) from dual;
#将返回的三位字符转换为大写
select upper(right(‘abcdefghijklmn’,3)) from dual;
3、查询名字为5个字符的员工的姓名，工资，奖金(奖金为空则显示为0)、职位、部门号
select ename,sal,ifnull(comm,0.00),job,deptno from emp
where length(ename) = 5;
4、查询姓名中不含’A’的员工的编号、姓名、职位、部门号
select empno,ename,job,deptno from emp
where instr(ename,‘A’) = 0;
select empno,ename,job,deptno from emp
where ename not like ‘%A%’;
5、显示将员工姓名的第一个字符去掉后的字符串，如’ALLEN’显示为’LLEN’
#查询姓名的长度
select length(ename) from emp;
select substring(ename,2,length(ename)-1) from emp;
select ename,substring(ename,2) from emp;
select ename,right(ename,length(ename)-1) from emp;
6、使用员工姓名的第一个字符，从左边将员工姓名补齐到长度为10
select ename,lpad(ename,10,left(ename,1)) from emp;
3.7 数字函数
1)floor(x) 返回小于等于x的最大整数
【例】返回小于等于5678.986的最大整数
select floor(5678.986) from dual;
【练习】查询员工表员工的日薪，返回小于等于日薪的最大整数
select sal/30,floor(sal/30) from emp;
2)ceiling(x) 返回大于等于x的最小整数
【例】返回大于等于5678.986的最小整数
select ceiling(5678.986) from dual;
3)mod(x,y) 返回x除以y的余数
【例】返回103除以5的余数
select mod(103,5) from dual;
【练习】返回1999除以6的余数
select mod(1999,6) from dual;
4)round(x,y) 返回对x精确到y位的结果
注意：当y为正数时，精确到小数点后第y位
当y为负数时，精确到小数点前第y位
【例】5678.986精确到小数点后2位
select round(5678.986,2) from dual;
select round(148.986,-2) from dual;
【练习】查询员工表中员工的姓名，工资，日薪，日薪保留两位小数
5)truncate(x,y) 返回对x截取到y位的结果
注意：当y为正数时，截取到小数点后第y位
当y为负数时，截取到小数点前第y位
【例】5678.986截取到小数点后2位
select truncate(5678.986,2) from dual;
select truncate(5678.986,-2) from dual;
3.8 日期函数
1)curdate() 返回年月日
curtime() 返回几点几分
now() 返回当前具体年月日和几点钟
select curdate();
select curtime();
select now();
2)last_day(x) 返回x所在月份的最后一天的日期
【例】查询员工表中员工的姓名，工资，入职日期，及员工入职月的最后一天的日期
select ename,sal,hiredate,last_day(hiredate) from emp;
【例】返回1968年2月12号所在月的最后一天
select last_day(‘1968-02-12’) from dual;
3)date_add(x,interval n f) 返回日期x加上间隔时间n的结果,f可以为 day/month/year
【例】查询员工表中员工的姓名，工资，入职日期，及入职日期3天后的日期
select ename,sal,hiredate,date_add(hiredate,interval 3 day) from emp;
注意：当n为正数时，日期+
当n为负数时，日期-
【例】查询员工表中员工的姓名，工资，入职日期，及入职日期前5个月的日期
select ename,sal,hiredate,date_add(hiredate,interval -5 month) from emp;
4)date_format(x,fmt) 返回日期x按指定格式fmt格式化后的结果
【例】查询员工表中员工的姓名，工资，入职日期，返回入职月份（2位数月份）
select ename,sal,hiredate,date_format(hiredate,‘%m’) from emp;
【练习】查询员工表中员工的姓名，工资，入职日期，返回入职年份（4位数）
select ename,sal,hiredate,date_format(hiredate,‘%Y’) from emp;
【日期函数练习题】
1）查询6月份入职的员工的姓名，工资，入职日期、职位、部门号
select ename,sal,hiredate,job,deptno from emp
where date_format(hiredate,‘%c’) = 6;
2）查询1981年下半年入职的员工的编号、姓名、职位、入职日期、部门号
select empno,ename,job,hiredate,deptno from emp
where date_format(hiredate,‘%Y’) = 1981
and date_format(hiredate,‘%c’) > 6;
3）查询在入职月倒数第三天入职的员工的姓名、职位、入职日期
select ename,job,hiredate from emp
where hiredate = last_day(hiredate)-2;
select ename,job,hiredate from emp
where hiredate = date_add(last_day(hiredate),interval -2 day);
3.9 控制流函数
3.9.1 case
语法：case 属性 when 值1 then 结果1 …when 值n then 结果n end
【例】查询员工表中员工的编号，姓名，职位，工资，部门号，10号部门时显示为’财务部’，
20号部门时显示为’研发部’，30号部门时显示为’销售部’，40号部门时显示为’运营部’
select empno,ename,job,sal,deptno,
case deptno when 10 then ‘财务部’ when 20 then ‘研发部’ when 30 then ‘销售部’ when 40 then ‘运营部’ end as “部门名称”
from emp
order by deptno;
【练习】查询员工表中员工的编号，姓名，职位，工资，部门号，当职位为’CLERK’时显示’员工’，为’MANAGER’时显示为’经理’，
为’PRESIDENT’时显示为’CEO’，为’SALESMAN’时显示为’销售人员’,为’ANALYST’时显示为’开发’
3.9.2 if
语法：if(条件，值1，值2) 如果条件成立，返回结果值1；否则，返回结果值2。（条件为真时返回值1，否则返回值2）
select ename,if(comm is null,0,comm) from emp;
【例】查询员工表中员工的姓名，工资，部门号，如果工资大于3500，显示为’H’，否则显示为’L’
select ename,sal,deptno,if(sal>3500,‘H’,‘L’) from emp;
【第三天练习题】
1、查询员工表中1981年上半年入职且部门号为30号的员工的编号，姓名，工资，部门号，入职日期
select empno,ename,sal,deptno,hiredate from emp where date_format(hiredate,‘%Y’) = 1981 and date_format(hiredate,‘%m’) < 7 and deptno = 30;
2、使用员工表中员工姓名的第三个字符将员工姓名补齐到长度为15个字符的字符串，如’SMITH’显示为’SMITHIIIIIIIIII’
select ename,rpad(ename,15,substring(ename,3,1)) from emp;
3、查询员工表中员工的编号，姓名，工资，奖金（奖金为空显示为0），年收入（12个月月收入，月收入为工资+奖金），年收入取别名
年收入，查询结果按年收入的降序排序
select empno,ename,sal,ifnull(comm,0.00),sal*12+ifnull(comm,0.00) “年收入” from emp order by 年收入 desc;
4、查询员工表中1981年下半年入职且工资大于2000的员工的编号，姓名，工资，部门号，入职日期，
select empno,ename,sal,deptno,hiredate from emp where date_format(hiredate,‘%Y’) = 1981 and date_format(hiredate,‘%m’) > 6 and sal > 2000;
5、查询员工表中姓名为6个字符的员工的姓名，工资，部门号，并用姓名中的第四个字符将姓名从左边补齐到长度为10的字符串
select ename,sal,deptno,lpad(ename,10,substring(ename,4,1)) from emp where length(ename) = 6;
6、查询员工表中员工的姓名，工资，部门号，10号部门时显示为’财务部’，20号部门时显示为’研发部’，
30号部门时显示为’销售部’，40号部门时显示为’运营部’，如果工资大于3000，显示为’H’，否则显示为’L’
select ename,if(sal>3000,‘H’,‘L’),
deptno,case deptno when 10 then ‘财务部’ when 20 then ‘研发部’ when 30 then ‘销售部’ when 40 then ‘运营部’ end ‘部门’ from emp;
第四章 多表查询
语法： select 表1.列1，表2.列2 from 表1，表2
where 连接条件（n张表联合查询至少需要n-1个连接条件）
4.1 内连接
4.1.1等值连接 表之间有相等值的列，主要用于有主外键关系的表
【例】查询员工的编号，部门号，以及部门地址
select emp.empno,emp.deptno,dept.loc from emp,dept
where emp.deptno = dept.deptno;
4.1.2不等连接 表之间没有相等的值
【例】查询员工的编号，姓名，工资以及工资等级
select emp.empno,emp.ename,emp.sal,salgrade.grade from emp,salgrade
where emp.sal between salgrade.losal and salgrade.hisal;
给表名取别名
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s
where e.sal between s.losal and s.hisal;
【练习】查询员工的编号，姓名，工资以及工资等级，部门号以及部门名称
select e.empno,e.ename,e.sal,s.grade,d.deptno,d.dname from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal;
【练习】查询20号部门员工的编号，姓名，工资以及工资等级，部门号以及部门名称
select e.empno,e.ename,e.sal,s.grade,d.deptno,d.dname from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e.deptno = 20;
4.1.3 自连接 将一张表当成多张表使用
【例】查询员工编号，姓名，MGR以及MGR 的姓名
select e.empno,e.ename as “员工姓名”,e.mgr,m.ename as “上级领导姓名” from emp e,emp m #e表为员工表,m表为上级领导表
where e.mgr = m.empno;
4.2 外连接
注意区分左表和右表
左边表为from后面的表，右边表为join后面的表;表的连接条件用on
1)左外连接 左边表显示出全部数据，右边表显示出匹配数据
语法： select 表1.列1,表2.列2 from 表1 left join 表2 on 表1.列=表2.列;
【例】查询员工编号，姓名，MGR以及MGR 的姓名，要求显出所有员工信息
select e.empno,e.ename as “员工姓名”,e.mgr,m.ename as “上级领导姓名” from emp e left join emp m on e.mgr=m.empno;
显示左边表全部数据：14行
显示右边表匹配数据：13行
最终结果：左外连接：14行
2)右外连接 右边表显示出全部数据，左边表显示出匹配数据
语法： select 表1.列1,表2.列2 from 表1 right join 表2 on 表1.列=表2.列;
select e.empno,e.ename as “员工姓名”,e.mgr,m.ename as “上级领导姓名” from emp m right join emp e on e.mgr=m.empno;
select e.empno,e.ename as “员工姓名”,e.mgr,m.ename as “上级领导姓名” from emp e right join emp m on e.mgr=m.empno
order by empno;
显示出右边全部数据：14行 其中6个匹配了左边表，还有8个未匹配
显示左边表匹配数据：13行 包含了6个MGR
最终显示数据，21行数据为：左边表匹配数据13行+右边表未匹配上数据8行
【多表查询练习题】
1）查询20号部门的员工姓名、职位、工资、部门名称
select e.ename,e.job,e.sal,d.dname from emp e,dept d
where e.deptno = d.deptno
and e.deptno = 20;
2）查询奖金为空的员工的姓名、工资、奖金(显示为0)、工资等级、部门号、部门名称
select e.ename,e.sal,ifnull(e.comm,0.00),s.grade,d.deptno,d.dname from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e.comm is null;
3）查询员工姓名、部门号、部门名称，要求将没有员工的部门也显示出来
select e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno = d.deptno;
第五章 聚合函数（分组函数）
5.1组函数
- count(column)
返回指定列中非NULL值的个数
【例】统计员工表中有几个员的有奖金
select count(comm) from emp;
【例】统计员工表中有几个部门
select count(distinct deptno) from emp;
【练习】统计员工表中有几个职位，有几个MGR
select count(distinct job),count(distinct mgr) from emp;
2)sum(column) 返回指定列的所有值之和
【例】统计员工表中所有员工的工资总和
select sum(sal) from emp;
【练习】统计员工表中所有员工的奖金总和
select sum(ifnull(comm,0.00)) from emp;
3)avg(column) 返回指定列的平均值
【例】查询员工表中所有员工的平均工资，要求保留两位小数
select round(avg(sal),2) from emp;
【练习】查询员工表中所有员工的平均月收入（工资+奖金），保留两位小数
select round(avg(sal+ifnull(comm,0.00)),2) from emp;
4)max(column) 返回最大值
min(column) 返回最小值
【例】查询员工表中最高工资，最低工资
select max(sal),min(sal) from emp;
【练习】统计30号部门员工人数
select count(empno) from emp where deptno = 30;
5.2 group by子句用于分组查询
注意：1、如果在select后面有组函数，又有普通列，需要使用group by子句，并且所有的普通列必须都出现在group by子句中
2、group by子句中的普通列可以不在select后面
1)单列分组
【例】查询每个部门的平均工资
select deptno,round(avg(sal),2) from emp
group by deptno;
【例】查询每个部门的平均工资，并显示部门名称
select e.deptno,round(avg(e.sal),2),d.dname from emp e,dept d
where e.deptno = d.deptno
group by e.deptno;
【练习】查询每个职位的平均工资
select avg(sal),job from emp group by job;
5.3多列分组
【例】统计每个部门每个职位的工资总和
select deptno,job,sum(sal) from emp
group by deptno,job;
【练习】统计20号部门每个职位的工资总和
select deptno,job,sum(sal) from emp
where deptno = 20
group by deptno,job;
5.4 having子句
having子句用于限定分组查询结果
注意：不能使用where子句限定分组结果，where子句中不能使用组函数
特点：如果出现 having 子句，一定会有 group by 子句，
有 group by 子句，不一定会有 having 子句
【例】查询最高工资大于2900的部门以及部门最高工资
select deptno,max(sal) from emp
group by deptno
having max(sal)>2900.00;
【练习】查询每个部门每个职位工资总和大于3000的部门号，职位，及工资总和
select deptno,job,sum(sal) from emp
group by deptno,job
having sum(sal) > 3000.00;
【练习题】
1）查询每个职位的平均工资(保留2位小数)，查询结果按平均工资降序排列
select job,round(avg(sal),2) as “avgsal” from emp
group by job
order by avgsal desc;
2）查询每个部门每个职位的平均工资(保留2位小数)，查询结果按部门号升序，平均工资降序排列
select deptno,job,round(avg(sal),2) as “avgsal” from emp
group by deptno,job
order by deptno,avgsal desc;
3）查询每个部门的人数，查询结果按以下格式显示
select count(if(deptno = 10,10,null)) as “10号部门人数”,count(if(deptno = 20,20,null)) as “20号部门人数”,
count(if(deptno = 30,30,null)) as “30号部门人数” from emp;
select sum(if(deptno = 10,1,0)) as “10号部门人数”,sum(if(deptno = 20,1,0)) as “20号部门人数”,
sum(if(deptno = 30,1,0)) as “30号部门人数” from emp;
第六章 子查询
子查询使用规则
子查询要用括号括起来
将子查询放在比较运算符的右边
子查询中不要加ORDER BY子句
对单行子查询使用单行运算符
对多行子查询使用多行运算符
6.1 单行子查询 子查询返回一行记录
使用单行比较运算符 = 、>、 <、 >=、 <=、 <>
【例】查询与’SMITH’同一个部门的员工的编号，姓名，工资，部门号
#查询SMITH的部门号
select deptno from emp where ename = ‘SMITH’;
select empno,ename,sal,deptno from emp
where deptno = 20;
select empno,ename,sal,deptno from emp
where deptno = (select deptno from emp where ename = ‘SMITH’);
【练习】查询与’BLAKE’职位相同的员工的编号，姓名，职位，工资，部门号，查询结果按工资降序排序
【练习】查询工资大于’SCOTT’的员工的编号，姓名，工资
a)在子查询中使用分组函数
【例】查询员工表工资最低的员工的姓名，工资
select ename,sal from emp
where sal =(select min(sal) from emp);
【练习】查询员工表中工资最高的员工的姓名，工资
【作业】
1.查询30号部门中名字由5个字符组成的员工的姓名，工资,并用姓名中的第三个字符将姓名从右边补齐到长度10
2.查询员工姓名，工资，工资等级，当工资等级为1时显示为’A’,当工资等级为2时显示为’B’，为3时显示为’C’,为4时显示为’D’，为5时显示为’E’
3.查询30号部门员工表中员工的编号，姓名，工资，部门名称，工资等级
4.查询奖金大于工资的员工的姓名，工资，奖金，工资等级，部门名称
5.查询员工表中员工姓名为5个字符的员工的姓名，工资，奖金，部门名称，工资等级，按部门名称升序排序
6.查询下半年入职的员工的姓名，工资，入职日期，部门名称，工资等级，按工资降序排序
7.查询上半年入职且姓名为5个字符且奖金为空的员工的姓名，工资，奖金，部门号，工资等级
8.查询入职日期早于直接上级的员工的编号、姓名、部门号、入职日期及直接上级姓名，直接上级入职日期
一、子查询
1、单行子查询 在子查询中使用HAVING子句
先执行子查询
然后返回结果到主查询的HAVING 子句
【例】查询部门最低工资大于20号部门最低工资的部门号及部门最低工资
select deptno,min(sal) from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);
6.2多行子查询 子查询返回多行一列
多行比较运算符
1)in 等于列表中的任何值
【例】查询与’SMITH’或者’WARD’职位相同的员工的编号，姓名，职位，部门号
#'SMITH’或者’WARD’职位
select job from emp where ename in(‘SMITH’,‘WARD’) #返回了两个结果clerk，salesman
select empno,ename,job,deptno from emp
where job in(select job from emp where ename in(‘SMITH’,‘WARD’));
【练习】查询与’ALLEN’或者’JONES’部门相同的员工的编号，姓名，工资，部门号
2)any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true
<any 指小于最大值
any 指大于最小值
【例】查询工资大于’WARD’或者’MARTIN’或者’MILLER’且职位不为’SALESMAN’的员工的姓名，工资，部门号
#查询’WARD’或者’MARTIN’或者’MILLER’的工资
select sal from emp where ename in(‘WARD’,‘MARTIN’,‘MILLER’)
select ename,sal,deptno,job from emp
where sal >any(select sal from emp where ename in(‘WARD’,‘MARTIN’,‘MILLER’))
and job <> ‘SALESMAN’;
【练习】查询入职日期早于’JANES’或者’KING’，且工资大于’ADAMS’的员工的编号，姓名，工资，入职日期，部门号
select empno,ename,sal,hiredate,deptno from emp
where hiredate < any(select hiredate from emp where ename in(‘JANES’,‘KING’))
and sal > (select sal from emp where ename = ‘ADAMS’);
3)all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true
all 指大于最大值
<all 指小于最小值
【例】查询工资高于’WARD’，‘MARTIN’，'MILLER’三个人的员工的姓名，工资
select ename,sal from emp
where sal >all(select sal from emp where ename in(‘WARD’,‘MARTIN’,‘MILLER’));
【例】查询入职日期早于’JANES’和’KING’，员工的编号，姓名，工资，入职日期，部门号
select empno,ename,sal,hiredate,deptno from emp
where hiredate <all(select hiredate from emp where ename in(‘JANES’,‘KING’));
6.3多列子查询 子查询返回多行多列
多列子查询使用in运算符
【例】查询与’MARTIN’同一个部门同一个职位的员工的姓名，职位，部门号
#查询’WARD’的部门和职位
select job,deptno from emp where ename = ‘MARTIN’
select ename,job,deptno from emp
where (job,deptno) in(select job,deptno from emp where ename = ‘MARTIN’);
【练习】查询上级领导与’MARTIN’相同且同一个部门的员工姓名，MGR，部门号
6.4分页查询
limit 起始行号,返回行数
语法：sql语句 limit 起始行(第一行的行号为0),返回行数量
【例】查询员工表中工资排名前五(工资降序)的员工的姓名，工资
select ename,sal from emp
order by sal desc
limit 0,5;
【练习】查询员工表中工资排名前6到10(工资降序)的员工的姓名，工资
select ename,sal from emp
order by sal desc
limit 5,5;
【练习】查询员工表中最高工资的员工的姓名，工资
select ename,sal from emp
order by sal desc
limit 0,1;
总结sql语句
select 表1.列1,表2.列2,… 查询指定列
from 表1,表2,… 指定从哪些表查询
where 条件 限定查询结果
group by 列1,列2,… 分组查询
having 条件 限定分组查询结果
order by 列1 asc/desc,列2,… 查询结果排序
limit 起始行号,返回行数 返回限定行数的结果
补充：将子查询当作一张表
【例】查询与’ALLEN’同一个部门的员工的姓名，工资，部门号
select e.ename,e.sal,a.deptno from emp e,(select deptno from emp where ename = ‘ALLEN’) a
where e.deptno = a.deptno;
1、查询薪金(工资+奖金)比“ALLEN”多的所有员工
select sal+ifnull(comm,0.00) from emp ename = ‘allen’
Select ename from emp where sal+ifnull(comm,0.00) > (select sal+ifnull(comm,0.00) from emp ename = ‘allen’
);
2、查询在部门销售部工作的员工姓名、工资、职位
select e.ename,e.sal,e.job from emp e,dept d where d.dname = ‘sales’ and e.deptno = d.deptno;
3、使用子查询，找出哪个部门下没有员工
select distinct deptno from emp
select deptno from dept where deptno not in(select distinct deptno from emp );
4、使用子查询，找出那些工资低于平均工资的员工姓名、工资、职位、部门号
select ename,sal,job,deptno from emp where sal < (select avg(sal) from emp);
5、使用子查询，找出那些工资低于其中任意一个部门的平均工资的员工
select ename,sal from emp where sal < all(select avg(sal) from emp group by deptno);
6、查询每个部门入职最晚的员工的部门编号、员工姓名及其工资的信息
select deptno,ename,sal,max(hiredate) from emp
group by deptno;
7、查询每个部门的部门号、部门名称、部门人数(没有员工的部门人数显示为0)
select d.deptno,d.dname,count(e.empno) from emp e right join dept d on d.deptno = e.deptno group by d.deptno;
8、查询高于自己部门平均工资的员工名字,部门号,工资
select deptno,avg(sal) from emp group by deptno;
select e.ename,e.deptno,e.sal from emp e,(select deptno,avg(sal) ‘00’ from emp group by deptno) a
where e.deptno = a.deptno
and e.sal > a.00;
9、查询工资排名前三的员工信息
select ename,sal from emp order by sal desc limit 0,3;
第七章 存储过程
7.1语法
delimiter //
create procedure 过程名(模式 形参1 数据类型,……) #过程名(模式 形参1 数据类型,……)为自定义内容
begin
程序体; #程序体; 存储过程所做的事
end; //
delimiter ;
注意：
(1)这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默
认以";“为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储
过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;“当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
(2)存储过程根据需要可能会有输入、输出、输入输出参数，多个参数用”,"分割开。
(3)过程体的开始与结束使用BEGIN与END进行标识。
1)形参模式：
in：该参数用于接收外部的值传入存储过程内部 in可以省略不写
out：该参数用于将存储过程内部处理结果传出存储过程
数据输出： select 输出的对象;
2)调用存储过程： call 过程名(实参1,实参2,…)
【例】创建存储过程，调用该过程输出结果"hello mysql"
#创建存储过程
delimiter //
create procedure test_pro()
begin
select ‘hello mysql’ as “test”;
end; //
delimiter ;
#调用存储过程
call test_pro();
【练习】创建存储过程，调用该过程输出结果’大家好’
delimiter //
create procedure test1()
begin
select ‘大家好’ as “test”;
end; //
delimiter ;
call test1();
7.2 用户定义变量
MySQL存储过程中，定义变量有两种方式：
用户变量：
使用SET或SELECT直接赋值，变量名以 @ 开头.
例如: set @var=1; #定义变量名@var并赋值为1
select @var:=5;
select empno,ename into @var,@name from EMP where empno =7499 ;
可以在一个会话的任何地方声明，用户变量可以作用于当前整个连接，但是当前连接断开后，其所
定义的用户变量都会消失称为用户变量。
【例】定义一个存储过程，该过程接收两个整数，调用该过程，输出两个整数的和
delimiter //
create procedure test2(in a int,b int) #定义两个输入型参数，In可以省略不写
begin
set @var1 = a+b; #声明变量@var1并且为其赋值为a+b的和
select @var1; #输出结果@var1
end; //
delimiter ;
#调用存储过程
call test2(32,4222);
【练习】定义一个存储过程，该过程接收两个整数a和b,调用该过程，输出两个数的差
delimiter //
create procedure test3(a int,b int)
begin
set @sum = a-b; # select @sum:=a-b;
select @sum;
end; //
delimiter ;
call test3(3,8);
【例】创建一个存储过程，该过程接收一个员工编号，调用该过程，输出该员工的姓名，工资，部门号
#创建存储过程
delimiter //
create procedure test4(eno int)
begin
select ename,sal,deptno into @ename,@sal,@deptno from emp where empno = eno; #定义变量并赋值
select @ename;
select @sal;
select @deptno;
end; //
delimiter ;
call test4(7369);
delimiter //
create procedure test5(eno int)
begin
select ename,sal,deptno into @ename,@sal,@deptno from emp where empno = eno; #定义变量并赋值
select @ename,@sal,@deptno;
end; //
delimiter ;
call test5(7521);
【例】创建一个存储过程，该过程接收一个部门号，调用该过程，输出该部门工资最高的员工的姓名，工资，部门号
delimiter //
create procedure test6(dno int)
begin
select ename,sal,deptno into @ename,@sal,@deptno from emp
where sal = (select max(sal) from emp where deptno = dno);
select @ename,@sal,@deptno;
end; //
delimiter ;
call test6(30);
7.3 条件结构
7.3.1 if语句
if语句根据条件表达式的值确定执行不同的语句块，if语句的语法格式如下。
if 条件表达式1 then
语句块1;
[elseif 条件表达式2 then
语句块2] …
[else 语句块n]
end if;
说明：end if后必须以“;”结束
a)单分支IF语句
【例】创建一个存储过程，该过程接收一个整数，如果这个整数为偶数，调用该过程则输出’xx为偶数’
#创建存储过程
delimiter //
create procedure test7(a int)
begin
if mod(a,2)=0 then
select concat(a,‘为偶数’) as “判定结果”;
end if;
end; //
delimiter ;
call test7(13);
b)双分支IF语句
【例】创建一个存储过程，该过程接收一个整数(不能为0)，如果这个整数为偶数，调用该过程则输出’xx为偶数’,否则输出’xx为奇数’
delimiter //
create procedure test8(a int)
begin
if mod(a,2)=0 then
select concat(a,‘为偶数’) as “判定结果”;
else
select concat(a,‘为奇数’) as “判定结果”;
end if;
end; //
delimiter ;
c)多分支IF 语句
【例】创建一个存储过程，该过程接收一个整数(不能为0)，如果这个整数为偶数，调用该过程则输出’xx为偶数’,否则输出’xx为奇数’，
如果为0则输出’0为偶数’
delimiter //
create procedure test9(a int)
begin
if mod(a,2)=0 then
select concat(a,‘为偶数’) as “判定结果”;
elseif a = 0 then
select concat(a,‘为偶数’) as “判定结果”;
else
select concat(a,‘为奇数’) as “判定结果”;
end if;
end; //
delimiter ;
call test9(113);
【例】创建一个存储过程，该过程接收一个员工编号，如果该员工工资大于1500，则输出’xx工资等级为H’，否则输出’xx工资等级为L’
delimiter //
create procedure test10(eno int)
begin
select sal into @sal from emp where empno = eno;
if @sal > 1500 then
select concat(eno,‘工资等级为H’);
else
select concat(eno,‘工资等级为L’);
end if;
end; //
delimiter ;
call test10(7499);
【练习】创建一个存储过程，该过程接收一个员工编号，如果该员工工资大于1500，则输出’xx工资等级为H’，工资大于2500时
输出’xx工资等级为M’，否则输出’xx工资等级为L’
【练习】创建一个存储过程，该过程接收一个员工编号，如果该员工工资等级为1，输出’xx工资等级为1级’，等级为2，输出
‘xx工资等级为2级’，等级为3，输出’xx工资等级为3级’，等级为4输出’xx工资等级为4级’，否则输出’xx工资等级为5级’
7.4 case语句
语法：case 表达式
when value1 then 语句块1;
when value2 then 语句块2;
…
else 语句块n;
end case;
注意：表达式的结果为一个常量，即一个具体的值
【例】创建一个存储过程，该过程接收一个员工编号，如果员工所在部门为10号时，输出’财务部’，部门为20号时，输出’研发部’，
部门为30号时，输出’销售部’,否则输出’运营部’
#创建存储过程
delimiter //
create procedure test11(eno int)
begin
select deptno into @deptno from emp where empno = eno;
case @deptno
when 10 then select eno,@deptno,‘财务部’ as “部门名称”; # as as 不能省略
when 20 then select eno,@deptno,‘研发部’ as “部门名称”;
when 30 then select eno,@deptno,‘销售部’ as “部门名称”;
else select eno,@deptno,‘运营部’ as “部门名称”;
end case;
end; //
delimiter ;
call test11(7369);
【练习】创建一个存储过程，该过程接收一个员工编号，如果职位为’ANALYST’，输出结果为’开发’，为’CLERK’输出’职员’，
‘MANAGER’输出’经理’,为’SALESMAN’输出’销售人员’，否则输出’CEO’
7.5 循环结构
MySQL提供了三种循环语句，分别是while、repeat以及loop。
除此以外，MySQL还提供了iterate语句以及leave语句用于循环的控制。
7.5.1 while循环
语法：
while语句
循环标签:while 条件表达式 do #循环标签为自定义内容
循环体;
end while 循环标签;
说明：
1）end while后必须以“;”结束
2）当条件表达式的值为true时,反复执行循环体,直到条件表达式的值为false,while语句的语法格式如下
3）循环标签可以省略
【例】创建存储过程，用WHILE循环输出1-10
#创建存储过程
delimiter //
create procedure test12()
begin
set @num=0; #定义变量并赋值为0，循环的初始值
w:while @num<10 do
set @num=@num+1; #当@num<10为真时，执行@num=@num+1
select @num; #每一次循环输出@num
end while w; #当@num<10为假时，停止循环
end; //
delimiter ;
call test12();
【练习】创建存储过程，用while循环输出20-30
7.5.2 repeat 循环
repeat语句：当条件表达式的值为false时，反复执行循环，直到条件表达式的值为true
语法：
循环标签:repeat
循环体;
until 条件表达式
end repeat 循环标签;
说明：end repeat后必须以“;”结束
【例】创建存储过程，用repeat循环输出1-10
delimiter //
create procedure test13()
begin
set @num=0; #定义变量并赋值为0，循环的初始值
r:repeat
set @num=@num+1; #执行@num=@num+1
select @num; #每次循环输出@num
until @num>9 #当@num>9为真时，停止循环
end repeat r;
end; //
delimiter ;
call test13();
【练习】创建存储过程，用repeat循环输出20-30
7.6 循环控制语句
7.6.1 Leave
关键字：用于跳出当前的循环语句（例如while语句）
语法格式如下：
leave 循环标签;
说明：leave 循环标签后必须以“;”结束
【例】创建存储过程，循环输出1-10
#创建存储过程
delimiter //
create procedure test14()
begin
set @num=0;
w:while 1=1 do
set @num=@num+1; #1=1为真，进行@num=@num+1
select @num;
if @num>9 then
leave w;
end if;
end while w;
end; //
delimiter ;
call test14();
7.6.2 Iterate
关键字：用于跳出本次循环，继而进行下次循环。
语法格式：
iterate 循环标签;
说明：iterate循环标签后必须以“;”结束
【例】创建存储过程，输出1-20之间能被4整除的数
#创建存储过程
delimiter //
create procedure test15()
begin
set @num=0; #定义变量@num并赋值为0
w:while @num<20 do
set @num=@num+1; #当@num<20为真时，执行@num=@num+1
if mod(@num,4)=0 then
select @num; #每一次循环结果@num能够被4整除，则输出@num
else
iterate w; #mod(@num,4)=0条件为假时，跳出本次循环。
end if;
end while w;
end; //
delimiter ;
call test15();
【练习】创建存储过程，输出1-10之间能被3整除的数
7.6.3 Loop循环 条件表达式为真时，结束循环
语法：
循环标签: loop
循环体;
if 条件表达式 then
leave 循环标签;
end if;
end loop;
说明：end loop后必须以“;”结束
【例】创建存储过程，输出1-10
#创建存储过程
delimiter //
create procedure test16()
begin
set @num=0;
l:loop
set @num=@num+1;
select @num;
if @num>9 then
leave l;
end if;
end loop;
end; //
delimiter ;
call test16();
delimiter //
create procedure test18()
begin
set @num=1;
l:loop
select @num;
set @num=@num+1;
if @num>9 then
leave l;
end if;
end loop;
end; //
delimiter ;
call test18();
【例】向dept表中插入50行数据，部门号是整10的数（要求部门名称与地址不能重复）
#创建存储过程
delimiter //
create procedure add_dept()
begin
set @deptno=50;
set @dname=‘测试’;
set @loc=‘成都’;
w:while @deptno<540 do
insert into dept values(@deptno,concat(@deptno,@dname),concat(@deptno,@loc)); #当 @deptno<540为真时，执行插入语句，并执行循环语句
set @deptno = @deptno+10;
end while w;
end; //
delimiter ;
【练习题】
1、输入学生的成绩，如果成绩大于等于90分，打印成绩优秀；成绩小于90大于等于80分，打印成绩良好；小于80分大于等于60分，打印成绩一般；否则打印不及格。
2、请输入两个整数a,b，比较a+b和a×b哪个大，输出判断结果。
3、编写一个存储过程， 输入部门号， 统计emp表中该部门员工人数，并返回输出人数
4、编写一个存储过程，统计emp表中的所有员工的平均工资，并返回输出
5、编写一个存储过程，输入一个员工的id号，返回输出该员工的所属部门名称和他的直接领导的姓名
6、编写一个存储，要求在调用存储时，输入一个员工的id号，该函数能对该id号进行判断，判断其是一个普通员工，还是一个管理人员，如果是一个管理人员，就把他所管理的人数统计出来
3203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
