Addition, deletion, query and modification of [mysql] table (basic)

Catalog

1.CURD

2. newly added （Create）

2.1 Single line data + Insert all columns

2.2 Multi row data + Specify column insertion

3. Inquire about （Retrieve）

3.1 Full column query

3.2 Specified column query

3.3 The query field is an expression

3.4 Alias

3.5 duplicate removal ：DISTINCT

3.6 Sort ：ORDER BY

3.7 Conditions of the query ：WHERE 

3.8 Paging query ：LIMIT

4. modify （Update）

5. Delete （Delete）

1.CURD

• notes ： stay SQL Can be used in “-- Space + describe ” To indicate a comment
• CRUD That is, increase (Create)、 Inquire about (Retrieve)、 to update (Update)、 Delete (Delete) An acronym for four words

2. newly added （Create）

insert [into] table_name
    [(column [ , column] ...)]
    value (value_list) [ , (value_list)] ...

value_list: value, [ , value] ...

Example ： 

--  Create a student table 
drop table if exists student;
create table student (
    id int,
    username varchar(20) comment ' The student's name ',
    sex bit comment ' Gender ',
    resume text,
    amount decimal(11, 2),
    birthday datetime
);

2.1 Single line data + Insert all columns

--  Insert two records ,value_list  The number must be consistent with the number and order of the columns that define the table 
insert into student values (1, ' Zhang San ', 0, ' resume ', 1234.11, '2000-01-01');
insert into student values (2, ' Li Si ', 1, null, 1234.11, '2000-01-01');

2.2 Multi row data + Specify column insertion

--  Insert two records ,value_list  The quantity must be consistent with the number and order of the specified columns 
--  Specify column insertion 
insert into student(id, username) values(2, ' Female duck LadyGaga');

--  Multi line insertion 
insert into student(id, username) values
(1, ' Roasted Duck '),
(2, ' Go to Beijing for the exam ');

3. Inquire about （Retrieve）

select
    [distinct] {* | {column [, column] ...}
    [from table_name]
    [where ...]
    [order by column [ASC | DESC], ...]
    limit ...

Example ：

--  Create test scores 
drop table if exists exam_result;
create table exam_result (
    id int,
    name varchar(20),
    chinese decimal(3,1),
    math decimal(3,1),
    english decimal(3,1)
);

--  Insert test case 
insert into exam_result (id, name, chinese, math, english) values
    (1,' Tang Sanzang ', 67, 98, 56),
    (2,' The Monkey King ', 87.5, 78, 77),
    (3,' Pig Wuneng ', 88, 98.5, 90),
    (4,' Cao mengde ', 82, 84, 67),
    (5,' Liu Xuande ', 55.5, 85, 45),
    (6,' king of Wu in the Three Kingdoms Era ', 70, 73, 78.5),
    (7,' Song Gongming ', 75, 65, 30);

3.1 Full column query

--  Generally, it is not recommended to use  *  Make a full column query 
-- 1.  The more columns you query , It means that the larger the amount of data that needs to be transferred ;
-- 2.  May affect the use of the index .
select * from exam_result;

3.2 Specified column query

--  The order of the specified columns does not need to be defined in the order of the table 
select id, name, english from exam_result;

3.3 The query field is an expression

--  The expression does not contain fields 
select id, name, 10 from exam_result;
--  The expression contains a field 
select id, name, english + 10 from exam_result;
--  The expression contains multiple fields 
select id, name, chinese + math + english from exam_result;
--  If the field is numeric , Operators can be used to calculate 
select id, name, chinese*2 from exam_result;

3.4 Alias

Specify aliases for columns in query results , Represents the returned result set , Use alias as the name of the column .

select column [as] alias_name [...] from table_name;

--  Result set , The column name of the header = Alias 
select id, name, chinese + math + english  Total score  from exam_result;
-- chinese*2 As the query field name , It doesn't look good , Aliases are usually used 
select id i, name as u, chinese*2 c from student;
--  Table names can also use aliases 
select stu.id i, stu.name as u, stu.chinese*2 c from exam_result stu;

3.5 duplicate removal ：DISTINCT

Use distinct Keyword to de duplicate a column of data ：

-- 98 The score is repeated 
select math from exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
7 rows in set (0.00 sec)

--  De duplication results 
select distinct math from exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)

3.6 Sort ：ORDER BY

-- asc  In ascending order （ From small to large ）
-- desc  For the descending order （ From big to small ）
--  The default is  asc
select ... from table_name [where ...]
order by column [asc | desc], [...];

1. No, ORDER BY Clause , The order of return is undefined , Never rely on this order
2. NULL Data sorting , Treat as less than any value , The ascending order appears at the top , Descending order appears at the bottom

   -- Ascending 
   select id, username,amount from student order by id, amount;
   -- Descending 
   select id, username,amount from student order by id, amount desc;
   

