web前端2021--小白进阶之路数据库(2)

web前端2021–小白进阶之路数据库(2)

1、列约束
MYSQL要对插入的数据进行特定的验证，满足条件则允许插入，否则被认为是非法插入，例如一个人的性别只能是男女，一个人的工资只能是正数。

• 主键约束PRIMARY KEY：声明主键约束的列上不允许插入重复的值，一个表中只能有一个主键约束，查询时会按照偏好列从小到大排序，加快查找速度。
• 非空约束NOT NULL：声明了非空约束的列上禁止插入NULL
• 唯一约束UNIQUE：声明了唯一约束的列上，禁止插入重复的值，允许插入NULL，甚至多个NULL
• 默认值约束DEFAULT：可以使用关键字声明默认值，有2种方式
• 检查约束CHECK：检查约束可以对插入的数据进行自定义验证(MySQL不支持检查运输)
• 外键约束FOREIGN
  KEY：声明了外键约束的列上，取值必须在另一个表的主键上出现过，两者的列类型要保持一致，一个表外键约束允许使用多次，并且可以使用NULL

注意：主键列上禁止插入NULL

练习：

1. 在商品表laptop中给编号添加主键约束，并插入数据测试。
2. 给laptop表的title列，添加非空约束和唯一约束，并插入数据测
3. 给笔记本表laptop添加默认价格2999，默认的是否在售为0，并应用两种方式插入数据。

CREATE TABLE laptop(
  lid INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(64) NOT NULL UNIQUE,
  price DECIMAL(7,2) DEFAULT 2999,  #99999.99
  isOnsale BOOL DEFAULT 0,
  familyId INT,
  FOREIGN KEY(familyId) REFERENCES laptop_family(fid)
  #外键familyId的取值要到另一个表laptop_familydefid中
);

#插入数据
INSERT INTO laptop VALUES(1,'小米Air',4199,'发烧版','详情一','2018-11-11',1,30);
INSERT INTO laptop VALUES(2,'ThinkpadE470',2999,'前端开发','详情二','2016-9-1',0,10);
INSERT INTO laptop VALUES(3,'燃7000',DEFAULT,'测试','详情三','2016-9-1',0,10);
INSERT INTO laptop VALUES(4,'外星人',DEFAULT,'测试','详情四','2016-9-1',DEFAULT,10);
INSERT INTO laptop(lid,title) VALUES(5,'apple air');
INSERT INTO laptop(lid,title) VALUES(NULL,'apple REO');

2、自增列
AUTO_INCREMENT：自动增长，若一个列上声明了自增列，则无需手动赋值NULL，就会自动获取当前最大的值，然后加1插进去
注意：自增列必须加载主键列上，自增列允许手动赋值

3、简单查询

• 查询特定的列
  查询所有员工的编号和姓名

SELECT eid,ename FROM emp;

• 查询所有的列

SELECT eid,ename,sex,birthday,salary,deptId FROM emp;
SELECT * FROM emp;

• 给列起别名
  查询所有员工的姓名和性别，使用汉字别名

SELECT ename AS 姓名,sex AS 性别 FROM emp;
//查询所有员工的姓名和生日，使用一个字母别名
SELECT ename a,birthday b FROM emp;

• 显示不同的记录
  查询员工都分布在哪些部门

SELECT DISTINCT sex FROM emp; 

• 查询时执行计算

SELECT  2+3-4*5+9*37;

• 查询结果及排序
  (1)查询所有的部门，结果集按照部门编号升序排列
  (2)查询所有的部门，结果集按照部门编号降序排列

SELECT * FROM dept ORDER BY did ASC;//ascendant升序
SELECT * FROM dept  ORDER BY did DESC; //descendant降序

• 条件查询

//练习：查询出姓名为lucy的编号，姓名，性别，工资
SELECT eid,ename,sex,salary FROM emp WHERE ename='lucy';
//练习：查询出20号部门以下的员工有哪些
SELECT *  FROM emp  WHERE deptId=20;
//练习：查询出所有女员工有哪些
SELECT * FROM emp WHERE sex=0；
//练习：查询出工资在7000以上的员工有哪些
SELECT *FROM emp WHERE salary>7000;
//练习：查询出不在10号部门的员工有哪些
SELECT *  FROM emp  WHERE  deptId!= 10;
//练习：查询出没有明确部门的员工有哪些
SELECT * FROM emp WHERE deptId IS NULL;
//练习：查询出有明确部门的员工有哪些
SELECT * FROM emp  WHERE  deptId IS NOT  NULL;
//练习：查询出工资在6000以上的男员工有哪些
SELECT * FROM emp WHERE salary>6000  AND  sex=1;
//练习：查询出工资在5000~7000的员工有哪些
SELECT * FROM emp WHERE salary>=5000 AND  salary <=7000;
//在...之间  BETWEEN  AND 
SELECT *  FROM emp WHERE salary BETWEEN 5000 AND 7000;
//练习：查询出1993年出生的员工有哪些 1993-1-1~1993-12-31
SELECT * FROM  emp  WHERE birthday>='1993-1-1' AND birthday <='1993-12-31';
SELECT * FROM emp WHERE birthday BETWEEN '1993-1-1' AND '1993-12-31';
//练习：查询出工资在5000以下和7000以上的员工有哪些
SELECT * FROM emp WHERE salary<5000 OR salary >7000;
SELECT * FROM emp WHERE NOT BETWEEN 5000 AND 7000;
//练习：查询出20号部门和30号部门的员工有哪些
SELECT * FROM emp WHERE deptId=20 OR deptId=30;
SELECT * FROM emp  WHERE deptId IN(20,30);
//练习：查询出不在20号部门和30号部门的员工有哪些
SELECT *  FROM emp WHERE deptId NOT IN(20,30);

