mysql 相关语法

mysql -h localhost -u root  -p 
create database  xxx  default character set utf8;
alter（修改） 创建库 指定编码格式
show databases ; 查看所有的库
show create database xxx; 查看库编码格式
drop database xxx; 删除库
库名不能被修改
数据约束（表约束）
默认值： default 默认值
非空：   not null
唯一： unique
主键： primary key （非空+唯一）
自增长： auto_increment
外键： foreign key   约束两种表
constraint xxx foreint key(xx) references dept(id)  外键约束声明
外键名称 外键参考表
create table class(id int); 创建表
create tbble T1 like T; 复制表结构
insert into T1 select * from T.....；复制数据
show tables; 查看所有的表
rename table old xxx to new xxx;修改表名
drop table xxx; 删表
use xxx; 进入表
desc xxx  ; 查看表的结构
alter table student rename to stu;  改表名
alter table xxxx add column name varchar(3) comment '注释'; 表中加字段
drop 删字段
modify 改
change 被改变的列名 列声明 修改列
first 第一列   
after  xxx  在某列后










set names  gbk; 编码格式修改 发送到数据库的语句用的编码格式
charset utf8;设置编码格式


insert into xxx (列1, 列2, ...) values(值1, '值2',...);
delete from 表名称 where 删除条件;
truncate table 表名  连带删除约束 且不可回滚
update 表名称 set 列名称=新值 where 更新条件;
select 列名称 from 表名称 [查询条件]




alter table 表名 add 列名 列数据类型 [after 插入位置];增加列
change 待修改列名  列声明
drop


字符集与校对集
客户端使用的字符集  set character_set_client = gbk/utf8
转换器 connection
返回给客户端 results
校对集  排序规则


unsigned
not null defualt 0








where 条件查询 where  基表中找 
子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用


where条件显示特定的行。
字段 between  xxx  and  xxx      比较运算符
字段 in（，，）  在某集合内
字段 notin(,,) 不在某集合内
xxx is null 是否为空  
xxx  is  not null 是否不为空
字段 like 'xxx'  以xxx开头 %任意数量字符  _  占位符 单个字符


group by 统计函数一起用 和聚合函数配合使用
max(xxx列名) 最大
min（xxx列名） 最小
sum（列名） 总和
avg（列名） 平均
count（*） 总行数
as  取别名


having  缓存表中找 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特


定的组，也可以使用多个分组按标准进行分组。
select age, id-i as a from xxx where age>3 having a>100;


order by  多字段排序  列1，列2    
字段名 desc 来降序排列
升序排列 asc  默认也是升序
发布时间排序 add_time


limit 在语句的最后，起到限制条目的作用
limit offset,N    Offset偏移量 N取出条目  Offset 可不写 相当于limit 0,N
从第offset 行开始的N个行数


where 把内层查询结果当作外层查询的比较条件 //三种子查询
  例:select * from stu where password in (select stu.passsword from stu where stu.password='123');
from  把内层的查询结果供外层再次查询
  例:select * from  (select stu.password from stu where stu.password='123')as a;
where exists 把外层查询的一横数据拿到内层，看内层的查询是否成立
  例:select * from xx where exists (select * from ss where xx.id=ss.id);












union 合并两次查询结果 各select查出的列数一致  自动去重  不去重 union all
如果子句中有order by  ，limit  要用（）括子句 不然不能通过语法检查 


连接查询
select * from 表1 inner join 表二 on   匹配条件 5个子句 内连接 左右连接的交集


select * from 表1 left  join 表二 on   匹配条件 左连接 推荐左连接 兼容性好
select * from 表1 right join 表二 on   匹配条件 右连接
不支持外连接   union联合左右连接  也可以达到






view 视图的修改不一定能改变表
create view 视图名 as select 语句
drop
alter
视图数据与表的数据一一对应时，视图必须包含表中没有默认值的列
视图的 algoritm  = merge/ temptable /undefined
merge: 当引用视图时，引用视图的语句与定义视图的语句合并 最终一条语句 可能失效
temptable：当引用视图时，根据视图的创建语句建立一个临时表 最终两条语句
undefined：未定义，自动，让系统帮忙选


