MySQL基础（二）之内置约束与函数

本篇主要内容如下：

一、表约束

  在数据库中为了保证数据库插入时按照指定规则插入，引入表约束，常见表约束有
  1.主键约束 primary key（不允许为空，不允许重复）如果是整数类型一般定义主键自动增长 auto_increment，一般通过可视化连接工具添加。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10),
  `password` char(10),
  PRIMARY KEY (`id`)
)

  2.唯一约束，也叫唯一索引，用于设置该字段不可重复。

ALTER TABLE `user`.`user` ADD UNIQUE INDEX index_name (`name`);

  3.非空约束 not null 用于指定该字段不能为空一般在创表时指定或可视化连接工具添加。也可以和 DEFAULT 配合使用，表示不输入时该字段的默认值。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `password` char(10) NOT NULL DEFAULT 123456,
  PRIMARY KEY (`id`)
)

  4.外键约束，在创建外键约束时一定要保证两个表的数据库引擎相同且不能为 MYISAM，一般通过可视化连接工具添加。

ALTER TABLE `client` ADD CONSTRAINT `emp_id_fk` FOREIGN KEY (`emp_id`) REFERENCES `emp`(`id`);

一般创表命令如下

CREATE TABLE mytb(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    pwd VARCHAR(40) NOT NULL,
    birth DATE,
    PRIMARY KEY ( id )
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为 NULL ，就会报错。
• AUTO_INCREMENT 定义列为自增的属性，一般用于主键，每次插入数据后数值会自动加 1。
• PRIMARY KEY 关键字用于定义列为主键。 可以使用多列来定义主键称为复合主键，列间以逗号分隔。
• ENGINE 设置存储引擎，使用 show engines 命令可以查看 MySQL 支持的数据库引擎，CHARSET 设置编码。
  注意：utf8 与 utf8mb4 的区别在于 utf8mb4 兼容部分图标字体 。如果引擎和字符编码在安装时设置好的则在创表时可以不指定。

二、数据类型

  MySQL 中为了满足各种业务需要，提供很多数据类型，常用类型如下：

• INT       整数        4 字节
• BIGINT     长整数       8 字节
• FLOAT     单精度小数      单精度浮点数值
• DOUBLE   双精度小数      双精度浮点数值
• DECIMAL    小数值        对DECIMAL(P,D)
• DATE      日期值      YYYY-MM-DD
• TIME      时间值      HH:MM:SS
• DATETIME   混合日期和时间值     YYYY-MM-DD HH:MM:SS
• CHAR      定长字符串      0-255字节
• VARCHAR    变长字符串      0-65535 字节
• TEXT      长文本数据      0-65535 字节
• BLOB       二进制形式的数据      0-65535 字节
  注意：

1.在 DECIMAL(P,D) 的语法中： P 是表示有效数字数的精度。 P 范围为 1〜65。 D 是表示小数点后的位数。 D 的范围是 0~30。MySQL 要求 D 小于或等于(<=)P。如：DECIMAL(6,2)中最多可以存储 6 位数字，小数位数为 2 位；因此，列的范围是从-9999.99 到 9999.99。

2.char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。char 是一种定长字符串。它一旦声明后无论存储的值是否达到该长度，都占有相应的字节长度。varchar 是一种变长字符串，它的长度由存储的内容决定，为存满的时以实际存储内容为主。

3.BLOB 是一个二进制大对象，可以容纳可变数量的二进制数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

4.TEXT 同样也有 4 种类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们的区别也是可存储的最大长度不同。具体使用哪种类型可根据实际情况选择。

练习：提交操作过程中的图片 word

1.创建一张表 system_user 包含 int id 自增主键，char(10) name 不为空，varchar(255) password 不为空且默认值为 123456，blob image 图片，text information 介绍，tinyint sex 性别，decimal(10,2) money 资产，DATETIME birthdays 生日，并使用 SQLYog 往表中插入 3 条测试数据。

2.创建一张表 system_classroom 包含 int id 不为空且自增，varchar(50) name 不为空，并往表中插入三个班级：JAVA，UI，新媒体

3.在 system_user 中加入字段 classroom_id，并添加外键指向班级表中的 id，同时为所有的 system_user 分配自己的班级。

参考答案：

#1、创建一张表 system_user 包含 int id，char（10） name， varchar （255） password 不为空且默认值 123456，blob image、text information
#tinyint sex,decimal(10,2) money. id 不为空且自增。并插入3条数据
create table system_user(
id int primary key not null auto_increment comment '编号',
name char(10) not null comment '姓名',
password varchar(255) not null default '123456' comment '密码',
image blob comment '头像',
information text comment '简历',
sex tinyint comment '性别',
money decimal(10,2) comment '余额'
)
#2、创建一张表 system_classroom 包含int id, varchar(50) name 不为空.并插入三个班级 JVAV ，UI ，新媒体
create table system_classroom(
id int primary key not null  comment '班级编号',
name varchar(50) comment '班级名称'
)
#3、在system_user中加入 system_user ,并添加外键指向班级表中的id，同时为system_user分配自己的班级
alter table system_user add classroom_id int after id;
alter table system_user add constraint id foreign key (classroom_id) references system_classroom(id)

三、视图

  视图其实就是一张虚拟表，一张从别的表中导出的一张不完整或经过运算的虚拟表。
  视图有什么用，当我们经常查询一个表中的某几个字段的时候，我们可以把这几个字段单独导出一个视图，以后查询的时候直接可以 select *，在数据库中只存放了视图的定义，没有存放视图的数据，视图是依赖原来表中的数据的，原表中的数据一旦发生变化，视图查询出的结果也会跟着变化。下面进行创建一个视图。

案例：给语文老师创建一个视图

CREATE VIEW vi_chinese_user 
AS SELECT id,`name`,`chinese` FROM `student`;

视图在查询时和普通表查询语句完全相同。删除视图使用命令如下：

DROP VIEW `vi_chinese_user`;

四、触发器

  触发器是由事件来触发某个操作，这些事件包括 insert、delete、update 语句。当数据库执行这些事件时，就会激活触发器执行相应的操作。
  我们需要一个表，这里用 user 表，用来对该表进行增删改操作，另外需要一个 user_log 表，当对 user 表操作的时候，会激活我们的触发器，对 user_log 表做出相应的操作。

CREATE TRIGGER auto_save_time BEFORE INSERT 
ON `user` FOR EACH ROW 
INSERT INTO user_log(log_time) VALUES(NOW());

  这条语句的意思是，创建一个触发器名字是 auto_save_time，在 user 表的任意行执行 insert 语句之前会激活触发器，并执行 insert into user_log(log_time) values(now()); 语句。下面我们对 user 表进行一个 insert 操作，可以看出，激活了触发器，并执行了那条语句。now() 表示当前时间括号前不能有空格。
  查看触发器使用命令 show triggers；删除触发器使用命令 drop trigger 触发器名；触发器有 6 个 分别是插入前后，删除前后，修改前后六个，与前面案例中的 before 对应的还有 after。

练习：

1.创建一张表 student(id,name) 和 student_log (id,log_time,method)，创建 3 个触发器，完成 student 表的所有修改操作的记录。

参考答案：

CREATE TABLE `student_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `log_time` DATETIME NOT NULL,
  `method` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;


CREATE TRIGGER student_insert BEFORE INSERT 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'添加');

CREATE TRIGGER student_update BEFORE UPDATE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'修改');

CREATE TRIGGER student_delete BEFORE DELETE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'删除');

五、存储过程

  存储过程 (Stored Procedure) 是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集 , 存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库中的一个重要对象；存储过程中可以包含逻辑控制语句和 数据操纵语句 , 它可以接受参数，输出参数，返回单个或多个结果集以及返回值；

create table p_user(
	id int primary key auto_increment,
	name varchar(10),
	sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"男");
insert into p_user(name,sex) values('B',"女");
insert into p_user(name,sex) values('C',"男");

  创建存储过程(查询得到男性或女性的数量，如果传入的是 0 就获取女性的数量，否则获取男性的数量)

DELIMITER $
CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
IF sex_id=0 THEN
SELECT COUNT(*) FROM p_user WHERE p_user.sex='女' INTO user_count;
ELSE
SELECT COUNT(*) FROM p_user WHERE p_user.sex='男' INTO user_count;
END IF;
END 
$

调用存储过程

SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;

查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性。

六、字符串、数字、时间处理函数

  在查询数据时有事需要对查出的字段进行处理，如小写转大写，切割等。这时就需要使用数据库的字符串处理函数，如下是 mysql 常用字符串处理函数。

CHARSET(字段)	                            返回字段的字符集
CONCAT(string2  [,... ])	            连接字串
INSTR(string ,substring )	            返回substring在string中出现的位置,没有返回0
UCASE(string2 )	                            转换成大写
LCASE(string2 )	                            转换成小写
LEFT(string2 ,length )	                    从string2中的左边起取length个字符
LENGTH(string )	                            返回string字节数量
REPLACE(str ,search_str ,replace_str )	    在str中用replace_str替换search_str
STRCMP(string1 ,string2 )	            逐字符比较两字串大小,返回1、0和-1
SUBSTRING(str , position  [,length ])	    从str的position开始,取length个字符
LTRIM(string2 ) RTRIM (string2 )            trim 去除前端空格或后端空格

案例：

SELECT CHARSET(`name`) FROM `emp`;
SELECT CONCAT(`name`,`pwd`) FROM `emp`;
SELECT INSTR(`pwd`,'2') FROM `emp`;
SELECT UCASE(`name`) FROM `emp`;
SELECT LCASE(`name`) FROM `emp`;
SELECT LEFT(`pwd`,2) FROM `emp`;
SELECT LENGTH(`pwd`) FROM `emp`;
SELECT STRCMP('b' ,'b' ) FROM `emp`;
SELECT SUBSTRING(`pwd`,2,2) FROM `emp`;
SELECT LTRIM(`name`) FROM `emp`;
SELECT RTRIM(LTRIM(`name`)) FROM `emp`;

数字处理函数

以下是 mysql 常用的数字处理函数

ABS(number2 )	                            绝对值
CEILING (number2 )	                    向上取整
FLOOR (number2 )	                    向下取整
FORMAT (number,小数位数)	            保留小数位数
HEX (DecimalNumber )	                    转十六进制
LEAST (number , number2  [,..])	            求最小值
MOD (numerator ,denominator )	            求余
RAND([seed])	                            随机数

案例：

SELECT `name`,ABS(`english`) FROM `student`;
SELECT `name`,CEILING(100.00000001) FROM `student`;
SELECT `name`,FLOOR (100.00000001) FROM `student`;
SELECT `name`,`english`,FORMAT (`english`,2) FROM `student`;
SELECT `name`,`english`,HEX  (`english`) FROM `student`;
SELECT `name`,RAND() FROM `student`;
SELECT `name`,CEILING(RAND()*10) FROM `student`;

日期时间处理函数

获取当前的日期（包含具体的时分秒）

SELECT NOW(),LOCALTIME(),SYSDATE()

获取当前的日期（不包含具体的时分秒）

SELECT CURRENT_DATE(),CURDATE()

日期与时间戳之间的转化

SELECT UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1629882598)

获取当前的时间（时分秒）

SELECT CURRENT_TIME(),CURTIME()

获取月份

MONTH(date)返回月份的数值； MONTHNAME(date)返回月份的英文名称

SELECT MONTH(NOW()),MONTHNAME(NOW())

获取天的函数

DAYOFMONTH(date)返回日期属于当前月的第几天； DAYOFYEAR(date)返回日期属于当前年的第几天

SELECT DAYOFMONTH(NOW()),DAYOFYEAR(NOW())

获取星期的函数

DAYNAME(date)返回星期几的英文名字；DAYOFWEEK(date)返回星期几的数值，返回值的范围是1-7，1表示星期日，以此类推； WEEKDAY(date)返回星期几的数值，返回值的范围是0-6，0表示星期一，以此类推

SELECT DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW())

两个日期间的间隔天数

TO_DAYS(date)日期date与默认日期（000年1月1日）之间间隔的天数；FROM_DAYS(number)默认日期经过 number 天后的日期；DATEDIFF(date1，date2)获取指定两个日期间的相隔天数，date1-date2

SELECT TO_DAYS(NOW()),FROM_DAYS(738166),DATEDIFF(NOW(),'2020-01-01')

日期的加减法

DATE_ADD(date,INTERVAL expr type)，在 date 的基础上添加上指定的时间间隔后的日期。

DATE_SUB(date,INTERVAL expr type)在 date 的基础上减去上指定的时间间隔后的日期。

type 值常用的有：year、month、day、hour、minute、second、microsecond（毫秒）、week、quarter

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY),DATE_SUB(NOW(),INTERVAL 1 DAY)

练习：（默写 20 分钟）

数据：导入下面的数据并完成相应的查询，下载地址

CREATE TABLE `employee` (
  `ID` int(4) NOT NULL,
  `NAME` varchar(10) NOT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MANAGER` int(4) DEFAULT NULL,
  `HIRE_DATE` date DEFAULT NULL,
  `SALARY` double(7,2) DEFAULT NULL,
  `PRIZE_MONEY` double(7,2) DEFAULT NULL,
  `DEPT` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7369,'smith','clerk',7902,'1980-12-17',800.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7499,'allen','salesman',7698,'1981-02-20',1600.00,300.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7521,'ward','salesman',7698,'1981-02-22',1250.00,500.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7566,'jones','manager',7839,'1981-04-02',2975.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7654,'martin','salesman',7698,'1981-09-28',1250.00,1400.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7698,'blake','manager',7839,'1981-05-01',2850.00,NULL,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7782,'clark','manager',7839,'1981-06-09',2450.00,NULL,'accounting');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7839,'king','president',NULL,'1981-11-17',5000.00,NULL,'accounting');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7844,'turner','salesman',7698,'1981-09-08',1500.00,0.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7900,'james','clerk',7698,'1981-12-03',950.00,NULL,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7902,'ford','analyst',7566,'1981-12-03',3000.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7934,'miller','clerk',7782,'1982-01-23',1300.00,NULL,'accounting');

