SQL语言 表格基础操作 增删改查 数据增删改查

常用SQL代码

表格创建(增删改查)

增 create

格式

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

创建一个名为 “Persons” 的表，包含五列：PersonID、LastName、FirstName、Address 和 City

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

SQL数据类型

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

约束

NOT NULL约束(重要)

约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

添加约束

在一个已创建的表的 “Age” 字段中添加 NOT NULL 约束如下所示

ALTER TABLE Persons
MODIFY Age int NOT NULL;

删除约束

ALTER TABLE Persons
MODIFY Age int NULL;

PRIMARY KEY 主键约束

添加约束

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

删除约束

ALTER TABLE Persons
DROP PRIMARY KEY

DEFAULT 默认值约束(重要)

实例

创建表格( GETDATE() 这样的函数 ,自动插入日期)

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_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

默认函数

AUTO INCREMENT : 在新记录插入表中时生成一个唯一的数字

删 DRDP

删除列

删除 “Person” 表中的 “DateOfBirth” 列

ALTER TABLE Persons
DROP COLUMN DateOfBirth

DROP TABLE 语句用于删除表。

DROP TABLE table_name

DROP DATABASE 语句用于删除数据库。

DROP DATABASE database_name

清空数据表

请使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name

改 ALTER

表格修改:

1. 改数据类型
2. 新增列数

改变 “Persons” 表中 “DateOfBirth” 列的数据类型。

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

增加列 ALTER ADD

格式

ALTER TABLE table_name
ADD column_name datatype

增加一个列为good_id,文本格式,不能为空

ALTER TABLE {table_name} ADD goods_id text no null

数据(增删改查)

增 INSERT INTO

格式

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

在websites表格里

在name, url, alexa, country列

写入’百度’,‘https://www.baidu.com/’,‘4’,‘CN’

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

在table_name表格中,选取img列写入数据he

insert into table_name(img)values ("he")

删 DELETE

格式

DELETE FROM table_name
WHERE some_column=some_value;

从 “Websites” 表中删除网站名为 “Facebook” 且国家为 USA 的网站

DELETE FROM Websites
WHERE name='Facebook' AND country='USA';

注意点:

WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除

改 UPDATE SET

数据库中更改叫更新数据.

UPDATE 语句用于更新表中已存在的记录

在websites表格里,

找到"菜鸟教程".修改该alexa值为5000,country值为USA

UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';

注意点:

如果没有加where.(后果很严重)

UPDATE Websites
SET alexa='5000', country='USA'

执行以上代码会将 Websites 表中所有数据的 alexa 改为 5000，country 改为 USA。

执行没有 WHERE 子句的 UPDATE 要慎重，再慎重。

查

查询所有 *

查询 Websites 表格中所有列的数据

 SELECT * FROM Websites 

查询设置别名 AS

指定了两个别名，一个是 name 列的别名，一个是 country 列的别名。**提示：**如果列名称包含空格，要求使用双引号或方括号

SELECT name AS n, country AS c
FROM Websites;

查询前几个数据 LIMIT

格式

SELECT column_name(s)
FROM table_name
LIMIT number;

查询persons中前5个.

SELECT * FROM Persons LIMIT 5;

从 websites 表中选取前面百分之 50 的记录

SELECT TOP 50 PERCENT * FROM Websites;

查询 指定模式 数据 LIKE

格式

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

选取 name 以字母 “G” 开始的所有客户

SELECT * FROM Websites
WHERE name LIKE 'G%';

选取 name 以字母 “k” 结尾的所有客户

SELECT * FROM Websites
WHERE name LIKE '%k';

选取 name 包含模式 “oo” 的所有客户

SELECT * FROM Websites
WHERE name LIKE '%oo%';

选取 name 不包含模式 “oo” 的所有客户

SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';

提示："%" 符号用于在模式的前后定义通配符（默认字母）

通配符, 与 SQL LIKE 操作符一起使用

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任

选取 name 以一个任意字符开始，然后是 “oogle” 的所有客户

SELECT * FROM Websites
WHERE name LIKE '_oogle';

选取 name 以 “G” 开始，然后是一个任意字符，然后是 “o”，然后是一个任意字符，然后是 “le” 的所有网站

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

去重 DISTINCT

查询Websites表格中该country列中唯一不同的值(去重复)

SELECT DISTINCT country FROM Websites;

单条件查询 WHERE

查询Websites表格中列中.country="CN"的所有列(*号可以是单纯一列的的名称)

