老杜Mysql 基础教程 笔记

最新推荐文章于 2024-07-24 23:35:30 发布
最新推荐文章于 2024-07-24 23:35:30 发布
阅读量810 收藏 9
点赞数 25
文章标签： mysql 笔记 adb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cxy00000/article/details/140288104
版权

Mysql

表 :

​ 行：数据记录

​ 列：字段名，数据类型，约束条件

DQLselect查询语句
DMLinsert update delete表中增删改语句
DDLcreate drop alter表结构增删改
TCLcommit rollback事务控制语句
DCLgrant revoke数据库权限控制

导入数据：source xxx.sql

查看表结构： desc 表名

查看当前使用的数据库名： select database();

查看Mysql版本号： select version();

查看建表语句：show create table 表名;

select 语句执行顺序： from–>where–>group by --> having -->select–>order by–>limit

between…… and…… 数字：闭区间，字符：左闭右开

多个order by 条件时，使用逗号分割，当前边条件一致时按照后边条件排序

mysql> select * from EMP order by SAL desc,ENAME asc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|    99 | nu_nij | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+-------+--------+-----------+------+------------+---------+---------+--------+

分组函数，自动忽略 null

mysql> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+

# 不需要加条件
select count(COMM) from EMP where COMM is not null;

mysql> select count(COMM) from EMP where COMM is not null;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.04 sec)
count计数
sum求和
avg平均值
max最大值
min最小值
mysql> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.10 sec)

有null 参与运算 ，结果一定是null

mysql> select * ,(SAL+COMM)*12 as total from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | total    |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
|    99 | nu_nij | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     NULL |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     NULL |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 22800.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 21000.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     NULL |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 31800.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     NULL |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     NULL |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     NULL |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     NULL |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     NULL |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     NULL |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     NULL |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     NULL |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
15 rows in set (0.00 sec)

空处理函数：ifnull(COMM,0): ifnull(可能为空的字段,当作什么处理)

mysql> select * ,(SAL+ifnull(COMM,0))*12 as total from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | total    |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
|    99 | nu_nij | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     NULL |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  9600.00 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 22800.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 21000.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 35700.00 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 31800.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 34200.00 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 29400.00 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | 36000.00 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | 60000.00 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | 13200.00 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | 11400.00 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | 36000.00 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 15600.00 |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
15 rows in set (0.01 sec)

分组函数不能直接出现在where后(group by 在where后执行)

mysql> select SAL,ENAME from EMP where SAL>avg(SAL);
ERROR 1111 (HY000): Invalid use of group function

mysql> select SAL,ENAME from EMP where SAL>(select avg(SAL) from EMP);
+---------+-------+
| SAL     | ENAME |
+---------+-------+
| 2975.00 | JONES |
| 2850.00 | BLAKE |
| 2450.00 | CLARK |
| 3000.00 | SCOTT |
| 5000.00 | KING  |
| 3000.00 | FORD  |
+---------+-------+
6 rows in set (0.07 sec)

count(*) 和count(comm) 区别：

count(*) ：统计总记录条数

count(comm)：统计comm字段中不为null的记录条数

mysql> select count(*) from EMP;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.05 sec)

mysql> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

group by:按照某个字段或者某些字段进行分组

having:对分组后的数据进行过滤

# 找出每个岗位的最高薪资
mysql> select JOB,max(SAL) from EMP  group by JOB;
+-----------+----------+
| JOB       | max(SAL) |
+-----------+----------+
| NULL      |     NULL |
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
6 rows in set (0.00 sec)

找出工资高于平均工资的用户

mysql> select ENAME,SAL from EMP where SAL > (select avg(SAL) from EMP);
+-------+---------+
| ENAME | SAL     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.06 sec)

group by 出现时 select后只允许出现参加分组的字段和分组函数

计算岗位平均薪资

mysql> select JOB,AVG(SAL) from EMP group by JOB;
+-----------+-------------+
| JOB       | AVG(SAL)    |
+-----------+-------------+
| NULL      |        NULL |
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
6 rows in set (0.01 sec)

找出每个部门不同岗位的最高薪资

mysql> select DEPTNO,JOB,MAX(SAL) from EMP group by DEPTNO,JOB order by DEPTNO;
+--------+-----------+----------+
| DEPTNO | JOB       | MAX(SAL) |
+--------+-----------+----------+
|   NULL | NULL      |     NULL |
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+	
10 rows in set (0.01 sec)

distinct ：去除重复字段，前边不能有任何字段，代表后边所有字段联合去重

查询岗位数量

select count(distinct JOB) from EMP;

# 错误
mysql> select distinct count(JOB) from EMP;
+------------+
| count(JOB) |
+------------+
|         14 |
+------------+
1 row in set (0.00 sec)
# 正确
mysql> select count(distinct JOB) from EMP;
+---------------------+
| count(distinct JOB) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

连接查询

内连接：

​ 等值连接

​ 非等值连接

​ 自连接

外连接：

​ 左外连接

​ 右外连接

全连接：

内连接：两张表平等

外连接：一张主表，一张副表

查询每个员工的部门名

mysql> select e.ENAME,d.DNAME from EMP as e join DEPT as 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)

找出每个员工的员工名，薪资，薪资等级

mysql> select e.ENAME,e.SAL,s.GRADE from EMP as e join SALGRADE as s on e.sal between s.LOSAL and s.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.00 sec)

查询员工名及对应的上级领导名

