MySQL总结

1. MySQL总结

1.1 SQL的分类

数据查询语言: (DQL)select
数据库操作语言: (DML)insert,delete,update
数据定义语言: (DDL)create,drop,alter
事务操作语言: (TCL)commit,rollback
数据控制语言:(DCL)grant,revoke

1.2 命令导入数据

source <sql脚本路径>  # source D://a.sql

1.3 简单命令

1.3.1 查看数据库版本

mysql --version
mysql -V

1.3.2 创建数据库

cerate datebase 数据库名;  #create database test;

使用数据库

use 数据库名; #use test

1.3.3 查看使用的数据库

select database();
# 查看数据库版本
select version();

1.3.4 查看所有数据库和查看当前库中所有的表

show databases; #查看所有数据库
show table; #查看库中所有的表

1.3.6 查看表结构

desc 表名;

1.3.7 查看创建表的语句

show create table 表名;

1.4 查询(DQL)

1.4.1 条件查询
条件查询需要用到where 语句,where 必须放到from 语句表的后面 支持如下运算符
distinct 去除重复行

运算符	说明
=	等于
<>或者!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between…and,	两者之间,等同于>=and <=
is null	为 null (is not null不为空
and	并且
or	或者
in	包含,相当于多个or(not in 不在这个范围中)
not	not 可以取非,主要用在is或in中
like	like 称为模糊查询,支持%或下划线匹配 ,%匹配任意个字符 下划线, 一个下划线匹配一个字符

1.4.2 排除数据
单一字段排序: 排序采用order by 子句,order by后面跟上排序字段,排序字段可以放多个,多个采用逗号间隔,order by 默认采用升序,如果存在where 子句那么order by 必须放到where 语句的后面
多个字段排序: order by 后面紧跟哪个字段,哪个字段就先排,依次向下排序
1.4.3 数据处理函数/单行处理函数

Lower	转换小写
upper	转换大写
substr	取子串(substr被截取的字符串,起始下标,截取的长度)
length	取长度
trim	去空格

str_to_date	将字符串转化为日期
date_format	格式化日期
format	设置前分位
round	四舍五入
rand()	生成随机数
Ifnull	可以将null转化为一个具体的值

select lower(字段) from 表名;#将该字段查出来的数据全部转换为小写

1.4.4 case…when…then…else…end
当员工的工作岗位是manager 的时候,工资上调10%,当工作岗位是salesman的时候,工资上调50%,其他不变

select 
  ename,job,sal as oldsal,
  (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
from 
	emp;

1.4.5 ifnull

select ifnull(字段名,0) from 表名;  #表示该字段如果为空就替换为0

在SQL 语句当中若有NULL值参与数学运算,计算结果一定是NULL为了防止计算结果出现NULL,建议先使用ifnull空值处理函数预先处理.
1.4.6 分组函数/聚合函数/多行处理函数

函数	使用
max	取某个字段的最大值
min	取某个字段的最小值
sum	取某个字段的总和
count	取得记录数
avg	娶某个字段的平均数

1.4.7 分组查询

分组查询主要涉及两个子句:group by 和 having

原则:能在where 中过滤的数据,尽量在where 中过滤,效率较高.having的过滤是专门对分组之后的数据进行过滤的.

select 字段
from 表名
where ....
group by ....
having ....(就是为了过滤分组后的数据而存在的-不可单独出现)
order by ...

1.4.8 连接查询
连接分类: 连接分为内连接和外连接
内连接: 表 1 inner join 表 2 on 关联条件 做连接查询的时候一定要写上关联条件 inner可以省略
**外连接:**外连接分为左外连接和右外连接
左外连接: 表 1 left outer join 表 2 on 关联条件 做连接查询的时候一定要写上关联条件 outer 可以省略
右外连接: 表 1 right outer join 表 2 on 关联条件做连接查询的时候一定要写上关联条件outer 可以省略
左外连接和右外连接的区别: 左外连接以左表的表为准和右边的表进行比较,和左边的表不相等都会显示出来,右表符合条件的显示,不符合条件的就不显示; 右外连接正好相反.

1.4.9子查询
子查询就是嵌套的select语句,可以理解为子查询就是一张表
where 子查询: 在where 语句中加入select 语句

select * from 表名 where id ={select id from 表名}

from 子查询: 在from语句中使用子查询

select * from 表名 join (子查询) on 条件

select 子查询: 在select语句中使用子查询

select 字段 ,(子查询) from 表名 字段

1.4.10 union和limit
union: 表示可以合并的集合(相加)

select * from student where hight > 170 union select * from student where hight < 155
# 查询hight大于170和hinght小于155的学生信息

limit: 主要用于提取前几条或者中间某几条数据

select * from 表名 where 条件 limit 0 5   # 拿取从第一条数据为始的5条数据

表

1.5.1 创建表
创建表的时候,表中有字段,每一个字段有:

• 字段名
• 字段数据类型
• 字段长度限制
• 字段约束

mysql常用数据类型：

类型	描述
char(长度)	定长字符串,存储空间大小固定,适合作为主键或外键
varchar(长度)	变长字符串,存储空间等于实际数据空间
double(有效数字位数,小数位)	数值型
Float(有效数字位,小数位)	数值型
int(长度)	整型
bigint(长度)	长整型
Date	日期型
biob	Binary Large Object(二进制大对象)
其他类型…

1.5.2 增加/删除/修改表结构
添加字段

alter table 表名 add 字段名 字段类型(长度);

修改字段

alter table 表名 modify 字段名 字段类型(长度);

删除字段

alter table 表名 drop 字段名;

1.5.3 数据库的增删改查
插入数据 (insert)

insert into 表名(字段名,字段名,....,字段名) value (字段值,字段值,....,字段值)

更新数据(update)

update 表名 set 字段名=字段值,...字段名=字段值 where 条件

删除数据(delete)

delete from 表名 where 条件

1.5.4 创建表加入约束

常见的约束

• 非空约束 not null
• 唯一约束 unique
• 主键约束 primary key
• 外键约束 foreign key
• 自定义检查约束,check (不建议使用) (在mysql中现在还不支持)

建立学生表

create table student(
	id int primary key,   #int类型的长度限制可以省略不写  id作为主键
	student_id  char(11) not null unique,  #学生学号，唯一且不为空
	name varchar(50)  not null,   #学生姓名，不为空
	cno int, #外键 班级id
	foreign key(cno) references class(id),  #将班级表class的主键id作为该表的外键
	unique(student_id,name)  #学生学号和姓名联合起来不唯一 约束没有添加在列的后面，这种约束被称为表级约束。
)

1.5.5 增加/删除/修改表约束
1.5.5.1 增加约束
添加外键约束

alter table 从表 add constraint 约束名称 foreign key 从表(外键字段) references 主表(主键字段);

添加主键约束

alter table 表 add constraint 约束名称 primary key 表(主键字段);

添加唯一性约束

alter table 表 add constraint 约束名称 unique 表(字段);

1.5.5.2 删除约束
删除外键约束

alter table 表名 drop foreign 外键字段;

删除主键约束

alter table 表名 drop primary key;

1.5.5.3 修改约束,其实就是修改字段

alter table 表名 modify 字段名 字段类型(长度) 约束;
# mysql 对有些约束的修改时不支持,所以我们可以先删除,再添加

存储引擎

1.6.1 存储引擎的使用

• 数据库中的各表均被(在创建表时)指定的存储引擎来处理
• 服务器可用引擎依赖于以下因素
  • MySQL 的版本
  • 服务器在开发时如何配置
  • 启动选项
• 为了解当前服务器中有哪些存储引擎可用,可用使用SHOW ENGINES 语句查看

 SHOW ENGINES\G

• 在创建表时,可使用ENGINE 选项为CREATE TABLE 语句显示指定存储引擎

CREATE TABLE 表名(NOINT) ENGINE =MyISAM;

• 如果在创建表时没有显示指定存储引擎,则该表使用当前默认的存储引擎
• 默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定
• 现有表的存储引擎可使用ALTER TABLE语句来改变

ALTER TABLE 表名 ENGINE = INNODB;

• 为确定某表所使用的存储引擎,可以使用SHOW CREATE TABLE 或 SHOW TABLE STATUS语句

SHOW CREATE TABLE 表名\G
SHOW TABLE STATUS LIKE '表名'\G

1.6.2 常见的存储引擎
1.6.2.1 MyISAM 存储引擎

• MyISAM是Mysql最常见的引擎
• 它管理表具有以下三个特征:
  • 使用三个文件表示每个表
    • 格式文件 :存储表结构的定义(mytable.frm)
    • 数据文件: 存储表行的内容(mytable.MYD)
    • 索引文件: 存储表表上的索引(mytable.MYI)
  • 灵活的AUTO_INCREMENT 字段处理
  • 可被转化为压缩,只读来节省空间

1.6.2.2 InnoDB 存储引擎

• InnoDB存储引擎是MySQL的缺省引擎
• 它管理的表具有下列主要特征:
  • 每个InnDB 表在数据库目录中以.frm 格式文件表示
  • InnoDB 表空间tablespace 被用于存储表的内容
  • 提供一组用来记录事务性活动的日志文件
  • 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
  • 提供全 ACID 兼容
  • 在 MySQL 服务器崩溃后提供自动恢复
  • 多版本（MVCC）和行级锁定
  • 支持外键及引用的完整性，包括级联删除和更新

1.6.2.3 Memory存储引擎

• 使用MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MENORY存储引擎非常快
• MEMORY 存储引擎管理的表具有下列的特征
  • 在数据库目录内,每个表均以.frm格式的文件表示
  • 表数据及索引被存在内存中
  • 表级锁机制
  • 不能包括TEXT和BLOB字段
• Memory 存储引擎以前被称为HEAP引擎

1.6.3 如何选择存储引擎

• MyISAM:适合大量数据读而少量数据更新的混合操作.MyISAM表的另一种适用情形是使用压缩的只读表
• InnoDB:如果包含较多的数据更新操作,那么就一个使用InnoDB.其行级锁机制和多版本的支持为数据读取和更新的混合 操作提供了良好的并发机制
• Memory:存储非永久性需要的数据,或者是能够从基于磁盘表中重新生成的数据

1.7事务

1.7.1 概述
事务可以保证多个操作的原子性,要么全部成功,要么全部失败.对应数据库来说事务保证批量的DML操作要么全部成功要么全部失败,事务有四个特征ACID

• 原子性(Atomicity): 整个事务中的全部操作,必须作为一个单元全部完成(或者全部取消)
• 一致性(Consistency):在事务开始之前和结束之后,数据库都保持一致
• 隔离性(Isolation):一个事务不会影响其他事物的运行
• 持久性(Durability):在事务完成后,该事务对数据库所有的更改都会被持久的保存在数据库中,不会被回滚

事务中存在一些概念
事务(Transaction):一批操作(一组DML操作)
开启事务(start transaction 或者 begin)
回滚事务(rollback)
提交事务(commit)

**SET AUTOCOMMIT： **禁用事务或者启用事务的自动提交模式
执行DML语句就是开启一个事务
关于事务的回滚: 只能回滚insert,update和delete语句,不能回滚select(回滚select没有意义),也不能回滚,create,drop和alter语句
事务只对DML语句有效

注意：rollback，或者commit后事务就结束了