MySQL数据类型,服务器变量,sql模型以及表管理,视图

水深无声 2022-05-25 02:35 92阅读 0赞

*  存储引擎本质上就是表类型,每个表都可以根据需要选择适合的表类型,是表级别的概念,同一个数据库的各个表是可以使用不同的存储类型的;
 *  存储引擎在创建表时,可以进行指定,否则使用的就是默认的存储引擎;
 *  存储引擎:
    
     *  `MyISAM`:不支持事务,支持表级别的锁;
     *  `InnoDB`:支持事务,支持行级别的锁;
 *  `show table status`:用于查看表的状态,以及使用的存储引擎等信息;  
    ![这里写图片描述][70]
 *  连接`SQL`服务器的两种方式:
    
     *  动态`SQL`:通过函数或者方法与数据库服务建立连接;
     *  嵌入式`SQL`:使用程序开发语言本身的格式来写的,通过`API`直接连接到服务器上面
 *  `mysqld_safe`:使用的是安全线程;
 *  `mysqld_multi`:用于在一个物理主机上面运行多个`MySQL`实例,一般是通过监听在不同的端口上面实现的;
 *  查看`MySQL`默认的指令和参数

[root@server60 ~]# mysql --help --verbose

![这里写图片描述][70 1]

*  `show character set`:用于查看支持的字符集合  
    ![这里写图片描述][70 2]
 *  管理`DBA`的主要职责:数据库安装,升级,备份恢复,恢复,用户管理,权限管理,监控,性能分析,基准测试;
 *  经验总结是核心;
 *  **MySQL的数据类型**
 *  数值型:
    
     *  精确数值型:`INT[整形] decimal[十进制]`
     *  近似数值型:`FLOAT[浮点型] DOUBLE real`
 *  字符型:
    
     *  定长:`CHAR BINARY[区分大小写]`
     *  变长:`VARCHAR VARBINARY[区分大小写]`
     *  文本大对象:`text[不区分大小写] blob[区分大小写]`
     *  枚举:`ENUM`
     *  集合:`SET`
 *  日期时间:
    
     *  `data`:
     *  `time`:
     *  `datetime`:
     *  `timestamp`:
 *  数据类型的作用:
    
     *  1.用于存储什么类型的值;
     *  2.能够占据多大的空间;
     *  3.定长还是变长的;
     *  4.`MySQL`如何对其进行比较和排序;
     *  5.是否可以创建索引;
 *  `MySQL`的数值类型:
    
     *  `Character String Types`;
     *  `Binary Large Object String Types`
     *  `Numeric Types`
     *  `Boolean Types`
     *  `datetime Types`
     *  `Internal Types`
 *  `Numeric Data Types`:
    
     *  `TINYINT`:`A Very Small integer 1`个字节;
     *  `SMALLINT`:`A Small integer 2`个字节;
     *  `MEDIUMINT`:`A medium-sized integer 3`个字节;
     *  `INT`:`A standard integer 4`个字节;
     *  `BIGINT`:`A large integer 8`个字节;
     *  `DECIMAL`:精确十进制数;
     *  `FLOAT`:`A single-precision floating-point number`;
     *  `DOUBLE`:`A Double-precision floating-point number`;
     *  `BIT`:`A bit field`,按位进行存储;
 *  `String Data Types` :
    
     *  `CHAR(M)`:`Max 255`;
     *  `VARCHAR(M)`:`MAX 65535 M-1`个字符,一个字节可以表示的需要一个结束符号,每一个字节都需要多出一位用于存储结束符号;
     *  `BINARY`:`MAX`
     *  `VARBINARY`:
     *  `TINYBLOB`:
     *  `BLOB`:表示二进制大对象;
     *  `MEDIUMBLOB`:
     *  `LONGBLOB`:
     *  `TINYTEXT(L)`:表示`255 L-1`;
     *  `TEXT(L)`:表示`65535 L-2`;
     *  `MEDIUMTET(L)`:`16777215 L-3`;
     *  `LONGTEXT(L)`:`4294967 295 L-4`;
     *  `ENUM`:枚举,只能够在里面选择一个,必须是有限的范围,最多可以表示`65535`种变化;
     *  `SET`:集合,里面的元素是可以随意组合的,通常枚举的是`1-->64`个字符串;
 *  `Data and Time Data Types` :
    
     *  `DATA`:`CCYY-MM-DD`;
     *  `TIME`:`hh:mm:ss`;
     *  `DATATIME`:`CCYY-MM-DD hh:mm:ss`表示范围`1000-01-01 00:00:01 to 9999-12-31 23:59:59`
     *  `YEAR`:`CCYY YY`,表示范围`YEAR(2) 00-->99 YEAR(4):1901-2155`
 *  字符串类型的修是属性:
    
     *  `NOT NULL`:表示不允许为空;
     *  `NULL`:表示可以为空;
     *  `DEFAULT`:表示默认值;
     *  `CHARACTER SET`:表示字符集和;
     *  `COLLATION`:表示排序规则字符的排序规则从表继承,表从数据库继承,数据库从数据库服务器来继承;
 *  查看当前数据库系统支持的字符集合  
    ![这里写图片描述][70 3]
 *  显示所有的排序规则  
    ![这里写图片描述][70 4]
 *  `Binary Large Object String Types`:这个是区分大小写的;
    
     *  `TINYBLOB`:`255bytes`;
     *  `MEDIUMBLOB`:`16Mb`;
     *  `LONGBLOB`:`4Gb`;
     *  `BLOB`:`64Kb`;
     *  `BINARY`:
     *  `VARBINARY`:
 *  字段支持的属性
    
     *  `AUTO_INCREMENT` :自动生成数据序列,可以自动进行增长,可以设定步长;
     *  这个字段不可以为空,需要创建索引,主键,或者是唯一键索引;
     *  必须是整数类型,必须是是无符号类型`unsigned`;
     *  可以使用函数`LAST_INSERT_ID()`用于显示上一次生成的自动序列的值,函数的执行使用`select 函数名()`
     *  必须定义为`NOT NULL`;
 *  `MySQL`的`SQL`模型
 *  用于定义用户的数据数据或者行为在违反了`SQL` 的模式的时候,应该采取怎样的机制;
    
     *  `ANSI_QUOTE`:表示双引号相当于反引号,单引号用于标识字符串;
     *  `IGNORE_SPACE`:表示在内键函数时,忽略空白字符;
     *  `STRICT_ALL_TABLES`:没有进行设置的时候,所有的非法数据都是允许的,但是返回警告信息如果设置,就不允许进行填入;
     *  `STRICT_TRANS_TABLS`:向支持事务的表中增加非法的数据是不允许的,并且返回错误;
     *  `TRADITIONAL`:使`MySQL`的模型支持更多中的数据库模型;
 *  查看使用的`SQL`模型  
    ![这里写图片描述][70 5]

