mysql常用命令（一）

登录MySQL：

-u表示登录用户名；-p表示登录密码；-P表示登录的端口号；-h表示连接的IP地址。

例如：mysql -uroot -P3306 -h39.x.x.1 -p 回车后输入密码

常用命令：

1、查看当前已有数据库；

show databases;

2、创建一个数据库；

create database testbook;

3、删除一个数据库；

drop database testbook;

4、使用某个数据库；

use testbook;

5、查看当前使用的database;

select database();

6、在testbook数据库下创建一个book数据表；

create table book(

  id int not null auto_increment,

  book_name varchar(100) not null,

  author varchar(40) not null,

  publish_date date,

  price float,

  primary key (id)

  )engine=innodb default charset=utf8;

7、删除testbook数据库中的一个table；

drop table user;

8、查看已创建的table结构；

desc book;

9、在名为book的table中插入数据；

insert into book values(1,"selenium","yzxn","2018-12-13",100);

10、查看book表单中插入的所有数据；

select * from book;

11、查看book表单中插入数据的条数总和;

select count(*) from book;

12、查看、删除指定字段条件的数据；

select * from book where id=100; select * from book where id=1 and author="yzxn";

delete from book;delete from book where id=4;delete from book where author="yzxn" and id=1;

delete from book;(删除整张表中的数据)

13、修改table表中某条数据的某个字段；

update book set book_name="new_name" where book_name="selenium";

14、通过table中某个或某些字段显示table中的一列数据；

select book_name from book; select author from book; select book_name,author from book;

15、使用table中某些字段的别名显示table中的一列数据；

select book_name as 书名, author as 作者 from book;

16、获取某字段的一行或多行数据；

select * from book where id=1;

select * from book where author="yzxn";

select * from book where id >=3;

select * from book where author <> 'yzxn';

17、获取table数据通过某一字段排序（升序、降序）后显示

select * from book order by price asc;

select * from book order by price desc;

18、table中的数据分组（group）

select count(*) from book group by author;

select count(*),author from book group by author;

19、计算table中某字段的平均值和总和

select avg(price), author from book group by author;

select count(*),author from book where id>3 group by author;

20、计算table中某group中总和大于200的数据；

select sum(price),author from book group by author having sum(price)>200;

select count(*),author from book where id>3 group by author;

21、查找指定范围的数据；

select * from student where id in (1,2);

select * from student where id not in (1,2);

22、子查询

select * from student where id in (select * from id where id>3);

23、新增两个数据表（内连接、左连接、右连接）

create table A(id int not null auto_increment,
name varchar(20) not null,
sex varchar(6),
salary varchar(20),
department_id int,
PRIMARY KEY (id)
)engine=innodb DEFAULT CHARSET=utf8;

create table department(
id int not null auto_increment,
name varchar(20) not null,
PRIMARY KEY (id)
)engine=innodb DEFAULT CHARSET=utf8;

内连接：

select person.name,depart.name from A as person inner join department as depart on person.department_id = depart.id;

左连接：

select person.name,depart.name from A as person left join department as depart on person.department_id=depart.id;

右连接：

select person.name,depart.name from A as person right join department as depart on person.department_id=depart.id;

显示所有字段：

select person.*,depart.* from A as person right/left join department as depart on person.department_id=depart.id;

24、union合并两个表的字段（如果有重复的数据，会自动排查），union all合并所有项（不会自动排重）

select name from A union select name from department;

select department_id from A union all select id from department;

25、查询已创建table的详细信息；

show create table emp \G;

转载于:https://my.oschina.net/u/4006483/blog/3078701