SQL---->sql-summary-mysql

Database operations:

1. Create a database and modify the default character encoding

Create database name [charset= character encoding];

Ee:

Create Database dog Charset=utf8;

Ps:

1.1. If the database already exists, an error will be made. You can create a database by using the following statement:

Create database if not exists name;

In fact, when you create a database, you should determine if the database already exists.

1.2. If you want to create a database that is named with a keyword, you can create it with an anti-quote. The position of the inverted quotation marks on the left of the number 1,

Be sure to use the English half-width format before it takes effect.

Ee:

Create database ' database ';

2. Querying the database

show databases;

Ps:

Database is a plural, otherwise it will be an error.

3. Display the data creation statement, you can see the encoded format of the

Show create database name;

Ee:

4. Change Database

Character encoding and Database engine

ALTER DATABASE name [Target parameter]

Ee:

ALTER DATABASE dog CHARSET=GBK;

5. Deleting a database

drop database name;

Ee:

Drop database Dog;

Ps:

If you delete a database that does not exist, you will get an error, preferably before you start the operation.

Drop database name if exists;

6. Select Database

Use database name;

Ee:

Use dog;

Operations on database tables

Relationship: Row---record column---field

1. Create a table

CREATE TABLE Table name (

Primary key field Data type 1 [null/not null] [default] [auto_increment] [primary key],

Non-primary key field data type 2,

...........

);

Ps:

Primary key: Non-null unique

There are only characters in the database, no concept of strings

use keywords to do table names need to add anti-quotation marks, using keywords to do field names need to add anti-quotation marks, with the above with the keyword database name practice consistent.

Data type: Int/char (fixed length)/varchar (variable length)/text (large text)/decimal (total number of digits, number of decimal places)

Ee:

1.1 Phone Number---varchar// Sex---char// Age---int/tinyint (max:255)// Photo---Not binary, only the path of the photo is stored in the data

Salary---decimal// QQ---varchar// mobile number---Char

1.2 Name---NOT NULL//address---NOT NULL//message---NULL//Score---NULL

2. View Table

Show tables;

Ps:

The tables is plural.

3. View created tables (can show table details)

Show create table name \g;

Ee:

Show create TABLE dog \g;

Ps:

\g: Indicates that the table field and the Create table field are arranged in an upright form

Details of the table when \g is not used:

From creating a database to creating a table procedure:

There's a leak , and you know the name of the creation table.

4. Show Table Structure

describe table name;

Or use the abbreviated form of the command query, the effect is the same:

DESC table name;

Ee:

Desc Dog;

5. Delete a table

drop table name;

You can delete multiple tables, and when there are foreign key relationship constraints, be aware of the deletion order, first delete the table containing the foreign key, and then delete the main table.

drop table name 1, table name 2,...;

Ee:

drop table Dog;

drop table t1,t2,t3;

Ps:

Latin 2 Characters of one Chinese

UTF8 1 Characters of one Chinese

Entering Chinese in Latin encoding environment will result in invalid values

6. Query character encoding:

Show variables like 'character_set_% ';

Data manipulation

1. Insert data (increment)

Insert into table name (field name 1, field name 2,......, field name N) VALUES (value 1, value 2,......, value N)

Or: (The values that must be inserted are the same as the order and number of fields in the data table):

Insert into table name values (value 1, value 2,......, value N)

Auto-grow primary Key ID Insert data: (primary key must be set to NULL):

Insert into table name (ID, field name 2,......, field name N) values (null, value 2,......, value N)

Ps:

Details:

1.1. The fields you insert can be inconsistent with the order of the fields in the database, but the values must be in the same order as the inserted fields, or you will get an error.

Ee:

Insert INTO Dog (Id,name) VALUES (1, ' Tom ');

1.2. The inserted field can be omitted, and the inserted value and the data table have the same order and number of fields

Ee:

Insert INTO dog values (1, ' Tom ');

1.3. Auto-growing primary key ID, which can be represented by the ID field in NULL when inserting data

Ee:

Insert into dog values (null, ' Tom ');

1.4. Insert Default value (field indicates when default value is used)

Ee:

Insert INTO dog values (2,default);

2. Deleting data (delete)

Delete from table name where condition

The difference between delete and drop

Delete means that the data in the table is all deleted, but the table's fields still exist.

Drop indicates that all information for the table has been deleted.

Auto_increment this mechanism, when a record in a table is deleted, the automatically growing record will no longer be the ID number of the deleted record,

The record that was deleted can be recovered from the database log

3. Query data (check)

Select Column name from table name [WHERE Condition] [order by sort Asc|desc] [limit paging parameter (start position, number of records queried)];

Ps:

* denotes all fields

ASC indicates ascending

desc = Descending

(the default is in ascending order)

Usage details for limit

Ee:

SELECT * from dog limit 3; #限制取前3条记录

SELECT * from dog limit 0, 3; #和上面的语句等价

The location of the first record in the database is marked as 0.

add: mysql operator

Comparison operators: >=, <, <=, =, <> (not equal to)

Logical operators: And,or,not

4. Modify data (change)

Update table name set field name 1= value 1, field name 2= value 2, field name 3= value 3 Where condition

Some questions:

The value entered by the primary key column is not allowed to be empty;

A table may not have more than one primary key, but the primary key can consist of multiple columns;

The identity column (auto-grow column) cannot be a character data type (the number automatically grows);

Aggregation functions

SUM (), AVG (), Max (), Min (), COUNT ()

With parameters in parentheses, parameter is the field name of the database table

SQL---->sql-summary-mysql