MySQL 查询命令
当前数据库支持的存储引擎
show engines;
- 1
显示所有数据库
show databases;
- 1
使用指定数据库
use `Northwind`;
- 1
显示数据库的所有表
show tables;
- 1
显示指定表的结构
describe `OrderDetails`;
show columns from `OrderDetails`;
- 1
- 2
显示指定表的建表命令
show create table `OrderDetails`;
- 1
显示指定表的属性
show table status like 'OrderDetails';
- 1
显示指定表的索引
show index from `OrderDetails`;
show keys from `OrderDetails`;
- 1
- 2
查看数据库的表约束
select * from information_schema.`TABLE_CONSTRAINTS` where table_schema='northwind';
- 1
查询表的所有数据
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查询指定字段，设置别名
select `Title` from `Employees`;
select `Title`,`FirstName` from `Employees`;
select `Title` as `职称` from `Employees`; #指定别名
select `Title` `职称`,`FirstName` `名字` from `Employees`; #指定别名，省略as
- 1
- 2
- 3
- 4
排序查询结果
select * from `Categories` #默认正序
select * from `Categories` order by `CategoryID` asc #正序
select * from `Categories` order by `CategoryID` desc #倒序
- 1
- 2
- 3
限制查询条数
select * from `Categories` limit 2 #头2行
select * from `Categories` limit 2,2 #第2行后2行
select * from `Categories` order by `CategoryID` desc limit 2#倒数2行
- 1
- 2
- 3
集合函数
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; #求指定商品的平均价格
- 1
- 2
- 3
- 4
- 5
- 6
函数查询
select * from `Categories` where char_length(`CategoryName`)=2; #按所占字符数
select * from `Categories` where length(`PictureFile`)=7; #按所占字节数
- 1
- 2
条件查询
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';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
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; #在上面分组查询的基础上添加新的条件
- 1
- 2
- 3
- 4
- 5
- 6
- 7
子查询
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`;
- 1
- 2
- 3
联表查询
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`;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 交叉连接(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; #将两个或两个以上的查询结果合并
- 1
- 2
- 3
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`;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
视图查询
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`; #删除视图
- 1
- 2
- 3
用视图修改数据表的数据：
- 若视图字段来自表达式或常量，则只能进行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
