MySQL 查询命令

当前数据库支持的存储引擎

show engines;

显示所有数据库

show databases;

使用指定数据库

use `Northwind`;

显示数据库的所有表

show tables;

显示指定表的结构

describe `OrderDetails`;show columns from `OrderDetails`;

显示指定表的建表命令

show create table `OrderDetails`;

显示指定表的属性

show table status like 'OrderDetails';

显示指定表的索引

show index from `OrderDetails`;show keys from `OrderDetails`;

查看数据库的表约束

select * from information_schema.`TABLE_CONSTRAINTS` where table_schema='northwind';

查询表的所有数据

select * from `Categories`; #商品种类select * from `Suppliers`; #供应厂商select * from `Products`; #商品信息select * from `Customers`; #客户信息select * from `Employees`; #员工信息select * from `Shippers`; #货运公司select * from `Orders`; #订单信息select * from `OrderDetails`; #订单详情select * from `Reports`; #报表配置(1)

查询指定字段，设置别名

select `Title` from `Employees`;select `Title`,`FirstName` from `Employees`;select `Title` as `职称` from `Employees`; #指定别名select `Title` `职称`,`FirstName` `名字` from `Employees`; #指定别名，省略as

排序查询结果

select * from `Categories` #默认正序select * from `Categories` order by `CategoryID` asc #正序select * from `Categories` order by `CategoryID` desc #倒序

限制查询条数

select * from `Categories` limit 2 #头2行select * from `Categories` limit 2,2 #第2行后2行select * from `Categories` order by `CategoryID` desc limit 2#倒数2行

集合函数

select count(*) `记录总数` from `Categories`; #计算总数select `UnitPrice`,`UnitPrice`+10 `结果值` from `OrderDetails`; #查询结果计算select max(`CategoryID`) from `Categories`; #求一列的最大值select min(`CategoryID`) from `Categories`; #求一列的最小值select avg(`UnitPrice`) `平均价格` from `Products`; #求所有商品的平均价格select avg(`UnitPrice`) from `Products` where `ProductID`<=3; #求指定商品的平均价格

函数查询

select * from `Categories` where char_length(`CategoryName`)=2; #按所占字符数select * from `Categories` where length(`PictureFile`)=7; #按所占字节数

条件查询

select * from `Categories` where `CategoryID`=2;select * from `Categories` where `CategoryID`<>2;select * from `Categories` where `CategoryID`!=2;select * from `Categories` where `CategoryID` in(2,4,6);select * from `Categories` where `CategoryID` not in(2,4,6);select * from `Categories` where `CategoryID`>3;select * from `Categories` where `CategoryID`>=3 and `CategoryID`<6;select * from `Categories` where `CategoryID`>=3 and `CategoryID`<6 and `CategoryID`<>4;select * from `Categories` where `CategoryID`<3 or `CategoryID`>6;select * from `Categories` where `CategoryID`<3 or `CategoryID`>6 or `CategoryID`=5;select * from `Categories` where `CategoryID` between 3 and 5;select * from `Categories` where `CategoryID` not between 3 and 5;select * from `Categories` where `CategoryID` not between 3 and 5 and `CategoryID` not in(1,2);select * from `Suppliers` where `Fax` is null;select * from `Suppliers` where `Fax` is not null;select * from `Categories` where `CategoryName`='谷类/麦片';select * from `Categories` where `CategoryName` like '_类/麦片';select * from `Categories` where `CategoryName` like '__类/麦片';select * from `Categories` where `CategoryName` like '%/麦片';select * from `Categories` where `CategoryName` like '谷类/%';select * from `Categories` where `CategoryName` like '%/%';select * from `Orders` where `OrderDate`='1996-07-04';select * from `Orders` where `OrderDate`>='1996-01-01' and `OrderDate`<'1997-01-01';select * from `Orders` where `OrderDate` between '1996-01-01' and '1996-12-31 23:59:59';

MySQL 通配符：

1. %，包含0个或多个字符的任意字符；

2. _，任何单个字符。

分组查询

