MYSQL数据库
第三部分：数据库查询操作
一.普通查询
SELECT 待显示列字段 FROM 表名 [WHERE 条件];
①显示全部列全部行
②显示全部行，部分列
③显示全部行，筛选满足某一条件的列
④显示部分列，部分行
注意:
运算符问题：逻辑运算符and or not(!)
比较运算符= <> != > >= < <=
二.常量查询
SELECT 常量 FROM 表名;
三.别名查询（重命名查询）AS
SELECT u.userid AS '编号',username AS '姓名' FROM userinfo u;
四.过滤查询 DISTINCT
SELECT DISTINCT useraddress FROM userinfo;
五.排序查询ORDER BY ,DESC(降序), ASC(升序)，默认是升序
SELECT * FROM userinfo ORDER BY userage DESC;
1.排序和筛选
先where后order by
2.多重排序问题
//(在相同的userage时，userid按降序排列)
SELECT * FROM userinfo ORDER BY userage,userid DESC;
3.表中有主键，主键自带排序，升序
六.为空查询
SELECT * FROM userinfo WHERE useraddress=''; //地址没有写，但存在
SELECT * FROM userinfo WHERE useraddress IS NULL;//地址是null
SELECT * FROM userinfo WHERE useraddress IS NOT NULL;
七.模糊查询 LIKE %
%:表示任意0个或多个字符，可匹配任意类型和长度的字符
_:表示任意单个字符
[]:表示括号内所列字符中的一个
[^]:表示不在括号所列之内的单个字符
SELECT * FROM userinfo WHERE username LIKE '张%';
SELECT * FROM userinfo WHERE username LIKE '%三';
SELECT * FROM userinfo WHERE username LIKE '%三%';
SELECT * FROM userinfo WHERE username NOT LIKE '%三%';
八.拼接查询 拼的是行 UNION(去掉重复的行) UNION ALL(不去掉重复的行)
注意：多行的列个数相同
九.分页查询 LIMIT?(从第几个开始),?(每页显示多少条)
page:当前页数
size:每页显示多少条
SELECT * FROM userinfo LIMIT (page-1)*size,size;
1. LIMIT与WHERE之间的关系：先where后limit
2. LIMIT与ORDER BY之间的关系：先order by后 limit
3.分页超出范围怎么办？ 有几条写几条 超出索引范围就什么也不显示
十.函数查询
1.数值函数
(1)ABS(x):返回x的绝对值
(2)BIN(x):返回x的二进制（OCT返回八进制，HEX返回十六进制）
(3)FLOOR(x):返回小于x的最大整数值
(4)CEILING(x):返回大于x的最小整数值
(5)ROUND(x,y):返回参数x的四舍五入的有y位小数的值
(6)RAND(x):返回0到1的随机数，可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
(7)MOD(x/y):返回x/y的模（余数）
2.字符函数
(1)BIN_LENGTH:字节容量
(2)CHAR_LENGTH:字符个数
(3)LENGTH:字节个数
(4)LEFT(’’, x):从左边去，取x个,RIGHT(’’,x ):从右边取，取x个
(5)ASCII
(6)TRIM(’ a b ‘):去掉前后空格,RTRIM(’ a b ‘)：去掉右空格,LTRIM(’ A B '):去掉左空格
(7))CONCAT:拼接 +：求和 CONCAT_WS:以某一个字符分割字符串
(8)LOWER:转化为小写 UPPER:转化为大写
(9)REPLACE (‘sdsfdvAvdvdENMFJ’,‘a’,8):将字符串中的a字符用8替换 INSERT(从1开始，)
(10)POSITION和INSTR：查询某一个字符在字符串中的位置 (不存在返回的是0)
(11)REVERSE：反转
(12)FIND_IN_SET:查询
3.日期函数
(1）获取当前时间
SELECT SYSDATE();
SELECT NOW();
SELECT CURRENT_DATE(),CURRENT_TIME();
(2)
SELECT DATE_ADD(SYSDATE(),INTERVAL 3 YEAR);//三年之后
SELECT DATE_ADD(SYSDATE(),INTERVAL 3 MONTH);
SELECT DATE_ADD(SYSDATE(),INTERVAL 3 DAY);//三天之后
SELECT DATE_ADD(SYSDATE(),INTERVAL -3 DAY);//三天之前
SELECT DATE_ADD(SYSDATE(),INTERVAL 3 HOUR);
SELECT DATE_ADD(SYSDATE(),INTERVAL 3 MINUTE);
SELECT DATE_ADD(SYSDATE(),INTERVAL 3 SECOND);
(3)
SELECT YEAR(SYSDATE()),MONTH(SYSDATE()),DAYOFMONTH(SYSDATE()),HOUR(SYSDATE()),MINUTE(SYSDATE()),SECOND(SYSDATE()),DAYOFWEEK(SYSDATE());
4.类型转换函数
类型有：binary char date time datetime signed unsigned
SELECT CAST('029'AS SIGNED INTEGER);
5.聚合函数(分组函数)COUNT:忽略空行 MAX MIN
SELECT MAX(userage),MIN(userage),SUM(userage),AVG(userage),COUNT(userid) FROM userinfo;
注意：
a.count忽略NULL
b.sum和avg只能应用于数值类型
c.count()等同于count(主键)，不推荐使用count()
d.聚合函数只能同分组字段一同使用
十一.分组查询 GROUP BY HAVING
先group by 后order by
1.按照地址分组，显示地址和总年龄数，并且按照总年龄数升序排列显示
SELECT useraddress,SUM(userage) FROM userinfo GROUP BY useraddress ORDER BY SUM(userage);
SELECT useraddress,SUM(userage) AS allage FROM userinfo GROUP BY useraddress ORDER BY allage;
2.按照地址分组，显示地址和分组后的每组的总数的哪些学生（每个个城市有多少个男生）
SELECT useraddress,COUNT(userid) FROM userinfo WHERE usersex='男'GROUP BY useraddress;
3.统计有哪些城市有四个以上的男生
SELECT useraddress,COUNT(userid) FROM userinfo WHERE usersex='男' GROUP BY useraddress HAVING COUNT(userid)>4;
where分组前筛选，group by分组，having分组后筛选
十二.范围查询 IN NOT IN
十三.加密查询 md5 账号=’’AND 密码=MD5(‘’)
有数据代表登录成功，没数据代表失败
SELECT MD5('aaaa'),MD5('123456');
SELECT * FROM userinfo WHERE username='张三' AND userpass=MD5('3333');
SELECT * FROM userinfo WHERE username='张三' AND userpass=MD5('123456');
第四部分：数据库的其它操作
一.连接查询
1.内连接
SELECT 待显示字段 FROM 表A INNER JOIN 表B ON 连接条件 [INNER JOIN 表C ON 连接条件……] [WHERE 筛选条件];
2.左外连接
SELECT 待显示字段 FROM 表A LEFT [OUTER] JOIN 表B ON 连接条件 [LEFT JOIN 表C ON 连接条件……] [WHERE 筛选条件];
3.右外连接
SELECT 待显示字段 FROM 表A RIGHT [OUTER] JOIN 表B ON 连接条件 [RIGHT JOIN 表C ON 连接条件……] [WHERE 筛选条件];
4.全外连接
往往我们多张表之间拼接，主要靠的是外键进行的，也就是说外键和主键之间的关系
一张表的外键一定是另一张表的主键
内连接与左连接、右连接的区别：
内连接两张表之间的关系是平等关系
左连接是以左表为主，右表为辅，如果左表的数据在右表中没有对应的，用null填充
右连接是以右表为主，左表为辅，如果右表的数据在左表中没有对应的，用null填充
二.子查询：将一张表的结果当成是另一张表的条件，这就叫子查询，外面的查询叫父查询
注意：
1.子查询的结果有且只有一列
2.如果父查询的条件使用关系运算符（>,>=,<,<=…），那么子查询的结果有且只能有一行
3.如果父查询的条件使用范围运算符（in,not in），那么子查询的结果可以有多行
4.子查询的结果类型和父查询的条件类型要一致
5.子查询不光可以使用在select中，insert，update和delete中也可以使用子查询
三.事务：一般指要做的或所做的事情，在计算机中是一段程序的执行单元。事务一般有事务开始和事务结束之间执行的全体操作组成。
1.特性（ACID）：
原子性（Atomicity）：要么全部成功，要么全部失败
一致性(Consistency)：数据库的完整性没有被破坏
隔离性(Isolation)：可防止多个事务并发执行时由于交叉执行而导致数据的不一致
持久性(Durability)：事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失
2.事务的关键字：
Begin 开始事务
Commit 提交事务
Rollback 回滚事务
SavePoint事务点：设置事务点
Rollback事务点：回滚到事务点
Set AutoCommit=0/1：设置自动提交事务（0：禁止自动提交|1：开始自动提交）
3.MYSQL中的事务注意事项：
（1）在mysql中只有使用了Innodb数据库引擎的数据库或表才支持事务
（2）事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么完全执行，要么全部不执行
（3）事务用来管理insert,update,delete语句
（4）MYSQL中的事务默认为自动提交
主键：也叫主关键字，一张表只有一个主键，主键可以由一个或多个列组成，它的作用是‘标识唯一’，主键列不能为null
外键：也叫外关键字，它的作用是连接关系，连接两张表之间的关系,一张表可以有多个外键
四.视图
1.概念：
视图是对若干张基本表的引用，一个虚拟存在的表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）
可以跟基表一样，进行增删改查操作（更新操作只有简单视图才可以）
2.作用：
①方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性，关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作
②更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别
3.分类：
①简单视图：单张表组成的视图
②复杂视图：多张表组成的视图
4.语法：
CREATE [OR REPLACE] [ALGORITHM]={UNDEFINED|MEGRE|TEMPTABLE}]
[DEFINER='root'@'localhost']
VIEW 视图名[(属性清单)]
AS SELECT 语句
[WITH[CASCADED|LOCAL]CHECK OPTION];
OR REPLACE:如果给定了此子句，表示该语句能够替换已有视图
ALGORIGHM:可选参数，表示视图选择的算法
UNDEFINED，表示MySQL将自动选择，所有使用的算法
MERGE，表示将使用视图的语句，与实图定义合并起来，使得试图定义的某一部分，取代语句的对应部分
TEMPTABLE，表示将视图的结果存入临时表，然后使用临时表执行语句
DEFINER='root'@'localhost'：指定视图创建者
WITH[CASCADED|LOCAL]CHECK OPTION：可选参数，表示创建视图时，要保证在该视图的权限范围之内
CASCADED可选参数，表示创建视图时，需要满足跟该视图有关的，所有相关视图和表的条件，该参数为默认值
LOCAL：可选参数，表示创建视图时，只要满足该视图本身定义的条件即可
DROP VIEW 视图名；
5.注意
①基表的数据发生修改，视图的数据也会自然而然修改
②简单视图也可以进行数据修改，修改的内容其实是基表的数据
③简单视图也可以进行插入操作，但是未引用的列必须满足允许为null
④简单视图也可以进行删除操作，删除条件由引用的列来完成
五.存储过程
1.概念：存储过程是一种在数据中存储的复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。
2.优缺点
优点：
①存储过程可封装，并隐藏复杂的商业逻辑
②存储过程可以回传值，并可以接收参数
③存储过程无法使用SELECT指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同
④存储过程可以用在数据校验，强制实行商业逻辑等
缺点：
①存储过程，往往定制于特定的数据库上，因为支持的编程语言不同。当切换到其它厂商的数据库系统时，需要重写原有的存储过程
②存储过程的性能调校与撰写，受限于各种数据库系统
3.语法
CREATE PROCEDURE 过程名 ([[IN|OUT|INOUT] 参数名 数据类型[[IN|OUT|INOUT]参数名 数据类型...]])
[characteristic]
过程体
注意：
存储过程一般事先用“DELIMITER //”声明当前段分隔符，让编译器把两个“//”之间的内容当做存储过程的代码，不会执行这些代码：“DELIMITER;”的意为把分隔符还原。
4.存储过程中的变量定义
DECLARE variable name[,variable name...] datatype [DEFAULT value];
5.赋值操作
set
select....into...
6.存储过程与函数之间的不同
①存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用。由于函数可以返回一个对象，因此它可以在查询语句中位于FROM关键字的后面；
②存储过程实现的功能较复杂，而函数实现的功能针对性较强
③函数需要用括号包住输入的参数，且只能返回一个值或表对象，而存储过程可以返回多个参数
④函数可以嵌入在SQL中使用，可以在select中调用，存储过程则不行
⑤函数不能直接操作实体表，只能操作内建表
⑥存储过程在创建时即在服务器上进行了编译，其执行速度比函数快
六、触发器
1.触发器是一种特殊类型的存储过程，它由事付触发，而不是稍索调用另手工启动。使用触发器用来保证数据的有效性和完整性。
2.触发器与存储过程的区别
| 触发器 | 存储过程 |
|---|---|
| 当某类数据操纵DML语句发生隐式地调用 | 从一个应用或过程中显式地调用 |
| 在触发器体内禁止使用COMMIT和ROLLACK语句 | 在过程体内可以使用所有PL/SQL块中都能使用的SQL语句，包括COMMLT和ROLLBACK语句) |
| 不能接受参数输入 | 可以接受参数输入 |
3.触发器可分为DML融发器和DDL触发器.
DML触发器是当数据库服务器发生数据操作语言事件时执行的存储过程。
| Instead Of触发器是在记录变更之前，去执行触发器本身所定义的操作。 |
| After触发器被激活触发是在记录改变之后进行的一种触发器 |
DDL触发器是在响应数据定义语言事件时执行的存储过程.
4.触发器的主要作用：
①增加安全性
②利用触发器记录所进行的修改以及相关信息，跟踪用户对数据库的操作，实现审计
③维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束以及对数据库中特定事件进行监控与响应。
④实现复杂的非标准的数据库相关完整性规则，同步实时地复制表中的数据
⑤触发器是自动的
5.语法
CREATE OR REPLACE TRIGGER update_weekends_check
BEFORE UPDATE OF sal ON EMP
FOR EACH ROW
EDCLARE
my_count number(4);
BEGIN
SELECT COUNT(u_name)
FROM WEEKEND_UPDATE_OK INTO my_count
WHERE u_name=user_name;
IF my_count=0 THEN
RAISE_APPLICATION_ERROR(20508,'Update not allowed');
END IF;
END;
七、游标
1.游标简介
游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
游标的作用就是用于对查调数据库所返回的记录进行遍历，以便进行相应的操作
2.游标的特性
①不敏感(Asensitive)：数据库可以选择不复制结果集
②只读(Read Only)
③不滚动(Nonscrollable)：游标只能向一个方向前进，并且不可以跳过任何一行数据.
3.游标的优点
①在使用游标的表中，对行提供删除和更新的功能
②游标将面向集合的数据库管理系统和面前行的程序设计连接了起来
4.游标的缺点
①性能不高
②只能一行一行操作
5.游标的操作
//游标的定义
DECLARE cursor_name CURSOR FOR select_statement;
//打开游标
OPEN cursor_name;
//取游标中的数据
FETCH cursor_name INTO var_name [,var_name]...
//关闭游标
CLOSE cursor_name;
//释放游标
DEALLOCATE cursor_name;
数据库的复习就到此了，没有更深入地理解，只是简单地整理了一下数据库的常见操作，后面会继续更进一步学习数据库的知识。
因为是自己整理的，不对的地方还希望各位大佬改正！^ ^
256
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