# 内连接 没有上级领导的字段忽略
mysql> select e2.ENAME,e.ENAME,e2.MGR from EMP as e join EMP as e2 on e.MGR=e2.EMPNO;
+--------+-------+------+
| ENAME  | ENAME | MGR  |
+--------+-------+------+
| SMITH  | FORD  | 7902 |
| ALLEN  | BLAKE | 7698 |
| WARD   | BLAKE | 7698 |
| JONES  | KING  | 7839 |
| MARTIN | BLAKE | 7698 |
| BLAKE  | KING  | 7839 |
| CLARK  | KING  | 7839 |
| SCOTT  | JONES | 7566 |
| TURNER | BLAKE | 7698 |
| ADAMS  | SCOTT | 7788 |
| JAMES  | BLAKE | 7698 |
| FORD   | JONES | 7566 |
| MILLER | CLARK | 7782 |
+--------+-------+------+
13 rows in set (0.00 sec)
# 外连接 没有上级领导的字段自动补NULL
mysql> select e.ENAME,e2.ENAME,e2.MGR from EMP as e left join EMP as e2 on e.MGR=e2.EMPNO;
+--------+-------+------+
| ENAME  | ENAME | MGR  |
+--------+-------+------+
| nu_nij | NULL  | NULL |
| SMITH  | FORD  | 7566 |
| ALLEN  | BLAKE | 7839 |
| WARD   | BLAKE | 7839 |
| JONES  | KING  | NULL |
| MARTIN | BLAKE | 7839 |
| BLAKE  | KING  | NULL |
| CLARK  | KING  | NULL |
| SCOTT  | JONES | 7839 |
| KING   | NULL  | NULL |
| TURNER | BLAKE | 7839 |
| ADAMS  | SCOTT | 7566 |
| JAMES  | BLAKE | 7839 |
| FORD   | JONES | 7839 |
| MILLER | CLARK | 7839 |
+--------+-------+------+
15 rows in set (0.00 sec)

找出每个员工所属部门及对应工资等级

# 内连接：忽略NULL
mysql> select e.ENAME,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  | DNAME      | GRADE |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

# 左外连接 NULL数据补齐
mysql> select e.ENAME,d.DNAME,s.GRADE from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO left join SALGRADE s on e.SAL between s.LOSAL and s.HISAL;
+--------+------------+-------+
| ENAME  | DNAME      | GRADE |
+--------+------------+-------+
| nu_nij | NULL       |  NULL |
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
15 rows in set (0.00 sec)

查询所有员工的上级领导 ，部门名称，工资等级

mysql> select e.ENAME,e2.ENAME LEADER,e.SAL,d.DNAME,s.GRADE from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO left join SALGRADE s on e.SAL between s.LOSAL an
d s.HISAL left join EMP e2 on e.MGR=e2.EMPNO;
+--------+--------+---------+------------+-------+
| ENAME  | LEADER | SAL     | DNAME      | GRADE |
+--------+--------+---------+------------+-------+
| nu_nij | NULL   |    NULL | NULL       |  NULL |
| SMITH  | FORD   |  800.00 | RESEARCH   |     1 |
| ALLEN  | BLAKE  | 1600.00 | SALES      |     3 |
| WARD   | BLAKE  | 1250.00 | SALES      |     2 |
| JONES  | KING   | 2975.00 | RESEARCH   |     4 |
| MARTIN | BLAKE  | 1250.00 | SALES      |     2 |
| BLAKE  | KING   | 2850.00 | SALES      |     4 |
| CLARK  | KING   | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | JONES  | 3000.00 | RESEARCH   |     4 |
| KING   | NULL   | 5000.00 | ACCOUNTING |     5 |
| TURNER | BLAKE  | 1500.00 | SALES      |     3 |
| ADAMS  | SCOTT  | 1100.00 | RESEARCH   |     1 |
| JAMES  | BLAKE  |  950.00 | SALES      |     1 |
| FORD   | JONES  | 3000.00 | RESEARCH   |     4 |
| MILLER | CLARK  | 1300.00 | ACCOUNTING |     2 |
+--------+--------+---------+------------+-------+
15 rows in set (0.00 sec)

查出每个部门薪资的平均值的薪资等级

mysql> select t.*,s.GRADE from (select DEPTNO ,AVG(SAL) avg from EMP group by DEPTNO) t left join SALGRADE s on t.avg between s.LOSAL and s.HISAL;
+--------+-------------+-------+
| DEPTNO | avg         | GRADE |
+--------+-------------+-------+
|   NULL |        NULL |  NULL |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
4 rows in set (0.00 sec)

计算每个部门的平均薪水等级

# 子查询 效率低
mysql> select avg(t.grade) from (select e.DEPTNO,e.SAL,s.GRADE grade from EMP e left join SALGRADE s on e.SAL between s.LOSAL and s.HISAL) t group by t.DEPTN
O;
+--------------+
| avg(t.grade) |
+--------------+
|         NULL |
|       2.8000 |
|       2.5000 |
|       3.6667 |
+--------------+
4 rows in set (0.00 sec)

# 直接查结果 效率高 
mysql> select e.DEPTNO,avg(s.GRADE) from EMP e left join SALGRADE s on e.SAL between s.LOSAL and s.HISAL group by e.DEPTNO;
+--------+--------------+
| DEPTNO | avg(s.GRADE) |
+--------+--------------+
|   NULL |         NULL |
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
4 rows in set (0.01 sec)

union 两张表结果连接

第一个结果查询列必须和第二个查询结果列数量保持一致

建表时复制其他表的数据

mysql> create table if not exists st2 as select * from student;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from st2 ;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    | 0021    | 2018-02-04 |
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
|    3 | lisi     | 0    | 0022    | 1900-08-12 |
+------+----------+------+---------+------------+
3 rows in set (0.00 sec)

把查询结果插入到数据表中[insert into st2 select * from st2 where id>2;]

