文章目录
1、常用函数
1、数据函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
2、字符串函数
/*返回字符串包含的字符数*/
SELECT CHARACTER_LENGTH('坚持就是胜利')
/*合并字符串,参数可以有多个*/
SELECT CONCAT('我','爱','你')
/*替换字符串,从某个位置开始替换某个长度*/
SELECT INSERT('我爱编程',1,2,'超级热爱')
/*小写*/
SELECT LOWER('LOVE')
/*大写*/
SELECT UPPER('love')
/*从左边截取*/
SELECT LEFT('hello,world',5)
/*从右边截取*/
SELECT RIGHT('hello,world',5)
/*替换字符串*/
SELECT REPLACE('坚持就是成功','坚持','努力');
/*截取字符串,开始和长度*/
SELECT SUBSTR('坚持就是成功',4,2)
/*反转*/
SELECT REVERSE('坚持就是成功')
3、日期和时间函数
/*获取当前日期*/
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());
2、聚合函数
1、类型
函数类型 | 描述 |
---|---|
COUNT() | 返回满足Select条件的记录总和数,如 select count(*) 【不建议使用 *,效率低】 |
SUM() | 返回数字字段或表达式列作统计,返回一列的总和 |
AVG() | 通常为数值字段或表达列作统计,返回一列的平均值 |
MAX() | 可以为数值字段,字符字段或表达式列作统计,返回最大的值 |
MIN() | 可以为数值字段,字符字段或表达式列作统计,返回最小的值 |
2、sql语句
-- 聚合函数
/*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的记录条数。
下面它们之间的一些对比:
1)在表没有主键时,count(1)比count(*)快
2)有主键时,主键作为计算条件,count(主键)效率最高;
3)若表格只有一个字段,则count(*)效率较高。
*/
SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;
注意:
-- 查询不同课程的平均分,最高分,最低分
-- 前提:根据不同的课程进行分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;
where写在group by前面,要是放在分组后面的筛选, 要使用HAVING,因为having是从前面筛选的字段再筛选,而where是从数据表中的>字段直接进行的筛选的
3、事务
1、什么是事务
简单来说:要么都成功,要么都失败
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
2、事务的ACID原则
- 原子性
- 整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样
- 概括来说 :要么都成功,要么都失败
- 一致性
- 一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性
- 概括来说 :事务前后的数据完整性要保持一致
- 隔离性
- 事务的隔离是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间要相互隔离
- 隔离的级别
- 脏读,幻读
- 持久性 ———— 事务提交
- 在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚
- 概括来说 :事务一旦提交则不可逆,被持久化到数据库
3、基本语法和测试
1、基本语法
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点(了解)
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
2、测试
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION;
UPDATE account SET money=money-500 WHERE `name`='A';
UPDATE account SET money=money+500 WHERE `name`='B';
-- 提交事务,提交完之后就被持久化
COMMIT;
# rollback;
SET autocommit = 1; -- 恢复自动提交
4、索引
索引(Index)是帮助MySQL高效获取数据的数据结构,提取句子主干,就可以得到索引的本质:索引是数据结构
作用:
-
提高查询速度
-
确保数据的唯一性
-
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
-
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
-
全文检索字段进行搜索优化.
1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引(Primary Key)
- 唯一的标识,主键不可重复,只能有一个列作为主键
-
唯一索引 (Unique)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
-
常规索引 (Index)
- 默认的,index,key关键字来设置
-
全文索引 (FullText)
- 快速定位特定数据
2、 索引创建方式
/*
#方法一:创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
-- id_表名_字段名
#方法二:CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三:ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引:DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
3、测试索引
- 数据库建表
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
- 批量插入数据:100w
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写,标志
CREATE FUNCTION mock_data2()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'19224305@qq.com',FLOOR(RAND()*2),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
-- 执行语句,开始插入数据
SELECT mock_data2();
- 索引效率测试
无索引时
-- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999'
创建索引
CREATE INDEX id_app_user_name ON app_user(`name`);
测试普通索引
SELECT * FROM app_user WHERE name = '用户9998'
4、索引的原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表建议不要加索引
-
索引一般应加在查找条件的字段