Commonly used SQL_ Official document use

Basic theory of SQL statements

SQL is the standard language for manipulating and retrieving relational databases, and standard SQL statements can be used to manipulate relational databases .

5 Major types:

①DQL, the data query Language, the query language, is the most complex and most versatile statement in SQL statements , mainly because of the SELECT keyword completion.

② DML statement, datamunipulation Language, data manipulation language) statements , and DML statements will maintain a good consistency; The statement of the operation table, such as inserting insert, updating update, deleting delete, etc.

The ③DDL (data definition Language) statement that operates on the language of the data object, including creating create, deleting drop, and modifying the ALTER database object.

④DCL (Data Control Language) statement , mainly with GRANT, REVOKE statement.

⑤ Transaction Control Statements : mainly commit, rollback and savepoint three keywords complete

Attention:

　　SQL is not case sensitive : Select is the same as select;

The SQL statement for MySQL is marked with a semicolon (;) as the end.

All DBA operations must come from the official documentation

All DBA operations must come from the official documentation

All DBA operations must come from the official documentation

(important thing to say three times!!) ）

A brief introduction to the official documents:

1,Tutorial: Some of the common MySQL operations using a scene series together

Just pay attention to the gray part of the inside, follow the gray operating part

---Detailed follow the official document of the tutorial module operation again, it is necessary for beginners

2.Server Administrator:Some commands, tools, parameters, etc. required by MySQL management

3.SQL Syntax

SQL syntax, most used, especially DDL statements must be referenced using SQL syntax (additional pruning)

4,Server option/variable Reference:mysql parameters and status values, use more

5,Functions and Operators

MySQL common functions and operators, use more

6. Views andStored Programs

Views, stored procedures, functions, triggers, event syntax reference

7,optimization: Optimization

It is worth looking at carefully, this document is not only used for reference, more to learn optimization knowledge, is the DBA Advanced Treasure

8,partitioning

If you are partitioning a table, this document is a must-see and the only reference material

9.information schema, performance schema

Two references common to intermediate DBAs

10.Spatial Extensions

Location information (nearby people, nearby restaurants ...) ）

11,Replication

MySQL uses the copy function, the common reference material

12,semisynchronous Replication

Semi-synchronous replication, which can be used on individual occasions

Second, the official document use skill

1,{} curly braces enclosed and there is a | pipe symbol in the middle of the N select one, you must enter

2,[] enclosed in parentheses, the expression is optional

3. lowercase letters represent variables and can be replaced in the future

4. uppercase letters represent keywords and must be entered

5, generally have sepcification suffix of the expression of this is a specification, there will be a detailed introduction behind

6, | Pipe character connection information is not enclosed in {} braces, which can have either one or both

MySQL Database common commands : Help Contents

1. Show all databases

Show databases: List of databases for MySQL database management system

2. Create a database

Create DATABASE [if not EXISTS] DbName: creating databases named DbName

3. Delete Database

Drop database dbName: delete dbName specified databases

4. Statistical data Base Information

Show Table status [{from|in} db_name] [like ' pattern ' |where expr] \g: Will output the performance and statistics of MySQL database management system

Example:

Show table status from Runoob; #显示数据库 information for all tables in Runoob

Show table status from Runoob like ' runoob% '; #表名以runoob开头的表的信息

Show Table status from Runoob like ' runoob% ' \g; #加上 \g, query results are printed by column

5. Using the database

use DbName: Switching, using the specified database  

6. Display table

Show tables: Displays all tables for the specified database

7. Display table structure

DESC tableName: Display table structure describe (DESC)

　　Show columns from TableName: Displays data table properties, property types, primary key information, whether null, default value, and other information---similar to desc tableName

8. Display Table Index

Show index from data table: Displays detailed index information for the data table, including primary key (primary key)

9. Create a table

CREATE table tableName (column declaration): Creating a Table

Example: Create a students table, hold a number (ID), name, Gender (sex), Age, contact phone (tel)

CREATE TABLE students (ID int unsigned NOT NULL auto_increment primary key, name char (8) is not NULL, sex char (4) is not NULL, Age tinyint unsigned NOT NULL, tel char (+) NULL default "-");

10. Display table contents (data)

SELECT * FROM TableName: Displays the details of the data table

Select column name from TableName [where condition]: querying data in a table by criteria

11, modify the contents of the table (data)---increase, change, delete

Add: Insert

Insert [into] tableName [(Column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...): Inserting data into a table

Example:

INSERT into classmate values ("Xiaoming", "Male", "a", "China", "tennis");

Change: Update

Update tableName set Column name = new value where update condition: Update data in table

Example:

Update classmate Set address= "Chongyi" where id=3

Delete: Delete

Delete from tableName where delete condition: delete data from table

Bulk Delete: Drop, truncate

drop table TableName: Delete tables (including table structure and all data)

Truncate tableName: Delete table (delete all data from table, keep the structure of tables)

12. Modify ALTER TABLE after creation

Adding columns

ALTER TABLE name add column list data type [after insertion position];

Example:

ALTER TABLE students add address char (60): Append column address at the end of the table

ALTER TABLE students add birthday date after age: Inserts a column after the Age column birthday

modifying columns

ALTER TABLE name change column Name column new name new data type;

Example:

ALTER TABLE students Change Tel Telphone char (All) Default "-": the Tel column in the table is renamed to Telphone

ALTER TABLE students change name name char (+) NOT NULL: Changes the data type of the Name column to char (16)

Delete Column

ALTER TABLE name drop column name;

Example:

ALTER TABLE students drop Birthday: Delete Birthday column  

Renaming a table

ALTER TABLE name rename new table name;

Example:

ALTER TABLE Students rename workmates: Rename students table to workmates

Commonly used SQL_ Official document use