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_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'); # 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_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.分组数据
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, 使其成为主键