SQL基础与高级查询-优快云博客

本文链接：https://blog.youkuaiyun.com/ZChiChi88/article/details/116375869

数据库的分类

网状结构数据库：美国通用电气公司IDS（Integrated Data Store），以节点形式存储和访问。
层次结构数据库：IBM公司IMS（Information Management System）定向有序的树状结构实现存储和访问。
关系结构数据库：Oracle、DB2、MySQL、SQL Server，以表格（Table）存储，多表间建立关联关系，通过分类、合并、连接、选取等运算实现访问。
非关系型数据库：ElastecSearch、MongoDB、Redis，多数使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据。

创建数据库

mysql> CREATE DATABASE mydb1; #创建mydb数据库
mysql> CREATE DATABASE mydb2 CHARACTER SET utf-8; #创建数据库并设置编码格式为utf-8
mysql> CREATE DATABASE IF NOT EXISTS mydb4; #如果mydb4数据库不存在，则创建；如果存在，则不创建。

展示、修改、删除数据库

#sql查询基本的语句
#查询员工表中所有员工的所有信息（所有列）
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;

#查询员工表中所有员工的编号、名字、年薪（列名均为中文）as相当于起了别名 在查询出来的表中 以这些别名命名。
SELECT employee_id as "编号" , first_name as "名字" , salary*12 as "年薪" 
FROM t_employees;

#查询员工表中所有经理的ID。distinct 去重
SELECT DISTINCT manager_id 
FROM t_employees;

#查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;

#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000  AND commission_pct = 0.30;
(WHERE salary BETWEEN 6000 AND 10000; )
(WHERE salary >= 6000 AND salary <= 10000;)

#查询没有提成的员工信息（编号，名字，薪资 , 提成）
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;

模糊查询

LIKE _ （单个任意字符）

列名 LIKE ‘张_’

LIKE %（任意长度的任意字符）

列名 LIKE ‘张%’

分支结构查询

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果
END

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT employee_id , first_name , salary , department_id , 
       CASE
           WHEN salary>=10000 THEN 'A'
           WHEN salary>=8000 AND salary<10000 THEN 'B'
           WHEN salary>=6000 AND salary<8000  THEN 'C'
           WHEN salary>=4000 AND salary<6000  THEN 'D'
   ELSE 'E'
       END as "LEVEL"
FROM t_employees;

字符串的查询

#拼接内容
SELECT CONCAT('My','S','QL');

#字符串替换
SELECT INSERT('这是一个数据库',3,2,'MySql');#结果为这是 MySql 数据库

#指定内容转换为小写
SELECT LOWER('MYSQL');#mysql

#指定内容转换为大写
SELECT UPPER('mysql');#MYSQL

#指定内容截取
SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL

聚合函数

语法：SELECT 聚合函数(列名) FROM 表名;

经验：对多条数据的单列进行统计，返回统计后的一行结果。

聚合函数	说明
SUM()	求所有行中单列结果的总和
AVG()	平均值
MAX()	最大值
MIN()	最小值
COUNT()	求总行数

#统计所有员工每月的工资总和
SELECT sum(salary)
FROM t_employees;

#统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees;

#统计所有员工中月薪最高的工资
SELECT MAX(salary)
FROM t_employees;

#统计所有员工中月薪最低的工资
SELECT MIN(salary)
FROM t_employees;

#统计员工总数
SELECT COUNT(*)
FROM t_employees;

Limit分页查询：一页显示 10 条，一共查询三页

每页起始下标值是（当前页数-1）* 每页显示行数
#思路：第一页是从 0开始，显示 10 条
SELECT * FROM LIMIT 0,10;

#第二页是从第 10 条开始，显示 10 条
SELECT * FROM LIMIT 10,10;

#第三页是从 20 条开始，显示 10 条
SELECT * FROM LIMIT 20,10;

7.11.2 SQL 语句执行顺序

1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY ： 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果

7.11.1 SQL 语句编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列（asc|desc）LIMIT 起始行，总条数

子查询

#查询与名为'King'同一部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列

合并查询

#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;

注意：合并结果的两张表，列数必须相同，列的数据类型可以不同

合并两张表的结果（保留重复记录）

#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

经验：使用 UNION 合并结果集，会去除掉两张表中重复的数据

表连接查询

SELECT 列名 FROM 表 1 连接方式表 2 ON 连接条件

内连接查询（INNER JOIN ON）

#1.查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID

#2.查询所有有部门的员工信息（不包括没有部门的员工） MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID

修改表和数据库的时候用的，
ALTER, DROP, CREATE,
对表中信息修改用的，
SELECT, UPDATE, INSERT, DELETE

约束

1.主键约束 primary key
2.唯一约束 UNIQUE
3.列自动增长，必须是数字类型，AUTO_INCREMENT
4.非空约束 NOT NULL
5.默认值约束 DEFAULT
6.CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名（列名）

事务

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL 语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

事务的特性

Atomicity(原子性)

表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

Consistency(一致性)

表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

Isolation(隔离性)

事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

Durability(持久性)

持久性事务完成之后，它对于系统的影响是永久性的。

事务完成转账

#A 账户给 B 账户转账。
#1.开启事务
START TRANSACTION;|setAutoCommit=0;#禁止自动提交 setAutoCommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
#3.事务内语句都成功了，执行 COMMIT；
COMMIT;
#4.事务内如果出现错误，执行 ROLLBACK;
ROLLBACK;