mysql> insert into st2 select * from st2 where id>2;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> insert into st2 select * from st2 where no>2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from st2;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    | 0021    | 2018-02-04 |
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
|    3 | lisi     | 0    | 0022    | 1900-08-12 |
|    3 | lisi     | 0    | 0022    | 1900-08-12 |
+------+----------+------+---------+------------+
4 rows in set (0.00 sec)

更新语句

mysql> update DEPT set LOC='SHANGHAI',DNAME='RENSHIBU' where DEPTNO=10;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

删除

mysql> select * from st2;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    | 0021    | 2018-02-04 |
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
+------+----------+------+---------+------------+
2 rows in set (0.00 sec)

mysql> delete from st2 where no =1;
Query OK, 1 row affected (0.04 sec)

truncate 表被截断，不可回滚，永久丢失

mysql> select * from st2;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
+------+----------+------+---------+------------+
1 row in set (0.00 sec)

mysql> truncate table st2 ;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from st2;
Empty set (0.00 sec)

约束：

建表时候，保证表中数据 合法性，有效性，完整性

非空约束

唯一约束：可以为null 不能重复

主键约束：既不能为null 也不能重复

外键约束

检查约束（oracle 中的check，mysql暂不支持）

列级约束：每个字段单独添加

表级约束：多个字段联合添加

联合唯一约束（表级约束）

from sqlalchemy import create_engine, Column, Integer, String, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    field1 = Column(String)
    field2 = Column(String)

    # 定义联合唯一约束
    __table_args__ = (
        UniqueConstraint('field1', 'field2'),
    )

# 创建引擎和表
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

主键约束：不能空，也不能重复（列级约束）

主键约束：primary key

主键字段：带有主键约束的字段

主键值：插入在主键字段下的数据

主键作用：（数据库设计三范式：任何一张表都应该有主键）

主键记录上的值是这行记录在这张表上的唯一标识

主键分类：

字段划分：

  单一主键：推荐使用

  复合主键：违背三范式

性质划分：

  自然主键：自然数

  业务主键：银行卡号（不推荐使用）

auto_increment：自增（1开始）

外键约束 ：

foreign key (classno) references t_class(cno)

外键约束：foreign key

外键字段：主键或唯一字段

外简值：

先删子表，再删父表

先加父表，再加子表

先创建父表，再创建子表

# 父表
create table t_class(
    cno int ,
    cname varchar(255),
    primary key (con)
);

# 子表
create table t_student(
    sno int ,
    sname varchar(255),
    classno int ,
    primary key (con),
    foreign key (classno) references t_class(cno)
);

存储引擎：

show engines \G;

MyISAM：不支持事务

  三个文件表示一个表：

     格式文件：.frm

     数据文件：.MYD        

     索引文件：.MYI

  可被压缩，可转换成只读表，提高效率

InnoDB:(默认，最安全) 支持事务，外键，行锁

表结构存储在 .frm 文件中

数据存储在tablespace 表空间中（逻辑概念），无法被压缩，无法转换成只读

提供崩溃自动恢复机制

支持外键完整性，支持级联删除，级联更新

sql 语句中，子查询位于主查询内部

mysql> select e.ENAME ,t.* from (select DEPTNO,max(SAL) as sal from EMP group by DEPTNO) t jo
MP e o    -> in EMP e on e.DEPTNO=t.DEPTNO and e.SAL=t.sal;
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 'jo
in EMP e on e.DEPTNO=t.DEPTNO and e.SAL=t.sal' at line 1
mysql> SELECT e.ENAME, t.*
FROM (
        -> FROM (
    ->     SELECT DEPTNO, MAX(SAL) AS sal
    ->     FROM EMP
    ->     GROUP BY DEPTNO
    -> ) t
    -> JOIN EMP e ON e.DEPTNO = t.DEPTNO AND e.SAL = t.sal;
+-------+--------+---------+
| ENAME | DEPTNO | sal     |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

事务（Transcation）

一个事务是一个完整的业务逻辑单元，不可再分

和事务相关的只有 DML语句（insert delete update）

事务是为了保证数据的完整性

事务四特性：

原子性：事务是最小的工作单元 ，不可再分

一致性：多条DML 同时成功，同时失败

隔离性：事务之间相互隔离

持久性：最终数据必须保存在硬盘上，事务才算成功

事务隔离级别

读未提交：存在脏读问题（当前事务读到其他事务未提交数据）

读已提交：解决了脏读，存在 不可重复读问题（一个事务中，还没有提交，两次读取的数据不一样【其它事务在期间提交更改数据导致数据不一致】）

可重复读：解决不可重复读问题，存在 幻读 问题（读取到数据幻象）【Mysql默认隔离级别】

串行化：解决所有问题，效率低，需要事务排队

Mysql 事务默认是执行一条就自动提交的，执行完一条DML就自动提交

关闭自动提交需要使用 ：start transcation 开启事务

索引：（目录）

原理：(缩小扫描范围)

     通过B+Tree 缩小扫描范围，索引底层进行了排序，分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位数据，效率是最高的





  不能随意添加，维护需要成本

  数据经常被修改，数据量较少 不适合用索引

  数据量大，字段很少DML操作 ，经常出现在where子句后边 适合用索引

全表扫描：

索引检索：（效率较高）

主键 和 具有unique约束 的字段会自动添加索引**

查看执行计划 [看 type]

mysql> explain select * from EMP where SAL >=3000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMP   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

给字段创建索引

mysql> CREATE INDEX emp_sal_index on EMP(SAL);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 执行计划
mysql> explain select * from EMP where SAL >=3000;
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | EMP   | NULL       | range | emp_sal_index | emp_sal_index | 9       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

删除索引

mysql> drop index emp_sal_index on EMP;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引分类：

单一索引

复合索引

主键索引：主键自动添加索引

唯一索引：有unique约束字段会自动添加索引

索引失效：

模糊查询时第一个通配符使用%

视图 只能以DQL创建视图

create table emp_bak as select * from EMP;
# 创建视图
create view myview as select EMPNO,ENAME,SAL from emp_bak;
# 更新视图(原表数据也会修改)
update myview set ENAME='hehe',SAL=1 where EMPNO=7369;
# 删除视图
drop view if exists myview;

DBA 命令

  1. 导出数据库中的数据

    Dos 窗口执行，不需要登录数据库管理系统

    mysqldump demo > E:\Desktop\demo.sql -u root -p123456

  2. 导入数据

    create database demo;
use demo;
source E:\Desktop\demo.sql

数据库三范式（解决数据冗余）

  1. 任何一张表都有主键，并且每一个字段原子性不可再分
  2. 建立在1基础上，所有非主键字段完全依赖主键，不能产生部分依赖 [复合主键会出现不满足2要求]
  3. 建立在2 基础上，不能产生传递依赖

查询每个部门最大薪资人员名称

mysql> select e.ENAME,t.DEPTNO,t.max_sal from EMP e join (select max(SAL) max_sal,DEPTNO from EMP group by DEPTNO) t on e.SAL=t.max_sal and e.DEPTNO=t.DEPTNO
;
+-------+--------+---------+
| ENAME | DEPTNO | max_sal |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

哪些人的薪水在部门平均薪水之上

mysql> select e.ENAME,e.DEPTNO,e.SAL,d.avg from EMP e join ( select avg(SAL) avg ,DEPTNO from EMP group by DEPTNO) d on e.SAL > d.avg and e.DEPTNO=d.DEPTNO;
+-------+--------+---------+-------------+
| ENAME | DEPTNO | SAL     | avg         |
+-------+--------+---------+-------------+
| FORD  |     20 | 3000.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| KING  |     10 | 5000.00 | 2916.666667 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)

