MySQL must be known to summarize

MySQL used to be not a lot, 2 days to read the MySQL must know will also review the foundation. The 200-page book will soon be finished,

Most of the knowledge is relatively basic, but still understand some previously unknown knowledge points. Make a backup of yourself and view it at any time.

Command:
SQL is case-insensitive, statement uppercase, column, table name lowercase is a habit
Connection command: Mysql-u user_name–h example.mysql.alibabalabs.com–p3306–pxxxx
Quit
Help show; View all Show commands
show databases;
Use dbname;
Show tables;
Show columns from TableName; (abbreviated DESC tablename;)
Show status;
Show grants;
Show CREATE Database dbname; View statements that create a database
Show CREATE TABLE tablename; View a statement that creates a table

Inquire:
Retrieving too many unwanted columns can degrade performance and try to find out what to look for
select * from Users;
Distinct
Select distinct title from users;
Limit
SELECT * from users limit 5;
Retrieving 5 lines starting at line 5th
SELECT * from Users limit 5, 5;
Retrieves the second line with the line number starting with 0
SELECT * from Users limit 1, 1;

Sort:
* * MySQL is sorted alphabetically by default A and a are the same, but configurable
SELECT * FROM news order by title;
Between and
SELECT * from news where ID between 3 and 6;
Null empty value Check
SELECT * FROM news where created is null;
Parentheses change the order of calculation
Select Prod_name,prod_price from Products where (id=1002 or id=1003) and Prod_price >= 10;
Inch
SELECT * FROM news where ID in (6,8,9,222);
Wildcard characters to avoid excessive use

Regular expressions
Select title from news where title RegExp '. 11 ';
Select title from news where title regexp ' 1000|2000|3000 '; [1|2|3] [^123]
Match Special character needs \ \ Escape
SELECT * FROM news where content regexp ' \ \. '
Pre-defined character set
[: alnum:] Any letter and number
[: Alpha:] Any character with [a-za-z]
[:d igit:] Any number with [0-9]
' \ \ ([0-9] sticks?\\) '
Locator characters
^ $
[[: <:]] match the beginning of the word
[[:;:]] match the end of the word

Data processing
concat function Connection string
Select Concat (title, ' (', Content, ') ') as AAA from news;
LTrim Rtim trim function to remove spaces
Select Trim (title) from news where title regexp ' BBB ';

Select statements can also be used to test statements
Select 3*2;
Select Now ();
Select ' Hello World ' regexp ' world '; Returns 1

Function Standard SQL portability is strong, function portability is not very strong, the implementation of each DBMS is likely to be different
String processing
Upper () lower () length () sustring ()
Date and time processing functions
Adddate () Addtime () curdate () Curtime () Date () DateDiff ()
Date_add () Date_format () Day () DayOfWeek () Hour () Minute ()
Month () Now () Second () Time () year ()
Querying data for 2014-12-15
SELECT * FROM news where Date (created) = ' 2014-12-15 ';
Querying data for November 2014
SELECT * FROM news where year (created) =2014 and Month (created) = 11;

Numeric processing functions
Abs () Sqrt () Sin () Cos () Mod () Pi () Rand ()
Aggregate function, can be calculated directly with SQL as far as possible do not use the program processing
AVG () COUNT () MAX () MIN () SUM ()
Count ignores NULL when acting on columns
Select count (created) from news;

Having to filter groups
Select Title, COUNT (*) as Num from news Group by title have count (*) >=2;
Sorting is not guaranteed at the time of grouping, it is usually necessary to display the sort yourself
Select Title, COUNT (*) as Num from news Group by title have count (*) >=2 ORDER by Num;
Related sub-query
More than one solution, not necessarily the most effective way to solve this query, you can also join the table, do more experiments, verify the performance
Select Cust_name, (select COUNT (*) from the orders where orders.cust_id=customers.cust_id) from the customers order by Cust_name;
Sub-query nesting too much when a layer of debugging, first from the innermost hard-coded

Table Junction
Select Vender.id as vender_id,vender_name,products.id as Prod_id,prod_name,prod_price from Vender, products where vender . id=products.vender_id;
The internal junction result is the same as the recommended syntax for this definite connection
Select Vender_name,prod_name,prod_price from vender inner joins products on vender.id=products.vender_id;
Self-coupling, the connection itself to query (compared to the sub-query which is high efficiency, should do more experiments)
Select P1.prod_name from the products as P1, the products as P2 where p1.vender_id=p2.vender_id and p2.prod_name= ' product2 ';

The outer junction junction contains rows that are not associated in the related table
Customers who do not have an order will also be linked in.
Left right specifies that the table must contain all rows (whether or not matches)
SELECT ' Cust_name ', ' order_num ' from ' customer ' left outer join ' order ' on ' customer '. ' cust_id ' = ' order '. ' cust_id ';

UNION
Combining multiple SELECT statements
UNION ALL (union default does not contain duplicate rows, union all contains duplicate rows)

Full-Text Search InnoDB does not support MyISAM support
Full-Text Search sorts results by priority

