可复制保存到本地,供日后查询

数据库操作

-- 增

-- 创建数据库

create database mydatabase charset utf8;

-- 创建关键字数据库需用反引号

create database `database` charset utf8;

-- 创建中文数据库(先告诉服务器当前中文的字符集)

set names gbk;

create database 中国 charset utf8;

-- 删

-- 删除数据库

drop database 数据库名字;

-- 改

-- 修改数据库mydatabase的字符集

alter database mydatabase charset gbk;

-- 查

-- 查看所有数据库

show databases;

-- 查看指定部分的数据库：模糊查询

show databases like 'pattern';

-- pattern是匹配模式

-- %：匹配多个字符

-- _: 匹配单个字符

-- 查看以abc_开始的数据库：_需要被转义

show databases like 'abc\_%';

-- 查看数据库创建语句

show create database 数据库名称;

-- 进入数据库环境

use 数据库名字;

表操作

-- 增

-- 增加表(若当前为某一数据库环境下,不用制定数据库名)

create table [if not exists] 数据库名.表名(

字段名字 数据类型 [not null] [unique] [default 默认值],

字段名字 数据类型 [primary key] [comment '注释']

)[表选项]; -- if not exists: 如果表名不存在,就创建,否则不执行创建代码(检查功能)

-- unique: 不能重复

-- 表选项：控制表的表现

-- 字符集：charset/character set 具体字符集; 保证表中数据存储的字符集

-- 校对集：collate 具体校对集;

-- 存储引擎：engine 具体存储引擎;(innodb 和 myisam)

-- primary key: 将该列设为主键, 主键不能为空

-- 复合主键： 在最后加 primary key(字段名, 字段名)

-- not null: 表示该字段不能为空

-- default 默认值：为该字段指定默认值

-- 外键：foreign key(本表字段名) references 外部表名(主键字段名) [on delete 模式 on update 模式]; 只有InnoDB存储引擎支持

-- 指定模式：外键约束有三种约束模式：都是针对父表的约束

-- district: 严格模式(默认),父表不能删除或者更新一个已经被字表引用的记录

-- cascade：级联模式,父表的操作,对应字表关联的数据跟着被操作

-- set null：置空模式,父表的操作之后,子表对应的数据(外键字段)被置空

-- 自增长：id int auto_increment,

create table 表名 like 数据库名:表名; -- 从已有表创建新表(复制表结构)

-- 删

-- 删除数据表

drop table 表名1, 表名2...;

-- 清空表,重置自增长

truncate 表名;

-- 改

-- 修改表名

rename table 旧表名 to 新表名;

-- 修改表选项：字符集,校对集和存储引擎

alter table 表名 表选项 [=] 值; --表选项同上;

-- 查

-- 查看所有表

show tables;

-- 查看部分表

show tables like 'pattern'; -- pattern同上

-- 查看表创建语句

show create table 表名;

show create table 表名\g -- \g == ;

show create table 表名\G -- \G将查到的结构旋转90度变成纵向

-- 查看表结构：产看表中的字段信息

desc 表名;

describe 表名;

show columns from 表名;

字段操作

-- 增

-- 新增字段

alter table 表名 add[column] 字段名 数据类型[列属性][位置];

-- 位置：字段名可以存放表中的任意位置.

-- 第一个：first;

-- 在哪个字段之后：after 字段名;

-- 新增外键

alter table 表名 add [constraint 外键名] foreign key (外键字段) references 父表(主键字段);

-- 删

-- 删除字段

alter table 表名 drop 字段名;

-- 删除外键

alter table 表名 drop foreign key 外键名;

-- 改

-- 修改字段

alter table 表名 modify 字段名 数据类型[属性][位置];

-- 重命名字段

alter table 表名 change 旧字段名 新字段名 数据类型[属性][位置];

-- 查

数据操作

-- 增

-- 插入数据

insert into 表名 values (值列表) [,(值列表)]; -- 给全表字段插入数据,不需要指定字段列表；

-- 要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致

-- 凡是非数值数据,都需要使用引号包裹；

-- 可以一次性插入多条记录

