Mysql series learning, mysql Series

Mysql series learning, mysql Series

# Help
Help create user;
# View current user
Select user ();
# Create a user
Create user 'egon' @ 'localhost' identified by '2013 ';
Create user 'Alex '@' % 'identified by '201312 ';
Create user 'ton '@ '192. 192. %' identified by '20140901 ';
# Authorization (add identified by '000000' after directly creating a user ')
Grant select on *. * to 'egon' @ 'localhost' identified by '123 ';
Grant select (id, name), update (name) on db1.t1 to 'egon' @ 'localhost ';
Flush privileges;
# Revoke permissions
Revoke select on db1. * from 'egon' @ 'localhost ';
# Permission table
Mysql. db/mysql. user/mysql. table_priv/mysql. columns_priv
Update mysql. user set authentication_strings = password ('20140901') where user = 'root' and host = 'localhost ';
# Remote connection
Mysql-h192.168.1.1-ualex-p123
# Add, delete, modify, and search databases (folders)
Create database db1 charset utf8;
Show create database db1;
Alter database db1 charset gbk;
Drop database db1;
# Add, delete, modify, and query tables (files)
Use db1;
Create table t1 (id int primary key auto_increment, name char (10) not null unique) engine = innodb default charset utf8;
Show tables;
Show create table t1;
Alter table t1 add age int;
Alter table t1 drop age int;
Alter table t1 modify id int primary key auto_increment;
Alter table t1 charset utf8;
Drop table t1;
# Viewing table structure
Describe t1; # desc
# Operation file content (add, delete, modify, and query)
Insert into db1.t1 (id, name) values (1, 'egon'), (2, 'egon2 ');
Truncate t1; # Fast deletion speed when the data volume is large
# Create a new table by copying table structures and records (keys are not copied)
Create table t_copy select * from t1;
# Only copy the table structure without copying records
Create table t_copy select * from t1 where 1 = 2;
# View Encoding
\ S
# Display in rows
; Add \ G

I. Data Types
1. Integer type
TINYINT SMALLINT MEDIUMINT INT BIGINT
Note: When you specify a width for this type, you only specify the display width of the query results, regardless of the storage range.
Create table t7 (x int (3) zerofill); fill the first eight digits with 0 (default int (11 ))
2. Floating Point
DECIMAL
FLOAT DOUBLE
Create table t4 (salary float (5, 2); #5 indicates a valid digit, and 2 indicates a decimal place.
Insert into t4 values (-3.33 );
3. string type (the width is the number of characters)
Char: Fixed Length, simple and crude, wasting space, fast access
Varchar: Variable Length, accurate, space-saving, and slow access
4. Date
Datetime 10:00:00
Date: 2017-01-01
Time: 10: 00: 00
Annual: 2017
Insert into student values (1, 'Alex ', now (), now ());
Insert into student values (1, 'Alex ', '2017-01-01', '20170101', '2017-01-01 10:00:00 ', '10: 00: 00 ');
Insert into student values (1, 'Alex ', '2014/1/01', '000000', '2014-01-01 10:00:00', '10: 00: 00 ');
Insert into student values (1, 'Alex ', '000000', '000000', '000000', '000000 ');
5. Enumeration and set (set can be multiple)
Create table student (sex enum ('male', 'female '), hobbies set ('music', 'read '));
Insert into student values ('male', 'music, read ');
If the insert value is not in the range, the storage is empty.

Ii. Table Constraints
1. not null and default
Create table student (
Sex enum ('male', 'female ') not null default 'male ',
Hobbies set ('music', 'read '));
2. unique
# Unique Columns
Create table services (
Name char (10 ),
Host char (15 ),
Port int,
Constraint host_port unique (host, port ));
3. auto_increment
Auto_increment_offset offset (start value), auto_increment_increment step size
# Step Size, effective for the specified table
Create table services (
Id int primary key auto_increment)
Auto_increment = 10;
# Viewing Variables
Show variables like '% auto_in % ';
# Effective for the current session
Set session auto_increment_offset = 200;
Set session auto_increment_increment = 2;
# All replies take effect
Set global auto_increment_offset = 200;
Set global auto_increment_increment = 2;
Note: The offset cannot be greater than the step size. Otherwise, the offset will be ignored. The default value starts from 1.
4. foreign key (on delete cascade delete operation Association)
Create table emp_info (
...
Dep_id int,
Constraint fk_depid_id foreign key (dep_id) references dep_info (id)
On delete cascade
On update cascade
);
Create table dep_info (
Id int primary key auto_increment,
...
);

Iii. Table query
1. Single Table query
1) group
# Aggregate Columns
Select depart_id, group_concat (name) from employee group by depart_id;
# Count
Select depart_id, count (name) from employee group by depart_id;
# Maximum value, minimum value min, sum, avg
Select depart_id, max (name) from employee group by depart_id;
2) keyword execution priority (key)
From
Where
Group
Obtain the new virtual table based on the field after select. If there is an aggregate function, aggregate the data in the group.
Having
Distinct
Order
Limit
3) limit (paging)
Select * from emp limit; # Start, step (1-3)
4) distinct
Select distinct sex from emp;
5) perform operations on the select Field
Select name, salary * 12 annual salary (annual_salary) from emp;