Copy Import table data Insert Select
INSERT into Customers (Id,name) select Cust_id,cust_name from Newcust;

Delete all rows in a table truncate TableName is faster than delete from TableName
Because truncate is the first to delete the original table and then create a new table

AutoIncrement can also insert a value manually, as long as it is unique.
Select last_insert_id (); Returns the current maximum value of the self-growing column, regardless of the table

Database engine
InnoDB supports reliable handling of things
Memory function equivalent to MyISAM, data stored in RAM (Trial and temp table)
MyISAM high-performance engine, full-text search, no support for object handling
Foreign keys cannot cross engine

Change table
ALTER TABLE NEWS add name varchar (50);
ALTER TABLE news drop column name;
ALTER TABLE PRODUCTS ADD constraint fk_products_venders foreign key (vender_id) references venders (vender_id);
Rename table news to news1;

A view is an SQL statement
Views are most commonly used for queries, but can also be updated
Create VIEW viewname;
Show CREATE VIEW viewname;
Drop View viewname;

Stored Procedures
Call a stored procedure
Call ProcedureName (@param1, @param2, @param3);
Create a stored procedure
Creage Procedure GetTitle ()
Begin
Select title from News;
End
The command line temporarily changes the delimiter
Delimiter//
delimiter;
To delete a stored procedure
drop procedure procedurename;
drop procedure procedurename if exists;
Pass parameters
Delimiter//
CREATE procedure GetCount (out P int)
Begin
Select COUNT (*)
Into P
From news;
End//
delimiter;
Call
Call GetCount (@ret);
Inquire
Select @ret;

View stored Procedure creation statements
Show CREATE PROCEDURE procedurename;
Get a list of stored procedures
Show procedure status;

// ***********************
Stored procedures are typically used to handle more complex business logic
Different operations are generally performed according to different conditions
--Name:ordertotal
--Parameters:onumber = order number
--Taxable = 0 if not taxable. 1 if Taxable
--Ototal = Order Total Variable
CREATE PROCEDURE OrderTotal (
in Onumber int,
In Taxable Boolean,
Out Ototal decimal (8,2)
)
Begin
Declare total decimal (8,2);
DECLARE taxrate int default 6;
Select SUM (item_price * quantity)
From OrderItems
where order_num = Onumber
into total;

If Taxable Then
Select Total + (total/100*taxrate) to total;
End If;

Select Total into Ototal;
End
// ************************

Cursors (for stored procedures and functions)
Using Cursors to traverse data

Trigger (defined on table), cannot return data in trigger
Create trigger Inserttrigger After insert on news for each row insert into vender (vender_name) VALUES (' New vender ');
Delete Trigger
Drop trigger Inserttrigger;
The INSERT trigger can reference a virtual table named new to access the inserted data
Delete trigger can reference a virtual table named old to access the deleted data

Transaction processing
Start transaction
Rollback
Commit
Rollback can fallback the Insert update delete operation and cannot roll back the Create drop operation
Reserved Point SavePoint
Complex transactions can set multiple retention points to facilitate fallback to a retention point instead of rolling back the entire transaction
SavePoint delete1
Rollback to Delete1
Disable Auto-commit
Set autocommit = 0

//Show all character sets
show character set;
Show all proofing (character comparison rules)
Show collation;
Displays the character set currently in use
Show variable like ' character% ';
Displays the currently used proofing
Show variable like ' collation ';
Specifying character sets and proofing
//columns for a table can also specify character sets and proofing
CREATE TABLE MyTable (
Column1 int,
Column2 varchar () Character Set latin1 Collate latin1_general_ci
) default character set UTF8
collate utf8_general_ci;
Temporarily change default proofing
Select * FROM Customers ORDER by LastName COLLATE Latin1_geeral_cs

When select

Security Management
In reality do not use root
All users stored in the MySQL database user table
//password with MD5 encryption, simple password A search can find out
Select User,password fro M user;
Create user
"identified by ' * * *" for ' aaa ' @ ' localhost ';
Rename
Rename user aaa to BBB;
Delete user account and related Permissions
Drop subscriber AAA;
View access permissions, defined by user name and host, to define
Show grants for AAA; (=show grants for [email protected]%;)
Show grants for [email protected];
Grant user Rights
//user AAA can access all tables of the MYDB database
Grant Select on mydb.* to AAA;
Mysql> show grants for AAA;
+---------------------------------------+
| Grants for [email protected]% |
+---------------------------------------+
| GRANT USAGE on * * to ' aaa ' @ '% ' |
| GRANT SELECT on ' mydb '. * to ' aaa ' @ '% ' |
+---------------------------------------+
//REVOKE Permissions
Revoke select on mydb.* from AAA;
Update password
Set password for AAA = password (' 123456 ');
Update the current user's password
Set password = password (");

Performance optimization
Hardware configuration
More than one way to write the same select (Connect and subquery)
Stored Procedures
No need to retrieve unwanted data
Turn off auto-commit when importing data, delete indexes, and rebuild after import
Index
。。。。

MySQL must be known to summarize