数据库总结

已于 2024-05-12 09:58:31 修改
阅读量372 收藏 10
点赞数 3
分类专栏： 知识总结 文章标签： 数据库 mysql
于 2024-05-12 09:57:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/yuexiaqiying/article/details/138746466
版权

概述

数据库的好处

  1. 将数据持久化到本地
  2. 提供结构化查询功能

常见概念

  1. DB：数据库，存储数据的仓库
  2. DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL Server
  3. DBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等，是最大的范畴
  4. SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

常见分类

  1. 关系型数据库：MySQL、Oracle、DB2、SQL Server
  2. 非关系型数据库：
    • 键值存储数据库：Redis、Memcached、MemcacheDB
    • 列存储数据库：HBase、Cassandra
    • 面向文档的数据库：MongoDB、CouchDB
    • 图形数据库：Neo4J

SQL语言的分类

  1. DQL：数据查询语言：select、from、where
  2. DML：数据操作语言：insert、update、delete
  3. DDL：数据定义语言：create、alter、drop、truncate
  4. DCL：数据控制语言：grant、revoke
  5. TCL：事务控制语言：commit、rollback

MySQL

1 命令行

#库操作
# 显示所有的数据库
show databases;
# 创建test数据库
create database test;
# 切换数据库
use test;
# 删除数据库
drop database test;
# 查看当前数据库；
select database();
# 当前库中的表
show tables;

# 表操作
# 建表
create table MyClass(
 id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default '男',
degree double(16,2));
# 查看表结构
desc MyClass;
# 删除表
drop table MyClass;
# 添加字段
alter table MyClass add passtest int(4) default;
# 修改字段
alter table MyClass MODIFY passtest VARCHAR(32) comment '盒内的数量';
# 删除字段
 alter table MyClass drop column `passtest`; 
# 修改表名
rename table MyClass to YouClass;

2 SQL语言

2.1 DQL语言-基本查询
# 基本查询
 SELECT 字段名 AS "别名" FROM 表名;
# 去重
SELECT DISTINCT 字段名 FROM 表名;
# 做加法
SELECT 数值+数值; # 直接运算
SELECT 字符+数值; #首先先将字符转换为整数，如果转换成功，则继续运算，如果转换失败，则默认为0，然后继续运算
SELECT NULL+数值; # NULL和任何数值参与运算结果都是NULL
# 如果为null，返回指定的值，否则返回原本的值
SELECT IFNULL(字段名, 指定值) FROM 表名;

# %任意多个字符、_任意单个字符
# 员工编号<=>100的员工信息
# =只能判断普通类型的数值，而<=>还可以判断NULL
SELECT * FROM employees WHERE employee_id <=> 100 ;
# 查询员工名中第一个字符为B、第三个字符为d的员工信息
SELECT * FROM employees WHERE last_name LIKE 'B_d%' ;
# 查询员工编号不在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 120 ;
# 查询员工的工种编号是 IT_PROG、AD_VP中的一个员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROT', 'AD_VP') ;
# 注意：=、!=不能用来判断NULL

# 按别名排序查询
SELECT 
  *,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪 
FROM
  employees 
ORDER BY 年薪 ASC ;

# 按函数排序查询
SELECT 
  LENGTH(last_name),
  last_name 
FROM
  employees 
ORDER BY LENGTH(last_name) DESC ;
2.2 DQL语言-分组查询
/*
SELECT 
  查询列表 
FROM
  表 
【where 筛选条件】 
GROUP BY 分组的字段 
【having 分组后的筛选】
【order BY 排序的字段】 ;
*/

# 查询每个工种的员工平均工资
SELECT 
  AVG(salary),
  job_id 
FROM
  employees 
GROUP BY job_id ;
# 条件应该用在where后还是having后，看该条件是针对总体的还是个体的。
# 查询有奖金的每个领导手下员工的平均工资
SELECT 
  AVG(salary),
  manager_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id ;
# 查询哪个部门的员工个数>5
SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 5 ;
# 查询每个工种有奖金的员工的最高工资>6000的最高工资和工种编号，按最高工资升序
SELECT 
  MAX(salary) m,
  job_id
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING m > 6000 
ORDER BY m ;
# 查询每个工种每个部门的最低工资并按最低工资降序
SELECT 
  MIN(salary),
  job_id,
  department_id 
FROM
  employees 
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC ;
2.3 DQL语言-连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行。

  • sql92标准：支持内连接；
  • sql99标准：支持内连接、部分外连接（左外、右外）、交叉连接。

sql92标准演示

# 等值连接
SELECT 
  last_name,
  department_name 
FROM
  employees,
  departments 