**服务器变量**

*  全局变量:
    
     *  是在服务器配置文件里面定义的变量,通过`mysql> SHOW GLOBAL VARIABLES;`进行查看,全局的配置只有管理员才有权限;
 *  会话变量:
    
     *  表示在登录`Mysql`会话中的变量,会话终止,变量的生命周期结束;,使用`mysql> SHOW SESSION VARIABLES;`进行查看;
 *  按照生效时间:
    
     *  动态调整:可以及时调整,立即生效;
     *  静态的:有的写在配置文件中,有的必须通过参数传递给`Mysqld`进程;
 *  动态调整参数的生效方式:
    
     *  全局:对当前会话无效,只对于新建立会话有效;
     *  会话:及时生效,只对于当前会话有效;
     *  新建立的会话默认是从全局继承的,但是允许修改;
 *  `@@`用于显示服务器变量,`@`用于显示用户自定义变量  
    ![这里写图片描述][70 6]  
    ![这里写图片描述][70 7]
 *  服务器变量的设定:
 *  `SELECT GLOBAL | SESSION 变量名='value';`;
 *  全局变量对于新建立会话有效,对于当前会话无效;  
    ![这里写图片描述][70 8]
 *  新建立会话查询会话变量是否生效  
    ![这里写图片描述][70 9]
 *  **数据库的管理**
 *  创建数据库  
    ![这里写图片描述][70 10]

mysql> CREATE DATABASE IF NOT EXISTS studentsdb CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';
    Query OK, 1 row affected (0.81 sec)

