MySQL基础操作到存储过程

创建龙珠数据库：create database qlz；
创建人物表：create table roal(name varchar(15)  ,zhanli int(10) zerofill,id int PRIMARY KEY auto_increament)；
注意：自增长属性要和主键一起用
插入人物数据；insert into roal (name,zhanli) values('孙悟空'，10000);
删除表：drop table roal；

建立时间表：create table date(day date,day_time datetime,thistime time,this_year year);
        date 日期      datetime 日期+时间   time 时间     year 年 

插入时可以直接调用获取时间的函数：insert into date values(CURDATE(),NOW(),TIME(NOW()),YEAR(CURDATE));

char和varchar区别：char分配的长度全部用来存字符串，给到多少长度就只能存多少长度的字符串，char的长度范围在0-255。
        当给定的字符串长度不够时会用空格右填充到指定长度，因此char给定多少长度就占多少位。
        varchar长度是变长的且会用额外用1或者2个字节来记录当前存放的字符串长度，当存放的字符串长度小于
        给定长度时，varchar只会占用实际长度。
        char不会保留字符串后面的空格而varchar会保留

建立含枚举字段的表（可用于优化表）（枚举字段设置not null在插入的时候不指定默认去第一个值：
create table enumm(meiju enum('男','女') not null,name int)；
inster into enumm (name) values(1);(这条语句执行后默认enum为男)

enum用于选择单个选项，要选择多个就用set。
创建含有set集合的表：create table setb(interest set('看电视'，'玩游戏'，'听歌')，name char（8）,age int);
插入数据：insert into setb values('看电视，玩游戏'，'小明',5); （注意：在给有集合的表中插入数据，要把集合中的数据用单引号包含）
set集合有几个元素就有对应的位数如上面的set有三个元素每个元素对应（000），插入对应元素对应位就置1
如上插入 看电视，玩游戏，听歌（111换算成10进制就是7）这时可以直接用数字代替
    insert into setb values(7,'小明'，5)；

创建联合主键(一个表只能有一个主键)：create table class(nianji char(2),banji char(2),name char(64),age int,PRIMARY KEY(nianji,banji));

添加主键：alter table keytable add constraint keyy PRIMARY KEY (id);
删除主键和唯一键/外键：alter table keytable drop primary key(id);
修改默认值和非空：alter table keytable modify num int default 5 not null;
取消默认和非空：alter table keybale modify num int;
删除一列：alter table keytable drop column teacher;

索引：数据库底层索引实现主要有两种存储类型，B树和HASH索引
创建索引：create table sy(id int,name char(20) UNIQUE,tname char(20),index (id DESC));(ASC升序，DESC降序)
索引查找：select * from sy where id>1;
表没有索引追加索引：方法一:create index index_id on sy(id ASC); 方法二：alter table sy add index id_index (id DESC);
查看索引是否生效：explain select * from sy1 where id>1;(主要查看possible_key,key,key_len字段)；
建立唯一索引：create table sy2(id int,idd int,iaa int,unique index id_index (id desc));
设置唯一属性后可以不用追加唯一索引。

全文索引以词为基础，mysql默认的分词是所有非字母和数字的特殊符号都是分词符。
创建全文索引：create table qwsy(id int,name varcher(128) unique,teacher varchar(64),comment varchar(1024),fulltext index index_qw (comment));
全文检索：select * from qwsy where match(comment) against('世界你好')；(中文索引的话需要将标点符号前的文字作为检索条件)
追加全文索引：alter table qwsy add fulltext index qwindex (comment)
删除索引：drop index qwindex on qwsy;

要支持中文词组检索需修改MySQL的配置文件（my.ini）在[mysqld]下面添加一行：ngram_token_size=2;
创建中文词组索引：create table zwsy(id int ,name varchar(20),comment varchar(128),fulltext index zwindex (comment) with parser ngram);(完成上
上述操作就可以进行中文词组检索)

创建多列索引：create table dlsy(id int,name varchar(10),age int,index dlindex (id,name));
多列索引查找：select * from dlsy where id>1 and name='小明'；（创建多列索引后在检索的时候必须包含第一个索引字段，如果直接从后面的字段索引，其他索引就会失效）
隐藏索引：alter table dlsy alter index id invisible;
取消索引隐藏：alter table dlsy alter index id visible

