bingmous

欢迎交流，不吝赐教~

导航

SQL笔记

目录

 

管理MySQL

事务

创建、删除、选择数据库

其他

创建表，删除表，CREATE，DROP

ALTER TABLE ，添加、删除或修改列，修改约束

查询数据 SELECT

插入 INSERT INTO

更新 UPDATE

删除 DELETE

TOP，规定要返回的记录的数目

操作符

连接 JOIN，UNION

复制数据 SELECT INTO，INSERT INTO SELECT

CONSTRAINT，约束

INDEX，添加索引，删除索引

AUTO INCREMENT，在每次插入新记录时，自动地创建主键字段的值。

视图

日期

MySQL 时间函数

NULL函数

SQL Server 数据类型

MySQL 数据类型

GROUP BY，结合聚合函数，根据一个或多个列对结果集进行分组

HAVING筛选分组后的各组数据

SQL函数

 

管理MySQL

  • 开启服务 net start mysql
  • 关闭服务 net stop mysql 或 mysqladmin -u root shutdown -p
  • 连接到数据库 mysql -u root -p

事务

多条语句作为一个整体进行操作的功能，称为数据库事务。要么全部成功，要么全部失败

  • 使用，begin; ... commit;
  • 主动让事务失败，使用，begin; ... rollback;

隔离级别：

Read Uncommited，读了修改后未提交的，后来又撤回了，读到了脏数据

Read Commited，读到了commited前后的数据，数据不一致

Repeatable Read，读了多次读不到，但后来更新成功了，就读到了

Serializable，脏读，不可重复度读，幻读都不会出现，事务串行执行，效率大大下降，应用程序性能急剧降低

数据库常用命令

  • 创建数据库， CREATE DATABASE db; 或者在命令行下 mysqladmin -u root -p create db
  • 删除数据库，DROP DATABASE db; 或者在命令行下 mysqladmin -u root -p drop db
  • USE db; 选择数据库，使用该命令后所有的mysql命令都只针对该数据库

  • SHOW DATABASES; 列出所有数据库

  • SHOW TABLES; 显示指定数据库的所有表，之前需要使用USE指定数据库
  • SHOW COLUMNS FROM table; 显示数据表的字段信息，字段类型，主键信息，是否为NULL，默认值等
  • SHOW INDEX FROM table; 显示数据表的索引信息
  • SHOW TABLE STATUS [FROM db] [LIKE ''pattern]\G; 显示系统性能及统计信息，所有表的，或某个表的，\G按列输出
  • 查看表结构，DESC table;
  • 查看创建时的SQL语句，SHOW CREATE TABLE table;

创建表，删除表，CREATE，DROP

