MySQL basic statement [Study Notes]

Put it here for future reference.

1. Database, database server, and database language

A database is a collection of persistent data that is used by enterprise application systems and managed by a database management system;

Database servers, also known as database management systems, are used to manage databases (efficiently store, query, update databases, and maintain database integrity );
 
A database language is a specific language in which an application sends commands to the database server and extracts the required data.

2. System Variables

Query System variables: Select @ system variable name, eg. Select @ datadir
Show all system variables: Show variables, show variables like 'pattern'

Set the system variable: Set @ Global (local, session). varname = value;

(1) database Directory: datadir; (2) log: log_warnings
(3) Maximum number of user connections: max_user_connections; (4) version: Version

3. System databases and tables

(1) mysql. User: The table that stores the login information of database users.
(2) information_schema: database for storing directory data

4. view the database details: help show; show warnings; show status;

5. Basic MySQL statements:

(1) Create and view users, permissions, and databases
 
Create user: create user 'username' @ 'hostname' identified by 'Password'

Create Database: Create Database database_name;

 

Grant permissions: grant [operation permissions, such as select, update, delete, all] On database_name.table_name to 'username' @ 'host'
View permission: Show grants; view database creation definition: Show create database database_name;

Display All databases owned by the current user: Show databases; select the database to use: Use database_name;
Display all tables in the current data: show tables;

(2) create tables and tables

