mysql增删改查

数据库范式

 第一范式--原子性
 第二范式--唯一性
 第三范式--关联性

字段约束

mysql四种字段约束

主键约束	primary key 字段值唯一,且不能为null
非空约束 not null 	字段不能为null
唯一约束	unique		字段值唯一可以为空
外键约束	foreign key 关联数据库(不推荐使用容易闭环)

数据查询(DML)

mysql执行查询语句
1客户端通过连接器连接到mysql服务器
2连接器权限验证通过后,先查询是否有查询缓存(之间是否查询过此语句),有则直接返回缓存数据,如果没有则进入分析器
3分析器会对查询语句进行语法分析和词法分析,判断sql 语句是否正确,语法错误会直接返回错误信息,语法正确进入优化器
4优化器对查询语句进行优化处理,当表中有多个索引,优化器会判断哪个索引性能更好
5优化器执行完毕后进入执行器,执行器进行查询对比,返回数据
#普通查询

select *from emp;
select emp_name,emp_salary from emp;

使用别名

select emp_id,emp_name,(emp_salary+1200) as salary from emp;

执行顺序

# 1--词法分析和优化(并不是从左到右执行)
# 2--from选择数据来源
# 3--select选择输出内容

数据分页–limit起始位置,偏移量;

select emp_id,emp_name from emp limit 0,5;

避免limit+offset过大
应使用自增主键ID分页
第一页直接查
第二页得到第一页max(id)=100最后一条记录
第二页查询where id>100 limit 100
这样数据分页扫描量仍然是100条记录
禁止查询xx页之后的数据

结果集排序–order by desc|asc(默认升序)

select *from emp order by emp_salary desc ;

如果首要条件排序内容相同,会启用次要排序条件,

select *from emp order by emp_salary desc ,emp_age asc ;

练习:

# 工资排前三的员工信息

select *from emp order by emp_salary desc limit 3;

去重distinct

select distinct emp_name from emp;

注意:使用distinct的select子句中只能查询一列数据,多列会失效

distinct在select中只能出现一次

条件查询–where and|or

select emp_name,emp_salary
from emp where emp_salary>5000 and emp_salary<7000;

select emp_name,emp_salary
from emp
where d_id=3 and  (10+ifnull(emp_salary,0))>7555
and datediff(now(),hiredate)/365=20;

datediff()计算时间相差天数,
now()当前年月日时分秒
ifnull(null,0)如果值为空返回0,空值和任何值计算为null

#模糊查询like
_匹配单个字符,%匹配任意字符

select *from emp where emp_name like '杨__';

select *from emp where emp_name LIKE  '杨%';

where注意事项:
条件从左到右执行,把索引条件或筛选记录最多的条件写在最左侧

执行迅速
from>where>select>order by>limit

高级查询

聚合函数返回值只有一条
求平均值
round()四舍五入,floor()向下取整 ceil()强制进位

select avg(emp_salary)from emp;

求和函数
#sum()用于数字求和,字符类型返回值为0

select sum(emp_name)from emp;