CREATE TABLE IF NOT EXISTS runoob_tb1 (
   runoob_id INT UNSIGNED AUTO_INCREMENT, # 自增
   runoob_title VARCHAR(100) NOT NULL, # 非空
   runoob_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY (runoob_id) # 添加主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 删除表
DROP TABLE table_name

ALTER TABLE ，添加、删除或修改列，修改约束

# 添加列
ALTER TABLE table_name
ADD column_name datatype
# 删除列
ALTER TABLE table_name
DROP COLUMN column_name

# 改变类型 SQL Serever
ALTER TABLE table_name
ALTER COLUMN column_name datatype

# 改变类型 MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

#修改表，改列名和类型，
alter table test change column birth varchar(10) not null;

查询数据 SELECT

  • 基本查询
SELECT * FROM runoob_tbl; # 查询所有

SELECT # *号代替其他字段
runoob_id AS id, # 设置别名，可以省略as
runoob_title title,
runoob_author author,
submission_date date
FROM runoob_tbl t1 # 设置别名t1，也可以查多个表，用逗号隔开
LIMIT 2 OFFSET 1; # limit 设定返回记录数，offset设置偏移量，默认为0

SELECT column_name,column_name
FROM table_name; # 多表查询时返回乘积
  • 条件查询
SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE score >= 80 AND gender = 'M'; # AND 查询
SELECT * FROM students WHERE score >= 80 OR gender = 'M'; # OR 查询
SELECT * FROM students WHERE NOT class_id = 2; # NOT 查询
# 多个条件查询
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
  • 去重查询
SELECT DISTINCT column_name,column_name
FROM table_name;
  • 排序查询
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

插入 INSERT INTO

INSERT INTO table_name # 插入所有列时可以省略字段
VALUES (value1,value2,value3,...);

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

INSERT INTO runoob_tbl
(runoob_title, runoob_author, submission_date) # 插入所有字段时可以省略
VALUES
("学习 PHP", "菜鸟教程", NOW()), # 可以一下插入多条
("学习 MySQL", "菜鸟教程", NOW()),
("JAVA 教程", "RUNOOB.COM", '2016-05-06');

# insert into 后面可以跟select，将结果插入表中，只要列是对应的就可以

# 插入或替换，
replace info test (...) values (...);
# 插入或更新，
insert info test (...) values  (...) on duplicate key update name='1', score='2';
# 插入或忽略，
insert info test (...) values  (...);

更新 UPDATE

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

# 使用replace函数替代将某些值替换
UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title,'JAVA 教程','C++');
WHERE runoob_id = 26; # 使用replace函数替换某些值

删除 DELETE

DELETE FROM table_name
WHERE some_column=some_value; # 省略了where删除所有内容

# 删除表中所有数据
TRUNCATE TABLE table_name; # 速度比delete快，见https://www.cnblogs.com/zhoufangcheng04050227/p/7991759.html

TOP，规定要返回的记录的数目

并非所有的数据库系统都支持 SELECT TOP子句

SQL server

SELECT TOP number|percent column_name(s) # 也支持百分比
FROM table_name;

MySQL语法

SELECT column_name(s)
FROM table_name
LIMIT number;

操作符

LIKE，IN，BETWEEN（可以用于时间），（NOT, AND, OR, 别名AS）

  • LIKE 操作符，用于在 WHERE 子句中搜索列中的指定模式。
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。不支持[charlist]和[^charlist]或[!charlist]

# 正则表达式 MySQL
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

通配符：% 代表0个或多个字符，_代表一个字符，[charlist]代表字符列表中的任何单一字符，[^charlist]或[!charlist]表示不再字符列表中的任何单一字符，也可以结合NOT  AND OR，

  • 操作符 IN，允许在 WHERE 子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
  • BETWEEN 操作符，选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
  • AS，给表名或列名取别名，使用.引用表的列
SELECT column_name AS alias_name
FROM table_name AS alias_name;

CONCAT函数把字符连在一起

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

连接 JOIN，UNION

  • JOIN，LEFT JOIN，RIGHT JOIN，FULL JOIN

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

join = inner join，left join = left outer join，right join = right outer join，full join = full outer join

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行

  • UNION 操作符用于合并两个或多个 SELECT 语句的结果集。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table1
UNION # UNION ALL允许重复值
SELECT column_name(s) FROM table2;

复制数据 SELECT INTO，INSERT INTO SELECT

  • SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT 。
SELECT *
INTO newtable [IN externaldb]
FROM table1;
# 或只是希望的列
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
  • INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
INSERT INTO table2
SELECT * FROM table1;
# 或者只复制希望的列到已存在的表中
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

CONSTRAINT，约束

  • 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
# 修改表的约束
ALTER TABLE Persons
MODIFY Age int NOT NULL;

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK - 保证列中的值符合指定的条件。

DEFAULT - 规定没有给列赋值时的默认值。

  • NOT NULL 约束
# 创建时添加约束
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

# 对于已有的表添加NOT NULL
ALTER TABLE Persons
MODIFY Age int NOT NULL;

