mysql安装与配置及四大引擎和数据类型、建表以及约束、增删改查、常用函数、聚合函数以及合并

why        数据在内存中容易丢失        数据在文件中，不便于操作what        使用完整的管理系统统一管理，易于查询where        凡是需要持久化存储数据的地方木语        DB                数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。        DBMS                数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的内器        SQL                结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言

#删除系统中的mariadb rpm包rpm -e --nodeps `rpm -qa|grep -i mariadb`#创建MySQL所用的用户和组groupadd -r mysql && useradd -r -g mysql -s /bin/false -M mysql                            #创建存储文件夹mkdir /data#解压压缩包tar zxf mysql.8.0.22.tar.gz#编写配置文件(my.cnf)vim /etc/my.cnf   [mysqld]  # 不区分大小写  lower_case_table_names=1  #设置分页内存和超时  max_allowed_packet=100000000 .NET_buffer_length=100000  interactive_timeout=28800000  wAIt_timeout=28800000  #sqlmodel  sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION  # 设置3306端口  port=3306  # 设置mysql的安装目录  basedir=/data/mysql  # 设置mysql数据库的数据的存放目录  datadir=/data/mysql/data  # 允许最大连接数  max_connections=200  # 允许连接失败的次数。  max_connect_errors=10  # 服务端使用的字符集默认为utf8mb4  character-set-server=utf8mb4  # 创建新表时将使用的默认存储引擎  default-storage-engine=INNODB  # 默认使用“mysql_native_password”插件认证  #mysql_native_password  default_authentication_plugin=mysql_native_password  [mysql]  # 设置mysql客户端默认字符集  default-character-set=utf8mb4  [client]  #设置mysql客户端连接服务端时默认使用的端口  port=3306#初始化数据库(无密码初始化)bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure#结果：2023-08-14T08:06:03.639569Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.2023-08-14T08:06:03.639705Z 0 [System] [MY-013169] [Server] /data/mysql/bin/mysqld (mysqld 8.0.22) initializing of server in progress as process 41762023-08-14T08:06:03.683673Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2023-08-14T08:06:04.515375Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2023-08-14T08:06:05.772764Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.#启动数据库support-files/mysql.server start#停止数据库support-files/mysql.server stop#重启数据库support-files/mysql.server restart#查看端口ss -anpt | grep 3306

1、user表(用户层权限)因为字段太多，只截取了一部分。首先登陆的时候验证Host,User,Password(authentication_string)也就是ip,用户名，密码是否匹配，匹配登陆成功将会为登录者分配权限，分配权限的顺序也是按照上面四张表的排列顺序进行的，举个例子，如果user表的Select_priv为Y说明他拥有所有表的查找权限，如果为N就需要到下一级db表中进行权限分配了。其中的%是通配符，代表任意的意思。2、db表(数据库层权限)来到db表之后会匹配Host,User然后会根据Db字段对应的表进行权限分配，像Select_priv这些字段对应的权限大家应该都能看出来是对应着什么权限了吧，这里不细说了（不偷懒，举个例子Select_priv，Insert_priv，Update_priv，Delete_priv，Create_priv，Drop_priv分别代表着查询，增加，更新，删除，创建，销毁）。其中Y代表着拥有此项权限，N则代表没有此项权限。3、tables_priv表(表层权限)与上面一样，这是通过Host，Db，User，Table来进行定位到表层的一个权限分配。不过它只有Table_priv和Column_priv两个字段来记录权限。4、columns_priv表(字段层权限)顾名思义，字段层权限，通过Host，Db，User，Table，Column来进行定位到字段层的一个权限分配，只有Column_priv来记录权限。相关SQLSELECT Host,User,authentication_string from user;SELECT * from user;SELECT * from db;SELECT * from tables_priv;SELECT * from columns_priv;

use mysql