insert into 表名 (字段列表) values (值列表)[,(值列表)]; -- 给部分字段插入数据,需要选定字段列表：

-- 字段列表出现的顺序与字段的顺序无关；

-- 但是值列表的顺序必须与选定的字段的顺序一致

-- 主键冲突时更新数据

insert into 表名 [(字段列表：包含主键)] values (值列表) on duplicate key update 数据名 = 数据值;

-- 主键冲突时替换数据

replace into 表名 [(字段列表：包含主键)] values(值列表); -- 不冲突直接插入

-- 复制表数据

insert into 表名 [(字段列表)] select */字段列表 from 数据库名:数据表名; -- 可以迅速让表中的数据膨胀到一定数量级：测试表的压力以及效率

-- 删

-- 删除数据

delete from 表名 [where 条件] [limit 数量];

-- 改

-- 修改数据

update 表名 set 字段 = 值 [where 条件] [limit 数量];

-- 查

-- 查看 指定字段/所有字段 [指定条件]的数据

select 字段列表/* from 表名 [where 条件];

-- 查询数据完整语句

select [select选项] 字段列表[字段别名]/* from 数据源 [where子句] [group by子句] [having子句] [order by子句] [limit子句];

-- select选项: select对查出来的结果的处理方式

-- all: 默认的,保留所有的结果

-- distinct：查出来的结果将重复(所有字段都相同)的去除

-- 字段别名：当数据进行查询的时候,有时候名字并不一定满足需求

-- (多表查询的时候,会有同名字段),需要对字段名进行重命名：即别名

-- 语法： 字段名 [as] 别名;

-- 数据源：数据的来源,关系型数据库的来源都是数据表：本质上只要保证数据类似二维表,最终都可以作为数据源

-- 数据源分为多种：单表数据源,多表数据源,查询语句

-- 单表数据源：select * from 表名;

-- 多表数据源：select * from 表名1,表名2...;

-- 从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留(记录数和字段数),没什么用

-- 查询语句：select * from (select语句) as 别名;

-- 子查询,数据的来源是一条查询语句(查询语句的结果是二维表)

-- 连接查询

-- 交叉连接：左表 cross join 右表; -- 等同于 from 左表,右表;

-- 内连接：左表 [inner] join 右表 on 左表.字段 = 右表.字段;

-- 从左表中取出每一条记录,去右表中与所有记录进行匹配,当左表.字段与右表.字段相等时保留结果

-- 外连接：左表 left/right join 右表 on 左表.字段 = 右表.字段;

-- 以其中一张表为主,取出里面所有记录,每条与另一张表进行连接.能匹配,正确保留,不能匹配,另一张表的字段都置空NULL.

-- 自然连接：左表 natural join 右表; -- 内连接

-- 左表 natural left/right join 右表; -- 外连接

-- 系统以字段名作为匹配模式,同名字段作为条件,连接之后合并同名字段,多个同名字段都作为条件

-- where字句: 用来判断数据,筛选数据,返回0或1(对磁盘数据进行判断,是否加载到内存)(条件可以使用子查询)

-- 比较运算符：>, <, >=, <=, !=, <>, =, like, between A and B(A到B 闭区间), in, not in, =any, !=any, =some, !=some, =all, !=all

-- 逻辑运算符：&&(and), ||(or), !(not)

-- group by字句： 根据某个字段进行分组(相同的放一组,不同的分到不同的组)

-- 基本语句：group by 字段名1,字段名2... [asc/desc] [with rollup];

-- 分组是为了统计数据,mysql提供的一些统计函数(在查询时放在字段列表中)

-- count(字段名)：统计分组后的记录数,每一组有多少记录(*代表统计记录,字段名代表统计对应的字段 NULL不统计)

-- max(字段名)：统计每组中最大值

-- min(字段名)：统计每组中最小值

-- avg(字段名)：统计平均值

-- sum(字段名)：统计和

-- group_concat(字段)：对分组的结果中的某个字段进行字符串连接(保留改组所有的某个字段)

-- asc/desc: 对分组的结果进行排序 asc:升序(默认) desc:降序

-- with rollup: 回溯统计

-- having子句：与where字句一样：进行条件判断的(进入内存后的判断)

-- 分组统计的结果或者说统计函数只有having能够使用

-- having能够使用字段别名

-- order by字句：排序,根据某个字段进行升降序排序,依赖校对集

-- 基本语法：order by 字段名1[asc/desc],字段名2[asc/desc]...;

-- limit子句: 一种限制结果的语句

-- 只用来限制长度(数量)：limit 数量;

-- 限制起始位置和长度(可以实现数据的分页)：limit 起始位置,长度;

-- 联合查询:将多次查询,在记录上进行拼接(字段不会增加)

select语句1 union [union选项] select语句2...; -- 字段数必须严格一致

-- union选项：与select选项一样有两个,all和distinct(默认)

-- order by: 在联合查询中order by不能直接使用,需要对查询语句使用括号才行.若要order by生效,必须搭配limit.

视图操作

-- 增

-- 创建视图

create [algorithm = 指定算法] view 视图名 as select语句;

-- with check option: 在where语句后添加,表示不允许修改where语句中的字段

-- 视图算法：分为三种(如果视图的select语句中包含五子句,而且很有可能顺序比外部的查询语句靠后,就要使用临时表算法了)

-- undefined：未定义(默认的),不是实际使用算法,让系统自主选择算法

-- temptable：临时表算法,先执行视图的select语句,后执行外部查询语句

-- merge：合并算法,系统将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(效率高,系统经常选择)

-- 删

-- 删除视图

drop view 视图名;

-- 改

-- 修改视图本身的来源语句

alter view 视图名 as 新select语句;

-- 查

-- 表的所有查看方式都使用于视图

-- 将视图当作表查询即可

数据备份与还原

-- 单表数据备份(前提是外部文件不存在)

select */字段列表 into outfile 文件所在路径 [fields 字段处理 lines 行处理] from 数据源;