IS NULL / IS NOT NULL
AND / OR
BETWEEN AND / NOT BETWEEN AND
IN() / NOT IN()

• 模糊条件查询

//查询出姓名中含有字母a的员工有哪些
SELECT * FROM emp WHERE ename LIKE '%e%';
//练习：查询出姓名中以e结尾的员工有哪些
SELECT * FROM emp WHERE ename LIKE '%e'
//练习：查询出姓名倒数第二个字符为e的员工有哪些
SELECT * FROM emp WHERE ename LIKE '%e_';

% 匹配任意0个或者多个字符 字符 >=0
_ 匹配任意一个字符 字符=1
以上两个字符必须结合着LIKE 关键字使用

• 分页查询
  每页开始查询的值=(当前页码-1)*每页的数据

SELECT * FROM emp LIMIT 每页开始查询的值，每页的数据量;
//假设每页显示5条数据
第一页 SELECT * FROM emp LIMIT 0,5;
第二页 SELECT * FROM emp LIMIT 5,5;
第三页 SELECT * FROM emp LIMIT 10,5;
//练习：假设每页显示7条数据，查询前3耶每页的数据
第一页 SELECT * FROM emp LIMIT 0,7;
第二页 SELECT * FROM emp LIMIT 7,7;
第三页 SELECT * FROM emp LIMIT 14,7;

注意：LIMIT后的两个值必须是数值型。

4、复杂查询

• 聚合查询/分组查询
  聚合函数
  函数是一个功能体，需要提供若干个数据，产出结果。———饺子机
  COUNT() / SUM() / AVG() / MAX() / MIN()
  数量 总和 平均 最大 最小

//示例：查询所有员工的数量
SELECT  COUNT (ename) FROM emp;
//练习：通过部门编号查询员工数量
SELECT COUNT(deptId) FROM emp;
//练习：通过员工编号查询员工数量
SELECT COUNT(eid) FROM emp;
//练习：通过*号查询所有女员工的数量
SELECT COUNT(*) FROM emp;
//练习：查询所有员工的工资总和
SELECT SUM(salary) FROM emp;
//练习：查询所有男员工的平均工资
SELECT AVG(salary) FROM emp WHERE sex=1;
//练习：查询出工资最高的女员工工资是多少
SELECT MAX(salary) FROM emp WHERE sex=0;
//练习：查询出年龄最大的员工生日是多少
SELECT MIN(birthday) FROM emp;

分组查询：只能查询聚合函数以及分组条件
GROUP BY

//练习：查询出男女员工的最高工资，最低工资，平均工资
SELECT MAX(salary),MIN(salary),AVG(salary),sex FROM  emp  GROUP BY sex;
//练习：查询出各个部门的员工数量和总的工资是多少
SELECT SUM(salary),COUNT(eid) FROM emp GROUP BY deptId;
//练习：查询出1991年出生的员工有哪些
SELECT * FROM emp WHERE YEAR(birthday)=1991;
//练习：查询出12月生日的员工有哪些
SELECT * FROM emp WHERE MONTH(birthday)=12;

• 子查询
  示例：查询研发部的员工有哪些

//步骤1：查询研发部的部门编号——10
SELECT did FROM dept WHERE dname='研发部';
//步骤2：查询出10号部门的员工有哪些
SELECT ename FROM emp WHERE deptId=10;
//总：
SELECT ename FROM emp WHERE (SELECT did FROM dept WHERE dname='研发部');

//练习：查询出和TOM 同一年出生的员工有哪些
//步骤1：查询出tom出生的年份
SELECT YEAR(birthday) FROM emp WHERE ename='tom';
//步骤2：查询出1990年出生的员工有哪些
SELECT * FROM emp WHERE YEAR(birthday)=1990;
SELECT * FROM emp WHERE YEAR(birthday)=(SELECT YEAR(birthday) FROM emp WHERE ename='tom' ) AND ename != 'tom';
//练习：查询出比tom工资高的员工有哪些
SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename='tom');

• 多表查询
  示例：查询所有员工姓名及其部门名称
  在多表查询中添加查询条件！

//内连接——和之前的结果一样
SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;
//左外连接——显示左侧表中所有记录
SELECT ename,dname FROM emp  LEFT  OUTER JOIN dept ON deptId=did;
                                                    左                                         右
//右外连接——显示右侧表中所有记录
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;
                                                    左                                        右

在左外和右外连接中，OUTER关键字可以省略

全连接——显示左侧和右侧所有记录
FULL JOIN ,MySQL不支持
UNION ALL联合后不合并相同记录

(SELECT ename,dname FROM emp  LEFT  OUTER JOIN dept ON deptId=did)    UNION  ALL  (SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did);
//UNION 联合后合并相同记录
(SELECT ename,dname FROM emp  LEFT  OUTER JOIN dept ON deptId=did)    UNION (SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did);