1.查询用户SELECT * FROM user;2.创建用户并设置登录密码（MySQL5.7）#命令：create user 用户名 identified by '密码';#注：identified by会将纯文本密码加密作为散列值存储create user ls identified by '123456';
MySQL8#用户名密码创建需要分开#命令：create user 用户名;create user ls;3.查看用户信息（MySQL5.7）select host,user,password from user;
MySQL8select host,user,authentication_string from user;4.删除用户（慎用）命令：drop user 用户名：drop user ls;5.修改用户密码5.1修改密码（MySQL5.7）命令：set password for 用户名=password('新密码');set password for zs=password('123456');
MySQL（8）ALTER USER 用户 IDENTIFIED WITH mysql_native_password BY '密码';ALTER USER 'ls'@'%' IDENTIFIED WITH mysql_native_password BY '123456';5.2刷新配置命令：flush privileges;MySQL5.7与MySQL8关于权限操作没有差异性6.设置权限（Grant）#语法：grant privileges on databasename.tablename to username@'host';#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 查询权限grant SELECT on db_xiaoli.t_p1_user to zs@'%';#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限grant UPDATE on db_xiaoli.t_p1_user to zs@'%';#给 zs用户 赋予 数据库db_xiaoli中所有表 查询权限grant SELECT on db_xiaoli.* to zs@'%';#给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限grant ALL on db_xiaoli.* to zs@'%';7.撤销权限（Revoke）#语法：revoke privileges on databasename.tablename from username@'host';#啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响revoke DELETE on db_xiaoli.t_p1_user from zs@'%';#可以回收GRANT SELECT, UPDATE ON `db_xiaoli`.`t_p1_user` TO `zs`@`%`这条权限语句revoke all on db_xiaoli.t_p1_user from zs@'%';#可以回收GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限revoke all on db_xiaoli.* from zs@'%';#注：revoke只能回收grants列表中更小的权限；设置权限（Grant）和撤销权限（Revoke）的参数说明：1) privileges：用户的操作权限,如SELECT,INSERT,UPDATE,DELETE等，如果要授予所有权限直接使用：all;2) databasename：数据库名;3) tablename：   表名，如果要授予用户对所有数据库和表的操作权限直接使用：*.*;8.查看用户权限命令：show grants for 用户名show grants for 'zs'@'%';user表中host列的值的意义%             匹配所有主机localhost     localhost不会被解析成IP地址，直接通过UNIXsocket连接127.0.0.1     会通过TCP/IP协议连接，并且只能在本机访问；::1           ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

1.MySQL默认数据库介绍1)information_schema：是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息；2)mysql：核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息；3)test：测试数据库，没有东西；
2.创建数据库语法：create database 数据库名;或者create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;注：默认的数据库编码集：utf8（即UTF-8），collate表示排序规则为utf8_general_ci
3.查看所有数据库语法：show databases;
4.删除数据库(慎用)语法：drop database 数据库名;

存储引擎查看MySQL给开发者提供了查询存储引擎的功能，我这里使用的是MySQL5.1，可以使用：SHOW ENGINES
InnoDB存储引擎InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎所不能匹敌的3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键5、InnoDB被用在众多需要高性能的大型数据库站点上InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
MyISAM存储引擎MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是164、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上5、BLOB和TEXT列可以被索引6、NULL被允许在索引的列中，这个值占每个键的0~1个字节7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快9、可以把数据文件和索引文件放在不同目录10、每个字符列可以有不同的字符集11、有VARCHAR的表可以固定或动态记录长度12、VARCHAR和CHAR列可以多达64KB使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）
MEMORY存储引擎MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度2、MEMORY存储引擎执行HASH和BTREE缩影3、可以在一个MEMORY表中有非唯一键值4、MEMORY表使用一个固定的记录长度格式5、MEMORY不支持BLOB或TEXT列6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。1）字符类型：char varchar text blob2）数值类型：int bigint float decimalint -> intbigint -> longfloat -> 成绩decimal -> 货币类型(精度,小数)3）日期类型：date time datetime timestampdate -> yyyy:MM:dd HH:mm:sstime -> HH:mm:ssdatetime -> yyyy:MM:ddtimestamp（时间戳） -> 长整数

