登录数据库服务器
mysql -uusername -ppassword

查询所有数据库
show databases;

创建数据库
create table table_name;

选中要操作的数据库
use database_name;

查看数据库中所有数据表
show tables;

导出数据库
mysqldump -u root -p database_name> export_name.sql

导入数据库
mysql -u root -p database_name < import_name.sql

查询字段属于哪张表
select table_schema,table_name from information_schema.columns where column_name = '字段名字';

清空表格
truncate table table_name;

查看表结构
desc table_name; describe table_name;

修改列属性
alter table table_name change url url varchar(500) character set utf8 not null;

退出数据库服务器
exit;

查询表中所有数据
select * from table_name;

查询表中指定字段
select id from table_name;

查询表中指定字段并去除重复值
select distinct id from table_name;

统计表中某一字段
select count(id) from table_name;

排序(默认是升序asc，降序是desc)
select * from table_name order by id desc;
select * from table_name order by id desc,no asc;

找到某一字段最大值
select max(id) from table_name;
select id from table_name order by id desc limit 0,1;

计算平均值
select avg(id) from table_name;
select avg(id) from table_name where sex='男';

计算各个条件的平均值(用到了分组)
select avg(id) from table_name group by sex;

筛选字段区间
select id from table_name where id > 1 and id < 10;
select id from table_name where id between 1 and 10;

筛选字段指定特殊字段
select id from table_name where id = 1 or id = 10;
select id from table_name where id in (1,10);

给查询结果取别名
select id as ID from table_name;

求两种查询结果的并集
select id from table_name where id = 1 union select id from table_name where id = 2;

选出任何、所有
select name from students1 where score > any(select score from students2);
select name from students1 where score > all(select score from students2);

求字段与某值的差
select name,id-10 from table_name;