9.10学习总结

-- SQL的执行顺序
-- 第一步：执行FROM
-- 第二步：WHERE条件过滤
-- 第三步：GROUP BY分组
-- 第四步：执行SELECT投影列
-- 第五步：HAVING条件过滤
-- 第六步：执行ORDER BY排序


-- 数据库完整性的分类：
-- 1、实体完整性（保证一行数据是有效的）（保证每行所代表的实体能相互区别，不能存在两条一模一样的记录）
--    实现方法：1、主键约束（主键是表中的一到多个列，主键列不能为空，也不能重复。一个表中只能有一个主键）
--              2、唯一约束（唯一约束是指给定列的所有值必须唯一，该列在表中每一个行的值必须唯一。它和主键约束的区别在于该列可以为空，并且可以在一张表中给多个列设置唯一约束）
-- 2、域完整性（保证一列数据是有效的）
--    域完整性是指定列的输入有效性
--    实现方法：1、非空约束：NOT Null
--              2、默认约束：Default
--              3、检查约束：Check（MYSQL不支持）
-- 3、引用完整性（保证引用的编号是有效的）
-- 4、用户自定义完整性（保证自定义规则）


-- 聚合函数的分类：
-- COUNT：统计行数量
-- SUM：获取单个列的合计值
-- AVG：计算某个列的平均值
-- MAX：计算列的最大值
-- MIN：计算列的最小值


-- HAVING和WHERE区别
-- 二者都是过滤条件，WHERE运行在分组前，因此不能执行任何聚合函数。HAVING是运行在分组后，只能用做聚合函数的过滤。


-- 创建数据库(--表示注释)
CREATE DATABASE mydb;


-- 使用数据库
USE mydb;


-- 删除数据库
DROP DATABASE mydb;




-- 创建表
CREATE TABLE t_student(
  -- PRIMARY KEY表示id列为主键列。主键特点：不能为null、不能重复
  -- AUTO_INCREMENT自动增长，表示由DBMS来自动分配主键id的值，
  -- 保证不为null，也不重复，从而满足主键的要求
  id INT PRIMARY KEY AUTO_INCREMENT,
  studentName VARCHAR(20),  -- 姓名
  sex VARCHAR(10), -- 性别
  height DECIMAL(10,2), -- 身高
  birthday DATE    -- 生日
)CHARSET = utf8;   -- CHARSET = utf8表示设置表的编码集


-- 删除表
DROP TABLE t_student;


-- 查询表中的所有记录   *表示查询表中所有列
SELECT * FROM t_student;


-- 查询表中所有记录，但只显示id、studentName两个列的值
SELECT id,studentName FROM t_student;


-- 添加
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('陈希','男',1.75,'1989-09-10');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('闻西','女',1.65,'1990-01-01');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('邓鹏','男',1.70,'1992-02-06');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('小邓鹏','男',1.69,'1992-03-06');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('邓鹏鹏','男',1.68,'1992-04-06');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('张基石','男',1.72,'1991-06-18');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('田野','男',1.78,'1992-07-05');
INSERT INTO t_student(studentName,sex,height,birthday) VALUES ('平西王','男',2.05,'1982-09-20');
INSERT INTO t_student(studentName,sex,height) VALUES ('刘浩','男',1.77);


-- 删除id为4的记录
DELETE FROM t_student WHERE id>0 AND id<6;
DELETE FROM t_student WHERE id=1;
DELETE FROM t_student WHERE studentName='平西王'; -- 删除姓名为平西王的记录


-- 修改
UPDATE t_student SET height=1.8 WHERE studentName='邓鹏';
UPDATE t_student SET sex='女' WHERE studentName='平西王';


-- 平西王的身高增加0.5
UPDATE t_student SET height=height+0.5 WHERE studentName='平西王';


-- 查询所有男人
SELECT * FROM t_student WHERE sex='男';


-- 查询所有的90后
SELECT * FROM t_student WHERE birthday>='1990-01-01' AND birthday<'2000-01-01';


-- 查询1.7以上的男人
SELECT * FROM t_student WHERE height>1.7 AND sex='男';