取得部门中所有人的平均的薪水等级

mysql> select t.DEPTNO,avg(t.grade) from (select e.*,d.GRADE grade from EMP e join SALGRADE d on e.SAL between d.LOSAL and d.HISAL) t group by DEPTNO;
+--------+--------------+
| DEPTNO | avg(t.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
# 不用临时表
mysql> select e.DEPTNO,avg(s.GRADE) from EMP e join SALGRADE s on e.SAL between s.LOSAL and s.HISAL group by e.DEPTNO;
+--------+--------------+
| DEPTNO | avg(s.GRADE) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

不用max 取得最高薪水

mysql> select * from EMP order  by SAL DESC limit 1;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

# 自连接
mysql> select * from EMP where SAL not in (select distinct b.SAL from EMP a join EMP b on a.SAL > b.SAL);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

取得平均薪水最高的部门编号

mysql> select DEPTNO ,avg(SAL) avg from EMP group by DEPTNO order by avg desc limit 1;
+--------+-------------+
| DEPTNO | avg         |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)




mysql> select DEPTNO ,avg(SAL) avgsal from EMP group by DEPTNO having avgsal = (select max(t.avg) from (select DEPTNO ,avg(SAL) avg from EMP group by DEPTNO)
 t );
+--------+-------------+
| DEPTNO | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

求平均薪水的等级最低的部门的部门名称

mysql> select d.DNAME from DEPT d join (select b.DEPTNO from SALGRADE t join (select DEPTNO,avg(SAL) sal from EMP group
by DEPTNO) b on b.sal between t.LOSAL and t.HISAL order by t.GRADE limit 1) q on q.DEPTNO =d.DEPTNO ;
+-------+
| DNAME |
+-------+
| SALES |
+-------+
1 row in set (0.00 sec)

取得比普通员工(员工编号没有在MGR字段出现)的最高薪水还要高的领导人的名字

mysql> select ENAME from EMP where EMPNO in (select distinct MGR from EMP where MGR) and SAL >(select MAX(SAL) from EMP where EMPNO not in (select distinct MGR from EMP where MGR));
+-------+
| ENAME |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
6 rows in set (0.01 sec)

取得每个薪水等级有多少员工

mysql> select g.GRADE,count(g.GRADE) from EMP e join SALGRADE g on e.SAL between g.LOSAL and g.HISAL group by g.GRADE;
+-------+----------------+
| GRADE | count(g.GRADE) |
+-------+----------------+
|     1 |              3 |
|     3 |              2 |
|     2 |              3 |
|     4 |              5 |
|     5 |              1 |
+-------+----------------+
5 rows in set (0.00 sec)

找出每个员工和对应上级领导的名字（left join）

mysql> select a.ENAME ,b.ENAME from EMP a left join EMP b on a.MGR=b.EMPNO;
+--------+-------+
| ENAME  | ENAME |
+--------+-------+
| nu_nij | NULL  |
| 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 |
+--------+-------+
15 rows in set (0.00 sec)

找出受雇日期早于直属上级领导的所有员工的员工编号，姓名，部门名

mysql> select a.EMPNO, a.ENAME,c.DNAME from EMP a join EMP b on a.HIREDATE < b.HIREDATE and a.MGR =b.EMPNO join DEPT c on c.DEPTNO=a.DEPTNO;
+-------+-------+------------+
| EMPNO | ENAME | DNAME      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+
6 rows in set (0.00 sec)

列出部门名称，和这些部门的员工信息，并同时列出没有员工的部门

mysql> select e.*,d.DEPTNO,d.DNAME from EMP e right join DEPT d on d.DEPTNO=e.DEPTNO;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     40 | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+
15 rows in set (0.00 sec)

列出至少有5个员工的所有部门