3. Use expression And Alias Sort

   --  Query students and total scores , From high to low 
   select name, chinese + english + math from exam_result
   order by chinese + english + math desc;
   
   select name, chinese + english + math total from exam_result
   order by total desc;

4. You can sort multiple fields , The order of priority follows the order of writing

   --  Check the results of each subject , Press... In turn   Mathematical descending order , English ascending order , The ascending order of Chinese shows 
   select name, math, english, chinese from exam_result
   order by math desc, english, chinese;

3.7 Conditions of the query ：WHERE 

Comparison operator ：

Logical operators ：

notes ：

1. WHERE Conditions can be expressed as , But you can't use aliases .
2. AND Has a higher priority than OR, When used at the same time , You need to use parentheses () The priority part of the package

Example ：

• The basic query ：

  --  Check the students who fail in English and their English scores  ( < 60 )
  select name, english from exam_result where english < 60;
  --  Query students whose Chinese scores are better than English scores 
  select name, chinese, english from exam_result where chinese > english;
  --  The total query score is  200  Students with the following scores 
  select name, chinese + math + english  Total score  from exam_result
      where chinese + math + english < 200;

• and And or：

  --  Query language score is greater than 80 branch , And the English score is greater than 80 Classmate 
  select * from exam_result where chinese > 80 and english > 80;
  --  Query language score is greater than 80 branch , Or English scores greater than 80 Classmate 
  select * from exam_result where chinese > 80 or english > 80;
  --  Observe and  and  or  The priority of the ：
  select * from exam_result where chinese > 80 or math>70 and english > 70;
  select * from exam_result where (chinese > 80 or math>70) and english > 70;

• Range queries ：                                                                                                                                 1.between ... and ... 

  --  Query Chinese scores in  [80, 90]  Students and Chinese scores 
  select name, chinese from exam_result where chinese between 80 and 90;
  --  Use  and  It can also be realized 
  select name, chinese from exam_result where chinese >= 80 and chinese <= 90;
  

   2.in

  --  The query math score is  58  perhaps  59  perhaps  98  perhaps  99  Students and math scores 
  select name, math from exam_result where math in (58, 59, 98, 99);
  --  Use  or  It can also be realized 
  select name, math from exam_result where math = 58 or math = 59 or math
  = 98 or math = 99;

• Fuzzy query ：LIKE

  -- %  Match any number of （ Include  0  individual ） character 
  select name from exam_result where name like ' Grandchildren %';--  Match to the monkey king 、 king of Wu in the Three Kingdoms Era 
  -- _  Match a strict arbitrary character 
  select name from exam_result where name like ' Grandchildren _';--  Match to Sun Quan 

• NULL Query for ：IS [NOT] NULL

  --  Inquire about  qq_mail  Names of known students 
  select name, qq_mail from student where qq_mail is not NULL;
  --  Inquire about  qq_mail  Unknown classmate name 
  select name, qq_mail from student where qq_mail is NULL;

3.8 Paging query ：LIMIT

--  Starting subscript is  0
--  from  0  Start , Screening  n  Bar result 
select ... from table_name [where ...] [order by ...] limit n;
--  from  s  Start , Screening  n  Bar result 
select ... from table_name [where ...] [order by ...] limit s, n;
--  from  s  Start , Screening  n  Bar result , More definite than the second usage , It is recommended to use 
select ... from table_name [where ...] [order by ...] limit n offset s;

Case study ： Press id paging , each page 3 Bar record , Respectively shows The first 1、2、3 page

--  The first  1  page 
select id, name, math, english, chinese from exam_result order by id limit 3 offset 0;
--  The first  2  page 
select id, name, math, english, chinese from exam_result order by id limit 3 offset 3;
--  The first  3  page , If the results are insufficient  3  individual , No impact 
select id, name, math, english, chinese from exam_result order by id limit 3 offset 6;

4. modify （Update）

update table_name set column = expr [, column = expr ...]
[where ...] [order by ...] [limit ...]

Example ：

--  Change the math score of Monkey King to  80  branch 
update exam_result set math = 80 where name = ' The Monkey King ';
--  Change Cao mengde's math score to  60  branch , The Chinese score is changed to  70  branch 
update exam_result set math = 60, chinese = 70 where name = ' Cao mengde ';
--  Count the bottom three of the total score  3  A student's math score plus  30  branch 
update exam_result set math = math + 30 order by chinese + math + english limit 3;
--  Update the Chinese scores of all students to the original  2  times 
update exam_result set chinese = chinese * 2;

5. Delete （Delete）

delete from table_name [where ...] [order by ...] [limit ...]

Example ：

--  Delete the exam results of Monkey King 
delete from exam_result where name = ' The Monkey King ';
--  Delete the entire table data 
--  Prepare the test sheet 
drop table if exists for_delete;
create table for_delete (
    id int,
    name varchar(20)
);
--  Insert test data 
insert into for_delete (name) values ('A'), ('B'), ('C');
--  Delete the whole table data 
delete from for_delete;