数据库笔记

最新推荐文章于 2024-08-09 21:25:57 发布

Tsuki_0

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量216

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/Tsuki_0/article/details/129416396

版权

域

date

获取当前日期时间/日期/时间，格式：YYYY-MM-DD HH:MM:SS

select NOW();
SELECT CURDATE();
SELECT CURTIME();

获取某时间的年、月、日、时、分、秒，格式：数字

SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());
SELECT EXTRACT(MINUTE FROM NOW());
SELECT EXTRACT(SECOND FROM NOW());

时间/日期增减

select date_sub(now(), INTERVAL 1 hour);
select date_add(now(), INTERVAL 1 day);
# 间隔
YEAR MONTH DAY HOUR MINUTE SECOND

格式化

select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
select DATE_FORMAT(now(),'%Y-%m-%d %H:00:00');

字符串转日期

select str_to_date('2023-03-07 11:22:34', '%Y-%m-%d %H:%i:%s');

DATEDIFF(date_expression_1,date_expression_2);

select a.id, b.id, a.date, b.date, datediff(a.date, b.date) as dd from datetest a, datetest b;
+----+----+------------+------------+----+
| id | id | date       | date       | dd |
+----+----+------------+------------+----+
|  3 |  1 | 2023-03-18 | 2023-03-22 | -4 |
|  2 |  1 | 2023-03-16 | 2023-03-22 | -6 |
|  1 |  1 | 2023-03-22 | 2023-03-22 |  0 |
|  3 |  2 | 2023-03-18 | 2023-03-16 |  2 |
|  2 |  2 | 2023-03-16 | 2023-03-16 |  0 |
|  1 |  2 | 2023-03-22 | 2023-03-16 |  6 |
|  3 |  3 | 2023-03-18 | 2023-03-18 |  0 |
|  2 |  3 | 2023-03-16 | 2023-03-18 | -2 |
|  1 |  3 | 2023-03-22 | 2023-03-18 |  4 |
+----+----+------------+------------+----+

数据表操作

创建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。

CREATE TABLE tb_emp
    (
    id 		INT(11) 	primary key,
    deptId	INT(11), 	# <-
    name	VARCHAR(25)	unique not null, (多个约束条件)
    pswd    CHAR(6)		DEFAULT 'qwerty',
    gender  VARCHAR(6)	,
    birthday DATE,
    salary1 FLOAT,
    salary2 DECIMAL(7,2),
    # primary key(id)
    constraint fk_emp_another foreign key(deptId) references tb_another(id)
    ON DELETE CASCADE;--级联删除
    );

外键取值规则：空值或参照的主键值
(1)插入非空值时，如果主键值中没有这个值，则不能插入。
(2)更新时，不能改为主键表中没有的值。
(3)删除主键表记录时，可以在建外键时选定外键记录一起联删除还是拒绝删除。
(4)更新主键记录时，同样有级联更新和拒绝执行的选择。

修改表

ALTER TABLE <表名> [修改选项]

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

删除表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 …]

显示表

DESCRIBE <表名>;
DESC<表名>;

数据操作

插入

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

INSERT INTO <表名>
SET <列名1> = <值1>,
    <列名2> = <值2>,
    …

insert into datetest (id, date) values(4, now());
insert into datetest set id=5, date=now();

修改

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

删除

DELETE FROM <表名> 
  [WHERE 子句] 
  [ORDER BY 子句] 
  [LIMIT 子句]

查询

SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后的行条件*/
  ORDER BY sorting_columns /*对结果分组*/
  LIMIT offset_start, row_count /*结果限定*/

条件

多条件：AND（&&）、OR（||）、 XOR

=、!=、<>、<、<=、>、>=

[NOT] BETWEEN 取值1 AND 取值2：

SELECT name,age FROM tb_students_info WHERE age BETWEEN 20 AND 23;

IN(set)：

SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

[NOT] LIKE [BINARY] ‘字符串’：
使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *；
使用 _ 代表单个字符，字符的长度不能为 0；
BINARY 字段区分大小写。

SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';

分组

GROUP BY

table:
+------+------+-------+
| type | name | value |
+------+------+-------+
|    1 | a    |     1 |
|    1 | b    |     2 |
|    1 | c    |     3 |
|    2 | d    |     4 |
|    3 | e    |     5 |
|    3 | f    |     6 |
+------+------+-------+