-- 查询所有含邓的姓名 (%表示多个任意字符, _表示一个任意字符)
SELECT * FROM t_student WHERE studentName LIKE '%邓%';


-- 查询所有含邓的只有两个字的姓名 
SELECT * FROM t_student WHERE studentName LIKE '邓_';


-- 查询所有姓邓的男人
SELECT * FROM t_student WHERE studentName LIKE '邓%' AND sex='男';


-- 给表添加别名(t_student s)
UPDATE t_student s SET s.height=s.height+5 WHERE s.studentName='平西王';


-- 同时改多个类的值,中间以 , 隔开
UPDATE t_student s SET s.height=s.height+5,s.sex='女' WHERE s.studentName='平西王';


-- 查询所有过期商品 date_add(date,int)在指定时间上增加或减少相应的值 （这是示例格式，不能运行测试）
SELECT * FROM t_good g WHERE DATE_ADD(g.createDate, INTERVAL g.keepDate DAY) < NOW();


-- 查询当前时间
SELECT NOW() FROM t_student; 


-- 插入多行记录（仅限于在MYSQL数据库使用）
INSERT INTO t_student VALUES ('小田野','男',1.88,'1992-06-05'),('田野君','男',1.70,'1994-07-05'),('小野君','男',1.68,'1991-07-011');


-- 删除所有数据
-- DELETE与TRUNCATE的区别：DELETE会记录日志，意味着删除后的数据还可以恢复，但是效率低。
-- TRUNCATE不会记录日志，删除后的数据不能恢复，但是效率高。TRUNCATE不能用于有外键约束引用的表。
TRUNCATE TABLE t_student


-- 查询所有的类别
-- DISTINCT去掉重复记录
SELECT DISTINCT sex FROM t_student;


-- LIMIT 0,3 （从第一条记录开始显示3条记录）
SELECT * FROM t_student LIMIT 0,3;


-- BETWEEN是查询身高在1.65到1.75之间的学生，包括1.65和1.75
SELECT * FROM t_student WHERE height BETWEEN 1.65 AND 1.75;


-- 查询id在（6,8,10）范围内的学生信息
SELECT * FROM t_student WHERE id IN(6,8,10);


-- 查询没有生日信息的学生
SELECT * FROM t_student WHERE birthday IS NULL;


-- 查询有有生日信息的学生
SELECT * FROM t_student WHERE birthday IS NOT NULL;


-- 按照身高进行排序，默认为ASC升序，加上desc为降序
SELECT * FROM t_student ORDER BY height;
SELECT * FROM t_student ORDER BY height DESC;


-- 添加一列
ALTER TABLE t_student ADD grade VARCHAR(20);


-- 修改列名
ALTER TABLE t_student CHANGE grade chinese VARCHAR(20); 


-- 删除列
ALTER TABLE t_student DROP COLUMN chinese;


-- UNIQUE 表示该列的值不能重复
ALTER TABLE t_student CHANGE height height VARCHAR(20) UNIQUE;
INSERT INTO t_student(height) VALUES (1.77); -- 运行该行命令会报错，因为1.77与前面的有重复


-- 自增长序列起始序号为10000
ALTER TABLE t_student AUTO_INCREMENT=10000;








CREATE TABLE t_woman(
  id INT PRIMARY KEY AUTO_INCREMENT,
  womamName VARCHAR(20) NOT NULL, -- not null 表示该列必须有值，不能为NULL
  womanStatus VARCHAR(20) DEFAULT '群众' -- default默认值。如果插入该列时，没有给该列赋值，则该列值为默认值
)CHARSET=utf8;


INSERT INTO t_woman(womamName,womanStatus) VALUES ('赵一曼','党员');
INSERT INTO t_woman(womamName) VALUES('李四');
INSERT INTO t_woman(womanStatus) VALUES('团员'); -- 运行后因为womamName不能为空值，所以womamName的值为空白的


SELECT * FROM t_woman;










CREATE TABLE t_class(
  id INT PRIMARY KEY AUTO_INCREMENT,
  className VARCHAR(20),
  createTime DATE 
)CHARSET=utf8;


