数据库常用的sql语句汇总

  • A+
所属分类:数据库技术

这篇文章主要介绍了数据库常用的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. DROP TABLE IF EXISTS `abc`;
  2. CREATE TABLE `abc` (
  3.   `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT comment'商品名称',
  4.   `name` char(80) NOT NULL DEFAULT '' comment'商品名称',
  5.   `title` char(20) NOT NULL DEFAULT '' comment'商品名称',
  6.   `type` tinyint(1) NOT NULL DEFAULT '1' comment'商品名称',
  7.   `condition` char(100) NOT NULL DEFAULT '' comment'商品名称',
  8.   `show` bit DEFAULT 1 comment '是否可见',
  9.   `price` decimal(5,2) not null comment '价格',
  10.   `status` enum('0', '1', '2') NOT NULL DEFAULT '0' comment '状态',
  11.   PRIMARY KEY (`id`),
  12.   UNIQUE KEY `name` (`name`)
  13. ) ENGINE=INNODB DEFAULT CHARSET=utf8;

建立数据库:

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

2.排序检索数据

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

3.过滤数据

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

4.高级数据过滤

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

5.通配符进行过滤

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

根据邮件地址的一部分来查找电子邮件,例如WHERE email LIKE ‘b%@forta.com'

WHERE prod_nameLIKE ‘%'; #不会匹配产品名称为NULL的行,其它均可

%代表搜索模式中给定位置的0个、1个或多个字符

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

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

方括号([])通配符用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符

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

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

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

6.创建计算字段

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

7.使用函数处理数据

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

8.数据汇总

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

9.分组数据

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

10.使用子查询

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

11.联结表

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

12.创建高级联结

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

13.组合查询

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

14.插入数据

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

15.更新和删除数据

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

16. 创建和操纵表

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

17.高级SQL特性

主键:表中一列(或多个列)的值唯一标识表中的每一行。主键是一种特殊的约束,用来保证一列或一组列的值唯一标识表中的每一行。这方便直接或交互地处理表中的行。没有主键,要安全地UPDATE 或DELETE特定行而不影响其他行会非常困难。

①任意两行的主键值都不相同;

②每行都具有一个主键值(即列中不允许NULL值)

③包含主键值的列从不修改或更新。

④主键值不能重用

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

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