*  对应的数据库的数据目录里面会生成一个目录  
    ![这里写图片描述][70 11]
 *  修改数据库  
    ![这里写图片描述][70 12]
 *  对于修改来说,通常只限于修改`CHARACTER[字符集合] SET COLLATE[排序规则]`以及`DIRECTORY NAME[数据字典]`,最后一个多用于数据库数据需要从一个老版本的迁移到新版本时可能需要升级数据字典名称
 *  删除数据库  
    ![这里写图片描述][70 13]
 *  **表的管理**
 *  创建表
 *  1.直接定义一张空表  
    ![这里写图片描述][70 14]

mysql> CREATE TABLE tb1(
        -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        -> name CHAR(30) NOT NULL,
        -> age TINYINT NOT NULL);
    Query OK, 0 rows affected (2.35 sec)

*  对于上面主键的定义还可以通过

mysql> CREATE TABLE tb2(
        -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        -> name CHAR(30) NOT NULL,
        -> age TINYINT NOT NULL,
        -> PRIMARY KEY(id) UNIQUE(name),INDEX(age));
    Query OK, 0 rows affected (0.03 sec)

*  键:都是索引,属于特殊的索引,也称为约束,属于特殊的索引,索引通常包括两种`B-tree HASH`;

mysql> CREATE TABLE courses(
        -> Cid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        -> Course VARCHAR(50) NOT NULL);
    Query OK, 0 rows affected (0.01 sec)

*  查看表的状态  
    ![这里写图片描述][70 15]
 *  删除表,并且重新创建表,指定存储引擎为`MyISAM`

mysql> CREATE TABLE courses( Cid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Course VARCHAR(50) NOT NULL) ENGINE=MyISAM;
    mysql> SHOW TABLE STATUS LIKE 'courses'\G;
    *************************** 1. row ***************************
               Name: courses
             Engine: MyISAM
            Version: 10
         Row_format: Dynamic  由于存储机制的不同这一项是发生改变了的;
               Rows: 0
     Avg_row_length: 0
        Data_length: 0
    Max_data_length: 281474976710655
       Index_length: 1024
          Data_free: 0
     Auto_increment: 1
        Create_time: 2018-05-07 06:14:15
        Update_time: 2018-05-07 06:14:15
         Check_time: NULL
          Collation: gbk_chinese_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)

*  插入数据

mysql> INSERT INTO courses (Course) values("lishi"),('shuxue'),('huaxue');
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    mysql> SELECT * FROM courses;
    +-----+--------+
    | Cid | Course |
    +-----+--------+
    |   1 | lishi  |
    |   2 | shuxue |
    |   3 | huaxue |
    +-----+--------+
    3 rows in set (0.00 sec)

*  查看索引

mysql> SHOW INDEX FROM courses\G;
    *************************** 1. row ***************************
            Table: courses
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: Cid
        Collation: A
      Cardinality: 3
         Sub_part: NULL
           Packed: NULL
             Null: 
       Index_type: BTREE
          Comment: 
    Index_comment: 
    1 row in set (0.01 sec)

*  2.从其他表中查询出数据,并且以之创建新表;  
    ![这里写图片描述][70 16]

mysql> CREATE TABLE testcourses SELECT * FROM courses WHERE Cid <=2;
    Query OK, 2 rows affected (0.05 sec)
    Records: 2  Duplicates: 0  Warnings: 0

![这里写图片描述][70 17]

*  查看表的结构  
    ![这里写图片描述][70 18]
 *  表的结构是近似一样的,但是丢失了`AUTO_INCREMENT`属性
 *  3.仿照其他表的末班进行创建新表  
    ![这里写图片描述][70 19];

*  关于`create_definition`  
    ![这里写图片描述][70 20]
 *  支持表的选项  
    ![这里写图片描述][70 21]
 *  \*\* MyISAM和InnoDB存储引擎的存储机制是不一样的\*\*
 *  **修改表**  
    ![这里写图片描述][70 22]
 *  添加唯一键索引  
    ![这里写图片描述][70 23]
 *  查看添加的索引  
    ![这里写图片描述][70 24]
 *  修改字段的定义,必须自己附带上所有的属性信息,否则属性信息会丢失;

