MySQL基础之子查询，case when语句，授权，事物，锁。

最新推荐文章于 2023-09-08 09:07:49 发布
最新推荐文章于 2023-09-08 09:07:49 发布
阅读量718 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanglulu12138/article/details/85079941
版权

数据库test

dept
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

emp
+-------+----------+-----------+------+------------+---------+---------+--------+
| empno | ename    | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+----------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH    | CLERK     | 7902 | 1980-12-17 | 5000.00 |    NULL |     20 |
|  7499 | ALLEN    | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD     | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES    | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN   | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE    | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK    | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT    | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING     | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER   | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS    | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES    | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD     | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER   | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  8000 | zhangsan | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     50 |
+-------+----------+-----------+------+------------+---------+---------+--------+

子查询（sub query）

概念：就是把一个查询的结果当作另一个查询的条件或者一张新表，进行查询或者连接。可以多个查询嵌套。

（1）求最高工资的员工信息（把一个查询的结果当作另一个查询的条件）

 分步骤做：

  1.先求出员工中的最高工资5000

  2.把最高工资5000当作条件，查出工资为5000的员工信息

 select max(sal) from emp e;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

 select * from emp where sal = 5000;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 | 5000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

子查询做：

select * from emp where sal = (select max(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 | 5000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

（2）每个部门的最高工资的员工（把一个查询的结果当作一张新表）

  分步骤做：

  1.先求出每个部门最高工资以及其部门编号，把结果当作一个新表a

  (select  max(sal) msal, deptno  from emp group by deptno) a

  2.把新表a与emp表连接，再选择出员工信息。

  select * from emp b inner join a on b.deptno=a.deptno and b.sal = a.msal;

  子查询做：

 select * from emp b inner join
    ->     (select  max(sal) msal, deptno  from emp group by deptno) a
    ->     on b.deptno=a.deptno and b.sal = a.msal;
+-------+----------+-----------+------+------------+---------+------+--------+---------+--------+
| empno | ename    | job       | mgr  | hiredate   | sal     | comm | deptno | msal    | deptno |
+-------+----------+-----------+------+------------+---------+------+--------+---------+--------+
|  7369 | SMITH    | CLERK     | 7902 | 1980-12-17 | 5000.00 | NULL |     20 | 5000.00 |     20 |
|  7698 | BLAKE    | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 | 2850.00 |     30 |
|  7839 | KING     | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 | 5000.00 |     10 |
|  8000 | zhangsan | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     50 | 1300.00 |     50 |
+-------+----------+-----------+------+------------+---------+------+--------+---------+--------+

case when语句

概念：可以配合select工作, 把一列的取值根据不同的条件进行翻译类似于 java 中的if else if。

语句格式：

case
   when 条件1 then 结果1
   when 条件2 then 结果2
   ...
   else 结果n
end

举例：把emp表中的员工工资输出，2000以下显示低工资，2000~3000显示中等，3000以上显示高工资

mysql> select empno, ename, sal,
    -> case
    ->     when sal <= 2000 then '低工资'
    ->     when sal >2000 and sal <=3000  then '中等工资'
    ->     else '高工资'
    -> end 工资级别  from emp;
+-------+----------+---------+----------+
| empno | ename    | sal     | 工资级别 |
+-------+----------+---------+----------+
|  7369 | SMITH    | 5000.00 | 高工资   |
|  7499 | ALLEN    | 1600.00 | 低工资   |
|  7521 | WARD     | 1250.00 | 低工资   |
|  7566 | JONES    | 2975.00 | 中等工资 |
|  7654 | MARTIN   | 1250.00 | 低工资   |
|  7698 | BLAKE    | 2850.00 | 中等工资 |
|  7782 | CLARK    | 2450.00 | 中等工资 |
|  7788 | SCOTT    | 3000.00 | 中等工资 |
|  7839 | KING     | 5000.00 | 高工资   |
|  7844 | TURNER   | 1500.00 | 低工资   |
|  7876 | ADAMS    | 1100.00 | 低工资   |
|  7900 | JAMES    |  950.00 | 低工资   |
|  7902 | FORD     | 3000.00 | 中等工资 |
|  7934 | MILLER   | 1300.00 | 低工资   |
|  8000 | zhangsan | 1300.00 | 低工资   |
+-------+----------+---------+----------+

注意：工资级别不加引号，else可有可无。

授权管理：

有两个关键字：grant  授权       revoke 回收权限

注意：普通用户只能授权自己创建的数据库数据权限，管理员用户（root）可以授权所有数据库权限。

创建一个新用户： create user ‘用户名’ identified by ‘用户密码’

 create user 'wang' identified by 'wang';

登陆之后新用户没有权限访问test库 在root用户下：

用 grant 权限 on 数据库  to  用户名 给新用户某给数据库的某个权限

grant select on test.* to wang

用 revoke权限 on 数据库 from 用户名 收回某给用户的某个权限 

revoke select on test.* from wang;

权限比如：select（选择权限） update（修改权限）.....

事物（transaction）

概念：把一组sql语句当作一个整体，这些sql语句要么全部执行完，要么一个也不执行，执行中过程有一个sql语句失败了，以前的sql语句也要撤销。相当于原子操作。

在MySQL中的事物控制默认是一条语句为一个事物。要让一组sql语句为事物，用关键字begin commit  rollback`来控制事务
begin（表示事物开始）  commit （表示提交sql语句为事物） rollback（表示撤回以上的sql语句）

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp set sal=8888 where empno=7369;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from emp;
+-------+----------+-----------+------+------------+---------+---------+--------+
| empno | ename    | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+----------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH    | CLERK     | 7902 | 1980-12-17 | 8888.00 |    NULL |     20 |
|  7499 | ALLEN    | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD     | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES    | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN   | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE    | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK    | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT    | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING     | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER   | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS    | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES    | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD     | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER   | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  8000 | zhangsan | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     50 |
+-------+----------+-----------+------+------------+---------+---------+--------+
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp set sal=0 where empno=7369;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from emp;
+-------+----------+-----------+------+------------+---------+---------+--------+
| empno | ename    | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+----------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH    | CLERK     | 7902 | 1980-12-17 | 5000.00 |    NULL |     20 |
|  7499 | ALLEN    | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD     | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES    | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN   | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE    | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK    | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT    | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING     | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER   | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS    | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES    | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD     | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER   | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  8000 | zhangsan | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     50 |
+-------+----------+-----------+------+------------+---------+---------+--------+

锁：

分为：InnoDB 行级锁, 只要两个客户端更新的是不同的行，互不干扰 。MyISAM 表锁，是锁住整个表。

在具体的语句中又分为X排他锁，S共享锁

X排他锁：一般加在修改语句上，当以有语句修该数据库中某个数据时加了X排他锁，其他语句不能访问此数据。

S共享锁：一般加在查询语句上，当以有语句查询数据库中某个数据时加了S共享锁，其他语句能查询此数据再加S共享锁，但是不能修改加X排他锁。

事物的隔离：

有不同的隔离级别， 隔离级别越低，并发性越好，但数据的一致性差 ，隔离级别越高，并发性差，但数据的一致性高
由低到高四种：读未提交 < 读提交 < 可重复读(mysql默认) < 序列化读

错误的级别由高到低
脏读（读未提交） ，不可重复读，幻读

脏读：比如一个数据初始值为100，在执行一组sql语句为事物中时，修改了这个数据为50。另一个事物语句想去访问数据，查出这个值为50使用了。此时前一个事物发现错误，rollbcak了，撤回了修改，数据有变成100，但是第二个事物使用的是50。

不可重复读：两个事物，一个查询，另一个做update操作，导致一个事务内多次查询结果不一致。

幻读：两个事物，一个查询，另一个做insert操作，插入了一条数据，第一个事物查询过后未查到特定的数据，第二个事物插入了这条特定的数据，此时第一个事物插入这个特定数据时又插不进去。

事物的四个特性：

原子性 A 多个sql要作为一个整体运行，不可分割
一致性 C 一个事务内结果应当一致 
隔离性 I 
持久性 D 事务一旦提交，事务内的修改就应当永久生效

其他sql总结 :
查看有哪些库:  show databases;
查看库的创建语句： show create database 库名;
使用库：       use 库名;
查看有哪些表： show tables;
查看表结构：   desc 表名;
查看建表语句： show create table 表名;
\G 可以取代; 效果是把表行转列
查看系统变量的值： select @@系统变量名;

select @@transaction_isolation;   // 查看事务隔离级别
select @@port;                    // 查看服务器端口号
select @@character_set_server;   // 查看默认字符集
select @@secure_file_priv;

在创建数据库时用[if not exists] 判断表不存在时才创建， 如果已经存在，不执行任何操作

举例：创建student表时，如果表已经存在，就不会再创建，但是不会报错，如果是用scoure导入一系列sql语句时，后面的sql语句还会执行。

create table if not exists student(
	sid int primary key auto_increment,
	sname varchar(20) not null unique,
	birthday date,
	sex char(1)
) ;

sql语句中创建表时的约束条件：

not null 表示此列取值不能为空
unique   表示此列的取值是唯一的 (一张表可以有多个唯一约束)
primary key 同时起到了约束的效果, 唯一且非空(一张表只能定义一次)
foreign key 外键约束, 某一列的取值来自于另一张表(列必须是主键或唯一的) 

格式：foreign key （列名）references  表名（列名）

注意：外键总建在多的一方。

 

 

 

 

wanglulu12138
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库的case when语句
别人笑我太疯癫，我笑他人看不穿。
01-13 3469
case when语句书写格式有两种 第一种是 CASE gender WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '其他' END 第二种是 CASE WHEN gender=1 THEN '男' WHEN gender=2 THEN '女' ELSE '其他' END 一般情况下数据库都是支持的，只是用的时候可能随着业务不同而难易程度不同 ...
SQL中的 CASE WHEN用法详解
最新发布
Taerge0110的博客
02-23 4201
​CASE WHEN语句提供了一种在 SQL 查询中根据不同条件执行逻辑的灵活方法。它可用于简单的条件检查，也可用于复杂的逻辑操作。使用CASE WHEN可以使查询更具可读性，并且可以减少在应用程序代码中进行逻辑操作的需要。
mysql权限模型子查询 casewhen子查询
Lauuii_的博客
02-08 538
MySQL 记录
MySQL使用CASE WHEN统计SQL语句代替子查询SQL统计，CASE WHEN常用写法，根据不同的条件对数据进行分类、分组和聚合
川子的博客
09-08 3218
MySQL使用CASE WHEN统计SQL语句代替子查询SQL统计，CASE WHEN常用写法，根据不同的条件对数据进行分类、分组和聚合。
sql case when的用法（转）
weixin_43955984的博客
09-18 3646
简单CASE WHEN函数： CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE ‘不及格’ END CASE SCORE WHEN ‘B’ THEN ‘良’ ELSE ‘不及格’ END CASE SCORE WHEN ‘C’ THEN ‘中’ ELSE ‘不及格’ END  等同于，使用CASE WHEN条件表达式函数实现： CASE WHEN SCORE = ‘A’...
MySQL中使用case when 语句实现多条件查询的方法
09-11
今天在一个应用中使用到了一个比较特殊的数据查询要求。需要的朋友可以参考下。
MySQL 的CASE WHEN 语句使用说明
12-15
mysql数据库中CASE WHEN语句。 case when语句，用于计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式： 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔...
MySQL的CASE WHEN语句的几个使用实例
09-10
主要介绍了MySQL的CASE WHEN语句的几个使用实例,需要的朋友可以参考下
case when语句（SQL）多种用法
shahuhubao的博客
07-11 1万+
case具有两种格式。简单case函数和case搜索函数。   --简单case函数 case sex when '1' then '男' when '2' then '女’ else '其他' end --case搜索函数 case when sex = '1' then '男' when sex = '2' then '女' else '其他' end ...
MySQL开窗、CASE WHEN以及CTE的使用和理解
qq_15994257的博客
09-09 1185
MySQL开窗、CASE WHEN以及CTE的使用和理解一、开窗函数什么时候适合用开窗函数:1、用法分类2、over()的使用3、partition by 的使用4、排序函数5、windows frames 自定义窗口6、默认的 window frames7、CTE表达式8、分析函数lead( x [, y] [, z] ) over( )lag(x [, y] [, z] ) over( )first_value( x ) over( )last_value( x ) over( )nth_val
case when的使用方法
热门推荐
聆听。。花开雨落的博客
04-21 5万+
这篇文章在借鉴前人的基础上，进行了验证，感谢前人的分享 一、case when的使用方法   Case具有两种格式。简单Case函数和Case搜索函数。  第一种 格式 : 简单Case函数 : 格式说明     　　　　case 列名 　　　　when   条件值1   then  选项1 　　　　when   条件值2    then  选项2....... 　　　　else &n.
MYSQL5下查询语句CASE WHEN 子查询的使用
菜菜De学习
02-17 1万+
SQL语句如下:根据表中某个字段的值 来进行判断,分别查询B表或C表中的字段值。select supplier.supplier_account,quot.quote_id,quot.master_id,quot.quote_Type,quot.supplier_id ,quot.attach_files_ids,quot.create_time,purchase.purchase_title,purchase.purchase_about,purchase.psort_Id ,(case whe
Mysql系列课程--第五章 高级查询 表连接 子查询 case when
极客—杨振的博客
11-25 2063
一.表连接1.内连接1.1等值内连接 /李文辉是哪个班的/select s.s_name,c.c_name from student s inner join class c on s.c_no = c.c_no where s.s_name='李文辉';（第一种写法） select s.s_name,c.c_name from student s,class c where s.c_no = c
oracle常用语句之case when语句
wanlin77的博客
06-09 1万+
case when语句是oracle常用的语句之一，这里有两种书写方式，一种是简单CASE表达式，使用表达式确定返回值，一种是搜索CASE表达式，使用条件确定返回值简单CASE表达式，使用表达式确定返回值语法：CASE search_expressionWHEN expression1 THEN result1WHEN expression2 THEN result2...WHEN express...
case when嵌套子查询_MySQL复习—（三）子查询
weixin_39680121的博客
11-28 2185
ps:若有不妥，请指教！子查询：select外面嵌套一个select。select * from （ select city from 库.表名）as t 子查询原因：子查询可以直接调用子查询里别名，不用很长的公式。使用子查询了就可以方便简洁。ps:as表中列起别名 //子查询，---as t ---tbale SELECT * FROM( SELECT LEFT(salary,LO...
case when 条件查询的心得
weixin_43939597的博客
07-01 2146
mybatis最常用的应该是 if test的动态查询，但if语句里只能接收外部传参，而当需要把某字段当成条件去查询时，可以使用case when 语句（这个语法之前只在select用过，一般用来将一些枚举项的id转化为对应的汉字意思） ` rpr.count as count, rpr.condition as condition_circle from r_point_rule rp...
mysql case when 子查询
08-22
在MySQL中，可以使用CASE WHEN语句结合子查询来解决一些问题。CASE WHEN语句是MySQL中的控制流语句，类似于其他编程工具中的IF…THEN…的分支判断逻辑。而子查询是将查询出来的结果作为一张表，在这个表上继续作查询...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值