MySQL总结笔记
一、安装和配置数据库:
下载mysql地址:https://dev.mysql.com/downloads/mysql/
windows下载的版本是installer msi版本:https://dev.mysql.com/downloads/windows/installer/5.7.html
环境变量的配置:
书上80页。
路径:C:\Program Files\mysql\MySQL Server 5.7\bin
环境变量配置地址:电脑——》属性——》系统属性——》高级——》环境变量——》Path
启动命令行:windows键+R键,输入CMD
启动数据库:net start mysql57
关闭数据库:net stop mysql57
二、连接数据库的方式:
1、连接本地数据库:
mysql -h localhost -u root -p 回车后输入密码
如果是连接远程的数据库的话,将localhost 换为数据库服务器的ip地址。
2、navicat连接数据库:
左上角的连接——》选择Mysql——》输入连接名(任意)——》输入数据库的密码——》测试连接——》保存——》双击连接名。
三、关于数据库的操作
查看当前所有的数据库:show databases;
mysql、information_schema、perfermance_schema、sys这四个是系统自带的数据库,不用动它。
创建数据库:create database 数据库名;
e.g create database mydb;
打开数据库:use 数据库名;
e.g use mydb;
删除数据库:drop database 数据库名;
e.g drop database mydb;
查看单个数据库信息: show create database 数据库名;
e.g show create database mydb;
查看系统支持的存储引擎类型:
SHOW ENGINES;
四、关于表的操作
1、创建表
约束条件与数据类型的宽度一样,都是可选参数。
作用:用于保证数据的完整性和一致性。
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
create table 表名 (
字段1 数据类型(长度) 完整性约束,
字段2 数据类型(长度) 完整性约束
);
e.g create table t1 (
id int(4) NOT NULL,
name varchar(14) default 'allen'
);
2、查看表
1)查看表结构
describe 表名; || desc 表名;
e.g desc t1;
2)查看表详细结构
show create table 表名\G;
\G:帮助整理结果的格式
e.g show create table course\G;
3)查看所有的表
show tables;
3、修改表结构
1)修改表名
-- 语法:ALTER TABLE 表名 RENAME 新表名;
ALTER TABLE sc RENAME score;
2)增加字段
-- 语法:ALTER TABLE 表名 ADD 新字段名 数据类型 [完整性约束条件];
-- 增加一个字段
ALTER TABLE student ADD motor varchar(30) DEFAULT 'cb300r';
-- 增加多个字段
ALTER TABLE teacher ADD motor varchar(30) DEFAULT 'cb500r',
ADD car varchar(30) DEFAULT 'BMW7';
-- 增加字段到第一项
ALTER TABLE score ADD semester varchar(30) DEFAULT '2021' FIRST;
-- 增加新字段到指定字段之后
ALTER TABLE student ADD salary int DEFAULT 1000 AFTER ssex;
3)删除字段
-- 删除字段:ALTER TABLE 表名 DROP 字段名;
ALTER TABLE student DROP salary;
4)修改字段类型和名称
-- 修改字段类型
-- 语法:ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE student MODIFY salary MEDIUMINT DEFAULT 3000;
-- 修改字段名称
-- 语法: ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE student CHANGE salary living_cost MEDIUMINT DEFAULT 3000;
-- 修改字段类型和名称
-- 语法:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
ALTER TABLE student CHANGE living_cost capital INT NOT NULL DEFAULT 300000;
4、删除表
-- 语法:DROP TABLE 表名;
DROP TABLE ts;
五、表记录相关操作
1、添加表记录
insert into 表名(列名) values (常量清单);
-- 单条数据
insert into student(sno, sname) values
('2005010104', '张三');
-- 多条数据
insert into student(sno, sname) values
('2001010101', '李四'),
('2002020202', '王五');
-- 不使用可选列名:
insert into t1 values
(1,'egon','male',18),
(2,'alex','female',81);
2、删除表记录
-- 语法:DELETE FROM 表名 WHERE 条件;
DELETE FROM student
WHERE sno='2005030301';
3、修改表记录
-- 语法:UPDATE 表名 SET 关系式 (WHERE 条件);
-- 改单个记录
UPDATE student SET ssex='女'
WHERE sname='张丽';
-- 改多个记录
UPDATE sc SET degree=0
WHERE sno IN (
SELECT sno
FROM student
WHERE sdept='计算机工程系'
);
-- 改单个记录多项
UPDATE student SET ssex='女', capital=300000
WHERE sname='张立';
六、关于表的查询的操作:
select 要查询的字段:1、*,2、name,sex,age
DISTINCT 去重
SELECT DISTINCT sno FROM sc;
from 要查询的表名
where 要查询结果的筛选条件
-
1、比较运算符:=、>、<、>=、<=、 两个不等于符号:<>、!= (不大于!>和不小于!< 在mysql中执行失败)
```
SELECT sname, ssex, YEAR(CURRENT_DATE)-YEAR(sbirthday) as age
FROM student
WHERE YEAR(CURRENT_DATE)-YEAR(sbirthday) > 33;SELECT sname, ssex, YEAR(CURRENT_DATE)-YEAR(sbirthday) as age FROM student WHERE YEAR(CURRENT_DATE)-YEAR(sbirthday) != 33; ```
-
2、范围运算符 between and 、not between and
```
SELECT sname, ssex, YEAR(CURRENT_DATE)-YEAR(sbirthday) as age
FROM student
WHERE YEAR(CURRENT_DATE)-YEAR(sbirthday) BETWEEN 30 AND 33;SELECT sname, ssex, YEAR(CURRENT_DATE)-YEAR(sbirthday) as age FROM student WHERE YEAR(CURRENT_DATE)-YEAR(sbirthday) NOT BETWEEN 30 AND 33; ```
-
3、列表运算符 in、 not in
SELECT sname, ssex, YEAR(CURRENT_DATE)-YEAR(sbirthday) as age FROM student WHERE YEAR(CURRENT_DATE)-YEAR(sbirthday) IN (30,32,33);
-
4、字符匹配符 LIKE、NOT LIKE
SELECT * FROM student WHERE sdept LIKE '%程%';
-
5、空值判断 IS NULL、IS NOT NULL
SELECT * FROM student WHERE sdept IS NULL;
-
6、逻辑运算符 AND、OR、NOT
```
SELECT *
FROM student
WHERE ssex='女' AND sdept LIKE '%数学%';SELECT * FROM student WHERE ssex='女' OR ssex='男'; ```
group by 指定查询结果的分组条件
SELECT * FROM student WHERE ssex='女' OR ssex='男' GROUP BY sdept;
having 分组或集合的查询条件 (在group by后执行)
SELECT * FROM student WHERE ssex='女' OR ssex='男' GROUP BY sdept HAVING ssex='女';
ORDER BY 指定查询结果排序 DESC:逆向排序
LIMIT 指定结果输出范围
```
SELECT *
FROM student
WHERE ssex='女'
ORDER BY sbirthday DESC
LIMIT 1;
SELECT *
FROM student
WHERE ssex='女'
ORDER BY sbirthday
LIMIT 1;
```
1、通配符:
1、%:任意多个字符
1)以什么开头的模糊查询
SELECT *
FROM student
WHERE sdept LIKE '信%';
2)以什么结尾的模糊查询
SELECT *
FROM student
WHERE saddress LIKE '%州';
3)包含有什么字段的模板查询
SELECT *
FROM student
WHERE sdept LIKE '%程%';
2、_:单个字符
SELECT *
FROM student
WHERE sname LIKE '王_';
SELECT *
FROM student
WHERE sname LIKE '王%';
2、正则表达式:
1、^以什么开头
SELECT *
FROM student
WHERE sname REGEXP '^王';
2、$以什么结尾
SELECT *
FROM student
WHERE speciality REGEXP '务$';
3、.匹配任意单字符(不支持中文,仅支持数字和字母字符串)
SELECT *
FROM student
WHERE sno REGEXP '2.050301';
4、*匹配任意个前面的字符串(不生效)
SELECT *
FROM student
WHERE sno REGEXP '2*50301';
SELECT *
FROM student
WHERE sno REGEXP '2*5*';
5、+匹配前面的字符1次或多次
SELECT *
FROM student
WHERE sbirthday REGEXP '198+-0';
SELECT *
FROM student
WHERE sno REGEXP '20+303';
6、匹配指定字符串文本
SELECT *
FROM student
WHERE sdept REGEXP '机工';
7、[]匹配字符串集合中任意一个字符(中文无法过滤,仅支持数字和字母字符串)
SELECT *
FROM sc
WHERE cno REGEXP '[13]';
8、[^]匹配不在括号内的任何字符(没有发生作用)
9、字符串{n}匹配前面的字符串至少n次(不支持中文,仅支持数字和字母字符串)
SELECT *
FROM student
WHERE sno REGEXP '21{3}';
10、字符串{m,n}匹配前面的字符串至少m次,至多n次
SELECT *
FROM student
WHERE sno REGEXP '21{4,7}';
SELECT *
FROM student
WHERE sno REGEXP '21{5,7}';
七、多表连接查询
注意:当对多个表进行查询时,要在 SELECT 语句后面指定字段是来源于哪一张表。
因此,在多表查询时,SELECT 语句后面的写法是表名.列名。
另外,如果表名非常长的话,也可以给表设置别名,这样就可以直接在 SELECT 语句后面写上表的别名.列名。
1、交叉连接
交叉连接(CROSS JOIN)一般用来返回连接表的笛卡尔积。
两个表的笛卡尔积,返回结果数量就是两个表的数据行相乘。
(1)语法格式:
方法一:
SELECT <字段名>
FROM <表1> CROSS JOIN <表2>;
方法二:
SELECT <字段名>
FROM <表1>, <表2>;
(2)示例:
SELECT * FROM student CROSS JOIN sc;
SELECT * FROM student, sc;
2、内连接
内连接(INNER JOIN)主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。
即取两张表共同部分,相当于上面利用条件从笛卡尔积结果中筛选出正确结果。
- INNER JOIN 关键字连接两张表
- ON 子句来设置连接条件
(1)语法格式:
方法一:
SELECT <字段名>
FROM <表1> INNER JOIN <表2>
ON <连接条件表达式>;
方法二:
SELECT <字段名>
FROM <表1>, <表2>
where <连接条件>;
(2)示例:
SELECT * FROM student INNER JOIN sc ON student.sno=sc.sno;
SELECT * FROM student, sc WHERE student.sno=sc.sno;
(3)多表连接查询
多个表内连接时,在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
SELECT * FROM student INNER JOIN sc ON student.sno=sc.sno
INNER JOIN course ON sc.cno=course.cno;
3、自连接
一张表内可以进行自身连接操作——同一个表的不同行连接起来。
- 必须为表指定两个别名(逻辑上成两个表)
(1)语法格式:
SELECT <字段名>
FROM <表1> [别名1], <表1> [别名2]
WHERE <连接条件表达式>;
(2)示例:
-- 查询同时选修C01和C04课程的学生学号
SELECT A.sno
FROM sc A, sc B
WHERE A.sno=B.sno AND A.cno='C01' AND B.cno='C04';
-- 查询与 王智刚 同在一个系的学生的学号、姓名、系
SELECT stu2.sno, stu2.sname, stu2.sdept
FROM student stu1, student stu2
WHERE stu1.sdept=stu2.sdept AND stu1.sname='王智刚' AND stu2.sname!='王智刚';
4、外连接
外连接分为三种:左外连接,右外连接,全外连接。对应SQL:LEFT/RIGHT/FULL OUTER JOIN。
通常省略 outer 这个关键字。写成:
LEFT JOIN:保留左边表中的非匹配记录。
RIGHT JOIN:保留右边表中的非匹配记录。
FULL JOIN:保留两边表的所有行。
(1)语法格式:
SELECT <字段名>
FROM <表1> LEFT/RIGHT/FULL JOIN <表2>
ON <连接条件表达式>;
(2)示例:
select * from student LEFT JOIN sc ON student.sno=sc.sno;
select * from sc RIGHT JOIN course ON course.cno=sc.cno;
(3)数据库不支持FULL JOIN
mysql不支持FULL JOIN,方法是使用 UNION ALL 模拟全连接。
SELECT * FROM sc
LEFT JOIN course ON course.cno=sc.cno
UNION ALL
SELECT * FROM sc
RIGHT JOIN student ON student.sno=sc.sno;
八、嵌套查询(子查询)
查询块:一个SELECT-FROM-WHERE语句称为一个查询块。
嵌套查询(子查询):将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中。简单来说,一个查询语句嵌套在另一个查询语句内部的查询。
子查询中的SELECT语句用一对括号“( )”定界,查询结果必须确定。
SELECT语句中不能使用ORDER BY子句,ORDER BY子句永远只能对最终查询结果排序。
求解方法:由里向外处理的,即每个子查询在其上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。
- 常用关键字:IN 、NOT IN 、ANY 、ALL、EXISTS 和 NOT EXISTS 等。
- 比较运算符:>, >=, <, <=, != 等
- 子查询一般分为两种:嵌套子查询和相关子查询。
1、嵌套子查询
嵌套子查询(不相关子查询):嵌套子查询的执行不依赖于外部嵌套。
- 执行顺序:先执行子查询,子查询的结果集传给外部查询作为条件使用,再执行外部查询,显示查询结果。
- 子查询可以多层嵌套。
(1)子查询返回单个值
子查询返回的单个值,被外部查询的比较操作使用。
-- 平均年龄
SELECT AVG(YEAR(CURDATE()) - YEAR(sbirthday))
FROM student;
-- 结果:25
-- 查询所有年龄大于25的学生姓名
SELECT *
FROM student
WHERE YEAR(CURDATE()) - YEAR(sbirthday) > 25;
-- 查询所有年龄大于平均年龄的学生姓名
SELECT *
FROM student
WHERE YEAR(CURDATE()) - YEAR(sbirthday) > (SELECT AVG(YEAR(CURDATE()) - YEAR(sbirthday)) FROM student);
(2)子查询返回一个值列表(用IN操作符实现查询)
IN表示属于,判断外部查询某个属性是否在子查询结果中。
-- 找出‘C01’这门课有成绩的学生
SELECT sname
FROM student
WHERE sno IN(
SELECT sno FROM sc WHERE cno='C01');
(3)子查询返回一个值列表(用ANY或ALL操作符实现查询)
- ANY和SOME关键字是同义词。
- ANY和ALL操作符都必须与比较运算符一起使用。
- 常用的比较运算符:>,<,>=,<=,=,!=,<>
# 查询其他系中比数学系某一学生年龄大的学生姓名和年龄 ANY
select YEAR(CURDATE()) - YEAR(sbirthday)
FROM student
where sdept='数学系';
select sname, YEAR(CURDATE()) - YEAR(sbirthday) as age
from student
where YEAR(CURDATE()) - YEAR(sbirthday) > ANY(
select YEAR(CURDATE()) - YEAR(sbirthday)
FROM student
where sdept='数学系'
) AND sdept!='数学系';
# 查询其他系中比数学系全部学生年龄大的学生姓名和年龄
select sname, YEAR(CURDATE()) - YEAR(sbirthday) as age
from student
where YEAR(CURDATE()) - YEAR(sbirthday) > ALL(
select YEAR(CURDATE()) - YEAR(sbirthday)
FROM student
where sdept='数学系'
) AND sdept!='数学系';
2、相关子查询
相关子查询:子查询的执行依赖于外部查询,即子查询的查询条件依赖于外部查询的某个属性值。
执行过程:
- 1)子查询为外部查询的每一个元组(行)执行一次,外部查询将子查询引用列的值传给子查询。
- 2)如果子查询的任何行与其匹配,外部查询则取此行放入结果表。
- 3)再回到 1),直到处理完外部表的每一行。
经常要用到 EXISTS 操作符,代表存在量词。
-- 查询所有选修C01课程的学生姓名
select sname from student
where EXISTS (
select * from sc
where sno=student.sno AND cno='C01'
);
-- 查询选修了全部课程的学生姓名。
SELECT sname FROM student
WHERE NOT EXISTS
(SELECT * FROM course
WHERE NOT EXISTS
(SELECT * FROM sc
WHERE sno=student.sno AND cno=course.cno));
九、集合查询
SELECT的查询结果是元组的集合,所以可以对SELECT的结果进行集合操作。
但是MySQL语言只支持UNION(并操作)运算,对于INTERSECT(交操作)和EXCEPT(差操作)没有实现。
-- 查询计算机工程系的学生及年龄不大于19岁的学生。
select * from student
where sdept='计算机工程系'
UNION
select * from student
where year(curdate())-year(sbirthday)<=19;
十、索引
索引是一种有效组合数据的方式。通过索引,可以快速快速查询到数据库表对象中的特定记录,是一种提供性能的常用方式。
1、关于索引
使用索引可以提高从表中检索数据的速度,索引由表中的一个字段和多个字段生成的键组成。
索引按存储类型可分为:B型树索引(BTREE)和哈希索引。
MySQL支持6种索引,分别为普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。
过多的索引会占据大量的磁盘空间。
以下情况适合创建索引:
- 经常被查询的字段,即在WHERE子句中经常出现的字段
- 在分组的字段,即在GROUP BY子句中出现的字段
- 存在依赖关系的子表和父表之间的联合查询,即主键或外键字段
- 设置唯一完整性约束的字段
2、创建表时创建普通索引
普通索引:在创建索引时,不附加任何限制条件(唯一,非空等限制),该类型的索引可以创建在任何数据类型的字段上。
语法形式:
create table 表名(
属性名 数据类型,
……
index|key [索引名](属性名1 [长度] [ASC|DESC])
)
示例:
create table t_dept(
deptno int,
dname varchar(20),
location varchar(40),
index index_deptno(deptno)
);
3、在已经存在的表上创建普通索引
语法形式:
create index 索引名 on 表名 (属性名 [长度] [ASC|DESC]);
示例:
create index index_dname on t_dept(dname);
4、通过SQL中语句alter table创建普通索引
语法形式:
alter table 表名 add index|key 索引名(属性名 [长度] [ASC|DESC]);
示例:
ALTER table t_dept add index index_deptno(deptno);
5、创建表时创建唯一索引
唯一索引:在创建索引时,限制索引的值必须是唯一的。
在MySQL中,根据索引的创建方式,分为手动索引和自动索引两种。
- 自动索引,是指在数据库表里设置完整性约束时,该表会被系统自动创建索引。
- 手动索引,是指手动在表上创建索引。当设置表的某个字段为主键或唯一完整性约束时,系统就会自动创建关联该字段的唯一索引。
语法形式:
create table 表名(
属性名 数据类型,
……
unique index|key [索引名](属性名1 [长度] [ASC|DESC])
);
示例:
create table t_dept1(
deptno int,
dname varchar(20),
location varchar(40),
unique index index_deptno(deptno)
);
6、在已经存在的表上创建唯一索引
语法形式:
create unique index 索引名 on 表名 (属性名 [长度] [ASC|DESC]);
示例:
create unique index index_dname on t_dept1(dname);
7、通过alter table 创建唯一索引
语法形式:
alter table table_name add unique index|key 索引名(属性名 [长度] [ASC|DESC]);
示例:
alter table t_dept1 add unique index index_deptno on t_dept1(deptno);
8、删除索引
删除索引即删除表中已存在的索引。之所以要删除索引,是由于这些索引会降低更新速度,影响数据库的性能。
语法形式:
drop index 索引名 on 表名;
示例:
drop index index_deptno on t_dept;
drop index index_dname on t_dept;
9、索引改名
对于MySQL 5.7及以上版本,可以执行以下命令:
-- 语法:
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name;
-- 示例:
ALTER TABLE workinfo RENAME INDEX index_t TO index_taddress;
对于MySQL 5.7以前的版本,可以执行下面两个命令:
-- 语法:
ALTER TABLE tbl_name DROP INDEX old_index_name;
ALTER TABLE tbl_name ADD INDEX new_index_name(column_name);
-- 示例:
drop index index_t on workinfo;
create index index_taddress on workinfo(type,address);
十一、视图
视图是一种数据库对象,是从一个或多个基表(或视图)导出的虚表。可以被看成是虚拟表或存储查询。
- 视图的结构和数据是对数据表进行查询的结果。
- 创建视图通过定义 SELECT 语句检索将在视图中显示的数据。
- 视图的基表是SELECT 语句引用的数据表称。
- 视图被定义后便存储在数据库中,通过视图看到的数据只是存放在基表中的数据。
数据修改:当对通过视图看到的数据进行修改时,相应的基表的数据也会发生变化;同时,若基表的数据发生变化,这种变化也会自动地反映到视图中。
视图产生:视图可以是一个数据表的一部分,也可以是多个基表的联合;视图也可以由一个或多个其他视图产生。(视图可以从表的数据查询产生,也可以从其他视图查询数据产生)
1、视图常用操作:
(1)筛选表中的行。
(2)防止未经许可的用户访问敏感数据。
(3)将多个物理数据表抽象为一个逻辑数据表。
注意:视图上的操作和基表类似,但是 DBMS对视图的更新操作(INSERT、DELETE、UPDATE)往往存在一定的限制。
2、视图优点
(1)视图能够简化用户的操作。
(2)视图使用户能从多种角度看待同一数据。
(3)视图对重构数据库提供一定程序的逻辑独立性。
(4)视图能够对机密数据提供安全保护。
3、创建视图
语法:
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(字段名列表)]
AS
select语句
[ WITH [CASCADED|LOCAL] CHECK OPTION ]
说明:
(1)OR REPLACE:表示当已具有同名的视图时,将覆盖原视图。
(2)ALGORITHM子句:可选项,表示视图选择的算法。ALGORITHM可取三个值:MERGE、TEMPTABLE或UNDEFINED。
如果没有ALGORITHM子句,默认算法是UNDEFINED(未定义的)。算法会影响MySQL处理视图的方式。
MERGE:会将引用视图的语句的文本与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分。
TEMPTABLE:视图的结果将被置于临时表中,然后使用它执行语句。
UNDEFINED:由MySQL选择所要使用的算法。如果可能,它倾向于MERGE而不是TEMPTABLE,这是因为MERGE通常更有效,而且如果使用了临时表,视图是不可更新的。
(3)WITH CHECK OPTION:对于可更新视图,给定WITH CHECK OPTION子句用来防止插入或更新行,除非作用在行上的select_statement中的WHERE子句为“真”。
在可更新视图中加入WITH CHECK OPTION子句,当视图是根据另一个视图定义时,LOCAL和CASCADED关键字决定了检查测试的范围。
LOCAL关键字对CHECK OPTION进行了限制,使其仅作用在定义的视图上,
CASCADED会对该视图相关的所有视图和基表进行检查。
如果未给定任一关键字,默认值为CASCADED。
例子:
-- 查询其他系中比数学系全部学生年龄大的学生姓名和年龄
select sname, YEAR(CURDATE()) - YEAR(sbirthday) as age
from student
where YEAR(CURDATE()) - YEAR(sbirthday) > ALL(
select YEAR(CURDATE()) - YEAR(sbirthday)
FROM student
where sdept='数学系'
) AND sdept!='数学系';
-- 定义视图(保存上面查询语句的结果)
create view stu_older_info
AS
select sname, YEAR(CURDATE()) - YEAR(sbirthday) as age
from student
where YEAR(CURDATE()) - YEAR(sbirthday) > ALL(
select YEAR(CURDATE()) - YEAR(sbirthday)
FROM student
where sdept='数学系'
) AND sdept!='数学系';
-- 查询视图数据
SELECT * FROM stu_older_info;
SELECT * FROM stu_older_info WHERE age=33; -- 视图的查询跟查询数据表一样可以条件查询 及其他各种查询方式
4、修改视图
-- 语法:alter view 视图名称 as 修改后的查询语句;
-- 示例:
ALTER VIEW stu_no
AS
SELECT *
FROM sc
WHERE degree > ANY(
SELECT degree FROM sc where sno='20050301'
) AND sno!='20050301';
SELECT * FROM stu_no;
视图修改完成后,再次查询视图,此时视图的数据变成执行修改后的查询语句。
修改视图的名称:可以先将视图删除,然后按照相同的定义语句进行视图的创建,并命名为新的视图名称
5、删除视图
-- 语法:drop view 视图名称
-- 示例:
drop view stu_no;
6、查看视图信息
1)使用DESCRIBE查看视图
在MySQL中,使用权DESCRIBE语句可以查看视图的字段信息,包括字段名、字段类型等信息。
-- DESCRIBE语句的语法格式如下所示:
DESCRIBE 视图名;
-- 或简写为:
DESC 视图名;
-- 示例:
desc stu_older_info;
查看信息结果说明:Field:视图中的字段名;Type:字段的数据类型;Null:表示该字段是否允许存放空值;Key:表示该字段是否已经建有索引;Default:表示该列是否有默认值;Extra:表示该列的附加信息。
2)使用SHOW TABLE STATUS语句查看视图
-- 语法:
SHOW TABLE STATUS LIKE '视图名称';
-- 示例
SHOW TABLE STATUS LIKE 'stu_older_info';
从查询中可以看到,Comment的值为VIEW,说明所查看的teacher_view是一个视图。
存储引擎(Engine)、数据长度(Data_length)、索引长度(Index_length)等信息都显示为NULL,说明视图是虚拟表。
3)查看视图的创建语句:SHOW CREATE VIEW语句
在查询结果的create view字段,可以查看定义视图的语句
SHOW CREATE VIEW stu_older_info;
7、通过视图更新数据
insert ,update ,delete
-- 通常是对基表的数据进行跟新,视图的数据随着更新
UPDATE t_stuinfo SET c_id=1 WHERE s_id=8 -- 更新基表
SELECT * FROM vi_stu_class -- 查询视图(数据已改变)
-- 能不能通过视图修改数据(把修改的数据保存到基表),可以,但是有很多限制
UPDATE vi_stu_class SET s_sex='女' WHERE s_id=8 -- 修改视图数据
SELECT * FROM t_stuinfo; -- 查询基表数据已修改
8、注意事项
(1) 创建,删除视图等操作需要权限
(2) 视图属于数据库。在默认情况下,将在当前数据库创建新视图。要想在给定数据库中创建视图,创建时,应将名称指定为数据库名.视图名。
如:当前数据库t4 ,想在t9中创建 名为 vi_stu_sc的视图
create view t9.vi_stu_sc as 查询语句
(3) 如果视图的基表有多张,多张表有共同的字段,查询select字句后要指定 该字段所属的表 ,
如:select * from 表1 inner join 表2 on 表1.id=表2.id
在创建视图时的sql语句 写 select 表1.id ,表1.某字段。。。。。from 表1 inner join 表2 on 表1.id=表2.id
(4) 通过修改视图从而修改基表数据的注意事项有:
使用INSERT语句进行插入操作的视图必须能够在基表中插入数据,否则插入操作会失败。
如果视图上没有包括基表中所有属性为NOT NULL 的字段,那么插入操作会由于那些字段的NULL值而失败。
如果在视图中使用聚合函数的结果,或者是包含表达式计算的结果,则插入操作不成功。
不能在使用了DISTINCT,UNION,TOP,GROUP BY 或HAVING语句的视图中插入数据。
如果在创建视图的CREATE VIEW语句中使用了WITH CHECK OPTION ,那么所有对视图进行修改的语句必须符合WITH CHECK OPTION中限定条件。
对于由多个基表联接查询而生成的视图来说,一次插入操作只能作用于一个基表上。
在视图中更新数据与在基表中更新数据一样,使用UPDATE语句。
但是当视图是来自多个基表中的数据时,与插入操作一样,每次更新操作只能更新一个基表中的数据,
如果通过视图修改存在于多个基表中的数据时,则对不同的基表要分别使用UPDATE语句来实现。
在视图中使用UPDATE语句进行更新操作也受到与插入操作一样的限制。
当一个视图联接了两个以上的基表时,对数据的删除操作则不允许的
本文来自博客园,作者:Cloudservice,转载请注明原文链接:https://www.cnblogs.com/whwh/p/16200066.html,只要学不死,就往死里学!