Made By Herolh

Catalog

[TOC]

1. What is MySQL

Server side Client

Mysql database

What is a database

Characteristics of relational database

• Data in tabular form
• Name of each record
• Each column is the data field corresponding to the record name
• Many rows and columns make up a form
• Several forms form database

--Relational databases keep data in different tables instead of putting all data in one large warehouse, which increases speed and flexibility.

Advantages of MySQL database

• Mysql is open source, so you don't have to pay extra.
• Mysql supports large databases. It can handle large databases with tens of millions of records.
• MySQL uses the standard SQL data language form.
• Mysql can be allowed on multiple systems and has good support for multiple languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.
• MySQL supports a large database, a data warehouse with 50 million records, a maximum of 4GB for 32-bit system table files, and 8TB for 64 bit system.
• Mysql can be customized. With the GPL protocol, you can modify the source code to develop your own Mysql system.

Skill

install

• Source installation / exe
• Initialization
  • Start server
  • Client connection
    • Send instruction
  • environment variable

Connect:

mysql -u root [-h Host] -p

# View mysql process (under LINUX)
ps -ef |grep mysql 	

Database operations

Database level operation:

status: View default settings
 desc table name; view table structure

Data table operation:

• data type
  • constraint
    • unique index
    • Primary key constraint
    • Foreign key
      • One-on-one
      • One to many
      • Many to many
  • Self increment

Data line operation:

• Additions and deletions
  • sort
  • Grouping
  • condition
  • cursor
  • Linked list
  • wildcard
  • paging
  • combination

view

trigger

function

stored procedure

• cursor
• Transaction logic:

pymysql

• Connect connect()
  • Operation (cursor)
    • Add, delete and change: commit
    • fetchone, fetchall, fetchany
  • Stored procedure call mode
    • callproc("name", parameter)
    • select @? Stored procedure name? 0
  • SQL injection
    • Close cursor
    • Close connection

II. MySQL operation

Create user

create user 'username'@'pasword' *identified by* 'password'	

Syntax code

use mysql;
grant create,delete,drop,update,insert,select   # Or write grant all directly
	on TUTORIALS.*                  # All databases. All tables
	to 'username'@'localhost'  
	# User name @ from which machine to log in '%' for any host
	IDENTIFIED by 'password'        # Login password
	;

To grant authorization

grant all privileges *on Library name.Table name to*  'username'@'%' 	

Cancellation of authorization

revoke al privileges on Library name.Table name *from* 'username'@'%'

view user permission

show grants for user name;

Character encoding

charset = GBK;
create Library name default charset = utf8;

To view the character set:

• View view MySQL database server and database mysql character set.

  show variables like '%char%'; 
  

  # Output result
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8     (Client character set)      |
  | character_set_connection | utf8                       |
  | character_set_database   | latin1   (Database character set)      |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8                       |
  | character_set_server     | latin1   (Server character set)      |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+
  

• View the MySQL character set of the MySQL data table.

  show table status from sqlstudy_db like '%countries%'; 
  

  # Output result
  +---------+--------+--------+------------+------+-----------------+ 
  | Name    | Engine | Version| Row_format | Rows | Collation       | 
  +---------+--------+--------+------------+------+-----------------+
  |countries| InnoDB |     10 | Compact    |   11 | utf8_general_ci | 
  +---------+--------+--------+------------+------+-----------------+
  

• View the MySQL character set for the MySQL data column.

  show full columns from countries;  
  

  +----------------------+-------------+-----------------+
  | Field                | Type        | Collation       | 
  +----------------------+-------------+-----------------+ 
  | countries_id         | int(11)     | NULL            | 
  | countries_name       | varchar(64) | utf8_general_ci | 
  | countries_iso_code_2 | char(2)     | utf8_general_ci |
  | countries_iso_code_3 | char(3)     | utf8_general_ci |  
  | address_format_id    | int(11)     | NULL            | 
  +----------------------+-------------+-----------------+
  

Modify character set

Temporary modification

Modify global character set

/*Code used to establish connection*/
set character_set_connection=utf8;
/*Coding of database*/
set character_set_database=utf8;
/*Encoding of result set*/
set character_set_results=utf8;
/*Coding of database server*/
set character_set_server=utf8;