-- fields 字段处理

-- enclosed by：字段使用什么包裹,默认是''空字符串

-- teminated by: 字段以什么结束,默认是'\t'tab键

-- escaped by：特殊符号用什么方式处理,默认是'\\'使用反斜杠转义

-- lines 行处理

-- starting by：每行以什么开始,默认是''空字符串

-- teminated by：每行以什么结束,默认是'\t\n'换行符

-- 单表数据还原(前提是表结构存在)

load data infile 文件所在路径 into table 表名 [(字段列表)] fields 字段处理 lines 行处理; -- 处理方式与备份相同

-- SQL备份(备份的是sql语句,系统对表结构以及数据进行处理,变成对应的sql语句,然后进行备份)(使用mysql提供的软件:mysqldump.exe)

mysqldump.exe -hPup 数据库名字 [数据表名字1,数据表名字2...] > 外部文件路径

-- SQL还原

mysql.exe -hPup 数据库名字 < 备份文件目录 -- (使用mysq.exe客户端还原)

source 文件所在路径; -- sql指令还原,数据库默认当前数据库

-- 增量备份

事务安全操作(免费的引擎只有InnoDB存储引擎支持)

-- 开启事务

start transaction;

-- 关闭事务

commit; -- 提交事务：同步数据表(操作成功)

rollback; -- 回滚事务：清空日志表(操作失败)

-- 设置回滚点

savepoint 回滚点名字;

-- 回到回滚点

rollback to 回滚点名字;

存储过程操作

-- 创建过程

create procedure 过程名字([参数列表])

begin

过程体

end

-- 删除过程

drop procedure 过程名;

-- 查看所有过程

show procedure status [like 'pattern'];

-- 查看过程创建语句

show create procedure 过程名;

-- 调用过程

call 过程名();

触发器

-- 创建触发器

delimiter 自定义结束符;

create trigger 触发器名 触发时间 事件类型 on 表名 for each row

begin

指令

end

自定义结束符

delimiter ;

-- 查看所有触发器

show triggers [like 'pattern'];

-- 查看触发器创建语句

show create trigger 触发器名字;

-- 删除触发器

drop trigger 触发器名;