MySql常用命令总结

一. ubuntu下常用

1.显示现有的数据库

show databases;

2.显示当前所在的所在的数据库

select database();

3.使用xxx数据库

use xxx;

4.进入xxx数据库后，查看所有表

show tables;

二. 数据库操作

1.创建xxx数据库

create database xxx;

2.删除xxx数据库

drop database xxx charset=utf8;

3.切换到xxx数据库

use xxx;

4.查看当前选择的数据库

select database();

三. 表操作

1.常用的的数据类型

数字：int,decimal
字符串：varchar,text
日期：datetime
布尔：bit

2.约束条件

主键primary key
非空not null
惟一unique
默认default
外键foreign key

3.显示当前数据库中的所有表

show tables;

4.创建一个human表，包含ID，名字，性别，年龄，生日

create table human(
    id int auto_increment primary key,
    name varchar(10) not null,
    sex bit default 1,
    age int not null,
    birthday datetime
);

5.显示当前创建human表所有字段对应的信息

desc human;

//输出的信息如下：
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | NO   |     | NULL    |                |
| sex      | bit(1)      | YES  |     | b'1'    |                |
| age      | int(11)     | NO   |     | NULL    |                |
| birthday | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

6.显示当前yyy表所有的数据

select * from yyy;

7.删除当前yyy表

drop table yyy;

8.把当前yyy表改成zzz表

alter table yyy rename zzz;

9.向当前human表插入字段，方式1

insert into human values(0,'吕布',1,58,'235-5-2');

//一次插入多条
insert into human values(0,'吕布',1,58,'235-5-2'),(0,'刘备',1,68,'232-5-6');

10.向当前human表插入字段，方式2

insert into human(name,age,weight) values('张飞',47,66.7);

11.human添加表列，名为weight，约束为不为空

alter table human add  weight float not null;

12.human删除weight表列

alter table human drop weight;

13.human修改name表列类型

alter table human change name man  char(20);

14.根据条件删除，删除age<60

delete from human where age<60; 

15.通常删除并不是真正的删除，而是设置一个标志位isDelete，逻辑上删除

alter table human add isDelete bit default 0;
//当isDelete为1是默认是删除的，用户查询数据时过滤为1的，比如：
select * from human where isDelete=0;

16.update更新

update human set name="曹操" where id=1;

四. 备份与恢复

1.数据备份

//1. 进入超级管理员
sudo -s
//2. 进入mysql库目录
cd /var/lib/mysql
//3. 运行mysqldump命令
mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
按提示输入mysql的密码

1. 数据恢复

//1. 连接mysql，创建数据库
//2. 退出连接，执行如下命令（下面的数据库名就是步骤1创建的数据库名）
mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
根据提示输入mysql密码

五. 查询

根据这个表中的数据来讲解：

mysql> desc human;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | NO   |     | NULL    |                |
| sex      | bit(1)      | YES  |     | b'1'    |                |
| age      | int(11)     | NO   |     | NULL    |                |
| birthday | datetime    | YES  |     | NULL    |                |
| isDelete | bit(1)      | YES  |     | b'0'    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

1.查询xx表所有数据

select * from human;

//显示现有的数据：
mysql> select * from human;
+----+---------+------+-----+---------------------+----------+
| id | name    | sex  | age | birthday            | isDelete |
+----+---------+------+-----+---------------------+----------+
|  4 | 刘备    | 1    |  68 | 0232-05-06 00:00:00 |          |
|  5 | 吕布    | 1    |  58 | 0235-05-02 00:00:00 |          |
|  6 | 刘备    | 1    |  68 | 0232-05-06 00:00:00 |          |
+----+---------+------+-----+---------------------+----------+

2.查询时消除重复行,sex为xx表中的性别属性0/1

select distinct sex from human;

3.根据条件来查询

select * from human where 条件;

3.1 比较运算符，> < = 或组合

select * from human where id<5;

select * from human where name!="吕布";

3.2 逻辑运算符，and or not

select * from human where id>4 and isdelete=0;

3.3 模糊查询，like % _ 或组合

//1. 查找姓吕的
select * from human where name like '吕%';

//2. 查找含有“备”字的：
select * from human where name like '%备%';

//3. 查询姓吕并且名字是一个字的人：
select * from human where name like '吕_';

3.4 范围查询，in between … and …

select * from human where id in(1,3,8);

select * from human where id between 3 and 8;

select * from human where id between 3 and 8 and sex=1;

3.5 空判断 is （not）null

select * from human where birthday is not null;

3.6 优先级 小括号>not>比较运算符>逻辑运算符

4.聚合为查询结果显示

4.1 查询总数

select count(*) from human;

