mysql（3）mysql的sql语句入门
1.登录mysql与退出mysql
登录：mysql -u账号 -p密码 -h主机地址 -P端口号 -e 执行的sql语句； //密码一般不直接写出，回车后隐藏写入 ，-h可以不写默认是localhost
退出：quit，exit ，ctrl+c
备注：参数可分为全局和会话，可修改与不可修改，全局需要加上global，例如 set global 参数 =值，会话级别只对当前会话有效， 例如set 【session】 autocommit=1；中间可以省略session，例如需要全局的话需要 set global autocommit=1；不能持久化，重启后是默认值，全局设置对已经开启了会话的不起作用，只会对以后连接的会话有效，查看可以使用show 【global/session】 【variables 】 【like pattern】 变量名 来查看。
备注：dbms数据库管理系统 ，dba数据库管理员， sql（ddl 表结构，dml增删改 ，dql 查询，dcl数据库控制，tcl事物控制）
2.数据的导入与导出
1）数据的导入：source （运行在mysql下的命令）
select version（）；//查看当前数据库版本；
create database 数据库名称；//创建数据库
show databases ； //显示所有的数据库，产看数据库是否已经导入，也可以使用 show databases like ‘’pattern’ 的形式，_代表一个字符，%匹配多个字符
use 刚刚创建的数据库；
source 需要导入的表（文件名）；//导入表，假如是表，那么需要在使用use 后导入相应的库。
show tables ；//显示该库下的表
desc 原表名（不是文件名）；//显示表的结构
select * from 表名； //查看表内容
load data infile ‘~/data.txt’ into table persons fields enclosed by ‘”’ erminated by ‘|’ lines staring by ‘ ’ terminated by ‘’ ；//导入； fields字段处理可选项 enclosed by ： 表锁字内容包裹符，默认是‘’ ，空字符串； terminated by： 表锁字段的结束符 默认是‘\t’ ；escape by：特殊符号处理 方式 ，默认是“\\” 反斜杠； lines处理 staring by 开始符，默认空字符串 terminated by：结束字符，默认空字符串
2）数据库的导出
mysqldump（mysql bin中的命令）
mysqldump 数据库 > 地址 -uroot -p ;//默认-h本机
3）导出表
mysqldump 数据库名 表名1 表名2 > ~/person.sql -uroot -p ;//参数： -d只导出数据表结构不导出数据，导出默认字符采用，latin1，按原文件导出，不会乱码。
select * into file ‘~/data.txt’ fields enclosed by ‘”’ erminated by ‘|’ lines staring by ‘ ’ terminated by ‘’ from persons；//导出，需要保证该文件不存在； select */字段列表 into file 文件路径 from 数据源 ；fields字段处理可选项 enclosed by ： 表锁字内容包裹符，默认是‘’ ，空字符串； terminated by： 表锁字段的结束符 默认是‘\t’ ；escape by：特殊符号处理 方式 ，默认是“\\” 反斜杠； lines处理 staring by 开始符，默认空字符串 terminated by：结束字符，默认空字符串
3.数据库及表的创建以及删除及修改及表的复制及视图
1）数据库的创建
Create database 数据库名称；//创建数据库
create database 数据库名称 charset utf8；//指定字符集
备注：
关键字需要使用''，例如create database 'database' charset utf8 ，表也遵循该规则；
使用中文数据库名，在没有设置字符集时，可能会乱码，那么需要使用set names 设置字符集，设置后保存在数据库中的名字可能看起来实际乱码，但是那不叫乱码，是以另外一种形式表示。
数据库不可以更改名称，只可以更改字符集和 校对集//alter database 数据库 charset =utf8 collate utf8_bin;
2）数据库的删除
drop database 数据库名；
备注：删除不可逆
3）表的创建
int，bigint（长整型），varchar（动态分配空间，效率换取空间，适合用于不固定长度的地方）， char（固定长度，空间换取效率），date（默认的格式%Y-%m-%d），datetime,time,timestamp(带时区的概念)，double（m，n）（有效长度m，小数点后n位），float（用法同double），decimal（精度较高，用于钱有关的事物），blob（存放二进制文件，很少使用，16k），mediumblob（16m），
longblob（最大4g ，数据库能存储的单元最大也是4g），clob（存放文本文件），text等。
unique（可以为空，可以修饰一个或者多个字段，不可重复，但是null可以存在一个或者多个，存在联合，unique（a,b），两个都相同则不同，在建立unique会先建立索引），primary key（主键，每个表必须有且只有一个主键，无主键可以认为是无效的表，主键附带索引，且不能为null，不可以重复，存在联合主键，但是不建议使用，因为存在部分依赖的问题不满足第二范式），not null（不为空），foreign key （外键，可以为null，可以修饰一个或者多个字段，可以是单一外键或者是联合外键，会先建立索引，且外键对应字段的修饰类型需要一致，包括级联模式（on cascade），置空模式（set null），严格模式，在导入数据的时候可以设置set foreign_key_checks=0来关闭外键约束，增加速率，导入后设置为1开启，一般不建议使用外键，外键不好控制），constraint 约束名（给约束取名字，便于删除等操作），on delete cascade （级联删除），on update cascade（级联更新），auto_increment（当插入0或者null时，默认会自增，假如设置的值大于当前自增后的值，那么自增值会设置为当前值，在小于且不发生冲突的前提下能插入，自增值保持原来的值，在其他情况下次可能会发生主键冲突，解决主键冲突的办法下面有，假如想为0，那么可以用update，一张表只能有一个增长在innodb中必须位于索引之上；自增多少有初始默认值，可以通过设置来解决，（show variables like ‘auto_increment%’） set auto_increment_increment=5;设置自增为5，但是第一次还是自增原来的值，假如是复合索引，那么也是联合索引的第一列发生自增，且是先排序后自增，只有存在两个复合索引都相同时才自增，必须是整型）。
备注：
默认的排序规则是字符编码集_ci，这个编码集表示不区分大小写，倘若需要区分大小写，show collation会显示所有的排序规则，通过collate设置，create table a（name varchar not null unique charset utf8 collate utf8_bin）,cs ,bin结尾的是区分大小写的；// show collation；//查看所有的校对字符集；//查看当前数据库的校对集：show variables like ‘collation_database’
主键必须先删除后才能添加；alter table 表名 drop primary key；//不需要写名称，因为一张表只能有一个主键
charset可以设置字符编码集 （create table a（name varchar not null unique charset utf8））//show variables like ‘字符集’ ;查看相应的设置;当前数据库的字符集show variables like ‘character_set_database’;
设置局部变量@xxx （set @age=1； select @age；）
int bigint等占用字节是定的，int 4个字节 ，bigint8个字节，tinyint 1个字节，smallint 2个字节 ，mediumint 3个字节，例如int（3），3对设置的值在没有超过相应的int.max都可以插入
--表示注释
客户端和服务端相关的3个字符集；‘character_set_client ；‘character_set_results，‘character_set_collection；可以通过set names 字符集来一起设置；三个需要保持一致，当然也可以在my.cnf文件中配置，default_character_set=字符集；
varchar和char在严格模式下不允许插入比当前数字长度长的字符，char会删除尾部的空格，varchar不会删除前后的空格，但是varchar字符长度去除尾部空格后刚刚好等于varchar定义的长度，那么会去除空格后插入，不会报错，varchar 单行长度65535 个字节，0用来代表false和true的，null 占用一个存储长度，varchar的变长也会占用1-2个字节长度（长度小于或等于255是一个字节，否则是2个字节）来确定占用的存储的实际长度，，text占用10个字节
外键产生对子表的约束作用，如果外键值在父表中找不到对应的项，那么就会失败（在可以为null的情况下，可以为null），一般不建议使用外键，不好控制，外键存在首先要是innodb引擎，其他引擎无效，外键的字段类型需要完全与主键的类型一致，一张表中外键的取名不能重复，存在三种模式district严格模式， cascade级联模式，set null 置空模式，假如not null，那么置空失败，district（默认的，要保证相应的顺序）// constraint 外键名 foreign key （字段名） references 父表（字段名） on delete 模式 on update 模式；
对于text 与blob存在空洞的问题，也就是删除，还是占用空间，一般需要使用optimize table 来整理磁盘碎片，使用散列码（例如md5）可以优化该效率，保存的时候是散列码，寻找的时候也转换成散列码（例如 where text1=md5（repeat'xxxx'）），这个很少用；
例如电话号码使用bigint，ip使用inet_aton装换为整数使用bigint保存，取出后转换。
例如
create table if not exists emp charset utf8；
create table if not exists db.emp charset utf8；//在db数据库创建 emp表
create table 表名（字段1 修饰（宽度）...）
create table students （
sno int primary key auto_increment，
sname varchar（10） not null，
sgander char(1) defalut '男' charset utf8，
sgrade int not null comment '分数'， //comment 描述，在desc 中不会显示 ，但是在show create table name 中会显示；
tno int not null，
tbirth date not null，
constraint students_tno_fk foreign key(tno) references teachers（tno） on delete cascade
）engine =innodb default charset=utf8；
备注：int，bigint 定义的宽度没有实际的意义
create table teachers（
tno int primary key auto_increment，
tname varchar（10） not null，
）engine =innodb default charset=utf8；
create table employees（
eno int primary key auto_increment，
ename varchar（10） not null，
）engine =innodb default charset=utf8；
4）表的修改：
rename table 表名 to 新表名；//更改表名
alter table 表名 auto_increment=值；//off 或者true 0或者1
alter table 表名 modify 字段名；//去掉自增修饰即可，
alter table 表名 drop index 索引名称 ；//删除唯一键 ，唯一键默认使用字段的名称作为索引名
alter table 表名 add 字段名 数据类型 first / after 字段名 //first 在第一个 after 字段名 位于某字段名之后，默认加在末端
alter table employees add age int ；//添加字段
alter table employees modify age int ；//修改字段
alter table employees drop age；//删除字段
alter table students drop foreign key students_tno_fk；//删除外键
alter table 表名 change 旧字段名 新字段名 数据类型 位置（first /after 字段名）；
alter table students add constraint students_tno_fk foreign key(tno) references teachers（tno）on update cascade on delete cascade//添加级联删除与更新；
alter table 表名 character set 字符集;//对以前插入的数据无效，只对之后插入的数据有效
alter database character set 字符集;//对以前插入的数据无效，只对之后插入的数据有效
5）删除表
drop table if exists 表名；//如果存在就删除 ，假如没有if exists 不存在就会报错；
drop table 表名1，表名2.//多表删除
6）视图的创建和删除
视图的创建
备注：可以使用join创建多表视图，多表视图不可以增删，但是可以改，还需要有对应的字段，没有的不能使用，例如视图的id是不存在的，
create view students_view as select * from studens；//对视图中的数据更改会对students的表的元数据更改；
视图的删除
drop view if exists students_view；//如果视图存在就删除
视图的修改
alter view 视图名 as select ...；
备注：视图的作用保证数据的安全，隐藏无关数据，删除视图不会对原视图造成影响，在单表中的增删改，都有影响，多表中的改有影响；
备注：如果在select语句中包含count ，max， avg ，min，sum时，且顺序要比外表的查询语句靠右需要使用 temptable ，其他情况下默认即可 // create algorithm=指定算法 view 视图 as select..；
7）表的复制
create table students_copy as select * from studens；//复制的表和原students没有任何的关系；
create table 表名 like 表名 ；//表结构的复制；
8）索引的创建和删除（主要用于百万级的数据，或者用在不变化的数据（较多），不然没有太大的意义）（详细见优化）
创建：
create index students_index on students（sname）；//在students的sname上添加上一个索引；
create unique index students_index on students（sname）；//不能重复的索引；
删除
drop index students_index on students；//删除索引，在oracle中 on students可以不写；
备注：mysql 所使用的是b +tree的数据结构 （平衡树（左旋，右旋），插入相对较慢一些，查询很快）
4.数据库及表的查询操作及表中的数据
4.1）数据库的查询
show databases ； 查询所有的表
show databases like ‘%es%’ ;//模糊查找过滤；
select database（）； 查看当前处于哪个数据库；
4.2）表
use test ；//使用库
show tables ；//显示该库下的所有表
show tables like ‘%es%’；//在数据库test下模糊查找具有es字段的表；
show create table 表名 ；//查看怎么创建的表
4.3）select 语句
4.3.1） +，-，*，/，%
select sgrade*2 as '2倍分' ，sname from students；//as 别名 ，+，-，/，%类似
4.3.2）> ,< ,>= , <=, != ,<>
select sgrade>90 as '是否大于90分，0表示false，1表示true' ，sname from students where sgrade >60；//在大于60分的同总共大于90的同学，< ,>= , <=, != ,<>类似
4.3.3）between 。。。and。。。
select count（*），sname from students where sgrade between 80 and 100；//80 到100之间的人数
4.3.4）max，min ，sum ，count ，avg，distinct
select count（*），sname from students；//这些函数不能用在where后面，*包括null，用字段不包括null，只能在分组的情况下能和其他非这些的字段匹配使用，否则不能匹配使用；
select count（ distinct sname ） from students;
select distinct sname ，sgrade from students ；//去和sname和sgrade两个都重复的结果,distinct的位置不能变
4.3.5）str_to_date，date_format ,format，adddate（加日期） ，subdate（减日期），addtime（加时间），subtime（减时间）
Mysql的日期格式 %Y:年，%m:月，%d:日，%H:时，%i:分，%s:秒；备注：区分大小写
Mysql的默认格式：%Y- %m-%d %H:时，%i:分，%s:秒 ，字符串会相应的转型
select name from students where sbirth>'2018-1-1';//自动转型，int等写成'6'也会自动转型等， 出生晚于2018-1-1的人
select name from students where sbirth>str_to_date('1-1-2018','%m-%d-%Y');//纠正字符格式；
select date_format(birth,'%m-%d-%Y') where sbirth>str_to_date('1-1-2018','%m-%d-%Y');//纠正格式，按指定格式输出； format处理数字的；
4.3.6）trim,upper,lower,substr（被处理字段，下标，长度）,length，round，rand, ifnull(字段，如果为空的默认值）；
select upper（name） from students where sbirth>'2018-1-1';//小写变大写 ，上述其他用法类似
4.3.7）order by 字段名 asc /desc
select sgrade，sname from students order by sgrade ；//默认升序asc
select sgrade，sname from students order by sgrade asc；//升序
select sgrade，sname from students order by sgrade desc；//降序
select sgrade，sname from students order by sgrade desc ，sname desc；//不同升序，相同降序
4.3.8）is null ， in ，not ，like，regex（类似java），
select name from students where name is null；//name为空的数据
select name from students where name is not null ；//name 不为空的数据
select name from students where name in （‘张三’，‘李四’）；//找出是张三或者李四的人
select name from students where name not in （‘张三’，‘李四’）；//找出不是张三或者李四的人
select name from students where name like '%s_s%';//模糊查找，%表示0个或者多个字符，_表示一个字符
4.3.9）group by having
select tno， max（sgrade） from students group by tno；//只能加分组字段（tno），其他字段不报错，但是结果不对，然后还能使用count，max等组函数
select tno， max（sgrade） from students where tno !=1 group by tno；//期初tno不等于1，后分组；
select tno， max（sgrade） from students where tno !=1 group by tno having avg（sgrade）>80；//having后面能放 avg等字段；
select tno， max（sgrade） from students where tno !=1 group by tno having avg（sgrade）>80 order by desc limit1；//group by having orderby limit的顺序不能变
备注： concat 一个或者多个字符连接函数，with rollup；//任何一个小组都会统计，//select c_id count（*） from mytable group by age with rolllup；
执行顺序：
from --where --groupby--select--orderby
from----groupby--select--having--orderby
4.3.10）union（具有去重的作用，获取的字段数必须一致（默认），假如不想去除重复 使用union all order by 在联合查询中需要使用括号阔成一个整体才行，若要orderby 生效还需要使用limit搭配，limit使用最大值）
（select tno， sgrade from students ）union （select tno， sgander from students ）；//连接显示，也不会报错。
4.3.11）password （），MD5（），encode（根据指定的字符串作为密码加密），decode（根据指定的字符串作为密码解密）
4.4）连接查询
4.4.1）内连接//交集，条件都需要满足才会显示（一个重组的过程）
select s.name ,g.cls from students s join grades g on s.sgrade between g.gmin and g.gmax;//非等值连接
select s.name ,g.cls from students s join grades g on s.sgrade=g.sgrade;//等值连接
select s.name ,s.sname from students s join students t on s.tno=t.sno;//自连接
select s.name ,t.tname from students s inner join teachers t on s.tno=t.tno;//inner 可以省略，主要是便于阅读，按left right来判定连接
备注：还有一个交叉连接，没有实际的意义 cross join == from 表1，表2； 自然连接 natural join 根据字段匹配，没有实际意义
4.4.2）外连接//左连接以左边的表为基准，假如满足右边，则显示左边和右边的重组，假如不满足右边，那么右边为null（任何值与null运算都是null），
select s.name ,t.tname from students s left join teachers t on s.tno=t.tno;//左外连接，左连接 join前面outer省略了
select s.name ,t.tname from students s right join teachers t on s.tno=t.tno;//右外连接，右连接 join前面outer省略了
select s.name ,p.pname from students s , persons p;//全连接,没啥意义
4.4.3）多表连接
select s.name ,t.tname from students s join teachers t on s.tno=t.tno join grades g on s.sgrade between g.gmin and g.gmax ;
备注：先创建父表，再创建子表 ，先删除子表在删除子表， 插入数据先插父表，再插入子表
4.4.4）子连接
备注：还有 in any some all 字段； exists 位于where之后 返回结构是0或者是1
select 【select】 from 【select】 where 【selelct】//select返回的结果作为一个表或者一个值，不能放在limit中
select name from studens where sgrade > select avg(sgrade) from students;
select s.sname ,(select t.name from teachers t where s.tno=t.tno) as tname from students s;
4.4.5)limit
select sname,sgrade from students order by sgrade desc limit (pageNo-1)*pageSize,pageSize;//分页仅适用于mysql
select sname from students order by sgrade desc limit 0,1；//0 ，可以省略，从1开始长度为1；、
4.4.6）now（）//表示当前时刻，可以带纳秒now(6)，最大6位，sysdate（）//开始执行该动作的时间 ，类似于可重复读与不可重复读的关系
4.4.7）case when...then..else...end或者case... when...then..else...end
select sname ,case when sgrade>90 then '优' when sgrade>80 then '良' else ‘加油’ end as ‘段位’ from students；
select sname ,case tno when 1 then '老师1' when 2 then '老师2' else ‘老师3’ end as ‘老师’ from students；
4.4.8） 变量名
show variables like ‘’//变量名的查看
select @@变量名// 具体变量名的查看
set 变量名=xxx； //变量名的设置，会话级修改
set global 变量名=xxx； //全局修改，重新登录才有效
5.增，删，改
insert into students (sname, sgrade...) values('lili','90'...)，...;//增 ，批量增加
insert into stu1 select * from stu;//将stu的插入stu1；
delete from students where id=1；//删除
update students set sname =‘lili’ where id=2；//修改
update students set sname =‘lili’ order by sal limit 10；//修改前几行，删除也可以类似
6.数据库引擎
查看所有的引擎：show engines；显示yes表示当前支持的引擎，no表示不支持的引擎，需要安装，默认的引擎回家defalut
innodb：服务器崩溃后自动恢复，acid兼容，支持提交，回滚等事务处理，支持级联删除和更新，行级锁定，日志文件等，行锁，支持高并发，共享模式也是保存在三张表中，frm,..dir,..path分别是结构，数据，索引，多表空间存储存在分表，结构frm文件，但是数据和索引保存在单独的.idb文件中
memory：速度快，但是存于缓存中，不能持久化，表锁
myisam：格式文件，数据文件，索引文件三张表组成，适用于查询多，不适用增删改多，表锁
7.枚举和集合
create table mytable （gender enum（‘男’，‘女’））；//enum 代表枚举 ，用数字表示 ，可以使用gender +0 来计算；默认是严格模式，也必须是严格模式，只能插入枚举中已经有的数据，最大65535个选项，占用一个字节来存储，牺牲效率节省空间，从0开始；
create table mytable （sport set（‘足球’，‘篮球’））；//可以在set中多多选 例如 insert into mytable values （‘足球，篮球’）；也可以插入数组，规则是十进制转换成2进制，1表示有，0表示没有
8.对应关系
一对一：一张表的记录对应另外一张表的一条记录进行对象，反之亦然
一对多：一张表的一条记录对应另一张表的多条记录
多对多：一张表的一条记录对应另一张表的多条记录，反之亦然；
9.主键冲突
insert into 表名 （字段名包含主键） values （值列表） on duplicate update 字段=新值； //冲突更新
replace into 表名 （字段名包含主键） values （值列表） ；//查看是否存在 存在就删除后添加，不存在就添加
10.变量
select @变量名 ；//变量的定义 ，会话级别
set @ 变量名：=值 ；//这里可以写=，但是有些场合不能，因为=认为是==判断
declare 局部变量名 类型 defalut 默认值；//只能使用在 begin ... end块 中；
set 局部变量名；//赋值
11.事务
acid//jdbc
可重复读：发出数据后，发出的那一刹那，直至完成数据依旧保持在哪个时刻，就像now 与sysdate
start transaction ；
dml...
commit；（提交）//需要手动
备注：可以设置//set autocommit=off/0；设置后需要手动高的commit 和rollback，innodb是行锁，在事务操作默认会升级为表锁
start transaction ；
dml....
rollback； （失败后回滚）//需要手动
设置事务隔离级别
set global transaction isolation level read commited；// read uncommited repeatable read serializable
set autocommit =off /on；省略了session，所有的ddl语句是不能回滚的，并且部分ddl语句会隐式提交，//oracle也是这样
不全部回滚
start transaction
语句..
savepoint sp1；
语句
rollback to sp1；回滚
12.数据库表设计规范
1）有主键且原子不可再分；
2）所有的非主键完全依赖主键，而不是部分依赖主键，所有不能使用联合主键；
3）非主键与主键不能存在传递性依赖；
备注：2），3）不一定要强制性实现，看实际情况。 范式的作用主要是减少数据的冗余，但是一般来说会降低效率；后一范式必须满足前一范式
13.触发器
创建触发器：
delimiter // ：以斜杠结束语句
create trigger 触发器名称 before/after（位置） insert（监听的事件） on 表名 for each row begin
insert into test2 set a2=new.a1；//插入的时候执行的语句
delete from test3 where a3=new.a1；//new 新值 old 旧值（原来的值）
end//
select triggers //查看触发器；
show create trigger 触发器名称；//查看触发过程；
select * from information_schema.triggers where 条件；//查看触发器信息
drop trigger 触发器名称//删除触发器，触发器不能修改，只能删除后新建
备注：
一张表最多只能有6个触发器，且只能有一种触发时间的一种类型的触发器，触发器是总动触发的
所有的触发器都是在一张表中 information_schema.triggers，触发器不能显示或者映射的开始或者结束事务
14.分支结构
if 条件 then 代码 else 代码 endif //类似 if（条件，x，y） ；
循环名称： while 判断条件 do 代码 leave /iterate 循环名称 end while ； //leave 类似break iterate类似continue
15.函数
create function 函数名（参数） returns 数据类型-- 规定返回值类型
begin
函数体
返回值
end//作用于外部是一个延迟的过程
show function status like ‘’； //查看所有函数
show create function 函数名；//查看创建语句
drop function 函数名； //函数只能先删除后重建；
15存储过程//一个没有返回值的函数
create procedure 过程名称 （参数）
begin
过程体
end
备注：过程没有返回值 select不能访问
show procedure status like ‘’； //查看所有存储过程
show create procedure 存储过程名；//查看创建语句
drop procedure 存储过程名； //只能先删除后重建；
调用使用call 存储过程名；
备注：参数类型有3中 in 可以是值，out必须是变量，inout 必须是变量 //create procedure 过程名 （in 形参名称 数据类型 ，out 形参名称 数据类型，inout 形参名称 数据类型），蒸锅过程是滞后的，只有在内部完成之后，赋值才给传入给外部的全局变量。
16.事件调度器
create event 名称 on schedule every 5 second do 语句（insert... ）；//每个5s调度一次
show events；//查看调度器的状态
show variables like ‘scheduler’；//查看调度器的状态 off 表示关闭；
alter event 名称 disable；//禁用调度器
drop event 调度器名称；//删除调度器；
