lxf's blog
0%

MySql学习整理(二)

发表于 更新于 分类于

MYSQL学习整理(二)

关键字：default 默认值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 创建时添加
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
);
# 默认时间
CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
);
# 向已创建表添加单个约束
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
# 删除约束
ALTER TABLE Persons
ALTER City DROP DEFAULT
关键字：create index 创建索引

可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

1
2
3
4
5
6
7
8
# 添加允许重复值的索引
CREATE INDEX index_name
ON table_name (column_names)
# 添加不允许重复值的索引
CREATE UNIQUE INDEX index_name
ON table_name (column_names)
# 删除索引
ALTER TABLE table_name DROP INDEX index_name
关键字：auto increment 在新记录插入表中时生成一个唯一的数字

可以用于在每次插入新记录时，自动地创建主键字段的值。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)
# 默认地，AUTO_INCREMENT 的开始值是1，每条新记录递增1。
# 要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100
函数：Date 时间

SQL Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY

NOW

1
2
3
4
5
6
7
SELECT NOW(),CURDATE(),CURTIME()
# NOW() 返回当前的日期和时间
# NOW() 2008-11-11 12:45:34
# CURDATE() 返回当前的日期
# CURDATE() 2008-11-11
# CURTIME() 返回当前的时间
# CURTIME() 12:45:34

DATE

1
2
3
4
# DATE() 函数提取日期或日期/时间表达式的日期部分
SELECT ProductName, DATE(OrderDate) AS OrderDate
FROM Orders
WHERE OrderId=1

EXTRACT

1
2
3
4
5
6
7
8
9
10
11
12
# EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等
EXTRACT(unit FROM date)
# Unit值：MICROSECOND/SECOND/MINUTE/HOUR/DAY/WEEK/MONTH
# MONTH/QUARTER/YEAR/SECOND_MICROSECOND/MINUTE_MICROSECOND
# MINUTE_SECOND/HOUR_MICROSECOND/HOUR_SECOND/HOUR_MINUTE
# DAY_MICROSECOND/DAY_SECOND/DAY_MINUTE/DAY_HOUR/YEAR_MONTH

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay 
FROM Orders
WHERE OrderId=1

DATE_ADD

1
2
3
4
5
6
7
8
9
# DATE_ADD() 函数向日期添加指定的时间间隔
DATE_ADD(date,INTERVAL expr type)
# Type 值：MICROSECOND/SECOND/MINUTE/HOUR/DAY/WEEK/MONTH
# QUARTER/YEAR/SECOND_MICROSECOND/MINUTE_MICROSECOND/MINUTE_SECOND
# HOUR_MICROSECOND/HOUR_SECOND/HOUR_MINUTE/DAY_MICROSECOND/DAY_SECOND
# DAY_MINUTE/DAY_HOUR/YEAR_MONTH

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
FROM Orders

DATE_SUB

1
2
3
4
5
# DATE_SUB() 函数从日期减去指定的时间间隔
# Type 值同上

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAY) AS SubtractDate
FROM Orders

DATEDIFF

1
2
# DATEDIFF() 函数返回两个日期之间的天数
SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

DATE_FORMAT

1
2
3
# DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据
DATE_FORMAT(date,format)
https://www.runoob.com/sql/func-date-format.html
函数：null 判断是否为空

IFNULL/COALESCE

1
2
3
4
5
6
# 若 "UnitsOnOrder" 是 NULL 则返回0
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
# 若 "UnitsOnOrder" 是 NULL 则返回0
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
关键字：group by 聚合

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

1
2
3
4
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

牛客网对应知识点题目链接：
SQL22:
https://www.nowcoder.com/practice/6a62b6c0a7324350a6d9959fa7c21db3?tpId=82&tqId=29774&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey

关键字：having

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
HAVING 子句可以让我们筛选分组后的各组数据

1
2
3
4
5
6
7
8
9
10
11
12
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
# 实例
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

牛客网对应知识点题目链接：
SQL16
https://www.nowcoder.com/practice/c8652e9e5a354b879e2a244200f1eaae?tpId=82&tags=&title=&diffculty=0&judgeStatus=0&rp=1&tab=answerKey

关键字：exists 存在

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False

1
2
3
4
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
关键字：rank 排名

sql 四大排名函数: ROW_NUMBER、RANK、DENSE_RANK、NTILE

ROW_NUMBER

1
2
3
4
# ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号
# ROW_NUMBER()不存在相同的序号，当存在相同数据时，ROW_NUMBER()会依次进行排序，他们序号不相同
select ROW_NUMBER() over (order by column_name desc/asc)
from table_name

RANK

1
2
3
4
# RANK()存在相同的序号，当存在相同数据时，他们序号是一样的
# RANK()是跳跃的排名
select RANK() over (order by column_name desc/asc)
from table_name

DENSE_RANK

1
2
3
4
# DENSE_RANK()存在相同的序号，当存在相同数据时，他们序号是一样的
# DENSE_RANK()是连续的排名
select DENSE_RANK() over (order by column_name desc/asc)
from table_name

NTILE

1
2
3
4
# NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始
# number是分区的个数
select NTILE(number) over (order by column_name desc/asc)
from table_name

牛客网对应知识点题目链接：
SQL23:
https://www.nowcoder.com/practice/b9068bfe5df74276bd015b9729eec4bf?tpId=82&tqId=29775&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey