php开源免费网站模板,永康建设局网站电话,精品课程网站开发平台,深圳建网站的目录 一.子查询——Subquery
语法 环境准备
In——查询已知的值的数据记录
子查询——Insert
子查询——Update
子查询——Delete
Not In——表示否定#xff0c;不在子查询的结果集里
Exists——判断查询结果集是否为空
子查询——别名
编辑
二.视图
理论不在子查询的结果集里
Exists——判断查询结果集是否为空
子查询——别名
编辑
二.视图
理论
视图和表的区别和联系
操作
创建视图单表
编辑 查看视图
查看表状态
编辑 修改原表数据 修改视图数据
总结
三.Null值——缺失
null值与空值的区别
编辑
四.连接查询
编辑 内连接——Inner Join
语法 左连接——Left Join
语法
右连接——Right Join
语法
五.存储过程
1.概述
2.存储过程的优点
3.语法 调用存储过程
查看存储过程
编辑
编辑 查看指定存储过程
删除存储过程
存储过程内容的修改方法是通过删除原有存储过程之后再以相同的名称创建新的存储过程。
参数
六.总结
1.子查询
2.视图——View
3.缺失值——Null
4.连接查询——内 左 右
5.存储过程——Procedure 创建 查看存储过程 调用存储过程
6.参数 一.子查询——Subquery
子查询也被称作为内查询或者嵌套查询是指一个查询语句里面还嵌套着另一个查询语句即SQL语句调用另一个Select子句。子查询语句是先于主查询语句被执行的其结果作为外层的条件返回给主查询进行下一步查询过滤。 子语句可以与主语句所查询的表相同也可以是不同表 子语句中的SQL语句是为了最后过滤出一个结果集用于主语句的判断条件 in是作为主表和另一个表的连接的语法 语法
IN 用来判断某个值是否在给定的结果集中通常结合子查询来使用语法
表达式 [NOT] IN 子查询 当表达式与子查询返回的结果集中的某个值相等时返回 TRUE否则返回 FALSE。 若启用了 NOT 关键字则返回值相反。需要注意的是子查询只能返回一列数据如果需 求比较复杂一列解决不了问题可以使用多层嵌套的方式来应对。 多数情况下子查询都是与 SELECT 语句一起使用的 子查询不仅可以在 SELECT 语句中使用在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候子查询内部还可以再次嵌套新的子查询也就是说可以多层嵌套。 环境准备
mysql create database school;
Query OK, 1 row affected (0.01 sec)mysql use school
Database changed
mysql create table class1(id int, name varchar(16),score char(10),address varchar(20),cardid int);
Query OK, 0 rows affected (0.00 sec)mysql insert into class1 values(1,zhangsan,80,nanjing,1);
Query OK, 1 row affected (0.00 sec)mysql insert into class1 values(2,lisi,80,beijing,1);
Query OK, 1 row affected (0.00 sec)mysql insert into class1 values(3,wanghan,95,chengdu,2);
Query OK, 1 row affected (0.01 sec)mysql insert into class1 values(4,wanghan,95,chengdu,2);
Query OK, 1 row affected (0.01 sec)mysql insert into class1 values(5,tianqi,90,shandong,3);
Query OK, 1 row affected (0.00 sec)mysql insert into class1 values(6,wangba,90,beijing,3);
Query OK, 1 row affected (0.01 sec)mysql select * from class1;
-----------------------------------------
| id | name | score | address | cardid |
-----------------------------------------
| 1 | zhangsan | 80 | nanjing | 1 |
| 2 | lisi | 80 | beijing | 1 |
| 3 | wanghan | 95 | chengdu | 2 |
| 4 | zhaoliu | 88 | shanghai | 2 |
| 5 | tianqi | 90 | shandong | 3 |
| 6 | wangba | 90 | beijing | 3 |
-----------------------------------------
6 rows in set (0.00 sec)mysql create table class2(id int);
Query OK, 0 rows affected (0.00 sec)mysql insert into class2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from class2;
------
| id |
------
| 1 |
| 2 |
| 3 |
------
3 rows in set (0.00 sec)mysql select * from class3- ;
---------------------------------------
| id | name | score | address | cardid |
---------------------------------------
| 6 | wangba | 90 | beijing | 3 |
| 3 | wangwu | 95 | chengdu | 2 |
| 5 | tianqi | 90 | shandong | 3 |
---------------------------------------
3 rows in set (0.00 sec)
In——查询已知的值的数据记录
IN 用来判断某个值是否在给定的结果集中通常结合子查询来使用
表达式 [NOT] IN 子查询
mysql select id,name,score from class where id in(select id from class2);
#先查询class2数据表中的id字段列 将查询到的结果id字段列作为一个已知的值的数据记录再根据已知的值的数据记录查询class1数据表中id,name,score字段列select id,name,score from class1 where id in(select id from class3 where score e 90);#先查询class3数据表判断条件为 score分数大于等于90分的数据作为id列 以此作为查询条件从class1数据表中获取数据 select id,name,score from class1 where name in(select name from class3 where score 90);子查询的判断条件主查询语句必须与子查询语句中的字段一致 当表达式与子查询返回的结果集中的某个值相等时返回 TRUE否则返回 FALSE。 若启用了 NOT 关键字则返回值相反。需要注意的是子查询只能返回一列数据如果需 求比较复杂一列解决不了问题可以使用多层嵌套的方式来应对。 多数情况下子查询都是与 SELECT 语句一
子查询——Insert
子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其 他的表中 将class3变成空表 mysql select * from class3;
---------------------------------------
| id | name | score | address | cardid |
---------------------------------------
| 6 | wangba | 90 | beijing | 3 |
| 3 | wangwu | 95 | chengdu | 2 |
| 5 | tianqi | 90 | shandong | 3 |
---------------------------------------
3 rows in set (0.00 sec)mysql delete from class3;
Query OK, 3 rows affected (0.00 sec)mysql select * from class3;
Empty set (0.00 sec)
mysql insert into class3 select * from class1 where id in(select id from class1);
Query OK, 6 rows affected (0.00 sec)
#先遍历class1数据表中id的字段列 然后根据查询到的字段列插入到class3数据表中
Records: 6 Duplicates: 0 Warnings: 0mysql select * from class3;
-----------------------------------------
| id | name | score | address | cardid |
-----------------------------------------
| 1 | zhangsan | 80 | nanjing | 1 |
| 2 | lisi | 80 | beijing | 1 |
| 3 | wanghan | 95 | chengdu | 2 |
| 4 | zhaoliu | 88 | shanghai | 2 |
| 5 | tianqi | 90 | shandong | 3 |
| 6 | wangba | 90 | beijing | 3 |
-----------------------------------------
6 rows in set (0.00 sec)子查询——Update
UPDATE 语句也可以使用子查询。UPDATE 内的子查询在 set 更新内容时可以是单独的一列也可以是多列。 更新class3数据表中 根据查询关联表class1数据表id1的数据内容 修改id1的字段的分数为85分 mysql select * from class3;
-----------------------------------------
| id | name | score | address | cardid |
-----------------------------------------
| 1 | zhangsan | 80 | nanjing | 1 |
| 2 | lisi | 80 | beijing | 1 |
| 3 | wanghan | 95 | chengdu | 2 |
| 4 | zhaoliu | 88 | shanghai | 2 |
| 5 | tianqi | 90 | shandong | 3 |
| 6 | wangba | 90 | beijing | 3 |
-----------------------------------------
6 rows in set (0.00 sec)mysql update class3 set score85 where id in(select id from class1 where id1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from class3;
-----------------------------------------
| id | name | score | address | cardid |
-----------------------------------------
| 1 | zhangsan | 85 | nanjing | 1 |
| 2 | lisi | 80 | beijing | 1 |
| 3 | wanghan | 95 | chengdu | 2 |
| 4 | zhaoliu | 88 | shanghai | 2 |
| 5 | tianqi | 90 | shandong | 3 |
| 6 | wangba | 90 | beijing | 3 |
-----------------------------------------
6 rows in set (0.00 sec)子查询——Delete 先查询class1数据表中 得分大于90分的 删除class3数据表中大于90分的数据 mysql select * from class3 where score90;
---------------------------------------
| id | name | score | address | cardid |
---------------------------------------
| 3 | wanghan | 95 | chengdu | 2 |
---------------------------------------
1 row in set (0.00 sec)mysql delete from class3 where id in (select id from class1 where score 90);
Query OK, 1 row affected (0.00 sec)mysql select * from class3;
-----------------------------------------
| id | name | score | address | cardid |
-----------------------------------------
| 1 | zhangsan | 85 | nanjing | 1 |
| 2 | lisi | 80 | beijing | 1 |
| 4 | zhaoliu | 88 | shanghai | 2 |
| 5 | tianqi | 90 | shandong | 3 |
| 6 | wangba | 90 | beijing | 3 |
-----------------------------------------
5 rows in set (0.00 sec)Not In——表示否定不在子查询的结果集里
在 IN 前面还可以添加 NOT其作用与IN相反表示否定即不在子查询的结果集里面 删除class数据表条件判断 取反 遍历score分数 小于85分的 也就是删除class3数据表中分数大于85分的所有数据 mysql select * from class3;
-----------------------------------------
| id | name | score | address | cardid |
-----------------------------------------
| 1 | zhangsan | 85 | nanjing | 1 |
| 2 | lisi | 80 | beijing | 1 |
| 4 | zhaoliu | 88 | shanghai | 2 |
| 5 | tianqi | 90 | shandong | 3 |
| 6 | wangba | 90 | beijing | 3 |
-----------------------------------------
5 rows in set (0.00 sec)mysql delete from class3 where id not in(select id where score 85);
Query OK, 4 rows affected (0.00 sec)mysql select * from class3;
------------------------------------
| id | name | score | address | cardid |
------------------------------------
| 2 | lisi | 80 | beijing | 1 |
------------------------------------
1 row in set (0.00 sec)Exists——判断查询结果集是否为空 先查询class数据表中id字段列 判断score分数是否大于80分 exists判断子查询结果是否为空如果为空返回false 子查询结果为空 那么 不执行class3数据表的求和 mysql select sum(score) from class3 where exists(select id from class3 where score 80);
------------
| sum(score) |
------------
| NULL |
------------
1 row in set (0.00 sec)先查询class3数据表中id字段列 判断条件score分数是否大于80 exists判断子查询结果为空 依旧不执行class1数据表的sum求和 mysql select sum(score) from class1 where exists(select id from class3 where score 80);
------------
| sum(score) |
------------
| NULL |
------------
1 row in set (0.00 sec) 先查询class3数据表 判断条件为小于等于80 exists子查询结果为TRUE 为真 那么执行前面的class1查询语句 查询所有字段并且Sum求和 mysql select sum(score) from class1 where exists(select id from class3 where score 80);
------------
| sum(score) |
------------
| 523 |
------------
1 row in set (0.00 sec)子查询——别名 报错的原因是子查询语句中select查询的表名是一个完整的结果集主查询语句不能识别子查询的结果不能识别子查询语句输出的整个数据表的数据内容 给class1做一个s的别名 select * from 表名 此为标准格式而以上的查询语句表名的位置其实是一个完整结果集mysql并不能直接识别而此时给与结果集设置一个别名 s 以”select s.id from s“的方式查询将此结果集视为一张表就可以正常查询数据了. 二.视图
理论
视图优化操作安全方案 数据库中的虚拟表这张虚拟表中不包含真实数据只是做了真实数据的映射
作用场景[图]: 针对不同的人权限身份提供不同结果集的“表”以表格的形式展示作用范围
select * from class1; #展示的部分是class1表select * from view_name; #展示的一张或多张表
功能 简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性 本质而言视图是一种 select (结果集的呈现)
注意
视图适合于多表连接浏览时使用不适合增、删、改 而存储过程适合于使用较频繁的SQL语句这样可以提高执行效率!
视图和表的区别和联系
区别
视图是已经编译好的sql语句。而表不是视图没有实际的物理记录。而表有表只用物理空间而视图不占用物理空间视图只是逻辑概念的存在表可以及时对它进行修改但视图只能有创建的语句来修改视图是查看数据表的一种方法可以查询数据表中某些字段构成的数据只是一些SQL语句的集合。从安全的角度说视图可以不给用户接触数据表从而不知道表结构表属于全局模式中的表是实表视图属于局部模式的表是虚表视图的建立和删除只影响视图本身不影响对应的基本表。但是更新视图数据是会影响到基本表的
联系
视图(view)是在基本表之上建立的表它的结构(即所定义的列)和内容(即所有数据行)都来自基本表它依据基本表存在而存在。一个视图可以对应一个基本表也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
操作
创建视图单表
create view v_score as select * from class1;#创建视图 视图表v_score 基于class1数据表而存在 查看视图
mysql select * from v_score;查看表状态
show table status\G 创建的视图看不到任何的状态信息 查看视图与源表结构
mysql desc v_score;mysql desc class1; 修改原表数据 更改原表数据 可以看到视图跟着原表的数据改动一起改动了 修改视图数据 可以看出修改视图数据表的数据也会改变 总结
修改表不能修改以函数、符合函数方式计算出来的字段视图的存在是为了查询更加方便为了增强安全性
三.Null值——缺失
在SQL语句使用过程中通常使用NULL表示缺失的值也就是在表中该字段是没有值的。如果在创建表时指定某字段不能为空Not Null不指定则默认可以为空。在向表中插入记录或者更新记录的时候如果该字段没有Not Null值且没有任何值这时新记录的字段数据为Null。 需要注意 的是NULL 值与数字 0 或者空白spaces的字段是不同的值为 NULL 的字段是没有 值的。在 SQL 语句中使用 IS NULL 可以判断表内的某个字段是不是 NULL 值相反的用 IS NOT NULL 可以判断不是 NULL 值。 null值与空值的区别
空值长度为0不占空间NULL值的长度为null占用空间is null 无法判断空值空值使用 “ 或者 ” 来处理count计算时NULL会忽略空值会加入计算 添加新的字段 count计数统计数量的时候 null值不会计数总数 四.连接查询
Mysql的连接查询通常都是将来自两个或多个表的记录行结合起来基于这些表之间的共同字段进行数据的拼接。首先要确定一个主表作为结果集然后将其他表的行有选择性的连接到该主表。 内连接——Inner Join
MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表并使用 ON 子句设置连接条件内连接是系统默认的表连接所以在 FROM 子句后可以省略 INNER 关键字只使用 关键字 JOIN。同时有多个表时也可以连续使用 INNER JOIN 来实现多表的内连接不过为了更好的性能建议最好不要超过三个表 语法
#语法
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name table2.column_name; 左连接——Left Join
左连接也可以被称为左外连接在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表接收左表的所有行并用这些行与右侧参 考表中的记录进行匹配也就是说匹配左表中的所有行以及右表中符合条件的行。 语法
mysql select a.a_id,a.a_name from test1 a left join test2 b on a.a_nameb.b_name; 左连接中左表的记录将会全部表示出来而右表只会显示符合搜索条件的记录右表记录不足的地方均为 NULL。 右连接——Right Join
右连接也被称为右外连接在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反它是以右表为基础表用于接收右表中的所有行并用这些记录与左表中的行进行匹配 语法
mysql select a.a_id,a.a_name from test1 a right join test2 b on a.a_nameb.b_name;在右连接的查询结果集中除了符合匹配规则的行外还包括右表中有但是左表中不匹 配的行这些记录在左表中以 NULL 补足 五.存储过程
1.概述
存储过程是一组为了完成特定功能的SQL语句集合。 两个点 第一 触发器定时任务 第二个判断 存储过程这个功能是从5.0版本才开始支持的它可以加快数据库的处理速度增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译然后再去执行跟存储过程一对比明显存储过程在执行上速度更快效率更高
存储过程在数据库中L 创建并保存它不仅仅是 SQ语句的集合还可以加入一些特殊的控制结构也可以控制数据的访问方式。存储过程的应用范围很广例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。
2.存储过程的优点
执行一次后会将生成的二进制代码驻留缓冲区提高执行效率SQL语句加上控制语句的集合灵活性高在服务器端存储客户端调用时降低网络负载可多次重复被调用可随时修改不影响客户端调用可完成所有的数据库操作也可控制数据库的信息访问权限
3.语法
CREATE PROCEDURE 过程名 ( [过程参数[,…] ] ) 过程体
[过程参数[,…] ] 格式
过程名尽量避免与内置的函数或字段重名
过程体语句
[ IN | OUT | INOUT ] 参数名类型
mysql delimiter $$
#将语句的结束符号从分号;临时改为两个$$(可以自定义)
mysql create procedure kgc()
#创建存储过程过程名为kgc不带参数- begin
#过程体以关键字 BEGIN 开始- create table class4(id int,name varchar(8),score decimal(5,2));- insert into class4 values(1,zhangsan,80);- insert into class4 values(2,lisi,70);- select * from class4;
#过程体语句- END $$
#过程体以关键字 END 结束
Query OK, 0 rows affected (0.12 sec)
mysql DELIMITER ;
#将语句的结束符号恢复为分号
mysql delimiter $$
mysql create procedure kgc()- begin- create table class4(id int,name varchar(8),score decimal(5,2)); - insert into class4 values(1,zhangsan,80); - insert into class4 values(2,lisi,70);- select * from class4; - end $$
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;调用存储过程
call Procedure_name(); 查看存储过程
show create procedure Procedure_name 查看指定存储过程 删除存储过程
存储过程内容的修改方法是通过删除原有存储过程之后再以相同的名称创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc; 参数
IN 输入参数表示调用者向过程传入值传入值可以是字面量或变量OUT 输出参数表示过程向调用者传出值(可以返回多个值)传出值只能是变量INOUT 输入输出参数既表示调用者向过程传入值又表示过程向调用者传出值值只能是变量 表示调用者向过程传入值又表示过程向调用者传出值只能是变量 定义方式与作用域不同。形参是在函数定义中声明的用于在函数体内进行使用但它们只在函数执行期间存在函数调用结束后形参的作用域结束。实参则是在主调函数中定义的用于传递值给形参在函数调用期间实参的值被传递给形参。内存占用不同。形参本质上是一个名字它不占用内存空间直到函数被调用时形参才被分配内存空间。实参则是一个已经占用内存空间的变量它在程序运行期间一直存在。传递机制不同。在传值调用中只有实参的值被使用而在引用调用中实参的地址被传递给形参这意味着对形参的任何修改都会影响到实参本身。 六.总结
1.子查询
In查询已知的值数据记录Not In表示否定不存在子查询结果集里相当于取反Exists判断查询结果集是否为空空返回False非空返回TRUE
2.视图——View
无论修改视图还是原表数据都会随之一起更改
3.缺失值——Null
Null占用空间Null值空值占用空间为0
4.连接查询——内 左 右
内连接Inner Join左连接Left Join右连接Right Join
5.存储过程——Procedure 创建
mysql delimiter $$
#将语句的结束符号从分号;临时改为两个$$(可以自定义)
mysql create procedure kgc()
#创建存储过程过程名为kgc不带参数- begin
#过程体以关键字 BEGIN 开始- create table class4(id int,name varchar(8),score decimal(5,2));- insert into class4 values(1,zhangsan,80);- insert into class4 values(2,lisi,70);- select * from class4;
#过程体语句- END $$
#过程体以关键字 END 结束
Query OK, 0 rows affected (0.12 sec)
mysql DELIMITER ;
#将语句的结束符号恢复为分号 查看存储过程
show create procedure Procedure_name 调用存储过程
call Procedure_name();
6.参数
IN 输入参数表示调用者向过程传入值传入值可以是字面量或变量程向调用者传出值(可以返回多个值)传出值只能是变量INOUT 输入输出参数既表示调用者向过程传入值又表示过程向调用者传出值值只能是变量