select name, sum(value), count(*) from table group by name;
+------+------------+----------+
| name | sum(value) | count(*) |
+------+------------+----------+
| a    | 6          |        3 |
| b    | 4          |        1 |
| c    | 11         |        2 |
+------+------------+----------+

使用 coalesce 设置名称：
select coalesce(name, '总数'), sum(value) from table group by name with rollup;
+------------------------+------------+
| coalesce(name, '总数') | sum(value) |
+------------------------+------------+
| a                      | 6          |
| b                      | 4          |
| c                      | 11         |
| 总数				 	 | 21         |
+------------------------+------------+

排序

ORDER BY <字段名>[,<字段名>] [ASC|DESC]

select * from ordertest order by id, name;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  1 | da   |
|  2 | acd  |
|  2 | bcd  |
|  3 | abc  |
|  3 | abd  |
+----+------+

合并

UNION：不去除重复
UNION ALL：去除重复

连接

内连接（交集）
左连接（依据左表，右表缺省为NULL）
右连接（依据右表，左表缺省为NULL）

select * from a inner join b on a.id = b.id;
select * from a left outer join b on a.id = b.id;
select * from a right outer join b on a.id = b.id;

自然连接

select * from a natural join b;
select * from a natural left join b;
select * from a natural right join b;

a:						b:
+----+------+			+----+-----+
| id | name |			| id | sex |
+----+------+			+----+-----+
|  1 | a    |			|  1 |   1 |
|  2 | b    |			|  3 |   2 |
+----+------+			+----+-----+

select * from a inner join b on a.id=b.id;
+----+------+----+-----+
| id | name | id | sex |
+----+------+----+-----+
|  1 | a    |  1 |   1 |
+----+------+----+-----+

select * from a left join b on a.id=b.id;
+----+------+------+------+
| id | name | id   | sex  |
+----+------+------+------+
|  1 | a    |    1 |    1 |
|  2 | b    | NULL | NULL |
+----+------+------+------+

select * from a right join b on a.id=b.id;
+------+------+----+-----+
| id   | name | id | sex |
+------+------+----+-----+
|    1 | a    |  1 |   1 |
| NULL | NULL |  3 |   2 |
+------+------+----+-----+

两个表中有同名字段时，如左右表都有id字段，会造成查询结果中左表的id值被右表的id值覆盖掉
解决办法：

select a.id as aid, b.id as bid from selftest a join selftest b on a.name=b.name;

结果限定

LIMIT 初始位置，记录数（初始位置从0开始）
LIMIT 记录数 OFFSET 初始位置
不确定？：

LIMIT 3,5 
与 
LIMIT 5 OFFSET 4 
等价

视图

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

优点：定制用户数据，聚焦特定的数据、简化数据操作、提高数据的安全性、重用 SQL 语句。

创建：CREATE VIEW <视图名> AS <SELECT语句>
查看：DESCRIBE 视图名;
修改：ALTER VIEW <视图名> AS <SELECT语句> （也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。）
删除：DROP VIEW <视图名1> [ , <视图名2> …]

索引

用于实现数据的快速检索。

创建：CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…) #表示在创建新表的同时创建该表的主键。
KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…) #表示在创建新表的同时创建该表的索引。
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…) #表示在创建新表的同时创建该表的唯一性索引。
FOREIGN KEY <索引名> <列名> #表示在创建新表的同时创建该表的外键。

查看：SHOW INDEX FROM <表名> [ FROM <数据库名>]
删除：DROP INDEX <索引名> ON <表名> （如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。）

函数

IFNULL(v1,v2);

接受两个参数，如果不是 NULL，则返回第一个参数。否则，IFNULL 函数返回第二个参数。两个参数可以是文字值或表达式。

select ifnull(
	(select distinct salary 
	from Employee 
	order by salary desc 
	limit 1 offset 1), null
) as SecondHighestSalary;

rank

table:
+-------+
| score |
+-------+
|    97 |
|    97 |
|    99 |
|    60 |
|    88 |
|    88 |
+-------+

select score, rank() over (order by score desc) ranking from table;
+-------+---------+
| score | ranking |
+-------+---------+
|    99 |       1 |
|    97 |       2 |
|    97 |       2 |
|    88 |       4 |  <-
|    88 |       4 |
|    60 |       6 |
+-------+---------+

