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 查询执行的步骤顺序
