MySQL table operations

MySQL table operations:
Insert:
Insert into indicates () ....
Create:
CREATE TABLE User (host char, user char (16))
Select Host,user from Mysql.user
The structure, fields and records of the table to be queried when creating the table
CREATE TABLE User_new1 (IP char, username char (16))
Select Host as ip,user as username from Mysql.user;
It is still the structure, field and record of the table that gets the query when the table is created, but if it is not followed by the as name,
There will be four fields, and the two fields you create will appear, but the record will not be there. So I want to change my name
Must be used as
Select Host as hostname, user as username from Mysql.user;
You can change the name of the displayed field of the query at the time of the query, using the AS
Update:
Update db1.t1 SET field = value where id>10 or (and) ID < 30;
The following conditions, when the conditions or conditions are met and what conditions and conditions are met
Delete:
Delete from table name condition;
Delete records for which conditions are met in the table
Clear the table:
TRUNCATE TABLE name;
Single-table query syntax:
SELECT * from table name;
* Can be all fields in a table, or it can be a field or a record under a field
Select distinct field from table name;
Go to heavy usage, query to the virtual table to go heavy
Select field, int field * integer number from table name;
The same can be used as the result of the numerical operation of the record to rename, or do not add as the purpose of renaming the word
Select Concat (' View info: ', field) from table name;
Using the concat () function, you can stitch strings together, which makes the displayed records clearer.
Select Concat_ws (': ', field, field) from table name;
Show Effects field: Field
Concat_ws () can be spelled in the middle of the data
In the order of single-table queries, the first is the constraint, then the grouping, then the filter, then go to repeat, and then sort,
The last is the restriction condition
A where constraint, which can be a comparison operator primarily used to determine what the condition is or the size range
For example:
To find an ID greater than 5 in the class table
SELECT * from class where ID >5
To find an ID equal to 5 in the class table
SELECT * from class where ID =5
Other operators ditto, no difference
You can use between to specify the range.
SELECT * from class where ID between 1 and 5
You can use in to determine the specific range
SELECT * from class where ID in (1,5)
You can use like to constrain records that have partial conditions
SELECT * from class where name like ' e% '
See you can use and or not
Constraints can be used as an equal sign to determine whether certain strings are specific
Or use an operator with a range to determine the range of numbers
Select Post,count (ID) from the class group by post
Group BY grouping, grouping the records in a table in a piece of the way, which means
Records of the same field values will be aggregated into one piece for use.
Therefore, after grouping, the where constraint is not used as a single constraint as before, so the where constraint
Before you put it in a group
Group can operate using aggregate functions, Count fields, Max Max, Min min, avg average
Sum and.
The purpose of grouping is to process records for a class in groups, rather than to process individual records
You can also use the Group_concat () function to query the records of the corresponding fields of each grouping
Filter conditions have
Having been used after grouping, that is to say, having the filter conditions used, can only be grouped aggregate function
Because the record becomes a piece of processing after grouping, it cannot be used for the filtering of heads-Up records, so only the aggregate function
Having can also do where that constraint, but generally used for grouping
Where cannot be used after grouping using aggregate functions as conditions
Order By default is ascending, or it can be set to descending with DESC
You can set the ascending order by field data first, if the data is the same, then the ascending order by ID
SELECT * from-Employee ORDER BY-age Asc,id desc;
Limit limits are placed at the end
SELECT * from class ORDER BY id desc LIMIT 1;
This shows the descending order by ID, the ID of the largest of the top, and then limit the only one, take out is the ID of the largest person
Elect * from employee where name RegExp ' ^e.*n$ ';
Write a regular on the back of RegExp

MySQL table operations