MySQL management and optimization (1) _ MySQL

MySQL management and optimization (1) SQL basics: SQL introduction

• SQL: Structure Query Language (Structured Query statement) is a way for us to interact with databases.

(MySQL) getting started

• SQL classification:

1. DDL (Data Definition Language), a Data Definition Language. The main operation objects are databases, tables, fields, indexes, etc.

2. DML (Data Manipulation Language), a Data Manipulation statement. Main operation database table records.

3. DCL (Data Control Language), Data Control Language. This module provides security control and authorization for tables and fields.

DDL statement

• Create a database

CREATE DATABASE
 <数据库名>
  
;
 

• Delete database

DROP DATABASE
 <数据库名>
  
;
 

• Create a table

CREATE TABLE
 <表名>
  
(Column name 1, data type 1, constraint 1, column name 2, data type 2, constraint 2 ,...)
 

For example:

Create table emp (ename VARCHAR (10) COMMENT 'employee name', hiredate datetime comment 'hire time', sal DECIMAL (10, 2) COMMENT 'pause', deptno INT (2) COMMENT 'Department number ');

• View table information

• View detailed information about the created table

• Delete table

drop table 
 

• Modify table

1. modify the field type:

2. Add table fields:

3. delete table fields:

4. rename the field:

5. modify the field Arrangement Order

6. modify the table name:

DML statement:

• Insert record

INSERT INTO 
 
   (field1, field2, ...)VALUES(val1, val2, ...),(val1', val2', ...),...
 

• Update record

UPDATE 
 
  SETfield1=val1, field2=val2, ...[WHERE] ...
 

• Delete record

DELETE FROM 
 
  WHERE ...
 

• Query records

SELECT * FROM 
 
   WHERE ...
 

(1) query records that are not repeated

SELECT DISTINCT field_name FROM 
 
  ;
 

(2) paging query

SELECT * FROM 
 
   LIMIT 
  
   , 
   
  
 

(3) aggregation

SELECT (field1, field2,...) func_name # indicates an aggregate function, such as max and sum FROM
 
  
[WHERE condition] # where condition statement, filtering before classification [group by field1, field2 ,... # grouping statement [with rollup] # [HAVING condition] # filter data after Classification
 

(4) joint

SELECT * FROM table1UNION/union all # perform DISTINCT once when the former is merged, and the latter directly merges SELECT * FROM table2

DCL statement:

(1) authorization:

GRANT [SELECT/UPDATE/INSERT/DELETE] on [DB. */DB.
 
  
'@'
  
   
'Identified'
   
    
';(2) revoke authorization:
    
REVOKE [SELECT/UPDATE/INSERT/DELETE] ON [DB.*/DB.
   
  
 
 
 
  
  
]'
 
 
 
  
'@'
  
   
';No.
  
 
 
 
  
  
] FROM'