2. Multi-Table query
1) simple query
# Cartesian product to form a virtual table
Select * from department, employee;
# Inner join: obtain the same part according to the on condition
Select * from department, employee where department. id = employee. dep_id;
Select * from employee inner join department on department. id = employee. dep_id;
# Left join: records in the left table are retained after the same part is obtained based on the on condition.
Select * from employee left join department on department. id = employee. dep_id;
# Right join: records in the right table are retained after the same part is obtained based on the on condition.
Select * from employee right join department on department. id = employee. dep_id;
# Full join: Take the same part according to the on condition and keep the records of the left and right tables.
Select * from employee left join department on department. id = employee. dep_id
Union
Select * from employee right join department on department. id = employee. dep_id;

2) subquery
Select * from employee where dep_id in (select id from department where name in ('tech', 'sales '));

Iv. Index
1. Principle
The goal is to improve the query efficiency. The essence is to constantly narrow the scope of the data to be obtained to filter the results.
2. index data structure
The B + tree is a binary search tree that is then evolved from a balanced binary tree and B.
### B + tree Properties
1. index fields should be as small as possible
2. leftmost matching feature of Indexes

V. MySQL index management
1. Common MySQL Indexes

Common INDEX: accelerated search

Unique index:
-Primary key Index primary key: accelerated search + constraint (not empty or repeated)
-UNIQUE index UNIQUE: accelerated search + constraints (repeated indexes are not allowed)

Joint index:
-Primary key (id, name): Federated primary key index
-UNIQUE (id, name): Union of UNIQUE Indexes
-INDEX (id, name): joins common indexes.

2. Two types of indexes: hash and btree
Hash indexes: query results are fast and query results are slow.
B-tree indexes: the more layers of B + trees, the more data volume increases exponentially (we use it because innodb supports it by default)

3. syntax for creating/deleting Indexes
Create an index
When creating a table:
Create table s1 (
Id int,
Name char (6 ),
Index (id) # normal indexes without constraints can only be added to the end
);
After creating a table:
Create index name on s1 (name );
Create index id_name on s1 (id, name); # Join Common index
Create unique index name on s1 (name );

Delete Index
Drop index name on s1;

4. Correct Use of Indexes
1. Scope Problems
2. Use a column with a high degree of discrimination as an index.
3 = and in can be out of order
4. Index Columns cannot be used for calculation. Keep the column "clean". For example, if from_unixtime (create_time) = '2017-05-29 ', indexes cannot be used. The reason is very simple, the B + tree stores the Field Values in the data table.
All elements can be compared only when functions are applied. Obviously, the cost is too high. Therefore, the statement should be written as create_time = unix_timestamp ('2017-05-29 ')
5 and/or
Condition:
A = 10 and B = 'xxx' and c> 3 and d = 4
Index:
Create a joint index (d, a, B, c)
Condition:
A = 10 or B = 'xxx' or c> 3 or d = 4
Index:
Create a joint index (d, a, B, c)

6. leftmost prefix Matching Principle

The second advantage of Federated indexes is that when the first key is the same, the second key has been sorted.

5. Basic Steps for slow Query Optimization
0. Run it first to see if it is really slow. Pay attention to setting SQL _NO_CACHE
1. query the where condition in a single table and lock the minimum returned record table. This statement applies the where statement of the query statement to the table with the minimum number of records returned from the table and starts to check. Each field in a single table is queried separately to see which field has the highest degree of discrimination.
2. explain to check whether the execution plan is as expected in 1 (query starting from a table with fewer locked Records)
3. order by limit SQL statements give priority to sorted tables
4. Understand business application scenarios
5. Refer to the indexing principles when adding Indexes
6. The observed results do not meet the expectation and continue to be analyzed from 0.

