ABSTRACT: This paper mainly studies the method of using DML statements to manipulate the data in tables.
insert data
grammar
By inserting the incoming data:
1 insert into table name [(column name 1,... (column name n)] values (value 1,... Value n;
Inserting through query data:
1 insert into table name [(column 1,..., column n)] 2 select column 1,..., column n from query table;
Example
1 mysql> insert into score values (null, 'Zhang San', 'Computer', 98); 2 Query OK, 1 row affected (0.00 sec) 3 4 mysql>
Update data
grammar
1 update table name set field 1 = value 1,... Field n = value n 2 [where expression] 3 [order by expression] 4 [limit start number, number of queries]
Example
1 mysql> update score set grade = 99 where id = 1; 2 Query OK, 1 row affected (0.00 sec) 3 Rows matched: 1 Changed: 1 Warnings: 0 4 5 mysql>
Delete data
grammar
1 delete from table name 2 [where expression] 3 [order by expression] 4 [limit expression]
Example
1 mysql> delete from score where id = 1; 2 Query OK, 1 row affected (0.00 sec) 3 4 mysql>
Basic Query
grammar
1 select field column name from table name 2 [where expression] 3 [group by expression] 4 [have expression] 5 [order by expression] 6 [limit start number, number of queries]
Explain
1 Field Column Name: Represents the field of the query, queries multiple fields with, separated, use * number to represent all the fields of the query. Table Name: Represents the source of query data, which can be single or multiple, separated by multiple table names. Where expression: optional, restricting the conditions that the query meets. 4 group by expression: optional, grouped according to the specified field. 5 having expression: optional, qualifying the conditions satisfied by the query, used to use aggregate function qualifying conditions. 6 order by expression: optional, specifying the sort mode, asc and desc. 7 limit start number, query number: optional, set the number of data bars displayed each time.
Example
Create a score sheet:
1 mysql> create table score ( 2 -> id int(10) primary key auto_increment , 3 -> student varchar(20) not null, 4 -> course varchar(20) not null, 5 -> grade int(10) 6 -> ); 7 Query OK, 0 rows affected (0.00 sec) 8 9 mysql>
Insert data:
1 insert into score values(null, 'Zhang San', 'Computer', 98); 2 insert into score values(null, 'Zhang San', 'English?', 53); 3 insert into score values(null, 'Li Si', 'Computer', 48); 4 insert into score values(null, 'Li Si', 'Chinese', 88); 5 insert into score values(null, 'Wang Wu', 'Chinese', 95); 6 insert into score values(null, 'Zhao Liu', 'Computer', 70); 7 insert into score values(null, 'Zhao Liu', 'English?', 92); 8 insert into score values(null, 'Zhao Liu', 'Chinese', 73); 9 insert into score values(null, 'Sun Qi', 'English?', 94); 10 insert into score values(null, 'Zhou Ba', 'Computer', 90); 11 insert into score values(null, 'Zhou Ba', 'English?', 85); 12 insert into score values(null, 'Wu 9', 'Computer', 90); 13 insert into score values(null, 'Wu 9', 'Chinese', 55);
Query all results:
1 mysql> select * from score; 2 +----+---------+-----------+-------+ 3 | id | student | course | grade | 4 +----+---------+-----------+-------+ 5 | 1 | Zhang San | Computer | 98| 6 | 2 | Zhang San | English | 53| 7 | 3 | Li Si | Computer | 48| 8 | 4 | Li Si | Chinese | 88| 9 | 5 | Wang Wu | Chinese | 95| 10 | 6 | Zhao 6 | Computer | 70| 11 | 7 | Zhao Liu | English | 92| 12 | 8 | Zhao Liu | Chinese | 73| 13 | 9 | Sun Qi | English | 94| 14 | 10 | Wednesday 8 | Computer | 90| 15 | 11 | Wednesday 8 | English | 85| 16 | 12 | Wu Jiu | Computer | 90| 17 | 13 | Wu Jiu | Chinese | 55| 18 +----+---------+-----------+-------+ 19 13 rows in set (0.00 sec) 20 21 mysql>
Query the computer's performance:
1 MySQL > select * from score where course = computer'; 2 +----+---------+-----------+-------+ 3 | id | student | course | grade | 4 +----+---------+-----------+-------+ 5 | 1 | Zhang San | Computer | 98| 6 | 3 | Li Si | Computer | 48| 7 | 6 | Zhao 6 | Computer | 70| 8 | 10 | Wednesday 8 | Computer | 90| 9 | 12 | Wu Jiu | Computer | 90| 10 +----+---------+-----------+-------+ 11 5 rows in set (0.00 sec) 12 13 mysql>
Query the number and name of the students who have passed the examination and rank them according to the number:
1 mysql> select id,student from score group by student having min(grade) > 60 order by id; 2 +----+---------+ 3 | id | student | 4 +----+---------+ 5 | 5 | Wang Wu | 6 | 6 | Zhao Liu | 7 | 9 | Sun Qi | 8 | 10 | Zhou Ba | 9 +----+---------+ 10 4 rows in set (0.00 sec) 11 12 mysql>
To be continued