mysql> select DEPTNO,count(DEPTNO) cnt from EMP group by DEPTNO having cnt >= 5;
+--------+-----+
| DEPTNO | cnt |
+--------+-----+
|     20 |   5 |
|     30 |   6 |
+--------+-----+
2 rows in set (0.00 sec)

列出比‘SMITH’薪资高的员工信息

mysql> select * from EMP where SAL >(select SAL from EMP where ENAME='SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

列出所有办事员‘CLERK’ 的姓名，部门名称，部门人数

mysql> select e.ENAME,d.DNAME,c.cnt from EMP e join DEPT d on e.DEPTNO=d.DEPTNO and e.JOB='CLERK' join (select DEPTNO,count(DEPTNO) cnt from EMP g
roup by DEPTNO) c on c.DEPTNO=e.DEPTNO;
+--------+------------+-----+
| ENAME  | DNAME      | cnt |
+--------+------------+-----+
| SMITH  | RESEARCH   |   5 |
| ADAMS  | RESEARCH   |   5 |
| JAMES  | SALES      |   6 |
| MILLER | ACCOUNTING |   3 |
+--------+------------+-----+
4 rows in set (0.00 sec)

列出最低薪金大于1500的各种工作 及从事此工作的全部雇员人数

mysql> select a.cnt, b.JOB from (select count(JOB) cnt,JOB from EMP group by JOB) a right join (select JOB from EMP group by JOB having min(SAL) > 1500) b on a.JOB=b.JOB;
+------+-----------+
| cnt  | JOB       |
+------+-----------+
|    3 | MANAGER   |
|    2 | ANALYST   |
|    1 | PRESIDENT |
+------+-----------+
3 rows in set (0.00 sec)

列出薪金高于在30部门工作的所有人员的薪金的员工姓名，薪金，部门名称

mysql> select e.ENAME,e.SAL,d.DNAME from EMP e join DEPT d on e.SAL > (select MAX(SAL) from EMP group by DEPTNO having DEPTNO=30) and d.DEPTNO=e.DEPTNO;
+-------+---------+----------+
| ENAME | SAL     | DNAME    |
+-------+---------+----------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING  | 5000.00 | RENSHIBU |
| FORD  | 3000.00 | RESEARCH |
+-------+---------+----------+
4 rows in set (0.00 sec)

列出在每个部门工作的员工数量，平均工资，平均服务期限，没有员工的部门，部门人数0

NOW() :当前时间

ifnull（判断字段，默认值）

TimeStampDiff(间隔类型，前一个日期，后一个日期)

​ 间隔类型：YEAR ,MONTH,DAY ,HOUR,MINUTE,SECOND,WEEK,QUARTER(季度)

mysql> select d.DEPTNO,count(e.ENAME),ifnull(avg(e.SAL),0),avg(TimeStampDiff(YEAR,e.HIREDATE,NOW())) from EMP e right join DEPT d on e.DEPTNO=d.D
EPTNO group by d.DEPTNO;
+--------+----------------+----------------------+-------------------------------------------+
| DEPTNO | count(e.ENAME) | ifnull(avg(e.SAL),0) | avg(TimeStampDiff(YEAR,e.HIREDATE,NOW())) |
+--------+----------------+----------------------+-------------------------------------------+
|     10 |              3 |          2916.666667 |                                   42.3333 |
|     20 |              5 |          2175.000000 |                                   40.4000 |
|     30 |              6 |          1566.666667 |                                   42.5000 |
|     40 |              0 |             0.000000 |                                      NULL |
+--------+----------------+----------------------+-------------------------------------------+
4 rows in set (0.00 sec)

列出所有员工姓名，部门名称，工资

mysql> select d.DNAME,e.ENAME,e.SAL from EMP e join DEPT d on e.DEPTNO=d.DEPTNO;
+----------+--------+---------+
| DNAME    | ENAME  | SAL     |
+----------+--------+---------+
| RESEARCH | SMITH  |  800.00 |
| SALES    | ALLEN  | 1600.00 |
| SALES    | WARD   | 1250.00 |
| RESEARCH | JONES  | 2975.00 |
| SALES    | MARTIN | 1250.00 |
| SALES    | BLAKE  | 2850.00 |
| RENSHIBU | CLARK  | 2450.00 |
| RESEARCH | SCOTT  | 3000.00 |
| RENSHIBU | KING   | 5000.00 |
| SALES    | TURNER | 1500.00 |
| RESEARCH | ADAMS  | 1100.00 |
| SALES    | JAMES  |  950.00 |
| RESEARCH | FORD   | 3000.00 |
| RENSHIBU | MILLER | 1300.00 |
+----------+--------+---------+
14 rows in set (0.00 sec)

列出所有部门详细信息和人数

mysql> select d.*,count(e.ENAME) from EMP e right join DEPT d on e.DEPTNO=d.DEPTNO group by d.DEPTNO;
+--------+------------+----------+----------------+
| DEPTNO | DNAME      | LOC      | count(e.ENAME) |
+--------+------------+----------+----------------+
|     10 | RENSHIBU   | SHANGHAI |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+
4 rows in set (0.00 sec)

列出各种工作的最低工资即对应雇员名

mysql> select e.ENAME,b.JOB,b.msal from EMP e join (select JOB,min(SAL) msal from EMP group by JOB) b on e.JOB=b.JOB and e.SAL=b.msal;
+--------+-----------+---------+
| ENAME  | JOB       | msal    |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)

列出各部门 MANAGER 的最低薪金

+----------+--------+
| min(SAL) | DEPTNO |
+----------+--------+
|  2975.00 |     20 |
|  2850.00 |     30 |
|  2450.00 |     10 |
+----------+--------+
3 rows in set (0.00 sec)

里出所有员工的年工资，按年薪从低到高排序

