MySql笔记

数据库（）语言

DDL：定义

DML：操作

DQL：查询

DCL：控制

模板

select [all | distinct]
{* | table.* | [table.field1[as alias1],[table.field2[as alias2]]}
from table_name[as table_alias]
	[left | right | inner join table2_name]-- 联合查询
    [where...] 		-- 指定结果满足的条件
    [group by...] 	-- 指定结果按照那几个字段分组
    [having...] 	-- 过滤分组的记录必须满足的次要条件
    [order by...]   -- 指定查询记录按一个或多个条件排序
    [limit startindex, size] -- 分页

操作数据库

CREATE DATABASE IF NOT EXISTS WESTOS
DROP DATABASE IF EXISTS WESTOS
USE  SCHOOL
/*如果字段名是一个特殊字符,	需要使用符号包裹，USE `SCHOOL`*/
SHOW DATABASES

字段数据类型

数值

tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)

float：单精度浮点数，4个字节

double：双精度浮点数，8个字节

decimal：字符串形式的浮点数，防止精度丢失

字符串
char(n) 固定长度，最多255个字符
varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符

时间日期

date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 1970.1.1到现在的毫秒数

字段属性

自增

0填充

不为空

主键

默认值

创建表

create table if not exists `student`(
`id` int(5) not null auto_increment comment'学生id',
`name` varchar(30) not null default'匿名' comment'姓名',
`pwd` varchar(30) not null default'123456' comment'密码',
`gender` varchar(1) not null default '男' comment'性别',
`birthday` datetime default null comment'生日',
`address` varchar(100) default null comment'地址',
`email` varchar(50) default null comment'邮箱',
primary key (`id`)
)ENGINE =innodb default charset=utf8;

show create database school
show create table student

DESCRIBE student/*查看表的结构*/

关于数据库引擎

	MYISAM	INNODB（默认使用）
事务支持	不支持	支持
数据行锁定	不支持	支持
外键	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍MYISAM
	节约空间，速度较快	安全性高，支持事务，多表多用户操作

修改删除

alter table teacher rename as teachers; -- 重命名表
alter table teachers add age int(3); -- 添加字段 add
alter table teachers modify `name` varchar(30); -- 修改字段数据类型  modify
alter table teachers change `name` `teachername` varchar(30); -- 字段重命名  change
alter table teachers drop age; -- 删除字段
drop table  if exists teachers; -- 删除表

外键（约束）

-- 设置外键
`gradeid` int(2) not null,
constraint `FK_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`);
...

-- 添加外键
ALTER TABLE `school`.`student` ADD INDEX `FK_gradeid_idx` (`gradeid` ASC) VISIBLE;
ALTER TABLE `school`.`student` ADD CONSTRAINT `FK_gradeid`FOREIGN KEY (`gradeid`)REFERENCES `school`.`grade` (`gradeid`)ON DELETE NO ACTION ON UPDATE NO ACTION;

不建议使用外键

insert

INSERT INTO `grade` (`gradename`) VALUES ('大三');
INSERT INTO `school`.`grade` (`gradename`) VALUES ('大二');
insert into `grade` (`gradename`) values('大四'),('大五');
insert into `student`(`name`,`pwd`,`gender`,`birthday`,`address`,`email`,`gradeid`)
values('aaa','123456','男','1999-01-08','地球','123456@qq.com','1');
-- 插入失败id也会自增，处理办法
alter table `student` drop `id`;
alter table `student` add `id` int(5) not null primary key auto_increment first;

update

-- update
update `student` set `name`='张三' where `id`=1;
update `student` set `name`='张三',`address`='中国' where `id`=1;
update `student` set `name`='张三',`address`='中国' where `id` between 1 and 2;
update `student` set `name`='张三',`address`='中国' where `id`=1 and `gradeid`=1;
update `student` set `name`='张三',`address`='中国' where `id`=1 or `gradeid`=1;
update `student` set `birthday`=curdate() where `id`=1;

update关闭安全模式

-- SET SQL_SAFE_UPDATES = 0

delete&truncate

delete from `student` where `id`=1;
delete from `student`;-- 也可以删除一张表
truncate `student`;-- 清空一个表，并且自增计数器归零，不会影响事务

-- 使用delete删除表后，重启数据库
-- innodb：自增列从1开始（内存中，断电即失）
-- myisam：自增列从不会从1开始

select

