数据库常用的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 ‘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, 使其成为主键


Notice: Undefined variable: cao_ver in /data/wwwroot/www.lingkb.com/wp-content/plugins/riprodl/old.php on line 409