select score, dense_rank() over (order by score desc) ranking from table;
+-------+---------+
| score | ranking |
+-------+---------+
|    99 |       1 |
|    97 |       2 |
|    97 |       2 |
|    88 |       3 |  <-
|    88 |       3 |
|    60 |       4 |
+-------+---------+

select score, row_number() over (order by score desc) ranking from table;
+-------+---------+
| score | ranking |
+-------+---------+
|    99 |       1 |
|    97 |       2 |
|    97 |       3 |
|    88 |       4 |
|    88 |       5 |
|    60 |       6 |
+-------+---------+

存储函数

CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type;
[characteristic ...] 
BEGIN
	DECLARE ...
	SET ...
	RETURN ...
END

CREATE FUNCTION func_student(id INT(11))
RETURNS VARCHAR(20)
COMMENT '查询某个学生的姓名'
BEGIN
RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
END //

报错记录

Every derived table must have its own alias.

每一个派生出来的表都必须有一个自己的别名

(select * from table) as a

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

字段名不可用，加上 ‘’，如 ‘rank’

You can’t specify target table ‘Person’ for update in FROM clause

mysql不支持在一个表同时实现查找和删除。

刷题记录

获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

select ifnull(
	(select distinct salary 
	from Employee 
	order by salary desc 
	limit 1 offset 1), null
) as SecondHighestSalary;

获取并返回 Employee 表中第N高的薪水。如果不存在第N高的薪水，查询应该返回 null 。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE t int;
  SET t = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull(
          (select distinct salary from Employee 
          order by salary desc 
          limit 1 offset t), null
      ) as NthHighestSalary
  );
END

编写一个SQL查询来查找收入比经理高的员工。

select name as Employee from Employee e1
where salary > (select salary from Employee e2 where e2.id = e1.managerId);

查找所有至少连续出现三次的数字。（Logs: id, num）

select distinct a.num from Logs a, Logs b, Logs c 
	where a.id=b.id-1 and b.id=c.id-1 
	and a.num=b.num and b.num=c.num;

查询来报告所有重复的电子邮件。请注意，可以保证电子邮件字段不为 NULL

select email from (
    select email, count(*) as times from Person group by email having times>1)a;

删除所有重复的email，只保留一个id最小的唯一email。

delete from Person
where id in(
    select bid from (
        select a.id as aid, b.id as bid from Person a left join Person b on a.email=b.email
    )t where bid>aid
);

查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

select distinct id from (
    select a.id as id, a.temperature as ta, b.temperature as tb, 
    datediff(a.recordDate, b.recordDate)dd 
    from Weather a, Weather b)t
    where dd=1 and ta>tb;

查找每个部门中薪资最高的员工。

Employee:							Department:
+--------------+---------+			+-------------+---------+
| 列名          | 类型    |			| 列名         | 类型    |
+--------------+---------+			+-------------+---------+
| id           | int     |			| id          | int     |
| name         | varchar |			| name        | varchar |
| salary       | int     |			+-------------+---------+
| departmentId | int     |	
+--------------+---------+

select 
	d.name as Department, Employee, maxsalary as Salary 
from 
	Department d join (
    select e.departmentId as eid, e.name as Employee, maxsalary 
	from Employee e right 
		join (
        	select max(salary) as maxsalary, departmentId 
        	from Employee group by departmentId)m
    	on e.salary=m.maxsalary
    	where e.departmentId=m.departmentId)t 
	on t.eid=d.id;
    
SELECT
	Department.NAME AS Department,
	Employee.NAME AS Employee,
	Salary 
FROM
	Employee,
	Department 
WHERE
	Employee.DepartmentId = Department.Id 
	AND ( Employee.DepartmentId, Salary ) 
    IN (SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId )

找出每个部门中收入高的员工（一个部门的高收入者是指一个员工的工资在该部门的不同工资中排名前三）

select 
    d.name as Department, e1.name as Employee, e1.salary as Salary 
from
    Employee e1 join Department d on e1.departmentId=d.id
where
    3 > (
        select count(distinct salary) from Employee e2 
        where e2.salary > e1.salary and e1.departmentId=e2.departmentId
    );