SQL分类

-------------------数据库------------

创建数据库  create database xxx;

查询所有的数据库 show databases;

查询当前数据库select database();

使用数据库use databaseName;

删除数据库:drop database databaseName;

显示警告:show warnings

查询表索引:show index from table_name 或者Show keys from tables

显示当前用户:select current_user();

-----------------操作表-----------------------

查询所有的表:show tables;

创建表:create table table_name(id int(4),name varchar(50));

显示表结构:desc table_name;

查询表创建的语句:show create table table_name G;

Alter table 语句

修改表名称:alter table table_name rename new_table_name

修改表字段名称:alter table table_name change 老字段名称 新字段名称 varchar(40) 类型

修改表字段类型:alter table table_name modify 字段 varchar(100);

添加表字段:alter table table_name add 字段名称 varchar(100) 类型

删除表字段:alter table table_name drop 字段名称

添加主键索引:alter table table_name add primary key(id);

添加唯一索引:alter table table_name add unqiue(name)

添加普通索引:alter table table_name add index index_name(id,name……)

添加全文索引:alter table table_name add fulltext(textName);

删除索引:alter table table_name drop index index_name;

删除主键索引:alter table table_name drop primary key

修改自增的初始值:alter table t_name auto_increment=10;

Modify和change 区别

         Modify只能修改字段类型,不能修改字段名称

Change可以修改字段名称

修改字段排列顺序:

在某个字段的后面:alter table table_name add 字段名称 varchar(10)  after 已有字段名称

把某个字段放到最前面:alter  table modify 字段名称 varchar(20) first

select y,c,p,sum(profit) from s  group by y,c,p with rollup;

with rollup 不能和order by 一起使用.分析统计函数.

SQL约束constraint

Mysql支持的约束条件:

主关键字约束:primary key

外关键字约束:foreign key

唯一约束:unique

检查约束:check

非空约束:not null

列子:create table table_name(id int(4),name varchar(20) not null default ‘信息’,sal decimal(9,2),constraint pk_id primary key(id),constraint un_name unique (name),constraint che_sal check(sal>2000 and sal<4000));

Create table xxx(id int primary key  auto_increment)自动增长

数据类型

查看数据类型:? Data types

Int：默认11位

Char和varchar 区别

Char 会删除尾部的空格,varchar保留了空格,定长字符串,varchar可变长字符串

Enum 类型 只能插入一个值

Create table enum_name(e enum (‘w’,’y’))  只能插入w和y字符,也可以插入数字类型,但是只是显示第一个w

Set类型可以插入多个值

Create table set_name(s set(‘a’,’b’,’c’)) 可以一次性插入abc

If(value,t, f) 如果value为真,返回t,否责返回f

Ifnull(value1,vlaue2) value1不为空返回value1, 否责value2

系统函数:

Database()  当前数据名称

Version() 当前数据库版本

User() 返回当前登录的用户名称

Password(str) 返回字符串加密版本

Md5() 返回字符串加密MD5值

last_insert_id() 返回最后一个插入的值

数据库管理

查询mysql 有多少个用户连接:show processlist;

杀死连接的数据库客户端:kill id-àid show processlist 的id

MYSQL 引擎

查看表默认的引擎类型:show variables like ‘table_name’;

查看数据库的引擎:show create database xxx G

查看当前数据库版本支持的引擎:show engines 或者show variables like ‘have%’;

创建表默认的引擎:create table t_name(id int(3),name varchar(20),primary key(id)) engine=myisam

修改表的引擎:alter table t_name engine=innodb;

Myisam：不支持事务,也不支持外键,其他优势就是访问速度快,对事务完整性没有要求,以查询,添加为主可以使用这个引擎来创建表,每一个myisam在磁盘上存储成3个文件,期文件名称和表名相同,*.frm(存储表定义)   *.Myd(mydata,存储数据)  *.Myi(myindex 存储索引)

当遇到myisam存储引擎表损坏的情况:可以修复

Myisamchk –r  table_name //

Myisamchk –o table_name 安全的修改表

检查表：Check table table_name

修复表:Repair table table_name;

Memory:存储引擎存在内存中的内容来创建.每个memory表只实际对应一个磁盘文件.*.frm文件.访问速度快,存在内存中.默认使用hash索引.一旦服务关闭,表中数据全部丢失

Show  table status like ‘table_name’ G;

Merge：引擎是一组myisam引擎的组合,这些myisam表结构必须一致,merge表本身没有数据,对merge表可以查询,更新,删除的操作,这些实际是对myisam表的操作

对于merge类型的表插入操作,是通过insert_method子句定义插入的表，有三个不同的值

