mysql 的相关操作_MySQL相关操作合集

运行环境

系统 Win10

MySQL版本 5.5

数据库组成

数据库 DB(database)

二维表 table

记录 record

字段 field

行 row

列 column

sql (结构化查询语言): 操作关系型数据库的通用语言

备注：分号(;)表示一条sql语句的结束

连接和退出数据库的方式

连接方式

mysql -u账号 -p密码

mysql -hIp地址 -p端口号 -u账户 -p密码

mysql -u账号 -p

备注：密码将会以*的方式显示

退出方式

exit

quit

\q

数据库操作

创建数据库

备注：“是反引号，可加可不加，当数据库名是关键字或者保留字的时候就要使用反引号引起来，其他时候是可以不使用反引号.

常见字符集：utf8和gbk

create database `数据库名` charset=字符集;

查看数据库

(1)查看所有的数据库

show databases;

(2)查看数据库并过滤

  show databases like "w%"; # 匹配数据库名以w开头的.

  show databases like "%w%"; # 匹配数据库名中包含w的

#特殊关键匹配字符： %：匹配任意字符的任意次数 _：匹配任意字符的出现一次

(3)查看数据库的建库语句

show create database 数据库名;

修改数据库的字符集

alter database 数据库名 charset=新字符集;

删除数据库

drop database 数据库名;

特殊语法

if exists 如果存在, 删除(数据库,表)

drop database if exists 数据库名;

if not exists 如果不存在

create database if not exists 数据库名;

表操作

数据类型

整型

int 4个字节

tinyint 1个字节

小数类型

float 有效位数7

double 有效位数15

定点数：decimal —-> 无精度损失

语法类型(M,D) M: 总位数，D: 小数位数

字符串类型

char 不可变

varchar 可变

文本类型

text 65535个字符

longtext 42亿

日期时间类型date、time、year、datetime、timestamp

其它类型

枚举类型(单选) enum(“选项1”,”选项2″…..)

集合类型(多选) set(“选项1″,”选项2”,”选项3″…..);

备注：集合内部机制: 内部存储的时候采用二进制数进行存储 (01010101), 实际看到的还10进制

字段属性

null ：字段是否允许为空

default ：设置字段的默认值

comment ：设置字段或者表的备注

primary key ：主键

auto_increment ：自动增长(字段类型必须是整型)

unique key ：唯一键

切换数据库

use 数据库名;

指明在哪个数据库里操作表，所以每次操作表前先得切换到指定的数据库.

创建表

create table [if not exists] `表名`(

字段名 字段类型 字段属性,

字段名 字段类型 字段属性

) charset=字符集 engine=引擎;

字符集：记录的字符集采用就近原则, 如果设置了字符集以表为准, 如果表没有就以数据库为。utf8、latin1、gbk

表引擎：设置数据结构的存储和管理的方式.

myisam(查询效率高)

innodb(安全性强,支持事务,支持外键的高级功能. 恢复能力强)

查看表

(1)查看所有的表

show tables;

(2)查看表并过滤

show tables like "%_"

(3)查看创建表的语句

show create table `表名`;

(4)查看表的结构(查看一个表中有哪些字段)

desc `表名`;

show full fields from `表名`;

修改表

(1)修改表的名

rename table 旧表名 to 新表名;

(2)修改表的选项

alter table 表名 charset=新字符集 engine=新引擎;

(3)修改表中的字段

# 1. 添加字段

alter table `表名` add column 字段名 字段定义;

# 2. 删除字段

alter table `表名` drop column 字段名;

# 3. 修改字段定义

alter table `表名` modify column 字段名 新定义;

# 4. 重命名字段

alter table `表名` change column 旧字段名 新字段名 新定义;

删除表

drop table `表名`;

记录操作

设置客户端字符集

set names 编码; # 常见gbk和utf8

添加记录

insert into 表名 (字段1,字段2...) values (字段1的值,字段2的值...);

查看记录

(1)完整查询语句组成

select 选项

段表达式

from 子句

where 子句

group by 子句

having 子句

order by 子句

limit 子句;

备注：每个部分要么不出现, 要么必须出现在正确的顺序上.

