一文让你彻底弄懂 MySQL 常用语句，值得收藏！

这篇文章主要记录了一些 MySQL 常用的一些语句，方便刚入门的小伙伴快速入门，希望对你们有所帮助。

1. 关于备份数据库

register 数据库中有 user 表和 logs 表

备份整个数据库

mysqldump -uroot -p register > register.sql
// 备份register数据库并生成register.sql数据库文件

备份数据库中单张表

mysqldump -u root -p register user > backups.sql
// 备份register数据库中user表到backups.sql数据库文件中

将sql文件写入数据库中

mysql -u root -p register < backups.sql
// 将backups.sql文件写入到register数据库中，这里请注意：mysql -u root -p 后面只能跟数据库，而不能跟sql文件!
这里backups.sql中如果只含有user中的数据，那么执行这个写入过程的时候，不会对logs有任何影响。

导出数据库的表结构(如果数据库中含有数据，不会导出数据，只会导出字段名)

mysqldump -d -uroot -p register user > user.sql
// 将register数据库中的user表结构导出

备份所有的数据库

mysqldump -u root -p --all-databases > database_dump.txt
// 将所有的数据库都备份到database_dump.txt文件中去

复制表

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。

// 显示创建 user 表的 sql 语句
SHOW CREATE TABLE workflow_oa


CREATE TABLE goods_image_backup LIKE goods_image;
INSERT INTO goods_image_backup SELECT * FROM goods_image;

2. 常用命令

连接数据库

mysql -h[主机地址] -u[用户名] -p[用户密码] 

//举例：连接本地数据库 mysql -h 127.0.0.1 -u root -p 123456 (有些情况下可以不需要输入主机地址):mysql -u root -p

创建数据库

create database [库名];

//举例：创建thinkcms数据库 create database thinkcms;

create database [库名] charset utf8; //创建数据库并存储字符集为utf8

create database [库名] DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8编码

create database [库名] DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk编码

显示所有数据库

show databases;

打开数据库

use [库名];

//举例：使用thinkcms数据库 use thinkcms

删除数据库

drop database [库名]; 

//举例：删除thinkcms数据库 drop database thinkcms

创建数据表

CREATE TABLE [表名] ([字段名] [字段类型] [字段参数]);

//举例：创建reg数据表并将id设置成自增主键，并将innodb设置成存储引擎，字符集设置成utf8
mysql> CREATE TABLE reg(
   -> id INT NOT NULL AUTO_INCREMENT,
   -> user VARCHAR(100) NOT NULL,
   -> pwd VARCHAR(40) NOT NULL,
   -> create_time DATE,
   -> PRIMARY KEY (id)
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

显示数据表字段名（以及字段名备注）

desc [表名];    （或者使用：show columns from [表名];)

// 举例：显示reg表的字段名 
desc reg;  
show columns from reg;  
// 显示字段名包含备注
show full columns from reg;

显示当前数据库下所有的数据表

show tables;

删除数据表

drop table [表名];

//举例：删除reg表 drop table reg;

清空表内容

truncate [表名];

//举例：清除 reg 表中所有内容， truncate reg;

对数据表重命名

alter table [旧表名] rename as [新表名];

//举例：将register表名称修改为users
alter table register rename as users;

修改表

• 添加列：

alter table 表名 add 列名 类型
// 举例：ALTER TABLE menus ADD status TINYINT(1) DEFAULT 1 COMMENT '是否显示'

• 删除列：

alter table 表名 drop column 列名

• 修改列：

alter table 表名 modify column 列名 类型;  -- 类型

alter table 表名 change column 原列名 新列名 类型; -- 列名，类型
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

• 添加主键：

alter table 表名 add primary key(列名);

• 删除主键：

alter table 表名 drop primary key;

alter table 表名  modify  列名 int, drop primary key;

• 添加外键：

alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);

// 例子
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）

• 删除外键：

alter table 表名 drop foreign key 外键名称

// 例子 （不会删除外键字段）
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

• 关闭外键约束：

// 关闭外键约束
SET FOREIGN_KEY_CHECKS=0;

// 开启外键约束
SET FOREIGN_KEY_CHECKS=1;

// 查询外键约束状态
select @@FOREIGN_KEY_CHECKS;

• 修改默认值：

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

• 删除默认值：

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

对数据表的 CURD

插入数据：
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
                       
