MySQL中查看当前连接的自动事务提交是否开启:
SHOW VARIABLES LIKE '%autocommit%';
MySQL关闭本次事务自动提交连接操作:
SET autocommit = 0;
注意:这种做法只能够关闭当前连接的自动事务提交功能,但是每次重新连接数据库,都会失效。
②数据添加操作
关键字:insert
(1)向全部字段中添加数据:
在向全部字段中添加数据的时候,可以不用手动声明字段名称
INSERT INTO job_level VALUES ('实习工资',1000.00,2999.00);
COMMIT;
(2)向部分字段中添加数据:
如果在插入数据的时候仅是向部分字段进行添加,那么必须手动声明字段名称;
并且在插入数据的时候,需要保证数据顺序和字段顺序一致。
INSERT INTO job (job_title,min_salary,max_salary) VALUES('董事长夫人',12999.00,13000.00);
COMMIT;
(3)向数据库表进行批量添加数据:
属于MySQL数据库方言
INSERT INTO location (city) VALUES ('武汉'),('苏州'),('南京'),('台北'),('无锡');
COMMIT;
③数据库删除操作:
关键字:delete
(1)全部删除操作:
DELETE FROM location;
(2)条件删除操作:
DELETE FROM location WHERE location_id >=9;
④数据修改操作:
(1)全修改操作:
UPDATE job SET job_title = '小秘',min_salary = 9999.99,max_salary = 99999.99;
(2)条件修改操作:
UPDATE job SET job_title = '小秘',min_salary = 9999.99,max_salary = 99999.99 WHERE job_id = 7;
#2.DQL
作用:执行数据查询操作,不需要使用数据库操作事务操作。
关键字:select
①查询表中全部数据:
SELECT * FROM emp;
注意:在查询数据库中所有字段的时候,不推荐使用*,而是推荐将所有的字段名称都写在SQL语句中。
②条件查询
(1)等值查询
#查询emp表中2号部门的全部员工
#一般的等值查询,使用=即可判断
SELECT * FROM emp WHERE dept_id = 2;
#查询emp表中谁是老板
#空值的等值查询,需要使用关键字is
SELECT * FROM emp WHERE manager_id IS NULL;
(2)不等值查询
#查询emp表中除了2号部门之外其他的员工
SELECT * FROM emp WHERE dept_id != 2;
#非空查询
SELECT * FROM emp WHERE dept_id IS NOT NULL;
(3)范围查询
#查询薪资在5000.00以上的所有员工信息
SELECT * FROM emp WHERE salary >= 5000.00;
#查询薪资在8000.00以下的所有员工信息
SELECT * FROM emp WHERE salary <= 8000.00;
#查询薪资在5000.00~8000.00之间的所有员工信息
SELECT * FROM emp WHERE salary >= 5000.00 AND salary <= 8000.00;
SELECT * FROM emp WHERE salary BETWEEN 5000.00 AND 8000.00;
#查询1,3,4,6部门下所有的员工信息
SELECT * FROM emp WHERE dept_id = 1 OR dept_id = 3 OR dept_id = 4 OR dept_id = 6;
SELECT * FROM emp WHERE dept_id IN (1,3,4,6);
(4)模糊查询
#查询名字当中以丝为结尾的,长度为2的员工信息
SELECT * FROM emp WHERE e_name LIKE '_丝';
#查询名字当中包含丝的员工信息,名字长度不限
SELECT * FROM emp WHERE e_name LIKE '%丝%';
(5)逻辑连词:
and:与条件,全真为真,一假全假
or:或条件,有真则真,全假为假
(6)分组查询
关键字:group by
#查询每个部门下的最高薪资
SELECT MAX(salary) FROM emp GROUP BY dept_id;
#查询每个工种下的最高薪资
SELECT MAX(salary) FROM emp GROUP BY job_id;
#查询薪资在6000.00以上的所有员工,并按照部门进行分组之后查询每一组的平均薪资
SELECT AVG(salary)
FROM emp
WHERE salary > 6000.00
GROUP BY dept_id;
#查询薪资在6000.00以上的所有员工,并按照部门进行分组之后统计人数
SELECT dept_id, COUNT(*)
FROM emp
WHERE salary > 6000.00
GROUP BY dept_id;
#统计每组员工每月的薪资总支出
SELECT dept_id, SUM(salary) FROM emp GROUP BY dept_id;
常见的分组函数:
max():求取最大值
min():求取最小值
avg():求取平均值
count():计总数
sum():求和
③组筛选:
关键字:having
#统计每组员工的平均薪资,并且只保留平均薪资在6999.00以上的组
SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id HAVING avg_salary > 6999.00;
④排序操作:
关键字:order by asc desc
#查询公司全部员工信息按照入职时间进行排序
SELECT * FROM emp ORDER BY hire_date;
#查询公司全部员工信息按照入职时间进行降序排序
SELECT * FROM emp ORDER BY hire_date DESC;
#查询公司中全部员工信息,按照部门编号升序排序,相同部门的员工按照工资降序排序
SELECT * FROM emp ORDER BY dept_id ASC, salary DESC;
⑤分页查询:
关键字:limit
#假设一页显示10条记录,查询第2页的emp表数据
SELECT * FROM emp LIMIT 10,10;
#LIMIT 关键字的两个参数:
# 参数1:表示当前页中数据的起始下标,取值从0开始
# 参数2: 表示当前页的篇幅
# 假设给定查询页码page和页面篇幅size,那么page页的起始记录下标:
# startIndex = (page-1)* size;
⑥查询语句关键字的执行顺序:
1.from:从目标数据表中取得所有字段和所有记录
2.where:针对每一条数据记录进行筛选之后,留下所有符合条件的记录
3.group by:将上一步骤中所有符合条件的单条记录按照分组要求进行筛选,留下所有符合条件的记录
4.having:在执行分组之后执行分组函数,然后按照分组函数的计算结果决定哪些组留下,哪些组舍弃
5.select:决定查询结果集合保留哪些字段
6.order by:根据某些指定的字段进行排序
⑦子查询:
定义:将一次查询的结果作为另一次(外层)查询的条件或者数据源使用
(1)使用子查询当作数据源
#子查询:使用内部查询结果作为外层查询数据源
SELECT * FROM (SELECT * FROM emp WHERE salary > 5000.00)AS e WHERE e.salary < 8000.00;
注意1:这种子查询的结果集可以是多行多列的,但是子查询的结果集必须包含外层查询的所需字段
注意2:我们需要为子查询的结果集起一个别名
(2)使用子查询作为外层查询条件使用:
情况1:一行一列一个数
#查询公司中工资大于平均薪资的员工信息
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
情况2:多行一列多个数
#查询哪些组的平均薪资在8200.00以上,查询这些组的员工有谁
SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING AVG(salary) > 8200.00);
⑧级联查询:
作用:可以实现跨表查询
原理:基于笛卡尔乘积的条件筛选
集合A={1,2,3},集合B={a,b,c}
笛卡尔乘积:A×B={1a,1b,1c,2a,2b,2c,3a,3b,3c}
(1)十字连接:
关键字:cross join
作用:没有太大的实际作用,就是表示两张数据表进行全排
#十字连接
SELECT e.*, d.*
FROM emp AS e CROSS JOIN dept AS d;
(2)左外连接:
关键字:left outer join....on
作用:在笛卡尔乘积的基础上,根据on后面的级联条件筛选记录,并且保证左表中的未匹配项。
#左外连接
SELECT e.*,d.* FROM emp AS e LEFT OUTER JOIN dept AS d
ON e.dept_id = d.dept_id;
(3)右外连接:
关键字:reight outer join....on
作用:在笛卡尔乘积的基础上,根据on后面的级联条件筛选记录,并且保证右表中的未匹配项。
#右外连接
SELECT e.*,d.* FROM emp AS e RIGHT OUTER JOIN dept AS d
ON e.dept_id = d.dept_id;
(4)内连接:
关键字:inner join....on
作用:在笛卡尔乘积的基础上,根据on后面的级联条件筛选记录,左右两张数据表中的未匹配项都不保留
#内连接
SELECT e.*,d.* FROM emp AS e INNER JOIN dept AS d
ON e.dept_id = d.dept_id;
(5)全外连接:
关键字:full outer join....on
作用:在笛卡尔乘积的基础上,根据on后面的级联条件筛选记录,左右两张数据表中的未匹配项都保留
#全外连接
SELECT e.*,d.* FROM emp AS e FULL OUTER JOIN dept AS d
ON e.dept_id = d.dept_id;
注意:MySQL数据库不支持全外连接。