数据库（三）

DML (Data Manipulation Language)

• 数据操作语言, 用来操作数据库表中的记录

• 用来操作表中的数据. 对表中记录的创建, 修改和删除

一.插入数据

1,插入单条数据

语法：insert into 表名称（column1，column2，...）values(value1，value1，...);

代码：insert into student（id, name）values(1,'tom');

2.在有参数都提供, 可以忽略列名

2.1插入单条数据

insert into student values（2，‘coco’）;

2.2插入多条数据

insert into student values（2，‘coco’）, (3,'bobo') , (4,'vivi');

二.数据修改

1.单条数据修改

语法：update 表名称 set 列名1=列值， 列名2=列值2 where 列名=列值

代码：update student set name=‘tom’ , age=12 where id=1;

2.批量数据修改

2.1：将学生所有的age改为18

update student set age =18；

2.2:将学生所有的age+1

update student set  age=age +1；

三.数据删除

1,删除一列

语法：delete from 表名称 where 列名=列值；

代码：delete from student where id=1;

2.删除表中所有的记录

delete from student；

---

DQL (Data Query Language)

• 数据查询语言, 用来查询数据库表中的记录

• 执行DQL语句不会改变数据库原始数据, 而是让数据库发送结果集给客户端查询返回的结果集是一张虚拟表

一.查询关键字

关键字：select

二.基础语法

SELECT 要查询的列 FROM 表名

WHERE 行条件

GROUP BY 对结果分组

HAVING 分组后的行条件

ORDER BY 对结果排序

LIMIT 结果限定

三.常用操作

1 基础查询

查询students表中所有数据：SELECT  *  FROM  students;

查询指定列：SELECT  id ,  gender  FROM  students ;

---

 

2 条件查询

概述：在查询时通过 WHERE 子句给出查询条件, WHERE子句可以使用到的运算符：

关系运算:

=, !=, <>, <, <=, >, >=  (<>和!=用法相同）。

BETWEEN 20 AND 40      20<=值<=40。

IN('a', 'b', 'c')       值包含在集合中。

NULL判断：IS NULL, IS NOT NULL 判断是否为NULL

逻辑运算：AND, OR, NOT 与, 或, 非

案例1：查询年龄在15岁至20岁之间的学生：

SELECT * FROM student WHERE age BETWEEN 15 AND 20;

案列2：查询年龄不等于3和年龄不等于11的学生(and,或not in分别实现)

select * from student where age!=3 and age !=11;
select * from student where age not in(3,11);

案列3：查询年龄大于10的学生

select * from student where age>10;

 

---

 

3 模糊查询

关键字：like

 

使用的通配符： _ 匹配任意一个字符     _b

                         % 匹配任意0~n个字符

 

案例1：查询名字中包含字母A的学生: SELECT * FROM students WHERE  name  LIKE  '%A%' ;

案列2：查询名字字母B开头的学生: SELECT * FROM students WHERE  name  LIKE  'B%' ;

案列3：查询名字为Cxxxx的学生: SELECT * FROM students WHERE  name  LIKE  'C_%' ;

---

 

4 字段控制

关键字： DISTINCT(去重), AS(别名)   函数： IFNULL()

 

案例：

1、查询雇员薪资, 使用DISTINCT去除重复记录

SELECT DISTINCT salary FROM employee;

 

2、查询雇员薪资和奖金之和

  SELECT *, salary+bonus FROM employee;

 

3、bonus为NULL时, 任何值和NULL相加还是NULL，需要使用 IFNULL() 函数将NULL转换成数值0

  SELECT *, salary+IFNULL(bonus, 0) FROM employee;

 

4、此时列名出现了 salary+IFNULL(bonus, 0), 很不美观，可以用 AS 关键字取一个别名

  SELECT *, salary+IFNULL(bonus, 0) AS total FROM employee;

---

5 排序和聚合

1、 排序：使用关键字可以给查询结果排序, 有两种排序方式

关键字：ORDER BY   

结果：asc  升序(默认)  从小到大

          desc 降序   从大到小

案例：查询所有学生, 按年龄降序排列：SELECT * FROM students ORDER BY age DESC;

2、聚合

概述，聚合函数用来做纵向运算,

分类：

计数 — COUNT

最大值 — MAX

最小值 — MIN

求和 — SUM

求平均值 — AVG

 

案例：

1、查询工资+奖金大于2500的员工人数：

SELECT COUNT(*) FROM employee WHERE  salary+IFNULL(bonus, 0) > 2500;

 

2、查询员工最高工资和最低工资

SELECT MAX(salary), MIN(salary) FROM employee;

 

3、 查询员工总工资和平均工资

SELECT SUM(salary), AVG(salary) FROM employee;

---

6 分组查询

概述：使用 GROUP BY 子句做分组查询, 分组查询一般和聚合函数一起使用

案例：

1.将查询结果根据年龄分组

Select age from students group by age;

 2.或者指定分组条件的位置

Select age from students group by 1;

Select id,age from students group by 2;

3.查询所有年龄大于15 的人的总数

Select age count(*) from students where age>15 group by age;

 

---

7HAVING子句

概述：HAVING子句也是用来过滤查询条件的

案例：查询总工资大于9000的所有部门, 部门总工资, 和部门总员工数

SELECT department, SUM(salary), COUNT(*)

FROM employee

GROUP BY department

HAVING SUM(salary)>9000;

注意 having后面可以添加聚合函数或者属性字段 如果添加字段，name字段必须在语句中出现

WHERE和HAVING的区别：

1.WHERE是在分组（GROUP BY）前过滤数据; HAVING是在分组（ GROUP BY）后过滤数据

2.WHERE子句不可以使用聚合函数; HAVING子句可以使用聚合函数

3.WHERE条件查询，针对于本地文件，不可以查询结果集；HAVING条件查询可以查询结果集

 

---

 

8分页查询

概述：使用 LIMIT 关键字可以用来限定查询结果的起始行和总行数

案例1：从第一行开始, 查询5条记录

SELECT * FROM employee LIMIT 0, 5;(参数一：从几行开始；参数二：查询几条数据)

分页查询: 可以使用LIMIT实现分页功能, 例如一页10条记录, 则有

SELECT * FROM employee LIMIT 0, 10; -- 第一页数据

SELECT * FROM employee LIMIT 10, 10; -- 第二页数据

SELECT * FROM employee LIMIT 20, 10; -- 第三页数据

案列2：查询前5条记录

SELECT * FROM employee LIMIT 0, 5;（LIMIT n 等价于 LIMIT 0,n）

注意: LIMIT不属于标准的SQL语法, MySQL支持, 其它DBMS不一定支持.

 书写顺序

where ，group by , having , order by, limit

 

union查询

 union查询：就是把多条sql语句的查询结果合并成结果集；

注意：

1.如果 两张表查询出来的数据，有完全一样的，那么union会默认的把他合并成一条数据；可以用union all 解决

2.使用union查询的时候，两个sql的列数必须一致，但是列名可以不一样，如果列名不一样，以第一条sql为准

3.在union字句中不需要用order by排序，可直接在最后进行排序

 子查询

子查询：将一条sql语句的结果，作为另一个sql语句的查询条件 或者查询中间表

案列1：查询出最新一行商品(以商品编号最大为最新,用子查询实现)

           select * from goods where goods_id =(select max(goods_id) from goods);

案列2：查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)

           select cat_name from category  where cat_id=(select cat_id from goods where goods_id =19);

案列3：用where型子查询把goods表中的每个栏目下面最新的商品取出来

           select *from goods where goods_id in(select max(goods_id) from goods group by cat_id);

案列4：用from型子查询把goods表中的每个栏目下面最新的商品取出来（用from型子查询必须取别名）

           select *from (select * from goods ORDER BY goods_id DESC) as temp GROUP BY cat_id;