First插入操作作用在第一个表上

Last插入操作作用在最后一个表上

No对merge不能专心插入操作

磁盘上的文件有两个:.frm和mrg包含组合表信息

Create table xx(id int(3),name varchar(20)) engine=myisam;

Create table xxxx(id int(3),name varchar(20))engine=myisam;

Create table allxxxx(id int(3),name varchar(20))engine=merge union(xx,xxx) insert_method=last;

当插入xx和xxxx表的数据,那么allxxx表里面也会有xx和xxx的数据

Innodb具有提交,回滚事务和崩溃恢复能力的事务安全,效率会差一点

特点:自动增长列,指定主键,如果没有自动编号,必须要加一个索引,主键也是一个索引

指定了自动编号也可以手工的插入值

修改自增的初始值:alter table t_name auto_increment=10;

得到最后一个插入的值Select last_insert_id()

添加外键约束:在no action都是限制子表有关联的记录下父表不能更新,cascade表在更新删除时候,更新子表的记录,导入多个表的数据的时候,可以关闭外键的检查

Set foreign_key_checks=0关闭检查

数据表占用磁盘空间优化:

Insert into table_name select 0,repeat(‘信息文本信息’,10)  10个文本

当有大量数据的时候,myisam引擎的myd文件会占用大量的空间,删除表数据他的空间也不一定会释放,就会产生大量的碎片,可以optimize table table_name优化表,整理碎片,这时候磁盘上的空间就会减少.

Optimize table table_name 只对myisam和bdb还有innodb引擎有作用

索引

总结:添加索引的顺序和长度很重要.如果添加两个索引01和02按照这个两个条件查询时候,如果只有一条记录,只会用到第一个索引,第二个所以就用不到,如果还想用到第二个索引,可以把这两个索引做为联合索引,联合索引的缺点:如果一个联合索引03(name,mobile)当只使用一个索mobile查询的时候不会使用索引,使用name查询的时候会使用上索引.

索引的长度影响着查询速度的快慢.长度越小速度越快.把持住索引的长度很重要。

当表数据有5000 isindex=1的有4800多条,所以isindex不会查询索引文件

处理TEXT 或者大文本:

合成索引:精确查询,当要查询text大文本数据的时候可以在表里面添加一个字符,这个字符存储大文本text的md5加密信息或者hashcode信息,记录text的密钥,所以只要按照文本的对应的密钥查询就行了：比如；

Insert into text select 0,repeat(‘mysql hello’,1000),md5(repeat(‘mysql hello’,1000))  这样就把text文本信息加密给MD5了,查询的时候拿到text文本信息去MD5配对

Select * from text where md5(repeat(‘mysql hello’,1000));

前缀索引:当查询大文本时候,使用迷糊查询,给文本加上前缀索引,提高效率:

Create index index_qz on table_name(content(10));

Select * from text where content like ‘mysql %’

每个表至少支持16个索引。Myisam和innodb引擎默认都是btree索引.myisam支持全文索引,只限于char.varchar和text列

创建普通索引:create index index_name on table_name(name)

创建前缀索引:create index index_name on table_name(name(20)); 带上长度就是前缀索引

删除索引:drop index index_name on table_name

创建唯一索引:create unique index  index_name on table_name(name)

创建指定类型索引:create index index_name using btree on table_name(id)指定是btree索引.

处理浮点数和定点数:

Double float(9,2) 或者real来表示浮点数:插入的值超过精度会四舍五入,会出现误差

Decimal(9,2) 表是定点数:实际是以字符串形式存储,可以完整的保留精度

UTF-8:对应3个字节

EXPLAIN 详解

Id: MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

select_type 查询类型：

SIMPLE: 简单的 select 查询,不使用 union 及子查询

 PRIMARY：最外层的 select 查询

UNION: UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集

DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集

SUBQUERY: 子查询中的第一个 select 查询,不依赖于外 部查询的结果集

DEPENDENT SUBQUERY: 子查询中的第一个 select 查询,依赖于外部 查询的结果集

DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里

UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估

UNCACHEABLE UNION: UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询

type显示连接使用的类型,按最 优到最差的类型排序

system：表仅有一行(=系统表)。这是 const 连接类型的一个特例。

Const: const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。

eq_ref：const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System

ref: 连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值, 这个值表示所有具有匹配的索引值的行都被用到

ref_or_null：如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找

index_merge: 说明索引合并优化被使用了

unique_subquery：在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery: 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range：只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。

Index: 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

All：最坏的情况,从头到尾全表扫描

possible_keys:  能在该表中使用哪些索引有助于 查询。如果为空,说明没有可用的索引。

Key：MySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引

