一、增删改查
创建表：
create table tab1(
id primary int , -- primary:主键
name varchar(100) not null, --非空
email varchar(100) null, -- 允许空
age int default 20, -- 默认值
No int -- 默认值
)default charset=utf8; -- 既然数据库是文件就需要指定字符类型
create table tab2(
id int not null auto_increment primary key, -- auto_increment primary key 不允许为空，主键自增
name varchar(100) not null, --非空
email varchar(100) null, -- 允许空
age int default 20, -- 默认值
)default charset=utf8; -- 既然数据库是文件就需要指定字符类型删除表：drop table tab1;
清空表：delete from tab1;(慢，写入日志，可回滚)，truncate table tab1(速度快，不可回滚)
修改表：
添加列：
- alter table 表名 add 列名 类型；
- alter table 表名 add 列名 类型 dufault 默认值;
- alter table 表名 add 列名 类型 not null default 默认值;
- alter table 表名 add 列名 类型 not null primary key auto_increment;
删除列：
alter table 表名 drop column 列名;
修改列：
- alter table 表名 change 原列名 新列名 新类型; -- 修改类型
- alter table 表名 change 原列名 新列名 新类型; -- 修改列名和类型
- alter table 表名 alter 列名 set default 1000; -- 修改列默认值为1000
- alter table 表名 alter 列名 drop default; -- 删除列默认值
- alter table 表名 add primary key; -- 添加主键
- alter table 表名 drop primary key; -- 删除主键
- alter table tab1 change id int not null; -- 设置非空
- alter table tab1 change id int not null default 5;-- 设置非空，默认值为5
- alter table tab1 change id int not null primary key auto_increment;-- 设置非空，自增
- alter table tab1 change id id int; -- 允许为空，删除默认值，删除自增
外键：
- alter table tab1add constraint 外键名 foreign key tab1(No) references 另一个表的主键。-- 创建
- alter table tab1 drop foreign key 外键名 -- 删除外键
二、数据类型
1. int
- int：有符号，-2147483648-----2147483647，int 4个字节，32位，拿出来一位做符号位，数字表示有31位，2^31= 2,147,483,648，负数部分是2,147,483,648个，非负数部分是0----2147483647，也就是常见的2^31-1，因为是2,147,483,648个数，包括0，最大表示的值是2147483647。
- int unsigned：无符号，0----4294967295不需要符号位了，不能表示负数，所以是0-----2^32-1
- int(5)zerofill：这种不常用，表示定义了5位，如果不足5位，左侧用0补齐
- tinyint[m][unsigned][zerofill]：用法同上，只是范围不同，有符号：-128---127，无符号：0---255。2字节8位。
- bigyint[m][unsigned][zerofill]：用法同上，范围不同，有符号：2^63----2^63-1，无符号：0----2^64。8字节，64位。
2.decimal[m[,d]][unsigned][zerofill]
- 准确表示小数位，如decimal(10,6)表示这个值有10位，其中有6位小数。
3.float[m[,d]][unsigned][zerofill]
- float单精度小数部分能精确到小数点后面6位，用 32 位二进制进行描述。float单精度内存占4个字节
4.double[m[,d]][unsigned][zerofill]
- double双精度小数部分能精确到小数点后的15位，，用64位二进制进行描述，比float型更精确。
- double双精度内存占8个字节，是float型的两倍，且运算速度也比float慢得多
- decimal型比浮点型计算精度要高，通常使用更少的空间。所以通常我们在设置小数的时候，都是用的decimal类型
5.char(m)
- 定长字符，m最大255个字符。
- 一般超长会报错，也可配置文件设置不报错，配置文件加入：sql-mode="NO_AUTO_USER,NO_ENGINE_SUBSTITUTION"，保存并重启服务，这样会自动截断。
- 固定长度保存用空格补齐的，查询会自动清除空格，如果要保留空格配置文件加入：sql_mode="PAD_CHAR_TO_FULL_LENGTH"
6.varchar(m)
- 变长字符，m最大655535个字符。
- 超长也会报错，配置文件加入：sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，自动截断。
7.text
- 变长大字符串，最大65535(2^16-1)，不指定长度。长文本比如文章新闻.
8.mediumtext
- 最大16777215(2^24-1)个字符
9.longtext
- 最大字符4294967295 or 4GB(2^32-1)
10.datetime()
- YYYY_MM_DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59)
11.timestamp()
- 时间戳 ，YYYY_MM_DD HH:MM:SS(1970-01-01 00:00:00/2038 )。存储的时候用的时世界标准时间格式UTC，查询的时候返回的是根据当前人所在的时区。
12.date
- YYYY-MM-DD(1000-01-01/9999-12-31)
13.time
- HH:MM:SS()
其他待补充：
三、必备SQL要点
1.条件查询的 between and 相当于 >= and <=
2.exists 条件成立才去查询：如select * from tab1 where id exists (select * from tab2 where id>1)，子查询条件成立的情况下才去查询tab1；当然not exists相反。
3.通配符：%多个，_表示一个
4.取部分数据：limit，limit offset。
- select * from tab1 limit 10; -- 获取前10条
- select * from tab1 limit (0*10), 10; -- 第一页。
- select * from tab1 limit (1*10), 10; -- 第二页。
- select * from tab1 limit 3 offset 2; -- 从位置2开始，向后取前3的数据。
以上用于实现分页查询。
5.分组
6.左右连接
left outer join =left join
right outer join = right join
inner outer join = inner join = join
交叉联结 CROSS JOIN就是笛卡尔积
full outer join -- 全连接，这个mysql没有，在oracle中才有，左右表所有的信息
7.联合
要求：联合的集合必须列相同
union ：自动去重，union all ：不去重
四、授权
1.用户管理
默认用户都在user表里。
创建：
create user '用户名'@'host' IDENTIFIED BY 'password' ;
例：create user 'zhangsan'@'127.0.0.1' IDENTIFIED BY '123' ;
drop user 'zhangsan'@'127.0.0.1';
create user 'zhangsan'@'127.0.0.%' IDENTIFIED BY '123' ; -- 127.0.0.1-----256
drop user 'zhangsan'@'127.0.0.%';
create user 'zhangsan'@'%' IDENTIFIED BY '123' ; -- 任意地址下
drop user 'zhangsan'@'%';
修改：
rename 'zhangsan'@'%' to 'lisi'@'%'
修改密码：
set password for 'lisi'@'%' = password('123456789')
2.授权管理
grant 权限 on 数据库.表 to 'lisi'@'%'；
例：
- grant all privileges on '*' to 'lisi'@'%'; -- 将所有数据库的所有表的所有权限都给lisi
- grant all privileges on 'test.*' to 'lisi'@'%'; -- 将test库的所有表的所有权限都给lisi
- grant all privileges on 'test.aaa' to 'lisi'@'%'; -- 将test库的aaa表的所有权限都给lisi
- grant select on 'test.aaa' to 'lisi'@'%'; -- 将test库的aaa表的查询权限都给lisi
- grant select,insert on 'test.aaa' to 'lisi'@'%'; -- 将test库的aaa表的查询，插入权限都给lisi
- 注意：finsh privlilges; -- 将数据读取到内存，从而立即生效，即让分配的权限立即生效
五、数据
批量导入：mysql -u root -p 数据库名 < 文件路径及文件名.sql -- cmd方式，文件是一堆insert语句
批量导出：
- 结构+数据：mysqldump -u root -p 数据库名 > 文件路径及文件名.sql --生成的语句文件
- 结构： mysqldump -u root -p -d 数据库名 > 文件路径及文件名.sql --生成的语句文件
其实mysqldump执行文件在mysql安装目录下，如果环境变量已添加，直接用，未添加需要mysqldump前面要加上全路径。
六、索引
B+tree，比查找的值大在右侧，小的在左侧，通过这种结构减少查找次数，提升速度。而且有索引的列是额外维护了的数据结构，因此缺点也就暴漏出来，增删改数据的时候会变慢。
注意：创建表时候，指定不同的引擎B+tree结构原理略有不同。如：myisam，非聚簇索引(数据和索引结构分开存储)；innodb，聚簇索引(数据和主键索引结构存储在一起)
执行计划：用于分析语句，作为参考，explain 语句。type：性能由低到高，all全表扫描<index全索引扫描<range对索引列进行查找<index_merge合并索引，使用多个单列索引搜索<ref根据索引直接去查找(非键)<EQ_REF 连表操作的时候常见<const 常量，表里最多有一个匹配行，就一行<system表中只有一行
七、函数
常用函数：
reverse(str)：反转函数；
concat(str1,str2....)：拼接字符串函数，可多个拼接，但是有一个Null，最终结果都是null；
concat_ws(separator,str1,str2....)：根据分隔符separator来拼接字符串函数，可多个拼接，忽略Null。
char_leng(str)：返図值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集，LENGTH()进田值为10，而CARLEG()的值为5。
conv(N,from_base,to_base)：进制转换。如：select conv('a',16,2) = 1010
FORMAT(X,D)：将数字x的格式写为"#,###,###.#”，以四含五入的方式保留小数点后D位，井将结
果以字符串的形式活因。若D为0，则返回结果不带有小数点，或不含小数部分。
例：SELECT FORMAT(12332.1,4):结果为:“12,332.1000
insert(str,pos,len,newstr)：在str的指定位置插入字符串。pos：要替换位置起始位置。冷：替换的长度。newstr：新字符串。特殊情况：如果pos超过原字符串长度，则返回原字符串，如果len超过原字符串长度，则由新字符串完全替换。
left(str,len)：返回str从开始位置len长度的字符串。
lower(str)：变小写
upper(str)：变大写。
ltrim(str)/rtrim(str)：去除左侧/右侧的所有空格。
substring(str,pos,len)：截取str字符串。pos：开始位置，len：长度。
locate(substr,str,pos)：获取子序列索引位置。第一次出现在字符串str中的位置。pos：从pos位置查找，不写就是从头查找。
REPEAT(str,count)返回一个由重复的字符事str 姐成的字符事，字符串str的数目等于count。 count<-0,则近回一个空字符串。若str或count内NULL，则透图 NULL。REPLACE(Btr,from str,to str)
返回字符串str以及所有被字符串to_str替代的字符串Irom_str
REVERSE(atr)
返回字符串stx，顺序和字符顺序相反。
RIGKT(atr,lon)
从字符串str 开始，返回从后边开始1on个字符组成的子序列
自定义函数
delimitor $$
create function f1(x int ,y int)
returns int
begin
declare num int;
declare resutl int;
set result = x+y;
return result;
end $$
delimitor ;
执行：select f1(1,2);注意： 正常来说mysql是遇到;识别为终止，delimitor $$目的是把;修改为 $$，因为函数不只是一行。最后 把终止符修改回来 delimitor ;
八、存储过程
sql语句的集合。
delimitor $$
create procedure p1(
in para1 int;
inout pr1 int;
out resul1 int;
)
begin
declare resutreturn int;
set resul1 = para1 * 2;
set pr1 = pr1 * 4+ para1 ;
end $$
delimitor ;
执行：call p1(10,2,0);---第三个虽然是返回值，但是也需要传，随便传一个。
实际使用：
set @t1=2;set @t2=0;
call p1(10,@t1,@t2);
select @t1,@t2;存储过程的参数3种：in 仅用于传入参数，out：仅用于返回值，inout：即可当传入参数也可当返回值。
还有存储过程可以返回结果集：比如存储过程执行语句是select 一张表。
异常：
delimitor $$
create procedure p1(ourput code tinyint)
begin
declare resutreturn int;
declare exit bandler for sqlexpection; -- 定义出错异常代码，这样就走这个
begin -- error
set code=1;
rollback;
end;
declare exit bandler for sqlexpection; -- 定义警告异常代码，这样就走这个
begin -- warning
set code=2;
rollback;
end;
start transaction -- 开启事务
delete from xxx where id=1;
insert into xxx(name)values('aaaaaa');
commit; -- 提交事务
set code=0; -- success -- 定义成功代码，这样就走这个
end $$
delimitor ;
call p1(x);
set @t1=2;
call p1(10,@t1);
select @t1;游标
BEGIN
-- 声明游标
DECLARE cur_salsum CURSOR FOR
SELECT
Id,EmpCode,Date,OrdCode,StyleNo,CXCode,CXType,SysCode,StationCode,StationName,StepCode,Price,BruDayCount,AddCount,DelCount,TotalCount
FROM DayCountEasy ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET curdone = 1; -- 没有下一条数据 修改标记为1
-- 打开游标
OPEN cur_salsum;
FETCH NEXT FROM cur_salsum INTO curId,curEmpCode,curDate,curOrdCode,curStyleNo,curCXCode,curCXType,curSysCode,curStationCode,curStationName,curStepCode,curPrice,curBruDayCount,curAddCount,curDelCount,curTotalCount;
-- 使用while循环语法
WHILE (curdone <> 1) DO
-- 取得系数 -- 生产单和款式号决定系数
SELECT IFNULL(CommonRate,1.0), IFNULL(QDRate,1.0),IFNULL(ZBRate,1.0),IFNULL(ZZRate,1),IFNULL(CustomerRate,1.0),IFNULL(NDRate,1.0)
INTO curCommonRatetemp, curQDRatetemp, curZBRatetemp,curZZRatetemp,curCustomerRatetemp,curNDRatetemp
FROM hr_salrate WHERE OrdCode=curOrdCode AND StyleNo=curStyleNo ;
set curdone=0;--每次有select语句的，用完之后需要置为0否则会不再执行
-- 系数和单价先乘起来
IF (curCXType='FrontEnd') THEN
set curRateSum=curPricetemp*curQDRatetemp*curCustomerRatetemp*curNDRatetemp;
set curCXRate=curQDRatetemp;
ELSE
set curRateSum=curPricetemp*curZZRatetemp*curCustomerRatetemp*curNDRatetemp;
set curCXRate=curZZRatetemp;
END IF;
-- 更新
UPDATE DayCountEasy set ActRank=ActRanktemp WHERE Id=curId;
-- 系数用完置为1,中间变量等置为0
set curPricetemp=0;
set curQDRatetemp=0;
set curZBRatetemp=0;
FETCH NEXT FROM cur_salsum INTO curId,curEmpCode,curDate,curOrdCode,curStyleNo,curCXCode,curCXType,curSysCode,curStationCode,curStationName,curStepCode,curPrice,curBruDayCount,curAddCount,curDelCount,curTotalCount;
END WHILE;
-- 关闭游标
CLOSE cur_salsum;
END;九、视图
临时表，虚拟表，一堆sql语句的集合，把这个集合起个名字就是视图 ，因为是虚拟的，所以不可插入不可更新的。
十、触发器
插入前
create trigger XXXXX_tb1 before insert/delete/updateon tb1 for each row
begn
.....
end
插入后
create trigger XXXXX_tb1 after insert/delete/update on tb1 for each row
begn
.....
end
十一、锁、事务
1.表锁、行锁。
2.myisan：只支持表锁。innodb：支持表锁和行锁
3.for update 排他锁：加锁后，其他不可读写
begin -- 或者是start transaction
select * from tab1 where name='' for update; -- name 非索引(表锁)
commit;-- 释放
begin -- 或者是start transaction
select * from tab1 where id='' for update; -- id是索引(行锁)
commit;-- 释放4.lock in share mode 共享锁：加锁后，其他不可写，只可读
begin -- 或者是start transaction
select * from tab1 where name='' lock in share mode; -- name 非索引(表锁)
commit;-- 释放
begin -- 或者是start transaction
select * from tab1 where id='' lock in share mode; -- id是索引(行锁)
commit;-- 释放十二、数据库连接池
建立连接和销毁连接都需要消耗资源，连接池的作用是保留一定数量的连接，当用户需要的时候从连接池中取用，用户不用的时候释放回连接池供别的用户使用。