题目：

1.查询没有上级领导的员工的编号，姓名，工资
2.查询表中有奖金的员工的姓名，职位，工资，以及奖金
3.查询含有上级领导的员工的姓名，工资以及上级领导的编号
4.查询表中名字以‘S’开头的所有员工的姓名
5.查询表中名字的最后一个字符是'S'的员工的姓名
6.查询倒数的第2个字符是‘E’的员工的姓名
7.查询表中员工的名字中包含‘A’的员工的姓名
8.查询表中名字不是以'K'开头的员工的所有信息
9.查询表中名字中不包含‘A’的所有员工的信息
10.做文员的员工人数（job 中 含有 CLERK 的）
11.销售人员 job: SALESMAN 的最高薪水的员工信息
12.查询表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
13.查询表中员工在 ACCOUNTING 部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门
14.查询表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
15.查询表中ACCOUNTING部门或者RESEARCH部门中员工的编号，姓名，所属部门
16.查询表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
17.查询工资高于3000或者部门是SALES的员工的姓名，职位，工资，入职时间以及所属部门
18.查询不是SALES部门的员工的所有信息
19.查询奖金不为空的员工的所有信息
20.查询表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
21.查询表中部门是ACCOUNTING或者RESEARCH中，所有员工姓名，职务，工资，根据工资进行升序排列
22.查询表中所有的数据，然后根据部门进行升序排列，如果部门一致，根据员工的编号进行降序排列
23.查询表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门，上级领导的编号，根据部门进行降序排列，如果部门一致根据工资进行升序排列。
24.查询表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
25.统计表中员工的总数量
26.查出表中员工的最高工资的员工
27.查出表中员工编号的最大的员工
28.查询表中员工的最低工资的员工
29.查询表中在周一入职的员工
30.查询表中在每月初(1号)入职的员工

