SQL数据库的增删改查 mysql

【SQL数据库的增删改查】
数据库

前言
一、建库/删库
二、建表/删表
- 1.添加
- 2.删除
- 3.修改
- 4.查询
二、MYSQL函数
- 1.时间
- 2.聚合函数
- 3.联表查询

前言数据库即数据的仓库，在数据中提供了专门的管理系统，对数据库中的数据进行集中的控制和管理。能高效的对数据进行存储，检索降低存储数据的冗余度更高的数据一致性存储数据共享实现数据的安全性便于维护数据的完整性建立数据库所遵循的标准一、建库/删库

创建数据库
CREATE DATABASE db;
删除数据库
DROP DATABASE db;
默认编码集为Latin，如果需要插入中文需要编码集改成utf-8

二、建表/删表

建表
CREATE TABLE t_student(
– s_id为列名 int表示该列数据类型
– primary key标识该列为主键列
– auto_increment 表示该列为自动增长列，该列的值由DBMS维护
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(20), – 姓名
s_class VARBINARY(20), – 所在班级
s_birthday DATE, – 生日
s_grade INT, – 成绩
s_sex ENUM(‘男’,‘女’) – 性别
);
删除数据库表
DROP TABLE t_student;

1.添加

添加表记录
INSERT INTO t_student(s_name,s_class,s_birthday,s_grade,s_sex)
VALUES(‘张三’,‘j179’,‘1998-09-20’,90,‘男’);
如何添加多条记录
INSERT INTO t_student(s_name,s_class,s_birthday,s_grade,s_sex)
VALUES(‘张无忌’,‘j171’,‘2020-09-22’,78,‘女’),
(‘张青峰’,‘j170’,‘2020-09-23’,67,‘女’);

2.删除

删除编号为2，可以删除表，但是会保留日志，删除数据可以恢复，从删除后的id开始增加，效率较低
DELETE FROM t_student WHERE s_id=2;
删除表,会把日志里的都删除了，删除数据不可恢复，从头开始，效率较高
TRUNCATE TABLE t_student;
删除有重复的数据
DELETE FROM t_employee WHERE pk_id NOT IN
(SELECT * FROM(SELECT MIN(pk_id) FROM t_employee GROUP BY e_name,e_dept,e_money) X);

3.修改

修改数据
UPDATE t_student SET s_class=‘j111’,s_grade=92,s_birthday=‘2020-09-09’ WHERE s_name=‘张青峰’;
修改数据二,将j179班 ,成绩加2分
UPDATE t_student SET s_grade=s_grade+2 WHERE s_class=‘j179’;

4.查询

查询个别记录
SELECT s_name FROM t_student;
查询所有的信息
SELECT * FROM t_student;
去重
– 查询现在有几个班(去重)
distinct表示去除重复记录
SELECT DISTINCT s_class FROM t_student;
WHERE
查询所有j179的学生姓名和生日
SELECT s_name,s_birthday FROM t_student WHERE s_class=‘j179’;
LIMIT
返回限定行，显示前三个学生信息 0表示从0开始显示，3表示显示3条，一般在分页用
SELECT * FROM t_student LIMIT 0,3;
between and
包含80和90
SELECT * FROM t_student WHERE s_grade BETWEEN 80 AND 90;
AND / OR
查询所有成绩是良好80-90的学生
SELECT * FROM t_student WHERE s_grade<=90 AND s_grade >=80;
查询90后的学生
SELECT * FROM t_student WHERE s_birthday>=‘1990-01-01’ AND s_birthday<=‘2000-01-01’;
查询j172和j179的学生
SELECT * FROM t_student WHERE s_class=‘j172’ OR s_class=‘j179’;
CONCAT
查询时连接更改列表明as 从s_grade 改为grade
SELECT s_name ,CONCAT(‘共’,s_grade,‘分’)AS grade FROM t_student;
IN / NOT IN
查询值在/不在指定集合的记录
查询J178和j172的学生
SELECT * FROM t_student WHERE s_class IN(‘j172’,‘j178’);
查询不属于J172和j179的学生
SELECT * FROM t_student WHERE s_class NOT IN(‘j172’,‘j178’);
模糊查询
查询姓张的 %匹配0个到多个
SELECT * FROM t_student WHERE s_name LIKE ‘张%’;
_ 匹配单个字符
SELECT * FROM t_student WHERE s_name LIKE ‘张_’;
IS NULL
处理null值，不能用!= = ，只能用is null 或者is not null
查询缺考的学生
SELECT * FROM t_student WHERE s_grade IS NULL;
排序
ORDER BY
排序将学生按照成绩从高到低排列 asc 升序（升序可以不写） desc 降序
SELECT * FROM t_student ORDER BY s_grade DESC;
多列排序将学生按成绩从高到低排序，一样,按生日降序排序
SELECT * FROM t_student ORDER BY s_grade DESC ,s_birthday DESC;

