Shulou(Shulou.com)05/31 Report--

This article will explain in detail the sample analysis of the basic knowledge points of mysql database. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Database

1. Modify the data table

Add a column:

ALTERTABLE tab_name ADD col_name column_ Defie [first | AFTER col_name]

You can specify the position relationship of the new column, either at the beginning (FIRST) or after a column (AFTER... Otherwise, the new column is last by default

Add multiple columns:

ALTERTABLE tab_name ADD (col_name1 column_defi1,col_name2 column_defi2...)

Delete a column:

ALTERTABLE tab_name DROP col_name

Delete multiple columns or add another column after deletion, just add a comma after drop col_name, and then write drop/add.

Add constraints:

ALTERTABLE tab_name ADD PRIMARY KEY (index_column)

Primary key can be replaced with other constraints UNIQUE and FOREIGN KEY, and the default constraint is SET DEFAULT.

Delete the constraint:

ALTERTABLE tab_name DROP {INDEX | KEY} index_name

Delete the foreign key:

ALTERTABLE tab_name DROP FOREIGN KEY fkey_name

The foreign key constraint name fkey_name can be obtained by viewing the constraint item through show create table tab_name;, for example

CONSTRAINT `users_ibfk_ 1` FOREIGN KEY (`pid`) REFERENCES

Modify the column definition:

ALTERTABLE tab_name MODIFY col_name col_defi

Modify the column name:

ALTERTABLE tab_name CHANGE old_colname new_name col_defi

Modify the name of the data table:

RENAMETABLE old_name TO new_name; II. Data table operation

Insert record:

INSERT tab_name [(col_name)] VALUES (val1,val2...)

The column name is optional. If the column name is not set, it is inserted for each column by default. The second insertion method: INSERT tab_name SET col_name=val... The third method: insert tab_name select. To insert the query results into the data table

Update the record:

UPDATE tab_name SET col_name=val [WHERE condition]

If the where condition is left empty, all records are updated by default.

Delete record:

DELETE FROM tab_name [WHERE condition]

Delete records that meet the requirements, if you do not enter where, delete them all.

Query record:

SELECT col_name1,col_name2... / * Select the column name to be queried * / FROM tab_name/* Select the data table to be queried * / WHERE condition/* query condition * / GROUP BY col_name [ASC | DESC] / * query result grouping * / HAVING condition/* to query grouping by row filtering * / ORDER BY col_ name [ASC | desc] / * sort the query results by row * / LIMIT [N1,] N2 * return N2 results starting from N1 If N1 is left empty, it returns * / 3 by default from the beginning.

The connection between the parent query and the subquery is connected by comparison symbols. When the subquery returns multiple results, the results can also be modified by ANY and ALL.

Multi-table update: update this table with reference to another table.

Inner join: INNERJOIN ON join_condition, which returns the items that meet the criteria in the left and right tables.

Left outer join: LEFTJOIN ON join_condition, returns all the items in the left table and the right table that meet the conditions.

Left outer join: RIGHT JOIN ON join_condition, which returns all the items in the right table that meet the conditions in the left table.

4. Database function

CONCAT ('axiomy. )

Concatenate multiple strings of aformab

CONCAT_WS ('the last minute, the last, the last. )

Concatenate multiple strings with s as the connector

FORMAT (fjinn)

Display the number f in n decimal places

LOWER () / UPPER ()

Convert content to lowercase / uppercase

LEFT/RIGHT ('squarry, n)

Gets the first n characters of the left / right side of the string

LENGTH ()

Get string length

LTRIM/RTRIM/TRIM ()

Remove the left / right / both sides of the string

REPLACE ('s recording, writing, etc.)

Replace the a character in the string s with b

SUBSTRING ('s magic, no, e)

Intercept the e characters of a string starting at the nth bit

IS NULL

Judge the air

N IN (a dint, b, c... )

Determine whether n is in the listed value

N BETWEEN an AND b

Judge whether n is between an and b

This is the end of this article on "sample Analysis of the basic knowledge points of mysql Database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.