参考答案：

1.查询没有上级领导的员工的编号，姓名，工资.
SELECT ID,NAME,SALARY FROM employeeloyee WHERE MANAGER IS NULL;

2.查询employee表中有奖金的员工的姓名，职位，工资，以及奖金
SELECT NAME,job,SALARY,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

3.查询含有上级领导的员工的姓名，工资以及上级领导的编号
SELECT NAME,SALARY,MANAGER FROM `employee` WHERE MANAGER IS NOT NULL

4.查询employee表中名字以‘S’开头的所有员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE 'S%'

5.查询employee表中名字的最后一个字符是'S'的员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE '%S'


6.查询倒数的第2个字符是‘E’的员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE '%E_'


7.查询employee表中员工的名字中包含‘A’的员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE '%A%'

8.查询employee表中名字不是以'K'开头的员工的所有信息
SELECT NAME FROM `employee` WHERE NAME NOT LIKE 'K%'


9.查询employee表中名字中不包含‘A’的所有员工的信息
SELECT * FROM `employee` WHERE NAME NOT LIKE '%A%'


10.做文员的员工人数（job 中 含有 CLERK 的）
SELECT COUNT(0) FROM `employee` WHERE job='clerk'

11.销售人员 job: SALESMAN 的最高薪水的员工信息
SELECT * FROM `employee` WHERE job='SALESMAN' ORDER BY SALARY DESC LIMIT 1


