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