• MySQL之账号管理、建库以及四大引擎

    目录

    一、安装下载

    二、用户管理

    三、建库建表

    四、CURD

    五、视图

    六、四大引擎的区别

    七、案例

    一、安装下载

            关于MySQL的下载及安装博主已经写过了，大家可以看一下 链接：MySQL的安装

    二、用户管理

         

      项目经理 就数据库而言：crud（增删改查），Create（创表），drop（删表）..

      项目组长而言：crud

      开发人员：select（查询）

    上面权限的划分、针对的是生产环境

       对于系统开发而言，三个环境

            线网、生产环境：系统开发完毕、客户使用的环境

            测试环境：公司内部模拟客户现场，塔尖而成的环境

            开发环境：本地电脑

            

            如没有测试环境这一层，直接把本地项目部署到客户使用环境，及有可能会报错

    一、让我们来看一下mysql默认数据库里面的四张表（user，db，tables_priv，columns_priv）

     

    注意：在MySQL安装好之后会有一个Mysql数据库里面的表千万不能删，如果删了就只能重装

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

    现在让我们来创建一个用户（项目经理）

     让我们查询一下用户，我们可以看到用户zs创建好了，但这个zs用户没有任何意义和

    价值

     我们查询一下用户的信息，可以看到我们新建的用户没有密码，没有密码的话也就无法登陆

     所以我们需要给创建的用户设置密码

    我们测试连接，点击连接选中Mysql进行测试，如下可以看到连接成功

     

    连接成功后点击确定，可以看到我们左边的状态栏出现了我们新建的用户zs

     接着我们给zs赋予权限，赋予权限之后，我们在重新连接

     可以看到我们用户zs也有了zy这个数据库的所有权限

     ​​​​​​​​​​​​​​

    我们在创建一个用户ls（项目组长）

    按上述步骤来，赋予权限只有查询、删除

     可以看到我们用户ls一创建成功，而对应的我们在赋予权限的时候只给

    其中一张表赋予了权限所以，只出现对应的那张表 

     在创建一个用户ww（开发人员）

    同样的如上述步骤，在赋予权限的时候，只有赋予查询的权限；

    对应的我们在给用户赋予权限的时候同时可以撤销用户的权限

    查看权限 

    注意：在对某一个库某一张表的权限进行撤回是不能的

    下面是基本的用户sql语句

    1. 0.查询用户
    2. SELECT * from user;
    3. ​
    4. 1.创建用户并设置登录密码
    5. #MySQL5.7
    6. #命令：create user 用户名 identified by '密码';
    7. #注：identified by会将纯文本密码加密作为散列值存储
    8. create user ls identified by '123456';
    9. #MySQL8
    10. #用户名密码创建需要分开
    11. #命令：create user 用户名;
    12. create user ls;
    13. ​
    14. ​
    15. 2.查看用户信息
    16. #MySQL5.7
    17. select host,user,password from user;
    18. #MySQL8
    19. select host,user,authentication_string from user;
    20. ​
    21. 3.删除用户（慎用）
    22. #命令：drop user 用户名;
    23. #drop user ls;
    24. ​
    25. 4.修改用户密码
    26. 4.1 修改密码
    27. #MySQL5.7
    28. #命令：set password for 用户名=password('新密码');
    29. set password for zs=password('123456');
    30. ​
    31. 6.撤销权限(Revoke)
    32. #语法：revoke privileges on databasename.tablename from username@'host';
    33. #啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响
    34. revoke DELETE on db_xiaoli.t_p1_user from zs@'%';
    35. #可以回收GRANT SELECT, UPDATE ON `db_xiaoli`.`t_p1_user` TO `zs`@`%`这条权限语句
    36. revoke all on db_xiaoli.t_p1_user from zs@'%';
    37. #可以回收GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限
    38. revoke all on db_xiaoli.* from zs@'%';
    39. #注：revoke只能回收grants列表中更小的权限；
    40. ​
    41. 设置权限(Grant)和撤销权限(Revoke)的参数说明：
    42. 1) privileges： 用户的操作权限,如SELECT,INSERT,UPDATE,DELETE等，如果要授予所有权限直接使用：all;
    43. 2) databasename：数据库名;
    44. 3) tablename：   表名，如果要授予用户对所有数据库和表的操作权限直接使用：*.*;  
    45. ​
    46. 7.查看用户权限
    47. #命令：show grants for 用户名;
    48. show grants for 'zs'@'%';
    49. ​
    50. ​
    51. user表中host列的值的意义
    52. %             匹配所有主机
    53. localhost     localhost不会被解析成IP地址，直接通过UNIXsocket连接
    54. 127.0.0.1     会通过TCP/IP协议连接，并且只能在本机访问；
    55. ::1           ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
    56. ​
    57. #MySQL8
    58. #ALTER USER 用户 IDENTIFIED WITH mysql_native_password BY '密码';
    59. ALTER USER 'ls'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    60. ​
    61. 4.2 刷新配置
    62. #命令：flush privileges;
    63. ​
    64. #MySQL5.7与MySQL8关于权限操作没有差异性
    65. 5.设置权限(Grant)
    66. #语法：grant privileges on databasename.tablename to username@'host';
    67. #给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 查询权限
    68. grant SELECT on db_xiaoli.t_p1_user to zs@'%';
    69. #给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限
    70. grant UPDATE on db_xiaoli.t_p1_user to zs@'%';
    71. #给 zs用户 赋予 数据库db_xiaoli中所有表 查询权限
    72. grant SELECT on db_xiaoli.* to zs@'%';
    73. #给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限
    74. grant ALL on db_xiaoli.* to zs@'%';

    三、建库建表

    按如下语句创库的话是不能行的，这回导致乱码，我们还要设置编码集

              

     

     

    如上我们可以看到数据库已经创建好了 

    相关sql语句

    1. 1.创建数据库
    2. 语法：create database 数据库名;
    3. 或者
    4. create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;
    5. ​
    6. 注：默认的数据库编码集：utf8（即UTF-8），collate表示排序规则为utf8_general_ci
    7. ​
    8. 2.查看所有数据库
    9. 语法：show databases;
    10. ​
    11. 3.删除数据库(慎用)
    12. 语法：drop database 数据库名;

     字段类型

    1.  
    2. MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
    3. 1）字符类型：char varchar text blob
    4. 2）数值类型：int bigint float decimal
    5. int -> int
    6. bigint -> long
    7. float -> 成绩
    8. decimal -> 货币类型(精度,小数)
    9. ​
    10. 3）日期类型：date time datetime timestamp
    11. ​
    12. date -> yyyy:MM:dd HH:mm:ss
    13. time -> HH:mm:ss
    14. datetime -> yyyy:MM:dd
    15. timestamp（时间戳） -> 长整数

     相关约束

    1.  
    2. ​
    3. 约束分为：六大约束
    4. NOT NULL：非空，用于保证该字段的值不能为空
    5. 比如姓名、学号等
    6. DEFAULT:默认，用于保证该字段有默认值
    7. 比如：默认约束我们可用到性别
    8.  
    9. PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
    10. 比如学号、员工编号等
    11.  
    12. UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
    13. 比如座位号
    14.  
    15. CHECK:检查约束【mysql中不支持】
    16. 比如年龄、性别
    17.  
    18. FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
    19. 在从表添加外键约束，用于引用主表中某列的值
    20. 比如学生表的专业编号，员工表的部门编号，员工表的工种编号
    21.  
    22. ​
    23. 添加约束的时机：
    24. 1.创建表时
    25. 2.修改表时
    26.  
    27.  
    28. 约束的添加分类：
    29. 列级约束：
    30. 六大约束语法上都支持，但外键约束没有效果
    31.  
    32. 表级约束：
    33.  
    34. 除了非空、默认，其他的都支持
    35.  
    36.  
    37. 主键和唯一的大对比：
    38. ​
    39. 保证唯一性 是否允许为空   一个表中可以有多少个   是否允许组合
    40. 主键         不允许            至多有1个          可以，但不推荐
    41. 唯一         允许              可以有多个         可以，但不推荐
    42. 外键：
    43. 1、要求在从表设置外键关系
    44. 2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
    45. 3、主表的关联列必须是一个key（一般是主键或唯一）
    46. 4、插入数据时，先插入主表，再插入从表
    47. 删除数据时，先删除从表，再删除主表
    48. ​
    49. ​
    50. */
    51. ​
    52. CREATE TABLE 表名(
    53. 字段名 字段类型 列级约束,
    54. 字段名 字段类型,
    55. 表级约束
    56. ​
    57. )
    58. CREATE DATABASE students;
    59. #一、创建表时添加约束
    60. ​
    61. #1.添加列级约束
    62. /*
    63. 语法：
    64. ​
    65. 直接在字段名和类型后面追加 约束类型即可。
    66. ​
    67. 只支持：默认、非空、主键、唯一
    68. ​
    69. ​
    70. ​
    71. */
    72. ​
    73. USE students;
    74. DROP TABLE stuinfo;
    75. CREATE TABLE stuinfo(
    76. id INT PRIMARY KEY,#主键
    77. stuName VARCHAR(20) NOT NULL UNIQUE,#非空
    78. gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
    79. seat INT UNIQUE,#唯一
    80. age INT DEFAULT 18,#默认约束
    81. majorId INT REFERENCES major(id)#外键
    82. ​
    83. );
    84. ​
    85. ​
    86. CREATE TABLE major(
    87. id INT PRIMARY KEY,
    88. majorName VARCHAR(20)
    89. );
    90. ​
    91. #查看stuinfo中的所有索引，包括主键、外键、唯一
    92. SHOW INDEX FROM stuinfo;
    93. ​
    94. ​
    95. #2.添加表级约束
    96. /*
    97. ​
    98. 语法：在各个字段的最下面
    99. 【constraint 约束名】 约束类型(字段名)
    100. */
    101. ​
    102. DROP TABLE IF EXISTS stuinfo;
    103. CREATE TABLE stuinfo(
    104. id INT,
    105. stuname VARCHAR(20),
    106. gender CHAR(1),
    107. seat INT,
    108. age INT,
    109. majorid INT,
    110.  
    111. CONSTRAINT pk PRIMARY KEY(id),#主键
    112. CONSTRAINT uq UNIQUE(seat),#唯一键
    113. CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
    114. CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
    115.  
    116. );
    117. ​
    118. ​
    119. ​
    120. ​
    121. ​
    122. SHOW INDEX FROM stuinfo;
    123. ​
    124. ​
    125. ​
    126. #通用的写法：★
    127. ​
    128. CREATE TABLE IF NOT EXISTS stuinfo(
    129. id INT PRIMARY KEY,
    130. stuname VARCHAR(20),
    131. sex CHAR(1),
    132. age INT DEFAULT 18,
    133. seat INT UNIQUE,
    134. majorid INT,
    135. CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    136. ​
    137. );
    138. ​
    139. ​
    140. ​
    141. #二、修改表时添加约束
    142. ​
    143. /*
    144. 1、添加列级约束
    145. alter table 表名 modify column 字段名 字段类型 新约束;
    146. ​
    147. 2、添加表级约束
    148. alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
    149. ​
    150. ​
    151. */
    152. DROP TABLE IF EXISTS stuinfo;
    153. CREATE TABLE stuinfo(
    154. id INT,
    155. stuname VARCHAR(20),
    156. gender CHAR(1),
    157. seat INT,
    158. age INT,
    159. majorid INT
    160. )
    161. DESC stuinfo;
    162. #1.添加非空约束
    163. ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
    164. #2.添加默认约束
    165. ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
    166. #3.添加主键
    167. #①列级约束
    168. ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
    169. #②表级约束
    170. ALTER TABLE stuinfo ADD PRIMARY KEY(id);
    171. ​
    172. #4.添加唯一
    173. ​
    174. #①列级约束
    175. ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
    176. #②表级约束
    177. ALTER TABLE stuinfo ADD UNIQUE(seat);
    178. ​
    179. ​
    180. #5.添加外键
    181. ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
    182. ​
    183. #三、修改表时删除约束
    184. ​
    185. #1.删除非空约束
    186. ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
    187. ​
    188. #2.删除默认约束
    189. ALTER TABLE stuinfo MODIFY COLUMN age INT ;
    190. ​
    191. #3.删除主键
    192. ALTER TABLE stuinfo DROP PRIMARY KEY;
    193. ​
    194. #4.删除唯一
    195. ALTER TABLE stuinfo DROP INDEX seat;
    196. ​
    197. #5.删除外键
    198. ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
    199. ​
    200. SHOW INDEX FROM stuinfo;

    四、CURD

    查询

    ①基础查询 

    1. /*
    2. 语法：
    3. select 查询列表 from 表名;
    4. ​
    5. ​
    6. 类似于：System.out.println(打印东西);
    7. ​
    8. 特点：
    9. ​
    10. 1、查询列表可以是：表中的字段、常量值、表达式、函数
    11. 2、查询的结果是一个虚拟的表格
    12. */
    13. ​
    14. # USE myemployees;
    15. ​
    16. #1.查询表中的单个字段
    17. ​
    18. SELECT hname FROM tb_hobby;
    19. ​
    20. #2.查询表中的多个字段
    21. SELECT hname,hid FROM tb_hobby;
    22. ​
    23. #3.查询表中的所有字段
    24. ​
    25. #方式一：
    26. SELECT
    27.   `employee_id`,
    28.   `first_name`,
    29.   `last_name`,
    30.   `phone_number`,
    31.   `last_name`,
    32.   `job_id`,
    33.   `phone_number`,
    34.   `job_id`,
    35.   `salary`,
    36.   `commission_pct`,
    37.   `manager_id`,
    38.   `department_id`,
    39.   `hiredate`
    40. FROM
    41.   t_mysql_employees ;
    42.  
    43. #方式二：  
    44. SELECT * FROM t_mysql_employees;
    45.  
    46. #4.查询常量值
    47. SELECT 100;
    48. SELECT 'john';
    49.  
    50. #5.查询表达式
    51. SELECT 100%98;
    52.  
    53. #6.查询函数
    54.  
    55. SELECT VERSION();
    56.  
    57.  
    58. #7.起别名
    59. /*
    60. ①便于理解
    61. ②如果要查询的字段有重名的情况，使用别名可以区分开来
    63. */
    64. #方式一：使用as
    65. SELECT 100%98 AS 结果;
    66. SELECT last_name AS 姓,first_name AS 名 FROM t_mysql_employees;
    67. ​
    68. #方式二：使用空格
    69. SELECT last_name 姓,first_name 名 FROM t_mysql_employees;
    70. ​
    71. ​
    72. #案例：查询salary，显示结果为 out put
    73. SELECT salary AS "out put" FROM t_mysql_employees;
    74. ​
    75. ​
    76. #8.去重
    77. ​
    78. ​
    79. #案例：查询员工表中涉及到的所有的部门编号
    80. SELECT DISTINCT department_id FROM t_mysql_employees;
    81. ​
    82. ​
    83. #9.+号的作用
    84. ​
    85. /*
    86. ​
    87. java中的+号：
    88. ①运算符，两个操作数都为数值型
    89. ②连接符，只要有一个操作数为字符串
    90. ​
    91. mysql中的+号：
    92. 仅仅只有一个功能：运算符
    93. ​
    94. select 100+90; 两个操作数都为数值型，则做加法运算
    95. select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
    96. 如果转换成功，则继续做加法运算
    97. select 'john'+90;如果转换失败，则将字符型数值转换成0
    98. ​
    99. select null+10; 只要其中一方为null，则结果肯定为null
    100. ​
    101. */
    102. ​
    103. #案例：查询员工名和姓连接成一个字段，并显示为 姓名
    104. ​
    105. ​
    106. SELECT CONCAT('a','b','c') AS 结果;
    107. ​
    108. SELECT
    109. CONCAT(last_name,first_name) AS 姓名
    110. FROM
    111. t_mysql_employees;
    112. ​

    ②过滤和排序数据

     

    1. 1、过滤
    2. 2、在查询中过滤行
    3. 3、where子句
    4. 4、比较运算
    5. 5、between
    6. 6、in
    7. 7、like
    8. 8、null
    9. 9、逻辑运算

    案例：

    1. /*
    2. ​
    3. 语法：
    4. select
    5. 查询列表
    6. from
    7. 表名
    8. where
    9. 筛选条件;
    10. ​
    11. 分类：
    12. 一、按条件表达式筛选
    13.  
    14. 简单条件运算符：> < = != <> >= <=
    15.  
    16. 二、按逻辑表达式筛选
    17. 逻辑运算符：
    18. 作用：用于连接条件表达式
    19. && || !
    20. and or not
    21.  
    22. &&和and：两个条件都为true，结果为true，反之为false
    23. ||或or： 只要有一个条件为true，结果为true，反之为false
    24. !或not： 如果连接的条件本身为false，结果为true，反之为false
    25.  
    26. 三、模糊查询
    27. like
    28. between and
    29. in
    30. is null
    31.  
    32. */
    33. #一、按条件表达式筛选
    34. ​
    35. #案例1：查询工资>12000的员工信息
    36. ​
    37. SELECT
    38. *
    39. FROM
    40. t_mysql_employees
    41. WHERE
    42. salary>12000;
    43.  
    44.  
    45. #案例2：查询部门编号不等于90号的员工名和部门编号
    46. SELECT
    47. last_name,
    48. department_id
    49. FROM
    50. t_mysql_employees
    51. WHERE
    52. department_id<>90;
    53. ​
    54. ​
    55. #二、按逻辑表达式筛选
    56. ​
    57. #案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
    58. SELECT
    59. last_name,
    60. salary,
    61. commission_pct
    62. FROM
    63. t_mysql_employees
    64. WHERE
    65. salary>=10000 AND salary<=20000;
    66. #案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
    67. SELECT
    68. *
    69. FROM
    70. t_mysql_employees
    71. WHERE
    72. NOT(department_id>=90 AND department_id<=110) OR salary>15000;
    73. #三、模糊查询
    74. /*
    75. like
    76. ​
    77.  
    78.  
    79. between and
    80. in
    81. is null|is not null
    82. ​
    83. */
    84. #1.like
    85. /*
    86. 特点：
    87. ①一般和通配符搭配使用
    88. 通配符：
    89. % 任意多个字符,包含0个字符
    90. _ 任意单个字符
    91. *、
    92. ​
    93. #案例1：查询员工名中包含字符a的员工信息
    94. ​
    95. select
    96. *
    97. from
    98. employees
    99. where
    100. last_name like '%a%';#abc
    101. #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
    102. select
    103. last_name,
    104. salary
    105. FROM
    106. t_mysql_employees
    107. WHERE
    108. last_name LIKE '__n_l%';
    109. ​
    110. ​
    111. ​
    112. #案例3：查询员工名中第二个字符为_的员工名
    113. ​
    114. SELECT
    115. last_name
    116. FROM
    117. t_mysql_employees
    118. WHERE
    119. last_name LIKE '_$_%' ESCAPE '$';
    120. #2.between and
    121. /*
    122. ①使用between and 可以提高语句的简洁度
    123. ②包含临界值
    124. ③两个临界值不要调换顺序
    125. ​
    126. */
    127. ​
    128. ​
    129. #案例1：查询员工编号在100到120之间的员工信息
    130. ​
    131. SELECT
    132. *
    133. FROM
    134. t_mysql_employees
    135. WHERE
    136. employee_id <= 120 AND employee_id>=100;
    137. #----------------------
    138. SELECT
    139. *
    140. FROM
    141. t_mysql_employees
    142. WHERE
    143. employee_id BETWEEN 100 AND 120;
    144. ​
    145. ​
    146. #3.in
    147. /*
    148. 含义：判断某字段的值是否属于in列表中的某一项
    149. 特点：
    150. ①使用in提高语句简洁度
    151. ②in列表的值类型必须一致或兼容
    152. ③in列表中不支持通配符
    153.  
    154. ​
    155. */
    156. #案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
    157. ​
    158. SELECT
    159. last_name,
    160. job_id
    161. FROM
    162. t_mysql_employees
    163. WHERE
    164. job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
    165. ​
    166. ​
    167. #------------------
    168. ​
    169. SELECT
    170. last_name,
    171. job_id
    172. FROM
    173. t_mysql_employees
    174. WHERE
    175. job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
    176. ​
    177. #4、is null
    178. /*
    179. =或<>不能用于判断null值
    180. is null或is not null 可以判断null值
    181. ​
    182. ​
    183. ​
    184. ​
    185. */
    186. ​
    187. #案例1：查询没有奖金的员工名和奖金率
    188. SELECT
    189. last_name,
    190. commission_pct
    191. FROM
    192. t_mysql_employees
    193. WHERE
    194. commission_pct IS NULL;
    195. ​
    196. ​
    197. #案例1：查询有奖金的员工名和奖金率
    198. SELECT
    199. last_name,
    200. commission_pct
    201. FROM
    202. t_mysql_employees
    203. WHERE
    204. commission_pct IS NOT NULL;
    205. ​
    206. #----------以下为×
    207. SELECT
    208. last_name,
    209. commission_pct
    210. FROM
    211. t_mysql_employees
    212. ​
    213. WHERE
    214. salary IS 12000;
    215.  
    216.  
    217. #安全等于 <=>
    218. ​
    219. ​
    220. #案例1：查询没有奖金的员工名和奖金率
    221. SELECT
    222. last_name,
    223. commission_pct
    224. FROM
    225. t_mysql_employees
    226. WHERE
    227. commission_pct <=>NULL;
    228.  
    229.  
    230. #案例2：查询工资为12000的员工信息
    231. SELECT
    232. last_name,
    233. salary
    234. FROM
    235. t_mysql_employees
    236. ​
    237. WHERE
    238. salary <=> 12000;
    239.  
    240. ​
    241. #is null pk <=>
    242. ​
    243. IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
    244. <=>   :既可以判断NULL值，又可以判断普通的数值，可读性较低

     order by查询语句 

    语法：

    1. 语法：
    2. select 查询列表
    3. from 表名
    4. 【where 筛选条件】
    5. order by 排序的字段或表达式;
    6. ​
    7. ​
    8. 特点：
    9. 1、asc代表的是升序，可以省略
    10. desc代表的是降序
    11. ​
    12. 2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
    13. ​
    14. 3、order by子句在查询语句的最后面，除了limit子句
    15. ​
    16. */
    17. ​
    18. #1、按单个字段排序
    19. SELECT * FROM t_mysql_employees ORDER BY salary DESC;
    20. ​
    21. #2、添加筛选条件再排序
    22. ​
    23. #案例：查询部门编号>=90的员工信息，并按员工编号降序
    24. ​
    25. SELECT *
    26. FROM t_mysql_employees
    27. WHERE department_id>=90
    28. ORDER BY employee_id DESC;
    29. ​
    30. ​
    31. #3、按表达式排序
    32. #案例：查询员工信息 按年薪降序
    33. ​
    34. ​
    35. SELECT *,salary*12*(1+IFNULL(commission_pct,0))
    36. FROM t_mysql_employees
    37. ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
    38. ​
    39. ​
    40. #4、按别名排序
    41. #案例：查询员工信息 按年薪升序
    42. ​
    43. SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    44. FROM t_mysql_employees
    45. ORDER BY 年薪 ASC;
    46. ​
    47. #5、按函数排序
    48. #案例：查询员工名，并且按名字的长度降序
    49. ​
    50. SELECT LENGTH(last_name),last_name
    51. FROM t_mysql_employees
    52. ORDER BY LENGTH(last_name) DESC;
    53. ​
    54. #6、按多个字段排序
    55. ​
    56. #案例：查询员工信息，要求先按工资降序，再按employee_id升序
    57. SELECT *
    58. FROM t_mysql_employees
    59. ORDER BY salary DESC,employee_id ASC;
    60. ​

    排序案例

    1. 1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
    2. ​
    3. SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    4. FROM t_mysql_employees
    5. ORDER BY 年薪 DESC,last_name ASC;
    6. ​
    7. ​
    8. #2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
    9. SELECT last_name,salary
    10. FROM t_mysql_employees
    11. ​
    12. WHERE salary NOT BETWEEN 8000 AND 17000
    13. ORDER BY salary DESC;
    14. ​
    15. #3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
    16. ​
    17. SELECT *,LENGTH(email)
    18. FROM t_mysql_employees
    19. WHERE email LIKE '%e%'
    20. ORDER BY LENGTH(email) DESC,department_id ASC;

    分组查询

    功能：用作统计使用，又称为聚合函数或统计函数或组函数
    ​​​​​​​

    1.  
    2. ​
    3. 分类：
    4. sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
    5. ​
    6. 特点：
    7. 1、sum、avg一般用于处理数值型
    8.   max、min、count可以处理任何类型
    9. 2、以上分组函数都忽略null值
    10. ​
    11. 3、可以和distinct搭配实现去重的运算
    12. ​
    13. 4、count函数的单独介绍
    14. 一般使用count(*)用作统计行数
    15. ​
    16. 5、和分组函数一同查询的字段要求是group by后的字段
    17. ​
    18. */
    19. ​
    20. ​
    21. #1、简单 的使用
    22. SELECT SUM(salary) FROM t_mysql_employees;
    23. SELECT AVG(salary) FROM t_mysql_employees;
    24. SELECT MIN(salary) FROM t_mysql_employees;
    25. SELECT MAX(salary) FROM t_mysql_employees;
    26. SELECT COUNT(salary) FROM t_mysql_employees;
    27. ​
    28. ​
    29. SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
    30. FROM t_mysql_employees;
    31. ​
    32. SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
    33. FROM t_mysql_employees;
    34. ​
    35. #2、参数支持哪些类型
    36. ​
    37. SELECT SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;
    38. SELECT SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;
    39. ​
    40. SELECT MAX(last_name),MIN(last_name) FROM t_mysql_employees;
    41. ​
    42. SELECT MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;
    43. ​
    44. SELECT COUNT(commission_pct) FROM t_mysql_employees;
    45. SELECT COUNT(last_name) FROM t_mysql_employees;
    46. ​
    47. #3、是否忽略null
    48. ​
    49. SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM t_mysql_employees;
    50. ​
    51. SELECT MAX(commission_pct) ,MIN(commission_pct) FROM t_mysql_employees;
    52. ​
    53. SELECT COUNT(commission_pct) FROM t_mysql_employees;
    54. SELECT commission_pct FROM t_mysql_employees;
    55. ​
    56. ​
    57. #4、和distinct搭配
    58. ​
    59. SELECT SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;
    60. ​
    61. SELECT COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;
    62. ​
    63. ​
    64. ​
    65. #5、count函数的详细介绍
    66. ​
    67. SELECT COUNT(salary) FROM t_mysql_employees;
    68. ​
    69. SELECT COUNT(*) FROM t_mysql_employees;
    70. ​
    71. SELECT COUNT(1) FROM t_mysql_employees;
    72. ​
    73. 效率：
    74. MYISAM存储引擎下 ，COUNT(*)的效率高
    75. INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
    76. ​
    77. ​
    78. #6、和分组函数一同查询的字段有限制,employee_id是最小的那个
    79. ​
    80. SELECT AVG(salary),employee_id FROM t_mysql_employees;
    81. ​
    82. 分组函数练习
    83.  
    84. #1.查询公司员工工资的最大值，最小值，平均值，总和
    85. ​
    86. SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
    87. FROM t_mysql_employees;
    88. #2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
    89. ​
    90. SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
    91. FROM t_mysql_employees;
    92. ​
    93. SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
    94. FROM t_mysql_employees;
    95. ​
    96. SELECT DATEDIFF('1995-2-7','1995-2-6');
    97. ​
    98. ​
    99. #3.查询部门编号为90的员工个数
    100. ​
    101. SELECT COUNT(*) FROM t_mysql_employees WHERE department_id = 90;

     

    五、视图

    1. 含义：虚拟表，和普通表一样使用
    2.  
    3. 比如：舞蹈班和普通班级的对比
    4. 创建语法的关键字是否实际占用物理空间使用
    5. ​
    6. 视图create view只是保存了sql逻辑增删改查，只是一般不能增删改
    7. ​
    8. 表create table保存了数据增删改查
    9. ​
    10. #案例：查询姓张的学生名和专业名
    11. SELECT stuname,majorname
    12. FROM stuinfo s
    13. INNER JOIN major m ON s.`majorid`= m.`id`
    14. WHERE s.`stuname` LIKE '张%';
    15. ​
    16. CREATE VIEW v1
    17. AS
    18. SELECT stuname,majorname
    19. FROM stuinfo s
    20. INNER JOIN major m ON s.`majorid`= m.`id`;
    21. ​
    22. SELECT * FROM v1 WHERE stuname LIKE '张%';
    23. ​
    24. ​
    25. #一、创建视图
    26. /*
    27. 语法：
    28. create view 视图名
    29. as
    30. 查询语句;
    31. ​
    32. */
    33. USE myemployees;
    34. ​
    35. #1.查询姓名中包含a字符的员工名、部门名和工种信息
    36. #①创建
    37. CREATE VIEW myv1
    38. AS
    39. ​
    40. SELECT last_name,department_name,job_title
    41. FROM employees e
    42. JOIN departments d ON e.department_id = d.department_id
    43. JOIN jobs j ON j.job_id = e.job_id;
    44. ​
    45. ​
    46. #②使用
    47. SELECT * FROM myv1 WHERE last_name LIKE '%a%';
    48. ​
    49. ​
    50. ​
    51. ​
    52. ​
    53. ​
    54. #2.查询各部门的平均工资级别
    55. ​
    56. #①创建视图查看每个部门的平均工资
    57. CREATE VIEW myv2
    58. AS
    59. SELECT AVG(salary) ag,department_id
    60. FROM employees
    61. GROUP BY department_id;
    62. ​
    63. #②使用
    64. SELECT myv2.`ag`,g.grade_level
    65. FROM myv2
    66. JOIN job_grades g
    67. ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
    68. ​
    69. ​
    70. ​
    71. #3.查询平均工资最低的部门信息
    72. ​
    73. SELECT * FROM myv2 ORDER BY ag LIMIT 1;
    74. ​
    75. #4.查询平均工资最低的部门名和工资
    76. ​
    77. CREATE VIEW myv3
    78. AS
    79. SELECT * FROM myv2 ORDER BY ag LIMIT 1;
    80. ​
    81. ​
    82. SELECT d.*,m.ag
    83. FROM myv3 m
    84. JOIN departments d
    85. ON m.`department_id`=d.`department_id`;
    86. ​
    87. ​
    88. ​
    89. ​
    90. #二、视图的修改
    91. ​
    92. #方式一：
    93. /*
    94. create or replace view 视图名
    95. as
    96. 查询语句;
    97. ​
    98. */
    99. SELECT * FROM myv3
    100. ​
    101. CREATE OR REPLACE VIEW myv3
    102. AS
    103. SELECT AVG(salary),job_id
    104. FROM employees
    105. GROUP BY job_id;
    106. ​
    107. #方式二：
    108. /*
    109. 语法：
    110. alter view 视图名
    111. as
    112. 查询语句;
    113. ​
    114. */
    115. ALTER VIEW myv3
    116. AS
    117. SELECT * FROM employees;
    118. ​
    119. #三、删除视图
    120. ​
    121. /*
    122. ​
    123. 语法：drop view 视图名,视图名,...;
    124. */
    125. ​
    126. DROP VIEW emp_v1,emp_v2,myv3;
    127. ​
    128. ​
    129. #四、查看视图
    130. ​
    131. DESC myv3;
    132. ​
    133. SHOW CREATE VIEW myv3;
    134. ​
    135. ​
    136. #五、视图的更新
    137. ​
    138. CREATE OR REPLACE VIEW myv1
    139. AS
    140. SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
    141. FROM employees;
    142. ​
    143. CREATE OR REPLACE VIEW myv1
    144. AS
    145. SELECT last_name,email
    146. FROM employees;
    147. ​
    148. ​
    149. SELECT * FROM myv1;
    150. SELECT * FROM employees;
    151. #1.插入
    152. ​
    153. INSERT INTO myv1 VALUES('张飞','zf@qq.com');
    154. ​
    155. #2.修改
    156. UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
    157. ​
    158. #3.删除
    159. DELETE FROM myv1 WHERE last_name = '张无忌';
    160. ​
    161. #具备以下特点的视图不允许更新
    162. ​
    163. ​
    164. #①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
    165. ​
    166. CREATE OR REPLACE VIEW myv1
    167. AS
    168. SELECT MAX(salary) m,department_id
    169. FROM employees
    170. GROUP BY department_id;
    171. ​
    172. SELECT * FROM myv1;
    173. ​
    174. #更新
    175. UPDATE myv1 SET m=9000 WHERE department_id=10;
    176. ​
    177. #②常量视图
    178. CREATE OR REPLACE VIEW myv2
    179. AS
    180. ​
    181. SELECT 'john' NAME;
    182. ​
    183. SELECT * FROM myv2;
    184. ​
    185. #更新
    186. UPDATE myv2 SET NAME='lucy';
    187. ​
    188. ​
    189. ​
    190. ​
    191. ​
    192. #③Select中包含子查询
    193. ​
    194. CREATE OR REPLACE VIEW myv3
    195. AS
    196. ​
    197. SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
    198. FROM departments;
    199. ​
    200. #更新
    201. SELECT * FROM myv3;
    202. UPDATE myv3 SET 最高工资=100000;
    203. ​
    204. ​
    205. #④join
    206. CREATE OR REPLACE VIEW myv4
    207. AS
    208. ​
    209. SELECT last_name,department_name
    210. FROM employees e
    211. JOIN departments d
    212. ON e.department_id = d.department_id;
    213. ​
    214. #更新
    215. ​
    216. SELECT * FROM myv4;
    217. UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
    218. INSERT INTO myv4 VALUES('陈真','xxxx');
    219. ​
    220. ​
    221. ​
    222. #⑤from一个不能更新的视图
    223. CREATE OR REPLACE VIEW myv5
    224. AS
    225. ​
    226. SELECT * FROM myv3;
    227. ​
    228. #更新
    229. ​
    230. SELECT * FROM myv5;
    231. ​
    232. UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
    233. ​
    234. ​
    235. ​
    236. #⑥where子句的子查询引用了from子句中的表
    237. ​
    238. CREATE OR REPLACE VIEW myv6
    239. AS
    240. ​
    241. SELECT last_name,email,salary
    242. FROM employees
    243. WHERE employee_id IN(
    244. SELECT manager_id
    245. FROM employees
    246. WHERE manager_id IS NOT NULL
    247. );
    248. ​
    249. #更新
    250. SELECT * FROM myv6;
    251. UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

    案例：

    1. 一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
    2. CREATE OR REPLACE VIEW emp_v1
    3. AS
    4. SELECT last_name,salary,email
    5. FROM employees
    6. WHERE phone_number LIKE '011%';
    7. ​
    8. 二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
    9. CREATE OR REPLACE VIEW emp_v2
    10. AS
    11. SELECT MAX(salary) mx_dep,department_id
    12. FROM employees
    13. GROUP BY department_id
    14. HAVING MAX(salary)>12000;
    15. ​
    16. ​
    17. SELECT d.*,m.mx_dep
    18. FROM departments d
    19. JOIN emp_v2 m
    20. ON m.department_id = d.`department_id`;

    六、四大引擎的区别

    MYISAM引擎、Menory引擎、InnoDB引擎、Archive引擎

    存储引擎的选中

    不同的储存引擎都有各自的特点，以适应不同的需求,如下图：

     

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

    七、案例

    bd_user表 

     bd_shop表

    一、一月每笔消费均大于20元的用户的总消费额

    select sum(order_amt) from  bd_user where order_amt > 20.0 and order_time <='2018-01-31' GROUP BY user_id

     

    二、1月只吃了麻辣烫和汉堡的人数

    ​​​​​​​
    select count(*) from (
            select count(*) from (
                    select * from(
                            select * from bd_user where order_time like '%-01-%'  
                     ) c where order_category ='麻辣烫' or order_category ='汉堡'
            ) d GROUP BY user_id HAVING count(*)=2
    ) e 

     

    三、计算每个BD_TEAM的BD对应门店的销售额

    select sum(b.order_amt) from bd_shop a ,bd_user b where a.shop_id=b.shop_id GROUP BY a.BD_team

     

     

  • 相关阅读:
    微机原理——汇编指令（上部）
    【Go语言】项目实战：客户信息管理系统（需求分析、项目设计、功能实现）
    PO模式在selenium自动化测试框架有什么好处
    Golang-01Golang开发环境配置
    Spring Security 6.x 系列【65】认证篇之 LDAP 协议
    瞬态抑制二极管TVS的核心参数？|深圳比创达电子EMC（下）
    【C语言】青蛙跳台阶 —— 详解
    Android 13.0 系统settings详情页卸载修改为停止，禁止卸载app功能实现
    阿里云新加坡主机服务器选择
    01-10-Hadoop-HA-概述
  • 原文地址：https://blog.csdn.net/qq_65345936/article/details/125551739