MySQL 查询命令

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL 查询命令

推荐码发放 2018-08-09 08:57:39 浏览695
展开阅读全文

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 #头2select * from `Categories` limit 2,2 #第2行后2select * 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
  1. 交叉连接(cross join):将两个表不加任何约束地组合起来,在实际应用中一般没有意义;
  2. 内连接(自然连接)([inner] join):将交叉连接按照连接条件进行过滤,匹配的才能出现在结果集,通常采用主键=外键的形式;
  3. 外连接:和内连接的不同是,不匹配条件的行也能出现在结果集,对应的空位会被填上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

网友评论

作者关闭了评论
推荐码发放
+ 关注