# SQL的分类:
# 1、DDL:数据定义语言:CREATE\ ALTER\ DROP \ RENAME \TRUNCATE
# 2、DML:数据操作语言:INSERT \ DELETE \ UPDATE \ SELECT
# 3、DCL:数据控制语言:COMMIT\ROLLBACK\SAVEPOINT\GRANT\REVOKE
-- 子查询:是指一个查询语句嵌套在另一个查询语句的查询,提高SELECT的查询能力
SELECT * FROM employees;
# 查询谁的工资比黑暗之女高
# 一、自连接
SELECT e1.name,e1.salary FROM employees e1 JOIN employees e2
ON e1.salary >= e2.salary AND e2.`name` = '黑暗之女';
# 二、子查询
SELECT e1.* FROM employees e1,(
SELECT * FROM employees e
WHERE e.`name` ='黑暗之女'
) e2
WHERE e1.salary >= e2.salary;
SELECT * FROM employees e1 WHERE salary >=
(
SELECT salary FROM employees e2
WHERE e2.name = '黑暗之女'
);
# 子查询的分类
# 一:从内查询返回的结果的条目数
# 单行子查询 vs 多行子查询
# 二:从内查询是否执行多次,内查询的结果和外查询是否相关
# 不相关子查询 vs 相关子查询
# 单行子查询:
# 操作符:< > <= >= = <>
# 子查询编写思路:从里往外写(建议这个)或 从外往里写
# 如:返回location_id和1003号员工相同并且比1017号员工工资高的员工信息
SELECT * FROM employees
WHERE location_id =
(
SELECT location_id FROM employees
WHERE department_id = 1003
)
AND salary >
(
SELECT salary FROM employees
WHERE department_id = 1017
);
# 返回工资最少的员工信息
SELECT * FROM employees
WHERE salary =
(
SELECT MIN(salary) FROM employees
);
# 查询与1027或1004号员工的location_id和sex相同的其他员工的信息
SELECT * FROM employees
WHERE location_id IN (
SELECT location_id FROM employees
WHERE department_id IN (1027,1004)
)
AND sex IN (
SELECT sex FROM employees
WHERE department_id IN (1027,1004)
);
-- 子查询还可以在select、流程语句等等中使用。。。。
# 多行子查询:内查询返回多行数据
# 多行操作符:IN、ANY、ALL、SOME
# 相关子查询(上面写的基本是不相关子查询)
# 查询员工中工资大于本部门平均工资的员工的信息
# 可以使用IN、NOT IN的地方基本也可以用EXISTS、 NOT EXISTS
SELECT * FROM employees e1
WHERE salary >= (
SELECT AVG(salary) FROM employees e2
WHERE e1.location_id = e2.location_id
GROUP BY e2.location_id
);
SELECT * FROM employees e1,(
SELECT AVG(salary) AS "pjgz",location_id
FROM employees e2
GROUP BY e2.location_id
)`table`
WHERE e1.salary >= `table`.pjgz AND e1.location_id = `table`.location_id;
/*查询员工的管理者信息
SELECT employees_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT * FROM employees e2
WHERE e1.employees_id = e2.manager_id
);
*/
# 1、创建和管理数据库
# 1.1、如何创建数据库
# 方式一: 创建数据库(也要有权限才能创建)
-- CREATE DATABASE 数据库名;
# 方式二:创建数据库并指定字符集
-- CREATE DATABASE 数据库名 CHARACTER SET 字符集;
# 方式三:判断数据库是否存在,不存在则创建数据库(推荐使用)
-- CREATE DATABASE IF NOT EXISTS 数据库名
# 方式四:判断是否存在并且设置字符集
-- CREATE DATABASE IF NOT EXISTS 数据库 CHARACTER SET '字符集';
# 方式一和方式二,如果在创建时已经存在数据库会报错
# 查看这个连接下的数据库的名称,数量
-- SHOW DATABASES; 注意DATABASES有S
# 查看具体一个数据库的信息,默认是utf8mb4
-- SHOW CREATE DATABASE 数据库名;
# 查看具体一个表的信息
-- SHOW CREATE TABLE 表名;
CREATE DATABASE IF NOT EXISTS example1;
SHOW DATABASES;
SHOW CREATE DATABASE example1;
SHOW CREATE DATABASE example2;
/*
CREATE DATABASE example2 CHARACTER SET 'GBK';
SHOW DATABASES;
SHOW CREATE DATABASE example2;
*/
CREATE DATABASE IF NOT EXISTS emample3 CHARACTER SET 'GBK';
SHOW DATABASES;
SHOW CREATE DATABASE emample3;
-- 对于已经创建有的数据库
-- 如果有判断,会创建失败,但不会报错
-- 没有判断,则报错
CREATE DATABASE IF NOT EXISTS nice1 CHARACTER SET 'utf8mb4';
SHOW CREATE DATABASE nice1;
SHOW DATABASES;
# 1.2管理数据库
# 查看当前连接中的数据库有哪些
-- SHOW DATABASES;
# 查看当前具体数据库的信息
-- SHOW CREATE DATABASE 数据库名;
# 切换数据库
-- USE 数据库名;
# 查看当前数据库中保存有那些数据表
-- SHOW TABLES;
# 查看当前使用的数据库
-- SELECT DATABASE() FROM DUAL;
# 查看指定数据库下保存的数据表
-- SHOW TABLES FROM 指定的数据库名;
USE example1;
SELECT DATABASE() FROM DUAL;
SHOW TABLES FROM demo;
SELECT DATABASE() FROM DUAL;
SHOW TABLES FROM demo;
SELECT DATABASE() FROM DUAL;
# 注意一般情况下,数据库命不能改,如果有也是复制一份然后改的
# 1.3 修改数据库
# 更改数据库字符集
-- ALTER DATABASE 数据库名 CHARACTER SET '字符集';
SHOW CREATE DATABASE example2;
ALTER DATABASE example2 CHARACTER SET 'UTF8MB4';
SHOW CREATE DATABASE example2;
# 1.4 删除数据库
# 方式1:DROP DATABASE 数据库名;如果不存在报错
# 方式2:DROP DATABASE IF EXISTS 数据库名;(推荐)
SHOW TABLES FROM example1;
DROP DATABASE IF EXISTS example1;
# 2、如何创建数据表,也需要权限,如果创建表没指明字符集,使用库的字符集
/* 方式一:使用前用use切换数据库
CREATE TABLE IF NOT EXISTS 数据表名(
列表名 数据类型,
列表名 数据类型,
.....
);
*/
/*
USE demo1;
CREATE TABLE IF NOT EXISTS example1(
name VARCHAR(10),
age INT);
SHOW TABLES FROM demo1;
DESC example1;
*/
# 查看表结构
-- DESC 表名;
# 方式二:基于查询语句来创建表,这两个表名要在同一数据库中 ,查询语句可以很丰富
-- CREATE TABLE 表名 AS SELECT 列名 FROM 表名;
USE demo;
CREATE TABLE IF NOT EXISTS example1 AS SELECT * FROM employees;
SELECT * FROM example1;
# 复制一个没有数据的表
USE demo1;
CREATE TABLE IF NOT EXISTS empty_table AS SELECT * FROM demo.employees WHERE 1 = 2;
SELECT * FROM demo1.empty_table;
# 修改表 -> ALTER TABLE
# 3.1添加一个字段(列名)
-- ALTER TABLE 表名 ADD 字段名(列名) 数据类型;默认情况下添加到最后一个
USE demo;
# ALTER TABLE example3
# ADD kp DOUBLE(10,2);
USE demo1;
CREATE TABLE IF NOT EXISTS demo1.table1 (
country VARCHAR(15),
name VARCHAR(15),
sex VARCHAR(10),
lucky_number INT
);
DESC demo1.table1;
ALTER TABLE demo1.table1 ADD love_sex VARCHAR(10) AFTER lucky_number;
-- ALTER TABLE 表名 ADD 字段名(列名) 数据类型 存放位置;
-- 存放位置:FIRST\AFTER 字段名
ALTER TABLE example3
ADD kpppP INT AFTER sex;
DESC example3;
# 3.2修改一个字段:数据类型(一般不会改)、长度、默认值
-- ALTER TABLE 表名 MODIFY 字段名 数据类型;这个的修改可以对其数据类型和长度修改
ALTER TABLE example3 MODIFY name VARCHAR(25);
-- ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT 默认值;
ALTER TABLE example3 MODIFY name VARCHAR(25) DEFAULT 'kp';
# 3.3重命名一个字段,可以同时修改字段的数据类型!
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE example3 CHANGE kpp KPL INT;
# 3.4删除一个字段
-- ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE example3 DROP COLUMN kpl;
CREATE DATABASE IF NOT EXISTS LLLL CHARACTER SET 'GBK';
SHOW CREATE DATABASE llll;
DROP DATABASE LLLL;
# 4、重命名表
# 方式一:
-- RENAME TABLE 原表名 TO 新表名;
RENAME TABLE example1 TO example11;
SHOW TABLES FROM demo1;
SELECT DATABASE() FROM DUAL;
RENAME TABLE demo1.example1 TO demo1.fw;
# 方式二:
-- ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE example11 RENAME TO example1;
# 5、删除表
# 不光把表结构删掉了,同时表中的数据也删掉了,释放表空间
-- DROP TABLE IF EXISTS 表名;
DROP TABLE IF EXISTS demo.example1,demo.example2,demo.example3;
SHOW TABLES FROM demo;
DROP TABLE IF EXISTS demo1.fw;
# 6、清空表
-- TRUNCATE TABLE 表名;
TRUNCATE TABLE example2;
SELECT * FROM example2;
# DCL 中 COMMIT 和 ROLLBACK 的区别
/*
COMMIT:提交数据,一旦执行COMMIT,则数据就永久保存在了数据库中,意味着数据不可回滚(撤销)
ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚,回滚到最近的一次COMMIT之后
*/
# TRUNCATE TABLE(DDL) 和 DELETE FROM(DML) 的区别
/*
相同:都会对表中所有数据的删除,同时保留表结构。
不同:TRUNCATE TABLE:一旦执行,表数据全部清除。同时,数据是不可以回滚
DELETE FROM:一旦执行,表数据可以全部清除(不带where),同时,表数据可以回滚,也可以不回滚
*/
# DDL 和 DML
/*
DDL的操作一旦执行,就不可以回滚。(本质因为操作之后一定会执行一次COMMIT提交了)
DML的操作默认情况下,一旦执行,也是不可以回滚。但是,如果在执行DML之前,执行了
SET autocommit = FALSE(对DDL不起作用),则执行DML就可以回滚
注:TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,
但truncate无事务且不触发TRIGGER,有可能造成事故,故不建议TRUNCATE TABLE。
*/
COMMIT;-- 提交一次
SELECT * FROM example1;
SET autocommit = FALSE;
DELETE FROM example1;
ROLLBACK;-- 回滚
SELECT * FROM example1;-- 回滚成功
COMMIT;-- 提交一次
SELECT * FROM example2;
SET autocommit = FALSE;
TRUNCATE TABLE example2;
ROLLBACK;-- 回滚
SELECT * FROM example2;-- 回滚失败