mysql学习笔记（三）

最新推荐文章于 2024-08-12 11:47:10 发布
最新推荐文章于 2024-08-12 11:47:10 发布
阅读量319 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53737524/article/details/123805784
版权 
1.
#去除重复记录
#distinct关键字，只能放在最前面，否则会报错
select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
select ename,distinct job from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1
#可以放在最前面相当于是联合起来进行筛选
select distinct ename,job from emp;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
14 rows in set (0.01 sec)
#统计一下工作岗位的数量
select count(distinct job) from emp;
2.
#连接查询

MySQL连接查询

#多个表联合起来查询就是连接查询
#根据年代分类分为SQL92 SQL99
#根据表的连接方式进行分类：内连接和外连接
#内连接分为：等值连接，非等值连接，自连接
#外连接分为：左外连接（左连接），右外连接（右连接）
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.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-04-19 | 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-05-23 | 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.23 sec)
mysql> select ename,dname from emp,dept;
#笛卡尔积现象
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | OPERATIONS |
| SMITH  | SALES      |
| SMITH  | RESEARCH   |
| SMITH  | ACCOUNTING |
| ALLEN  | OPERATIONS |
| ALLEN  | SALES      |
| ALLEN  | RESEARCH   |
| ALLEN  | ACCOUNTING |
.......................
.......................
+--------+------------+
56 rows in set (0.11 sec)

mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.12 sec)
#匹配次数并没有减少
#表要起别名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
#内连接之等值连接
sql92的语法容易造成在where 出现杂糅 很乱
所以采用sql99的语法 select ... from ... (两个表中间用join隔开)
 on...(表示的是条件)之后再用where来进行过滤
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
select e.ename,d.dname from emp e (inner) join dept d on e.deptno=d.deptno;
#带着inner可读性更好一些
#内连接之非等值连接
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.02 sec)
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.11 sec)
#内连接就是要把一张表看成两张表
select a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr=b.empno;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| SMITH     | FORD      |
| ALLEN     | BLAKE     |
| WARD      | BLAKE     |
| JONES     | KING      |
| MARTIN    | BLAKE     |
| BLAKE     | KING      |
| CLARK     | KING      |
| SCOTT     | JONES     |
| TURNER    | BLAKE     |
| ADAMS     | SCOTT     |
| JAMES     | BLAKE     |
| FORD      | JONES     |
| MILLER    | CLARK     |
+-----------+-----------+
13 rows in set (0.01 sec)

外连接

#右外连接
#外连接就是表之间存在着主次关系
select e.ename,d.ename from emp e right join dept d on e.deptno=d.deptno;
#right 表示要顺着右边的表把右边的表全都展示出来
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.22 sec)
#left和right在join的前面，带有left的是左连接
#在left/right和join之间有一个outer默认是可以省略的
#外连接的查询结果条数一定是大于内连接的查询结果条数这是正确的
mysql> select a.ename as '员工名',b.ename as '领导名' from emp a  left join emp b on a.mgr=b.empno;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| SMITH     | FORD      |
| ALLEN     | BLAKE     |
| WARD      | BLAKE     |
| JONES     | KING      |
| MARTIN    | BLAKE     |
| BLAKE     | KING      |
| CLARK     | KING      |
| SCOTT     | JONES     |
| KING      | NULL      |
| TURNER    | BLAKE     |
| ADAMS     | SCOTT     |
| JAMES     | BLAKE     |
| FORD      | JONES     |
| MILLER    | CLARK     |
+-----------+-----------+
14 rows in set (0.04 sec)

多表连接

#顺序是这样的
select ... from a join b on ...(a和b连接条件)  join c on ...(a和c连接条件)
#一条查询语句当中可以含有多个内连接和外连接
mysql> select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+------------+-------+
| ename  | sal     | dname      | grade |
+--------+---------+------------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 |
| ALLEN  | 1600.00 | SALES      |     3 |
| WARD   | 1250.00 | SALES      |     2 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| MARTIN | 1250.00 | SALES      |     2 |
| BLAKE  | 2850.00 | SALES      |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+-------+
14 rows in set (0.01 sec)
mysql> select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr=l.deptno;
+--------+---------+------------+-------+
| ename  | sal     | dname      | grade |
+--------+---------+------------+-------+
| JAMES  |  950.00 | SALES      |     1 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| SMITH  |  800.00 | RESEARCH   |     1 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
| MARTIN | 1250.00 | SALES      |     2 |
| WARD   | 1250.00 | SALES      |     2 |
| TURNER | 1500.00 | SALES      |     3 |
| ALLEN  | 1600.00 | SALES      |     3 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| BLAKE  | 2850.00 | SALES      |     4 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
+--------+---------+------------+-------+
14 rows in set (0.01 sec)

