2017年06月30号课堂笔记
2017年06月30号 星期五 多云 空气质量：中度污染
内容：MySQL第三节课
一、delete 和 truncate的区别
01.delete
begin （开启事务）
select * from grade;(查询年级表中所有的数据)
delete from grade; (删除年级表中所有的数据)
select * from grade;(查询年级表中所有的数据，没有数据)
rollback; （事务回滚）
select * from grade;(查询年级表中所有的数据,删除的数据恢复)
commit （提交事务)
02.truncate
begin （开启事务）
select * from grade;(查询年级表中所有的数据)
truncate table grade; (删除年级表中所有的数据)
select * from grade;(查询年级表中所有的数据，没有数据)
rollback; （事务回滚）
select * from grade;(查询年级表中所有的数据，没有数据)
commit （提交事务)
03.区别：
001.delete后面可以拼接where条件，删除指定的行！
truncate只能删除表中所有的数据！不能有where！
002.delete可以回滚，数据库可以恢复！
truncate 不能事务混滚，数据不可以恢复！
003.truncate执行效率高！
二.事务的特性 ACID
01.事务：一条或者多条sql语句的集合！
02.事务的特性
001.原子性 (Atomicity)：在事务中的操作，要么都执行，要么都不执行！
002.一致性（Consistency）：事务必须保证数据库从一个一致性的状态变成另一个一致性的状态！
003.隔离性（Isolation）:每个事务之间互不干扰！哪怕是并发执行也不干扰！
004.持久性（Durability）:事务一旦被改变，那么对数据库中数据的影响是永久性的！
三.查询
01.将查询结果保存到 新表中
create table newStudent
(select stuName,address from student)
02.使用 具体的列 代替 *
select * from student;
select stuName,age ,address from student;
03.使用别名
SELECT gradeID AS 年级编号,gradeName '年级 名称' FROM grade;
*格式:
001. 列名 AS 别名
002. 列名 别名
003. 如果别名中有特殊符号，必须把 别名用 单引号 引起来！
04.查询年级表中 id不等于1的数据 <> !=
SELECT gradeID,gradeName
FROM grade
WHERE gradeid <> 1
05.合并
001.+ :
必须是相同的数据类型，而且是能转换成2进制的数据！
如果有一个列是null 整体返回null！
002.我们通常使用 concat来做合并
SELECT CONCAT(loginPwd,',',studentNAME) AS 合并列 FROM STUDENT
06.null
001.标识 空值
002.不是0，也不是空串" "
003.只能出现在定义 允许为null的字段
004.只能使用is null 或者is not null 进行比较！
使用is null 的时候 要确保 查询的列 可以为空！
SELECT * FROM student
WHERE loginPwd IS NULL
07.聚合函数
001.概念：SQL基本函数，聚合函数对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。
002.具体函数及应用
count() 查询某个字段的行数
max()查询某个字段的最大值
min()查询某个字段的最小值
sum()查询某个字段的和
avg()查询某个字段的平均值
-- 查询成绩表的总成绩
SELECT SUM(studentResult) FROM result;
-- 查询成绩的平均值
SELECT AVG(studentResult) FROM result;
-- 查询成绩的最高分
SELECT MAX(studentResult) FROM result;
-- 查询成绩的最低分
SELECT MIN(studentResult) FROM result;
-- 查询有成绩的总数
SELECT COUNT(studentResult) FROM result;
08.常用的字符串函数
001.-- concat(str1,str2...strN) 连接字符串
SELECT CONCAT('h','el','lo') FROM DUAL;
002.-- insert(str,begin,length,newStr) 替换字符串
-- str初始的字符串 begin 开始的位置 从1开始
-- length 替换长度 newStr替换的字符串
SELECT INSERT('hello',2,3,'55555') FROM DUAL;
003.-- subString(str,begin,length) 截取字符串
SELECT SUBSTRING('hello',2,3) FROM DUAL;
004.-- lower(str)转换成小写
005.-- upper(str)转换成大写
09.dual
001.我们称之为 伪表！
1. dual 确实是一张表.是一张只有一个字段,一行记录的表.
2.习惯上,我们称之为'伪表'.因为他不存储主题数据.
3. 他的存在,是为了操作上的方便.因为select 都是要有特定对象的.
如:select * from mytable ;
select * from myview;
等等.
但如果我们不需要从具体的表来取得表中数据,而是单纯地为了得到一些我们想得到的信息,
并要通过select 完成时,就要借助一个对象,这个对象,就是dual;
002.在mysql中是一个摆设
select 9*9;
select 9*9 from dual;
select * from dual; 报错
003.oracle中 必须使用 from dual;
select 9*9 from dual; 正确的
select 9*9 ; 错误
004.dual是一个只有一行一列的表！只能查询！ 不能对 dual进行增删改！
10.和并列
001.建立表testa
DROP TABLE IF EXISTS `testa`;
CREATE TABLE `testa` (
`name` varchar(20) DEFAULT NULL,
`subject` varchar(20) DEFAULT NULL,
`score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `testa`(`name`,`subject`,`score`) values ('张三','语文',80),('李四','语文',90),('王五','语文',70),('张三','数学',60),('李四','数学',98),('王五','数学',100);
002.需要成绩和科目 在一列 显示 根据name分组
SELECT
`name` AS 姓名,
GROUP_CONCAT(`subject`,':',score) AS 成绩
FROM testa
GROUP BY `name`;
11.常用的日期和时间函数
-- 001.获取 年月日
SELECT CURDATE() FROM DUAL;
-- 002.获取 时分秒
SELECT CURTIME() FROM DUAL;
-- 003.获取 年月日 时分秒
SELECT NOW() FROM DUAL;
-- 004.获取年份
SELECT YEAR(NOW()) FROM DUAL;
SELECT YEAR(CURDATE()) FROM DUAL;
-- 005.获取小时
SELECT HOUR(NOW()) FROM DUAL;
SELECT HOUR(CURTIME()) FROM DUAL;
-- 006.获取分钟
SELECT MINUTE(NOW()) FROM DUAL;
SELECT MINUTE(CURTIME()) FROM DUAL;
-- 007.获取当前日期是本年的第几周
SELECT WEEK(NOW()) FROM DUAL;
-- 008.获取两个日期之间的天数
SELECT DATEDIFF(NOW(),'2015-06-01') FROM DUAL;
-- 009.获取给定日期之后的某个日期
SELECT ADDDATE(NOW(),30) FROM DUAL;
12.天花板函数
-- 001.返回大于或者等于指定表达式的最小整数
SELECT CEIL(3.0) FROM DUAL;
SELECT CEIL(3.1) FROM DUAL;
-- 002.返回小于或者等于指定表达式的最大整数
SELECT FLOOR(3.9) FROM DUAL;
SELECT FLOOR(4.0) FROM DUAL;
-- 003.返回0-1之间的随机数
SELECT RAND() FROM DUAL;
-- 004.四舍五入
SELECT ROUND(2.499) FROM DUAL;
SELECT ROUND(2.501) FROM DUAL;
13.ORDER BY 排序
ORDER BY 语句用于对结果集进行排序。
ASC：升序（默认），DESC：降序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
001. asc：默认值！升序 desc:降序
002.应用
-- 查询所有年级编号为1的学员信息，按学号升序排序
SELECT * FROM student
WHERE GradeID=1
ORDER BY Studentno ASC;
14.limit 分页显示
001.用法
-- 分页 使用 limit begin,pageSize
-- begin 从哪一条数据开始 （当前页码-1）*pageSize
-- pageSize 每页显示的数量
002.应用
-- 显示前4条记录
SELECT * FROM student
WHERE GradeID=1
ORDER BY Studentno ASC
LIMIT 0,4;
-- 每页4条，显示第2页，即从第5条记录开始显示4条数据
SELECT * FROM student
WHERE GradeID=1
ORDER BY Studentno ASC
LIMIT 4,4
003.图解
15.子查询
-- 子查询：把一个查询的结果 当成另一个查询的 字段，条件或者表！
SELECT studentName FROM student
-- 只能通过student 表 查询出 学生对应的 年级名称
-- 001. 先查询出 学生 武松 对应的 年级编号
SELECT GradeID FROM student WHERE studentName='武松'
-- 002.根据年级编号 取 年级名称
SELECT gradeName FROM grade WHERE GradeID=???
SELECT gradeName FROM grade WHERE GradeID
=(SELECT GradeID FROM student WHERE studentName='武松')
四、作业
看完mysql
五、老师辛苦了！
