Home > Database > Mysql Tutorial > MySQL Lecture 2: DML Data Operation Statements

MySQL Lecture 2: DML Data Operation Statements

coldplay.xixi
Release: 2021-02-23 09:32:16
forward
2003 people have browsed it

MySQL Lecture 2: DML Data Operation Statements

Free learning recommendation: mysql video tutorial

##Article Directory

    1. Insert
  • 2. Modify update
  • 3. Delete delete/truncate
  • 4. DML statement exercises

1. Insert

Method 1:

insert into table name (column name,...) values(value1,...); Method 2:
insert into table name set column name=value, column name=value...

CompareWhether it supports inserting multiple rowsWhether it supports subqueriesMethod 1√×Method 2√×
【插入方式一】# 1.插入的值的类型要与列的类型一致或兼容INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUE(13,'双笙','女','1999-02-15','13868666666',NULL,2);# 2.列的顺序可以调换INSERT INTO beauty(NAME,id,sex,borndate,phone,photo,boyfriend_id)VALUE('徐婕',14,'女','1999-02-16','13868666661',NULL,2);# 3.可以省略列名,列的顺序和表中列的顺序一致INSERT INTO beautyVALUE(16,'金克斯','女','2000-02-16','13868666661',NULL,2);# 4.支持子查询INSERT INTO beauty(id,NAME,phone)SELECT id,boyname,'110'FROM boys WHERE id
Copy after login

2. Modify update

1. Modify the record syntax of a single table:

update table name
set column=new value, column=new value,...
where filter condition;

Execution order:

update > where > ; set

2. Modify the record syntax of multiple tables:


update table 1 alias
[inner|left|right] join table 2 alias
on connection condition
set column=new value, column=new value,...
where filter condition;

【修改单表的记录】# 修改beautty表中姓周的女生电话为15888888888UPDATE beautySET phone='15888888888'WHERE NAME LIKE '%周%';【修改多表的记录】# 将张无忌的女朋友的电话号改为2333UPDATE boys b1JOIN beauty b2 
ON b1.id=b2.boyfriend_idSET b2.phone='2333'WHERE b1.boyName='张无忌';
Copy after login

3. Delete delete/truncate

delete

Single table deletion:

delete from table name where filter conditions

Multiple table deletion:


delete table 1 alias, table 2 alias
from table 1 alias
inner|left|right join table 2 alias on connection condition
where filter condition;

truncate

Clear Single table:

truncate table table name;

Note:

There is no return value for truncate deletion, and there is a return value for delete deletion.

ComparisonWhether it is possible to add a where conditionWhether it can be rolled backIs there a return value? EfficiencyWhen there are auto-increasing columns in the table##deletetruncate
【delete】# 1.单表删除# 删除以9结尾的女生信息DELETE FROM beauty WHERE phone LIKE '%9';# 2.多表删除# 删除黄晓明的信息以及他女朋友的信息DELETE b1,b2FROM beauty b1INNER JOIN boys b2ON b1.boyfriend_id=b2.idWHERE b2.boyName='黄晓明';【truncate】# 仅支持单表删除全部数据TRUNCATE TABLE boys;
Copy after login
Generally Use delete to delete and then insert data. The self-increasing value starts from the breakpoint
× × × is slightly higher than delete Use truncate to delete and then insert data, which will grow automatically The value starts from 1
4. DML statement exercises

Learned the addition, deletion and modification of DML language statements, Try to complete the following exercises:

Answer: MySQL Lecture 2: DML Data Operation Statements
1. Execute the following sql statement
CREATE TABLE my_employees( Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT ,
userid VARCHAR(10),
department_id INT
);

2.
DESC my_employees
3.
Method 1: INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860 ),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5, 'Ropeburn','Audrey','Aropebur',1550);
TRUNCATE TABLE my_employees;
Method 1:
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel ',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4, 'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;

4.
INSERT INTO users VALUE(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman' ,30),
(5,'Aropebur',40);

5.
UPDATE my_employees SET Last_name='drelxer'
WHERE id=3;

6.
UPDATE my_employees SET salary=1000
WHERE salary
7.
DELETE u,e FROM users u
JOIN my_employees e ON u.userid=e.userid
WHERE u.userid='Bbiri';

8.
DELETE FROM my_employees; DELETE FROM users;

9.
SELECT * FROM my_employees; SELECT * FROM users;

10.
TRUNCATE TABLE my_employees;

More related free learning recommendations:

mysql tutorial(Video)

The above is the detailed content of MySQL Lecture 2: DML Data Operation Statements. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:csdn.net
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template