mysql> ALTER TABLE test CHANGE Course course VARCHAR(50) NOT NULL;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0

*  新增加字段

*  更改表的名称

*  **删除表**  
    ![这里写图片描述][70 25]
 *  创建`student`表

mysql> CREATE TABLE student(
        -> SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        -> Name VARCHAR(50),
        -> CID INT NOT NULL);
    Query OK, 0 rows affected (0.00 sec)

*  插入一些数据

mysql> INSERT INTO student(Name,CID) VALUES('Yue Buqun','2'),('Zhang Wuji','3');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0

*  将两张表进行笛卡尔乘积

*  添加外键约束\[只能够用在支持事物的存储引擎上面,并且两个字段的属性必须是一样的\],并且需要定义数据不匹配时的行为

mysql> ALTER TABLE courses ENGINE=InnoDB;
    Query OK, 3 rows affected (0.39 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> ALTER TABLE student ADD FOREIGN KEY (CID) REFERENCES courses(CID);
    ERROR 1005 (HY000): Can't create table 'studentsdb.#sql-ac4_1' (errno: 150)
    mysql> ALTER TABLE student MODIFY CID TINYINT UNSIGNED NOT NULL;
    Query OK, 2 rows affected (0.08 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> ALTER TABLE student ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses(CID);
    Query OK, 2 rows affected (0.77 sec)
    Records: 2  Duplicates: 0  Warnings: 0

*  查看外键索引  
    ![这里写图片描述][70 26]
 *  索引是不能够被删除的;
 *  接下来尝试插入课程名不存在的课程`ID`

mysql> INSERT INTO student (name,cid) VALUES('Chen xiaoxiao','5');
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`studentsdb`.`student`, CONSTRAINT `student_ibfk_2` FOREIGN KEY (`CID`) REFERENCES `courses` (`Cid`))
    
    mysql> INSERT INTO student (name,cid) VALUES('Chen xiaoxiao','3');
    Query OK, 1 row affected (0.00 sec)

*  如果擅长已经存在学生选修的课程,会执行默认的策略,这里的默认的策略是不允许进行删除的

mysql> DELETE FROM courses WHERE CID=3;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`studentsdb`.`student`, CONSTRAINT `student_ibfk_2` FOREIGN KEY (`CID`) REFERENCES `courses` (`Cid`))

*  创建索引  
    ![这里写图片描述][70 27]

mysql> CREATE INDEX name_on_student ON student (Name) USING BTREE;
    Query OK, 0 rows affected (0.09 sec)
    Records: 0  Duplicates: 0  Warnings: 0

*  查看索引  
    ![这里写图片描述][70 28]
 *  删除索引  
    ![这里写图片描述][70 29]

mysql> DROP INDEX name_on_student ON student;
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    //创建索引是为了实现比较和排序,索引的长度只需要能够用于区分索引,就可以了,不需要太长;
    //索引不能够太大,索引是为了加速查找,
    mysql> CREATE INDEX name_on_student ON student (Name(5) DESC) USING BTREE;

![这里写图片描述][70 30]

*  **单表,多表查询操作**
 *  `SELECT`查询
 *  简单查询:
 *  `SELECT * FROM tb_name;`  
    ![这里写图片描述][70 31]
 *  `[投影]SELECT field1 field2 FROM tb_name;`  
    ![这里写图片描述][70 32]
 *  `[选择]SELECT [DISTINCT(表示相同的值只显示一次)] * FROM tb_name WHERE qualification;`  
    ![这里写图片描述][70 33]

![这里写图片描述][70 34]

*  `FROM`子句:表示要查询的关系 表,多个表,其他`SELECT`语句;
 *  `WHERE`子句:`bool`关系表达`!= | > | < | <= | >=`,数值比较不能够加引号,字符串比较必须添加引号;
 *  `<=>`:表示存在空值也能够进行正确比较的;
 *  对于字符使用`''`进行引用,对于数值型,不能够使用引号;

mysql> SELECT Name,Age FROM students WHERE Age+1>20;
    +-------------+------+
    | Name        | Age  |
    +-------------+------+
    | DingDian    |   25 |
    | HuFei       |   31 |
    | ZhangWuji   |   20 |
    | Xuzhu       |   26 |
    | LingHuchong |   22 |
    +-------------+------+
    5 rows in set (0.00 sec)