1.表的创建 ★/*语法：create table 表名(列名 列的类型【(长度) 约束】,列名 列的类型【(长度) 约束】,列名 列的类型【(长度) 约束】,...列名 列的类型【(长度) 约束】)*/案例：创建表BookCREATE TABLE book(id INT,#编号bName VARCHAR(20),#图书名price DOUBLE,#价格authorId INT,#作者编号publishDate DATETIME#出版日期);DESC book;案例：创建表authorCREATE TABLE IF NOT EXISTS author(id INT,au_name VARCHAR(20),nation VARCHAR(10))DESC author;
2.表的修改/*语法alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;*/①修改列名ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;②修改列的类型或约束ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;③添加新列ALTER TABLE author ADD COLUMN annual DOUBLE;④删除列ALTER TABLE book_author DROP COLUMN annual;⑤修改表名ALTER TABLE author RENAME TO book_author;DESC book;
3.表的删除DROP TABLE IF EXISTS book_author;SHOW TABLES;通用的写法：DROP DATABASE IF EXISTS 旧库名;CREATE DATABASE 新库名;DROP TABLE IF EXISTS 旧表名;CREATE TABLE 表名();4.表的复制INSERT INTO author VALUES(1,'村上春树','日本'),(2,'莫言','中国'),(3,'冯唐','中国'),(4,'金庸','中国');SELECT * FROM Author;SELECT * FROM copy2;
1.仅仅复制表的结构CREATE TABLE copy LIKE author;2.复制表的结构+数据CREATE TABLE copy2SELECT * FROM author;只复制部分数据CREATE TABLE copy3SELECT id,au_nameFROM authorWHERE nation='中国';仅仅复制某些字段CREATE TABLE copy4SELECT id,au_nameFROM authorWHERE 0;

/*含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性分类：六大约束NOT NULL：非空，用于保证该字段的值不能为空比如姓名、学号等DEFAULT:默认，用于保证该字段有默认值比如性别PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空比如学号、员工编号等UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空比如座位号CHECK:检查约束【mysql中不支持】比如年龄、性别FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值在从表添加外键约束，用于引用主表中某列的值比如学生表的专业编号，员工表的部门编号，员工表的工种编号添加约束的时机：1.创建表时2.修改表时约束的添加分类：列级约束：六大约束语法上都支持，但外键约束没有效果表级约束：除了非空、默认，其他的都支持主键和唯一的大对比：保证唯一性 是否允许为空   一个表中可以有多少个   是否允许组合主键√×至多有1个           √，但不推荐唯一√√可以有多个         √，但不推荐外键：1、要求在从表设置外键关系2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求3、主表的关联列必须是一个key（一般是主键或唯一）4、插入数据时，先插入主表，再插入从表删除数据时，先删除从表，再删除主表*/CREATE TABLE 表名(字段名 字段类型 列级约束,字段名 字段类型,表级约束)CREATE DATABASE students;

一、创建表时添加约束1.添加列级约束/*语法：直接在字段名和类型后面追加 约束类型即可。只支持：默认、非空、主键、唯一*/USE students;DROP TABLE stuinfo;CREATE TABLE stuinfo(id INT PRIMARY KEY,#主键stuName VARCHAR(20) NOT NULL UNIQUE,#非空gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查seat INT UNIQUE,#唯一age INT DEFAULT 18,#默认约束majorId INT REFERENCES major(id)#外键);CREATE TABLE major(id INT PRIMARY KEY,majorName VARCHAR(20));查看stuinfo中的所有索引，包括主键、外键、唯一SHOW INDEX FROM stuinfo;2.添加表级约束/*语法：在各个字段的最下面【constraint 约束名】 约束类型(字段名)*/DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(id INT,stuname VARCHAR(20),gender CHAR(1),seat INT,age INT,majorid INT,CONSTRAINT pk PRIMARY KEY(id),#主键CONSTRAINT uq UNIQUE(seat),#唯一键CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键);
SHOW INDEX FROM stuinfo;通用的写法：★CREATE TABLE IF NOT EXISTS stuinfo(id INT PRIMARY KEY,stuname VARCHAR(20),sex CHAR(1),age INT DEFAULT 18,seat INT UNIQUE,majorid INT,CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id));二、修改表时添加约束/*1、添加列级约束alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

*/DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(id INT,stuname VARCHAR(20),gender CHAR(1),seat INT,age INT,majorid INT)DESC stuinfo;1.添加非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;2.添加默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;3.添加主键①列级约束ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;②表级约束ALTER TABLE stuinfo ADD PRIMARY KEY(id);4.添加唯一①列级约束ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;②表级约束ALTER TABLE stuinfo ADD UNIQUE(seat);5.添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);三、修改表时删除约束1.删除非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;2.删除默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT ;3.删除主键ALTER TABLE stuinfo DROP PRIMARY KEY;4.删除唯一ALTER TABLE stuinfo DROP INDEX seat;5.删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;SHOW INDEX FROM stuinfo;

CRUD:创建（Create）、读取（Read）、更新（Update）和删除（Delete）

/*语法：select 查询列表 from 表名;类似于：System.out.println(打印东西);特点：1、查询列表可以是：表中的字段、常量值、表达式、函数2、查询的结果是一个虚拟的表格*/ USE myemployees;1.查询表中的单个字段SELECT last_name FROM t_mysql_employees;2.查询表中的多个字段SELECT last_name,salary,email FROM t_mysql_employees;3.查询表中的所有字段方式一：SELECT  `employee_id`,  `first_name`,  `last_name`,  `phone_number`,  `last_name`,  `job_id`,  `phone_number`,  `job_id`,  `salary`,  `commission_pct`,  `manager_id`,  `department_id`,  `hiredate`FROM  t_mysql_employees ;方式二：SELECT * FROM t_mysql_employees;4.查询常量值SELECT 100;SELECT 'john';5.查询表达式SELECT 100%98;6.查询函数SELECT VERSION();7.起别名/*①便于理解②如果要查询的字段有重名的情况，使用别名可以区分开来*/方式一：使用asSELECT 100%98 AS 结果;SELECT last_name AS 姓,first_name AS 名 FROM t_mysql_employees;方式二：使用空格SELECT last_name 姓,first_name 名 FROM t_mysql_employees;案例：查询salary，显示结果为 out putSELECT salary AS "out put" FROM t_mysql_employees;8.去重案例：查询员工表中涉及到的所有的部门编号SELECT DISTINCT department_id FROM t_mysql_employees;9.+号的作用/*java中的+号：①运算符，两个操作数都为数值型②连接符，只要有一个操作数为字符串mysql中的+号：仅仅只有一个功能：运算符select 100+90; 两个操作数都为数值型，则做加法运算select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型如果转换成功，则继续做加法运算select 'john'+90;如果转换失败，则将字符型数值转换成0select null+10; 只要其中一方为null，则结果肯定为null*/案例：查询员工名和姓连接成一个字段，并显示为 姓名SELECT CONCAT('a','b','c') AS 结果;SELECTCONCAT(last_name,first_name) AS 姓名FROMt_mysql_employees;