# 删除 NOT NULL约束
ALTER TABLE Persons
MODIFY Age int NULL;
  • UNIQUE约束
# 创建表时添加 UNIQUE约束
CREATE TABLE Persons 
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons # 或者写在后面，或者unique key (P_Id)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

CREATE TABLE Persons # 命名或定义多个列的约束
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

# 对于已创建的表添加 UNIQUE 约束
ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons # 命名或定义多个列的约束
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

# 撤销约束
ALTER TABLE Persons # 撤销用constraint 命名的
DROP CONSTRAINT uc_PersonID

ALTER TABLE Persons # 撤销一个的，也可以用drop key https://zhidao.baidu.com/question/547479964.html
DROP INDEX uc_PersonID
  • PRIMARY KEY
# 创建表时添加
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

CREATE TABLE Persons # 命名或定义多个为主键
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

# 对于已创建的
# 如果使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

ALTER TABLE Persons # 命名或多个列为主键
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

# 撤销主键
ALTER TABLE Persons
DROP PRIMARY KEY

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
  • FOREIGN KEY
# 创建表时
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

# 对于已经创建的表
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

#撤销
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
  • CHECK
# 创建表时
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

# 对于已存在的表
ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

# 撤销
ALTER TABLE Persons
DROP CONSTRAINT chk_Person

ALTER TABLE Persons
DROP CHECK chk_Person
  • DEFAULT
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 # MySQL
ALTER City SET DEFAULT 'SANDNES'

ALTER TABLE Persons # SQL Serever
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

# 撤销
ALTER TABLE Persons # MySQL
ALTER City DROP DEFAULT

ALTER TABLE Persons # SQL Serever
ALTER COLUMN City DROP DEFAULT

INDEX，添加索引，删除索引

# SQL Serever
CREATE INDEX index_name # 创建索引
ON table_name (column_name) # 可以为多个列

CREATE UNIQUE INDEX index_name # 创建唯一索引
ON table_name (column_name)

# MySQL
ALTER TABLE students
ADD INDEX idx_name_score (name, score); 

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

ALTER TABLE students # 添加唯一性约束不建索引
ADD CONSTRAINT uni_name UNIQUE (name);


# 删除索引 SQL Serever
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID # uc_PersonID 为唯一性约束

# 删除索引 MySQL
DROP INDEX table_name.index_name # SQL server

ALTER TABLE table_name DROP INDEX index_name

AUTO INCREMENT，在每次插入新记录时，自动地创建主键字段的值。

# SQL Serever 
CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

# MySQL
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)
)

视图

# 创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

# 更新视图
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

# SQL Serever 更新视图
ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{ 
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     
}

# 删除视图
DROP VIEW view_name

日期

SQL Serever 时间函数

GETDATE()返回当前的日期和时间
DATEPART()返回日期/时间的单独部分
DATEADD()在日期中添加或减去指定的时间间隔
DATEDIFF()返回两个日期之间的时间
CONVERT()用不同的格式显示日期/时间

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

  • DATE - 格式：YYYY-MM-DD
  • DATETIME - 格式：YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式：唯一的数字

MySQL 时间函数

NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间的单独部分
DATE_ADD()向日期添加指定的时间间隔
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

  • DATE - 格式：YYYY-MM-DD
  • DATETIME - 格式：YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
  • YEAR - 格式：YYYY 或 YY

NULL函数

NULL无法通过等于判断，通过IS NULL 或IS NOT NULL判断。

# ISNULL函数 SQL Serever
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

# IFNULL函数 MySQL
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

# 或者使用coalescs MySQL
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

SQL Server 数据类型

String 类型：