*  对于直接在搜索码上面进行表达式计算,是不能够有效的使用索引的;
 *  表达式之间的逻辑关系:`AND OR NOT`;
 *  `AND`

mysql> SELECT Name,Gender,Age FROM students WHERE Age>20 AND Gender='M';
    +-------------+--------+------+
    | Name        | Gender | Age  |
    +-------------+--------+------+
    | DingDian    | M      |   25 |
    | HuFei       | M      |   31 |
    | Xuzhu       | M      |   26 |
    | LingHuchong | M      |   22 |
    +-------------+--------+------+
    4 rows in set (0.00 sec)

*  `OR`

mysql> SELECT Name,Gender,Age FROM students WHERE Age>20 OR Gender='M';
    +-------------+--------+------+
    | Name        | Gender | Age  |
    +-------------+--------+------+
    | GuoJing     | M      |   19 |
    | YangGuo     | M      |   17 |
    | DingDian    | M      |   25 |
    | HuFei       | M      |   31 |
    | ZhangWuji   | M      |   20 |
    | Xuzhu       | M      |   26 |
    | LingHuchong | M      |   22 |
    +-------------+--------+------+
    7 rows in set (0.00 sec)

*  `NOT`

mysql> SELECT Name,Age,Gender FROM students WHERE NOT Age>20 AND NOT Gender='M';
    +--------------+------+--------+
    | Name         | Age  | Gender |
    +--------------+------+--------+
    | HuangRong    |   16 | F      |
    | YueLingshang |   18 | F      |
    | YiLin        |   19 | F      |
    +--------------+------+--------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender='M');
    +--------------+------+--------+
    | Name         | Age  | Gender |
    +--------------+------+--------+
    | HuangRong    |   16 | F      |
    | YueLingshang |   18 | F      |
    | YiLin        |   19 | F      |
    +--------------+------+--------+
    3 rows in set (0.00 sec)

*  `BETWEEN AND`

mysql> SELECT Name,Age,Gender FROM students WHERE Age BETWEEN 20 AND 25;
    +-------------+------+--------+
    | Name        | Age  | Gender |
    +-------------+------+--------+
    | DingDian    |   25 | M      |
    | ZhangWuji   |   20 | M      |
    | LingHuchong |   22 | M      |
    +-------------+------+--------+
    3 rows in set (0.00 sec)

*  字符串的匹配操作:
 *  `%`:任意长度任意字符;

mysql> SELECT Age,Name,Gender FROM students WHERE Name LIKE 'Y%';
    +------+--------------+--------+
    | Age  | Name         | Gender |
    +------+--------------+--------+
    |   17 | YangGuo      | M      |
    |   18 | YueLingshang | F      |
    |   19 | YiLin        | F      |
    +------+--------------+--------+
    3 rows in set (0.00 sec)

*  `_`:任意单个字符:

mysql> SELECT Age,Name,Gender FROM students WHERE Name LIKE 'Y____';
    +------+-------+--------+
    | Age  | Name  | Gender |
    +------+-------+--------+
    |   19 | YiLin | F      |
    +------+-------+--------+
    1 row in set (0.00 sec)

*  **正则表达式匹配**

mysql> SELECT Age,Name,Gender FROM students WHERE Name RLIKE '^[XY].*';
    +------+--------------+--------+
    | Age  | Name         | Gender |
    +------+--------------+--------+
    |   17 | YangGuo      | M      |
    |   18 | YueLingshang | F      |
    |   26 | Xuzhu        | M      |
    |   19 | YiLin        | F      |
    +------+--------------+--------+
    4 rows in set (0.00 sec)

*  `IN`

mysql> SELECT Age,Name,Gender FROM students WHERE Age IN(18,20,25);
    +------+--------------+--------+
    | Age  | Name         | Gender |
    +------+--------------+--------+
    |   25 | DingDian     | M      |
    |   18 | YueLingshang | F      |
    |   20 | ZhangWuji    | M      |
    +------+--------------+--------+
    3 rows in set (0.00 sec)

*  和`NULL`进行比较的时候,是不能够使用`=`号的

*  `Order By`