select distinct `ProductID` from `OrderDetails`; #出现过的ProductID（查询结果不会有重复的值）select `ProductID`,count(`ProductID`) `订单数量`,sum(`Quantity`) `该类总量` from `OrderDetails` group by `ProductID`; #按ProductID分组，并求得每种的出现次数，与该种类的数量总和select `ProductID`,count(`ProductID`) 订单数量,sum(`Quantity`) 该类总量 from `OrderDetails` group by `ProductID` having sum(`Quantity`)<200; #在上面分组查询的基础上添加新的条件select `ProductID`,count(`ProductID`) 订单数量,sum(`Quantity`) `该类总量 from OrderDetails group by ProductID` having sum(`Quantity`)<200 and `ProductID`<>15; #在上面分组查询的基础上添加新的条件

子查询

select * from `Products` where `SupplierID` in(select `SupplierID` from `Suppliers` where `City`='上海');select `Tab1`.`CompanyName` from (select * from `Suppliers` where `City`='上海') as `Tab1`;select `CompanyName` from (select * from `Suppliers` where `City`='上海') as `Tab1`;

联表查询

select     `P`.`CategoryID`,    `C`.`CategoryName`,    `P`.`ProductID`,    `P`.`ProductName`,    `P`.`QuantityPerUnit`,    `P`.`UnitPrice`,    `P`.`UnitsInStock`     from `Products` `P`     join `Categories` `C`     on `P`.`CategoryID`=`C`.`CategoryID`;

交叉连接(cross join)：将两个表不加任何约束地组合起来，在实际应用中一般没有意义；

内连接(自然连接)([inner] join)：将交叉连接按照连接条件进行过滤，匹配的才能出现在结果集，通常采用主键=外键的形式；

外连接：和内连接的不同是，不匹配条件的行也能出现在结果集，对应的空位会被填上NULL，
- 左外连接(left join, left outer join)，对左表不加限制；
- 右外连接(right join, right outer join)，对右表不加限制；
- 全外连接(full join, full outer join)，对左右两表都不加限制。

合并查询

select `CategoryID`,`CategoryName` from `Categories` where `CategoryID`<=4 union select `CategoryID`,`CategoryName` from `Categories` where `CategoryID`>4; #将两个或两个以上的查询结果合并select `CategoryID`,`CategoryName` from `Categories` where `CategoryID`<=4 union all select `CategoryID`,`CategoryName` from `Categories` where `CategoryID`>4; #将两个或两个以上的查询结果合并

case查询，concat()函数参数可以是查询结果，也可以是字符串常量

select concat(`LastName`,`FirstName`) as `姓名`,`TitleOfCourtesy` as `称谓` from `Employees`;select concat(`LastName`,`FirstName`) `姓名`,case `Gender`when 0 then '女'when 1 then '男' end as `性别` from `Employees`;select concat(`LastName`,`FirstName`) `姓名`,case `TitleOfCourtesy`when '女士' then '女孩' when '先生' then '男孩' else '未知' end as `称谓` from `Employees`;

视图查询

create view `Categories_Products` as select `P`.`CategoryID`,`C`.`CategoryName`,`P`.`ProductID`,`P`.`ProductName`,`P`.`QuantityPerUnit`,`P`.`UnitPrice`,`P`.`UnitsInStock` from `Products` `P` join `Categories` `C` on `P`.`CategoryID`=`C`.`CategoryID`; #创建视图select * from `Categories_Products`; #查询视图drop view `Categories_Products`; #删除视图

用视图修改数据表的数据：

若视图字段来自表达式或常量，则只能进行delete操作；

若视图字段来自集合函数，则不允许修改操作；

若视图定义中含group by子句，则不允许修改操作；

若视图定义中含有distinct短语，则不允许修改操作；

在一个不允许修改操作视图上定义的视图，不允许修改操作。

修改与删除

update `Categories` set `CategoryName`='牛奶2' where `CategoryID`=2;update `Categories` set CategoryName='牛奶' where `CategoryID`=2;update `Categories` set CategoryName='牛奶2',`Description`='暂无描述' where `CategoryID`=2;delete from `Categories` where `CategoryID`=2;delete from `OrderDetails` where `OrderID`>10470;delete from `Categories`; #删除指定表内全部数据：有删除记录，可恢复truncate `orderdetails`; #删除指定表内全部数据：无删除记录，不可恢复原文地址https://blog.csdn.net/petezh/article/details/81510778