Appearance

什么是数据库 ​

数据库（database）是用来组织、存储和管理数据的仓库。 记事本PLUS。

常见的数据库和分类

传统型数据库: MySQL(3306)、PostgreSQL(5432)、SQL Server(1433)、Oracle(1521)、HANADB(30013)。 它们设计理念相同，用法类似，其中Oracle和HANADB原生不支持自增ID,但是可以用迭代器实现。 非关系型数据库: Mongodb(27017)、redis(6379)

传统型数据库的数据组织结构

分为: 数据库（database）、数据表（table）、字段（field）、数据行（row）

传统型数据库三范式

第一范式: 原子性，确保每列不可再分

第二范式: 唯一性，确保表中的每列都和主键相关，即不能存在某些字段只依赖于主键的一部分

第三范式: 独立性，确保每列都和主键列直接相关，而不是间接相关

常用SQL语句 ​

什么是SQL语句？

SQL（Structured Query Language）是结构化语言，专门用来访问和处理数据库的编程语言。 且在SQL语句中，关键字的大小写不敏感

1.简单的SQL语句 ​

增删改查（Create、Retrieve、Update、Delete）

增(C) 向数据表中插入新的数据行: INSERT INTO 表名 ([列名]) values ('[对应值]')删(R) 删除一条数据: DELETE FROM 表名 WHERE 限定条件改(U) 修改数据表中的数据: UPDATE 表名 SET [列名='值'] WHERE 限定条件查(D) 查询所有数据: SELECT * FROM 表名称 查询对应列的数据: SELECT [列名称] FROM 表名称

例:

sql
SELECT username.password FROM users
INSERT INTO users (username.password) values ('admin','admin')
UPDATE users SET username='admin',password='admin' WHERE id=0
DELETE FROM users WHERE id=0

2.限定条件的WHERE子句 ​

WHERE子句用于限定选择的标准。在SELECT、UPDATE、DELETE语句中，皆可使用WHERE语句来限定选择的标准。

各种限定方式： 条件运算符: > 、< 、= 、<> 、>=、 <= 逻辑运算符: AND 、OR 、NOT 模糊查询: BETWEEN AND: 在一个数据段之间 LIKE: 含有 %、_、escape 三个关键字。风别表示多个字符、单个字符、和转义字符 IN : 包含关键字，表示是否含有列表中的其中一项 IS NULL / IS NOT NULL: 是否为NULL

例:

sql
SELECT * FROM users WHERE username<>'admin'
SELECT * FROM users WHERE wealth>=0 AND wealth<=9
SELECT * FROM users WHERE wealth BETWEEN 0 AND 9
SELECT * FROM users WHERE username LIKE '%a%'
SELECT * FROM users WHERE username IN('administrator','admin')
SELECT * FROM users WHERE username IS NULL

优先级列表:

计算顺序优先级
1算术运算符，例如+、-、*、/运算符
2比较运算符，例如>、<、>=、<=、<>运算符
3IS [NOT] NULL，LIKE，[NOT] IN
4[NOT] BETWEEN
5NOT
6AND
7OR

秘诀: 该加括号加括号

3.DISTINCT关键字 ​

DISTINCT关键字可用于返回唯一不同的值。 例: 从users表中获取所有用户姓名，且不出现重复。

sql
SELECT DISTINCT username FROM users

4.ORDER BY排序 ​

默认为升序排序，如按username列中的首字母升序排序: SELECT * FROM users ORDER BY username 在ORDER BY的内容后加上DESC关键字后即可进行降序排序: SELECT * FROM users ORDER BY username DESC ORDER BY可在后面用CASE WHEN:

SQL
ORDER BY CASE WHEN "KHFL" = '大型' THEN '1'
				WHEN "KHFL" = '中型' THEN '2'
				WHEN "KHFL" = '小型' THEN '3'
				WHEN "KHFL" = '零散' THEN '4' 
				ELSE '5' END

5.LIMIT限制输出,不同数据库语句不同,例: SQL Server为top ​

LIMIT可以限定输出的记录数量 例: 输出前五行: SELECT * FROM users LIMIT 5 从行号为0的行开始，输出5行（第一行行号为0）: SELECT * FROM users LIMIT 0,5 LIMIT可和ORDER BY 可组合使用， LIMIT放在ORDER BY 后面: SELECT * FROM users ORDER BY username LIMIT 5


6.COUNT(*)统计返回结果的总数据条数 ​

语句: SELECR COUNT(*) FROM 表名 WHERE 限定条件 例:查询users表中名称不为admin的用户数量

sql
SELECT COUNT(*) FROM users WHERE username<>'admin'

COUNT中可以用CASE WHEN:

SQL
SELECT COUNT(CASE WHEN sex <= 0 THEN 1 END) FROM users

7.使用AS为列设置别名 ​

语句: 放在列名后面，每个列名单独设置 别名。 例: 给users表中username和password列取别名

sql
SELECT username AS uname,password AS upwd FROM users

个人开发使用体会: 在JAVA中设置相关类接受数据库中的数据时，方便统一命名以及调试。


8.CASE WHEN函数 ​

语句:

sql
CASE SEX WHEN '1' THEN '男' ELSE '女' END`

也可以在WHEN中写条件表达式函数实现。例:

sql
CASE WHEN SEX = 1 THEN '男'
	WHEN SEX =2 THEN '女'
	ELSE NULL
END AS 性别

9.GROUP BY ​

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。 语句:

sql
SELECT name, score
FROM table_name
GROUP BY name

也可配合MAX()实现重复数据合并

sql
SELECT name, MAX(score)
FROM table_name
GROUP BY name

10.JOIN-以LEFT JOIN为例 ​

LEFT JOIN 关键字从左表(table1)返回所有的行，即使右表(table2)中没有匹配。如果右表中没有匹配，则结果为 NULL。 语句:

sql
SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name

11.HAVING筛选 ​

HAVING 子句可以让我们筛选分组后的各组数据。 语句:

sql
SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1
HAVING SUM(column2) > 0;

12.UNION ​

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 UNION 操作符默认会去除重复的记录，如果需要保留所有重复记录，可以使用 UNION ALL 操作符。

语句:

sql
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

13.子查询 ​

子查询通常与 SELECT 语句一起使用。

语句:

sql
SELECT column_name
	,(SELECT SUM(column_name) FROM table2 WHERE table1.ID = table2.ID)
FROM table1

14.EXISTS ​

使用EXISTS代替IN。 语句:

sql
SELECT * FROM table_name1
where column1 IN(SELECT column1 FROM table_name2)

SELECT * FROM table_name1 e
where  exists(SELECT 1 FROM table_name2 d WHERE d.column1=e.column1)

使用EXISTS代替DISTINCT。 语句:

sql
select distinct e.deptno,d.dname  from emp e,dept d
where e.deptno=d.deptno;

select  d.deptno,d.dname  from dept d
where exists(select 1 from emp e where e.deptno=d.deptno);

👇以下关键字仅在新的数据库版本中可用，如MySQL8.0👇

15.WITH ​

用于创建临时表或视图。 语句:

sql
WITH temp_table AS (
    SELECT column1, column2
    FROM some_table
    WHERE column3 = 'value'
)

SELECT *
FROM temp_table

也可用WITH语句进行递归查询。如:

sql
WITH recursive_query AS (
    SELECT start_id, end_id, distance
    FROM some_table
    WHERE start_id = 1
    UNION ALL
    SELECT r.start_id, t.end_id, r.distance + t.distance
    FROM recursive_query r
    JOIN some_table t ON r.end_id = t.start_id
)
SELECT *
FROM recursive_query

16.RANK(),ROW_NUMBER(),DENSE_RANK() ​

  • rank 遇重复值排序并列，然后跳跃到当前排序记录次数开始（递增或递减）排序
  • row_number 遇重复值排序不并列，连续不间断（递增或递减）排序
  • dense_rank 遇重复值排序并列，然后继续不间断（递增或递减）排序 语句:
sql
select  name,salary
	,RANK() over (PARTITION BY NAME ORDER BY salary desc) as rank1
	,ROW_NUMBER() over (PARTITION BY NAME ORDER BY salary desc) as ROW1
	,DENSE_RANK() over (PARTITION BY NAME ORDER BY salary desc) as DENSE1 
from employee

sql例子-获取用户信息 ​

sql
SELECT
	OT_User.EmployeeNumber AS 工号,
	OT_User.NAME AS 姓名,
	CASE
		WHEN OT_User.state IN ('0') THEN '已离职'
		WHEN OT_User.state IN ('1') THEN '在职'
		ELSE NULL
	END AS 在职状况,
	CASE sys_user.sex
		WHEN 1 THEN '男'
		WHEN 2 THEN '女'
		ELSE NULL
	END AS 性别,
	sys_user.mobile AS 手机号,
	sys_user.zhiji AS 职级,
	wx_scbab.fch AS 车牌号,
	wx_scbab.flx AS 车辆类型,
	sys_user.dept1,
	sys_user.dept2,
	sys_user.dept3,
	sys_user.dept4,
	sys_user.dept5
FROM
	OT_User
	LEFT JOIN sys_user ON OT_User.EmployeeNumber = sys_user.username
	LEFT JOIN wx_scbab ON OT_User.EmployeeNumber = wx_scbab.fclyyzgh 
WHERE
	1 = 1 
	AND OT_User.EmployeeNumber LIKE '[1|2][0|9]%[1-9]%' 
	AND OT_User.NAME LIKE '%%'
	AND OT_User.EmployeeNumber LIKE '%%' 
	
	AND OT_User.state LIKE '%1%' 
ORDER BY
	工号 DESC

SQL程序功能语句 ​

数据库操作 创建数据库: CREATE DATABASE database-name 删除数据库: drop database dbname

表操作 创建新表: create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 删除表: drop table tabname

字段操作 增加一个字段: Alter table tabname add column col type 修改字段长度: ALTER TABLE tabname ALTER (COL NVARCHAR(100))

其他操作 添加主键: Alter table tabname add primary key(col) 删除主键: Alter table tabname drop primary key(col) 创建索引: create [unique] index idxname on tabname(col….) 删除索引: drop index idxname 创建视图: create view viewname as select statement 删除视图: drop view viewname

个人开发使用体会: 除Alter不常用，一般在编写程序时会先设计好数据库，不会在Java等后端编程语言中对设计好的数据库进行更改。