DML: add, delete and modify data in the table

1. Add data

Syntax:

insert into table name (column name 1, column name 2,... Column name n) values (value 1, value 2,... Value n);

be careful:

1. The column name should correspond to the value one by one

2. If the column name is not defined after the table name, values are added to all columns by default: insert into table name values (value 1, value 2,... Value n)

3. When dealing with numeric types, other types need to be enclosed in quotation marks (either single or double)

4. Delete data

Syntax: delete from table name [where condition]

be careful:

1. If no condition is added, all records in the table will be deleted;

2. If you want to delete all records: 1. delete from table name: – not recommended. How many records will be deleted, 2.TRUNCATE TABLE table name; – delete the table first, and then create the same table

3. Modify data

Syntax: update table name set column name 1 = value 1, column name 2 = value 2, [where condition]

Note: 1. If no conditions are added, all records in the table will be modified;

##DQL: query records in a table

select from table name;

INSERT INTO stu(id,NAME,age)VALUES(1,'zhang wuji',20);
INSERT INTO stu VALUES(2,'pofenx',17,99.9,NULL,NULL);
INSERT INTO stu VALUES(3,"Zhang San",17,99.99,"1893-01-16",NULL);
SELECT*FROM stu; 
SHOW DATABASES;
SELECT*FROM stu;
UPDATE stu SET age =17 WHERE id = 3;
UPDATE stu SET age =18,score=100 WHERE id =2;
UPDATE stu SET age =20;

DQL: querying records in a table

select*from table name;

1. grammar

   select

   Field list

   from

   Table name list

   where

   Condition list

   gorup by

   Grouping field

   having

   Conditions after grouping

   order by

   sort

   limit

   Paging limit

   1. Basic query

   2. Query of multiple fields: select field name 1, field name 2,... from table name;

Note: if you query all fields, you can use * instead of the field list

1. Remove duplicates: distinct
2. Calculation column: generally, four operations can be used to calculate the values of some columns (generally, only numerical calculations will be performed)

ifnull (expression 1, expression 2): the calculation results of null involved operations are null. If the field is the replacement value after nul

Alias: as:as can also be omitted

CREATE TABLE student(
id INT ,
NAME VARCHAR(20) ,
age INT ,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT );


INSERT INTO student(id,NAME,age,sex,address,math,english)
 VALUES
  (1,'Jack Ma',55,'male','Hangzhou',66,78),
 (2,'pony ',45,'female','Shenzhen',98,87),
 (3,'Ma Jingtao',55,'male','Hong Kong',56,77),
 (4,'Liuyan',20,'female','Hunan',76,65),
 (5,'Liu Qing',20,'male','Hunan',86,NULL),
 (6,'Lau Andy',57,'male','Hong Kong',99,99),
 (7,'Madder',22,'female','Hong Kong',99,99),
 (8,'Demacia',18,'male','Nanjing',56,65); 
SELECT * FROM student;
-- Remove duplicate result sets;
SELECT DISTINCT address FROM student;
SELECT DISTINCT NAME,address FROM student;

-- calculation math and english Sum of
SELECT NAME,math,english,math+english FROM student;
-- If so null The calculation results are null
SELECT NAME ,math,english,math+IFNULL(english,0) FROM student;

-- Alias
SELECT NAME ,math,english,math+IFNULL(english,0) AS Total score FROM student;
SELECT NAME full name ,math mathematics,english English,math+IFNULL(english,0) Total score  FROM student;

1. Condition query

2. where clause followed by condition

3. Operators: >, <, < =, > =, =, < >, between... And, in (set), like (fuzzy query), is null,and or & &, or or or |, not or!;

• like: fuzzy query:: single arbitrary character,% multiple arbitrary characters

CREATE TABLE student(
id INT ,
NAME VARCHAR(20) ,
age INT ,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT );


INSERT INTO student(id,NAME,age,sex,address,math,english)
 VALUES
  (1,'Jack Ma',55,'male','Hangzhou',66,78),
 (2,'pony ',45,'female','Shenzhen',98,87),
 (3,'Ma Jingtao',55,'male','Hong Kong',56,77),
 (4,'Liuyan',20,'female','Hunan',76,65),
 (5,'Liu Qing',20,'male','Hunan',86,NULL),
 (6,'Lau Andy',57,'male','Hong Kong',99,99),
 (7,'Madder',22,'female','Hong Kong',99,99),
 (8,'Demacia',18,'male','Nanjing',56,65); 
SELECT * FROM student;
-- Remove duplicate result sets;
SELECT DISTINCT address FROM student;
SELECT DISTINCT NAME,address FROM student;

-- calculation math and english Sum of
SELECT NAME,math,english,math+english FROM student;
-- If so null The calculation results are null
SELECT NAME ,math,english,math+IFNULL(english,0) FROM student;

-- Alias`student`
SELECT NAME ,math,english,math+IFNULL(english,0) AS Total score FROM student;
SELECT NAME full name ,math mathematics,english English,math+IFNULL(english,0) Total score  FROM student;

-- Query age((small)(be equal to)(Not equal to 20 years old

SELECT * FROM student WHERE age >20; 
SELECT * FROM student WHERE age <20; 
SELECT * FROM student WHERE age = 20; 
SELECT * FROM student WHERE age != 20; 
 -- Query age is greater than or equal to 20 and less than or equal to 30
 SELECT * FROM student WHERE age>= 20 && age <=30;
  SELECT * FROM student  WHERE age>= 20 AND age <=30;
   SELECT * FROM student WHERE age BETWEEN 20 AND 20 ;
    -- Query the information of age 22, 19 and 25
    SELECT * FROM student WHERE age = 20 OR age =19 OR age =25;
     SELECT * FROM student WHERE age IN (18,22,25);
     --  Query English program is null
     SELECT * FROM student WHERE english =NULL -- incorrect, null out-of-service=judge
     SELECT * FROM student WHERE english IS NULL;
     -- Query English score is not null
     SELECT * FROM student WHERE english IS NOT NULL;
     -- What are the surnames of horses
     SELECT * FROM student WHERE NAME LIKE 'horse%';
     -- The second word of the query name is the humanized person
     SELECT * FROM student WHERE NAME LIKE '_turn%';
     -- A person whose name is three words
     SELECT * FROM student WHERE NAME LIKE '___';
     -- Query the person whose name contains de;
     SELECT * FROM student WHERE NAME LIKE '%virtue%';