mysql> SELECT Name,CID2 FROM students WHERE CID2 IS NOT NULL ORDER BY Name;
    +--------------+------+
    | Name         | CID2 |
    +--------------+------+
    | DingDian     |    1 |
    | GuoJing      |    7 |
    | HuangRong    |    9 |
    | HuFei        |   10 |
    | Xuzhu        |    4 |
    | YangGuo      |    3 |
    | YueLingshang |    4 |
    | ZhangWuji    |    7 |
    +--------------+------+
    8 rows in set (0.01 sec)
    
    mysql> SELECT Name,CID2 FROM students WHERE CID2 IS NOT NULL ORDER BY Name DESC;
    +--------------+------+
    | Name         | CID2 |
    +--------------+------+
    | ZhangWuji    |    7 |
    | YueLingshang |    4 |
    | YangGuo      |    3 |
    | Xuzhu        |    4 |
    | HuFei        |   10 |
    | HuangRong    |    9 |
    | GuoJing      |    7 |
    | DingDian     |    1 |
    +--------------+------+
    8 rows in set (0.00 sec)

*  字段别名

*  `LIMIT`

*  `LIMIT 2,3`表示偏移两个之后,取三个;

*  聚合运算:通常包括`MAX MIN SUM AVG COUNT`

mysql> SELECT AVG(age) FROM students;
    +----------+
    | AVG(age) |
    +----------+
    |  21.3000 |
    +----------+
    1 row in set (0.02 sec)

*  `Group By`用于进行分组,分组的目的多用于进行聚合运算

mysql> SELECT AVG(Age) FROM students GROUP BY Gender;
    +----------+
    | AVG(Age) |
    +----------+
    |  17.6667 |
    |  22.8571 |
    +----------+
    2 rows in set (0.00 sec)

*  对于`GROP BY`的结果进行过滤使用`HAVING qualification`(条件等同于`Where`);

mysql> SELECT CID2, COUNT(CID2) AS Person_choice FROM new_student GROUP BY CID2  HAVING Person_choice>=2;
    +------+---------------+
    | CID2 | Person_choice |
    +------+---------------+
    |    6 |             2 |
    |    7 |             2 |
    +------+---------------+
    2 rows in set (0.00 sec)

*  **多表查询**
 *  交叉连接:也成为迪卡尔乘积;
 *  内连接:自然连接,两张表上面的值,进行等值比较,只有保持了等值关系的才连接起来;对应字段必须存在等值关系;

*  表的别名机制

*  外连接:不需要在两个表之间建立完全的等值关系,比如说,按照右表为标准,显示右表的所有字段和左表的某些字段;或者使用左表为标准,显示左表的某些字段和右表的所有字段;
 *  左外连接: `... LEFT JOIN ...ON...`;

*  右外连接:`..... RIGHT JOIN ...ON...`

*  全外连接:`MySQL`不支持;
 *  自连接:需要连接的数据项依然在自己的这张表上面;

*  子查询:在某一张表内部进行的查询,一个查询中嵌套另外一个查询的方式,在比较操作中使用子查询,子查询的返回值只有一个;

*  `IN`中使用子查询

mysql> SELECT Name FROM students WHERE Age IN(SELECT Age FROM tutors);
    Empty set (0.01 sec)

*  `FROM`中使用子查询

mysql> SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age >=20;
    +-------------+------+
    | Name        | Age  |
    +-------------+------+
    | DingDian    |   25 |
    | HuFei       |   31 |
    | ZhangWuji   |   20 |
    | Xuzhu       |   26 |
    | LingHuchong |   22 |
    +-------------+------+
    5 rows in set (0.00 sec)

*  `UNION`联合两个`SELECT`查询结果

mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
    +--------------+------+
    | Name         | Age  |
    +--------------+------+
    | GuoJing      |   19 |
    | YangGuo      |   17 |
    | DingDian     |   25 |
    | HuFei        |   31 |
    | HuangRong    |   16 |
    | YueLingshang |   18 |
    | ZhangWuji    |   20 |
    | Xuzhu        |   26 |
    | LingHuchong  |   22 |
    | YiLin        |   19 |
    | HongQigong   |   93 |
    | HuangYaoshi  |   63 |
    | Miejueshitai |   72 |
    | OuYangfeng   |   76 |
    | YiDeng       |   90 |
    | YuCanghai    |   56 |
    | Jinlunfawang |   67 |
    | HuYidao      |   42 |
    | NingZhongze  |   49 |
    +--------------+------+
    19 rows in set (0.00 sec)

