个人学习总结,内容较多。 如有错漏,请指正,谢谢!
SQL语句在oracle中的执行过程: 1.语法检查,检查语句的语法是否存在问题。 2.语义检查,检查语句的寓意是否存在问题。 3.权限检查,检查当前用户是否有访问权限。 4.共享池
若存在共享池中->优化器(软解析)若不存在共享池中->解析器->执行器(硬解析)共享池是oracle中的术语,主要包括了库缓存以及数据字典缓冲区。 库缓存存储了SQL语句以及执行计划,数据字段缓冲区存储的是Oracle对对象的定义,比如表、视图、索引等。
主要用于判断该SQL语句是硬解析还是软解析。
SQL语句在MySQL中的执行过程: 1.查询缓存,服务端判断查询缓存中是否存在该条语句,若存在则直接返回给客户端,若不存在,则进入解析器。(查询缓存效率不高,MySQL8.0后,取消了查询缓存) 2.解析器,分析语句中的语义和语法。 3.优化器,会确定语句的执行路径,比如:判断是全表检索还是根据索引检索。 4.执行器,在执行语句之前,会先判断当前数据用户是否有权限访问,若有权限则返回执行结果。(MySQL8.0之前若设置了查询缓存,会将执行结果进行缓存)
DDL,常用语法有
--增加数据库 CREATE DATABASE data_db; --删除数据库 DROP DATABASE data_db; --创建表 CREATE table1(id INT identity); --删除表 DROP TABLE table1; --修改表 ALTER TABLE table1 ... --增加列 ->add column col_name type; --删除列 ->drop column col_name; --修改列名 ->rename column col_name to new_col_name; --修改属性的类型 ->modify column col_name typeSQL语句各个关键字查询顺序:
SELECT count(*) AS num,distinct t1.relate_id FROM table1 as t1 left join table2 as t2 USING(id) WHERE t1.times > "2020-01-01" GROUP BY id having num > 200 ORDER BY t1.relate_id desc limit 100;form->where->group by ->having->select 查询内容->distinct->order by ->limit
计算函数
函数名作用MOD取余,MOD(4,2) -> 4*VG求平均值 AVG(list),需要的是数据列表,相当于是聚集函数ROUND四舍五入,ROUND(data,2),data是需要四舍五入的数据,2是保留两位小数ABS求绝对值,ABC(-1)->1GREATEST取当前列,设置列名中最大的数值GREATEST的用法为: 例如:有表数据字段为以下表格
idmonthuesWed1234那么下面这句伪语句的结果是什么呢?
select GREATEST(mon,thues,wed) ## 输出结果为4转换函数
函数名作用CAST转换函数,CAST(A AS B),B为转换的类型,A为需要转换的数据COALESCE返回参数中第一个不为空的数,COALESCE(VALUE…),可以有多个参数这边需要注意的是,CAST中的B,不是正常的INTEGER等数据类型而是要参照以下的表格
支持的类型具体描述BINARY二进制型CHAR字符型DATE日期,格式为 ‘YYYY-MM-DD’DATETIME日期加具体的时间,格式为 ‘YYYY-MM-DD HH:MM:SS’TIME时间,格式为 ‘HH:MM:SS’DECIMALfloat 型SIGNEDint 型UNSIGNED无符号int select cast(2.3 as SIGNED) #输出结果为:2字符串函数
函数名作用SUBSTRING截取字符串,有三个参数,第一个参数,需要截取的数据,第二个参数,截取数据的起点,第三个参数截取数据的终点REPLACE替换函数,有三个参数,第一个参数,需要替换的数据,第二个参数,替换数据中需要替换的字符串,第三个参数,需要替换成什么字符串CONCAT拼接字符串,可以将数字与字符串进行拼接,用法为:CONCAT(“USER”,“user2”)->USERuser2LENGTH返回字符串的长度,汉字返回三个字节,字母和数字返回一个字节CHAR_LENGTH返回字符串的长度,汉字、字母、数字都是返回一个字节LOWER将字母转换成小写UPPER将字母转换成大写GROUP_CONCAT使用GROUP BY 语句时,可以将SELECT的字段合并在一起,用“,”分隔,这查询后,若有不一样的字段,也会合并为一行显示。substring_index根据字符串中的字符截取字符串,substring_index(column,‘字符’,1)left从左开始截取字符串,例如left(date,7),从左开始截取日期7位,获得结果:2020-11right从右开始截取字符串,例如right(date,7),从左开始截取日期7位,获得结果:0-11-13GROUP_CONCAT的使用方法
select school_no,student_name from student group by school_no,student_name;以上sql会查询出关于school_no的多行数据
school_nostudent_name1001张三1001李四1001王五1002赵六若使用了GROUP_CONCAT后
select school_no,GROUP_CONCAT(student_name) from student group by school_no,student_name;结果就如以下表格:
school_nostudent_name1001张三,李四,王五1002赵六GROUP_CONCAT的用法为
GROUP_CONCAT([distinct] column [order by column desc|asc])
substring_index的用法
用上面GROUP_CONCAT查询出来的数据进行对比
select substring_index(name,',',1) from (select school_no,GROUP_CONCAT(student_name) name from student group by school_no,student_name);已知name为张三,李四。那么substring_index语句中,第二个参数就是在字符串中查找“,”找到后,根据第三个参数进行截取,为1就截取“,”前的字符串(张三),为2就截取“,”前的两个字符串*(张三,王五)以此类推。所以该语句输出结果就为:
name张三王五日期函数
函数名作用CURRENT_DATE获取当前日期:yyyy-MM-ddCURRENT_TIME获取当前时间:HH:mm:ssCURRENT_TIMESTAMP获取当前日期时间:yyyy-MM-dd HH:mm:ssEXTRACT抽取日期时间的年月日时分秒,用法:EXTRACT(until from data)->until可以使用YEAR获取年,MONTH获取月等,data就是需要抽取的日期,如2020-10-01 20:48:10。DATE获取当前日期部分YEAR获取当前年MONTH获取当前月,由于月份是0-11月的所以若要获取正确的月份,需要+1DAY获取当前日HOUR获取当前小时MINUTE获取当前分钟SECOND获取当前秒钟DATE_SUB减日期DATE_ADD加日期DATEDIFF计算两个日期之间的天数date_sub 的用法为:DATE_SUB(date,INTERVAL val expr); date_add 的用法为:DATE_ADD(date,INTERVAL val expr); 其中val必须为数值,可为正负数,若SUB中 为负数的话,实际上就是+val,--得加嘛。同样的ADD中为负数就是-val。而expr则是你要操作的格式可为YEAR|MONTH|DAY|HOUR|MINUTE|SECOND等 主要用于查询date之前的数据或者date之后的数据,这种未来过去的数据。
其中DATE、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 这都是一样的用法,抽取传入的时间中的年月日时分秒。 若传入的时间格式错误,或者数据错误,则返回NULL。 例如:
SELECT DATE("2020-01-01 20:20:20");-- 2020-01-01 SELECT DAY("2020-01-01 20:20:20"); -- 01 SELECT YEAR("2020-01-01 20:20:20"); -- 2020 SELECT hour("2020-01-01 20:20:20"); -- 01DATEDIFF的用法为:
select datediff('2020-10-20','2020-10-19') ##结果为1 select datediff('2020-10-20','2020-10-21') ##结果为-1聚集函数
函数名作用SUM返回查询出来某个字段的数据的总和,例如费用总和->SUM(amount)COUNT返回集合的长度AVG返回集合中某个字段的平均值,例如费用平均值->AVG(amount)MAX返回查询集合中某个数据的最大值,例如查询离当前时间最近的值->SUM(cur_date)MIN返回查询集合中魔偶个数据的最小值,例如查询收入最低的人->MIN(salary)关联子查询与非关联子查询
关联子查询就是主表与从表有关联关系的连接查询。 非关联子查询就是主表和从表没有任何关系的连接查询。
EXIST查询
EXIST就是判断主表中的某个属性是否能在从表中查询到。 NOT EXIST就是与EXIST相反
SELECT * FROM users u where EXIST(SELECT * FROM roles r WHERE r.userid = u.id) SELECT * FROM users u where NOT EXIST(SELECT * FROM roles r WHERE r.userid = u.id)集合比较子查询
命令作用IN判断主表字段是否在从表中ANY需要与比较操作符一起使用,和返回集合的任何值做比较ALL需要与比较操作符一起使用,和返回集合的任何值做比较这其中 IN 与EXIST的返回的结果是相同的,但是不同的场景就需要使用不放的方法。
SELECT * FROM A a WHERE a.id IN (SELECT aid from B) SELECT * FROM A a WHERE EXIST (SELECT * FROM B WHERE aid = a.id )从以上可以看出,B表是A的从表。若A表大于B表的时候,此时若B表的aid列建了索引,那么使用IN的效率就会更高。若A表小于B表的时候,此时若A表的id列建了索引,那么就不需要再B表中查询aid列,那么使用EXIST的效率就会更高。
若主表大于从表,则使用IN 若主表小于从表,则使用EXIST
SQL标准有很多,但是常用的标准就是SQL92与SQL99。 SQL92的连接: 1.笛卡尔积又称交叉连接。 返回连接表的所有属性。
SELECT * FROM player as p,team as t2.等值连接将主表与从表中的等值数据进行连接。
SELECT * FROM player as p,team as t where p.id = t.player_id;非等值连接就是不使用等号的连接方式。
SELECT * FROM player as p ,team as t where p.score < t.score; --或者 SELECT * FROM player as p ,height_grades as h where p.height between h.lowheight and h.highheight;3.外连接 外连接分为左外连接、右外连接(在SQL99中存在全外连接) 左外连接:以左侧的表为主表,右侧的表为从表。返回左侧主表的所有属性的值(包括null),与右侧从表的连接属性。 右外连接:与左外连接相反。
以哪侧的表为主表,若主表连接的属性有空值,也会一起返回。
在SQL92中使用(+)表示左外连接还是右外连接
--左外连接 SELECT * FROM table1 t1 ,table2 t2 where t1.id(+) = t2.id --右外连接 SELECT * FROM table1 t1 ,table2 t2 where t1.id = t2.id(+)4.自连接主表与从表都为一张表,使用同一张表进行连接查询。 比如若要查询比张三年龄小的人出来 不使用自连接的方式,使用子查询的话,就需要两步来查询
--1.查询张三的年龄->20 SELECT age FROM person WHERE name = '张三'; --2.通过返回的age再查询比他小的人 SELECT * FROM person WHERE age<20;而使用自连接的方式,只需要一步就可以查询出来,效率会比子查询更高。
SELECT * FROM person p1,person p2 WHERE p1.name = '张三' AND p2.age < p1.age;在SQL99标准中,比SQL92标准的可读性更高,例如SQL92使用(+)来表示左外连接,还是右外连接,而SQL99中使用的是直观的LEFT JOIN 与RIGHT JOIN 来表示。
1.交叉连接其实就是SQL92的笛卡尔积连接,只不过SQL99中使用的是CROSS JOIN
SELECT * FROM player p CROSS JOIN team t where p.team_id = t.team_id;2.自然连接其实就是SQL92的等值连接,在SQL99中使用的是NATURAL JOIN
SELECT * FROM player NATURAL JOIN teamNATURAL JOIN team就相当于where p.team_id = t.team_id;
3.ON连接可以在ON连接后面可以使用等值连接或者非等值连接
--等值连接 SELECT * FROM player p JOIN team t ON p.team_id = t.team_id; --非等值连接 SELECT * FROM heros hero JOIN heros_growth hg ON hero.growth BETWEEN hg.lowergrowth AND hg.highgrowth;4.USING连接可以使用两张表中相同字段的值来进行连接,使用USING比直接使用等于号效率更高一点。
SELECT * FROM player JOIN team USING(team_id);5.外连接 SQL99中在SQL92的基础上增加了全外连接,并将92的语法调整得更可读。
SQL99中使用LEFT JOIN 表示左外连接、RIGHT JOIN 表示右外连接,FULL JOIN表示全外连接
--左外连接 SELECT p.player_name,t.team_name FROM player p LEFT JOIN team t USING(team_id) --右外连接 SELECT t.team_name,p.player_name FROM player p RIGHT JOIN team t USING(team_id) --全外连接 --返回两张表匹配的属性 --返回左表未匹配的属性 --返回右表未匹配的属性 SELECT * FROM player p FULL JOIN team t USING(team_id)MySQL不支持FULL JOIN ,Oracle、DB2、SqlServer支持FULL JOIN。 若想要在MySQL中使用全外连接,则需要将左外连接与右外连接通过UNION连接起来
SELECT p.player_name,t.team_name FROM player p LEFT JOIN team t USING(team_id) UNION SELECT t.team_name,p.player_name FROM player p RIGHT JOIN team t USING(team_id)SQL视图 创建视图,将数据表的数据结构进行封装,提供一个对外接口,与较好的安全性。
CREATE VIEW view_name AS SELECT team_name as tname ,player_name as pname FROM player p LEFT JOIN team t USING(team_id);那么就可直接查询view_name视图 视图中只有tname、pname两个字段。 若视图中查询表的数据有改动,查询视图中的数据也会跟着改动。
如何创建存储过程,在存储过程中可能需要的几个步骤 1.创建存储过程,声明存储过程的名称以及参数。
CREATE PROCEDURE procudure_name(IN data_name VARCHAR,OUT result_name VARCHAR) BEGIN ... END参数中存在IN(输入的参数,不能改变)、OUT(输出的参数,可以改变)
2.声明变量 存储过程中若要使用变量就必须声明变量,否则会报错。
... BEGIN DECLARE i int default 0; ... END3.赋值
SET i = 1;4.循环 有三种循环方式:
LOOPREPEAT … UNTIL … END REPEATWHILE … DO … END WHILELOOP 进行无限循环,在循环体内判断跳出循环
label: LOOP statement_list IF exit_condition THEN LEAVE label; --离开循环 END IF; END LOOP label;REPEAT … UNTIL … END REPEAT类似于JAVA的do{…}while(condition)先进入循环,再判断是否跳出循环
REPEAT statement_list UNTIL search_condition END REPEAT;WHILE … DO … END WHILE 类似与JAVA的while(condition){…},先满足条件才能进入循环。
WHILE search_condition DO statement_list END WHILE;5.流程控制
IF … THEN … ELSEIF … THEN … ELSE … END IF 类似于JAVA中简单的IF条件判断 IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list ELSE statement_list END IF; CASE … WHEN … THEN … ELSE … END CASE 类似于JAVA中的switch(){ case … } CASE case_value WHEN when_value THEN statement_list ELSE statement_list END CASE; SELECT … INTO … 查询数据批量赋值变量 可以直接赋值OUT的变量 CREATE DEFINER=`root`@`localhost` PROCEDURE `procudure_name`(OUT result_name VARCHAR(35)) BEGIN select team_name from team where team_id = '1001' INTO result_name; END然后执行命令
call procudure_name(@result_name); select @result_name若使用INOUT或者OUT,传入参数则必须为@result_name,result_name为存储过程参数的名称。
启动事务后的所有基本单元,要么全部执行,要么全部不执行。若是事务后,有语句执行错误,那么前面的语句无论有没有成功,都将数据进行回滚到之前最新的数据,确保数据的一致性,这也是事务其中一种特性。
事务的特性是什么? 事务的特性:ACID ACID就是事务的四个特性。其中,A表示原子性,C表示一致性,I表示隔离性,D表示持久性。
A:Atomicity,原子性就是表的基本单位是不可分割的。 C:Consistency,一致性就是将数据的一致性状态变为另一种一致性状态,也就是数据改变后,表的完整性约束不可被改变。(加锁,是保证事务的一致性的方法。) I:Isolation,隔离性就是让一个事务执行完成之前,其他事务获取不到该事务的信息。 D:Durability,持久性就是在提交事务之前,系统宕机,还能有方式能恢复这期间的数据。用的是写前日志,在语句写入之前,就将语句保存在事务的重写日志中,事务的日志包括(回滚日志、重写日志)。
这四大特性中,隔离性是基础,一致性是约束条件,隔离性是手段,持久性是目的。
如何启动事务?
START TRANSACTION 或者 BEGIN,开启一个显示事务。COMMIT 提交一个事务ROLLBACK 或 ROLLBACK TO [SAVEPOINT],当执行异常后,会回滚当前事务,或回滚到当前事务的一个保存点。SAVEPOINT 在事务执行前,创建一个保存点。RELEASE SAVEPOINT 删除一个保存点SET TRANSACTION 设置事务的隔离级别Oracle中的事务不自动提交,需要自己使用commit来提交事务,而MySQL中时自动提交的,若不需要自动提交就需要对autocommit进行设置,那么就需要begin启动事务,commit提交事务,rollback回滚事务。这就是隐式事务与显示事务。
set autocommit = 0; --查询autocommit值 select @@autocommit;关于事务,MySQL就要说一下它的存储引擎, 在MySQL5.0之前的默认存储引擎是MyISAM,这个是不支持事务的,但是执行速度快。 而在MySQL5.0之后的默认存储引擎使用的是InnoDB,该存储引擎支持事务,行级锁,以及外键。 所以,若是数据库只用来读取的话,那存储引擎就选择MyISAM。
也可以查看MySQL中有哪些存储引擎
show engines;事务的隔离 事务的隔离性是事务的基本特性之一,主要用于并发的背景下, 保证数据的一致性。有些情况需要降低一些正确性从而提高效率,有时候的数据背景可能不允许出错,那就需要将隔离调到最高,从而牺牲执行效率。
事务并发处理可能产生的异常,这些异常与隔离级别在SQL92标准中有得到定义,这些异常就是脏读、不可重复读、幻读。
脏读:一个事务读到了其他事务还没有提交的数据。此时其他事务可能会回滚,那么读到的数据就是脏数据。
不可重复读:一个事务使用一条语句进行查询后,有其他事务对这条语句的数据进行修改,这时这个事务又将该语句重新执行了一遍,这时就会发现查询出来的数据不一致,这就是不可重复读。
幻读:一个事务使用一条语句进行查询得到集合后,有其他事务对该表进行增加或删除,这时该事务又使用同一条语句重新执行了一遍,这时,会发现查询出来的数量不一致。
事务有四大隔离级别,级别从低到高有:
读不提交(READ UNCOMMITTED):隔离级别最低,并发的问题都会存在。读已提交(READ COMMITTED): 能解决脏读,而不可重复读与幻读都还存在。可重复读(REPEATABLE READ):能解决脏读,不可重复读,而幻读还存在。可串行化(SERIALIZABLE):能解决所有的并发问题。隔离级别从低到高,也表示了执行效率也是从低到高。有时就需要降低正确性从而提高执行效率。
有些情况不需要对所有结果集的所有数据行都采用相同的处理方式,这些情况就可以使用面向过程的编程方式,浮标是一种面向过程的编程方式。
什么是游标? 游标是一种临时的数据库对象,可以充当指针的作用,指向的是存储在数据库中的数据行指针。操作游标,就相当于操作数据航。
怎么使用游标? DBMS不同使用游标的方式可能略有不同
1.创建游标 MySQL,SQLServer,DB2,MariaDB中打开游标的方法
CREATE cursor_name CURSOR FOR select_elementlist;Oracle,PostgreSQL中略有不同
CREATE cursor_name CURSOR IS select_elementlist;select_elementlist是查询的结果集
CREATE cursor_name CURSOR FOR select tname from player2.打开游标
OPEN cursor_name;游标打开后,会将查询的结果集发送到游标工作区中。
3.提取数据
FETCH cursor_name INTO varname...;从游标中提取出当前行通过INTO进行赋值,若由表中的列有多个,赋值的参数写多个即可。
4.关闭游标
CLOSE cursor_name;有打开就有关闭,若关闭游标后,就不允许查询到结果集,若想要继续查询,就得打开游标。
5.释放游标
DEALLOCATE cursor_name有DECLARE就需要有DEALLOCATE,要养成释放游标的习惯,否则游标就会一直存储在内存中,知道进程结束后才会释放。
一个比较复杂的例子:模拟英雄联盟中版本升级带动英雄攻击力升级的存储过程。 若英雄的攻击成长(attack_growth)小于5,并且攻击力成长空间小于200(attack_max - attack_start)则攻击成长提升10%。若在150到200之间,则攻击力成长提升8%。若小于150,则攻击力成长提升7%。
若攻击成长大于5小于10,则提升5%。 若大于10,则保持不变。
CREATE DEFINER=`root`@`localhost` PROCEDURE `attack_growth_pro`() BEGIN #定义临时变量 DECLARE temp_id INT; DECLARE temp_growth,temp_diff,temp_max,temp_start FLOAT; #创建游标结束参数 DECLARE DONE INT DEFAULT false; #创建游标 DECLARE growth_cursor CURSOR FOR SELECT id,attack_growth,attack_max,attack_start FROM heros; #若游标结束则将DONE赋值为TRUE DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE; #开启游标,并将查询的集合放到游标处理区中 OPEN growth_cursor; #读取游标的参数并赋值 FETCH growth_cursor into temp_id,temp_growth,temp_max,temp_start; #循环,相当于do{selectment}while(contition) REPEAT IF NOT DONE THEN SET temp_diff = temp_max - temp_start; IF temp_growth < 5 THEN if temp_diff > 200 THEN SET temp_growth = temp_growth + temp_growth*0.1; ELSEIF temp_diff <200 and temp_diff > 150 THEN SET temp_growth = temp_growth + temp_growth*0.08; ELSEIF temp_diff < 150 THEN SET temp_growth = temp_growth + temp_growth*0.07; END IF; ELSEIF temp_growth > 5 and temp_growth<10 THEN SET temp_growth = temp_growth + temp_growth*0.05; END IF; UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id; END IF; -- 将游标指针指向下一行 FETCH growth_cursor into temp_id,temp_growth,temp_max,temp_start; UNTIL DONE = true END REPEAT; END如何求两个集合的差集 可以用NOT IN
select * from tables1 where id not in (select id from tables2 where date between date1 and date2)但是这种方式的效率较低
还有第二种方式,使用 left join
select t1.id from tables1 t1 left join tables2 t2 on t1.id = t2.id where t2.id is null;查询出两个表的并集,这是将表2与表1相等的值置为null,这时查出来的就是不相等的值了。