WHERE employees.`department_id` = departments.`department_id` ;
# 非等值连接
SELECT 
  salary,
  grade_level 
FROM
  employees e,
  job_grades g 
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
# 自连接
# 查询员工名和它对应上级的名称
SELECT 
  e.employee_id,
  e.last_name,
  m.employee_id,
  m.last_name 
FROM
  employees e,
  employees m 
WHERE e.`manager_id` = m.`employee_id` ;

sql99标准演示

# 内连接：等值连接
SELECT 
  last_name,
  department_name 
FROM
  departments d 
INNER JOIN employees e ON e.`department_id` = d.`department_id` ;
# 内连接：非等值连接
SELECT 
  salary,
  grade_level 
FROM
  employees e 
INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
# 内连接：自连接
SELECT 
  e.last_name,
  m.last_name 
FROM
  employees e 
INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;
# 外连接：左外连接
SELECT 
  d.`department_name`
FROM
  departments d 
LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;
# 外连接：右外连接
SELECT 
  d.`department_name`
FROM
  employees e 
RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;
# 交叉连接
#使用交叉连接进行笛卡尔乘积查询
SELECT 
  b.*,
  bo.* 
FROM beauty b 
CROSS JOIN boys bo ;
2.4 子查询

3 函数

3.1 字符串函数
# concat：连接字符
SELECT CONCAT('Hello',' ','World') AS out_put;
# substr：截取子串
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  AS out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;
# replace：替换字符
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
# upper：变大写
SELECT UPPER('john') AS out_put;
# lower：变小写
SELECT LOWER('john') AS out_put;
# lpad：左填充
SELECT LPAD('殷素素',10/*字符串长度，而不是填充个数*/,'*') AS out_put;
# rpad：右填充
SELECT RPAD('殷素素',10,'*') AS out_put;
# length：获取字节长度
SELECT LENGTH('john') AS out_put;
# trim：去除前后空格
#删除指定字符的左右空格
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
#删除字符串的中某些字符
SELECT TRIM('aaa' FROM 'aaaaaaaaa张翠山aaaaaaaaa')  AS out_put;
# instr：获取子串第一次出现的索引
# 注意MySQL中的索引是从1开始的
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
3.2 数学函数
# round：四舍五入
#默认四舍五入
SELECT ROUND(-1.55) AS out_put;
#指定小数位数
SELECT ROUND(1.567,2) AS out_put;
# ceil：向上取整
SELECT CEIL(-1.02) AS out_put;
# floor：向下取整
SELECT FLOOR(-9.99) AS out_put;
# 取模
SELECT MOD(10,3) AS out_put;
# truncate：保留小数的位数，不进行四舍五入
SELECT TRUNCATE(1.69999,1) AS out_put;
# rand：获取随机数，返回0-1之间的小数
SELECT RAND() AS out_put;
3.3 控制函数
# if
SELECT IF(10 < 5, '大', '小') AS out_put;
# case形式一
SELECT 
  salary 原始工资,
  department_id,
  CASE
    department_id 
    WHEN 30 THEN salary * 1.1 
    WHEN 40 THEN salary * 1.2 
    WHEN 50 THEN salary * 1.3 
    ELSE salary 
  END AS 新工资 
FROM
  employees ;
# case形式二
SELECT 
  salary,
  CASE
    WHEN salary > 20000 THEN 'A' 
    WHEN salary > 15000 THEN 'B' 
    WHEN salary > 10000 THEN 'C' 
    ELSE 'D' 
  END AS 工资级别 
FROM
  employees ;
3.4 分组函数
/*
1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;
4、一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
*/
日期函数大全

表

order

idnamedateadd_time
1手机2020-06-23 00:50:001592992778
2书2020-06-23 00:00:001592992778

ADDDATE(expr,days):向expr中添加days天

SELECT ADDDATE(`date`,5) FROM `order` WHERE id = 1; -- 2020-06-28 00:50:00

ADDDATE(date,INTERVAL expr unit):向date中添加expr个unit

SELECT ADDDATE(`date`,INTERVAL 5 DAY) FROM `order` WHERE id = 1; -- 2020-06-28 00:50:00
SELECT ADDDATE(`date`,INTERVAL 5 YEAR) FROM `order` WHERE id = 1; -- 2025-06-23 00:50:00

ADDTIME(expr1,expr2):向expr1中添加expr2时间

SELECT ADDTIME(`date`,5) FROM `order` WHERE id = 1; -- 2020-06-23 00:50:05
SELECT ADDTIME(`date`,'01:01:05') FROM `order` WHERE id = 1; -- 2020-06-23 01:51:05

CURDATE():返回当前的日期

SELECT CURDATE(); -- 2020-06-24

CURTIME():返回当前的时间