*  挑选出`courses`表中没有被`students`中的`CID2`学习的课程的名称;挑选出`courses`表中没有被`students`中的`CID2`学习的课程的名称;

*  挑选出没有教授任何课程的老师

mysql>  SELECT Tname FROM tutors WHERE TID NOT IN (SELECT DISTINCT TID FROM courses);
    +-------------+
    | Tname       |
    +-------------+
    | NingZhongze |
    +-------------+
    1 row in set (0.00 sec)

*  找出`students`表中`CID1`有两个或者两个以上同学学习了同一门课程的课程名称

*  显示每一位老师及其所教授的课程,没有教授的课程显示为`NULL`;

*  显示每一个课程及其相关的老师,没有老师教授的课程将其老师显示为空

*  显示每位同学`CID1`课程的课程名称,以及其讲授的相关课程的老师的名称;

*  **视图**
 *  表示的含义就是存储下来的`SELECT`语句,基于基表的查询结果,视图就是保存的`SELECT`语句,但是可以使用`SHOW TABLES`进行查看,查看这张表数据的过程其实就是在执行存储的`SELECT`语句的过程;
 *  创建视图  
    ![这里写图片描述][70 35]

*  查询`SELECT * FROM sct`

*  查询的得到的结果就是存储的`SELECT`语句的执行过程,视图在不违反基表的存储法则的情况下,是可以插入数据的,但是是不建议这么做的;
 *  删除视图

DROP VIEW VIEW_NAME

*  物化视图,可以将`SELECT`的执行结果,保存下来,用于加快查询结果,否则`SELECT`的查询结果就在缓存中,如果缓存的的结果非常大,会导致资源的浪费,所以物化视图可以降低缓存的利用率,但是如果基表更新,那么物化下来的视图也要随之更新,更新不频繁的使用物化视图,MySQL是不支持物化视图的,也不支持在视图上面创建索引;
 *  视图可以用于限定某些用户茶看到的字段,这是为了安全的考虑;
 *  查看创建表的语句或者是过程