6. Slow log management
Slow log
-Execution time> 10
-Index not hit
-Log File Path

Configuration:
-Memory
Show variables like '% query % ';
Show variables like '% queries % ';
Set global variable name = Value
-Configuration file
Mysqld -- defaults-file = 'e: \ wupeiqi \ mysql-5.7.16-winx64 \ mysql-5.7.16-winx64 \ my-default.ini'

My. conf content:
Slow_query_log = ON
Slow_query_log_file = D :/....

Note: After modifying the configuration file, restart the service.

Vi. database backup and recovery
#1. Physical backup: directly copy database files, suitable for large database environments. However, it cannot be restored to a heterogeneous system such as Windows.
#2. Logical backup: it backs up SQL statements executed for table creation, database creation, and insertion. It is suitable for small and medium databases with relatively low efficiency.
#3. Export a table: import the table to a text file.
1. Use mysqldump for logical backup
# Syntax:
# Mysqldump-h server-u user name-p Password Database Name> Backup file. SQL

# Example:
# Single-Database Backup
Mysqldump-uroot-p123 db1> db1. SQL
Mysqldump-uroot-p123 db1 table1 table2> db1-table1-table2. SQL

# Multi-database backup
Mysqldump-uroot-p123 -- databases db1 db2 mysql db3> db1_db2_mysql_db3. SQL

# Back up all databases
Mysqldump-uroot-p123 -- all-databases> all. SQL

Ii. Restore logical backup
# Method 1:
Mysql-uroot-p123 </backup/all. SQL
Mysql-uroot-p123 db1 </backup/db1. SQL
# Method 2:
Mysql> use db1;
Mysql> SET SQL _LOG_BIN = 0;
Mysql> source/root/db1. SQL
Iii. Backup/recovery cases
Iv. automated backup
V. Export and Import tables
Vi. database migration
Http://www.cnblogs.com/linhaifeng/articles/7525619.html

VII. pymysql Module
1. execute
Pip3 install pymysql
Import pymysql
User = input ('user> '). strip ()
Pwd = input ('password> '). strip ()
Conn = pymysql. connect (host = 'localhost', user = 'root', password = '000000', database = 'day47', charset = 'utf8 ')
Cursor = conn. cursor () # The cursor is mysql>
# Execute SQL
SQL = 'select * from user where user = % s and password = % s ;'
Rows = cursor.exe cute (SQL, [user, pwd])
Print ('% s rows in set' % rows) # print rows as the number of affected rows
Cursor. close ()
Conn. close ()
If row:
Print ('sucessful ')
Else:
Print ('failed ')

2. add, delete, and modify
SQL = 'insert into user (name, password) values (% s, % s );'
Rows = cursor.exe cute.pdf (SQL, [(user, pwd), (user, pwd)]) # execute
Print (cursor. lastrowid) # query the last auto-increment ID
Print ('% s rows in set' % rows) # print rows as the number of affected rows
Conn. commit ()

3. Check fetchone, fetchall, and fetchall. None is returned.
SQL = 'select * from user ;'
Rows = cursor.exe cute (SQL)
Res1 = cursor. fetchone () # tuples
Res2_4 = cursor. fetch (3) # The tuples are nested. None is returned.
Print ('% s rows in set' % rows) # print rows as the number of affected rows
Conn. commit ()

4. move the cursor
Cursor. scroll (0, mode = 'absolute ') # absolute position
Cursor. scroll (0, mode = 'relative ') # relative Position

8. View
Definition: A view is a virtual table that saves frequently-queried SQL statements as a view for reuse.
1. Syntax:
Create view user_dep
As
Select user. id uid, user. name uname, dep. id depid, dep. name depname
From user left join dep on user. dep_id = dep. id;
# View a view
Show create table user_dep;
# Modifying views
Alter view user_dep as new query statement;

IX. triggers
Create
TRIGGER trigger_name
Trigger_time trigger_event
On tb1_name for each row
Begin
...;
End

Delimiter //
Create
TRIGGER tri_after_insert_assist_log
After insert
On every _log for each row
Begin
If new. is_success = 'no' then
Insert into err_log (partition _name, sub_time) values (NEW. Partition _name, NEW. sub_time );
End if;
End //
Delimiter;

# Delete a trigger
Delete trigger trigger_name;

10. Transactions
A transaction is used to take multiple SQL statements of some operations as atomic operations. Once an error occurs, the transaction can be rolled back to the original state to ensure database data integrity.
Start transaction;

