MySQL常用操作

1. 库操作

1.1 创建数据库

语法：create database 数据库名字 [库选项];

例子：

create database 数据库名 charset 字符集名称;
1

1.2 查看和选择数据库

show databases;                  # 查看全部数据库
show databases like ‘my%’;       # 获取以my开头的全部数据库
show databases like ‘%database’; # 获取以database结尾的数据库

use mydatabase;                  # 选择名为mydatabase的数据库
1
2
3
4
5

1.3 修改数据库信息

基本语法：alter database 数据库名字 charset = 字符集;

是否可以修改数据库名字？

mysql5.5之前是可以修改的rename命令；但是5.5之后就不可以。

1.4 删除数据库

基本语法：drop database 数据库名字;

1.5 SQL备份和恢复

1.5.1 SQL备份

SQL备份用到的是专门的备份客户端，因此还没与数据库服务器进行连接。

基本语法：

mysqldump/mysqldump.exe -hPup 数据库名字 [表1 [表2…]] > 备份文件地址

备份可以有三种形式：

1、整库备份（只需要提供数据库名字）

2、单表备份：数据库后面跟一张表

3、多表备份：数据库后跟多张表

1.5.2 数据还原

Mysqldump备份的数据中没有关于数据库本身的操作，都是针对表级别的操作：当进行数据（SQL还原），必须指定数据库

方式一

利用mysql.exe客户端：没有登录之前，可以直接用该客户端进行数据还原

Mysql.exe –hPup 数据库 < 文件位置

方式二

在SQL指令，提供了一种导入SQL指令的方式

Source SQL文件位置;

（必须先进入到对应的数据库）

2. 表操作

2.1 创建表

基本语法：create table 表名(字段名 字段类型 [字段属性], 字段名 字段类型 [字段属性],…) [表选项]

# 创建新的表
create table teacher(
  	id int not null primary key auto_increment,
		name varchar(10) unique,
  	age int
);

# 复制一份已经有的表结构
create table teacher like mydatabase2.teacher; 
# 在当前数据库下创建一个与数据库mydatabase2下的teacher表结构一个样的表，
名为teacher
1
2
3
4
5
6
7
8
9
10
11

2.2 查看表信息

show tables;               # 显示所有表
show tables like  ‘%aaa’;  # 显示以aaa结尾的表
show tables like  ‘bbb%’;  # 显示以bbb开头的表

# 以下三条语句都是查看teachers表的表结构
desc teachers;
describe teachers;
show columns from teachers;

show create table teachers; # 显示teachers表的创建语句
1
2
3
4
5
6
7
8
9
10

2.3 修改表信息

• 修改表名：rename table 旧表名 to 新表名
• 修改表选项：alter table 表名表选项 [=] 新值

• 新增字段：alter table 表名 add [column] 新字段名列类型 [列属性] [位置first/after 字段名]
• 修改字段名：alter table 表名 change 旧字段名新字段名字段类型 [列属性] [新位置]

• 修改字段类型（属性）： alter table 表名 modify 字段名新类型 [新属性] [新位置]
• 删除字段：alter table 表名 drop 字段名

2.3.1 新增字段

字段位置：字段想要存放的位置

first：在某某之前（最前面），第一个字段

after 字段名：放在某个具体的字段之后（默认的）

2.3.2 修改字段名

2.3.3 修改字段属性

2.3.4 删除字段

3. 删除表

基础语法：drop table 表名[,表名2…]，可以同时删除多个数据表

3. 数据操作

3.1 插入数据

基础语法：

insert into 表名[(字段列表)] values(对应字段列表);

下方操作值列表必须与字段列表一致

insert into 表名 values(对应表结构的字段列表);

---

1、注意：后面（values中）对应的值列表只需要与前面的字段列表相对应即可（不一定与表结构完全一致）

2、注意：字段列表并不一定非要有所有的表中字段

向表中所有字段插入数据

3.2.1 查询数据

select * from 表名;  # 查询表中全部数据
 # *表示匹配所有的字段

select 字段列表 from 表名;   # 查询表中部分字段
# 字段列表使用逗号“,”隔开

select 字段列表/* from 表名 where 字段名 = 值;  # 简单条件查询数据
# mysql中没有==符号

# 完整查询指令
select选项 字段列表 from 数据源 where条件 group by 分组 
having条件 order by 排序 limit限制;

# 排序 asc：升序、desc：降序

# 限制条件可以实现分页
# limit offset,length; 跳过offset条数据，向后取length条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

比较运算符：>、>=、<、<=、=、<>
逻辑运算符：and、or、not
in运算符：in (结果1,结果2,结果3…)
is运算符：is null / is not null

like运算符：like '%xxx'
# 匹配模式中，有两种占位符：
# _：匹配对应的单个字符
# %：匹配多个字符
1
2
3
4
5
6
7
8
9

3.2.3 删除数据

基本语法：delete from 表名 [where 条件];

如果没有where条件：意味着系统会自动删除该表所有数据（慎用）

3.2.4 更新数据

基本语法：update 表名 set 字段名 = 新值 [where 条件];

如果没有where条件，那么所有的表中对应的那个字段都会被修改成统一值。

4. 其它

4.1 外连接

outer join，按照某一张表作为主表（表中所有记录在最后都会保留），根据条件去连接另外一张表，从而得到目标数据。

外连接分为两种：左外连接（left join），右外连接（right join）

左连接：左表是主表

右连接：右表是主表

原理

1、确定连接主表：左连接就是left join左边的表为主表；right join就是右边为主表

2、拿主表的每一条记录，去匹配另外一张表（从表）的每一条记录

3、如果满足匹配条件：保留；不满足即不保留

4、如果主表记录在从表中一条都没有匹配成功，那么也要保留该记录：从表对应的字段值都未NULL

语法

基本语法：

左连接：主表 left join 从表 on 连接条件;

右连接：从表 right join 主表 on连接条件;

左连接对应的主表数据在左边；右连接对应的主表数据在右边：

左连接：

特点：

1、外连接中主表数据记录一定会保存：连接之后不会出现记录数少于主表（内连接可能）

2、左连接和右连接其实可以互相转换，但是数据对应的位置（表顺序）会改变

4.2 Using关键字

简介

是在连接查询中用来代替对应的on关键字的，进行条件匹配。

原理

1、在连接查询时，使用on的地方用using代替

2、使用using的前提是对应的两张表连接的字段是同名（类似自然连接自动匹配）

3、如果使用using关键字，那么对应的同名字段，最终在结果中只会保留一个。

语法

基本语法：表1 [inner,left,right] join 表2 using(同名字段列表);