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

This article introduces the knowledge of "basic commands of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Create a database

CREATE DATABASE database_name

two。 Delete database

DROP DATABASE database_name

3. Select a database

USE database_name

4. Data type

(1) numerical type

(2) date and time type

(3) string type

5. Create a datasheet

CREATE TABLE table_name (column_name column_type)

6. Delete data tabl

DROP TABLE table_name

7. Update data table information

(1) add table fields

ALTER TABLE table_name ADD new_column DATATYPE

Use the FIRST keyword to adjust the order of the new columns to the first column of the data table

ALTER TABLE table_name ADD new_column DATATYPE FIRST

Use the AFTER keyword to adjust the new column after the specified column of the data table

ALTER TABLE table_name ADD new_column DATATYPE AFTER old_column

(2) Delete table field

ALTER TABLE table_name DROP old_column

(3) modify table field type

ALTER TABLE table_name MODIFY column_name NEW_DATATYPE

(4) modify the field name

ALTER TABLE table_name CHANGE old_column_name new_column_name DATATYPE

8. Insert data

INSERT INTO table_name (column1, column2,column3...columnN)

VALUES

(value1, value2, value3...valueN)

9. Query data

SELECT column1, column2, column3...columnN FROM table_name

(1) you can replace the field name with *, and the SELECT statement returns all fields of the table.

Example: SELECT * FROM table_name

(2) you can use the where statement to include any condition.

Example: SELECT * FROM table_name WHERE column=1

(3) you can use the LIMIT attribute to set the number of records returned

Example: return the first three records of the query result

SELECT * FROM table_name LIMIT 3

Example: the third record that returns the query result

SELECT * FROM table_name LIMIT 2 1 (2 refers to the number of data (counting from 0), and 1 refers to how many pieces of data are returned from 2)

(4) you can use OFFSET to specify the offset to start the query. By default, the offset is 0.

Example: SELECT * FROM table_name LIMIT 2 OFFSET 3 equals SELECT * FROM table_name LIMIT 2

10. Update data

UPDATE table_name SET column1=value1, column2=value2

WHERE condition

11. Delete data

DELECE FROM table_name

WHERE condition

12.LIKE clause

The% sign is used in the LIKE clause to represent any character, and its effect is similar to that of * in a regular expression. If% is not used, the effect of LIKE is equivalent to =

SELECT * FROM table_name

WHERE column1 LIKE condtion%

13.UNION

SELECT column1, column2, column3...columnN FROM table_a

[WHERE condition]

UNION [ALL | DISTINCT]

SELECT column1, column2, column3...columnN FROM table_b

[WHERE condition]

The role of UNION in joining two query result sets

The function of DISTINCT is to deduplicate the two result sets, which is already the result of DISTINCT by default.

The function of ALL is not to de-reprocess the two result sets.

14.ORDER BY

SELECT * FROM table_name

ORDER BY column1 [ASC | DESC]

ASC: sorts the result sets in column1 ascending order, using ascending order by default

DESC: sort result sets in descending order of column1

15.GROUP BY

Groups data according to specified columns (can be one or more columns), usually used with evaluation functions such as COUNT () and SUM (), AVG (), etc.

Example: group data according to column1, and count the number of records for each type of data

SELECT column1, COUNT (*) FROM table_name

GROUP BY column1

WITH ROLLUP can do the same statistics based on the statistical result set of GROUP BY (SUM,AVG....).

Example: suppose you have the following table: name= name, website= website, access_count= visit record

Namewebsiteaccess_count Zhang Sandu 3 Li Si Sina 5 Wang Wu Taobao 4 Zhang San Sina 2 Li 400du 1 Wang Wu Sohu 4 Zhao Liu Sohu 5

Execute the following code:

SELECT name, SUM (access_count) FROM table_name

GROUP BY name WITH ROLLUP

Get:

AmeSUM (access_count) Zhang San 5 Li Si 6 Wang Wu 8 Zhao Liu 5 NULL24

16.INNER JOIN

INNER JOIN is called inner join or equivalent join, which gets the record of the matching relationship between the fields in two tables.

Example: SELECT table1.column1, table1.column1, table2.column3 FROM table1

INNER JOIN table2

ON condition

17.LEFT JOIN

LEFT JOIN is called a left join, which gets all the records on the left, and the records that are not in the right table are filled with NULL.

Example: SELECT table1.column1, table1.column2, table2.column3 FROM table1

LEFT JOIN table2

ON condition

18.RIGHT JOIN

RIGHT JOIN is called a right join, which gets all the data on the right, and the records that are not in the left table are filled with NULL.

Example: SELECT table1.column1, table2.column2, table2.column3 FROM table1

RIGHT JOIN table2

ON condition

This is the end of the content of "MySQL Database basic commands". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.