mysql> SHOW CREATE TABLE courses\G;
    *************************** 1. row ***************************
           Table: courses
    Create Table: CREATE TABLE `courses` (
      `CID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `Cname` varchar(100) NOT NULL,
      `TID` smallint(6) NOT NULL,
      UNIQUE KEY `CID` (`CID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

*  查看视图的创建过程

mysql> SHOW CREATE VIEW sct\G;
    *************************** 1. row ***************************
                    View: sct
             Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sct` AS select `t`.`Tname` AS `Tname`,`c`.`Cname` AS `Cname` from (`tutors` `t` left join `courses` `c` on((`t`.`TID` = `c`.`TID`)))
    character_set_client: utf8
    collation_connection: utf8_general_ci
    1 row in set (0.00 sec)

*  使用`AUTO_INCREMENT`字段属性,可以通过计数器进行查看
 *  这是一个计数器,即使表清空,但是计数器没有清空,那么仍然是从这里面计数值的下一个开始;

mysql> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |               10 |
    +------------------+
    1 row in set (0.00 sec)

*  如果需要删除表,并且清空计数器,那么应该使用`DRUNCATE table_name`来删除某张表;
 *  将查询的数据批量插入另一个表中

mysql> INSERT INTO tutors (Tname,Gender,Age) SELECT Name,Gender,Age FROM students WHERE Age >= 20;
    Query OK, 5 rows affected (0.77 sec)
    Records: 5  Duplicates: 0  Warnings: 0

*  `UPDATE`  
    ![这里写图片描述][70 36]
 *  物化视图:就是用于保存`select`语句的执行结果,坏处,基表更新,物化视图不建议使用,
 *  `mysql`不支持物化视图;
 *  数据的插入操作:
 *  `insert into tb_name (coli,coli,..) values(vali,vali,...)[,(vali,vali,...)]`
 *  对于字符串使用单引号,否则不使用,对于空值,使用`NULL`,不能够使用`''`来表示;
 *  还可以使用这种方式进行插入  
    ![这里写图片描述][70 37]
 *  还支持将一个表的查询结果,插入到另一张表中;  
    ![这里写图片描述][70 38]
 *  `replace into`:如果原来的表中存在数据,就进行替换,否则进行插入,用法和上面的一样;
 *  `DELETE`:操作很危险,可以通过限定条件来删除表中的数据;
 *  `truncate`:同时是一个函数,用于截取字符串;这里是用来清空表,并且重置计数器;
 *  `update`:用于更新数据,

update tb_name set coli=...,coli=... where

[70]: /images/20220525/02453f0507584d14b8c67781c454bb83.png
[70 1]: /images/20220525/88da801fb5a04335b01726165b99b72c.png
[70 2]: /images/20220525/d2356caaad7549f09bc0caa32783e05f.png
[70 3]: /images/20220525/373fc51f4f3e4bdbbee38d54e97ea37a.png
[70 4]: /images/20220525/9c50221830864a5db14baf746a96f9ad.png
[70 5]: /images/20220525/9a72989fd55b49e98f05d17136a4b567.png
[70 6]: /images/20220525/9a09054f46da40b49a73be48248f03dc.png
[70 7]: /images/20220525/01f15e437dda4352b71b6099f34d5691.png
[70 8]: /images/20220525/90092569b412488d80586d09f352d9ba.png
[70 9]: /images/20220525/170f0a722a4e41b9a9aa88c740b3be5f.png
[70 10]: /images/20220525/cd07eaf5dc614c14932981eb0ae0ce56.png
[70 11]: /images/20220525/dd38a86df5b04d389361396994df4fd5.png
[70 12]: /images/20220525/f5665e9d26764411951fcf4c5256daae.png
[70 13]: /images/20220525/8c75f6b38c1c478aa0b0d9ec26c6de41.png
[70 14]: /images/20220525/f6810a96c5e444a6bdf38751c71ffdbb.png
[70 15]: /images/20220525/12b881c36d744cbcb0bf56bad68c8aaa.png
[70 16]: /images/20220525/f83b21721bc44228825965740da3988c.png
[70 17]: /images/20220525/f5f98bdf4d3b47aea28b9cbc6cabd36a.png
[70 18]: /images/20220525/707c5554305a408882950a56df3fdc9f.png
[70 19]: /images/20220525/7467bd83728f4058a52804b4c90d6af3.png
[70 20]: /images/20220525/80c218281dd141209655a5181b62e47d.png
[70 21]: /images/20220525/0fd3060d247748fb896202b840d77f3f.png
[70 22]: /images/20220525/d2e6f8c0d9e14030b391702f88981678.png
[70 23]: /images/20220525/e80c2aa367a14f9ab66a0d8e65c8c35d.png
[70 24]: /images/20220525/477db2fceb3d42bca0331d5928c5aae2.png
[70 25]: /images/20220525/33f94bbf2a3047b1ba5f4cbe412de031.png
[70 26]: /images/20220525/4673484291a84fceaa5278b0d19eeec8.png
[70 27]: /images/20220525/3ad633be08c04f9d9d2463fdb00102ce.png
[70 28]: /images/20220525/f9a2252a1bb24f4d839cd0ea84415f4b.png
[70 29]: /images/20220525/0c6b7cce05ff4476b72f7ba02a6e3bb4.png
[70 30]: /images/20220525/ea0e07dc7ace4933bd7f05c346ac8a6a.png
[70 31]: /images/20220525/0c7803db28f6447c94eb9be947bc6085.png
[70 32]: /images/20220525/64d59fd2b92a45c48ef72d339923cf49.png
[70 33]: /images/20220525/c9d9b50dc52d4ca9b9410ea49d9c3419.png
[70 34]: /images/20220525/011471a7c8034c488d5ef474566da23e.png
[70 35]: /images/20220525/75a1ba35f2f24fb89260416897802a93.png
[70 36]: /images/20220525/d4b9c315c0bb41e3a05746fabcdaa22e.png
[70 37]: /images/20220525/80f294a0fec24201b953c1db25c6b8be.png
[70 38]: /images/20220525/3d69187e2d994191981fa90b99e6bc70.png