mysql命令总结

创建数据库

首先启动数据库服务端服务

service mysqld start

连接数据库客户端

mysql -uroot -p

创建数据库，使用数据库

create database 库名 [charset=utf8] [collate utf8_general_ci]
use 库名;

字符集一般设置utf8支持中文，校验规则可以影响排序和区分大小写。

[ ]内的内容可省略

创建表

create table 表名 (
    id int primary key auto_increment,
    name varchar(20) default 'hello',
    class char(20) not null unique,
    salary decimal(5, 2) zerofill comment '薪金'
)[character set utf8 engine MyISAM];     --MyISAM 支持全文索引

如果需要支持外键，引擎必须是InnoDB (还支持事务)

char 和 varchar 的区别在于char使用固定的存储空间保存串，浪费空间但查询效率高，varchar 需要额外字节来索引串的长度。

char 最大长度为255个字符， varchar 的有效长度是65532 字节，所以如果是utf8编码的表（3个字节为一个字符）可以存65532/3 = 21844 字符，当元组有一个varchar设置为65535时就不能再有其他字段了，因为一个元组最多65535个字节。

操纵数据库

操纵语句

库和表都建好了，现在有各种语句来操纵它

show databases;   
show tables;
show processlist;  --显示当前连接到服务端的用户
show create table 表名；
show create database 库名; --查看库的创建属性
explain  select  xxx;    --分析执行的查询语句
desc 表名;     --描述表结构

修改库和表

alter database 库名 [charset = utf8] [collate utf8_general_ci];
alter table 表名 add 字段名 类型 约束;
alter table 表名 modify  字段名 类型 约束;
alter table 表名 drop 字段名;
alter table 表名 rename to newname;        --改表名
alter table 表名 change name oldName 类型 约束;    ---某字段改名
alter table 表名 charset =utf8;
alter table 表名 engine = InnoDB;
drop database 库名;
drop table 表名;

增删改语句

insert into 表名（字段，） values（值，），（值，）  --可以一次插入多个元组
replace into 表名（字段，） values（值，）   --不确定有没有重复主键时可以替换上面的
update 表名 set 字段 = 值 [where condition ] [limin 更新数量]
delete from 表名 [where condition]
truncate table 表名    --这个指令直接删除表，效率高，无条件

数据库备份

在命令行敲

mysqldump -u root -p 数据库名 > 路径/xx.sql

在数据库里敲

create database xx;
use xx;
source 路径/xx.sql

也可以备份表，还可以加上 -B选项，备份多个数据库并且不用重新建库。

查询优化

1. where条件从最后开始筛选，因此将能筛选最多的条件放后面
2. 多表查询小表放前，大表放后。

关于sql语句的执行顺序

开始先用 from 语句从磁盘中选出数据， 然后用where 筛选符合条件的磁盘上的数据到内存中， 之后把select 后面相应的字段数据取出来，最后是 order by。如果有group by 的话是在order by 处理之前，也就是group by处理的是内存中的数据，所以group by可以使用select 后面的列别名（而where后面就不能使用列别名），并且group by 用having 筛选数据而不是用 where。

多表查询

1. 如果直接from 两个表，则默认查询结果为笛卡儿积
2. 通过主键连接查询
3. 从笛卡儿积过滤查询(from 中创建子表)

自连接

当一张表中某些字段有联系（比如上下级关系），可以在同一张表上连接查询。

可以使用子查询或者多表查询实现（给当前表重命名 ，用两个自身表进行查询）。

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件

外连接

分为左外连接和右外连接，左外连接就是当进行连接查询时如果存在左表有数据，右表为null，则依然显示查询结果，语法为：

select 字段名  from 表名1 left join 表名2 on 连接条件

子查询

单行子查询：子查询返回的是一条记录，在母查询中where 条件用 =。

多行子查询：子查询返回的是多条记录，在母查询中where 条件用 in ，any ，all 。

多列子查询：

select xx from xx where (a, b) = (select a,b from ...);

在from中使用子查询：当子查询返回多行，多列，比较复杂，考虑创建临时表，再条件过滤。

如题目（如何显示高于自己部门平均工资的员工的信息）

select ... from EMP, (select ... from ... group by ..) tmp
where EMP.sal > tmp.sal and EMP.deptno=tmp.deptno;

删除表中的的重复记录

创建一张空表，空表的结构和原来一样

create table new like old;

将原表进行distinct，把数据导入新表(特殊插入语法)

insert into new (select distinct * from old);

删除原表

drop table old;

将新表改名

alter table new rename old;

外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束,当定义外键后，要求外键列数据必须在主表的主键列存在或为null

对从表外键约束的语法

foreign key (字段名) references 主表(列)

为表增加外键约束

ALTER TABLE 表名 ADD FOREIGN KEY (字段名) REFERENCES 表名(字段名) ;

外键能避免数据库存储大量重复信息，并且向从表添加元组时如果外键不存在就不能插入（可以为null）。

索引

常见索引分为：主键索引(primary key)，唯一索引(unique)，普通索引(index)，全文索引(fulltext)–解决中子文索 引问题

索引的创建非常简单，可以在建表的时候创建索引

create table xx(
    name varchar(20),
    index(name)
);

也可以修改表结构创建索引

alter table 表名 add index(字段);

主键，唯一键是自动创建索引的。全文索引需要在MyISAM引擎下使用。

CREATE TABLE articles (
    body TEXT, 
    FULLTEXT (title,body)
)engine=MyISAM;

使用/查找 全文索引

 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')
 show keys from 表名

事务

四个属性要牢记~

1. 原子性(Atomicity)： 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性(Consistency)： 事务必须使数据库从一个一致性状态变到另外一个一致性状态。
3. 隔离性(Isolation)： 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务 的操作数据所干扰，多个并发事务之间要相互隔离。
4. 持久性(Durability)： 持久性是指一个事务一旦被提交，它对数据库中的数据的修改就是永久性的，接下来即使数据库 发生故障也不应该对其有任何影响。

开始事务

start transaction;
savepoint 保存点名;
rollback to 保存点名;
commit;

事务隔离级别

脏读：一个客户端（事务）会读取到另外一个客户端（事务）没有提交的修改数据。

不可重复读：同一个查询在同一个事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。

幻读：同一个查询在同一个事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

设置事务隔离级别

set session transaction isolation level read uncommitted;
select @@tx_isolation;  --查看隔离级别

简单点来理解事务级别，两个客户端在进行两个事务，第一个事务未commit第二个事务就能看到数据（脏读），第一个事务提交了后第二个事务能看到数据（发生了不可重复读），第一个事务未commit之前第二个事务不能开始（锁），幻读是针对插入操作的。

视图

视图的数据变化会影 响到基表，基表的数据变化也会影响到视图

create view 视图名 as select语句

视图与表区别

1. 表要占用磁盘空间，视图不需要
2. 视图不能添加索引
3. 使用视图可以简化查询
4. 视图可以提高安全性