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

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 ‘[e-mail]'
 
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 = ‘[e-mail]'
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, 使其成为主键