select * from student;
select `studentno`,`studentname` from `student`;
select `studentno` as '学号',`studentname` as '姓名' from `student`;-- as 别名
select concat('姓名：',`studentname`)as '姓名' from `student`;-- concat()

select * from result;
select `studentno` from `result`;
select distinct `studentno` from `result`;-- 去重
select version();
select 100-99 as'result';
select `studentno`,`studentresult`+99 from `result`;
select `studentno`,`studentresult` from `result` where `studentresult`>=70 and `studentresult`<=90;
select `studentno`,`studentresult` from `result` where `studentresult`between 70 and 90;
select `studentno`,`studentresult` from `result` where not `studentno` =1000;

模糊查询

-- 模糊查询
-- is null
-- is not null
-- between and
-- like %代表多个字符，_代表一个字符
select `studentno`,`studentname` from `student` where `studentname`like '张_';
select `studentno`,`studentname` from `student` where `studentname`like '张&';
select `studentno`,`studentname`,`address` from `student` where `address`like('%北京%') or `address`like('%广东%');
-- in 是一个多个具体的值，不能用%
select `studentno`,`studentname` from `student` where `studentno`in (1000,1001);
-- null,not null
select `studentno`,`studentname` from `student` where `address`='' or `address` is null;-- is not null

联表查询

-- 联表查询
use school;
-- inner join
select `s`.`studentno`,`studentname`,`subjectno`,`studentresult` 
from `student` as `s`, `result` as `r` 
where`s`.`studentno`=`r`.`studentno`;

select `s`.`studentno`,`studentname`,`subjectno`,`studentresult` 
from `student` as `s`inner join`result` as `r` 
on`s`.`studentno`=`r`.`studentno` where `s`.`studentno`=1000;

-- left join
select `s`.`studentno`,`studentname`,`subjectno`,`studentresult`
from `student` as `s`
left join `result` as `r` 
on `s`.`studentno`=`r`.`studentno`;

-- right join
select `s`.`studentno`,`studentname`,`subjectno`,`studentresult`
from `student` as `s`
right join `result` as `r` 
on `s`.`studentno`=`r`.`studentno`;

-- 学号，姓名，科目名，分数
select `s`.`studentno`,`studentname`,`subjectname`,`studentresult`
from `student` as `s`
right join `result` as `r`
on `s`.`studentno`=`r`.`studentno` 
inner join `subject` as `sub`
on `r`.`subjectno`=`sub`.`subjectno`;

自连接