12.查询表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
SELECT id,NAME,job,SALARY FROM `employee` WHERE SALARY BETWEEN 1000 AND 2000
SELECT id,NAME,job,SALARY FROM `employee` WHERE SALARY >= 1000 AND SALARY<= 2000

13.查询表中员工在 ACCOUNTING 部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门
SELECT NAME,job,MANAGER,dept FROM `employee` WHERE dept ='ACCOUNTING' AND MANAGER IS NOT NULL

14.查询表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
SELECT id,NAME,job,SALARY FROM `employee` WHERE NAME LIKE '%E%' AND job != 'MANAGER'

15.查询表中ACCOUNTING部门或者RESEARCH部门中员工的编号，姓名，所属部门
SELECT id,NAME,dept FROM `employee` WHERE dept='ACCOUNTING' OR dept ='RESEARCH'


16.查询表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
SELECT id,NAME,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NULL OR NAME NOT LIKE '%T_'

17.查询工资高于3000或者部门是SALES的员工的姓名，职位，工资，入职时间以及所属部门
SELECT NAME,JOB,SALARY,HIRE_DATE dept FROM `employee` WHERE SALARY >3000 OR dept ='SALES'


18.查询不是SALES部门的员工的所有信息
SELECT * FROM `employee` WHERE dept !='SALES'