key_len: 使用的索引的长度。在不损失精确性的情况 下,长度越短越好

ref：显示索引的哪一列被使用了

rows：MYSQL 认为必须检查的用来返回请求数据的行数

extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化

Using filesort: 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”

Using temporary：表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by

视图

视图主要简化复杂查询SQL,不能提高查询速度.可以嵌套视图

限制:select 语句不能包含from子句的子查询不能使用*

Select 语句不能引用系统或者用户变量

Select语句不能引用预处理语句参数

在存储子程序内,定义不能引用子程序参数或者局部变量

创建视图:create or replace view view_name as select ….

删除视图:drop view view_name;

查询所有视图:DESC  INFORMATION_SCHEMA.VIEWS;

查询视图表:select * from INFORMATION_SCHEMA.VIEWS where table_name=’view_name’

详细信息:show create view view_name G

视图约束:比如创建视图v1，create or replace view v1 as select * from table_name where a<2

With check option

在创建其他视图,都是v1视图上做查询

Create or replace view v2 as select * from v1 where a>0  with local check option;

在创建一个视图v3：也在v1的基础上创建

Create or replace view v3 as select * from v1 where a>0 with cascade check option

使用insert into v1 select 2 插入视图数据会报错因为a要小于2

其他的也一样..一般都在试图上做添加操作

存储过程

存储过程可以有3个参数:in(输入) out(输出) inout(输入和输出)

打开delimiter @@ 把;换成@@

Create procedure pro_name(in id int,out outIds int)

[Reads sql data] 只读,

[modify sql data]只写

[contains sql]默认不读也不写

Begin

         Select  id from table_name where num=id;

Select found_rows() into outids;

end@@

call pro_name(1111,@a) ;调用

select @a; 查询变量a

创建函数的方式:create function constr(str varchar(50))  returns char(50)

Return concat(‘xxx’,’!’);

删除存储过程和函数:drop procedure if exists pro_name;

查看存储过程和函数的状态:Show procedure|function status like ‘pattern’

查看过程和函数的详细信息:show create procedure pro_name G

变量的使用:

声明变量:Declare varName varchar(10) default ‘mysql’

设置变量值:Set varName =’java’

Select name into varName from table_name where id=11;

Select varName;

Declare处理程序

Declare可以定义变量:declare name varchar(30) default ‘java’;

游标

声明游标—打开游标—使用游标—关闭游标

控制语句

If 条件 then …

Elseif 条件 then ….

Else ….

End if

Case xxx

When xxx then …

When yyy then ..

End

Case

When 1 then ….

When 2 then …

….

End;

Leave hqj:退出循环

触发器triggers

Mysql支持的行级的触发,不支持表的触发

定义dept表和emp表 插入emp表的deptname的时候触发dept表的行

New.deptname 调出新插入的值

删除触发器:drop trigger trigger_name;

查看所有的触发器: show triggers G;

查看触发器详情: show create trigger trigger_test G

查看数据库所有的触发器名称select trigger_name from information_schema.triggers

触发器中不能控制事务,和存储过程

事务和锁

锁定表:lock table table_name read;只能读不能写,write只能写不能读

解除锁定表:unlock tables;

设置自动提交:set autocommit=1 自动提交设置为0的时候自动不提交,默认自动提交(1)

提交:commit 后会使用默认的事务

提交开启新的事务:commit and chain

提交链接:commit and release

事务:

比如两个session其中一个session设置事务:

开始事务:start transaction; 后做插入和更新操作,在本session中不提交,可以看的到新插入和修改的数据,不在同一个session中就不会有更新或者插入的数据,除非本session1 commit;了

SQL MODE

解决数据库迁移问题:和数据合法性的校验

查看SQL MODE:select @@SQL_MODE;

设置SQL 模式 set session sql_mode=’ANSI’ 设置sql_mode为ANSI 标准模式

在标准的mode中,比如create table t(name varchar(5))

插入超过5个字符的数据,insert into t values(‘1111111111’)不会报错,只是会截取,给个警告而已,当设置严格的mode插入超过5个字符的数据就报错了.

设置严格的模式:Set session sql_mode=’ STRICT_TRANS_TABLES’

数据库工具

。。。。以后再看

权限管理

Use mysql 数据库系统

查看user表 desc user

查看db ：desc db

查看表的权限:desc table_priv

查询user表select_priv 是否有查询的权限

select user,host,select_priv from user where user='root' and host='localhost';

grant :授权

grant select on db.table_table to user_name@localhost; 授查询权限

grant all privileges on *.* to  ‘root@localhost’ identified by ‘password’;

索引优化

 

SQL 查询执行的步骤顺序