子查询

where下面的子查询

mysql> select job,sal from emp where sal>(select min(sal) from emp);
+-----------+---------+
| job       | sal     |
+-----------+---------+
| SALESMAN  | 1600.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2975.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2850.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1500.00 |
| CLERK     | 1100.00 |
| CLERK     |  950.00 |
| ANALYST   | 3000.00 |
| CLERK     | 1300.00 |
+-----------+---------+
13 rows in set (0.01 sec)

from下面的子查询

#from下面的子查询
#带着小括号容易当成函数所以需要去起别名
mysql> select a.job,a.avgsal,b.grade from (select job,avg(sal) as avgsal from emp group by job) a join salgrade b on a.avgsal between b.losal and b.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.11 sec)
#select后面嵌套的select是不能返回两条语句的否则就会报错 是按着一条一条去查询的
mysql> select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
14 rows in set (0.11 sec)

union合并查询结果集

mysql> select ename,job from emp where job in ('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.10 sec)
mysql> select ename,job from emp where job = 'manager'or'salesman';
+-------+---------+
| ename | job     |
+-------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+
3 rows in set, 1 warning (0.00 sec)
#这里少了一个job=
select ename,job from emp where job='manager' or job='salesman';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
#是用union的效率要高一些
#a连接b再去连接c运算次数是n的三次方
#但是利用union关键字之后就会变成2*n的二次方
select ename,job from emp where job='MANAGER'
union
select ename,job from emp where job='SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.11 sec)
#union在使用的时候要注意列数是一样的
select job from emp where job='MANAGER'
union
select ename,job from emp where job='SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns
#MYSQL中的要求不会那么多，不要求列和列的数据类型一致 但是Oracle会报错

limit用法

#作用是不会将数据都取出来，而是以分页的形式来取数据
1
#按照薪资的降序排，去除前五条记录
select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.11 sec)
select * from emp order by sal desc limit 3;
mysql> select * from emp order by sal desc limit 3;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.11 sec)

2
limit后面是跟着两个参数的 limit 默认下标 length
limit 是在order by 后执行的 并且后面的数字用逗号（，）隔开
mysql> select * from emp order by sal desc limit 1,3;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

3
取出薪资排名3—5名的员工姓名，薪水
select ename,job,sal from emp order by sal desc limit 2,3;
mysql> select ename,job,sal from emp order by sal desc limit 2,3;
+-------+---------+---------+
| ename | job     | sal     |
+-------+---------+---------+
| FORD  | ANALYST | 3000.00 |
| JONES | MANAGER | 2975.00 |
| BLAKE | MANAGER | 2850.00 |
+-------+---------+---------+
3 rows in set (0.01 sec)

分页通用

#会返回一个页码pageSize pageNumber
Public static void main（String[] args）{
    int pageSize;
    int pageNo;
    int startIndex = (pageNo-1)*pageSize;
    System.out.println("select ename,job from emp order by sal limit startIndex,pageSize")}

dql顺序

select ... from ... where ... group by ... having ... order by ... limit ...
顺序:1.from
    2.where
    3.group by having
    4.select
    5.order by limit