1.告诉服务器，发送的编码格式   character_set_client
2.告诉转换器，转换成编码格式   character_set_connerction
3.查询结果的编码格式 character_set_results
以上3句简写 set names N
乱码产生情况 client声明与事实不符 result与客户端页面不符
数据丢失情况 connetion 和服务器的字符集比 客户端小




触发器：trigger
show triggers 查看触发器 
drop
作用：监视某种情况并触发某种操作
触发时间
监视事件类型
监视地点
触发事件
create trigger triggerName
after/berfor   insert /update/delete on  表名
for each row #固定语句
beging 
sql语句，触发事件可多个sql ；分号结束 和结尾分号冲突 要改变结束符
end；


delimiter $  结束符则为$
insert 新增行 new   列值  new.列名
delete old
update 改前 old  改后 new




例子：g t 表  much值超过5  改成5
create trigger  g
berfor    insert  on t
for each row
begin
if nwe.much>5 then
set new.much=5;
end if;
update g set num =num-new much where id=new.gid;
end$


表引擎  engine=
Myisam：批量插入速度快，不支持事务，锁表
innodb：批量插入教慢，支持事务，锁行
全文索引：5.5版本都支持




事务
原子性： 操作过程不能被分割
隔离性：操作过程不能被访问
一致性：操作成功或失败前后数据一致
持久性：操作完成，保留下来


开启事务：start transaction
提交事务：commit
回滚：rollback


隐式提交：如 start transaction  调用存储过程 修改表结构  


数据备份与恢复
例：导出mugua库下的account表
Mysqldump -u 用户名 -p 密码 库名 表1（空格隔开）表2 > 地址/备份文件
不写表名  就是库下所有表   只会有表的建立  插入语句
Mysqldump -u 用户名 -p 密码 -B 库名  导库  
-B 换成-A 导出所有库
恢复
source < D:\\xxx.sql   库级单位
先进库 表级单位


索引  二叉树   哈希散列
加快查询速度 
降低增删改的速度 增加了表的大小
1.不过度索引 
2.索引条件 适合where后面频繁的条件
3.索引散列值  过于集中的值 即相同值 不索引


普通索引：index 仅仅加快查询速度
主键索引：unique index 行上的值不能重复
唯一索引：primary key 不能重复，主键必须唯一，但是唯一索引不一定是主键，一张表上可以是多个唯一
全文索引：fulltext index   Match(全文索引名) against（"keyword"）；
mysql默认情况 对中文意义不大 英文用空格 逗号分割  mysql 无法识别每个中文词
全文索引停止词：不针对非常频繁的词做索引 如 this，is，you，my


查看一张表上所有索引
show index from 表名
建立索引
alter table 表名  add index/unique/fulltext [索引名]（列名）
alter table 表名  add primary key (列明) 不加索引名，因为主键只有一个


删除索引
alter table 表名 drop index 索引名
alter table 表名 drop index primary key  删除主键索引


存储过程：
可以使用if/else,case,while do 等控制结构 可以进行sql编程


procedure 存储过程 和函数相似  没返回值 名字也不同
show procedure status 查看现有存储过程
drop procedure 存储过程名字 
call 存储过程名字(xxx);


create procedure p1存储过程名字() 
begin
select * from xxx;
end$




create procedure x(n smallint 用于传参)
begin
select * from xx;
declare创建变量 i int;
set赋值 i=1;
set i=i+1;
if i>0
sql语句
else
sql语句
end if;
end$


delimiter $


create table a ;
insert into colin(name) values('123132131');
select * from colin where state = 0;
select * from colin where id = 4;
select * from colin where id = 4 and id = 5;
select * from colin where id = 4 or id = 5;
select * from colin where id = 4 and name = '12313213 1';
select * from colin where id = 4 and name like '123%';
select * from colin where id = 4 and name like '%123';
select * from colin where id = 4 and name like '%123%';


写的顺序：select ... from... where.... group by... having... order by.. limit [offset,] (rows)
执行顺序：from... where...group by... having.... select ... order by... limit








执行顺序
FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产


生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三


个步骤，一直到处理完所有的表为止。
WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。