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] 表名;删除表（可选择添加是否存在则删除）
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_idFROMProducts; */SELECTprod_nameFROMProducts;#多行注释
2.排序检索数据
SELECTprod_nameFROMProductsORDER BYprod_name;#排序数据 SELECT prod_id, prod_price, prod_nameFROMProductsORDER BY prod_price, prod_name;#按多个列排序 SELECT prod_id, prod_price, prod_nameFROMProductsORDER BY 2, 3;#按列位置排序，第三行表示先按prod_price, 再按prod_name进行排序 SELECT prod_id, prod_price, prod_nameFROMProductsORDER BY prod_priceDESC, prod_name;#prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序
3.过滤数据
SELECT prod_name, prod_priceFROMProductsWHERE prod_price< 10;#检查单个值 SELECT prod_name, prod_priceFROMProductsWHERE vend_id <> ‘DLL01';#不匹配检查 SELECT prod_name, prod_priceFROMProductsWHERE prod_priceBETWEEN 5 AND 10;#范围值检查 SELECT cust_nameFROMCUSTOMERSWHERE cust_emailIS NULL;#空值检查
4.高级数据过滤
SELECTprod_id, prod_price, prod_nameFROMProductsWHERE vend_id = ‘DLL01'ANDprod_price <= 4;#AND操作符 SELECTprod_name, prod_priceFROMProductsWHEREvend_id='DLL01' OR vend_id='BRS01';#OR操作符 SELECTprod_name, prod_priceFROMProductsWHERE (vend_id = 'DLL01'ORvend_id='BRS01') ANDprod_price >= 10；#求值顺序 AND的优先级高于OR SELECTprod_name, prod_priceFROMProductsWHERE vend_idIN (‘DLL01','BRS01')ORDER BY prod_name;#IN操作符 SELECT prod_nameFROMProductsWHERE NOTvend_id = ‘DLL01'ORDER BY prod_name;#NOT 操作符 SELECT prod_nameFROMProductsWHEREvend_id <> ‘DLL01'ORDER BY prod_name;#NOT 操作符
5.通配符进行过滤
SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE ‘Fish%';#%表示任何字符出现任意次数，找出所有以词Fish起头的产品 SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE ‘%bean bag%';#‘%bean bag%'表示匹配任何位置上包含文本bean bag的值，不论它在之前或之后出现什么字符 SELECT prod_nameFROMProductsWHERE prod_nameLIKE ‘F%y';#找出以F起头，以y结尾的所有产品
根据邮件地址的一部分来查找电子邮件，例如WHERE email LIKE ‘b%@forta.com'
WHERE prod_nameLIKE ‘%'; #不会匹配产品名称为NULL的行，其它均可
%代表搜索模式中给定位置的0个、1个或多个字符
下划线的用途与%一样，但它只匹配单个字符，而不是多个字符
SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE ‘__inchteddy bear';#搜索模式要求匹配两个通配符而不是一个
方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符
SELECT cust_contactFROMCustomersWHERE cust_contactLIKE ‘[JM]%'ORDER BY cust_contact;
#[JM]匹配方括号中任意一个字符，它也只能匹配单个字符，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。
SELECT cust_contactFROMCustomersWHERE cust_contactLIKE ‘[^JM]%'ORDER BY cust_contact;#以J和M之外的任意字符起头的任意联系人名
6.创建计算字段
SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)')FROMVendorsORDER 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_titleFROMVendorsORDER BY vend_name; #给拼接而成新字段起了一个名称 SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_priceFROMOrderItemsWHERE order_num = 20008;#汇总物品的价格
7.使用函数处理数据
SELECT vend_name, UPPER(vend_name)AS vend_name_upcaseFROMVendorsORDER BY vend_name;#文本处理函数 SELECT cust_name, cust_contactFROMCustomersWHERE SOUNDEX(cust_contact) =SOUNDEX(‘MichaelGreen');# SOUNDEX()函数搜索，匹配所有发音类似于Michael Green 的联系名 SELECT order_numFROMOrdersWHERE YEAR(order_date) = 2012;#从日期中提取年份
8.数据汇总
SELECT AVG(prod_price)ASavg_priceFROMProducts;WHERE vend_id = ‘DLL01'; SELECT COUNT(*)ASnum_custFROMCustomers;#COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值 SELECT COUNT(cust_email)ASnum_custFROMCustomers;#只对具有电子邮件地址的客户计数 SELECT MAX(prod_price)ASmax_priceFROMProducts;#返回Products表中最贵物品的价格 SELECT MIN(prod_price)ASmin_priceFROMProducts;#返回Products表中最便宜物品的价格 SELECT SUM(quantity)ASitems_orderedFROMOrderItemsWHERE order_num = 20005;#SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品 SELECT SUM(item_price*quantity)AS total_priceFROMOrderItemsWHERE order_num = 20005;#SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句保证只统计某个物品订单中的物品 SELECT AVG(DISTINCTprod_price)AS avg_priceFROMProductsWHERE 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_avgFROMProducts;#组合聚集函数
9.分组数据
SELECT vend_id,COUNT(*) AS num_prodsFROMProductsGROUP BY vend_id;#创建分组 SELECT vend_id,COUNT(*) AS num_prodsFROMProductsWHERE prod_price >= 4GROUP BY vend_idHAVING COUNT(*) >= 2;#WHERE 子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。 SELECT order_num,COUNT(*) AS itemsFROMOrderItemsGROUP BY order_numHAVING COUNT(*) >= 3ORDER BY items, order_num;#按订购物品的数目排序输出
10.使用子查询
SELECT cust_idFROMOrdersWHERE order_numIN (SELECT order_num FROM OrderItems WHERE prod_id = ‘RGAN01'); SELECT cust_name, cust_contactFROMCustomersWHERE cust_idIN (‘10000000004', ‘10000000005');
11.联结表
SELECT vend_name, prod_name, prod_priceFROMVendors, ProductsWHERE Vendors vend_id = Products.vend_id;#创建联结 SELECT vend_name, prod_name, prod_priceFROMVendorsINNER JOIN ProductsONVendors.vend_id = Products.vend_id;#内联结 SELECT prod_name, vend_name, prod_price, quantityFROMOrderItems, Products, VendorsWHERE Products.vend_id = Vendors.vend_idANDOrderItems.prod_id = Products.prod_idANDorder_num = 20007;#联结多个表
12.创建高级联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contactFROMCustomersAS c1, Customers AS c2WHERE c1.cust_name = c2.cust_nameANDc2.cust_contact = ‘Jim Jones';#自联结，此查询中需要的两个表实际上是相同的表 SELECT C. *, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_priceFROMCustomersAS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_idANDOI.order_num = O.order_numANDprod_id = ‘RGAN01';#自然联结排除多次出现，使每一列只返回一次 SELECT Customers.cust_id, Orders.order_numFROMCustomersLEFT OUTER JOIN OrdersONCustomers.cust_id = Orders.cust_id;#从FROM子句左边的表Customers表中选择所有行 SELECT Customers.cust_id, Orders.order_numFROMCustomersRIGHT OUTER JOIN OrdersONOrders.cust_id =Customers.cust_id;#从右边的表中选择所有行。 SELECT Customers.cust_id, Orders.order_numFROMOrdersFULL OUTER JOIN CustomersONOrders.cust_id = Customers.cust_id;#检索两个表中的所有行并关联那些可以关联的行
13.组合查询
SELECT cust_name, cust_contact, cust_emailFROMCustomersWHERE cust_state IN (‘IL', ‘IN', ‘MI')UNIONSELECT cust_name, cust_contact, cust_emailFROMCustomersWHERE 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_countryFROMCustNew；#将另一个表中的顾客列合并到Customers表中。 SELECT *INTOCustCopyFROMCustomers;#从一个表复制到另一个表中
15.更新和删除数据
UPDATE CustomersSETcust_contact = ‘Sam Roberts',Cust_email = ‘sam@toyland.com'WHERE cust_id = ‘100000000000006';#更新多个列 UPDATE CustomersSETcust_email = NULLWHERE cust_id = ‘1000000005';#删除某个列 DELETE FROM CustomersWHERE 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 VendorsADDvend_phone CHAR(20);#给表增加一个名为vend_phone的列，其数据类型为CHAR ALTER TABLE VendorsDROP 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 VendorsADD CONSTRAINT PRIMARY KEY (vend_id);
#给表vend_id 列定义添加关键字PRIMARYKEY, 使其成为主键
新闻热点
疑难解答