//举例：在register表中插入一行数据：
insert into register (`user`,`time`,`pwd`) values ('小明', '2016-04-22 15:25:33', '1');
//插入多行数据：
INSERT INTO employee_tbl (`pid`,`user`,`time`,`pwd`) VALUES 
('1', '小明', '2016-04-22 15:25:33', '1'), 
('2', '小王', '2016-04-20 15:25:47', '3'), 
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'),
('6', '小明', '2016-04-04 15:26:54', '2');

更新数据：
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];

//举例：在register表中更新第一条数据：
update register set user="王五",pwd=555 where id=1;
// 所有80分以下的同学成绩加10分
UPDATE students SET score=score+10 WHERE score<80;

删除数据：
DELETE FROM table_name [WHERE Clause];

//举例：在register表中删除第一条数据：
delete from register where id=1;

查询语句：
SELECT column_name,column_name
FROM table_name
[WHERE Clause];

=====================where 条件=====================
//举例：在register表中查询id=1的数据：
select * from register where id=1;

select * from 表 where id > 1 and name != 'aylin' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)

=====================通配符 like=====================
//在register表中查询所有名字name含有张三的人
select * from register where name like "%张三%";
select * from register where name like '张三_'  # 张三开头的所有（一个字符）

=====================限制 limit=====================
select * from 表 limit 5;            - 前5行
select * from 表 limit 4,5;          - 从第4行开始的5行
select * from 表 limit 5 offset 4    - 从第4行开始的5行

=====================排序 asc，desc=====================
select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

=====================分组 group by=====================
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表  where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
select num from 表 group by num having max(id) > 10

特别的：group by 必须在where之后，order by之前

3. 其它简单命令

查询时间：

select now();

查询当前用户：

select user();

查询数据库版本：

// 方法01、命令行中直接敲以下命令
mysql -V (大写的V)
// 方法02、命令行中输入以下命令
mysql --help | grep Distrib
// 方法03、连接数据库之后输入
status
// 方法04、直接查询版本
select version();

查询当前使用的数据库：

select database();

查询当前日期：

select current_date;

查询当前时间距离指定时间的天数

SELECT DATEDIFF(CURDATE(), FROM_UNIXTIME(1527782400, '%Y-%m-%d'))
// 1527782400对应的是2018-06-01 00:00的时间戳，如果今天是2018-06-07，那么返回的值就是6

时间戳和时间互相转换

// 时间戳转换成时间
SELECT FROM_UNIXTIME(1234567890);
SELECT FROM_UNIXTIME(1234567890, '%Y-%m-%d %H:%i:%S')
// 会输出 2009-02-14 07:31:30

// 时间转换成时间戳
SELECT unix_timestamp("2018-6-1 00:00:00");
// 会输出 1527782400

4. 默认数据库

默认数据库有如下：

1. mysql ：用户权限相关数据
2. test ：用于用于测试数据
3. information_schema ：MySQL本身架构相关数据

用户管理：

1. 创建用户

create user '用户名'@'IP地址' identified by '密码';

2. 删除用户

drop user '用户名'@'IP地址';

3. 修改用户

rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';

4. 修改密码

set password for '用户名'@'IP地址' = Password('新密码');

权限管理：

1. 查看权限

show grants for '用户'@'IP地址'

2. 授权

grant  权限 on 数据库.表 to   '用户'@'IP地址'  

// 创建一个alex用户，并赋予权限
grant all privileges on *.* to  alex@"%" identified by "qazwsx" with grant option;

//grant 是授权命令，其中 alex 是我们连接用的用户名、"qazwsx"是连接密码，用户名后面的 "%" 通用符表示允许各 host 操作。

3. 取消授权

revoke 权限 on 数据库.表 from '用户名'@'IP地址'

4. 刷新授权

flush privileges;

5. 授权示例如下：

grant all privileges on db1.tb1 TO '用户名'@'IP'

grant select on db1.* TO '用户名'@'IP'

grant select,insert on *.* TO '用户名'@'IP'

revoke select on db1.tb1 from '用户名'@'IP'

6. 锁定账号（禁用账号）

select Host,User,plugin,authentication_string,password_last_changed,account_locked from user;

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

最后说个事
公号算法变了，为防止看不到我的更新
大家帮忙加个星标
点击上方的公众号卡片
再点右上角三个点
就能看到设为星标
算我跪下来求你们

往期精选：

微信，看看你的另一半跟谁聊天频繁！

让你的微信“拍一拍”有趣且不失风度

微信年度账单来了，不敢看！

张万林，下雪了……我用编程带你看场纷飞大雪

还在使用默认的微信图标？赶紧换个吧！

我的微信和你们的不一样！？