SELECT CURTIME(); -- 16:01:33

DATE_ADD(date,INTERVAL expr unit):向date中添加expr个unit

SELECT DATE_ADD(`date`,INTERVAL 5 DAY) FROM `order` WHERE id = 1; -- 2020-06-28 00:50:00
SELECT DATE_ADD(`date`,INTERVAL 5 YEAR) FROM `order` WHERE id = 1; -- 2025-06-23 00:50:00

DATE_FORMAT(date,format):按format格式化date

SELECT DATE_FORMAT(`date`,'%y/%m/%d') FROM `order` WHERE id = 1; -- 20/06/23

DATE_SUB(date,INTERVAL expr unit):向date中减去expr个unit

SELECT DATE_SUB(`date`,INTERVAL 1 DAY) FROM `order` WHERE id = 1; -- 2020-06-22 00:50:00

DAYOFWEEK(date):返回date对应的星期

SELECT DAYOFWEEK(`date`) FROM `order` WHERE id = 1; -- 3
-- 星期日->1 星期一->2 星期六 ->7

DAYOFMONTH(date):返回date月中对应的天

SELECT DAYOFMONTH(`date`) FROM `order` WHERE id = 1;  -- 23

DAYOFYEAR(date):返回date年中对应的天

SELECT DAYOFYEAR(`date`) FROM `order` WHERE id = 1; -- 175

DAYNAME(date):返回date对应的星期名

MONTHNAME(date) :返回date对应的月份名

SELECT DAYNAME(`date`) FROM `order` WHERE id = 1;  -- Tuesday
SELECT MONTHNAME(`date`) FROM `order` WHERE id = 1; -- June

FROM_UNIXTIME(unix_timestamp):将时间戳unix_timestamp格式化为时间

FROM_UNIXTIME(unix_timestamp,format)：将时间戳unix_timestamp按format格式化为时间

SELECT UNIX_TIMESTAMP(date):将date转为unix时间戳

SELECT FROM_UNIXTIME(`add_time`) FROM `order` WHERE id = 1; -- 2020-06-24 17:59:38
SELECT FROM_UNIXTIME(`add_time`,'%d') FROM `order` WHERE id = 1; -- 24
SELECT UNIX_TIMESTAMP('2020-01-22') -- 1579622400

注意：mysql中的unix时间戳是10位，最后一位到秒，而java中的时间戳13位，最后一位是毫秒。

HOUR(time):返回time中的小时

MINUTE(time):返回time中的分钟

SECOND(time):返回time中的秒

SELECT HOUR(`date`) FROM `order` WHERE id = 1; -- 0
SELECT HOUR('14:42:56') -- 14
SELECT MINUTE(`date`) FROM `order` WHERE id = 1; -- 50
SELECT MINUTE('14:42:56'); -- 42
SELECT SECOND(`date`) FROM `order` WHERE id = 1; -- 0
SELECT SECOND('14:42:56'); -- 56

DAY(date)：返回date中的天
MONTH(date)：返回date中的月
QUARTER(date)：返回date中对应的季
WEEK(date)：返回date中的周
YEAR(date)：返回date中的年

SELECT DAY(`date`) FROM `order` WHERE id = 1; -- 23
SELECT MONTH(`date`) FROM `order` WHERE id = 1; -- 6
SELECT QUARTER(`date`) FROM `order` WHERE id = 1; -- 2
SELECT WEEK(`date`) FROM `order` WHERE id = 1; -- 25
SELECT YEAR(`date`) FROM `order` WHERE id = 1; -- 2020

NOW()：返回当前时间

SELECT NOW(); -- 2020-06-24 17:56:33

format格式

SELECT DATE_FORMAT(`date`,'%a') FROM `order` WHERE id = 1; -- Tue
格式描述示例
%a缩写星期名Tue
%b缩写月名Jun
%c月，数值6
%D带有英文前缀的月中的天23rd
%d月的天，数值(00-31)23
%e月的天，数值(0-31)23
%f微秒000000
%H小时 (00-23)00
%h小时 (01-12)12
%I小时 (01-12)12
%i分钟，数值(00-59)50
%j年的天 (001-366)175
%k小时 (0-23)0
%l小时 (1-12)12
%M月名June
%m月，数值(00-12)06
%pAM 或 PMAM
%r时间，12-小时（hh:mm:ss AM 或 PM）12:50:00 AM
%S秒(00-59)00
%s秒(00-59)00
%T时间, 24-小时 (hh:mm:ss)00:50:00
%U周 (00-53) 星期日是一周的第一天25
%u周 (00-53) 星期一是一周的第一天26
%V周 (01-53) 星期日是一周的第一天，与 %X 使用25
%v周 (01-53) 星期一是一周的第一天，与 %x 使用26
%W星期名Tuesday
%w周的天 （0=星期日, 6=星期六）2
%X年，其中的星期日是周的第一天，4 位，与 %V 使用2020
%x年，其中的星期一是周的第一天，4 位，与 %v 使用2020
%Y年，4 位2020
%y年，2 位20

