Mysql 添加、修改和删除数据_mysql loc shanghai-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45801177/article/details/113647486

本文详细介绍了SQL中增删改的基本操作，包括INSERT、UPDATE和DELETE语句的使用方法及注意事项。通过多个实例展示了如何进行数据的添加、更新和删除，适用于初学者和需要复习SQL基本操作的读者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

insert

语法格式

insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。

例1：字段名和值一一对应

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','F1','2000-1-1');
结果：
select * from t_student;

+------+----------+------+---------+----------+
| no   | name     | sex  | classno | birth    |
+------+----------+------+---------+----------+
|    1 | zhangsan | 1    | F1      | 2000-1-1 |
+------+----------+------+---------+----------+

例2：插入的字段顺序不影响结果：

insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);
结果：
select * from t_student;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | F1         | 2000-1-1   |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+

例3：可以只插入部分字段，其余全部为NULL。

insert into t_student(name) values('wangwu'); // 除name字段之外，剩下的所有字段自动插入NULL。
结果：
select * from t_student;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | F1         | 2000-1-1   |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
| NULL | wangwu   | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+

p.s.
drop table if exists t_student; // 当这个表存在的话删除。

例4：可以省略字段名但必须符合顺序和个数

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | F1         | 2000-1-1   |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
| NULL | wangwu   | NULL | NULL       | NULL       |
|    1 | jack     | 0    | gaosan2ban | 1986-10-23 |
+------+----------+------+------------+------------+

例5：一次插入多行数据

insert into t_student
		(no,name,sex,classno,birth) 
	values
		(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');

结果：

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | F1         | 2000-1-1   |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
| NULL | wangwu   | NULL | NULL       | NULL       |
|    1 | jack     | 0    | gaosan2ban | 1986-10-23 |
|    3 | rose     | 1    | gaosi2ban  | 1952-12-14 |
|    4 | laotie   | 1    | gaosi2ban  | 1955-12-14 |
+------+----------+------+------------+------------+

例6：复制表

create table 表名 as select语句;
如：create table t_student_2 as select * from t_student;

例7：将查询结果插入到表

insert into t_student select * from t_student_2 where name='zhangsan;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | F1         | 2000-1-1   |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
| NULL | wangwu   | NULL | NULL       | NULL       |
|    1 | jack     | 0    | gaosan2ban | 1986-10-23 |
|    3 | rose     | 1    | gaosi2ban  | 1952-12-14 |
|    4 | laotie   | 1    | gaosi2ban  | 1955-12-14 |
|    1 | zhangsan | 1    | F1         | 2000-1-1   |
+------+----------+------+------------+------------+

例8：插入日期

方法1：插入的日期格式和显示的日期格式一致

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);

--+----------+-----------+------+------------+---------+---------+--------+
| 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 |
|  9997 | zhangsan | MANAGER   | NULL | 1981-06-12 | 3000.00 |  500.00 |     10 |
+-------+----------+-----------+------+------------+---------+---------+--------+

方法2：采用str_to_date

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);

+-------+----------+-----------+------+------------+---------+---------+--------+
| 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 |
|  9996 | zhangsan | MANAGER   | NULL | 1981-06-12 | 3000.00 |  500.00 |     10 |
|  9997 | zhangsan | MANAGER   | NULL | 1981-06-12 | 3000.00 |  500.00 |     10 |
+-------+----------+-----------+------+------------+---------+---------+--------+

方法3：添加系统日期（now()）

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);

+-------+----------+-----------+------+------------+---------+---------+--------+
| 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 |
|  9995 | zhangsan | MANAGER   | NULL | 2021-02-04 | 3000.00 |  500.00 |     10 |
|  9996 | zhangsan | MANAGER   | NULL | 1981-06-12 | 3000.00 |  500.00 |     10 |
|  9997 | zhangsan | MANAGER   | NULL | 1981-06-12 | 3000.00 |  500.00 |     10 |
+-------+----------+-----------+------+------------+---------+---------+--------+

update

语法格式

update 表名 set 字段名1=值1,字段名2=值2... where 条件;
没有条件整张表数据全部更新。

例：将部门10的LOC修改为SHANGHAI，将部门名称修改为HR

update dept set loc='shanghai',dname='HR' where deptno=10;

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | HR         | shanghai |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

delete

语法格式

delete from 表名 where 条件;
没有条件整张表数据全部删除。

例1：普通数据删除

delete from dept where deptno = 10;

+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+