1、过滤2、在查询中过滤行3、where子句4、比较运算5、between6、in7、like8、null9、逻辑运算

/*语法：select 查询列表 from 表名 where 筛选条件;分类：一、按条件表达式筛选简单条件运算符：> < = != <> >= <=案例1：查询工资>12000的员工信息SELECT*FROMt_mysql_employeesWHEREsalary>12000;案例2：查询部门编号不等于90号的员工名和部门编号SELECTlast_name,department_idFROMt_mysql_employeesWHEREdepartment_id<>90;
二、按逻辑表达式筛选逻辑运算符：作用：用于连接条件表达式&& || !and or not&&和and：两个条件都为true，结果为true，反之为false||或or：只要有一个条件为true，结果为true，反之为false!或not： 如果连接的条件本身为false，结果为true，反之为false
案例1：查询工资z在10000到20000之间的员工名、工资以及奖金SELECTlast_name,salary,commission_pctFROMt_mysql_employeesWHEREsalary>=10000 AND salary<=20000;案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息SELECT*FROMt_mysql_employeesWHERENOT(department_id>=90 AND department_id<=110) OR salary>15000;三、模糊查询likebetween andinis null*/案例1：查询员工名中包含字符a的员工信息select*fromemployeeswherelast_name like '%a%';#abc案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资selectlast_name,salaryFROMt_mysql_employeesWHERElast_name LIKE '__n_l%';案例3：查询员工名中第二个字符为_的员工名SELECTlast_nameFROMt_mysql_employeesWHERElast_name LIKE '_$_%' ESCAPE '$';
2.between and/*①使用between and 可以提高语句的简洁度②包含临界值③两个临界值不要调换顺序案例1：查询员工编号在100到120之间的员工信息SELECT*FROMt_mysql_employeesWHEREemployee_id <= 120 AND employee_id>=100;----------------------SELECT*FROMt_mysql_employeesWHEREemployee_id BETWEEN 100 AND 120;

1.按条件表达式筛选案例1：查询工资>12000的员工信息SELECT*FROMt_mysql_employeesWHEREsalary>12000;
案例2：查询部门编号不等于90号的员工名和部门编号SELECTlast_name,department_idFROMt_mysql_employeesWHEREdepartment_id<>90;
2.按逻辑表达式筛选案例1：查询工资z在10000到20000之间的员工名、工资以及奖金SELECTlast_name,salary,commission_pctFROMt_mysql_employeesWHEREsalary>=10000 AND salary<=20000;
案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息SELECT*FROMt_mysql_employeesWHERENOT(department_id>=90 AND department_id<=110) OR salary>15000;
3.模糊查询/*likebetween andinis null|is not null
*/1.like/*特点：①一般和通配符搭配使用通配符：% 任意多个字符,包含0个字符_ 任意单个字符*、案例1：查询员工名中包含字符a的员工信息

select*fromemployeeswherelast_name like '%a%';#abc案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资selectlast_name,salaryFROMt_mysql_employeesWHERElast_name LIKE '__n_l%';

案例3：查询员工名中第二个字符为_的员工名
SELECTlast_nameFROMt_mysql_employeesWHERElast_name LIKE '_$_%' ESCAPE '$';2.between and/*①使用between and 可以提高语句的简洁度②包含临界值③两个临界值不要调换顺序*/案例1：查询员工编号在100到120之间的员工信息SELECT*FROMt_mysql_employeesWHEREemployee_id <= 120 AND employee_id>=100;----------------------SELECT*FROMt_mysql_employeesWHEREemployee_id BETWEEN 100 AND 120;
3.in/*含义：判断某字段的值是否属于in列表中的某一项特点：①使用in提高语句简洁度②in列表的值类型必须一致或兼容③in列表中不支持通配符*/案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号SELECTlast_name,job_idFROMt_mysql_employeesWHEREjob_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';------------------SELECTlast_name,job_idFROMt_mysql_employeesWHEREjob_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
4、is null/*=或<>不能用于判断null值is null或is not null 可以判断null值*/案例1：查询没有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMt_mysql_employeesWHEREcommission_pct IS NULL;
案例1：查询有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMt_mysql_employeesWHEREcommission_pct IS NOT NULL;
----------以下为×SELECTlast_name,commission_pctFROMt_mysql_employeesWHEREsalary IS 12000;
安全等于 <=>案例1：查询没有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMt_mysql_employeesWHEREcommission_pct <=>NULL;
案例2：查询工资为12000的员工信息SELECTlast_name,salaryFROMt_mysql_employeesWHEREsalary

