MySQL总结

一、MySQL

1.1、sql语句

1.1.1、增加数据

insert into user(id, name) values (#{id}, #{name})

1.1.2、查询所有

select * from user

1.1.3、根据id查询用户

select * from user where id = #{id}

1.1.4、删除数据

delete from user where id = #{id}

1.1.5、更新数据

update user set id = #{id}, name = #{name} where id = #{id}

1.1.6、创建表

create table teacher(
	`id` int(40) not null primary key AUTO_INCREMENT,
	`name` varchar(40) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into teacher(`id`,`name`) values (1,'白老师');
insert into teacher(`id`,`name`) values (2,'洪老师');

create table student(
	`id` INT(10) not null primary key,
	`name` varchar(40) not null,
	`tid` int not null,
	key `fktid` (`tid`),
	constraint `fktid` foreign key (`tid`) references `teacher` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `student`(`id`, `name`, `tid`) values ('1','小白','1');
insert into `student`(`id`, `name`, `tid`) values ('2','小黄','2');
insert into `student`(`id`, `name`, `tid`) values ('3','小绿','1');
insert into `student`(`id`, `name`, `tid`) values ('4','小红','2');
insert into `student`(`id`, `name`, `tid`) values ('5','小黑','1');

AUTO_INCREMENT：主键自增

primary key：主键

1.2、三大范式

1.2.1、第一范式 (1st NF)

• 第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式。

1.2.2、第二范式 (2nd NF)

• 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）；
• 第二范式要求每个表只描述一件事情。

1.2.3、第三范式 (3rd NF)

• 如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式；
• 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

1.2.4、规范化和性能的关系

• 为满足某种商业目标 , 数据库性能比规范化数据库更重要
• 在数据规范化的同时 , 要综合考虑数据库的性能
• 通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
• 通过在给定的表中插入计算列,以方便查询

1.3、事务

1.3.1、事务

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

1.3.2、事务的ACID原则

• 原子性(Atomic)

  • 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节；

  • 事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性(Consist)

  • 一个事务可以封装状态改变（除非它是一个只读的）；

  • 事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少；

  • 如果事务是并发多个，系统也必须如同串行事务一样操作；

  • 其主要特征是保护性和不变性(Preserving an Invariant)。

    以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

• 隔离性(Isolated)

  • 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。
  • 如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。
  • 这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

• 持久性(Durable)

  • 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

1.3.3、基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/
 
-- 注意:
--  1.MySQL中默认是自动提交
--  2.使用事务时应先关闭自动提交
 
-- 开始一个事务,标记事务的起始点
START TRANSACTION  
 
-- 提交一个事务给数据库
COMMIT
 
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
 
-- 还原MySQL数据库的自动提交
SET autocommit =1;
 
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

1.4、函数

1.4.1、常用函数

数据函数

/* 绝对值 */
SELECT ABS(-8); 

/* 向上取整 */
SELECT CEILING(9.4); 

/* 向下取整 */
SELECT FLOOR(9.4);   

/* 随机数,返回一个0-1之间的随机数 */
SELECT RAND();  

/* 符号函数: 负数返回-1,正数返回1,0返回0 */
SELECT SIGN(0); 

字符串函数

/* 返回字符串包含的字符数 */
SELECT CHAR_LENGTH('狂神说坚持就能成功');

/* 合并字符串,参数可以有多个 */
SELECT CONCAT('我','爱','程序'); 

/* 替换字符串,从某个位置开始替换某个长度 */
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); 

/* 小写 */
SELECT LOWER('KuangShen'); 

/* 大写 */
SELECT UPPER('KuangShen'); 

/* 从左边截取 */
SELECT LEFT('hello,world',5); 

/* 从右边截取 */
SELECT RIGHT('hello,world',5); 

/* 替换字符串 */
SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  

/* 截取字符串,开始和长度 */
SELECT SUBSTR('狂神说坚持就能成功',4,6); 

/* 反转 */
SELECT REVERSE('狂神说坚持就能成功'); 

/* 查询姓周的同学,改成邹 */
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student 
WHERE studentname LIKE '周%';

日期和时间函数

/* 获取当前日期 */
SELECT CURRENT_DATE();   

/* 获取当前日期 */
SELECT CURDATE();   

/* 获取当前日期和时间 */
SELECT NOW();   

/* 获取当前日期和时间 */
SELECT LOCALTIME();  

/* 获取当前日期和时间 */
SELECT SYSDATE();   

/* 获取年月日,时分秒 */
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

1.4.2、聚合函数

函数名称	描述
COUNT()	返回满足Select条件记录的总数，如 select count(*) 【不建议使用 *，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值

COUNT：

/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;  /*推荐*/

从含义上讲， count(1) 与 count(*) 都表示对全部数据行的查询。

• count(字段) 会统计该字段在表中出现的次数，忽略字段为 null 的情况。即不统计字段为 null 的记录。
• count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
• count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。

很多人认为 count(1) 执行的效率会比 count(*) 高，原因是 count(*) 会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然， count(1) 和 count(*) 都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而 count(字段) 则与前两者不同，它会统计该字段不为null的记录条数。

下面它们之间的一些对比：

• 在表没有主键时， count(1) 比 count(*) 快；
• 有主键时，主键作为计算条件， count(主键) 效率最高；
• 若表格只有一个字段，则 count(*) 效率较高。

SUM：

SELECT SUM(StudentResult) AS 总和 FROM result;

AVG：

SELECT AVG(StudentResult) AS 平均分 FROM result;

MAX：

SELECT MAX(StudentResult) AS 最高分 FROM result;

MIN：

SELECT MIN(StudentResult) AS 最低分 FROM result;

1.4.3、MD5加密

实现数据加密

新建一个表 testmd5

 CREATE TABLE `testmd5` (
   `id` INT(4) NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8

插入一些数据

INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')

如果我们要对pwd这一列数据进行加密，语法是：

update testmd5 set pwd = md5(pwd);

如果单独对某个用户(如kuangshen)的密码加密：

INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';

插入新的数据自动加密：

INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）：

SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

1.4.4、总结

 -- ================ 内置函数 ================
 -- 数值函数
 abs(x)            -- 绝对值 abs(-10.9) = 10
 format(x, d)      -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)           -- 向上取整 ceil(10.1) = 11
 floor(x)          -- 向下取整 floor (10.1) = 10
 round(x)          -- 四舍五入去整
 mod(m, n)         -- m%n m mod n 求余 10%3=1
 pi()              -- 获得圆周率
 pow(m, n)         -- m^n
 sqrt(x)           -- 算术平方根
 rand()            -- 随机数
 truncate(x, d)    -- 截取d位小数
 
 -- 时间日期函数
 now(), current_timestamp();        -- 当前日期时间
 current_date();                    -- 当前日期
 current_time();                    -- 当前时间
 date('yyyy-mm-dd hh:ii:ss');       -- 获取日期部分
 time('yyyy-mm-dd hh:ii:ss');       -- 获取时间部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
 unix_timestamp();                  -- 获得unix时间戳
 from_unixtime();                   -- 从时间戳获得时间
 
 -- 字符串函数
 length(string)                             -- string长度，字节
 char_length(string)                        -- string的字符个数
 substring(str, position [,length])         -- 从str的position开始,取length个字符
 replace(str ,search_str ,replace_str)      -- 在str中用replace_str替换search_str
 instr(string ,substring)                   -- 返回substring首次在string中出现的位置
 concat(string [,...])                      -- 连接字串
 charset(str)                               -- 返回字串字符集
 lcase(string)                              -- 转换成小写
 left(string, length)                       -- 从string2中的左边起取length个字符
 load_file(file_name)                       -- 从文件读取内容
 locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
 lpad(string, length, pad)                  -- 重复用pad加在string开头,直到字串长度为length
 ltrim(string)                              -- 去除前端空格
 repeat(string, count)                      -- 重复count次
 rpad(string, length, pad)                  --在str后用pad补充,直到长度为length
 rtrim(string)                              -- 去除后端空格
 strcmp(string1 ,string2)                   -- 逐字符比较两字串大小
 
 -- 聚合函数
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函数
 md5();
 default();