[MySQL] (4) operation data table records, mysql DATA

[MySQL] (4) operation data table records, mysql DATA

1. INSERT record

Method 1:

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

For example:

Create table users (id smallint unsigned primary key AUTO_INCREMENT, username VARCHAR (20) not null, password VARCHAR (32) not null, age tinyint unsigned not null default 10, sex BOOLEAN ); # INSERT record. If the number of columns is not specified, all fields must be assigned an INSERT users VALUES value (NULL, 'Tom ', '123', 25, 1 ); INSERT users VALUES (NULL, 'tom2', '000000', 28, 1); INSERT users VALUES (DEFAULT, 'tom3', '000000', 28, 1 ); # using mathematical expressions, you can also add values insert users VALUES (DEFAULT, 'tom4 ', '000000', 3*7 + 111, 1); # To age DEFAULT, the DEFAULT value 10 INSERT users VALUES (DEFAULT, 'tom5 ', '000000', DEFAULT, 1) will be used; # add multiple records at a time INSERT users VALUES (DEFAULT, 'tom6 ', '123', DEFAULT, 1), (NULL, 'Rose ', md5 ('123'), DEFAULT, 0 );

Method 2:

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

The difference between this method and the first method is that this method can use SubQuery. This method can insert only one record at a time.

For example:

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

Method 3:

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...;

You can use this method to insert query results to a specified data table.

2. UPDATE a single table UPDATE record

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}]... [WHERE where_condition];

For example:

# Add 5 UPDATE users SET age = age + 5 to the age of all records; # UPDATE multiple columns UPDATE users SET age = age-id, sex = 0; # UPDATE users SET age = age + 10 WHERE id % 2 = 0;

3. DELETE a single table deletion record

Delete from tbl_name [WHERE where_condition];

For example:

DELETE FROM users WHERE id=6;

Even after deletion, the newly added data will still be added with the largest ID number.

4. parsing when querying the table

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}];

Each expression indicates the desired column. Each expression must have at least one column. Multiple keywords are separated by commas. Asterisk (*) indicates all columns. Tbl_name. * indicates all columns in the naming table. You can use [AS] alias_name to assign an alias to a query expression. Aliases can be used for group by, order by, or HAVING clauses.

For example:

# View MySQL version select version (); # view the current time select now (); # view only the first two columns of 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; # The AS keyword can be omitted, but write AS much AS possible to avoid unnecessary errors SELECT id username FROM users;

The order and result set of fields affect the query result set.

(1). WHERE

Conditional expressions

Filter records. If no WHERE clause is specified, all records are displayed. In the WHERE expression, functions or operators supported by MySQL can be used.

(2). GROUP

Query Result Group

For example:

SELECT sex FROM users group by sex; #1 indicates sorting BY the first field in the SELECT statement SELECT sex FROM users group by 1;

(3). HAVING

Grouping Conditions

For example:

# When the HAVING statement has the age condition, the SELECT sex field must appear in the preceding SELECT statement. age FROM users group by 1 HAVING age> 35; # Or an aggregate function SELECT sex, age FROM users group by 1 HAVING count (id)> = 2;

(4). ORDER

Sort query results

For example:

# SELECT * FROM users order by id DESC in descending order by id; # sort BY two fields at the same time BY age in ascending order by default, SELECT * FROM users order by age, id DESC in descending order by id;

(5). LIMIT

Limit the number of returned results

For example:

# Return two records FROM 1st: SELECT * FROM users LIMIT 2; # FROM 1st, query two records: SELECT * FROM users LIMIT 2 OFFSET 2; # Starts FROM 4th records (starts FROM 0), returns two records: SELECT * FROM users LIMIT 3, 2; SELECT * FROM users order by id desc limit 2, 2; create table test (id tinyint unsigned primary key AUTO_INCREMENT, username VARCHAR (20); # INSERT users older than 30 into the test table insert test (username) SELECT username FROM users WHERE age> = 30;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.