数据库常用的sql语句汇总

站长资源 2024/11/23 佚名

18 1538 18

DDR爱好者之家 Design By 杰米

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, 使其成为主键

sql语句

DDR爱好者之家 Design By 杰米

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

DDR爱好者之家 Design By 杰米

评论“数据库常用的sql语句汇总”

暂无评论...

数据库常用的sql语句汇总

SQL Server 日期和时间的内部存储过程

sql server日志处理不当造成的隐患详解

评论“数据库常用的sql语句汇总”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

数据库常用的sql语句汇总

SQL Server 日期和时间的内部存储过程

sql server日志处理不当造成的隐患详解

评论“数据库常用的sql语句汇总”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存