二、MYSQL函数 1.时间

时间函数
SELECT NOW(); – 年月日时分秒
SELECT CURDATE(); – 年月日
得到指定时间年份
SELECT YEAR(指定日期);
得到指定日期后的100天
SELECT DATE_ADD(NOW(),INTERVAL 100 DAY);
算两个日期的差值
SELECT TIMESTAMPDIFF(DAY,NOW(),‘2020-10-01’);
条件判断
case when then when end
– 显示所有商品100以下低价商品，100-600中价商品，600以上高价商品
SELECT p.,(CASE WHEN p_price<100 THEN ‘低价商品’
WHEN p_price>100 AND p_price<600 THEN ‘中价商品’
ELSE ‘高价商品’ END)info FROM t_product p;
– 将所有食品类商品价格上涨2元，药品价格上涨5元，酒类上涨10元
SELECT p.,(CASE WHEN p_type=‘食品类’ THEN p_price+2
WHEN p_type=‘药品类’ THEN p_price+5
ELSE p_price+10 END)price FROM t_product p;

UPDATE t_product SET p_price=p_price+(
CASE WHEN p_type=‘食品类’ THEN 2
WHEN p_type=‘药品类’ THEN 5
ELSE 10 END
);
if
SELECT p.,(IF(p_price<100,‘低价产品’,‘高价产品’)) AS info FROM t_product p; – 给表加别名
SELECT p.,(IF(DATE_ADD(p_createDate,INTERVAL p_valid DAY) SELECT p.*,(IF(TIMESTAMPDIFF(DAY,p_createDate,NOW())>p_valid,‘过期商品’,‘未过期商品’))info FROM t_product p;
2.聚合函数

count()：统计行的数量
– 统计有多少商品 COUNT() 表示所有符合条件商品，包括null
SELECT COUNT() FROM t_product;
– 统计有多少商品有厂家 count(列名) 只统计非空列
SELECT COUNT(p_factory) FROM t_product;
– 统计有几个类别 count(distinct 列名) 去除重复列值，统计行的数量
SELECT COUNT(DISTINCT p_type) FROM t_product;
sum()：统计某个列的综合
– 统计所有食品类的总价格
SELECT SUM(p_price),AVG(p_price) FROM t_product WHERE p_type=‘食品类’;
avg()：统计某个列的平均值
– 统计所有食品类的平均价格
SELECT AVG(p_price),SUM(p_price)/COUNT(*) FROM t_product WHERE p_type=‘食品类’;
avg()：只统计非空列
max()：统计某个列的最大值
– 显示价格最高，最低商品
SELECT MAX(p_price),MIN(p_price) FROM t_product;
max（）min（）：
不统计空值null
min()：统计某个列的最小值
– 显示价格最高，最低商品
SELECT MAX(p_price),MIN(p_price) FROM t_product;
max（）min（）：不统计空值null
分组
将某个列值相同的记录分成一个组，在进行数据运算
GROUP BY
select count() from 表名 group by 分组列
– 统计每个类别的商品有多少种
SELECT p_type,COUNT() FROM t_product GROUP BY p_type;
多列分组：只有两个或多个列值完全相同的记录，才分成一个组
分组查询后，查询列只能出现分组列和聚合函数
where 和HAVING区别：
where执行分组之前，不能使用聚合函数
having执行在分组之后，是对分组的结构进行筛选，只能使用聚合函数
– 查询类别商品数量在2个以上的类别
SELECT p_type,COUNT() FROM t_product GROUP BY p_type HAVING COUNT()>2;
– 查询平均价格在1000以下
SELECT p_type,AVG(p_price)FROM t_product GROUP BY p_type HAVING AVG(p_price)<1000;
子查询
在一个查询语句中，再嵌入一个查询，称为子查询
在子查询中，如果返回单行单列，外查询可以使用<,>,<= … 比较运算符
查询比海之蓝生产日期更早的商品
SELECT * FROM t_product WHERE p_createDate<
(SELECT p_createDate FROM t_product WHERE p_name=‘海之蓝’);
查询最贵的商品
SELECT * FROM t_product WHERE p_price=
(SELECT MAX(p_price) FROM t_product);
查询海之蓝更贵的酒
SELECT * FROM t_product WHERE p_price>
(SELECT p_price FROM t_product WHERE p_name=‘海之蓝’);
如果子查询返回的是多行单列，外查询比较运算符只能使用in或者not in
显示每个部门最高工资的员工
SELECT * FROM t_employee WHERE e_money IN
(SELECT MAX(e_money) FROM t_employee GROUP BY e_dept);

SELECT * FROM t_employee e1 WHERE e_money IN
(SELECT MAX(e_money) FROM t_employee e2 WHERE e1.e_dept=e2.e_dept GROUP BY e_dept);

临时表
是嵌入在from后面的子查询，也就是将子查询的查询结果，当做是一个表来使用
注意临时表创建时必须加别名
SELECT * FROM (SELECT e_name,e_money FROM t_employee em)a;
ALL运算符
和子查询结果逐一作比较，必须全部满足时表达式的值才为真
查询比张三的所有成绩都要高学生成绩信息

select * from t_student where 分数>
ALL (select 分数 from t_student where 姓名=‘张三’)；

ANY运算符
和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真
查询比张三的某一科成绩高的其他学生

select * from t_student where 分数>
ANY (select 分数 from t_student where 姓名=‘张三’)
and 姓名！= ‘张三’；

exists
exists判断子查询是否存在数据，如果存在则表达式为真，反之为假。not exists 相反
exists 和in 功能类似，可以互换
exists与in的使用效率的问题，通常采用exists要比in效率高，因为in不走索引，但要实际情况具体使用
UNION
用于连接连个查询语句的查询结果
– 查询市场部和行政部工资最高的两个员工
(SELECT * FROM t_employee WHERE e_dept=‘市场部’ ORDER BY e_money DESC LIMIT 0,2)
UNION
(SELECT * FROM t_employee WHERE e_dept=‘行政部’ ORDER BY e_money DESC LIMIT 0,2);
分类
相关子查询
子查询的执行需要依赖于外查询，外查询执行一次，子查询就要执行一次，效率低
非相关子查询
子查询可以单独执行，子查询不需要依赖与外查询，子查询的结果不会改变。执行效率高

3.联表查询

将两个表中的数据，使用查询语句同时显示在查询结果中
联表查询采用笛卡尔乘积实现，将一个表中所有记录和另一个表中所有记录一一关联
显示主表从表信息
显示所有的汽车以及汽车主人的姓名
SELECT c.*,m.m_name FROM t_car c JOIN t_man m ON c.fk_manId=m.pk_id; – 只能用于内连接
等价于
SELECT c.*,m.m_name FROM t_car c,t_man m WHERE c.fk_manId=m.pk_id;
分类
内联接
只能查询两个表之间有对应记录的数据
select 查询列 from 表一，表二 where 主键=外键
select 查询列 from 表一 join 表二on 主键= 外键
显示所有的主人，显示每个主人的汽车数量
SELECT m.*,COUNT(c.pk_id) FROM t_man m JOIN t_car c ON m.pk_id=c.fk_manId
GROUP BY m.pk_id;
外联接
可以查询一个表中所有记录，无论该记录是否有对应的记录
select 查询 from 表一 left join 表二 on 主键=外键
显示所有的主人，显示每个主人的汽车数量
SELECT m.*,COUNT(c.pk_id) FROM t_man m LEFT JOIN t_car c ON m.pk_id=c.fk_manId
GROUP BY m.pk_id;
自联接
CREATE TABLE t_em(
pk_id INT PRIMARY KEY,
e_name VARCHAR(20),
e_job VARCHAR(20),
fk_emId INT
);
INSERT INTO t_em(pk_id,e_name,e_job,fk_emId)
VALUES(100,‘胡老板’,‘BOSS’,NULL),
(101,‘李通’,‘常务经理’,100),(102,‘周军’,‘销售经理’,100),
(103,‘周勇’,‘业务员’,102),(104,‘刘红’,‘业务员’,102),(105,‘何军’,‘业务员’,102),
(106,‘马处’,‘会计’,101),(107,‘刘红’,‘文员’,101);

SELECT * FROM t_em;

列出所有员工的姓名和职位，以及上级领导的姓名
SELECT e1.e_name,e1.e_job,e2.e_name FROM t_em e1 LEFT JOIN t_em e2 ON e1.fk_emId=e2.pk_id;
多对多查询
语法：select 查询列 from 表一 join 中间表 on
中间表外键=表一主键 join 表二 on 中间表外键=表二主键