数据库常用的sql语句汇总

数据运维 2023-04-21 张二河 手机阅读

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

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

数据库相关

查所有数据库 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] 表名;删除表（可选择添加是否存在则删除）

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.检索数据

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.排序检索数据

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.过滤数据

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.高级数据过滤

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.通配符进行过滤

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个或多个字符

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

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

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

SELECT cust_contact FROMCustomers WHERE cust_contactLIKE ‘[JM]%' ORDER BY cust_contact;

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

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

6.创建计算字段

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.使用函数处理数据

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');

  1. SOUNDEX()函数搜索，匹配所有发音类似于Michael Green 的联系名

SELECT order_num FROMOrders WHERE YEAR(order_date) = 2012; #从日期中提取年份

8.数据汇总

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_pricequantity)AS total_price FROMOrderItems WHERE order_num = 20005; #SUM(item_pricequantity)返回订单中所有物品价钱之和，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.分组数据

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.使用子查询

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.联结表

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.创建高级联结

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.组合查询

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.插入数据

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.更新和删除数据

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. 创建和操纵表

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值）       ③包含主键值的列从不修改或更新。       ④主键值不能重用

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, 使其成为主键