### DQL查询语句
##### *ORDER BY(排序):
降序:desc 表名;
升序(默认):ASC 表名;
##### *聚合函数
count:计算总记录数,一般选择非空列:主键列
max:计算最大值
min:计算最小值
avg:计算平均值
sum:求和
用法: SELECT 聚合函数(列名) FROM 表名;
##### *GROUP BY(分组)
**group by 分组列 having 过滤条件 ;**
**WHERE 过滤条件 group by 分组列;**
where 与having的区别:
where放在分组之前,对原表的数据过滤,不能使用聚合函数.
having放在分组之后,对分组后的数据过滤,可以使用聚合函数.
-- 按照性别分组,并统计人数个数
SELECT sex, count(*) FROM student3 GROUP BY sex;
##### *LIMIT(分页查询)
SELECT * FROM 表名 LIMIT 起始索引值,每页显示条数;
公式:起始索引值=(当前页数-1)*每页条数(页数要在两页以上)
##### *备份数据库
mysqldump -uroot -proot db1 > d:/day01.sql (这是在数据库未登陆状态下DOS窗口操作)
##### *还原数据库
create database db1;(创建数据库)
USE db3; (使用数据库)
source d:/day01.sql;
### 数据表的约束
##### 非空约束-not null(常用)
1.创建表时添加非空约束
CREATE TABLE st5(id INT not null, NAME VARCHAR(20), age INT)
2.创建表完后,添加非空约束
alter table 表名 modify 字段名 字段类型 not null;
3.删除非空约束
alter table 表 modify 字段名 字段类型;
##### 唯一约束-unipue(常用)
注意:唯一约束,字段数据不能重复,不能为两个null;
1.创建表时添加唯一约束
CREATE TABLE st5(id INT , phone VARCHAR(20) unipue, age INT)
2.创建表完后,添加唯一约束
alert table 表名 modify 字段名 字段类型 unique;
3.删除唯一约束
alter table 表名 drop index 字段名;
##### 主键约束-PRIMARY KEY(常用)
注意:非空且唯一,只有一个字段为主键,一般是id.
1.创建表时添加主键约束
CREATE TABLE 表名( id INT PRIMARY KEY, NAME VARCHAR(20), age INT)
2.创建表完后,添加主键约束
- [ ] alter table 表名 modify 字段名 字段类型 PRIMARY KEY;
- [ ] alter table 表名 add PRIMARY KEY(id);
3.删除主键约束
alter table 表名 drop PRIMARY KEY;
##### 自动增长-AUTO_INCREMENT
注意:自动增长一般和主键一起使用,自动增长只对int类型数据有用.
1.创建表时主键添加自动增长
CREATE TABLE 表名( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT)
2.添加自动增长
alter table 表名 modify 字段名 字段类型 AUTO_INCREMENT;
3.删除主键自动增长
alter table 表名 modify 主键字段 字段类型;
##### 外键约束-foreign key
注意:外键就是从表何和主表主键对应的那一列.
1.在创建表的时候,可以添加约束
create table 表名(
...
外键列
constraint (外键名称) foreign key (外键列名称) references 主表名(主键字段名)
)
2.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3.创建表之后,添加外键
ALTER TABLE 表名 ADD constraint 外键名称 foreign key (外键列名称) references 主表名(主键字段名)
##### 级联操作
级联更新:ON UPDATE CASCADE
级联删除:ON DELETE CASCADE
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表键) ON UPDATE CASCADE ON DELETE CASCADE;
### 数据表关系
##### 多表之间的关系(重点)
**一对一:**人和身份证----可以在任意一方添加外键指向另一方主键.(一般情况下合成一张表)
**一对多(多对一):**员工和部门----在多的一方建立外键,指向一的一方的主键
**多对多:**学生选课程----需要创建第三张中间表,至少包含两个列,这两个字段作为第三张表的外键,分别指向两张表的主键
### 数据表的范式
注意:共有6类范式,常用前三种.
作用:消除冗余数据.
| 范式 | 特点 |
| ---- | ------------------------------------------------------------ |
| 1NF | 原子性：表中每列不可再拆分。 |
| 2NF | 不产生局部依赖，一张表只描述一件事情 |
| 3NF | 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。 |
### ======================================================
### 数据表约束案例:
```
-- 创建(主表)部门表(id,dep_name,dep_location)
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
-- 查询部门表
SELECT * FROM department;
-- 2) 创建从表 employee 并添加外键约束 emp_depid_fk
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, -- 外键对应主表的主键
CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id) -- 创建外键约束
)
-- 3) 添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
-- 查询员工表
SELECT * FROM employee;
```
### 数据表关系案例:
```
-- 部门表
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14), -- 部门名称
LOC VARCHAR(13)-- 部门地址
);
-- 为部门表添加数据
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
-- 员工表
CREATE TABLE EMP(
EMPNO INT PRIMARY KEY, -- 员工编号
ENAME VARCHAR(10), -- 员工姓名
JOB VARCHAR(9), -- 员工工作
MGR INT, -- 员工直属领导编号
HIREDATE DATE, -- 入职时间
SAL DOUBLE, -- 工资
COMM DOUBLE, -- 奖金
DEPTNO INT -- 所在部门
);
-- 为员工表添加数据
INSERT INTO EMP VALUES(7369,'SMITH','职员',7566,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','销售员',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','销售员',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','经理',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','销售员',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','经理',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','经理',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','职员',7566,'1987-07-03',3000,2000,20);
INSERT INTO EMP VALUES(7839,'KING','董事长',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNERS','销售员',7698,'1981-09-08',1500,50,30);
INSERT INTO EMP VALUES(7876,'ADAMS','职员',7566,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','职员',7698,'1981-12-03',1250,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','销售员',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','职员',7782,'1981-01-23',1300,NULL,10);
-- 工资等级表
CREATE TABLE SALGRADE(
GRADE INT,-- 等级
LOSAL DOUBLE, -- 最低工资
HISAL DOUBLE -- 最高工资
);
-- 为工资表添加数据
INSERT INTO SALGRADE VALUES (1,500,1000);
INSERT INTO SALGRADE VALUES (2,1001,1500);
INSERT INTO SALGRADE VALUES (3,1501,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
-- 查询员工表
SELECT * FROM emp;
-- 查询工资表
SELECT * FROM SALGRADE;
-- 1、查找部门30中员工的详细信息。
SELECT * FROM emp WHERE DEPTNO=30;
-- 2、找出从事职员工作的员工的编号、姓名、部门号。
SELECT EMPNO,ENAME,DEPTNO,job FROM emp WHERE job='职员';
-- 3、检索出奖金多于基本工资的员工信息。
SELECT * FROM emp WHERE COMM>SAL;
-- 4、检索出奖金多于基本工资60%的员工信息。
SELECT * FROM emp WHERE COMM>(SAL*0.6);
-- 5、找出姓名中包含A的员工信息.
SELECT * FROM emp WHERE ENAME LIKE '%A%';
-- 6、找出姓名以A、B、S开始的员工信息。
SELECT * FROM emp WHERE ENAME LIKE 'A%' OR 'B%' OR 'S%';
-- 7、找到名字长度为7个字符的员工信息。
SELECT * FROM emp WHERE ENAME LIKE '_______';
-- 8、名字中不包含R字符的员工信息。
SELECT * FROM emp WHERE ENAME NOT LIKE 'R%';
-- 9、返回员工的详细信息并按姓名升序排序。
SELECT * FROM emp ORDER BY ENAME ASC;
-- 10、返回员工的信息并按姓名降序,工资升序排列。
SELECT * FROM emp ORDER BY ENAME DESC,SAL ASC;
-- 11、计算员工的日薪(按30天)。
SELECT *,round((sal/30),2) AS 日薪 FROM emp; -- round(计算值,取值小数位)
SELECT *,round(((sal+ IFNULL(comm,0)) /30),2) AS 日薪 FROM emp;
-- 12、找出获得奖金的员工的信息。
SELECT * FROM emp WHERE comm IS NOT NULL;
-- 13、找出奖金少于100或者没有获得奖金的员工的信息。
SELECT * FROM emp WHERE comm IS NULL OR comm<100;
-- 14、找出10部门的经理、20部门的职员 的员工信息。
SELECT * FROM emp WHERE DEPTNO=10 AND job='经理' OR DEPTNO=20 AND job='职员';
-- 查询部门表
SELECT *FROM dept;
```
### DQL查询案例:
##### *条件查询
-- 查询年龄小于20的人
SELECT * FROM student3 WHERE age<20;
-- 查询年龄不等于23的人
SELECT * FROM student3 WHERE age <> 23;
SELECT * FROM student3 WHERE age !=23;
-- 查询年龄在20到30岁之间的人
SELECT * FROM student3 WHERE age BETWEEN 20 AND 30;
SELECT * FROM student3 WHERE age>=20 AND age<=30;
-- 查询年龄是 22和23岁的人
SELECT * FROM student3 WHERE age IN(22,23);
SELECT * FROM student3 WHERE age=22 OR age=23;
-- 查询英语成绩为空的数据
SELECT * FROM student3 WHERE english IS NULL ;
-- 查询英语成绩不为空的数据
SELECT * FROM student3 WHERE english IS NOT NULL ;
##### *模糊查询
-- 查询姓马的人
SELECT * FROM student3 WHERE NAME LIKE '马%';
-- 查询三个字的姓名的人(此处有三个下划线)
SELECT * FROM student3 WHERE NAME LIKE '___';
-- 查询名字中有德字的人(模糊查询)
SELECT * FROM student3 WHERE NAME LIKE '%德%';
##### *排序
-- 按照年龄排序
SELECT * FROM student3 ORDER BY age ASC;
-- 按照年龄排序,如果年龄相同,就按照数学成绩排序
SELECT * FROM student3 ORDER BY age ASC,math DESC;
##### *去重
SELECT DISTINCT address FROM student3;
##### *聚合函数
-- 计算总数据
SELECT COUNT(*) FROM student3;
SELECT COUNT(1) FROM student3; -- 效率高
-- 求最大值
SELECT MAX(math) FROM student3;
-- 求最小值
SELECT MIN(math) FROM student3;
-- 求平均分
SELECT AVG(english) FROM student3;
-- 求和
SELECT SUM(english) FROM student3;
##### *分组查询
-- 按照性别分组,并统计人数个数
SELECT sex, COUNT(*) FROM student3 GROUP BY sex;
-- 按性别分组,分别计算男女同学的平均分和人数
SELECT sex,AVG(math),COUNT(id) FROM student3 GROUP BY sex;
-- 按性别分组,分别计算男女同学的平均分和人数,分数低于80分的人不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student3 WHERE math >80 GROUP BY sex;
-- 按性别分组,分别计算男女同学的平均分和人数,分数低于80分的人不参与分组,分组之后,要求人数大于2个人
SELECT sex,AVG(math),COUNT(id) FROM student3 WHERE math >80 GROUP BY sex HAVING COUNT(id) > 2;
-- 按性别分组,分别计算男女同学的平均分和人数,分数低于80分的人不参与分组,分组之后,要求人数大于2个人
SELECT sex,AVG(math),COUNT(id) AS 人数 FROM student3 WHERE math >80 GROUP BY sex HAVING 人数 >2;
##### *分页查询
-- 从第3条开始显示,显示6条数据
SELECT * FROM student3 LIMIT 2,6;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
