Basic MySQL operations

Understanding of databases SQL is the intermediary between users and databases. SQL commands are case insensitive. "Mysql" is a database that contains basic information related to MySQL operations and other management data. "Information_schema database is also called information architecture. It manages the composition information of the database starting from the table and the retrieval specialized information of user management information.

Understanding of databases SQL is the intermediary between users and databases. SQL commands are case insensitive. "Mysql" is a database that contains basic information related to MySQL operations and other management data. "Information_schema database is also called information architecture. It manages the composition information of the database starting from the table and the retrieval specialized information of user management information.

Database knowledge

SQL is the intermediary between users and databases.

SQL commands are case insensitive.

"Mysql" is a database that contains basic information related to MySQL operations and other management data.

"Information_schema database is also called information architecture. It manages the composition information of the database starting from the table and the retrieval of user management information. The two databases can be used to understand the current database status. You cannot store frequently used data in these two databases.

The test database is a test database. The database itself is empty and can be deleted.

Show databases.

Delete database drop database Name

!! Windows is case-insensitive for table names and database names. linux is case-sensitive.

Create new users and grant them database operation Permissions

Grant all privileges on database name. * to username @ localhost identified by password

Permission is a measure of what operations a user can perform on a database. All privileges Permissions Create Select search Update Delete

Create user Username identified by 'Password'

User Logon mysql-u user name-p password (no space between-p and password)

Select database use Database Name

View the currently used database select database ();

When creating a table, specify the character set create table name (...) charset = uft8;

Show tables for all tables in the current database;

Display table structure desc/describe table name

Drop table name

Auto-incrementing Sequence

Auto_increment:

1. The data type must be an auto-incrementing sequence.

2. Use primary key to set its uniqueness.

Initialize the initial value of auto_increment

Alter table name auto_increment = initial value

Types of SQL statements

1. Database Operation statements

2. database definition statements

3. Database Control statements

Use SQL rules on the terminal:

1. the SQL statement must end; 2. reserved keywords are case insensitive 3. You can freely add spaces or line breaks in the middle of an SQL statement (the spaces in the middle of a keyword are incorrect) 4. Single line comment: --, multi-line comment :/*...*/

When inserting data into a database, if the value is a string or date, you must use ''to enclose the value.

When updating or deleting all data in a table, you can set where = null, but there is also a better command to clear the data table.

Truncate table name

Determines whether the field value is null. The condition cannot be = null. It should be is null.

Priority of logical operators:

NOT> AND> OR

Count () function counts non-null records

Table maintenance and Transformation

Pay attention to backup before making changes !!

1. modify the column definition: alter table... modify

2. append column: alter table... add

3. Modify the column name and definition: alter table... change

4. delete a column: alter table... drop

Change the Data Type of a column

Alter table name modify column Name Data Type

Append a new column

Alter table name add new column Name Data Type append a new column at the beginning of the table alter table name add new column name data type first append a new column alter table name add new column name data type at any location after previous column name

Change column position

Alter table name modify column name data type to be moved after the previous column name

Modify column name and Data Type

Alter table name change old column name new column name New Data Type

Delete column

Alter table Name drop column name

Copy and Delete tables

1. Copy the column structure and data of the table

Create table new table name select * from old table name

2. Copy the Column Structure of the table

Create table new table name like old table name

3. Copy data to an existing table

Insert into table name select * from old table name

Delete table

Drop table Name

Determine whether the table exists before deleting the table

Drop table if exists table name