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

	当一个视图联接了两个以上的基表时，对数据的删除操作则不允许的

posted on 2022-04-27 18:17 Cloudservice 阅读(68) 评论(0) 编辑收藏举报

MySQL总结笔记

一、安装和配置数据库：

二、连接数据库的方式：

三、关于数据库的操作

四、关于表的操作

1、创建表

2、查看表

1）查看表结构

2）查看表详细结构

3）查看所有的表

3、修改表结构

1）修改表名

2）增加字段

3）删除字段

4）修改字段类型和名称

4、删除表

五、表记录相关操作

1、添加表记录

2、删除表记录

3、修改表记录

六、关于表的查询的操作：

1、通配符：

2、正则表达式：

七、多表连接查询

1、交叉连接

(1)语法格式：

(2)示例：

2、内连接

（1）语法格式：

（2）示例：

（3）多表连接查询

3、自连接

(1)语法格式：

(2)示例：

4、外连接

(1)语法格式：

(2)示例：

(3)数据库不支持FULL JOIN

八、嵌套查询（子查询）

1、嵌套子查询

（1）子查询返回单个值

（2）子查询返回一个值列表(用IN操作符实现查询)

（3）子查询返回一个值列表(用ANY或ALL操作符实现查询)

2、相关子查询

九、集合查询

十、索引

1、关于索引

2、创建表时创建普通索引

3、在已经存在的表上创建普通索引

4、通过SQL中语句alter table创建普通索引

5、创建表时创建唯一索引

6、在已经存在的表上创建唯一索引

7、通过alter table 创建唯一索引

8、删除索引

9、索引改名

十一、视图

1、视图常用操作：

2、视图优点

3、创建视图

4、修改视图

5、删除视图

6、查看视图信息

1）使用DESCRIBE查看视图

2）使用SHOW TABLE STATUS语句查看视图

3）查看视图的创建语句：SHOW CREATE VIEW语句

7、通过视图更新数据

8、注意事项

2）使用SHOW TABLE　STATUS语句查看视图