mysql> select *,ifnull(SAL*12,0) ysal from EMP order by ysal;
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | ysal     |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  9600.00 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | 11400.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | 13200.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 15000.00 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 15000.00 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 15600.00 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 19200.00 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 29400.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 34200.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 35700.00 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | 36000.00 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | 36000.00 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | 60000.00 |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
14 rows in set (0.00 sec)

求出员工 领导的薪水超过3000的员工姓名与领导

mysql> select a.ENAME 'yuangong',b.ENAME 'lingdao' from EMP a join EMP b on a.MGR=b.EMPNO and b.SAL >3000;
+----------+---------+
| yuangong | lingdao |
+----------+---------+
| JONES    | KING    |
| BLAKE    | KING    |
| CLARK    | KING    |
+----------+---------+
3 rows in set (0.00 sec)

查询结果转大写:ucase，upper

mysql> select upper(ename) from EMP;
+--------------+
| upper(ename) |
+--------------+
| NU_NIJ       |
| SMITH        |
| ALLEN        |
| WARD         |
| JONES        |
| MARTIN       |
| BLAKE        |
| CLARK        |
| SCOTT        |
| KING         |
| TURNER       |
| ADAMS        |
| JAMES        |
| FORD         |
| MILLER       |
+--------------+
15 rows in set (0.00 sec)

mysql> select ucase(ename) from EMP;
+--------------+
| ucase(ename) |
+--------------+
| NU_NIJ       |
| SMITH        |
| ALLEN        |
| WARD         |
| JONES        |
| MARTIN       |
| BLAKE        |
| CLARK        |
| SCOTT        |
| KING         |
| TURNER       |
| ADAMS        |
| JAMES        |
| FORD         |
| MILLER       |
+--------------+
15 rows in set (0.00 sec)

查询结果转小写 lower，lcase

mysql> select lower(ename) from EMP;
+--------------+
| lower(ename) |
+--------------+
| nu_nij       |
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
15 rows in set (0.00 sec)

mysql> select lcase(ename) from EMP;
+--------------+
| lcase(ename) |
+--------------+
| nu_nij       |
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
15 rows in set (0.00 sec)

查询结果中截取 substr（字段名，开始(从1开始)，截取长度）

mysql> select substr(ENAME,1,2) from EMP;
+-------------------+
| substr(ENAME,1,2) |
+-------------------+
| nu                |
| SM                |
| AL                |
| WA                |
| JO                |
| MA                |
| BL                |
| CL                |
| SC                |
| KI                |
| TU                |
| AD                |
| JA                |
| FO                |
| MI                |
+-------------------+
15 rows in set (0.00 sec)

获取结果长度

length：统计字节长度（一个中文2字节）

char_length:统计字符长度

mysql>  select length(ENAME),ENAME from EMP;
+---------------+--------+
| length(ENAME) | ENAME  |
+---------------+--------+
|             6 | nu_nij |
|             5 | SMITH  |
|             5 | ALLEN  |
|             4 | WARD   |
|             5 | JONES  |
|             6 | MARTIN |
|             5 | BLAKE  |
|             5 | CLARK  |
|             5 | SCOTT  |
|             4 | KING   |
|             6 | TURNER |
|             5 | ADAMS  |
|             5 | JAMES  |
|             4 | FORD   |
|             6 | MILLER |
+---------------+--------+
15 rows in set (0.00 sec)

mysql>  select char_length(ENAME),ENAME from EMP;
+--------------------+--------+
| char_length(ENAME) | ENAME  |
+--------------------+--------+
|                  6 | nu_nij |
|                  5 | SMITH  |
|                  5 | ALLEN  |
|                  4 | WARD   |
|                  5 | JONES  |
|                  6 | MARTIN |
|                  5 | BLAKE  |
|                  5 | CLARK  |
|                  5 | SCOTT  |
|                  4 | KING   |
|                  6 | TURNER |
|                  5 | ADAMS  |
|                  5 | JAMES  |
|                  4 | FORD   |
|                  6 | MILLER |
+--------------------+--------+
15 rows in set (0.00 sec)

查询结果拼接 concat

mysql> select concat(ENAME,sal,'123') from EMP;
+-------------------------+
| concat(ENAME,sal,'123') |
+-------------------------+
| NULL                    |
| SMITH800.00123          |
| ALLEN1600.00123         |
| WARD1250.00123          |
| JONES2975.00123         |
| MARTIN1250.00123        |
| BLAKE2850.00123         |
| CLARK2450.00123         |
| SCOTT3000.00123         |
| KING5000.00123          |
| TURNER1500.00123        |
| ADAMS1100.00123         |
| JAMES950.00123          |
| FORD3000.00123          |
| MILLER1300.00123        |
+-------------------------+
15 rows in set (0.00 sec)

去除参与拼接字符串中的前后空白字符 trim

mysql> select concat(trim(' a   sd a s           '),ename) from EMP;
+----------------------------------------------+
| concat(trim(' a   sd a s           '),ename) |
+----------------------------------------------+
| a   sd a snu_nij                             |
| a   sd a sSMITH                              |
| a   sd a sALLEN                              |
| a   sd a sWARD                               |
| a   sd a sJONES                              |
| a   sd a sMARTIN                             |
| a   sd a sBLAKE                              |
| a   sd a sCLARK                              |
| a   sd a sSCOTT                              |
| a   sd a sKING                               |
| a   sd a sTURNER                             |
| a   sd a sADAMS                              |
| a   sd a sJAMES                              |
| a   sd a sFORD                               |
| a   sd a sMILLER                             |
+----------------------------------------------+
15 rows in set (0.01 sec)

