太原建设局网站,建网站需要什么知识,wordpress常用主题修改,浙江省住房城乡建设厅官网一. 视图
视图#xff1a;一种虚拟存在的表#xff0c;行和列的数据来自定义视图的查询中使用的表#xff0c;并且是在使用视图时动态生成的#xff0c;只保存了sql逻辑#xff0c;不保存查询结果
视图语法
-- 创建
create view 视图名
as
查询语句;-- 使用
select * f…一. 视图
视图一种虚拟存在的表行和列的数据来自定义视图的查询中使用的表并且是在使用视图时动态生成的只保存了sql逻辑不保存查询结果
视图语法
-- 创建
create view 视图名
as
查询语句;-- 使用
select * from 视图名;-- 修改
create or replace view 视图名
as
查询语句;alter view 视图名
as 查询语句;应用场景
多个地方用到同样的查询结果该查询结果使用的sql语句较复杂
视图的好处 重用sql语句简化复杂的sql操作不必知道它的查询细节保护数据提高安全性 创建
create view 视图名 as 查询语句 如果在创建视图遇到了问题可能是以下几种情况之一 表不存在 请确保真实的数据表确实存在于你的数据库中。 列不存在 确保表中有列。可以通过执行 DESCRIBE 表名; 或者 SHOW COLUMNS FROM 表名; 来查看表的列信息。 权限问题 确保你有足够的权限来执行 CREATE VIEW 操作。 查看
show create view 视图名 使用
select * from 视图名 修改
方式 1如果视图存在则进行修改
create or replace view 视图名 as 查询语句 方式 2直接对已存在的视图进行修改
alter view 视图名 as 查询语句 删除
drop view 视图名 更新内容
insert into 视图名 values(值...)update 视图名 set 列名值delete from 视图名 在对视图更新的同时对原表也进行了更新。一般对视图中不进行更新而且许多类型的视图不支持更新创建视图的sql语句中包含分组函数、distinct、group by、having、union。常量视图。创建视图的sql语句中select中包含子查询。创建视图的sql语句中用到了join或其他连接可以修改不能插入删除。创建视图的sql语句中from了一个不能更新的视图。创建视图的sql语句中where子句的子查询引用了from子句中的表。视图与表创建语句不同。视图没有实际占用数据存储的物理空间。而表实际占有。视图一般不进行增删改只进行查询。而表都可以进行。 二. 索引
索引概述
MySQL官方对索引的定义为索引Index是帮助MySQL高效获取数据的数据结构。
数据本身之外数据库还维护着一个满足特定查找算法的数据结构这些数据结构以某种方式指向数据这样就可以在这些数据结构的基础上实现高级查找算法这种数据结构B树就是索引。总之就是排序查找功能。
为了加快查找可以维护一个右边所示的二叉查找树每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针这样就可以运用二叉查找在一定的复杂度内获取到相应数据从而快速的检索出符合条件的记录。 索引的目的在于提高查询效率可以类比字典。
如果要查“mysql”这个单词我们肯定需要定位到m字母然后从下往下找到y字母再找到剩下的sql。
如果没有索引就需要遍历所有表项。
一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。
平常所说的索引如果没有特别指明都是指B树多路搜索树并不一定是二叉的结构组织的索引。其中聚集索引次要索引复合索引前缀索引唯一索引默认都是使用B树索引统称索引。
索引
提高数据检索的效率降低数据库的IO成本。
通过索引列对数据进行排序降低数据排序的成本降低了CPU的消耗。
劣势
实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录所以索引列也是要占用空间的。
虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件每次更新添加了索引列的字段都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素如果有大数据量的表就需要花时间研究建立最优秀的索引。
索引的分类 Primary Key聚集索引InnoDB存储引擎的表会存在主键唯一非null如果建表的时候没有指定主键则会使用第一非空的唯一索引作为聚集索引否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。 单列索引单列索引即一个索引只包含单个列 组合索引组合索引指在表的多个字段组合上创建的索引只有在查询条件中使用了这些字段的左边字段时索引才会被使用。使用组合索引时遵循最左前缀集合 Unique唯一索引索引列的值必须唯一但允许有空值。若是组合索引则列值的组合必须唯一。主键索引是一种特殊的唯一索引不允许有空值 Key普通索引是MySQL中的基本索引类型允许在定义索引的列中插入重复值和空值 FULLTEXT全文索引全文索引类型为FULLTEXT在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建 SPATIAL空间索引空间索引是对空间数据类型的字段建立的索引MySQL中的空间数据类型有4种分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL 一般而言一张表中的索引不超过5个。
索引的操作
创建 create index 索引名 on 表名(索引列)。
alter 表名 add index [索引名] on (索引列)。 可以将index替换为primary、unique、fulltext分别表示主键索引、唯一索引和全文索引。
删除
drop index [索引名] on 表名 查看
show index from 表名
索引的结构B树索引。 那些情况需要创建索引 主键自动建立唯一索引。 频繁作为查询条件的字段应该创建索引。 查询中与其它表关联的字段外键关系建立索引。 频繁更新的字段不适合创建索引。 Where条件里用不到的字段不创建索引。 在高并发下倾向创建组合索引。 查询中排序的字段排序字段若通过索引去访问将大大提高排序速度。 查询中统计或者分组字段。 那些情况不适合创建索引 表记录太少。 经常增删改的表。 数据重复差异率低并且分布均匀。 索引的选择性索引列不同的值个数 / 索引列记录数越接近1效率越高。 性能分析 MySQL Query Optimizer优化select语句的执行效率。 MySQL常见瓶颈 CPUCPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。 IO磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。 服务器硬件的性能瓶颈topfreeiostat和vmstat来查看系统的性能状态。 三 .MySQL面试题
3.1 SQL脚本
/*Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 80018Source Host : localhost:3306Source Schema : mybatis_ssmTarget Server Type : MySQLTarget Server Version : 80018File Encoding : 65001Date: 04/07/2023 23:53:33
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS 0;-- ----------------------------
-- Table structure for t_mysql_course
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_course;
CREATE TABLE t_mysql_course (cid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程编号,cname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程名称,tid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师编号,PRIMARY KEY (cid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 课程信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_course
-- ----------------------------
INSERT INTO t_mysql_course VALUES (01, 语文, 02);
INSERT INTO t_mysql_course VALUES (02, 数学, 01);
INSERT INTO t_mysql_course VALUES (03, 英语, 03);-- ----------------------------
-- Table structure for t_mysql_score
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_score;
CREATE TABLE t_mysql_score (sid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生编号外键,cid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程编号外键,score float NULL DEFAULT 0 COMMENT 成绩,INDEX sid(sid) USING BTREE,INDEX cid(cid) USING BTREE,CONSTRAINT t_mysql_score_ibfk_1 FOREIGN KEY (sid) REFERENCES t_mysql_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT t_mysql_score_ibfk_2 FOREIGN KEY (cid) REFERENCES t_mysql_course (cid) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 成绩信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_score
-- ----------------------------
INSERT INTO t_mysql_score VALUES (01, 01, 80);
INSERT INTO t_mysql_score VALUES (01, 02, 90);
INSERT INTO t_mysql_score VALUES (01, 03, 99);
INSERT INTO t_mysql_score VALUES (02, 01, 70);
INSERT INTO t_mysql_score VALUES (02, 02, 60);
INSERT INTO t_mysql_score VALUES (02, 03, 80);
INSERT INTO t_mysql_score VALUES (03, 01, 80);
INSERT INTO t_mysql_score VALUES (03, 02, 80);
INSERT INTO t_mysql_score VALUES (03, 03, 80);
INSERT INTO t_mysql_score VALUES (04, 01, 50);
INSERT INTO t_mysql_score VALUES (04, 02, 30);
INSERT INTO t_mysql_score VALUES (04, 03, 20);
INSERT INTO t_mysql_score VALUES (05, 01, 76);
INSERT INTO t_mysql_score VALUES (05, 02, 87);
INSERT INTO t_mysql_score VALUES (06, 01, 31);
INSERT INTO t_mysql_score VALUES (06, 03, 34);
INSERT INTO t_mysql_score VALUES (07, 02, 89);
INSERT INTO t_mysql_score VALUES (07, 03, 98);-- ----------------------------
-- Table structure for t_mysql_student
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_student;
CREATE TABLE t_mysql_student (sid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生编号,sname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生名称,sage varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生年龄,ssex varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生性别,PRIMARY KEY (sid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 学生信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_student
-- ----------------------------
INSERT INTO t_mysql_student VALUES (01, 赵雷, 1990-01-01, 男);
INSERT INTO t_mysql_student VALUES (02, 钱电, 1990-12-21, 男);
INSERT INTO t_mysql_student VALUES (03, 孙风, 1990-12-20, 男);
INSERT INTO t_mysql_student VALUES (04, 李云, 1990-12-06, 男);
INSERT INTO t_mysql_student VALUES (05, 周梅, 1991-12-01, 女);
INSERT INTO t_mysql_student VALUES (06, 吴兰, 1992-01-01, 女);
INSERT INTO t_mysql_student VALUES (07, 郑竹, 1989-01-01, 女);
INSERT INTO t_mysql_student VALUES (09, 张三, 2017-12-20, 女);
INSERT INTO t_mysql_student VALUES (10, 李四, 2017-12-25, 女);
INSERT INTO t_mysql_student VALUES (11, 李四, 2012-06-06, 女);
INSERT INTO t_mysql_student VALUES (12, 赵六, 2013-06-13, 女);
INSERT INTO t_mysql_student VALUES (13, 孙七, 2014-06-01, 女);-- ----------------------------
-- Table structure for t_mysql_teacher
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_teacher;
CREATE TABLE t_mysql_teacher (tid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师编号,tname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师名称,PRIMARY KEY (tid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 教师信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_teacher
-- ----------------------------
INSERT INTO t_mysql_teacher VALUES (01, 张三);
INSERT INTO t_mysql_teacher VALUES (02, 李四);
INSERT INTO t_mysql_teacher VALUES (03, 王五);SET FOREIGN_KEY_CHECKS 1; 3.2 面试题实战 01查询 01 课程比 02 课程成绩高的学生的信息及课程分数
SELECTs.*,( CASE WHEN t1.cid 01 THEN t1.score END ) 01课程,( CASE WHEN t2.cid 02 THEN t2.score END ) 02课程
FROMt_mysql_student s,( SELECT * FROM t_mysql_score WHERE cid 01 ) t1,( SELECT * FROM t_mysql_score WHERE cid 02 ) t2
WHEREs.sid t1.sid AND t1.sid t2.sid AND t1.score t2.score 02查询同时存在 01 课程和 02 课程的情况
SELECTs.*,( CASE WHEN t1.cid 01 THEN t1.score END ) 01课程,( CASE WHEN t2.cid 02 THEN t2.score END ) 02课程
FROMt_mysql_student s,( SELECT * FROM t_mysql_score WHERE cid 01 ) t1,( SELECT * FROM t_mysql_score WHERE cid 02 ) t2
WHEREs.sid t1.sid AND t1.sid t2.sid 03查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null )
SELECT * from
( SELECT * FROM t_mysql_score WHERE cid 01 ) t1
LEFT JOIN
( SELECT * FROM t_mysql_score WHERE cid 02 ) t2
on t1.sidt2.sid 04查询不存在 01 课程但存在 02 课程的情况 SELECT*
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid AND s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid 01 ) AND sc.cid 02 05查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECTs.sid,s.sname,ROUND( AVG( sc.score ) ) 分数
FROMt_mysql_student sLEFT JOIN t_mysql_score sc ON s.sid sc.sid
GROUP BYs.sid,s.sname
HAVING分数 60 06查询在t_mysql_score表存在成绩的学生信息
SELECTs.sid,s.sname
FROMt_mysql_student sINNER JOIN t_mysql_score sc ON s.sid sc.sid
GROUP BYs.sid,s.sname 07查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECTs.sid,s.sname,COUNT( sc.cid ),SUM( sc.score )
FROMt_mysql_score sc,t_mysql_student s
WHEREsc.sid s.sid
GROUP BYs.sid,s.sname 08查询「李」姓老师的数量
select count(*) from t_mysql_teacher where tname like 李% 思维导图:
