Mysql学习——数据库操作语言(DML) (2)

HAVING和WHERE的区别
HAVING是在分组后对数据进行过滤;
WHERE是在分组前对数据进行过滤;
HAVING后面可以使用分组函数(统计函数);
WHERE后面不可以使用分组函数;
WHERE是对分组前记录的条件,如果某行记录没有满足where子语句的条件,那么这行记录不参与分组;而HAVING是对分组后的数据约束

工资总和

SELECT department,GROUP_CONCAT(salary),SUM(salary)
FROM employee
WHERE salary > 2000
GROUP BY department
HAVING SUM(salary)>9000;
ORDER BY SUM(salary) DESC;

书写顺序
SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT

执行顺序
FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT

分页 LIMIT
从哪一行开始查,总共要查几行
LIMIT 参数1, 参数2
参数1:从哪一行开始查
参数2:一共要查几行
角标是从0开始
格式
SELECT * FROM 表名 LIMIT 0,5;
分页思路
curPage = 1 # 当前页
pageSize = 3 # 每页显示多少条
当前页为1 第一页从0开始 (1-1)3 = 0
当前页为2 第二页从3开始 (2-1)3 = 3
当前页为3 第三页从6开始 (3-1)3 = 6
当前页为4 第四页从9开始 (4-1)3 = 9

SELECT * FROM employee
LIMIT (curPage-1)*pageSize,pageSize;

查询student表的第2条到第4条记录
(其中1表示从第二条记录开始查询,3表示查询出3条记录)

SELECT * FROM student LIMIT 1,3;

约束类型
主键约束(primary key)
唯一约束(unique)
自动增长(auto_increment)

添加主键约束 PRIMARY KEY
CREATE TABLE　表名（字段名１　数据类型　primary key,字段名２　数据类型);

CREATE TABLE 表名(字段1 数据类型,字段名2 数据类型,primary key(要设置主键的字段));

先创建表再添加主键

ALTER TABLE student ADD CONSTRAINT primary key(sid);

唯一约束UNIQUE
CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)

自动增长列　auto_increment
CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);

域完整性
数据类型
数值类型
日期类型
字符串类型
非空约束(NOT NULL)
CREATE TABLE stu(
id　int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default ‘男’
)

默认值约束(DEFAULT)
CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE NOT NULL,
字段3 数据类型 DEFAULT ‘默认值’);

外键
对已有表添加外键关系

ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);

删除外键关系的语法:

ALTER + TABLE + 外键所在表的表名 + DROP + FOREING KEY + 外键名）

数据库设计三范式
设计数据库表的时候所依据的规范,共三个规范:
第一范式: 要求每一个字段原子性不可在分
第二范式: 要求所有非主键字段完全依赖主键,不能产生部分依赖
第三范式: 所有非主键字段和主键字段之间不能产生传递依赖

修改表内容
将name字段的数据类型改为VARCHAR(30)，且保留非空约束。
参考:
ALTER TABLE animal CHANGE name VARCHAR(30);

将behavior 字段的位置改到legs字段的前面。
参考:
ALTER TABLE animal MODIFY legs INT(4) AFTER behavior;

将kinds字段改名为category。
参考:
ALTER TABLE animal CHANGE kinds category VARCHAR(8) NOT NULL;

在表中增加fur字段，数据类型为VARCHAR(10)
ALTER TABLE animal ADD fur VARCHAR(10)

删除legs字段
ALTER TABLE animal DROP legs;

将animal表的储存引擎更改为MyISAM类型
ALTER TABLE animal engine = MyISAM;

将animal表更名为animalInfo
RENAME TABLE animal TO animalInfo;

合并结果集UNION与UNION_ALL
合并结果集就是把两个SELECT语句的查询结果合并到一起展示

UNION 合并时去除重复记录
SELECT * FROM 表1 UNION SELECT * FROM 表2;

UNION ALL合并时不去除重复记录
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

多表联查,如何保证数据正确(解决笛卡尔集现象)
在查询时要把主键和外键保持一致
要保证我们当时建表的时候,关系的那个键保持一致

SELECT *
FROM students,score
WHERE students.id = score.sid

多表查询_内连接之等值连接 INNER JOIN
内连接分为:等值连接,非等值连接

内连接写法

SELECT * FROM students st INNER JOIN score sc ON st.id = sc.sid

多表联查后还有条件

SELECT st.id,st.name,sc.score FROM students st INNER JOIN score sc ON st.id = sc.sid WHERE sc.score >60 AND st.age>10;

多表查询_左外连接(LEFT_JOIN)
两表满足条件相同的数据查出来,如果左边表当中有不相同的数据,也把左边表当中的数据查出来.(换句话说就是左边表数据全部查询出来,右边表只查询满足条件的)

SELECT *
FROM students st
LEFT OUTER JOIN score sc
ON st.id = sc.sid;

多表查询_右外连接(RIGHT_JOIN)
使用右连接,就把右边表当中的数据全部查出,左边查出满足条件的

SELECT *
FROM students st
RIGHT OUTER JOIN score sc
ON st.id = sc.sid;

多表查询_多表联合查询
多表连接属于内链接

SELECT *
FROM students st
JOIN score sc
ON st.id = sc.sid
JOIN course co
ON sc.cid = co.id;

多表查询_非等值连接实现
SELECT e.ename,e.salary,d.dname
FROM emp e,dept d,salgrade g
WHERE e.deptno = d.deptno
AND e.salary >= g.lowSalary
AND e.salary <= g.highSalary;

SELECT e.ename,e.salary,d.dname
FROM emp e,dept d,salgrade g
WHERE e.deptno = d.deptno
BETWEEN g.lowSalary AND g.highSalary;

SELECT * FROM emp e
JOIN dept d ON e.deptno = d.deptno

JOIN salgrade g ON e.salary BETWEEN g.lowSalary AND g.highSalary;

子查询
什么是子查询
一个select语句中包含另外一个完整的select语句
或者说两个以上select,那么就是子查询语句了
子查询出现的位置
where后,把select查询出的结果当做另外一个select的条件值
from后,把查询出的结果当作一个新表
从emp表里查询名字和部门和项羽是一个部门的人

SELECT ename,deptno FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘项羽’);

查询３０号以内大于２０００薪水的人

SELECT ename FROM (SELECT ename,salary,deptno FROM emp WHERE deptno=30) AS s
WHERE s.salary > 2000;

查询工资高于程咬金的员工

SELECT ename,salary FROM emp
WHERE salary > (SELECT salary FROM emp WHERE ename = ‘程咬金’)