#、进阶3：排序查询/*语法：select 查询列表from 表名【where 筛选条件】order by 排序的字段或表达式;
特点：1、asc代表的是升序，可以省略desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面，除了limit子句
*/
1、按单个字段排序SELECT * FROM t_mysql_employees ORDER BY salary DESC;
2、添加筛选条件再排序
案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT *FROM t_mysql_employeesWHERE department_id>=90ORDER BY employee_id DESC;
3、按表达式排序案例：查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))FROM t_mysql_employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4、按别名排序案例：查询员工信息 按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM t_mysql_employeesORDER BY 年薪 ASC;
5、按函数排序案例：查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name),last_nameFROM t_mysql_employeesORDER BY LENGTH(last_name) DESC;
6、按多个字段排序
案例：查询员工信息，要求先按工资降序，再按employee_id升序SELECT *FROM t_mysql_employeesORDER BY salary DESC,employee_id ASC;
排序练习1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM t_mysql_employeesORDER BY 年薪 DESC,last_name ASC;
2.选择工资不在8000到17000的员工的姓名和工资，按工资降序SELECT last_name,salaryFROM t_mysql_employees
WHERE salary NOT BETWEEN 8000 AND 17000ORDER BY salary DESC;
3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)FROM t_mysql_employeesWHERE email LIKE '%e%'ORDER BY LENGTH(email) DESC,department_id ASC;

/*功能：用作统计使用，又称为聚合函数或统计函数或组函数分类：sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数特点：1、sum、avg一般用于处理数值型max、min、count可以处理任何类型2、以上分组函数都忽略null值3、可以和distinct搭配实现去重的运算4、count函数的单独介绍一般使用count(*)用作统计行数5、和分组函数一同查询的字段要求是group by后的字段*/
1、简单 的使用SELECT SUM(salary) FROM t_mysql_employees;SELECT AVG(salary) FROM t_mysql_employees;SELECT MIN(salary) FROM t_mysql_employees;SELECT MAX(salary) FROM t_mysql_employees;SELECT COUNT(salary) FROM t_mysql_employees;SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM t_mysql_employees;SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM t_mysql_employees;
2、参数支持哪些类型SELECT SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;SELECT SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;SELECT MAX(last_name),MIN(last_name) FROM t_mysql_employees;SELECT MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;SELECT COUNT(commission_pct) FROM t_mysql_employees;SELECT COUNT(last_name) FROM t_mysql_employees;
3、是否忽略nullSELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM t_mysql_employees;SELECT MAX(commission_pct) ,MIN(commission_pct) FROM t_mysql_employees;SELECT COUNT(commission_pct) FROM t_mysql_employees;SELECT commission_pct FROM t_mysql_employees;
4、和distinct搭配SELECT SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;SELECT COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;
5、count函数的详细介绍SELECT COUNT(salary) FROM t_mysql_employees;SELECT COUNT(*) FROM t_mysql_employees;SELECT COUNT(1) FROM t_mysql_employees;
效率：MYISAM存储引擎下 ，COUNT(*)的效率高INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些6、和分组函数一同查询的字段有限制,employee_id是最小的那个SELECT AVG(salary),employee_id FROM t_mysql_employees;
分组函数练习1.查询公司员工工资的最大值，最小值，平均值，总和SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和FROM t_mysql_employees;
2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCEFROM t_mysql_employees;SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCEFROM t_mysql_employees;SELECT DATEDIFF('1995-2-7','1995-2-6');
3.查询部门编号为90的员工个数SELECT COUNT(*) FROM t_mysql_employees WHERE department_id = 90;

select name,boyname from beauty,boys;

– 省略连接条件– 连接条件无效– 所有表中的所有行互相连接• 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。