王墨鱼
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习笔记（三）
Bee_Darker的博客
04-03 408
一、设置索引 索引是一种将数据库中单列或者多列的值进行排序的结构。创建索引的目的是为了优化数据库的查询速度。 1.1 创建索引 1.1.2 在建立数据表时创建索引 在建立数据表时创建索引的基本语法结构如下： CREATE TABLE table_name( 属性名 数据类型[约束条件], 属性名 数据类型[约束条件], ``` 属性名 数据类型 [UNIQU...
Mysql学习笔记（全）
热门推荐
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
MySQL学习笔记8——游标
qq_44778824的博客
04-24 1764
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
尚硅谷MySQL学习笔记
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySql学习笔记（详细）
05-18
### MySQL学习笔记（详细） #### 一、数据库操作 MySQL是一种关系型数据库管理系统，通过一系列命令来进行数据库的管理和数据的处理。以下是一些常见的数据库操作命令： - **连接MySQL服务器**： - `mysql -u ...
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL进阶篇学习笔记
05-14
黑马MySQL课程总结的学习笔记
MySQL学习笔记小结
09-10
本篇笔记将总结MySQL的学习要点，包括慢查询日志、日志分类、SQL架构以及其内部组件的功能。 首先，慢查询日志是MySQL中用于追踪执行时间过长的SQL语句的重要工具。默认情况下，MySQL不会记录所有查询，但可以通过...
简述MYSQL聚簇索引、二级索引、索引下推
weixin_52466601的博客
08-09 779
它是在数据查询的过程中，将通常由用户查询表达式处理的计算任务下推到数据存储层，比如从磁盘或内存的索引结构中直接完成部分计算，而不是等到所有的数据都被加载到内存后再进行处理。除了聚簇(主键)索引之外的所有索引都成为二级索引也就是非主键索引，二级索引的叶子节点内容是主键的值，主键长度越小，二级索引的叶子节点就越小，占用的空间也就越小；聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，就是按照每张表的主键构造一颗B+树，同时叶子节点存放的就是整张表的行记录数据，所以主键索引就默认使用到了聚簇索引；
亚马逊 Linux mysql5.7 安装纪录
tian2kong的专栏
08-11 360
将服务文件拷贝到init.d下，并重命名为mysql。查看状态：service mysqld status。重启：service mysqld restart。启动：service mysqld start。停止：service mysqld stop。#到mysql-5.7.24。#在文件最后面加入下面配置。#保存后刷新环境变量。
Linux下安装Scuba并对MySQL进行漏洞扫描
weixin_57379923的博客
08-09 426
版 权 声 明：本博客所有文章除特别声明外，均采用。文章版权归作者所有，未经允许请勿转载！作 者：PeiyuJue。
深入浅出Mysql 第三期
Pandaer杂货铺
08-08 943
我认为有两种，一个经常被拿来举例的例子就是转账，比如用户A给用户B转账，这个业务至少需要两步，用户A的余额减去一个值，用户B的余额加上一个值，如果用户A的值成功修改后，这个时候数据库崩溃了，那么就出现了数据不一致的情况，即没有符合预期。之所以有隔离级别，其实就是。这个的加锁过程是这样的，针对写操作和读已提交一样，但是针对读操作，是不加锁的读。针对可重复读而言，在每个事务中，在执行SQL语句的时候，就会开始加锁，不过这个锁是读写锁，根据SQL语句的类型加对应的锁，比如是查询操作就加读锁，是写操作就加写锁。
day23(mysql主从脚本与mysql详细语句介绍)
weixin_68368034的博客
08-08 979
只有 select ⼦句和 having ⼦句、order by ⼦句中能使⽤聚合函数，where ⼦句不能使⽤聚合函 数。当使⽤聚合查询以后，不能使⽤where条件，如果要添加条件，就使⽤having。常⽤聚合``函数统计表中数据的⾏数或者统计指定列不为空值的数据⾏个数select count(*或表头名) from 表名；计算指定列的最⼤值，如果指定列是字符串类型(⽂字类型)则使 ⽤字符⾸拼排序select max(表头名) from 表名;
MySQL进阶-MySQL管理
qq_64737356的博客
08-08 316
系统数据库 常用工具 mysql mysqladmin mysqlbinlog mysqlshow mysqldump mysqlimport/source
【数据库】MySql深度分页SQL查询优化
雪的期许
08-08 335
【代码】【数据库】MySql深度分页SQL查询优化。
Java实现MySQL表结构导出
最新发布
promise524的博客
08-12 305
Java实现MySQL表结构导出
MySQL 存储引擎之MyISAM
qq_29751083的博客
08-08 263
这个 SQL 语句创建了一个名为 users 的表，其中 id 是主键并自动递增，name 和 email 分别是名字和电子邮件地址字段，email 字段还设置了唯一性约束。最后 尽管 MyISAM 在某些方面表现优秀，但它已经被标记为不再维护的状态，新的开发项目通常推荐使用 InnoDB 存储引擎，因为它提供了更好的事务支持、行级锁定以及其它高级特性。MyISAM 是 MySQL 中的一个存储引擎，它在 MySQL 的早期版本中非常流行，并且在某些特定的应用场景下仍然有其独特的优势。
MySQL学习笔记：从基础到高级
"一千行+MySQL+学习笔记.pdf" MySQL是一种广泛应用的关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性受到广大开发者的青睐。这份学习笔记涵盖了MySQL的多个关键知识点，包括基本的SQL语句、索引、数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值