-- 自连接
CREATE TABLE `school`.`category`( 
`categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');
-- 自连接
select `a`.`categoryname` as '父类',`b`.`categoryname` as '子类'
from `category` as `a`,`category` as `b`
where `a`.`categoryid` =`b`.`pid`;

分页和排序

-- 排序 asc, desc
select `s`.`studentno`,`studentname`,`subjectname`,`studentresult`
from `student` `s`
inner join `result` `r`
on `s`.`studentno`=`r`.`studentno`
inner join `subject` `sub`
on `r`.`subjectno`=`sub`.`subjectno`
where `subjectname`='高等数学-1'
order by `studentresult` desc;-- asc

-- 分页 limit startindex(从0开始),pagesize;
select `s`.`studentno`,`studentname`,`subjectname`,`studentresult`
from `student` `s`
inner join `result` `r`
on `s`.`studentno`=`r`.`studentno`
inner join `subject` `sub`
on `r`.`subjectno`=`sub`.`subjectno`
where `subjectname`='高等数学-1'
order by `studentresult` asc
limit 1,1;

嵌套查询、子查询

-- 1.连接查询
select `studentno`,`subjectname`,`studentresult`
from `result` `s`
inner join `subject` `sub`
on `s`.`subjectno`=`sub`.`subjectno`
where `subjectname`='高等数学-1'
order by `studentresult` desc;
-- 2.子查询
select `studentno`,`subjectno`,`studentresult`
from `result`
where `subjectno`=(
	select `subjectno` from `subject`
    where `subjectname`='高等数学-1'
)order by `studentresult` desc;


select `s`.`studentno`,`studentname`
from `student` `s`
inner join `result` `r`
on `s`.`studentno`=`r`.`studentno`
where `subjectno` =(-- in
	select `subjectno` from `subject` where `subjectname`='高等数学-1' -- or `subjectname`='高等数学-2'
);

select `studentno`,`studentname` from `student` 
where `studentno`in (
	select `studentno` from `result` where `studentresult`>10 and `subjectno`=(
		select `subjectno` from `subject` where `subjectname`='高等数学-1'
    )
);

select `s`.`studentno`,`studentname`
from `student` `s`
inner join `result` `r`
on `s`.`studentno`=`r`.`studentno`
inner join `subject` `sub`
on `r`.`subjectno`=`sub`.`subjectno`
where `subjectname`='高等数学-1';

常用函数

数学函数

（1）ABS(x)

返回x的绝对值

（2）PI()

返回圆周率π，默认显示6位小数

（3）SQRT(x)

返回非负数的x的二次方根

（4）MOD(x,y)

返回x被y除后的余数

（5）CEIL(x)、CEILING(x)

返回不小于x的最小整数

（6）FLOOR(x)

返回不大于x的最大整数

（7）ROUND(x)、ROUND(x,y)

前者返回最接近于x的整数，即对x进行四舍五入；
后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

（8）SIGN(x)

返回参数x的符号，-1表示负数，0表示0，1表示正数

（9）POW(x,y)和、POWER(x,y)

返回x的y次乘方的值

（10）EXP(x)

返回e的x乘方后的值

（11）LOG(x)

返回x的自然对数，x相对于基数e的对数

（12）LOG10(x)

返回x的基数为10的对数

（13）RADIANS(x)

返回x由角度转化为弧度的值

（14）DEGREES(x)

返回x由弧度转化为角度的值

（15）SIN(x)、ASIN(x)

前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦

（16）COS(x)、ACOS(x)

前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦

（17）TAN(x)、ATAN(x)

前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切

（18）COT(x)

返回给定弧度值x的余切

（19）RAND()

返回一个0-1之间的随机数

字符串函数

（1）CHAR_LENGTH(str)

计算字符串字符个数

（2）CONCAT(s1,s2，...)

返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

（3）CONCAT_WS(x,s1,s2,...)

返回多个字符串拼接之后的字符串，每个字符串之间有一个x

（4）INSERT(s1,x,len,s2)

返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符

（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写

（6）LEFT(s,n)、RIGHT(s,n)

前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符

（7）LPAD(s1,len,s2)、RPAD(s1,len,s2)

前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符

（8）LTRIM(s)、RTRIM(s)

前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除

（9）TRIM(s)

返回字符串s删除了两边空格之后的字符串

（10）TRIM(s1 FROM s)

删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格

（11）REPEAT(s,n)

返回一个由重复字符串s组成的字符串，字符串s的数目等于n

（12）SPACE(n)

返回一个由n个空格组成的字符串

（13）REPLACE(s,s1,s2)

返回一个字符串，用字符串s2替代字符串s中所有的字符串s1

（14）STRCMP(s1,s2)

若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1

（15）SUBSTRING(s,n,len)、MID(s,n,len)

两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串

（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）

（17）REVERSE(s)

将字符串s反转

（18）ELT(N,str1,str2,str3,str4,...)

返回第N个字符串

（19）INSTR(s,str)

str在s中的首次出现的坐标，不存在则返回0

日期和时间函数

（1）CURDATE()、CURRENT_DATE()

将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

（4）FROM_UNIXTIME(date)

和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间

（5）UTC_DATE()和UTC_TIME()

前者返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中

（6）MONTH(date)和MONTHNAME(date)

前者返回指定日期中的月份，后者返回指定日期中的月份的名称

（7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；
DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；
WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

（8）WEEK(d)、WEEKOFYEAD(d)

前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周

（9）DAYOFYEAR(d)、DAYOFMONTH(d)

前者返回d是一年中的第几天，后者返回d是一月中的第几天

（10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

YEAR(date)返回指定日期对应的年份，范围是1970~2069；
QUARTER(date)返回date对应一年中的季度，范围是1~4；
MINUTE(time)返回time对应的分钟数，范围是0~59；
SECOND(time)返回制定时间的秒值

（11）EXTRACE(type FROM date)

从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

（12）TIME_TO_SEC(time)

返回以转换为秒的time参数，转换公式为"3600小时 + 60分钟 + 秒"

（13）SEC_TO_TIME()

和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式

（14）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

（15）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

返回将起始时间减去expr type之后的时间

（16）ADDTIME(date,expr)、SUBTIME(date,expr)

前者进行date的时间加操作，后者进行date的时间减操作

条件判断函数

（1）IF(expr,v1,v2)

如果expr是TRUE则返回v1，否则返回v2

（2）IFNULL(v1,v2)

如果v1不为NULL，则返回v1，否则返回v2

（3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

系统信息函数

（1）VERSION()

查看MySQL版本号

（2）CONNECTION_ID()

查看当前用户的连接数

（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

（4）CHARSET(str)

查看字符串str使用的字符集

（5）COLLATION()

查看字符串排列方式

加密函数

（1）PASSWORD(str)

从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

（2）MD5(str)

为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回

（3）ENCODE(str, pswd_str)

使用pswd_str作为密码，加密str

（4）DECODE(crypt_str,pswd_str)

使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

其他函数

（1）FORMAT(x,n)

将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回

（2）CONV(N,from_base,to_base)

不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制

（3）INET_ATON(expr)

给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特

（4）INET_NTOA(expr)

给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示

（5）BENCHMARK(count,expr)

重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间

（6）CONVERT(str USING charset)

使用字符集charset表示字符串str

聚合函数

-- count();
-- sum();
-- avg();
-- max();
-- min();
select count(`studentno`) from `student`;-- 会忽略字段为null的记录
select count(*) from `student`;-- 不忽略null
select count(1) from `student`;-- 不忽略null
-- group by
select `subjectname`,avg(`studentresult`),max(`studentresult`),min(`studentresult`)
from `result` `r`
inner join `subject` `sub`
on `r`.`subjectno`=`sub`.`subjectno`
group by `subjectname`
having avg(`studentresult`)>30;

数据库MD5加密

use `school`;
set sql_safe_updates = 0;
update testmd5 set pwd=md5(pwd) where char_length(`pwd`)<30;-- 应该插入的时候就加密

事务

-- 事务
-- 事务的原子性(Atomicity)：是指一个事务要么全部执行，要么不执行，
-- 也就是说一个事务不可能只执行了一半就停止了。比如你从取款机取钱，这个事务可以分成两个步骤：1划卡，2出钱。不可能划了卡，而钱却没出来。这两步必须同时完成，要么就不完成。
-- 事务的一致性(Consistency)：是指事务的运行并不改变数据库中数据的一致性。
-- 例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。
-- 独立性(Isolation）：事务的独立性也称作隔离性，是指两个以上的事务不会出现交错执行的状态。
-- 因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。
-- 持久性(Durability）：事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

-- mysql默认开启事务
-- 开启事务
set autocommit = 0;
-- 开始事务
start transaction;
-- 提交
commit;
-- 回滚 
rollback;
-- 事务结束
set autocommit = 1;

savepoint 保存点;
rollback to savepoint 保存点;
release savepoint 保存点;

CREATE SCHEMA `shop` DEFAULT CHARACTER SET utf8 ;
use `shop`;
create table `account`(
`id` int(5) not null auto_increment,
`name` varchar(20) not null,
`money` decimal(9,2) not null,
primary key(`id`)
)engine=innodb default charset=utf8;

insert into `account`(`name`,`money`) values('张三',2000),('李四',1000);

-- 转账事务
set sql_safe_updates =0;
set autocommit = 0;-- 关闭自动提交
start transaction;-- 开始事务
update `account` set `money`=`money`-500 where `name`='张三';
update `account` set `money`=`money`+500 where `name`='李四';-- 执行更新后数据就会发生变化
commit;
rollback;
set autocommit = 1;

索引

索引分类：

主键索引（primary key）：主键唯一标识

唯一索引（unique key）：是指所有记录中字段的值不能重复出现，唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

常规索引（key / index）

全文索引（FullText）

-- 索引
use `school`;
show index from `student`;
-- 添加索引
alter table `student` add unique key `studentname_index`(`studentname`);
-- 删除索引
ALTER TABLE `school`.`student` DROP INDEX `studentname_index` ;

权限管理与备份

-- 用户管理
-- 创建用户
create user 'user_test' identified by '123456';
-- 修改当前用户密码
-- set password =password('123');
-- 修改指定用户密码
-- set password for user_test = password('123456');
-- 修改密码
alter user user_test IDENTIFIED by '666666';
-- 重命名
rename user 'user_test' to 'user_t';
-- 授权
grant all privileges on *.* to 'user_t';
-- 撤销权限
revoke all privileges on *.* from 'user_t';
-- 显示权限
show grants for 'user_t';
show grants for root@localhost;
-- 删除用户
drop user 'user_t';

三大范式

-- 三大范式
-- 第一范式(确保每列保持原子性)
-- 第二范式(确保表中的每列都和主键相关)
-- 第三范式(确保每列都和主键列直接相关,而不是间接相关)

JDBC