"MySQL" (4) manipulate the records in the data sheet

1. Inserting a record insert

Method One:

INSERT [into] tbl_name [(Clo_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...;

For example:

CREATE TABLE users (id SMALLINT UNSIGNED PRIMARY KEY auto_increment, username varchar () not NULL, password varchar (+) NO  T null, age TINYINT UNSIGNED not null DEFAULT, sex BOOLEAN); # when inserting records without specifying the number of columns, all fields must be assigned the Insert users values (NULL, ' Tom ', ' 123 ', 1); Insert users values (NULL, ' Tom2 ', ' 123 ', 1); Insert users values (DEFAULT, ' Tom3 ', ' 111 ', 28, 1); # Use mathematical expressions You can also add the value Insert users values (default, ' TOM4 ', ' 111 ', 3*7+2/3, 1); # for age default, the defaults are 10INSERT, and the Users values ("default, ' TOM5 ', ' 111 ', DEFAULT, 1); # add more than one record at a time Insert users VALUES (default, ' Tom6 ', ' 111 ', default, 1), (NULL, ' Rose ', MD5 (' 213 '), default, 0);

Method Two:

INSERT [into] tbl_name SET col_name={exp | DEFAULT},...;

The difference between this method and the first approach is that this method can use a subquery (subquery), which can only insert one record at a time.

For example:

INSERT users SET username= ' Ben ', password= ' 456 ';

Method Three:

INSERT [into] tbl_name [(Col_name, ...)] SELECT ...;

Use this method to insert a query result into the specified data table.

2. Single-table update recording update

UPDATE [low_priority] [IGNORE] table_reference SET COL_NAME1={EXPR1 | DEFAULT} [, COL_NAME2={EXPR2 | DEFAULT}] ... [WHERE where_condition];

For example:

# The age of all records plus 5UPDATE users set age=age+5;# update multiple columns of update users set Age=age-id, sex=0;# update ID to even record updates users set age=age+10 WH ERE id%2=0;

3. Single-table deletion record delete

DELETE from Tbl_name [WHERE where_condition];

For example:

DELETE from users WHERE id=6;

Even after the deletion, the ID number is not contiguous, then the new data will be the largest ID number plus one.

4. Parsing when querying for expressions

SELECT select_expr [, select_expr ...] [From Table_references [WHERE whrere_condition] [GROUP by {col_name | position} [ASC | DESC],...] [Having where_condition] [ORDER by {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count offset offset}];

Each expression represents the desired column and must have at least one. Multiple strongly separated by commas. An asterisk (*) indicates all columns. Tbl_name.* can represent all the columns of a named table. A query expression can use [as] alias_name to give it an alias. Aliases can be used for group By,order by or having a clause.

For example:

# view MySQL version select version (); # View current time Select Now (); # View only the first two columns of the Select ID, username from users; SELECT username, id from users; SELECT users.id, users.username from users; Select ID as userid, username as uname from users;# as keyword can be omitted, but as far as possible to write, avoid unnecessary errors Select ID username from users;

both the order of the fields and the result set affect the result set that is queried.

(1). WHERE

Conditional expressions

Filters the records and displays all records if no WHERE clause is specified. In the where expression, you can use the functions or operators supported by MySQL.

(2). GROUP by

Query result grouping

For example:

Select Sex from the Users group by sex;# 1 means that the first occurrence of the field in the SELECT statement is sorted by the select sex from the Users group by 1;

(3). Having

Grouping conditions

For example:

# This age field must be present in the previous select when the having statement has a condition of the term "select sex" from the users GROUP by 1 have age>35;# or an aggregate function select Sex From the users GROUP by 1 have count (ID) >=2;

(4). ORDER by

Sort the results of a query

For example:

# Sort by ID Descending select * from the users order by ID desc;# two fields at the same time age default ascending, id descending select * from the users order by age, ID DESC;

(5). LIMIT

Limit the number of query results returned

For example:

# starting from 1th, return 2 records SELECT * from users limit 2;# starting with 1th, after 2, query 2 records SELECT * from users LIMIT 2 OFFSET 2;# starting from 4th (counting from 0), return 2 record Record SELECT * from users LIMIT 3, 2; SELECT * from the users ORDER by ID DESC LIMIT 2, 2; CREATE table Test (ID TINYINT UNSIGNED PRIMARY KEY auto_increment, username VARCHAR (20)) # Insert data with users older than 30 into test sheet insert Te St (username) SELECT username from users WHERE age>=30;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"MySQL" (4) manipulate the records in the data sheet