1、使用连接在多个表中查询数据  在 WHERE 子句中写入连接条件。  在表中有相同列时，在列名之前加上表名前缀2、区分重复的列名  在不同表中具有相同列名的列可以用表的别名加以区分。  如果使用了表别名，则在select语句中需要使用表别名代替表名  表别名最多支持32个字符长度，但建议越少越好3、表的别名  使用别名可以简化查询  使用表名前缀可以提高执行效率。4、连接多表  连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表，至少需要两个连接条件。  案例：含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行发生原因：没有有效的连接条件如何避免：添加有效的连接条件分类：按年代分类：sql92标准:仅仅支持内连接sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接按功能分类：内连接：等值连接非等值连接自连接外连接：左外连接右外连接全外连接交叉连接*/SELECT * FROM beauty;SELECT * FROM boys;SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id= boys.id;一、sql92标准1、等值连接/*
① 多表等值连接的结果为多表的交集部分②n表连接，至少需要n-1个连接条件③ 多表的顺序没有要求④一般需要为表起别名⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

*/
案例1：查询女神名和对应的男神名SELECT NAME,boyNameFROM boys,beautyWHERE beauty.boyfriend_id= boys.id;
案例2：查询员工名和对应的部门名SELECT last_name,department_nameFROM t_mysql_employees,departmentsWHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;
2、为表起别名/*①提高语句的简洁度②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM t_mysql_employees e,jobs jWHERE e.`job_id`=j.`job_id`;
3、两个表的顺序是否可以调换查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_titleFROM t_mysql_jobs j,t_mysql_employees eWHERE e.`job_id`=j.`job_id`;
4、可以加筛选案例：查询 有奖金 的员工名、部门名SELECT last_name,department_name,commission_pctFROM t_mysql_employees e,t_mysql_departments dWHERE e.`department_id`=d.`department_id`AND e.`commission_pct` IS NOT NULL;
案例2：查询 城市名中第二个字符为o 的部门名和城市名SELECT department_name,cityFROM t_mysql_departments t_mysql_d,locations lWHERE d.`location_id` = l.`location_id`AND city LIKE '_o%';
5、可以加分组案例1：查询 每个城市 的部门个数SELECT COUNT(*) 个数,cityFROM t_mysql_departments d,t_mysql_locations lWHERE d.`location_id`=l.`location_id`GROUP BY city;
案例2：查询 有奖金 的 每个部门 的 部门名和部门的领导编号 和该部门的最低工资SELECT department_name,d.`manager_id`,MIN(salary)FROM t_mysql_departments d,t_mysql_employees eWHERE d.`department_id`=e.`department_id`AND commission_pct IS NOT NULLGROUP BY department_name,d.`manager_id`;
6、可以加排序案例：查询 每个工种 的 工种名和员工的个数，并且 按员工个数降序SELECT job_title,COUNT(*)FROM t_mysql_employees e,t_mysql_jobs jWHERE e.`job_id`=j.`job_id`GROUP BY job_titleORDER BY COUNT(*) DESC;
7、可以实现三表连接？案例：查询员工名、部门名和所在的城市SELECT last_name,department_name,cityFROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations lWHERE e.`department_id`=d.`department_id`AND d.`location_id`=l.`location_id`AND city LIKE 's%'ORDER BY department_name DESC;
2、非等值连接案例1：查询员工的工资和工资级别SELECT salary,grade_levelFROM t_mysql_employees e,t_mysql_job_grades gWHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`AND g.`grade_level`='A';/*select salary,employee_id from employees;select * from job_grades;CREATE TABLE job_grades(grade_level VARCHAR(3),lowest_sal int,highest_sal int);
INSERT INTO job_gradesVALUES ('A', 1000, 2999);
INSERT INTO job_gradesVALUES ('B', 3000, 5999);
INSERT INTO job_gradesVALUES('C', 6000, 9999);
INSERT INTO job_gradesVALUES('D', 10000, 14999);
INSERT INTO job_gradesVALUES('E', 15000, 24999);
INSERT INTO job_gradesVALUES('F', 25000, 40000);
*/
3、自连接案例：查询 员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM t_mysql_employees e,t_mysql_employees mWHERE e.`manager_id`=m.`employee_id`;