select count(name) from human;

4.2 查询最大值

select max(age) from human;

4.3 查询最小值

select min(id) from human where isdelete=0;

4.4 查询总和

select avg(age) from human where isdelete=0 and sex=0;

4.5 查询平均值

select avg(age) from human where isdelete=0 and sex=1;

5.分组查询

按照字段分组，表示此字段相同的数据会被放到一个组中；分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中；可以对分组后的数据进行统计，做聚合运算

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

5.1查询男女生总数,as 性别–>别名

select sex as 性别,count(*)
from human
group by sex;

5.2 having后面的条件运算符与where的相同，查询男生总人数

//方案一
select count(*)
from human
where sex=1;

//方案二：
select sex as 性别,count(*)
from human
group by sex
having sex=1;

6.排序 asc升序,desc降序:

select * from human
where sex=1 and isdelete=0
order by id desc;

7.分页获取, 从下标0开始，获取3条数据

select * from human limit 0,3;

六. 多表操作

1.创建三个表，students,subjects,scores

//学生
create table students(
    id int auto_increment primary key not null,
    name varchar(10) not null,
    sex bit default 1
);

//学科
create table subjects(
    id int auto_increment primary key not null,
    name varchar(20) not null
);

//分数,最后一条为关联外键的写法
create table scores(
    id int auto_increment primary key not null,
    score decimal(5,2) not null,
    stuid int,
    subid int,
    foreign key(stuid) references students(id)
);

2.动态插入外键

alter table scores add foreign key(subid) references subjects(id);

//添加外键名字sco_sub，可以根据名字把外键删除掉，通常不删
alter table scores add constraint sco_sub foreign key(subid) references subjects(id);

3.插入多条数据

//students
insert into students values(0,"张三",1),(0,"李四",1),(0,"王五",1),(0,"赵柳",0);

//subjects
insert into subjects(name) values("语文"),("数学"),("英语");

//scores, (0,89.5,1,1)->(id,score,stuid=students(id),subid=sujects(id))，stuid要students中存在的id，subid同理
insert into scores values(0,89.5,1,1),(0,96,1,2),(0,85,1,3),
(0,93.5,2,1),(0,88,2,2),(0,95,2,3),
(0,79,3,1),(0,66,3,2),(0,77,3,3),
(0,55.5,4,1),(0,58,4,2),(0,65,4,3);

3.查询

//1. 查询全部
select * from scores 
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

//2. 根据字段查询，
select students.name,subjects.name,score
from scores
inner join students on students.id=scores.stuid
inner join subjects on subjects.id=scores.subid;

//3. 聚合分组查询，查平均分
select students.name,avg(score)
from scores
inner join students on scores.stuid=students.id
group by students.name;

//4. 聚合分组+条件查询，男的总分; 此处scores.score可以省略scores，因为后面查询的是from scores
select students.name,sum(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.sex=1
group by students.name;

4.视图, 用于结合多表后查询

//1. 创建, 因为students.*里面的字段包含（id,name,sex）和subjects.name重名了，所以要起别名
create view v_1 as
select students.*,subjects.name as title,scores.score from scores
inner join students on students.id=scores.stuid
inner join subjects on subjects.id=scores.subid;

//起别名写法， 表名 as 表别名
create view v_1 as
select stu.*,sub.name as title,sco.score from scores as sco
inner join students as stu on stu.id=sco.stuid
inner join subjects as sub on sub.id=sco.subid;

//2. 查询全部
select * from v_1;

//3. 查询视图中姓名和性别
select name,sex from v_1 where score>70;

5.业务，银行存取，支付等

//1. 开始事物
begin;

//2. 操作过程，比如更新/插入字段: 执行后内存发生变化，但硬件内存没有生效
update students set name='陈二' where id=1;

//3. 提交（或回退=取消）
commit;
//回退
rollback;

6.自关联（多表做成一张表，减少多表的开销）

//1. 创建
create table areas(
    id int primary key,
    atitle varchar(20),
    pid int,
    foreign key(pid) references areas(id)
);

//2. 从sql文件中导入数据
source areas.sql;

//3. 查询一共有多少个省，查询省的名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';

//4. 查询市的名称为“广州市”的所有区县
select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='广州市';

七. 服务器上MySQL

1.安装

sudo apt-get install mysql-server mysql-client

2.管理服务

//1. 启动
service mysql start

//2. 停止
service mysql stop

//3. 重启
service mysql restart

3.允许远程连接服务

//1. 找到mysql配置文件, 将bind-address=127.0.0.1注释
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

//2. 登录mysql
mysql -uroot -p
请输入密码：mysql

//3. 执行以下两条命令
grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;

//4. 重启MySQL