SELECT * FROM Websites WHERE country='CN';

注意点:

1. 如果是条件是字符必须加引号
2. 如果条件是数字,则不需要加引号

SELECT * FROM Websites WHERE id=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6kX8e3IP-1648269925889)(1648213896695.png)]

多条件查询 AND OR

AND

需要满足两个条件的才输出

从 “Websites” 表中选取国家为 “CN” 且alexa排名大于 “50” 的所有网站

SELECT * FROM WEBSITES WHERE country = "cn" AND alexa>50

OR

只需要满足一个条件就可以了

从 “Websites” 表中选取国家为 “USA” 或者 “CN” 的所有客户：

SELECT * FROM Websites
WHERE country='USA'
OR country='CN';

混合使用

从 “Websites” 表中选取 alexa 排名大于 “15” 且国家为 “CN” 或 “USA” 的所有网站：

SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');

查询多个值的结果(重要) IN

选取 name 为 “Google” 或 “菜鸟教程” 的所有网站

SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');

查询范围内的值(重要) BETWEEN

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

格式

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

选取 alexa 介于 1 和 20 之间的所有网站

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

除了 alexa 介于 1 和 20 之间的所有网站 外

SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

选取 name 以介于 ‘A’ 和 ‘H’ 之间字母开始的所有网站

SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

重要点

选取 date 介于 ‘2016-05-10’ 和 ‘2016-05-14’ 之间的所有访问记录

SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

混合使用

IN 跟BETWEEN

选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');

特殊提醒:不同数据库队BERWEEN的处理方式不同.

操作两个select结果 UNION

格式

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

**注释：**默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

注意点:

UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

查询 函数计算 返回结果(重要)

AVG ():平均值

SELECT AVG(column_name) FROM table_name

从 “access_log” 表的 “count” 列获取平均值：

SELECT AVG(count) AS CountAverage FROM access_log;

COUNT(): 返回匹配指定条件的行数。

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

计算 “access_log” 表中 “site_id”=3 的总访问量

SELECT COUNT(count) AS nums FROM access_log
WHERE site_id=3;

计算 “access_log” 表中总记录数：

SELECT COUNT(*) AS nums FROM access_log;

计算 “access_log” 表中不同 site_id 的记录数：

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

FIRST() 函数返回指定的列中第一个记录的值

LAST() 函数返回指定的列中最后一个记录的值。

MAX() 函数返回指定列的最大值。

SELECT MAX(column_name) FROM table_name;

MIN() 函数返回指定列的最小值。

SELECT MIN(column_name) FROM table_name;

SUM() 函数返回数值列的总数。

SELECT SUM(column_name) FROM table_name;

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

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

实例: 统计 access_log 各个 site_id 的访问量：

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

就是获取结果后,聚合成一个新的表格

HAVING( GROUP BY的子句)

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

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;

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

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

UCASE() 函数把字段的值转换为大写。

SELECT UPPER(column_name) FROM table_name;

LCASE() 函数把字段的值转换为小写。

SELECT LOWER(column_name) FROM table_name;

MID() 函数用于从文本字段中提取字符。

SELECT MID(column_name,start[,length]) FROM table_name;

LEN() 函数返回文本字段中值的长度。

SELECT LEN(column_name) FROM table_name;

ROUND() 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM TABLE_NAME;

参数	描述
column_name	必需。要舍入的字段。
decimals	可选。规定要返回的小数位数。

NOW() 函数返回当前系统的日期和时间。 (重要)

SELECT NOW() FROM table_name;

从 “Websites” 表中选取 name，url，及当天日期：

SELECT name, url, Now() AS date
FROM Websites;

FORMAT() 函数用于对字段的显示进行格式化

SELECT FORMAT(column_name,format) FROM table_name;

参数	描述
column_name	必需。要格式化的字段。
format	必需。规定格式。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f65z7Anq-1648269925891)(1648224046320.png)]

排序 ORDER BY

格式.

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

单列排序

从 “Websites” 表中选取所有网站，并按照 “alexa” 列排序 (正序)

SELECT * FROM Websites
ORDER BY alexa;

从 “Websites” 表中选取所有网站，并按照 “alexa” 列降序排序

SELECT * FROM Websites
ORDER BY alexa DESC;

多列排序

从 “Websites” 表中选取所有网站，并按照 “country” 和 “alexa” 列排序

SELECT * FROM Websites
ORDER BY country,alexa;