【无标题】MYSQL: difference Demo on DELETE, Truncate and DROP

最新推荐文章于 2025-02-28 22:16:24 发布
Dyan GONG
最新推荐文章于 2025-02-28 22:16:24 发布
阅读量115
点赞数 1
分类专栏： Mysql 文章标签： sql mysql
本文链接：https://blog.csdn.net/DavidsonGong/article/details/136918784
版权
Mysql 专栏收录该内容
1 篇文章 0 订阅
订阅专栏
mysql> select * from dept;

+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|      1 | accounting | 一区   |
|      2 | research   | 二区   |
|      3 | operations | 二区   |
+--------+------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT d.dname,e.ename,e.job
    ->  
    -> FROM emp e INNER JOIN dept d
    ->  
    -> ON e.deptno=d.deptno
    ->  
    -> WHERE d.dname='research';
+----------+-------+--------+
| dname    | ename | job    |
+----------+-------+--------+
| research | tony  | 总监   |
| research | hana  | 经理   |
| research | leo   | 员工   |
| research | liu   | 员工   |
+----------+-------+--------+
4 rows in set (0.00 sec)

mysql> SELECT d.dname,d.loc,e.ename,e.job   FROM emp e INNER JOIN dept d   ON e.deptno=d.deptno   WHERE d.dname='research';
+----------+--------+-------+--------+
| dname    | loc    | ename | job    |
+----------+--------+-------+--------+
| research | 二区   | tony  | 总监   |
| research | 二区   | hana  | 经理   |
| research | 二区   | leo   | 员工   |
| research | 二区   | liu   | 员工   |
+----------+--------+-------+--------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|      1 | accounting | 一区   |
|      2 | research   | 二区   |
|      3 | operations | 二区   |
+--------+------------+--------+
3 rows in set (0.00 sec)

mysql> desc dept;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| deptno | int(11)     | NO   | PRI | NULL    | auto_increment |
| dname  | varchar(20) | YES  |     | NULL    |                |
| loc    | varchar(13) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe  emp;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| empno    | int(11)      | NO   | PRI | NULL    | auto_increment |
| ename    | varchar(10)  | YES  |     | NULL    |                |
| job      | varchar(10)  | YES  |     | NULL    |                |
| mgr      | int(11)      | YES  |     | NULL    |                |
| hiredate | date         | YES  |     | NULL    |                |
| sal      | double       | YES  |     | NULL    |                |
| comm     | decimal(7,2) | YES  |     | NULL    |                |
| deptno   | int(11)      | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|      1 | accounting | 一区   |
|      2 | research   | 二区   |
|      3 | operations | 二区   |
+--------+------------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-------+-------+--------+------+------------+-------+---------+--------+
| empno | ename | job    | mgr  | hiredate   | sal   | comm    | deptno |
+-------+-------+--------+------+------------+-------+---------+--------+
|   100 | jack  | 副总   | NULL | 2002-05-01 | 90000 |    NULL |      1 |
|   200 | tony  | 总监   |  100 | 2015-02-02 | 10000 | 2000.00 |      2 |
|   300 | hana  | 经理   |  200 | 2017-02-02 |  8000 | 1000.00 |      2 |
|   400 | leo   | 员工   |  300 | 2019-02-22 |  3000 |  200.12 |      2 |
|   500 | liu   | 员工   |  300 | 2019-03-19 |  3500 |  200.58 |      2 |
+-------+-------+--------+------+------------+-------+---------+--------+
5 rows in set (0.00 sec)

mysql> DELETE FROM emp where empno = 500;
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+-------+-------+--------+------+------------+-------+---------+--------+
| empno | ename | job    | mgr  | hiredate   | sal   | comm    | deptno |
+-------+-------+--------+------+------------+-------+---------+--------+
|   100 | jack  | 副总   | NULL | 2002-05-01 | 90000 |    NULL |      1 |
|   200 | tony  | 总监   |  100 | 2015-02-02 | 10000 | 2000.00 |      2 |
|   300 | hana  | 经理   |  200 | 2017-02-02 |  8000 | 1000.00 |      2 |
|   400 | leo   | 员工   |  300 | 2019-02-22 |  3000 |  200.12 |      2 |
+-------+-------+--------+------+------------+-------+---------+--------+
4 rows in set (0.00 sec)

mysql> INSERT INTO emp VALUES(null,'liu','员工',300,'2019-03-19',3500,200.58,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+-------+-------+--------+------+------------+-------+---------+--------+
| empno | ename | job    | mgr  | hiredate   | sal   | comm    | deptno |
+-------+-------+--------+------+------------+-------+---------+--------+
|   100 | jack  | 副总   | NULL | 2002-05-01 | 90000 |    NULL |      1 |
|   200 | tony  | 总监   |  100 | 2015-02-02 | 10000 | 2000.00 |      2 |
|   300 | hana  | 经理   |  200 | 2017-02-02 |  8000 | 1000.00 |      2 |
|   400 | leo   | 员工   |  300 | 2019-02-22 |  3000 |  200.12 |      2 |
|   501 | liu   | 员工   |  300 | 2019-03-19 |  3500 |  200.58 |      2 |
+-------+-------+--------+------+------------+-------+---------+--------+
5 rows in set (0.00 sec)

mysql> truncate table emp;
Query OK, 0 rows affected (0.01 sec)

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

mysql> INSERT INTO emp VALUES(null,'liu','员工',300,'2019-03-19',3500,200.58,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+-------+-------+--------+------+------------+------+--------+--------+
| empno | ename | job    | mgr  | hiredate   | sal  | comm   | deptno |
+-------+-------+--------+------+------------+------+--------+--------+
|     1 | liu   | 员工   |  300 | 2019-03-19 | 3500 | 200.58 |      2 |
+-------+-------+--------+------+------------+------+--------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO emp VALUES(null,'liu','员工',300,'2019-03-19',3500,200.58,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+-------+-------+--------+------+------------+------+--------+--------+
| empno | ename | job    | mgr  | hiredate   | sal  | comm   | deptno |
+-------+-------+--------+------+------------+------+--------+--------+
|     1 | liu   | 员工   |  300 | 2019-03-19 | 3500 | 200.58 |      2 |
|     2 | liu   | 员工   |  300 | 2019-03-19 | 3500 | 200.58 |      2 |
+-------+-------+--------+------+------------+------+--------+--------+
2 rows in set (0.00 sec)

mysql> delete table 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 'table emp' at line 1
mysql> delete from emp;
Query OK, 2 rows affected (0.00 sec)

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

mysql> INSERT INTO emp VALUES(null,'liu','员工',300,'2019-03-19',3500,200.58,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+-------+-------+--------+------+------------+------+--------+--------+
| empno | ename | job    | mgr  | hiredate   | sal  | comm   | deptno |
+-------+-------+--------+------+------------+------+--------+--------+
|     3 | liu   | 员工   |  300 | 2019-03-19 | 3500 | 200.58 |      2 |
+-------+-------+--------+------+------------+------+--------+--------+
1 row in set (0.00 sec)

mysql> drop 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 'emp' at line 1
mysql> drop 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 'from emp' at line 1
mysql> drop table emp;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
ERROR 1146 (42S02): Table 'cgb2022.emp' doesn't exist
mysql> desc emp;
ERROR 1146 (42S02): Table 'cgb2022.emp' doesn't exist
mysql>