生成0-1随机数 rand()

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8497881794812048 |
+--------------------+
1 row in set (0.01 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.2676997531835919 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7891342582079901 |
+--------------------+
1 row in set (0.00 sec)
# 后边加个固定值就可以重复获取某一个随机数
mysql> select rand(2);
+--------------------+
| rand(2)            |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set (0.01 sec)

mysql> select rand(2);
+--------------------+
| rand(2)            |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set (0.00 sec)

四舍五入 round(数值，保留位数)

mysql> select round(3);
+----------+
| round(3) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select round(3.1);
+------------+
| round(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select round(3.6);
+------------+
| round(3.6) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> select round(3.66,1);
+---------------+
| round(3.66,1) |
+---------------+
|           3.7 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(3.66,2);
+---------------+
| round(3.66,2) |
+---------------+
|          3.66 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(3.66,3);
+---------------+
| round(3.66,3) |
+---------------+
|          3.66 |
+---------------+
1 row in set (0.00 sec)

舍位 truncate（数值，保留位数）

mysql> select truncate(9.99,2);
+------------------+
| truncate(9.99,2) |
+------------------+
|             9.99 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(9.99999,2);
+---------------------+
| truncate(9.99999,2) |
+---------------------+
|                9.99 |
+---------------------+
1 row in set (0.00 sec)

向上取整（ceil），向下取整(floor)

# 向上取整
mysql> select ceil(4.44);
+------------+
| ceil(4.44) |
+------------+
|          5 |
+------------+
1 row in set (0.01 sec)

mysql> select ceil(4.1);
+-----------+
| ceil(4.1) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)
# 向下取整
mysql> select floor(111.1);
+--------------+
| floor(111.1) |
+--------------+
|          111 |
+--------------+
1 row in set (0.00 sec)

mysql> select floor(111.9);
+--------------+
| floor(111.9) |
+--------------+
|          111 |
+--------------+
1 row in set (0.00 sec)

获取当前时间

# 获取select语句执行时间节点
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-05 06:32:46 |
+---------------------+
1 row in set (0.00 sec)

# 获取sysdate（）执行时间节点
mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2024-07-05 06:32:51 |
+---------------------+
1 row in set (0.00 sec)


mysql> select sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2024-07-05 06:34:34 |        0 | 2024-07-05 06:34:36 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql> select now(),sleep(2),now();
+---------------------+----------+---------------------+
| now()               | sleep(2) | now()               |
+---------------------+----------+---------------------+
| 2024-07-05 06:34:51 |        0 | 2024-07-05 06:34:51 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

mysql> select now(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| now()               | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2024-07-05 06:35:06 |        0 | 2024-07-05 06:35:08 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

获取当前日期 单独年月日时分秒

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2024-07-05 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 06:36:50  |
+-----------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2024 |
+-------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            7 |
+--------------+
1 row in set (0.01 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            37 |
+---------------+
1 row in set (0.01 sec)

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            52 |
+---------------+
1 row in set (0.00 sec)

获取执行时间间隔指定时间的时间 (add_date(时间，interval 时长 时间单位))

​ 时长为负数时为向前计算间隔时间

mysql> select date_add(now(),interval 10 day);
+---------------------------------+
| date_add(now(),interval 10 day) |
+---------------------------------+
| 2024-07-15 06:39:50             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 10 hour);
+----------------------------------+
| date_add(now(),interval 10 hour) |
+----------------------------------+
| 2024-07-05 16:39:55              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval -10 hour);
+-----------------------------------+
| date_add(now(),interval -10 hour) |
+-----------------------------------+
| 2024-07-04 20:42:12               |
+-----------------------------------+
1 row in set (0.00 sec)

# 支持复合单位 ，最多两个基本单位拼接
# 三年两个月之后
mysql> select date_add(now(),interval '10,2' year_month);
+--------------------------------------------+
| date_add(now(),interval '10,2' year_month) |
+--------------------------------------------+
| 2034-09-05 06:44:52                        |
+--------------------------------------------+
1 row in set (0.00 sec)

格式化日期类型字符串 date_format(日期，格式)

%Y : 四位年

%y : 两位年

%m : 月

%d : 日

%H : 时

%i : 分

%s : 秒

mysql> select date_format(now(),'%Y-%m-%d');
+-------------------------------+
| date_format(now(),'%Y-%m-%d') |
+-------------------------------+
| 2024-07-05                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y %m %d');
+-------------------------------+
| date_format(now(),'%Y %m %d') |
+-------------------------------+
| 2024 07 05                    |
+-------------------------------+
1 row in set (0.00 sec)

非标准日期格式化 str_to_date(日期，格式)

mysql> insert into t_student (id,birth) values(1,'10-10-1999');
ERROR 1292 (22007): Incorrect date value: '10-10-1999' for column 'birth' at row 1、

mysql> insert into t_student (id,birth) values(1,str_to_date('10-10-1999'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'str_to_date'

mysql> insert into t_student (id,birth) values(1,str_to_date('10-10-1999','%d-%m-%Y'));
Query OK, 1 row affected (0.03 sec)

mysql> select * from t_student;
+------+------------+
| id   | birth      |
+------+------------+
|    1 | 1999-10-10 |
+------+------------+
1 row in set (0.01 sec)

计算两个日期时间差

mysql> select datediff('2020-12-12','2021-12-12');
+-------------------------------------+
| datediff('2020-12-12','2021-12-12') |
+-------------------------------------+
|                                -365 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('2020-12-12 12:12:12','2021-12-12 12:12:01');
+-------------------------------------------------------+
| timediff('2020-12-12 12:12:12','2021-12-12 12:12:01') |
+-------------------------------------------------------+
| -838:59:59                                            |
+-------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

if 判断

mysql> select * from t_stu;
+------+------+
| id   | sex  |
+------+------+
|    1 |    0 |
|    2 |    1 |
|    3 |    0 |
+------+------+
3 rows in set (0.00 sec)

mysql> select id,if(sex=0,'nan','nv') from t_stu;
+------+----------------------+
| id   | if(sex=0,'nan','nv') |
+------+----------------------+
|    1 | nan                  |
|    2 | nv                   |
|    3 | nan                  |
+------+----------------------+
3 rows in set (0.00 sec)

数据库md5()

mysql> select md5('124312413431');
+----------------------------------+
| md5('124312413431')              |
+----------------------------------+
| 089c41c7e4de8b53f9c8472c6ea68623 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select md5('124312413431');
+----------------------------------+
| md5('124312413431')              |
+----------------------------------+
| 089c41c7e4de8b53f9c8472c6ea68623 |
+----------------------------------+
1 row in set (0.00 sec)

# 直接往数据库里插入md5字符串
mysql> create table t_d ( id int, des varchar(32));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t_d (id,des) values(1,md5('i like you'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_d;
+------+----------------------------------+
| id   | des                              |
+------+----------------------------------+
|    1 | 6ece875294d9caabb6655a1704e63d98 |
+------+----------------------------------+
1 row in set (0.00 sec)
JHC000000
关注
  • 25
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
动力节点老杜MySQL数据库教程课堂笔记分享
07-24
其中详细讲解了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性，通过观看本套Java视频教程就可掌握MySQL全套知识。
MySQL笔记，老杜，初级MySQL笔记
06-23
MySQL笔记，老杜，初级MySQL笔记
MySQL基础入门笔记
05-07
mysql
MySQL课堂笔记.txt
03-28
老杜mysql全套笔记，简单直接，易懂，精简。可读性很好，自己就是学习此笔记，在此分享，坚持就是胜利，程序猿最有耐心，何愁到不了岸呢，赶紧上岸，go go go
MySQL数据库视频教程老杜最新MySQL教程
06-16
首先，教程会从MySQL的基础知识开始，包括数据库的概念、MySQL的安装与配置。学习者将了解到如何在不同的操作系统环境下安装MySQL服务器，并进行基本的配置，以满足不同项目的需求。 接下来，教程会深入讲解SQL语言...
MYSQL
maoqiwei的博客
07-23 232
1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint 2.为Course表中Cno 课程号字段设置索引,并查看索引 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩5.删除所有索引
测试开发面试题---MySQL
m0_53302824的博客
07-22 1000
索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。
MySQL字段设置的varchar长度小于数据长度自动截取丢弃超出的长度而不是报错？
weixin_43831997的博客
07-23 330
当MySQL数据库设置为==严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）==时，如果尝试插入或更新超过设定长度的数据，将会导致错误，并阻止该事务进行。在默认情况下，或者在非严格模式（NO_STRICT）下，当插入长度超出 VARCHAR 字段定义的字符串时，MySQL会自动截断超出的部分，并发出一个警告，而不是错误。
MySQL的MHA
2402_84844434的博客
07-24 778
高可用模式下的故障切换，基于主从复制。单点故障和主从复制不能切换的问题。至少需要3台。奇数台故障切换的过程0~30秒。根据vip地址所在的主机，确定主备。主和备不是靠优先级决定的，主从复制的时候就确定了主，备是在MHA的过程中确定的。
Mysql (五)
qq_64410777的博客
07-23 498
以左边的表为基础，接收左表的所有行，以左表的记录与右表的记录进行匹配，匹配左表的所有，以及右表中符合条件的行，不符合的显示null值。表和列的别名 在实际工作中，表和列名可能很长，写起来不方便，如果需要多次声明表和列时，全部展示太复杂，设置别名使书写简化。这里只是判断，结果为空 则不执行 ，如果不为空则执行前面的 结果是7 只要不为空就是7 执行的是count(*)SELECT i.name 姓名,i.score 成绩 from info as i;
[Mysql-DML数据操作语句]
2301_78794952的博客
07-23 1007
insert into student (sid,sname,birthday,ssex,classid) values(9,'张三','2007-1-1','男',1);insert into student(sname,ssex) values('某某1','男'),('某某2','女'),('某某3','男');insert into student values(9,'张三','2007-1-1','男',1);-- 将姓名为某某的生日改为1888-1-1。
Mysql - 索引
m0_69594200的博客
07-23 919
存储引擎、SQL优化工具、SQL优化，索引的分类、语法和使用规则等【笔记】内容
深入MySQL中的IF和IFNULL函数
一起coding，一起嗨。
07-23 388
深入MySQL中的IF和IFNULL函数
MySQL中不等于筛选时会漏掉null值的问题
山子的博客
07-22 363
MySQL中使用不等于进行筛选数据时，若筛选值为`null`，则该条数据不会被选中，如何解决该问题？
详解Mysql InnoDB引擎 04
最新发布
程序员进阶之路
07-24 620
InnoDB 是MySQL数据库管理系统的一个存储引擎，它为MySQL提供了事务处理、外键约束和并发控制等关系型数据库管理系统（RDBMS）中重要的功能。以下是关于InnoDB存储引擎的一些关键特点和特性：
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1237
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
Mysql的索引
2401_84592402的博客
07-24 461
索引是一个排序的列表，在列表当中存储索引的值以及索引对应数据所在的物理行索引值和数据是一个映射关系。
使用sqlalchemy查询mysql的JSON字段
ithongchou的博客
07-23 121
首先，你需要定义一个与表格对应的模型类。
MySQL(1)
yajunjiao的专栏
07-22 1766
数据库这节开始写，好久没写数据库，觉得有点陌生了，现在复习下，顺便记录下。重新学习一遍，每一遍都有不一样的体会，可能就是刻意练习里的心理表征吧。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值