Mysql数据库安装到操作到应用

该博文转载(https://www.cnblogs.com/whwh/p/16200066.html)

一、安装和配置数据库:

下载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语句进行更新操作也受到与插入操作一样的限制。

	当一个视图联接了两个以上的基表时,对数据的删除操作则不允许的
posted @ 2022-04-28 22:21  郑琰  阅读(76)  评论(0编辑  收藏  举报
#

# #