长春做网站价格,iis怎么创建网站,网站空间数据库,wordpress更新php版本一、常用查询#xff08;增、删、改、查#xff09; 对 MySQL 数据库的查询#xff0c;除了基本的查询外#xff0c;有时候需要对查询的结果集进行处理。 例如只取 10 条数据、对查询结果进行排序或分组等等
模板表#xff1a;
数据库有一张info表#xff0c;记录了学生…一、常用查询增、删、改、查 对 MySQL 数据库的查询除了基本的查询外有时候需要对查询的结果集进行处理。 例如只取 10 条数据、对查询结果进行排序或分组等等
模板表
数据库有一张info表记录了学生的id姓名分数地址和爱好
create table info (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));insert into info values(1,liuyi,80,beijing,2);
insert into info values(2,wangwu,90,shengzheng,2);
insert into info values(3,lisi,60,shanghai,4);
insert into info values(4,tianqi,99,hangzhou,5);
insert into info values(5,jiaoshou,98,laowo,3);
insert into info values(6,hanmeimei,10,nanjing,3);
insert into info values(7,lilei,11,nanjing,5);select * from info;1.按关键字排序
1语法
#按分数排序默认不指定是升序排列
select * from info order by score;
#分数按降序排列
select * from info order by score desc;ASC|DESC
asc按升序排列是默认的排序方式加上desc会按降序排列order by还可以结合where进行条件过滤
例筛选地址是杭州的学生按分数降序排列
select name,score from info where addresshangzhou order by score desc;多字段排序
当排序的第一个字段有相同的记录时这些记录再按照第二个字段进行排序字段之间使用英文逗号隔开优先级是按先后顺序而定
select * from 表名 order by 字段1 desc,字段2 desc;
① 查询学生信息先按兴趣降序排列再按id也降序排列
select id,name,hobbid from info order by hobbid desc,id desc;
2.区间判断查询不重复记录
and/or 且/或
distinct 查询不重复记录
例
select * from info where score 70 and score 90;
select * from info where score 70 or score 90;嵌套/多条件
select * from info where score 70 or (score 75 and score 90);查询不重复记录-distinct
select distinct 字段 from 表名﹔
select distinct hobbid from info;
3.对结果进行分组
查询sql结果对结果进行分组(group by)来实现
group by通常结合聚合函数一起使用
常用聚合函数count-计数sum-求和avg-平均数max-最大值min-最小值按hobbid相同的分组计算各组的学生个数基于name个数进行计数
select count(name),hobbid from info group by hobbid;4.限制结果条目
limit 限制输出结果记录
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
LIMIT 的第一个参数是位置偏移量可选参数是设置 MySQL 从哪一行开始显示。
如果不设定第一个参数将会从表中的第一条记录开始显示。
第一条记录的位置偏移量是0第二条是1以此类推。
第二个参数是设置返回记录行的最大数目。例
1、limit 2,2 #表示从第三行(偏移量2)开始显示后2个
select * from info limit 2,2;
2、结合order by语句按id的大小升序排列显示前三行
select id,name from info order by id limit 3;
3、输出最后三行⭐⭐
select id,name from info order by id desc limit 3;
5.设置别名alias——as
给字段列或表设置别名可以方便书写或者多次使用相同的表。
使用的时候直接使用别名简洁明了增强可读性语法
对于列的别名SELECT column_name AS alias_name FROM table_name;
对于表的别名SELECT column_name(s) FROM table_name AS alias_name;select name as 姓名,score as 成绩,assress as 地址 from ky30;列别名设置示例
select name as 姓名,score as 成绩 from info;
表别名
#临时设置ky30的别名为ias 为默认可以不写
select i.name as 姓名,i.score as 成绩,i.assress as 地址 from info as i;
#查询info表的字段数量以number显示
select count(*) as number from info;使用场景
1、对复杂的表进行查询的时候别名可以缩短查询语句的长度
2、多表相连查询的时候通俗易懂、减短sql语句此外AS 还可以作为连接语句的操作符。
创建t1表将info表的查询记录全部插入t1表create table t1 as select * from info;
#此处AS起到的作用
1、创建了一个新表t1 并定义表结构插入表数据与info表相同
2、但是“约束”没有被完全“复制”过来 #如果原表设置了主键那么附表的default字段会默认设置一个0
相似
克隆、复制表结构
也可以省略as加上括号
create table t1 (select * from info);
#也可以加入where 语句判断
create table test1 as select * from info where score 60;#别名不能与数据库中的其他表的名称冲突。
#列的别名是在结果中有显示的而表的别名在结果中没有显示只在执行查询时使用。统计数据量
#显示表中有多少条数据
select count(*) from info;
#创建表t1并赋予class表的属性和数据但是t1没有主键可以用来备份表
create table t1 as select * from class;
create table t2 (select * from class);
#通过条件判断写入
create table t1 as select * from class where score 70;
6.通配符
用于替换字符串中的部分字符通过部分字符的匹配将相关结果查询出来。
通配符通常与 LIKE 一起使用的并协同 WHERE 子句共同来完成查询任务
% 表示匹配0到多个字符
_ 下划线表示匹配单个字符查询name列中以c开头的字符
select id,name from info where name like c%查询name列中以c开头、第三个字符为i、并且以o结尾的字符
name like c_i_o查询name中以u开头结尾为5的数据
select id,name from info where name like u%_5;查询名字中间有g的记录
select id,name from info where name like %g%;
7.子查询
子查询内查询、嵌套查询
是指一个查询语句中还嵌套着另一个查询语句子查询(括号里的)优先于主查询
可以在相同表和不同表之间进行查询in 用来关联主表和子表主查询和子查询⭐⭐语法in用来判断某个值是否在给定的 结果集 中通常结合子查询使用示例
select name,score from info where id in (select id from info where score 80);
主语句select name,score from info where id
子语句(集合) select id from info where score 80
PS:子语句中的sql语句是为了最后过滤出一个结果集用于主语句的判断条件
多表查询
#一次性添加多条数据
insert into class values (),(),();先查括号里的再查括号外的in用来关联表
select name,score from info where id in (select id from info where score 80);多表查询
select id,name,score from info where id in (select id from class);多层嵌套
语法
IN 用来判断某个值是否在给定的结果集中通常结合子查询来使用语法
表达式 [NOT] IN 子查询
当表达式与子查询返回的结果集中的某个值相等时返回 TRUE否则返回 FALSE。
not 取反
需要注意的是子查询只能返回一列数据如果需求比较复杂一列解决不了问题可以使用多层嵌套#查询分数大于80的记录
select name,score from info where id in (select id from info where score80);
#将t1里的记录全部删除重新插入info表的记录
insert into t1 select * from info where id in (select id from info);
#将caicai的分数改为50
update info set score50 where id in (select * from ky30 where id2);
#删除分数大于80的记录
delete from info where id in (select id where score80);
#not in 表示取反
update t1 set id2 where id not in (select id from info where id2);#删除
delete from t1 where id in (select id from info where age 15);子查询支持select、insert、update、deleteexists 判断查询结果是否为空否、则返回true是、则返回falseEXISTS
用于判断子查询的结果集是否为空如果不为空则返回 TRUE反之则返回 FALSE0例
#查询如果存在分数等于80的记录则计算info的字段数
select count(*) from info where exists(select id from info where score80);
#查询如果存在分数小于50的记录则计算info的字段数
select count(*) from info where exists(select id from info where score50);
别名as
#查询info表id,name 字段
select id,name from info;例
需求从info表中的id和name字段的内容做为内容 输出id的部分
mysql select id from (select id,name from info);
ERROR 1248 (42000): Every derived table must have its own alias
#此时会报错因为“表名”的位置是一个结果集mysql无法识别
#将这个结果集设置一个别名后就能正常查询了
select a.id from (select id,name from info) a;
相当于
select info.id,name from info;
MySQL视图
视图优化操作安全方案 ⭐⭐
是一个虚拟表不包含真实数据只是做了真实数据的映射
视图可以理解为倒影/镜花水月动态的保存结果集数据作用
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
PS:视图适合于多表连接浏览时使用不适合增、删、改
而存储过程适合于使用较频繁的SQL语句可以提高执行效率!作用场景
针对不同的人权限提供不同的结果集
创建视图表
#创建视图(单表)
create view 视图名 as select * from 表名 where age 15;
create view v_score as select * from info where score80;#查看表状态
show table status\G#查看视图
select * from v_score;#查看视图与源表结构
desc v_score;
desc info;多表创建视图
创建test01表
create table test01 (id int,name varchar(10),age char(10));
insert into test01 values(1,zhangsan,20);
insert into test01 values(2,lisi,30);
insert into test01 values(3,wangwu,29);需求需要创建一个视图需要输出id、学生姓名、分数以及年龄
create view v_info(id,name,score,age) as select info.id,info.name,info.score,test01.age from info,test01 where info.nametest01.name;
select * from v_info;#修改原表数据
update info set score60 where nameliuyi;#查看视图
select * from v_score;#同时可以通过视图修改原表
update v_score set score120 where nametianqi;select * from v_score;
select * from info; 修改表不能修改以函数、复合函数方式计算出来的字段
特点查询方便、安全性
查询方便索引速度快、同时可以多表查询更为迅速视图不保存真实数据视图本质类似select
安全性我们实现登陆的账户是root拥有权限 视图无法显示完整的约束
连接查询⭐⭐⭐ MySQL 的连接查询通常都是将来自两个或多个表的记录行结合起来基于这些表之间的共同字段进行数据的拼接。 首先要确定一个主表作为结果集然后将其他表的行有选择性的连接到选定的主表结果集上。常用连接查询包括内连接、左连接和右连接
inner join(内连接)只返回两个表中联结字段相等的行
left join(左连接)返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接)返回包括右表中的所有记录和左表中联结字段相等的记录
模版
create table test1 (a_id int(11) default null,a_name varchar(32) default null,a_level int(11) default null);
insert into test1 values (1,aaaa,10);
insert into test1 values (2,bbbb,20);
insert into test1 values (3,cccc,30);
insert into test1 values (4,dddd,40);create table test2 (b_id int(11) default null,b_name varchar(32) default null,b_level int(11) default null);
insert into test2 values (2,bbbb,20);
insert into test2 values (3,cccc,30);
insert into test2 values (5,eeee,50);
insert into test2 values (6,ffff,60);
1.内连接 MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表并使用 ON 子句设置连接条件内连接是系统默认的表连接所以在 FROM 子句后可以省略 INNER 关键字只使用 关键字 JOIN。同时有多个表时也可以连续使用 INNER JOIN 来实现多表的内连接不过为了更好的性能建议最好不要超过三个表
语法
#连接两个表中字段记录相等的数据记录方法一
SELECT * FROM 表名1 别名1 INNER JOIN 表名2 别名2 ON 别名1.列名 别名2.列名;
select * from test1 A inner join test2 B on A.nameB.name;SELECT * FROM 表名1 INNER JOIN 表名2 ON 表名1.列名 表名2.列名;
select * from test1 inner join test2 on test1.nametest2.name;方法二
select * from test1 A, test2 B where A.nameB.name;2.左连接 左连接也可以被称为左外连接在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表接收左表的所有行并用这些行与右侧参考表中的记录进行匹配也就是说匹配左表中的所有行以及右表中符合条件的行。
select * from test1 left join test2 on test1.nametest2.name;左连接中左表的记录将会全部表示出来而右表只会显示符合搜索条件的记录右表记录不足的地方均为 NULL。
3.右连接 右连接也被称为右外连接在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反它是以右表为基础表用于接收右表中的所有行并用这些记录与左表中的行进行匹配
select * from test1 right join test2 on test1.nametest2.name;在右连接的查询结果集中除了符合匹配规则的行外还包括右表中有但是左表中不匹 配的行这些记录在左表中以 NULL 补足
存储过程⭐⭐⭐ 1.存储过程是一组为了完成特定功能的SQL语句集合 两个点 第一 触发器定时任务 第二个判断 2.存储过程可以加快数据库的处理速度增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时只需要调用它即可。 操作数据库的传统 SQL 语句在执行时需要先编译然后再去执行所以相比来说存储过程在执行上速度更快效率更高。
存储过程的优点
1执行一次后会将生成的二进制代码驻留缓冲区提高执行效率 2SQL语句加上控制语句的集合灵活性高 3在服务器端存储客户端调用时降低网络负载 4可多次重复被调用可随时修改不影响客户端调用 5可完成所有的数据库操作也可控制数据库的信息访问权限
语法
CREATE PROCEDURE 过程名 ( [过程参数[,…] ] ) 过程体[过程参数[,…] ] 格式
过程名尽量避免与内置的函数或字段重名
过程体语句
[ IN | OUT | INOUT ] 参数名类型
示例
创建存储过程
#将语句的结束符号从分号;临时改为两个$$(可以自定义)
DELIMITER $$
#创建存储过程过程名为Proc不带参数
CREATE PROCEDURE Proc()
#过程体以关键字 BEGIN 开始
- BEGIN
#过程体语句
- create table mk (id int (10), name char(10),score int (10));
- insert into mk values (1, wang,13);
- select * from mk;
#过程体以关键字 END 结束
- END $$
#将语句的结束符号恢复为分号
DELIMITER ;例
mysql delimiter $$
mysql create procedure proc()- begin- create table test (id int(10),name char(10),score int(10));- insert into test values(1,zhangsan,13);- select * from test;- end $$
Query OK, 0 rows affected (0.02 sec)mysql delimiter ;调用存储过程
CALL Proc();
例
call proc();
1.存储过程的主体都分,被称为过程体
2.以BEGIN开始以END结束若只有一条SQL语句则可以省略BEGIN-END
3.以DELIMITER开始和结束
mysglDEL工M工TER $$ $$是用户自定义的结束符
省略存储过程其他步骤
mysqlDELIMITER ; 分号前有空格
查看存储过程
#查看某个存储过程的具体信息
SHOW CREATE PROCEDURE 数据库.存储过程名;
show create procedure proc\G#查看存储过程
SHOW PROCEDURE STATUS ;
show procedure status;#查看指定存储过程信息
SHOW PROCEDURE STATUS like %proc%\G;
show procedure status like %proc%\G;
存储过程的参数
IN 输入参数表示调用者向过程传入值传入值可以是字面量或变量 OUT 输出参数表示过程向调用者传出值(可以返回多个值)传出值只能是变量 INOUT 输入输出参数既表示调用者向过程传入值又表示过程向调用者传出值值只能是变量 即表示调用者向过程传入值又表示过程向调用者传出值只能是变量)
示例
mysql delimiter
mysql create procedure proc (in inname varchar(40)) #行参- begin- select * from info where nameinname;- end
mysql delimiter
mysql call proc2(wangwu); #实参
------------------------
| name | score | address |
------------------------
| wangwu | 80.00 | beijing |
------------------------
1 row in set (0.00 sec)
修改存储过程
ALTER PROCEDURE 过程名[特征... ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;MODIFIES SQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时只要执行者有执行权限就可以成功执行。
删除存储过程
存储过程内容的修改方法是通过删除原有存储过程之后再以相同的名称创建新的存储过程。DROP PROCEDURE IF EXISTS Proc;
drop procedure if exists proc