结果:`0

select sum(emp_salary)from emp
where d_id in (1,5);

求最大值

select max(emp_salary)from emp
where d_id in (1,5);

select max(length(emp_name))from emp;

count函数
count(*)包含空值,count(列名)统计非空值

注意:聚合函数不能出现在where中,
聚合函数用于where过滤条件后统计数据
聚合函数判断条件写在having之后

#分组group by

select d_id from emp group by d_id;

select d_did from emp group by d_id having d_id>2;

逐级分组

查询每个同学,每种成绩的平均成绩

select sname,cname,avg(grade)from score
group by sname,cname;

注意:
含有group by 的select子句中包括聚合函数或group by的分组列,其余内容不能出现

对分组结果汇总 with rollup

select  sname,avg(grade),max(grade)from score
group by sname with rollup ;

group_concat函数
#分组查询中的某个字段拼接成一个字符串

select group_concat(sname) from score
where grade=80
group by sname;

表连接
内连接查询符合连接条件的数据

select *from emp join depart d on emp.d_id = d.dept_id;

注意:
表连接不写表连接条件会产生笛卡尔积
内连接的数据表不一定必须有同名字段,只要字段之间符合逻辑关系就可以

select score.sno,score.cno,grade.rank
from score join grade
on degree between grade.low and grade.upp
order by sno;

同表连接
c查询与’杨里生’同部门的员工有谁

select e2.emp_name
from emp e1 join emp e2
on e1.d_id=e2.d_id
where e1.emp_name='杨里生' and e2.emp_name!='杨里生';
select *from emp;

子查询建表

查询底薪超过公司平均底薪的员工信息

select e.emp_name from emp e join
(select avg(emp_salary) avg from emp)e2
on e.emp_salary>e2.avg;

查询’市场部’的人数,最高底薪,最低底薪,平均底薪,平均工资

select count(*),max(emp_salary),min(emp_salary),avg(emp_salary)
 from emp join depart d on emp.d_id = d.dept_id
where dept_name='市场部';

# 表的外连接
无论是否符合连接条件数据都会保留
左外连接
保留左表的全部记录和右表连接
右外连接逻辑相同

select * from teacher left join course
on teacher.tno=course.tno

外连接的注意事项:

#内连接只保留符合条件的记录,所以查询条件写在on子句和where子句的效果是相同的
#但是外连接里,条件写在where子句里,不符合条件的记录会被过滤的而不是保留下来

子查询

子查询:查询中嵌套查询

select cno,sno,degree from score where degree>
                                       (select max(degree)max from score where cno='3-245')
                                   and cno='3-105'
order by degree desc;

where子查询的效率 很低,比较每条记录都要重新执行子查询

from子查询只会执行一次,所以查询效率很高

where子句中的多行子查询,可以使用in,all any,exists关键字来处理多行表达式结果集的条件判断

select  name from employee where salary > any
(select salary from employee where name in ('张三','李四'))

exists 关键字把原来在子查询之外的条件判断,写在子查询里

数据添加修改删除

数据添加 insert

insert into student(sid, name, gender, age)
VALUES (null,'王丽','女',30),(null,'王青','男',44);

添加值时可以用子查询

insert into employee(eid, name, age, salary, depart, workage, haredate)
VALUES (21,'陈勤',33,6000,
        (select dept_name from depart where dept_id=1),2,'2015-3-9');

ignore忽略主键冲突只插入数据库不存在的记录
数据表中有id为2的记录,使用ignore关键字数据并未添加也为报错

insert ignore  into student(sid, name, gender, age)
 VALUES (2,'张楠','男',33);

数据修改update***

update 默认全表范围修改记录,where限定记录

update student
set age=age+2
order by sid
limit 3;

update的表连接
将李四调往行政部

update employee  e join depart d
set e.d_id=d.dept_id,e.depart='行政部'
where e.name='李四' and d.dept_name='行政部'

把低于平均工资的的员工.工资加1000

update employee e1 join
(select avg(salary) avg from employee ) e2
on e1.salary<e2.avg
set e1.salary=e1.salary+1000;

update的外连接
#把没有部门的员工,或者人事部薪资低于6000的员工,都调往3部门

update emp left join depart d on emp.d_id = d.dept_id
set d_id=3
where emp.d_id is null or (d.dept_name='人事部' and emp.emp_salary<6000);

数据删除delete
delete最后执行

delete ignore from emp
where d_id=1 and emp_age>22
order by emp_salary desc
limit  3;

mysql函数

数字函数	作用
abs()	求绝对值
round()	四舍五入
floor()	强制舍位
ceil()	强制进位

select floor(3.9);
select ceil(3.9);

执行结果依次如下:

3
4

日期函数	作用
now()	获取当前yyyy-MM-dd hh:mm:ss
curdate()	获取当前日期yyyy-MM-dd
curtime()	获取当前时间hh:mm:ss

select now();
select curdate();
select curtime();

执行结果依次如下:

2019-10-21 17:21:20

2019-10-21

17:21:59

日期函数
date_format(日期源,占位符)	返回想要的格式
date_add(日期,interval 偏移量 day/minute)	实现日期加减
datediff(日期,日期)	计算相差的天数

select date_format(haredate,'%Y')
from employee;

输出出生日期的年份

2018
2017

符号表示

符号	作用	符号	作用
%Y	年份
%m	月份
%d	日期
%W	星期(英文)	%w	星期(数字)
%h	小时(12)	%H	小时(24)
%i	分钟
%s	秒
%r	小时分钟秒(24)	%T	小时分钟秒(12)

获取时间对应星期几

select date_format('2019-10-21','%W');

Monday

查询2018年上半年入职员工的名字

select name
from employee
where date_format(haredate,'%Y')=2018
and date_format(haredate,'%m')<6;

注意:日期不能直接加减,日期不能与数字加减

select date_add('2019-09-4',interval -3 day );
select date_add('2019-09-4',interval 3 month );
select date_add('2019-10-21 17:59:31',interval -30 minute );
#嵌套
select date_add(date_add('2019-10-21 17:59:31',interval 6 month ),interval 3 day );

结果依次如下:

2019-09-01
2019-12-04
2019-10-21 17:29:31
2020-04-24 17:59:31

字符函数

函数	功能
lower()	转换成小写
upper()	转换成大写
length()	字符数量
concat()	字符串连接

批处理
addBatch()

数据库地址加速?rewriteBatchedStatements=true

package test;

import Util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Tset {
    public static void main(String[] args) {
        Connection conn = JDBCUtil.getConnection();
        String s = "insert into users(uname, psvd) value (?,?)";
        try {
            PreparedStatement stmt=conn.prepareStatement(s);
            for (int i = 0; i < 100; i++) {
                stmt.setString(1,"aaaa");
                stmt.setString(2,"afafdas");
                //添加批处理
                stmt.addBatch();
            }
            stmt.executeBatch();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}