Rollback; # If any SQL exception occurs, use this command to roll back
Commit; # If all SQL statements are normal, use this command to submit

11. Stored Procedure
The Stored Procedure contains a series of executable SQL statements. The stored procedure is stored in MySQL. By calling its name, you can execute a bunch of internal SQL statements.
1. No Parameters
Delimiter //
Create procedure procedure_name ()
Begin
...
End //
Delimiter;
# Call a stored procedure
Call procedure_name ();
Cursor. callproc ('p1 ');

2. Parameters
Delimiter //
Create procedure procedure_name (
In min int,
In max int,
Out res int
)
Begin
Select * from test where id between min and max;
Set res = 1;
End //
Delimiter;

# Call a stored procedure
Set @ n = 1 # global variables
Call p1 (3,7, @ n );
Select @ n; # view variable values

# Python call
Import pymysql
Conn = pymysql. connect (host = 'localhost', user = 'root', password = '000000', database = 'db1', charset = 'utf8 ')
Cursor = conn. cursor (cursor = pymysql. cursors. DictCursor)
Res = cursor. callproc ('p1', args = (3,7, random) # _ p1_0 = 3, _ p1_1 = 7, _ p1_2 = 123
Conn. commit ()
Print (res. fetchall () # Only select results of the stored procedure can be obtained.
Rows = cursor.exe cute ('select @ _ p1_0, @ _ p1_1, @ _ p1_2 ')
Print (cursor. fetchall ())
Cursor. close ()
Conn. close ()

# Inout Parameters
Delimiter //
Create procedure p1 (
Inout m int
)
Begin
Select * from test where id> m;
Set m = 1;
End //
Delimiter;

# Mysql
Set @ x = 2
Call p1 (@ x)
Select @ x

3. View stored procedures
Show create procedure p1 \ G
# View all stored procedures (a class uses like)
Show procedure status like 'p1% ';

4. delete a stored procedure
Drop procedure proc_name;

12. Application of stored procedures to transactions
Delimiter //
Create PROCEDURE p5 (
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
Set p_return_code = 1;
Rollback;
END;

DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
Set p_return_code = 2;
Rollback;
END;

Start transaction;
DELETE from tb1; # execution failed
Insert into blog (name, sub_time) values ('yyy', now ());
COMMIT;

-- SUCCESS
Set p_return_code = 0; #0 indicates that the execution is successful.

END //
Delimiter;

# Calling stored procedures in mysql
Set @ res = 123;
Call p5 (@ res );
Select @ res;

# Calling a stored procedure based on pymysql in python
Cursor. callproc ('p5 ', (123 ,))
Print (cursor. fetchall () # query the select query result

Cursor.exe cute ('select @ _ p5_0 ;')
Print (cursor. fetchall ())

 

12. Functions
1. built-in common functions
1) mathematical functions
ROUND (x, y)
Returns x rounded to y decimal places.

RAND ()
Returns a random value between 0 and 1. You can provide a parameter (SEED) to generate a specified value for the RAND () random number generator.

2) Aggregate functions (usually used in SELECT queries of group by clauses)
AVG (col) returns the average value of the specified Column
COUNT (col) returns the number of non-NULL values in the specified column.
MIN (col) returns the minimum value of the specified Column
MAX (col) returns the maximum value of the specified Column
SUM (col) returns the SUM of all values in the specified column.
GROUP_CONCAT (col) returns the result of a combination of column values.

3) string functions
CHAR_LENGTH (str)
View the number of characters.
CONCAT (str1, str2 ,...)
String concatenation
If any parameter is NULL, the return value is NULL.
CONCAT_WS (separator, str1, str2 ,...)
String concatenation (custom connector)
CONCAT_WS () does not ignore any null strings. (However, all NULL values are ignored ).

CONV (N, from_base, to_base)
Hexadecimal conversion
For example:
Select conv ('A',); converts a from hexadecimal to a binary string.
FORMAT (X, D)
Write the number X format '#,###,###. # ', retain the D-digit after the decimal point and return the result as a string. If D is 0, the returned result does not contain the decimal point or decimal part.
For example:
Select format (12332.1, 4); the result is: '20140901'
INSERT (str, pos, len, newstr)
Insert a string at the specified position of str
Pos: to replace the actual position
Len: length of replacement
Newstr: New String
Special:
If the pos value exceeds the length of the original string, the original string is returned.
If len exceeds the length of the original string, it is completely replaced by the new string.
INSTR (str, substr)
Returns the first occurrence position of the str substring.

LEFT (str, len)
Returns the substring of str from the starting len position.
LOWER (str)
Small write
UPPER (str)
Change to uppercase
REVERSE (str)
Returns the str string, which is in the opposite order of characters.

SUBSTRING (str, pos), SUBSTRING (str FROM pos) SUBSTRING (str, pos, len), SUBSTRING (str FROM pos FOR len)
Returns a substring from the str string in a format that does not contain the len parameter, starting from the position pos. The format with the len parameter returns a substring with the same length as the len character from the string str, starting from the position pos. Use FROM
The format is standard SQL syntax. It may also use a negative value for pos. In this case, the position of the substring starts with the pos character at the end of the string, rather than the start position of the string. You can use a negative value for pos in the following format.

4) Date and Time Functions
CURDATE () or CURRENT_DATE () returns the current date
CURTIME () or CURRENT_TIME () returns the current time
DAYOFWEEK (date) returns the Day (1 ~ 7)
DAYOFMONTH (date) returns the Day (1 ~ 31)
DAYOFYEAR (date) returns the Day (1 ~ 366)
DAYNAME (date) returns the week name of date, for example, select dayname (CURRENT_DATE );
FROM_UNIXTIME (ts, fmt) format the UNIX timestamp ts Based on the specified fmt Format
HOUR (time) returns the HOUR value of time (0 ~ 23)
MINUTE (time) returns the MINUTE value of time (0 ~ 59)
MONTH (date) returns the MONTH value of date (1 ~ 12)
MONTHNAME (date) returns the month name of date, for example, select monthname (CURRENT_DATE );
NOW () returns the current date and time
QUARTER (date) returns the QUARTER (1 ~ 4), such as select quarter (CURRENT_DATE)
WEEK (date) returns the WEEK (0 ~ 53)
YEAR (date) returns the YEAR (1000 ~ 9999)