Create Table: Create Table table_name (attribute name 1 attribute type 1 constraint 1 ,... attribute name n attribute type N constraint N, primary key (attribute name I, j ,..., p) engine = engine name default Character Set Name collate verification name

A. constraint: whether to allow null values (not null, default value allowed); whether there is a default value (default value)
B. Primary Key Value: if you do not want to specify the value, null is used instead. The system automatically generates the corresponding value for filling.
C. Multiple columns as the primary key: List of multiple column names separated by commas
D. Engine type: InnoDB (reliable transaction processing), MyISAM (high performance, full-text search is supported, but transaction processing is not supported), and memory (FAST)
E. Specify the character set used. You can specify a single column.

View the table creation definition: Show create table table name; view the table field definition: DESC table_name;

Rename table: rename table old table name to new table name

Change the table structure:

Alter table table name (add column name type) [add column] | (drop column name) [Delete existing column] |
(Add constraint name foreign key (foreign key name) References foreign key table name (corresponding primary key name of the table where the foreign key is located) [add foreign key]

View the table creation definition: Show create table table name; view the table field definition: DESC table_name;

Insert data: insert into table_name values (data 1, data 2,... data N); the inserted data must match the attribute type of the corresponding attribute name

Delete data: delete from table_name where conditions

Update Data: Update table_name set attribute name = new value where conditions

Create an index: add (unique) Index name on table name (attribute name)

Create view: Create view name (attribute name I,... attribute name J) as SELECT query statement

(3) Delete

Delete Database: drop database database_name;
 
Delete view: Drop view name

Delete table: Drop Table Name

Delete index: Drop Index name

6. query and filter data

Table Definition [required for MySQL]:
A. Customer table MERs: cust_id (PK), cust_name, cust_email, others
B. Supplier table: Vendors: vend_id (PK), vend_name, vend_country, others
C. Product table products: prod_id (PK), vend_id (FK), prod_name, prod_price, prod_desc
D. Customer Order table orders: order_num (PK), order_date, cust_id (FK)
E. orderitems: (order_num, order_item) (PK), prod_id (FK), quantity, item_price

 

 

(1) Basic query and filtering:
 
Select [distinct] [OP1] from [OP2]

Where [cond_clause]

Order by [OP3] <DESC>

Limit offset, linenum

---> OP1: a single column name or column name expression, or multiple column name or column name expressions separated by commas.
---> OP2: one or more table names separated by commas;
---> OP3: one or more column names separated by commas;
---> Order by: sorts the search results in ascending order of the specified column names. DESC indicates the descending order;
---> Limit offset, linenum: The linenum row starting from the offset row [subscript starting from zero]; if the number of rows is less than linenum, the maximum number of rows can be retrieved.
---> Cond_clause: it consists of one or more condition clauses.
-----> The Condition Clause structure is 'table name. Column name operator value or value set'
-----> Null query: Is null;
-----> Set operator: In (set of values); indicates the value of the set given in parentheses only;
-----> Wildcard OPERATOR: like [binary] 'text'; text is a wildcard text.
Wildcard: % Any multiple arbitrary characters; _ any single character
-----> Regular Expression: Regexp [binary] 'regextext'; regextext is the regular expression text
Regular Expression:. match any single character; or | or; range: [0-9], [A-Za-Z];

// C, // escape characters, such as matching character periods //.;
* Zero or multiple; + one or more; {n} Exactly N; {n,} n> = N; {n, m} n <= m
^ Start of text; $ end of Text
-----> You can use the logical operators "and" or "to connect multiple condition clauses to form multiple filter conditions.

The and priority is higher than or. To ensure the correct order, use parentheses as much as possible to indicate the priority;

Not can reverse the result of a Condition Statement (F-> T, T-> F ).
---> Keyword: Binary Search Area is case sensitive; distinct removes duplicate rows

(2) generate a new field: in the preceding query statement, [OP1] can also be any legal column name expression:

A. Fields concatenated by multiple column names and strings. For example, select Concat (column name 1, '***', column name 2) from table name.
B. Arithmetic Operation of column names. For example, select (prod_price * quantity) from items;

C. Column name function. For example, select Concat (Year (order_date), '/', month (order_date) from orders;
D. As alias_name: can be used to name newly generated fields.

(3) group data: you can add a group clause after the WHERE clause. The WHERE clause filters data before the group, and the having clause filters data after the group.
Select [OP1] from [OP2] [where clause] Group by one or more column names having Condition Clause

(4) subquery: A subquery can be used to replace any places with values or value sets, especially in. For example
Select cust_name from MERs

Where cust_id in

(Select cust_id from orders where date (order_date) between '2017-09-01 'and '2017-09-30 ');

(5) table join:

 

A. Basic table join: it is mainly performed using Cartesian Product and where clause. For example, find all product names and supplier names:

Select prod_name, vend_name from products, vendors where products. vend_id = vendors. vend_id; or
Select prod_name, vend_name from products inner join vendors on products. vend_id = vendors. vend_id;

B. Join multiple tables: The method remains unchanged. To avoid errors, you can write the statements in the text file first, and then copy and paste the statements to verify them.

For example, find out the customer's order information:

Select cust_name, orders. order_num, prod_name, vend_name, quantity, prod_price, item_price * quantity as order_price
From MERs, vendors, orders, products, orderitems
Where products. prod_id = orderitems. prod_id
And orderitems. order_num = orders. order_num
And customers. cust_id = orders. cust_id
And products. vend_id = vendors. vend_id;

Tip: three steps: A. Select the column name to be displayed or construct the column name expression in multiple tables in the SELECT statement;
B. list all table names involved in the from statement;
C. Establish a join Based on the foreign key in the table and the where equal clause condition.

 

C. Auto join: if you reference and join the same table multiple times in a single SELECT statement, you must use the table alias to eliminate ambiguity.
 
For example, to query other products produced by a supplier whose production product ID is 'dtntr:

Select p1.vend _ id, p1.prod _ id from products as P1, products as p2
Where p1.vend _ id = p2.vend _ id and p2.prod _ id = 'dtntr'

D. External join: Add unrelated rows on the basis of the natural join (please refer to relevant database theory books on your own)

SQL: Select... from table name 1 left [right] Outer Join table name 2 on...

(6) Combined Query: combines multiple select queries into a single query result.

----> SQL: Select... Union [all] Select... [order by DESC]
----> Union all: Contains duplicate rows in multiple query results. By default, duplicate rows are not included;
----> Order by: the query results of the entire combination must be sorted after the last SELECT statement.

7. Database Maintenance:

(1) execute the SQL file:/. <FILENAME> or source <FILENAME>; mysql-u username-P database_name <XXX. SQL

(2) backup and recovery data tables: Select * into OUTFILE '/var/lib/MySQL/user. Bak' from tblname;

Load data infile '/var/lib/MySQL/user. Bak' replace into Table tblname;

(3) backup and recovery of databases or tables:

Mysqldump-uxtools-h127.0.0.1-pxtool
Dbname tablename>/tmp/TBL. Bak. SQL