Chapter 14: (DML) Data Manipulation Language

Back to Mysql database theory and practice

Painted # DML statements

Data Manipulation Language Data Manipulation Language Keyword: INSERT, UPDATE, and DELETE

USE stu0906;
CREATE TABLE stuinfo(
  id INT,
  stuname VARCHAR(20) NOT NULL,
  gender CHAR,
  borndate TIMESTAMP,
  seat INT
);
Copy the code

# 1. Insert

Insert into table name (insert into table name, insert into table name) Values (1, 2, 3…) ; Insert into table name (insert into table name, insert into table name…) Values (1, 2, 3…) ,(value 1, value 2, value 3…) ; Method 2: Features: (1) The value must be consistent with the type and constraint of the field. Date and character types require single quotation marks. Non-empty fields must be explicitly inserted. (3) The number of fields and values must be the same. (4) The list of fields can be omitted. The default is all fields

SELECT * FROM stuinfo; #1. Simple insertion

INSERT INTO stuinfo (id, stuname, gender, borndate, seat) VALUES (1, 'house', 'woman', '1999-1-1', 10);Copy the code

#2. Non-empty fields must have an explicitly inserted value. # can be null field, can not explicitly insert value # method 1: field and value are omitted

INSERT INTO stuinfo (id, stuname, gender, borndate) VALUES (2, 'dragon', 'woman', '1999-1-1');Copy the code

# Method 2: Fields are not omitted and values are filled with null

INSERT INTO stuinfo (id, stuname, gender, borndate, seat) VALUES (3, 'he', 'woman', '1999-1-1', NULL);Copy the code

#3. The number of fields and values must be consistent

INSERT INTO stuinfo (id, stuname, gender, borndate, seat) VALUES (4, 'good', 'woman', '1999-1-1', NULL);Copy the code

#4. The list of fields can be omitted; default is all fields

INSERT INTO stuinfo VALUES(5,' read ',' male ','1999-1-1');Copy the code

#5. Insert multiple rows

Way # 1: INSERT INTO stuinfo VALUES (6, 'closed', 'woman', '1996-1-1', 9), (7, 'round', 'woman', '1979-1-1', 11), (8, 'red', 'woman', '1973-1-1', 12); # Method 2: INSERT INTO STUinfo SELECT 9,' stuinfo ',' female ','1985-1-1',13 UNION ALL SELECT 10,' shan ',' female ','1985-1-1',13 UNION ALL SELECT 11, 'Dan', 'female' and '1985-1-1', 12, 13 UNION ALL the SELECT 'spring', 'female' and '1985-1-1 s' 13Copy the code

# Add: Set autogrow columns

DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo( id INT PRIMARY KEY AUTO_INCREMENT, stuname VARCHAR(20) NOT NULL, gender CHAR, borndate TIMESTAMP, seat INT ); SELECT * FROM stuinfo; INSERT INTO stuinfo (stuname, gender, borndate, seat) SELECT 'Gu Ling 1', 'woman', '1985-1-1', 13 UNION ALL SELECT 'SAN SAN yuan 2', 'female' and '1985-1-1', 13 UNION ALL SELECT 'eternal song dan-dan famous 3', 'woman', '1985-1-1', 13 UNION ALL SELECT 'li 4', 'female' and '1985-1-1 s' 13Copy the code

# 2. Modify

Update table_name set table_name = new table_name where table_name = new table_name = new table_name

UPDATE STUinfo SET stuname = ‘stuname’ WHERE id = 1; UPDATE STUinfo SET stuname = ‘1990-12-12’ WHERE id = 2; Example 1: Change the seat number of the student whose major name is HTML to 10 and change the major name to page #sql92

UPDATE stuinfo s,major M SET s.eat = 10,m. 'majorname' =' WWW 'WHERE s.' majorid '= M.' id 'AND M.' majorname '=' HTML ';Copy the code

#sql99

UPDATE STUinfo s JOIN major m ON S. 'majorid' =m. 'id' SET s.eat = 1, M. 'majorname' ='BigData' WHERE M. 'majorname' ='BigData';Copy the code

# 3. Delete

★ delete from table where condition; SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL Sql99: Thinking mode 2: Use TRUNCATE syntax: TRUNCate table table

DELETE FROM stuinfo WHERE YEAR(borndate) = 2018; DELETE FROM stuinfo; TRUNCATE TABLE stuinfo ; Example 3: Delete the user whose professional name is BigData

  DELETE  s FROM stuinfo s,major m WHERE s.majorid = m.id AND m.majorname = 'BigData';
  SELECT * FROM stuinfo;
  SELECT * FROM major;
Copy the code

#【 interview questions 】 A comparison between delete and truncate

Delete Can add A WHERE condition TRUNCATE cannot add a WHERE condition. 2. High TRUNCATE efficiency. 3. Truncate Deletes a table with self-growing columns, and the values of the self-growing columns are added starting from 1. Delete Rollback of transactions truncATE does not support rollback of transactions. Delete Belongs to DML TRUNCATE belongs to DDL

  SELECT * FROM major;
  INSERT INTO major(majorname)
  VALUES('java'),('BigData'),('Python');
  DELETE FROM major;
  TRUNCATE TABLE major;
Copy the code