Modify the character set of the library

alter database library name default character set character set;

Modify the character set of the table

alter table table name convert to character set character set;

Modify the character set of the field

alter table name modify field name field attribute character set gbk;

Permanent modification

• Modify mysql configuration file

  sudo vi /etc/my.cnf
  

• Add the following settings above [mysqld]:

  default-character-set=utf8
  

• Add the following settings under [mysqld]:

  character-set-server=utf8
  collation-server=utf8_general_ci
  

Atomic operation

engine = inodb

Database operations

view the database

show databases;

Create database

create database database database name; ා by default, Chinese cannot be processed
 create database library name charset utf8; solve the problem of Chinese character processing:

Delete database

drop database library name;

Enter database

use database name;

View all tables

show tables;

View table structure

desc table name;
- or
 Show colors from table name;

Additions and deletions

Create data table

CREATE TABLE table_name (column_name column_type);

insert data

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

Query data

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

UPDATE update data

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DELETE statement

DELETE FROM table_name [WHERE Clause]

data type

int decimal datatime char，text

Gender enum( '','' ) username char
set()

String type:

Number type

Date type

Other data types

constraint

• unique constraint
• primary key constraint
• Foreign key constraint foreign key name foreign key (column name) references appearance (column name)

Automatic increment

*auto_increment

wildcard

• %

• _

Arithmetic, logic, comparison operators

order by

• asc
• desc

group by

• Multi table connection

• Left and right tables: join

• union table: union

  • Automatic de duplication:

    select * from Table 1		(Ten assumptions)
    union
    select * from Table 2		(Ten assumptions)
    (Back to 20)
    

  • Not heavy

    select * from Table 1		(Ten assumptions)
    union
    select * from Table 1		
    (Back to 20)
    

Paging (limit)

select * from table name limit 0,10;

(the larger the page is, the slower it will be) - > acceleration method: scan only the index

Acceleration mode 1: index acceleration

# Overwrite index, but not much faster
select * from userinfo3 where uid in ( select uid from limit 20000,10)

Best way to accelerate:

# Last ten of current page
select * from userinfo3 where uid > 20000 limit 10;					
# Top 10 items on current page
select * from userinfo3 where uid < 20000 order by id desc limit 10;

Pagination method:

The page has only the previous and next pages:

# Previous page:
select * from userinfo3 where uid > max_id limit 10;
# Next page:
select * from userinfo3 where uid < min_id order by id desc limit 10;

Previous 192 193 [196] 197 198 199 next

# Previous page:
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)

III. MySQL advanced programming

view

Create views (reuse of temporary tables)

create views view name as SQL statement (select...). Get data from real table dynamically

When using a view, it can be operated as a table. Because the view is a virtual table, it cannot be used to create, update, and delete the real table. It can only be used for query.

To modify a view:

alter views view name as SQL statement (select

To delete a view:

 drop views view name

Triggers (database level operations)

Create trigger

create trigger trigger name before/after (SQL behavior statement) ON table and Library

eg:

create trigger tri_before_insert before insert ON tb1 [For EACH ROW Trigger for each row inserted ]
begin
	...;
end

create trigger tri_before_insert after insert ON tb1 For EACH ROW
begin
	...;
end

Be careful:

#Query will not trigger

Function (low performance)

Execute function:

select function name ();

Built in functions:

Aggregate function

select CURDATE();			# May 15th, 2018
select CURRENT_TIMESTAMP();	#  May 15, 2018 20:53:36 - year month day hour: minute: Second
select CHAR_LENGTH(str);	# String str length
select CONCAT(str1,str2)	# String splicing

Custom function:

delimiter //
create function f1(
	i1 int,		--Afferent parameter
	i2 int 
)
return int
begin
	-- Cannot write select * from Table name, etc. SQL behavior
	declare num int default 0;	# Define temporary variables
	set num = i1 + i2;
	return(num);
END//
delimiter ;

Stored procedures (more important)

1. Create stored procedure

Parameterless stored procedure

delimiter //
create procedure p1()
begin
	INSERT INTO userinfo(username,password) VALUES("user5","pd5");
	SELECT *  FROM userinfo;
end//
delimiter ;

Parameter stored procedure: (key: in\out\inout)

delimiter //
create procedure p2(
	in n1 int,
	in n2 int
)
begin
	select * from userinfo where id > n1;
end//
delimiter ;

• The stored procedure does not have the concept of return value. out is to pass in a variable, and then you can change the value of the variable in the stored procedure

• out does not pass values to the inside, that is, although @ var=1 is outside, in fact, @ var cannot be used in p3

  delimiter //
  create procedure p3(
  	in n1 int,
  	out n2 int
  )
  begin
  	set n2 = 123123;
  	select * from userinfo where id > n1;
  end//
  delimiter ;
  
  set @var = 1;			# Define a variable @ var = 0
  call p3(8,@var );
  select @var				# @var = 123123
  

• inout: you can transfer values to the stored procedure or to the external

! Be careful:

• Why do we need to have a result set and an out fake return value?

  out is used to set a value that identifies the execution result of the stored procedure

2. Call the stored procedure:

call p1();

Advantages and disadvantages of stored procedures

Benefits:

Disadvantages:

How to work in the future:

Mode 1: stored procedure

MySQL: stored procedure Programs: calling stored procedures

Mode 2: SQL statement

MySQL: . . Programs: SQL statements

Mode 3: ORM framework

MySQL: . . Programs: classes and objects (SQL statements)

Transaction (low performance)

Transaction logic:

delimiter //
create procedure p4(
	out status int
)
BEGIN
	# Declare execute if exception occurs
	{
		set status = 1;
		rollback;
	}

	#Start business
	SQL;
	commit;

	# End
	set status = 2;
END //
delimiter ;

Syntax code

delimiter \\
create PROCEDURE p5(
	OUT p_return_code tinyint
)
BEGIN 
	DECLARE exit handler for sqlexception 		--Declare execute if exception occurs
	BEGIN 
		-- ERROR 
		set p_return_code = 1; 
		rollback; 
	END; 

	START TRANSACTION; 							--Start business
		DELETE from tb1;
		insert into tb2(name)values('seven');
	COMMIT; 

	-- SUCCESS 
	set p_return_code = 2; 
END\\
delimiter ;

Cursor (poor performance)

Create cursors

declare my_cursor CURSOR FOR [SQL Sentence]select * from A；

Use cursors

fetch my_cursor into row_namename,row_psd;

An example of a cycle:

delimiter \\
create procedure p6()
BEGIN
	DECLARE row_name varchar(20);		-- Customize a variable 1
	DECLARE row_psd varchar(50);		-- Customize a variable 2
	declare done int default False;		-- Custom loop end flag

	declare my_cursor CURSOR FOR select username,password from userinfo;
	declare CONTINUE HANDLER FOR NOT FOUND set done = TRUE;				
	-- Change loop end flag when cursor is empty

	open my_cursor;						-- Open cursor
		SIGN:LOOP						-- Start loop flag
			fetch my_cursor into row_namename,row_psd;	-- Get cursor content
				if done then 				
					leave SIGN;				-- breake
				end if;
				insert into userinfo2( row_namename,row_psd) values( row_name,row_psd);
		end loop SIGN;					-- Turn off cycle flag
	close my_cursor;					-- Close cursor
END\\
delimiter ;

Dynamic execution of SQL statements: (anti SQL injection)

Pseudo code:

delimiter \\
create procedure p7(
	in str varchar(255),			# Place the SQL statement to execute
	in arg int
)
begin 
	1. Pre detect something SQL Statement validity
	2. SQL =Format tpl + arg 
	3. implement SQL Sentence
end

Syntax code

delimiter \\
CREATE PROCEDURE p7(
	in nid int
)
BEGIN
	set @nid = nid;				-- because execute Only use@ So assign him a value
	PREPARE prod FROM 'select * from student where sid > ?';		
	-- Pre test SQL Sentence
	EXECUTE prod USING @nid;										
	-- Splicing
	DEALLOCATE prepare prod; 
END\\
delimiter ;

Index (speed lookup)

Effect:

constraint

Accelerated search

Slow find:

select * from tb where Column names="..."

Quick find:

select * from tb where id = 65

No index:

Index:

• Create additional files (some form of storage) to hold special data structures
• Query fast, insert update delete slow
• Hit index (version and version, database and database have different standards)

Index category

Classification by action

• Primary key index: accelerated search + cannot be empty + cannot be duplicate
• General indexes: accelerating discovery
• Unique index: accelerated search + cannot be repeated
• Union index (multiple columns):
  • Union primary key index
  • Union unique index
  • Joint general index

special

• Override index:

  --Get data directly in index file
  select id from userinfo3 where email = ".....";
  

• Index merge:

  --Combine multiple single column indexes
  select id from userinfo3 where email = "....." and pd = "...";	
  

By implementation

hash index

• Single value fast
• Slow range

btree index (by default)

• Check by binary tree
• fast

Index creation

General index:

create index index name on table name (column name);

Unique index:

create unique index index name on table name (column name);

Joint index:

create unique index index name on table name (column name 1, column name 2);

Leftmost prefix match:

select * from table name where column name 1 = xxx and column name 2 = xxx; -- go to index
 select * from table name where column name 2 = xxx; -- no index
 --Union index is more efficient than index merging

Short index:

create index index name on table name (column name (16)) -- index the data after 16 bytes of column name 1

ORM framework operations (relational object mapping)

• When a class of functions share the same parameters, it can be transformed into a class for classification

• Object oriented: combination of data and logic (attributes and behaviors) Functional programming: separation of data and logic

• Template constraints

  Extraction commonality A kind of things have in common: attribute and behavior

Effect

• Provide simple gauge
• Automatically convert to SQL statement

ORM framework category:

• DB first:

  graph LR A (create database and table manually) -- > b (ORM framework) B -- > C (auto generate class)

• code first:

  SQLAlchmy belongs to this class

  graph LR A (create classes and databases manually) -- > b (ORM framework) B -- > C (and table)

Matters needing attention

No like

Avoid using functions

Avoid or

Special: only when there are unindexed columns in the or condition, the following will be indexed

select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

The type should be consistent, and the type conversion function will be used if the type is inconsistent

The general index does not follow the following syntax

!=:

select * from tb1 where email != '...'
-- Special: if it is a primary key, it will still go to index
select * from tb1 where nid != 123

>:

select * from tb1 where email > 'alex'
-- Special: if the primary key or index is of integer type, the index will still be used
select * from tb1 where nid > 123
select * from tb1 where num > 123	

order by

select name from tb1 order by email desc;( Inconsistent index )
-- When sorting by index, if the selected map is not an index, the index will not be taken
-- Special: if the primary key is sorted, the index is still used:
select * from tb1 order by nid desc;

IV. precautions for MySQL

• Avoid using select*

• count(1) or count (column) instead of count(*)

• Try to replace varchar with char when creating tables

• Table fields order fixed length fields take precedence

• Composite index instead of multiple single column indexes (when multiple conditional queries are often used)

• Try to use a short index

• Use JOIN instead of sub queries

  --There is no difference in MYSQL
  

• Note that condition types should be consistent when connecting tables

• Index hash value (less repetition) is not suitable for indexing, e.g. gender is not suitable

• Because mysql is the statement terminator by default, there will be an error when executing the [begin...end] inside the compound statement. The solution is as follows:

  The delimiter / / modify statement terminator is//
  create trigger tri_before_insert after insert ON tb1 For EACH ROW
  begin
  ...;
  end//
  delimiter; - restore to avoid interference with other statements
   Supplement:
  --NEW refers to NEW data (when added)
  insert into tb() values( NEW.user );
  --OLD refers to OLD data (when deleting) 
  

Supplement:

• NEW refers to NEW data (when added)

  insert into tb() values( NEW.user );
  

• OLD refers to OLD data (when deleting)

V. DBA work

Slow log

• Execution time > 10
• Miss index
• log file path

To configure:

Mode 1: memory

show variables like '%query%'
set global Variable name = value
set global slow_query_log = ON;			--Turn on slow log, turn off by default

Mode 2: specify the 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:/....

Mode 3: modify in Mysql configuration file