MySQL Basic statement "Learning notes"

Put it here for later inspection.

1. Database, database server, database language

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

Database server, also known as database management system, is used to manage the database (efficiently store, query, update the database, and maintain the integrity state of the database);

A database language is a specific language that an application uses to send commands to the database server and extract the data it needs.

2. System Variables

Query System variables: SELECT @@ 系统 variable name, eg. SELECT @ @DATADIR
Show all system variables: Show VARIABLES, show VARIABLES like ' pattern '
Set System variables: SET @ @GLOBAL (local,session). Varname= value;

(1) database directory: DataDir; (2) Log: Log_warnings
(3) Maximum user connection: max_user_connections; (4) Edition: Version

3. system databases and tables

(1) MYSQL. User: The table that holds the database user login information.
(2) Information_schema: Database for storing directory data

4. View database details: Help show; show WARNINGS; show STATUS;

5. mysql Basic statement:

(1) Create and view users, permissions, databases

Created by: Create user ' username ' @ ' hostname ' identified by ' password '

Creating a database: Create databases database_name;

Grant permissions: Grant "Operation permission, for example, select, Update, delete, all" on database_name.table_name to ' username ' @ ' host '
View permissions: Show GRANTS; View the database creation definition: Show create the database database_name;

Displays all databases owned by the current user: show databases; Select the database to be used: use database_name;
Displays all tables in the current data: show tables;

(2) Creating table and table operations

Creating tables: CREATE TABLE table_name (property name 1 property type 1 constraint 1, ... Property name n attribute type N constraint N, PRIMARY KEY (property name I,j,..., p)) engine= engine name DEFAULT CHARACTER set Character set name COLLATE proofing name

A. Constraint: Whether NULL is allowed (not NULL, NULL is allowed by default), whether there is a default value (default value)
B. Primary key value: If you do not want to specify, use NULL instead, the system automatically generates the corresponding values to fill
C. Multiple columns as primary key: List of multiple column names separated by commas
D. Engine Type: InnoDB (reliable transaction processing), MyISAM (high performance, support full-text search, but no transaction processing), MEMORY (speed particularly fast)
E. Indicate the character set used, and a single column can be specified.

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

Rename table: RENAME table name to new table name

To change the table structure:

ALTER table name (add column name type) [Adding column] | (DROP column name) [Delete existing columns] |
(ADD CONSTRAINT constraint name FOREIGN key (foreign key name) REFERENCES The table name of the foreign key (the corresponding primary key name of the table with the foreign key)) [Add foreign Key]

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

Insert data: INSERT INTO TABLE_NAME values (data 1, Data 2, ...) Data n); Insert data must match the property type of the corresponding property name

Delete data: Delete from table_name WHERE CONDITIONS

Updated data: Update table_name SET Property name = new Value WHERE CONDITIONS

CREATE INDEX: ADD (UNIQUE) index name on table name (property name)

Creating view: Create View name (property name I, ... Property name J) as SELECT query statement

(3) Delete operation

Delete databases: DROP database database_name;

Delete view: Drop view Name

Delete tables: drop table table name

DROP INDEX: Dropping index name



6. Querying and filtering data

Table definition [see MySQL must know]:
A. Customer table customers: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 Form Orders:order_num (PK), Order_date, cust_id (FK)
E. Order Information table OrderItems: (Order_num, Order_item) (PK), prod_id (FK), quantity, Item_price