INSERT INTO t_class(className,createTime) VALUES ('J114','2015-07-16');
INSERT INTO t_class(className,createTime) VALUES ('J115','2015-08-12');


SELECT * FROM t_class;


CREATE TABLE t_xuesheng(
  id INT PRIMARY KEY AUTO_INCREMENT,
  xueshengName VARCHAR(20),
  classId INT 
)CHARSET=utf8;


INSERT INTO t_xuesheng(xueshengName,classId) VALUES('康鹏',1);
INSERT INTO t_xuesheng(xueshengName,classId) VALUES('王建军',1);


INSERT INTO t_xuesheng(xueshengName,classId) VALUES('张三',2);
INSERT INTO t_xuesheng(xueshengName,classId) VALUES('李四',2);


SELECT * FROM t_xuesheng;


-- 引用完整性（外键约束），强制要求从表的外键必须引用主表中出现过的主键值
ALTER TABLE t_xuesheng ADD CONSTRAINT fk_1 FOREIGN KEY (classId) REFERENCES t_class(id);










CREATE TABLE t_studentText(
  id INT PRIMARY KEY AUTO_INCREMENT,
  studentTextName VARCHAR(20),
  grade INT
)CHARSET=utf8;


INSERT INTO t_studentText(studentTextName,grade) VALUES('张三',90),('李四',83),('王五',40),('赵六',50);
INSERT INTO t_studentText(studentTextName) VALUES('田七');


-- 统计学生总数 count(*)表示统计所有记录。count(列名)表示只统计非空列
SELECT COUNT(*) FROM t_studentText;
SELECT COUNT(grade) FROM t_studentText;


-- 统计总分
SELECT SUM(grade) FROM t_studentText;


-- 统计平均分
SELECT AVG(grade) FROM t_studentText;


-- 显示该班的最高分、最低分
SELECT MAX(grade),MIN(grade) FROM t_studentText;


-- 显示最高分是谁
-- 在where条件中不能使用聚合函数
SELECT * FROM t_studentText ORDER BY grade DESC LIMIT 0,1;


SELECT * FROM t_studentText;








CREATE TABLE t_employee(
  id INT PRIMARY KEY AUTO_INCREMENT,
  employeeName VARCHAR(20),
  money INT,
  deptName VARCHAR(20)
)CHARSET=utf8;


INSERT INTO t_employee(employeeName,money,deptName) VALUES ('张三',3000,'销售部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('李四',8000,'行政部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('张翠山',2000,'销售部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('李渊',4000,'行政部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('李世民',5000,'市场部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('张何',3500,'销售部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('王语嫣',2300,'财务部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES ('刘文彩',4000,'企划部');


-- 统计每个部门有多少人
SELECT deptName,COUNT(*) FROM t_employee GROUP BY deptName;


-- 在分组查询中，查询字段只能是聚合函数和分组函数
SELECT * FROM t_employee GROUP BY deptName;


-- 统计每个部门的最高工资是多少
SELECT deptName,MAX(money) FROM t_employee GROUP BY deptName;


-- 统计每个部门工资在5000以下的人数
SELECT deptName,COUNT(money) FROM t_employee WHERE money<5000 GROUP BY deptName;


-- 统计各部门的平均工资
SELECT deptName,AVG(money) FROM t_employee GROUP BY deptName;


-- 统计平均工资在3000以上的部门
-- having是对分组的结果再进行过滤，所以having条件中可以加入聚合函数
SELECT deptName,AVG(money) FROM t_employee GROUP BY deptName HAVING AVG(money)>3000;


-- 统计每个部门中工资在3000以上的部门人数
SELECT deptName,COUNT(*) FROM t_employee WHERE money>3000 GROUP BY deptName;


-- 查询部门人数至少是2个的部门
SELECT deptName,COUNT(*) FROM t_employee GROUP BY deptName HAVING COUNT(*)>=2;


-- 按部门平均工资降序显示部门
SELECT deptName,AVG(money) FROM t_employee GROUP BY deptName ORDER BY AVG(money) DESC;


SELECT * FROM t_employee;