5) encryption functions
MD5 ()
Calculate the MD5 checksum of the str string.
PASSWORD (str)
Returns the encrypted version of the string 'str'. This encryption process is irreversible and uses different algorithms than the UNIX password encryption process.

6) control flow Functions
Case when [test1] THEN [result1]... ELSE [default] END
If test1 is true, result1 is returned; otherwise, default is returned.

CASE [test] WHEN [val1] THEN [result]... ELSE [default] END
If test and val1 are equal, result is returned; otherwise, default is returned.

IF (test, t, f)
If test is true, t is returned; otherwise, f is returned.

IFNULL (arg1, arg2)
If arg1 is not empty, arg1 is returned; otherwise, arg2 is returned.

NULLIF (arg1, arg2)
If arg1 = arg2, NULL is returned; otherwise, arg1 is returned.
Isnull (arg)
Null returns true

 

Built-in functions:
1. char_length: Check the number of characters
Select char_length (name) from t1;
# Set the character filling mode to full character matching mode
Set SQL _mode = 'pad _ CHAR_TO_FULL_LENGTH ';
2. length: view the number of bytes
3. date_format
Select date_format ('2017-01-01 11:11:11 ',' % Y-% m-% d % H: % I: % s ')
Example:
Extract the value of the sub_time field and group it by the formatted result, namely, "year and month ".
SELECT DATE_FORMAT (sub_time, '% Y-% m'), COUNT (1) FROM blog group by DATE_FORMAT (sub_time,' % Y-% m ');

4. User-Defined Functions
Delimiter //
Create function f1 (
X int,
Y int)
Returns int
Begin
Declare num int;
Set num = x + y;
Return (num );
End //
Delimiter;
# Deleting a function
Drop function func_name;
# Execution function
Select UPPER ('egon') into @ res;
SELECT @ res;
# Use in Query
Select f1 (11, nid), name from tb2;

XIII. Process Control
1. Condition statements
Delimiter //
Create function f5 (
I int
)
Returns int
Begin
Declare res int default 0;
If I = 10 then
Set res = 100;
Elseif I = 20 then
Set res = 200;
Elseif I = 30 then
Set res = 300;
Else
Set res = 400;
End if;
Return res;
End //
Delimiter;

2. Loop statements
Delimiter //
Create procedure proc_while ()
BEGIN

DECLARE num INT;
SET num = 0;
WHILE num <10 DO
SELECT
Num;
SET num = num + 1;
End while;

END //
Delimiter;

 

 

# My. ini

[Client]
Default-character-set = utf8
User = 'root'
Password = '000000'

[Mysql]
Default-character-set = utf8

[Mysqld]
Character_set_server = utf8
Secure_file_priv = 'e :\\'