(1) Basic Query and filter:

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 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 results of the search, sorted sequentially by the specified column name, by default in ascending order, and DESC indicates descending order;
---> LIMIT offset, linenum: The LineNum line starting from offset line [subscript from zero], or the maximum number of rows to be retrieved if the number of rows is less than linenum.
---> cond_clause: Consists of one or more conditional clauses.
-----> Conditional clause structure is ' table name '. Column name operator value or value set '
-----> Null value query: is NULL;
-----> Set Operator: In (a collection of values); Represents a value only in the set of values given in parentheses;
-----> Wildcard operators: Like [BINARY] ' text '; Text is a wildcard character.
Wildcard:% any number of arbitrary characters; _ any single character
-----> Regular Expressions: REGEXP [BINARY] ' regextext '; Regextext as regular expression text
Regular expression:. Match any single character; or | or range matching [0-9], [a-za-z];

                                                    //c,//escape character, such as match word Fu number// . ;
                          * 0 or more; + one or more; {n} exactly n; {n} n>=n; {n,m}  n<=n<=m
                          ^ text start; $ text end  
            -----> You can use the logical operator and, or concatenate multiple conditional clauses to form multiple filter conditions.

and priority is higher than OR, in order to ensure the correct order, as much as possible to use parentheses to indicate priority;

Not can negate the result of a conditional sentence (f->t, t->f).
---> Keywords: BINARY search is case-sensitive; DISTINCT Remove Duplicate rows

(2) Create a new field: in the above query statement, [OP1] can also be any valid column name expression:

                A. A field that is stitched together by multiple column names and strings. For example, SELECT Concat (column name 1, ' * * * ', column name 2) from table name.
                B. Arithmetic of the column name. For example SELECT (Prod_price * quantity) from items;              

                C. Column name functions. For example, SELECT Concat (year (order_date), '/', Month (order_date)) from orders;          & nbsp;        
                 D. As Alias_name: can be used to name newly generated fields.  
       
      (3) grouping data: You can After the sentence is added, the WHERE clause filters the data before grouping, and the HAVING clause filters the data after grouping.
          select [op1]   from [OP2]   [WHERE clause ]   GROUP by   One or more column names   having conditional clauses

      (4) Subqueries: Subqueries can be used in place of any value or set of values, especially with in. For example
          select cust_name from customers

WHERE cust_id in

(SELECT cust_id from Orders WHERE Date (order_date) between ' 2005-09-01 ' and ' 2005-09-30 ');

(5) Table junction:

A. Basic table joins: mainly using the Cartesian product and the WHERE clause. For example, find out 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 joins vendors on products.vend_id = vendors.vend_id;

B. Multiple table joins: The method does not change, in order to avoid errors, you can first write the statement in a text file, and then copy and paste the past to test.

For example, find out what the customer ordered:

SELECT Cust_name, Orders.order_num, Prod_name, Vend_name, Quantity, Prod_price, item_price*quantity as Order_price
From customers, 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: In three steps: A. Select the column name or construct the column name expression in multiple tables in select that you want to display;
B. List all related table names in from;
C. Establish a junction based on the foreign key in the table and the WHERE equality clause condition.

C. Self-coupling: referencing and joining the same table multiple times in a single select requires the use of table aliases to disambiguate.

For example, query for other products produced by a supplier that produces a product ID of ' 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. Outer joins: Attaching non-connected lines on the basis of a natural connection (please consult the relevant database theory books yourself)

Sql:select ... From table name 1 Left[right] OUTER JOIN table Name 2 on ...

(6) Combinatorial query: Combine multiple SELECT queries into a single query result

----> Sql:select ... UNION [All] SELECT ... [ORDER by DESC]
----> UNION All: Contains rows that recur in multiple query results, and default does not contain duplicate rows;
----> ORDER BY: The results of the entire combined query must be sorted after the last SELECT statement.

7. Database maintenance:

(1) Execute SQL file:/. <filename> or source <filename>; Mysql-u username-p database_name < Xxx.sql

(2) Backup and Recovery data sheet: SELECT * into outfile '/var/lib/mysql/user.bak ' from tblname;

Load data infile '/var/lib/mysql/user.bak ' replace into table tblname;

(3) Backing up and restoring Databases or tables:

Mysqldump-uxtools-h127.0.0.1-pxtool dbname tablename >/tmp/tbl.bak.sql

MySQL Basic statement "Learning notes"