服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - 数据库常用的sql语句汇总

数据库常用的sql语句汇总

2020-06-04 15:00MSSQL教程网 Sql Server

这篇文章主要介绍了数据库常用的sql语句汇总,需要的朋友可以参考下

SQL是目前使用最为广泛的数据库语言之一。这里，我总结了在数据库上，用SQL语言对数据排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。

数据库相关

查所有数据库 show databases;
创建数据库 create database 数据库名;
查看数据库 show create database 数据库名; //显示当初创建这个库的时候使用什么样的sql语句
创建数据库指定字符集 create database 数据库名 character set utf8/gbk
删除数据库 drop database 数据库名;
使用数据库 use 数据库名;

表相关

创建表 create table 表名(id int,name varchar(10)); //表名区分大小写
查看所有表 show tables;
查看单个表属性 show create table 表名; //使用的什么创建语句，可以在后面加\G使描述更清晰
查看表字段 desc 表名;
创建表指定引擎和字符集 create table 表名(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
删除表 drop table [if exists] 表名;删除表（可选择添加是否存在则删除）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF EXISTS `abc`;
CREATE TABLE `abc` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT comment'商品名称',
  `name` char(80) NOT NULL DEFAULT '' comment'商品名称',
  `title` char(20) NOT NULL DEFAULT '' comment'商品名称',
  `type` tinyint(1) NOT NULL DEFAULT '1' comment'商品名称',
  `condition` char(100) NOT NULL DEFAULT '' comment'商品名称',
  `show` bit DEFAULT 1 comment '是否可见',
  `price` decimal(5,2) not null comment '价格',
  `status` enum('0', '1', '2') NOT NULL DEFAULT '0' comment '状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

建立数据库：

CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

约束

not null 非空
default 默认约束语句，用于约束对应列中的值的默认值，除非默认值为空值，否则不可插入空值
unique 唯一约束语句，用于约束对应列中的值不能重复，可以有空值，但只能出现一个空值
primary 主键 = 唯一 + 非空
auto_increment 自动增长，用于系统自动生成字段的主键值
foreign key(从表id) reference 主表名（id）； 表与表之间建立联系

修改表

修改表名 rename table 旧表名 to 新表名;
修改表名 alter table 旧表名 rename 新表名
修改字段数据类型 alter table 表名 modify 字段名 数据类型
修改表属性 alter table 表名 engine=myisam/innodb charset=utf8/gbk;
添加表字段 alter table 表名 add 新字段名 新数据类型 [约束] [first/after 已存在字段名];
删除表字段 alter table 表名 drop 字段名;
修改表字段名和类型 alter table 表名 change 旧字段名 新字段名 类型;
修改表的类型和位置 alter table 表名 modify 字段名 类型 first/after 已存在字段名;
删除表 drop table 表名;
更改表的存储引擎 alter table 表名 engine = 新的存储引擎;
删除表的外键约束 alter table 表名 drop foreign key 外键名; //删除所有的外键之后，才能删除对应的主键所在的表

数据相关

插入数据：
insert into 表名 values(5,‘xiaoming',null);
insert into 表名 (字段名1,字段名2…) values (2,‘aa'…);
insert into 表名 values(5,‘xiaoming',null),(5,‘xiaoming',null),(5,‘xiaoming',null);
insert into 表名 (字段名1,字段名2) values (2,‘aa'),(2,‘aa'),(2,‘aa');

查询

select * from 表名;
select name from 表名;
select * from 表名 where id=10;

修改

update 表名 set 要修改的字段名=100 where 根据字段名=10;

删除

delete from 表名 where 字段名=10;

下面是补充

1.检索数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT prod_nameFROM Products;
#检索单列
 
SELECT prod_id, prod_name, prod_priceFROMProducts;
#检索多列
 
SELECT * FROM Products;
#检索所有列
 
SELECT DISTINCTvend_id FROMProducts;
#检索不同的值
 
SELECTprod_name FROM Products LIMIT 5;
#返回不超过5行数据
 
SELECTprod_name FROM Products LIMIT 5 OFFSET 5;
#返回从第5行起的5行数据。LIMIT指定返回的行数，LIMIT带的OFFSET指定从哪儿开始。
 
/* SELECT prod_name, vend_id
FROMProducts; */
SELECTprod_name
FROMProducts;
#多行注释

2.排序检索数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECTprod_name
FROMProducts
ORDER BYprod_name;
#排序数据
 
SELECT prod_id, prod_price, prod_name
FROMProducts
ORDER BY prod_price, prod_name;
#按多个列排序
 
SELECT prod_id, prod_price, prod_name
FROMProducts
ORDER BY 2, 3;
#按列位置排序，第三行表示先按prod_price, 再按prod_name进行排序
 
SELECT prod_id, prod_price, prod_name
FROMProducts
ORDER BY prod_priceDESC, prod_name;
#prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序

3.过滤数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT prod_name, prod_price
FROMProducts
WHERE prod_price< 10;
#检查单个值
 
SELECT prod_name, prod_price
FROMProducts
WHERE vend_id <> ‘DLL01';
#不匹配检查
 
SELECT prod_name, prod_price
FROMProducts
WHERE prod_priceBETWEEN 5 AND 10;
#范围值检查
 
SELECT cust_name
FROMCUSTOMERS
WHERE cust_emailIS NULL;
#空值检查

4.高级数据过滤

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECTprod_id, prod_price, prod_name
FROMProducts
WHERE vend_id = ‘DLL01'ANDprod_price <= 4;
#AND操作符
 
SELECTprod_name, prod_price
FROMProducts
WHEREvend_id='DLL01' OR vend_id='BRS01';
#OR操作符
 
SELECTprod_name, prod_price
FROMProducts
WHERE (vend_id = 'DLL01'ORvend_id='BRS01')
    ANDprod_price >= 10；
#求值顺序 AND的优先级高于OR
 
SELECTprod_name, prod_price
FROMProducts
WHERE vend_idIN (‘DLL01','BRS01')
ORDER BY prod_name;
#IN操作符
 
SELECT prod_name
FROMProducts
WHERE NOTvend_id = ‘DLL01'
ORDER BY prod_name;
#NOT 操作符
 
SELECT prod_name
FROMProducts
WHEREvend_id <> ‘DLL01'
ORDER BY prod_name;
#NOT 操作符

5.通配符进行过滤

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT prod_id, prod_name
FROMProducts
WHERE prod_nameLIKE ‘Fish%';
#%表示任何字符出现任意次数，找出所有以词Fish起头的产品
 
SELECT prod_id, prod_name
FROMProducts
WHERE prod_nameLIKE ‘%bean bag%';
#‘%bean bag%'表示匹配任何位置上包含文本bean bag的值，不论它在之前或之后出现什么字符
 
SELECT prod_name
FROMProducts
WHERE prod_nameLIKE ‘F%y';
#找出以F起头，以y结尾的所有产品

根据邮件地址的一部分来查找电子邮件，例如WHERE email LIKE ‘b%@forta.com'
 
WHERE prod_nameLIKE ‘%'; #不会匹配产品名称为NULL的行，其它均可
 
%代表搜索模式中给定位置的0个、1个或多个字符

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符

?
1
2
3
4
SELECT prod_id, prod_name
FROMProducts
WHERE prod_nameLIKE ‘__inchteddy bear';
#搜索模式要求匹配两个通配符而不是一个

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符

?
1
2
3
4
SELECT cust_contact
FROMCustomers
WHERE cust_contactLIKE ‘[JM]%'
ORDER BY cust_contact;

#[JM]匹配方括号中任意一个字符，它也只能匹配单个字符，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

?
1
2
3
4
5
SELECT cust_contact
FROMCustomers
WHERE cust_contactLIKE ‘[^JM]%'
ORDER BY cust_contact;
#以J和M之外的任意字符起头的任意联系人名

6.创建计算字段

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)')
FROMVendors
ORDER BY vend_name;
 
输出
Bear Emporium(USA)
Bears R Us (USA)
Doll House Inc.(USA)
Fun and Games(England)
 
SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)')
    ASvend_title
FROMVendors
ORDER BY vend_name; #给拼接而成新字段起了一个名称
 
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROMOrderItems
WHERE order_num = 20008;
#汇总物品的价格

7.使用函数处理数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT vend_name, UPPER(vend_name)AS vend_name_upcase
FROMVendors
ORDER BY vend_name;
#文本处理函数
 
SELECT cust_name, cust_contact
FROMCustomers
WHERE SOUNDEX(cust_contact) =SOUNDEX(‘MichaelGreen');
# SOUNDEX()函数搜索，匹配所有发音类似于Michael Green 的联系名
 
SELECT order_num
FROMOrders
WHERE YEAR(order_date) = 2012;
#从日期中提取年份

8.数据汇总

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT AVG(prod_price)ASavg_price
FROMProducts;
WHERE vend_id = ‘DLL01';
 
SELECT COUNT(*)ASnum_cust
FROMCustomers;
#COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值
 
SELECT COUNT(cust_email)ASnum_cust
FROMCustomers;
#只对具有电子邮件地址的客户计数
 
SELECT MAX(prod_price)ASmax_price
FROMProducts;
#返回Products表中最贵物品的价格
 
SELECT MIN(prod_price)ASmin_price
FROMProducts;
#返回Products表中最便宜物品的价格
 
SELECT SUM(quantity)ASitems_ordered
FROMOrderItems
WHERE order_num = 20005;
#SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品
 
SELECT SUM(item_price*quantity)AS total_price
FROMOrderItems
WHERE order_num = 20005;
#SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句保证只统计某个物品订单中的物品
 
SELECT AVG(DISTINCTprod_price)AS avg_price
FROMProducts
WHERE vend_id = ‘DLL01';
#使用DISTINCT参数，平均值只考虑各个不同的价格
 
SELECT COUNT(*) AS num_items,
    MIN(prod_price)AS price_min,
    MAX(prod_price)AS price_max,
    AVG(prod_price)AS price_avg
FROMProducts;
#组合聚集函数

9.分组数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT vend_id,COUNT(*) AS num_prods
FROMProducts
GROUP BY vend_id;
#创建分组
 
SELECT vend_id,COUNT(*) AS num_prods
FROMProducts
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
#WHERE 子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。
 
SELECT order_num,COUNT(*) AS items
FROMOrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
#按订购物品的数目排序输出

10.使用子查询

?
1
2
3
4
5
6
7
8
9
SELECT cust_id
FROMOrders
WHERE order_numIN (SELECT order_num
          FROM OrderItems
          WHERE prod_id = ‘RGAN01');
 
SELECT cust_name, cust_contact
FROMCustomers
WHERE cust_idIN (‘10000000004', ‘10000000005');

11.联结表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT vend_name, prod_name, prod_price
FROMVendors, Products
WHERE Vendors vend_id = Products.vend_id;
#创建联结
 
SELECT vend_name, prod_name, prod_price
FROMVendorsINNER JOIN Products
ONVendors.vend_id = Products.vend_id;
#内联结
 
SELECT prod_name, vend_name, prod_price, quantity
FROMOrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
ANDOrderItems.prod_id = Products.prod_id
ANDorder_num = 20007;
#联结多个表

12.创建高级联结

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROMCustomersAS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
ANDc2.cust_contact = ‘Jim Jones';
#自联结，此查询中需要的两个表实际上是相同的表
 
SELECT C. *, O.order_num, O.order_date,
    OI.prod_id, OI.quantity, OI.item_price
FROMCustomersAS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
ANDOI.order_num = O.order_num
ANDprod_id = ‘RGAN01';
#自然联结排除多次出现，使每一列只返回一次
 
SELECT Customers.cust_id, Orders.order_num
FROMCustomersLEFT OUTER JOIN Orders
ONCustomers.cust_id = Orders.cust_id;
#从FROM子句左边的表Customers表中选择所有行
 
SELECT Customers.cust_id, Orders.order_num
FROMCustomersRIGHT OUTER JOIN Orders
ONOrders.cust_id =Customers.cust_id;
#从右边的表中选择所有行。
 
SELECT Customers.cust_id, Orders.order_num
FROMOrdersFULL OUTER JOIN Customers
ONOrders.cust_id = Customers.cust_id;
#检索两个表中的所有行并关联那些可以关联的行

13.组合查询

?
1
2
3
4
5
6
7
8
9
SELECT cust_name, cust_contact, cust_email
FROMCustomers
WHERE cust_state IN (‘IL', ‘IN', ‘MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROMCustomers
WHERE cust_name = ‘Fun4ALL'
ORDER BY cust_name, cust_contact;
#SQL允许执行多个查询，并将结果作为一个查询结果集返回

14.插入数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
INSERT INTO Customers(cust_id,
            Cust_name,
            Cust_address,
            Cust_city,
            Cust_state,
            Cust_zip,
            Cust_country,
            Cust_contact,
            Cust_email)
VALUES(‘100000000006',
     ‘Toy Land',
     ‘123 Any Street',
     ‘New York',
     ‘NY',
     ‘111111',
     ‘USA',
     NULL,
     NULL);
#插入完整的行
 
INSERT INTO Customers(cust_id,
           Cust_contact,
           Cust_email,
           Cust_name,
           Cust_address,
           Cust_city,
           Cust_state,
           Cust_zip,
           Cust_country)
SELECT cust_id,
    Cust_contact,
    Cust_email,
    Cust_name,
    Cust_address,
    Cust_city,
    Cust_state,
    Cust_zip,
    Cust_country
FROMCustNew；
#将另一个表中的顾客列合并到Customers表中。
 
SELECT *
INTOCustCopy
FROMCustomers;
#从一个表复制到另一个表中

15.更新和删除数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
UPDATE Customers
SETcust_contact = ‘Sam Roberts',
Cust_email = ‘sam@toyland.com'
WHERE cust_id = ‘100000000000006';
#更新多个列
 
UPDATE Customers
SETcust_email = NULL
WHERE cust_id = ‘1000000005';
#删除某个列
 
DELETE FROM Customers
WHERE cust_id = ‘1000000006';
#删除数据

16. 创建和操纵表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE OrderItems
(
Order_num    INTEGER     NOT NULL,
Order_item    INTEGER     NOT NULL,
Prod_id      CHAR(10)     NOT NULL,
Quantity     INTEGER     NOT NULL     DEFAULT 1,
Item_price     DECIMAL(8, 2)  NOT NULL
);
 
ALTER TABLE Vendors
ADDvend_phone CHAR(20);
#给表增加一个名为vend_phone的列，其数据类型为CHAR
 
ALTER TABLE Vendors
DROP COLUMN vend_phone;
#该表中的某列
 
DROP TABLE CustCopy;
#删除表

17.高级SQL特性

主键：表中一列（或多个列）的值唯一标识表中的每一行。主键是一种特殊的约束，用来保证一列或一组列的值唯一标识表中的每一行。这方便直接或交互地处理表中的行。没有主键，要安全地UPDATE 或DELETE特定行而不影响其他行会非常困难。
①任意两行的主键值都不相同；
      ②每行都具有一个主键值（即列中不允许NULL值）
      ③包含主键值的列从不修改或更新。
      ④主键值不能重用

?
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE Vendors
(
Vend_id      CHAR(10)     NOT NULL PRIMARYKEY,
Vend_name     CHAR(50)     NOT NULL,
Vend_address    CHAR(50)     NULL,
Vend_city     CHAR(5)      NULL,
Vend_state     CHAR(10)     NULL,
Vend_zip      CHAR(10)     NULL,
Vend_country    CHAR(50)     NULL
);
 
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

 

#给表vend_id 列定义添加关键字PRIMARYKEY, 使其成为主键

  • 数据库
  • SQL语句

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 1355 　 3456 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 错误5123 　 SQL导入 　 sql导出 　 新增 　 sp_executesql 　 xp_cmdshell 　 Transact_SQL 　 手册 　 SQL Server2005 　 网络备份 　 日期查询 　 数据库开发 　 SQL Server 2000 　 经典语句 　 删除重复记录 　 收缩日志 　 排序规则 　 基本函数 　 加密函数 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图