19.查询奖金不为空的员工的所有信息
SELECT * FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

20.查询表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
SELECT id,NAME,job FROM `employee` ORDER BY id DESC


21.查询表中部门是ACCOUNTING或者RESEARCH中，所有员工姓名，职务，工资，根据工资进行升序排列
SELECT NAME,JOB,SALARY FROM `employee` WHERE dept ='ACCOUNTING' OR dept ='RESEARCH' ORDER BY SALARY ASC


22.查询表中所有的数据，然后根据部门进行升序排列，如果部门一致，根据员工的编号进行降序排列
SELECT * FROM `employee` ORDER BY dept ASC,id DESC 

23.查询表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门，上级领导的编号，
根据部门进行降序排列，如果部门一致根据工资进行升序排列。
SELECT id,NAME,SALARY,DEPT,MANAGER FROM `employee` WHERE SALARY>1000 OR MANAGER IS NULL ORDER BY dept DESC,SALARY ASC


24.查询表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
SELECT id,NAME,SALARY,PRIZE_MONEY FROM `employee` WHERE NAME NOT LIKE '%S%' ORDER BY SALARY ASC,id DESC

25.统计表中员工的总数量
SELECT COUNT(0) FROM `employee`

26.查出表中员工的最高工资的员工
SELECT * FROM `employee` ORDER BY SALARY DESC LIMIT 1

27.查出表中员工编号的最大的员工
SELECT * FROM `employee` ORDER BY id DESC LIMIT 1