unit值

SELECT ADDDATE(`date`,INTERVAL 5 DAY) FROM `order` WHERE id = 1;
-- 2020-06-23 00:50:00.000005
值含义示例
MICROSECOND微秒2020-06-23 00:50:00.000005
SECOND秒2020-06-23 00:50:05
MINUTE分2020-06-23 00:55:00
HOUR小时2020-06-23 05:50:00
DAY天2020-06-28 00:50:00
WEEK周2020-07-28 00:50:00
MONTH月2020-11-23 00:50:00
QUARTER季2021-09-23 00:50:00
YEAR年2025-06-23 00:50:00

0 其他

典型题

1 行列转换

列转行

/*
+------+------+------+
| 姓名 | 课程 | 分数 |
+------+------+------+
| 张三 | 语文 |   74 |
| 张三 | 数学 |   83 |
| 张三 | 物理 |   93 |
| 李四 | 语文 |   74 |
| 李四 | 数学 |   84 |
| 李四 | 物理 |   94 |
+------+------+------+
*/
select 姓名, 
/*
为什么要sum:分成临时表后，不用max取得都是第一行的语文，数学和物理取不到
为什么要else:以姓名分成临时表时，非本级的case都为Null。
*/
sum(case 课程 when '语文' then 分数 else 0 end) 语文,
sum(case 课程 when '数学' then 分数 else 0 end) 数学,
sum(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名;
/*
+------+------+------+------+
| 姓名 | 语文 | 数学 | 物理 |
+------+------+------+------+
| 张三 |   74 |   83 |   93 |
| 李四 |   74 |   84 |   94 |
+------+------+------+------+
*/

行转列

/*
+------+------+------+------+
| 姓名 | 语文 | 数学 | 物理 |
+------+------+------+------+
| 张三 |   74 |   83 |   93 |
| 李四 |   74 |   84 |   94 |
+------+------+------+------+
*/
SELECT
    姓名,
    '语文' AS 课程 ,
    语文 AS 分数
FROM tb 
UNION
SELECT
    姓名,
    '数学' AS 课程 ,
    数学 AS 分数
FROM tb 
UNION
SELECT
    姓名,
    '物理' AS 课程 ,
    物理 AS 分数
FROM tb; 
/*
+------+------+------+
| 姓名 | 课程 | 分数 |
+------+------+------+
| 张三 | 语文 |   74 |
| 张三 | 数学 |   83 |
| 张三 | 物理 |   93 |
| 李四 | 语文 |   74 |
| 李四 | 数学 |   84 |
| 李四 | 物理 |   94 |
+------+------+------+
*/
2 查询表中所有学科前两名的信息
/*
id name course score
1	a	英语	80
2	b	英语	81
3	c	英语	80
4	d	数学	87
5	e	数学	91
6	f	数学	89
*/
/*
思路：
关键的是子查询，本题需要查询前两名，所以分组是不可以的；
where后的条件会对每条数据执行；
整个语句的含义：针对每条数据判断在该学科内大于他成绩的人不多于2人(不含2人)，
这样，该条数据在该学科内肯定就是第一名或第二名
语句的缺点就是需要遍历所有数据，每条数据都需要执行子查询，效率会很慢。
为什么不是该名学生的成绩大于多少人呢？因为数据量不确定，没办法确定需要大于多少人才是第一名；
引申而言，正整数的开头我们知道是1，但是结束没办法确定
*/
SELECT * 
FROM class a 
WHERE (
	SELECT count(*) FROM class b where b.course = a.course and b.score > a.score
	)< 2;

数据库设计

命名：
使用英文小写，多个字符用_分割，字符数量不超过30个，表名字加上业务前缀；
部分字段命名参考：

  • 状态：status
  • 创建时间：create_time
  • 修改时间：update_time
  • 删除状态：delete_status
  • 其他表主键：其他表名_id/其他表名_no

字段设置：

  • 字段尽量设置为not null
  • 时间字段优先选择datetime，它的范围更大
  • 金融字段选择decimal(m,n)，n表示小数，m表示整数和小数和（小数点并不算）

字符集：

  • 字符集选择utf8mb4
  • 排序规则utf8mb4_general_ci对字母的大小写不敏感，utf8mb4_bin排序规则，对字符大小写敏感

欢迎使用面试题小程序

在这里插入图片描述

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值