数据类型描述存储
char(n)固定长度的字符串。最多 8,000 个字符。Defined width
varchar(n)可变长度的字符串。最多 8,000 个字符。2 bytes + number of chars
varchar(max)可变长度的字符串。最多 1,073,741,824 个字符。2 bytes + number of chars
text可变长度的字符串。最多 2GB 文本数据。4 bytes + number of chars
nchar固定长度的 Unicode 字符串。最多 4,000 个字符。Defined width x 2
nvarchar可变长度的 Unicode 字符串。最多 4,000 个字符。 
nvarchar(max)可变长度的 Unicode 字符串。最多 536,870,912 个字符。 
ntext可变长度的 Unicode 字符串。最多 2GB 文本数据。 
bit允许 0、1 或 NULL 
binary(n)固定长度的二进制字符串。最多 8,000 字节。 
varbinary可变长度的二进制字符串。最多 8,000 字节。 
varbinary(max)可变长度的二进制字符串。最多 2GB。 
image可变长度的二进制字符串。最多 2GB。 

Number 类型：

数据类型描述存储
tinyint允许从 0 到 255 的所有数字。1 字节
smallint允许介于 -32,768 与 32,767 的所有数字。2 字节
int允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。4 字节
bigint允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。8 字节
decimal(p,s)固定精度和比例的数字。

允许从 -10^38 +1 到 10^38 -1 之间的数字。

p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。

s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。

5-17 字节
numeric(p,s)固定精度和比例的数字。

允许从 -10^38 +1 到 10^38 -1 之间的数字。

p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。

s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。

5-17 字节
smallmoney介于 -214,748.3648 与 214,748.3647 之间的货币数据。4 字节
money介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。8 字节
float(n)从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。

n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。

4 或 8 字节
real从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。4 字节

Date 类型：

数据类型描述存储
datetime从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。8 字节
datetime2从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。6-8 字节
smalldatetime从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。4 字节
date仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。3 bytes
time仅存储时间。精度为 100 纳秒。3-5 字节
datetimeoffset与 datetime2 相同，外加时区偏移。8-10 字节
timestamp存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 值基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

Text 类型：

数据类型描述
CHAR(size)保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT存放最大长度为 255 个字符的字符串。
TEXT存放最大长度为 65,535 个字符的字符串。
BLOB用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
MEDIUMTEXT存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。

注释：这些值是按照您输入的顺序排序的。

可以按照此格式输入可能的值： ENUM('X','Y','Z')

SET与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

Number 类型：

数据类型描述
TINYINT(size)带符号-128到127 ，无符号0到255。
SMALLINT(size)带符号范围-32768到32767，无符号0到65535, size 默认为 6。
MEDIUMINT(size)带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
INT(size)带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
BIGINT(size)带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
FLOAT(size,d)带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。

注意：以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。

实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。

例如：

1、int的值为10 （指定zerofill）

int（9）显示结果为000000010
int（3）显示结果为010

就是显示的长度不一样而已 都是占用四个字节的空间

Date 类型：

数据类型描述
DATE()日期。格式：YYYY-MM-DD

注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'

DATETIME()*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS

注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'

TIMESTAMP()*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS

注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC

TIME()时间。格式：HH:MM:SS

注释：支持的范围是从 '-838:59:59' 到 '838:59:59'

YEAR()2 位或 4 位格式的年。

注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD

GROUP BY，结合聚合函数，根据一个或多个列对结果集进行分组

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

HAVING筛选分组后的各组数据

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;

SQL函数

聚合函数

  • AVG() - 返回平均值
  • COUNT() - 返回行数
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和

标量函数

  • UCASE() - 将某个字段转换为大写
  • LCASE() - 将某个字段转换为小写
  • MID() - 从某个文本字段提取字符，MySql 中使用
  • SubString(字段，1，end) - 从某个文本字段提取字符
  • LEN() - 返回某个文本字段的长度
  • ROUND() - 对某个数值字段进行指定小数位数的四舍五入
  • NOW() - 返回当前的系统日期和时间
  • FORMAT() - 格式化某个字段的显示方式

 

posted on 2019-10-12 22:01  Bingmous  阅读(23)  评论(0)  编辑  收藏  举报