select 选项

选项可以有可以没有.(一般都没有)

all: 查询全部数据, 默认就是查询全部数据.

distinct : 查询全部数据并且去除重复数据(所有的字段值完全一样才算重复, 记录重复才算重复)

字段表达式

*匹配的是所有的字段

字段列表

别名 as

聚合函数

备注: 聚合(统计)函数 往往结合分组查询使用

count(字段或者*) 统计记录的条数(不统计为null的)

sum(字段) 对某个字段求和

max(字段) 对某个字段求最大值

min(字段) 对某个字段求最小值

avg(字段) 对某个字段求平均值

group_concat() : 将组内的值拼接成一个字符串.有序

concat()：将组内的值拼接成一个字符串.无序

select rand(); — 随机数

select now(); — 查询当前时间

计算：select 字段+10 from 表名;

from 子句

from 后面通常用于接一个数据源，表示从哪个数据源查询数据

where 子句

where 子句的作用: 对数据源进行过滤.

语法：where 条件表达式

关系运算符 > < >= <= != = <>

模糊查询 like

字段 like “%关键字”

%: 匹配任意字符任意次数

_: 匹配任意字符一次

集合判断 in和not in

字段 in (值1,值2,值3….)

范围判断 between and 和 not between and

字段 between 值1 and 值2; (子段在值1和值2之间，包括值1和值2)

is null 和 is not null

判断某个字段的值是否为 null

逻辑运算符 与and(&&) 或or(||) 非not(!)

group by 分组查询

group by 字段

备注：分组的目的是用于统计,一般与聚合函数一起使用

having子句 分组后再过滤

order by 排序 默认asc升序，desc降序

limit 子句 限制查询结果的数量

limit start,length;

start开始索引，可写可不写

length查询条数

(2)连接查询、子查询和索引

连接查询 join

概念: 将多张表通连接条件连接形成一个新的数据源

select 字段

from 表名1

join 表名2 on 连接条件

左连接：在join左边加上left

右连接：在join右边加上right

子查询

子查询的分类

a. 按位置分类

where 型子查询

from 型子查询(需要为查询的虚表取别名)

b. 按子查询的结果分

标量子查询 (> < = !=)

列子查询 (in 或者 not in)

行子查询 (构建一个行)

表子查询 (构建一个行) in (集合)

修改记录

update 表名 set 字段1=新值,字段2=新值 where 条件;

删除记录

delete from 表名 where 条件;

备份和还原

备份

dos下操作指令:

mysqldump -h主机 -P端口 -u用户名 -p密码 数据库名 >脚本文件存储地址

还原

dos下操作指令:

mysql -h主机 -P端口 -u用户名 -p密码 数据库名< 脚本文件存储地址

外键约束

创建外键

alter table `从表名` add foreign key(`从表字段`) references 主表名(`主表字段`);

alter table 从表名 add constraint `约束名` foreign key (`从表的字段`) references 主表(`主表字段`) [高级]

高级指的是设置 主表发生改变,从表应该如何自动变化.

设置有三个级别:

严格模式: restrict 默认模式.

级联模式: cascade, 主表的关联字段发生变化,从表跟着变.

置空模式: set null, 主表的关联字段发生变化, 从表字段设置为null.(从表的字段必须能为null)

语法：alter table 从表名 add constraint 约束名 foreign key (从表的字段) references 主表(主表字段)[on update 模式 on delete 模式];

查看外键

show create table 从表名;

KEY: 普通键

CONSTRAINT 约束

删除外键

alter table 从表 drop foreign key 约束名称;

备注: 删除外键,自动生成的key仍然存在

alter table 从表 drop key 键名;

事务

语法

开启事务: start transaction; 或者 begin;

提交事务: commit;

回滚事务: rollback;

特点 ACID

原子性，一致性，隔离性，持久性

实体之间的关系

重点关系的处理(设计表结构)：

1. 一对一: 使用相同的主键作为关联字段.

2. 一对多: 在多的一方 添加一个字段 保存一的一方的主键id

3. 多对多: 创建一张中间表保存两张表的主键id.