28.查询表中员工的最低工资的员工。
SELECT * FROM `employee` ORDER BY SALARY ASC LIMIT 1

29.查询表中在周一入职的员工。
select * from employee where WEEKDAY(HIRE_DATE) = 0

30.查询表中在每月初(1号)入职的员工。
select * from employee where DAYOFMONTH(HIRE_DATE) = 1

七、数据库的引擎（自学）

  数据库提供的引擎如下：

  MyISAM：默认的 MySQL 插件式存储引擎，它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

  InnoDB：用于事务处理应用程序，具有众多特性，包括 ACID 事务支持，提供行级锁。

  Memory：将所有数据保存在内存中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

…

常用的引擎有两个 Innodb 和 MyIASM。

Innodb 引擎

  Innodb 引擎提供了对数据库 ACID 事务的支持，并且实现了 SQL 标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于 MySQL 后台的完整数据库系统，MySQL 运行时 Innodb 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT 类型的索引，而且它没有保存表的行数，当 SELECT COUNT(*) FROM TABLE 时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用 Innodb 引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个 SQL 语句时MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表。

MyIASM 引擎

  MyIASM 是 MySQL 默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE (更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和 Innodb 不同，MyIASM 中存储了表的行数，于是 SELECT COUNT(*) FROM TABLE 时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么 MyIASM 也是很好的选择。

主要区别：

1、MyIASM 是非事务安全的，而 InnoDB 是事务安全的

2、MyIASM 锁的粒度是表级的，而 InnoDB 支持行级锁

3、MyIASM 支持全文类型索引，而 InnoDB 不支持全文索引

4、MyIASM 相对简单，效率上要优于 InnoDB，小型应用可以考虑使用 MyIASM

5、MyIASM 表保存成文件形式，跨平台使用更加方便

应用场景：

  1、MyIASM 管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量 select 操作，应该选择 MyIASM

  2、InnoDB 用于事务处理，具有 ACID 事务支持等特性，如果在应用中执行大量 insert 和 update 操作，应该选择 InnoDB

章节练习：以 word 文档的方式提交

1.创建表 person （id int 自增主键, name varchar(10), sex char(2), age int），并为该表创建一张日志表 person_log （id int 自增主键，method varchar(6)方式，log_date datetime 时间日期）用于记录该表的操作，同时创建触发器完成相关增删改的监控日志。

2.向 person 表中插入数据（1，张三，男，20），（2，李四，男，30），（3，王五，男，25），（4，赵六，男，22），（5，王七，男，22），（6，朱八，男，-22）并查看 person_log 的数据。

3.创建一个 v_person 的视图，原表为 person。v_person 上不包含 age 字段。

4.创建一个存储过程 get_count（‘王’,@person_count）传入用户的姓，返回该姓的用户数量。

5.查询 person 表，将 person 的 id 和 age 拼接在一起取别名为 code。

6.查询 person 表全部信息，并对 age 取绝对值。

参考答案：

CREATE TABLE `mydb`.`person` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (10),
  `sex` CHAR(2),
  `age` INT,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `mydb`.`person_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `method` VARCHAR (6) NOT NULL,
  `log_date` DATETIME,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TRIGGER `person_add` BEFORE INSERT 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('添加', NOW()) ;

CREATE TRIGGER `person_update` BEFORE UPDATE 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('修改', NOW()) ;

CREATE TRIGGER `person_delete` BEFORE DELETE 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('删除', NOW()) ;


CREATE VIEW v_person AS
SELECT `id`,`name`,`sex` FROM `person`;


SELECT * FROM v_person;



CREATE  PROCEDURE `get_count`(IN first_name VARCHAR(5),OUT person_count INT)
BEGIN
   SELECT COUNT(*) FROM `person` WHERE `name` LIKE CONCAT(first_name,'%') INTO person_count;
    END


DELIMITER ;
SET @user_count = 0;
CALL `get_count`('王', @user_count);
SELECT @user_count;


SELECT CONCAT(id,age) `code`,`name`,sex FROM `person`;

SELECT id,NAME,sex,ABS(age) age FROM `person`;