分类内连接 [inner] join on外连接左外连接 left [outer] join on右外连接 right [outer] join on
使用ON子句创建连接  自然连接中是以具有相同名字的列为连接条件的。  可以使用 ON 子句指定额外的连接条件。  这个连接条件是与其它条件分开的。  ON 子句使语句具有更高的易读性。
join案例:/*语法：select 查询列表from 表1 别名 【连接类型】join 表2 别名on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】分类：内连接（★）：inner外连接左外(★):left 【outer】右外(★)：right 【outer】全外：full【outer】交叉连接：cross*/一）内连接/*语法：
select 查询列表from 表1 别名inner join 表2 别名on 连接条件;
分类：等值非等值自连接
特点：①添加排序、分组、筛选②inner可以省略③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集*/1、等值连接案例1.查询员工名、部门名
SELECT last_name,department_nameFROM t_mysql_departments dJOIN t_mysql_employees eON e.`department_id` = d.`department_id`;
案例2.查询名字中包含e的员工名和工种名（添加筛选）SELECT last_name,job_titleFROM t_mysql_employees eINNER JOIN t_mysql_jobs jON e.`job_id`= j.`job_id`WHERE e.`last_name` LIKE '%e%';
3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
①查询每个城市的部门个数②在①结果上筛选满足条件的SELECT city,COUNT(*) 部门个数FROM t_mysql_departments dINNER JOIN t_mysql_locations lON d.`location_id`=l.`location_id`GROUP BY cityHAVING COUNT(*)>3;
案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
①查询每个部门的员工个数SELECT COUNT(*),department_nameFROM t_mysql_employees eINNER JOIN t_mysql_departments dON e.`department_id`=d.`department_id`GROUP BY department_name
② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_nameFROM t_mysql_employees eINNER JOIN t_mysql_departments dON e.`department_id`=d.`department_id`GROUP BY department_nameHAVING COUNT(*)>3ORDER BY COUNT(*) DESC;
5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_titleFROM t_mysql_employees eINNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id`INNER JOIN t_mysql_jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
二）非等值连接
查询员工的工资级别
SELECT salary,grade_levelFROM t_mysql_employees eJOIN t_mysql_job_grades gON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
查询工资级别的个数>20的个数，并且按工资级别降序SELECT COUNT(*),grade_levelFROM t_mysql_employees eJOIN t_mysql_job_grades gON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`GROUP BY grade_levelHAVING COUNT(*)>20ORDER BY grade_level DESC;
三）自连接查询员工的名字、上级的名字SELECT e.last_name,m.last_nameFROM t_mysql_employees eJOIN t_mysql_employees mON e.`manager_id`= m.`employee_id`;
查询姓名中包含字符k的员工的名字、上级的名字SELECT e.last_name,m.last_nameFROM t_mysql_employees eJOIN t_mysql_employees mON e.`manager_id`= m.`employee_id`WHERE e.`last_name` LIKE '%k%';
二、外连接/*应用场景：用于查询一个表中有，另一个表没有的记录特点：1、外连接的查询结果为主表中的所有记录如果从表中有和它匹配的，则显示匹配的值如果从表中没有和它匹配的，则显示null外连接查询结果=内连接结果+主表中有而从表没有的记录2、左外连接，left join左边的是主表  右外连接，right join右边的是主表3、左外和右外交换两个表的顺序，可以实现同样的效果4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的*/引入：查询男朋友 不在男神表的的女神名SELECT * FROM t_mysql_beauty;SELECT * FROM t_mysql_boys;左外连接SELECT b.*,bo.*FROM t_mysql_boys boLEFT OUTER JOIN t_mysql_beauty bON b.`boyfriend_id` = bo.`id`WHERE b.`id` IS NULL;
案例1：查询哪个部门没有员工左外SELECT d.*,e.employee_idFROM t_mysql_departments dLEFT OUTER JOIN t_mysql_employees eON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;
右外
SELECT d.*,e.employee_idFROM t_mysql_employees eRIGHT OUTER JOIN t_mysql_departments dON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;
全外USE girls;SELECT b.*,bo.*FROM t_mysql_beauty bFULL OUTER JOIN t_mysql_boys boON b.`boyfriend_id` = bo.id;
交叉连接SELECT b.*,bo.*FROM t_mysql_beauty bCROSS JOIN boys bo;

#查看版本SELECT VERSION();#查看当前数据库SELECT DATABASE();#查看当前用户SELECT USER();

1.if函数： if else 的效果
SELECT IF(10<5,'大','小');SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注FROM t_mysql_employees;
2.case函数的使用一： switch case 的效果/*java中switch(变量或表达式){case 常量1：语句1;break;...default:语句n;break;}
mysql中case 要判断的字段或表达式when 常量1 then 要显示的值1或语句1;when 常量2 then 要显示的值2或语句2;...else 要显示的值n或语句n;end*/
/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍部门号=40，显示的工资为1.2倍部门号=50，显示的工资为1.3倍其他部门，显示的工资为原工资*/SELECT salary 原始工资,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工资FROM t_mysql_employees;
3.case 函数的使用二：类似于 多重if/*java中：if(条件1){语句1；}else if(条件2){语句2；}...else{语句n;}
mysql中：
casewhen 条件1 then 要显示的值1或语句1when 条件2 then 要显示的值2或语句2。。。else 要显示的值n或语句nend*/案例：查询员工的工资的情况如果工资>20000,显示A级别如果工资>15000,显示B级别如果工资>10000，显示C级别否则，显示D级别
SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM t_mysql_employees;);