向表中插入部分数据：insert into dlsy(age) values(5)；插入部分数据的时候需要将部分数据对应的字段提示出来。
插入多条记录：insert into dlsy (id,age) values(1,2),(2,3),(0,4);
更新数据库的字段：update dlsy set id=1 where id=100;
当更新字段数据的时候如果不用where做限定 就是会将这个字段的所有值更新为设置的值：update dlsy set id=100;
可以多字段同时更新：update dlsy set id=100,name='xiaoming' where age=5;
删除某条符合条件的数据：delete from dlsy where id=1;
删除所有记录：delete from dlsy ;

在实际的开发当中如设计游戏角色的数据库时不太适合将所有信息键为一个表主要有三方面的考虑：
    一：面向对象方面考虑 -用户信息和用户验证是两种对象；
    二：性能方面考虑：    -登录验证的时候列较少，查询速度快，当有大量查询请求时效果更为明显，甚至可以成为不法分子的一种攻击手段；
    三：安全方面考虑：      -防止在查询角色信息时，把隐私信息也查出来，这样会容易被攻击和进行恶意操作

查找列：select field1,field2,...fieldn;select * 中*就代表所有列；用*查询时查询出来的结果会按照创建时的字段的顺序显示，而用指定字段查询可以自定义字段位置，
去重查找：select distinct field from table;
包含内容查询：select * from table where field in(field1..fieldn);反包含查询：select * from table where field not in (field1,field2);
范围查找：select id from crouse where match between 50 and 100;
模糊查找：select name from course where name not like '_%师'；‘_’通配一个字符,'%'通配0个或多个字符；
排序查询：select name,id from grade order by math asc;

求和查询：select class_id,sum(math) from grade group by class_id; 此外还提供求函数avg(),max(),min(),count()用法同sum。
 select class_id,count(*),group_concat(name) from grade group by class_id;解释按class_id分组并统计各分组总数并显示每个分组中name元素；

内联查询：select * from class inner join student on class.id= student.id;  ，
在内联查询的时候由于两个表的字段有可能重名，在内联的时候可以对查询字段取别名，同时如果在内联查询的时候表的名称长也可以用as取别名
select class.id as cid,student.id as sid from class as a inner join student as b on a.id=b.class_id;

子查询：select name from student where class_id=(select class_id from class where teacher='刘老师');
in只查询：select teacher from class where id in(select id from student where name='小%');
exists查询：select * from class where id =102 and exists (select * from student where class_id=102 ); 如果102班存在学生记录，就查询102班的班级信息
any查询：seelect id,name,math+chinese+english zf from grade where (math+chinese+english) >= any(select score from scholarship);
all查询：seelect id,name,math+chinese+english zf from grade where (math+chinese+english) >all(select score from scholarship);

视图就是将图标中的某些列抽象出来组成的一张表；是虚表不是物理存在
查询用户数据库权限：select user，select_priv,create_view_priv from mysql.user;
创建私有列：alter table student add privacy varchar(64);
创建视图：create view vs as select id,class_id,name from student;
查看视图：desc vs；
查询视图：select * from vs;
多表上建立视图：create view vs as select student.id,student.name,class.name as cn,class.teacher from class inner join student on class.id = student.class_id;
更新视图：update vs set name='小花花' where name ='小花'；
删除视图：drop view vs;

创建插入触发器:create trigger triname after insert on student for each row update class set count=count+1 where class.id=new.class_id;
创建删除触发器：create trigger triname after delete on studen for each row update class set count=count-1 where class.id=new.class_id;
创建触发器同时执行多条sql语句：
DELIMITER $$                                                                                
create trigger tri_delete_student after delete on student for each row 
        BEGIN                                    
         Delete from grade where id = OLD.id;  #删除成绩表中的记录                                                        
         update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录   
         END;                                    
         $$                                       
         DELIMITER ;sql默认‘；’为结束符，在begin end中间的多条语句会有‘；’所有需要修改默认结束符；

查看触发器：use information_schema;（触发器存放在这个表中） select *from triggers; 
加条件限制：select * from triggers where trigger_name='trid'

创建存储过程：
    delimiter$$
    create procedure proc_delete_student(in sid int,out cname varcher(128))
    begin
    declare cid int;
    declare temp_name varchar(1218);
    select class_id into cid ,name from student where id=sid;
    delete from grade where id=sid;
    delete from student where id=sid;
    set cnmae = temp_name;
    update class set count=count-1 where id=cid;
    end;
    $$
存储过程调用：call proc_delete_student(2,@cname);
查看out的变量：select @cname;
声明变量：declare al,a2,a3 int default 1;(default 将变量初始化为1，若没有default限定将会将变量值默认为null)
对变量进行赋值：set a1=88;也可以用表达式为右值来赋值
同时为多个变量赋值：select id,name,teacher,count into id1,name1,teacher1,count1 from class where id=101;