Shulou(Shulou.com)06/01 Report--

This article mainly introduces the basic sentence operation of MySQL, which has a certain reference value, friends who need can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Database operation statement

Create

Create database database name

View all databases

Show databases .

View the specified database creation table statement and character set

Show create database database name

Delete database

Drop database database name

Modify database character set-understand

Alter database database name character set 'character set'

Switch database

Use database name

View the current database name

Select database ()

Addition, deletion, modification and query of data table structure

After you have a database, if you want to save the data, you must first have a data table in the database.

Create a datasheet:

Use database name

View the table:

Show tables; views all the tables in the database

Desc table name; view information about the columns of the table (table structure)

Constraints when a single table is created

In order to prevent duplicate names, ensure the integrity and validity of the data stored in the data table.

Common syntax for constraints: column name data type constraints

There can be only one primary key in a table: id int primary key auto_increment

Data table structure deletion: table names, column names, class types, class constraints can be added, deleted and modified.

Add columns: alter table table name add / delete / change column name type (length) constraint

Modify column type, length, and constraints: alter table table name modify column name type (length) constraint

Modify existing column name: alter table table name change old column name new column name type (length) constraint

Modify an existing column: alter table table name drop column name

Modify table name: rename table old table name to new table name

Modify the character set of the table: alter table table name character set coding set

Datasheet deletion: drop table table name

Summary of data sheet

Datasheet creation (important)

Create table table name (

Column name data type constraint

Column name data type constraint

.

);

View tabl

Show tables: view all tables

Show create table table name: view table statement and character set

Desc table name: view the table structure.

Modify the statement of the table (understand)

Alter table table name (add | modify | drop | change) column name type (length) constraint.

Rename table old table name to new table name

Delete tabl

Drop table table name

Addition, deletion, modification and query of the contents of a simple data table (very important)

Insert statement-an increase in data records

CRUD:create 、 read/retrieve 、 update 、 delete

The most frequent operation on the database in Java code is the CRUD operation on the data in the table.

The location where the data is stored: table.

Method 1: write all

Syntax: insert into table name (column name, column name, column name...) Values (value, value, value...)

Note:

1. The value corresponds to the column one by one. You need to write as many values as there are columns. If a column has no value. You can use null. Indicates that the insert is empty.

2. The data type of the value should match the data type defined by the column. And the length of the value cannot exceed the length of the defined column.

3. String: insert data of character type, you must write single quotation marks. In mysql, use single quotation marks to represent a string.

4. Date time type data can also be directly expressed in single quotation marks: 'yyyyMMdd',' yyyy-MM-dd','yyyy/MM/dd'.

5. When inserting data, if some columns can be null, or auto-growing columns, or have default values, you can omit them when inserting. Or write null to achieve automatic growth.

6. If you insert data into all the columns in the table, you can omit the column name at the end of the table and write values.

Use the select*from table name-- view all the information about the table.

Method 2: omit some columns

A column can be omitted only if it has a default value, or if null is allowed.

The primary key is self-growing and is considered to have a default value, or it can be omitted.

Method 3: omit all columns

Syntax: insert into table name values (value, value)

Update statement-modify table records

Syntax: update table name set column name = value, column name = value. [where conditional statement]

Square brackets are not grammatical content, which means that this conditional statement can be added or not.

Note:

1. If no condition is added, all values of a column will be modified.

2. In general, when you modify data, you need to add conditions.

Multiple columns are separated by commas.

Eg: change everyone's age to 20

Update user set age=20

Eg: change the age of the person named Zhang San to 18

Update user set age=18 where name= "Zhang San"

Delete statement-A statement that deletes data from a table

Syntax: delete from table name [where conditional statement]

If there is no where, delete all data in the table

Delete deleted rows.

Truncate statement-Delete data

Syntax: truncate table table name

Deleting the table first, and then creating the table, is equivalent to deleting all the data.

In terms of performance: truncate table has better performance.

Summary of addition, deletion and modification of data records:

New:

Insert into table name values (value, value, value...)

Insert into table name (column name 1, column 2, column 3... (.) Values (value 1, value 2, value 3...)

Insert into table name (column name 2, column 4, column 5... (.) Values (value 2, value 4, value 5.)

Modify:

Update table name set column name = value, column name = value where condition

Delete:

Delete from table name where condition

If you do not add where conditions, you will delete all data.

Deleting: emptying data

Truncate table table name

The purpose of emptying the data is achieved by deleting the entire table and then creating a new table.

The difference between delete and truncate is that data deleted by delete can be recovered under transaction management, while truncate cannot be recovered.

Aggregation / aggregation functions in SQL

Aggregate function: multiple data are operated to produce a result.

For example: summation, average, maximum, minimum.

Some functions are defined in the SQL language to implement these operations.

Count function-count the number of records (count rows)

Syntax: select count () | count (column name) from table name

Select count () from table name: the number of rows in the statistical table.

Sum summation function

Syntax: select sum (column name) from table name

Select sum (column name) from table name where condition

Avg function-- average

Syntax: select avg (column name) from table name

Maximum / minimum value of max/min

Select max (column name), min (column name) from table name

Group by grouping query * *

According to a column or columns. Merge and output the same data.

Select... From... Group by column name

Description: in fact, it is classified by columns, and then you can use aggregation functions to calculate the classified data.

Note:

1. Aggregate function: calculate after grouping

2. Usually the content of select: a column to be grouped, b aggregate function.

3. If you encounter this kind of situation, according to each, each. Grouping is usually used when similar statements are made.

4. Filter the data if you use group by to group the data. Where is generally not available at this time, because the functions described above cannot be followed by the where keyword. If you need to add the above function to the filter condition, you can only use the having keyword.

5. Aggregate functions cannot be followed after where, but can be followed by aggregate functions in having.

Group by column name having condition

Filter conditions are added after grouping.

The difference between where and having.

1. Having is usually used in conjunction with group by grouping.

2. Having can write aggregate functions (where the aggregate functions appear: after select, group by … After having) where is not allowed.

That is to say, all the conditions after Where can be followed by having, while the conditions followed by having may not be followed by where.

3. Where is filtered before grouping. Having is filtered after grouping.

When querying, it is more efficient to use where if not necessary, because the data is filtered first and then other conditions are judged.

Description: select … From... Where condition 1... Gropu by... Having condition 2 order by

Condition 1 will perform filtering first.

Group into groups

Condition 2 for filtering

The execution order of select statements and the summary of the query:

The order in which query keywords appear is fixed.

Select... What is to be displayed... From... Table name... Where condition... . Group by... Grouped columns... Having... The conditions after grouping... Order by... Sort

Select... 5... From... 1... Where... 2... Group by... 3... Having... 4... Order by... 6.

Select product,sum (price) as Total Price from orders where price > 10 group by product having Total Price > 30 order by Total Price asc

The execution order of the query

From: table name

Where: conditional filtering

(define aliases)

Group by: grouping

(aggregate function execution)

Having: filter after grouping.

Select: after execution, query the content.

Order by: sort output display.

Thank you for reading this article carefully. I hope the editor will share what is helpful to you in the operation of MySQL basic sentences. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.