/*应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求语法：select 查询列表from 表【join type join 表2on 连接条件where 筛选条件group by 分组字段having 分组后的筛选order by 排序的字段】limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）size 要显示的条目个数特点：①limit语句放在查询语句的最后②公式要显示的页数 page，每页的条目数sizeselect 查询列表from 表limit (page-1)*size,size;size=10page  102 10320*/案例1：查询前五条员工信息SELECT * FROM t_mysql_employees LIMIT 0,5;SELECT * FROM t_mysql_employees LIMIT 5;
案例2：查询第11条——第25条SELECT * FROM t_mysql_employees LIMIT 10,15;
案例3：有奖金的员工信息，并且工资较高的前10名显示出来SELECT  *FROM  t_mysql_employeesWHERE commission_pct IS NOT NULLORDER BY salary DESCLIMIT 10 ;

含义：虚拟表，和普通表一样使用mysql5.1版本出现的新特性，是通过表动态生成的数据比如：舞蹈班和普通班级的对比创建语法的关键字是否实际占用物理空间使用视图create view只是保存了sql逻辑增删改查，只是一般不能增删改表create table保存了数据增删改查
案例：查询姓张的学生名和专业名SELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.`majorid`= m.`id`WHERE s.`stuname` LIKE '张%';CREATE VIEW v1ASSELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.`majorid`= m.`id`;SELECT * FROM v1 WHERE stuname LIKE '张%';

/*语法：create view 视图名as查询语句;*/USE myemployees;1.查询姓名中包含a字符的员工名、部门名和工种信息①创建CREATE VIEW myv1ASSELECT last_name,department_name,job_titleFROM employees eJOIN departments d ON e.department_id = d.department_idJOIN jobs j ON j.job_id = e.job_id;
②使用SELECT * FROM myv1 WHERE last_name LIKE '%a%';
2.查询各部门的平均工资级别①创建视图查看每个部门的平均工资CREATE VIEW myv2ASSELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;
②使用SELECT myv2.`ag`,g.grade_levelFROM myv2JOIN job_grades gON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
3.查询平均工资最低的部门信息SELECT * FROM myv2 ORDER BY ag LIMIT 1;
4.查询平均工资最低的部门名和工资CREATE VIEW myv3ASSELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.agFROM myv3 mJOIN departments dON m.`department_id`=d.`department_id`;

方式一：/*create or replace view 视图名as查询语句;
*/SELECT * FROM myv3CREATE OR REPLACE VIEW myv3ASSELECT AVG(salary),job_idFROM employeesGROUP BY job_id;
方式二：/*语法：alter view 视图名as查询语句;
*/ALTER VIEW myv3ASSELECT * FROM employees;

/*
语法：drop view 视图名,视图名,...;*/
DROP VIEW emp_v1,emp_v2,myv3;

DESC myv3;SHOW CREATE VIEW myv3;

REATE OR REPLACE VIEW myv1ASSELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"FROM employees;CREATE OR REPLACE VIEW myv1ASSELECT last_name,emailFROM employees;SELECT * FROM myv1;SELECT * FROM employees;1.插入INSERT INTO myv1 VALUES('张飞','zf@qq.com');2.修改UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';3.删除DELETE FROM myv1 WHERE last_name = '张无忌';具备以下特点的视图不允许更新
①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union allCREATE OR REPLACE VIEW myv1ASSELECT MAX(salary) m,department_idFROM employeesGROUP BY department_id;SELECT * FROM myv1;
更新UPDATE myv1 SET m=9000 WHERE department_id=10;②常量视图CREATE OR REPLACE VIEW myv2ASSELECT 'john' NAME;SELECT * FROM myv2;更新UPDATE myv2 SET NAME='lucy';
③Select中包含子查询CREATE OR REPLACE VIEW myv3ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资FROM departments;
更新SELECT * FROM myv3;UPDATE myv3 SET 最高工资=100000;
④joinCREATE OR REPLACE VIEW myv4ASSELECT last_name,department_nameFROM employees eJOIN departments dON e.department_id = d.department_id;
更新SELECT * FROM myv4;UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';INSERT INTO myv4 VALUES('陈真','xxxx');
⑤from一个不能更新的视图CREATE OR REPLACE VIEW myv5ASSELECT * FROM myv3;
更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
⑥where子句的子查询引用了from子句中的表CREATE OR REPLACE VIEW myv6ASSELECT last_name,email,salaryFROM employeesWHERE employee_id IN(SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL);
更新SELECT * FROM myv6;UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
视图练习一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱CREATE OR REPLACE VIEW emp_v1ASSELECT last_name,salary,emailFROM employeesWHERE phone_number LIKE '011%';
二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息CREATE OR REPLACE VIEW emp_v2ASSELECT MAX(salary) mx_dep,department_idFROM employeesGROUP BY department_idHAVING MAX(salary)>12000;
SELECT d.*,m.mx_depFROM departments dJOIN emp_v2 mON m.department_id = d.`department_id`;