编程知识 cdmana.com

MYSQL

view the database

show databases;

Select database

use  Database name ;

View database tables

show tables;

Create database

create database  Database name ;

Delete database

drop database  Database name ;

Delete database tables

drop table  Table name ;

insert data

insert into  Table name ( Field )values( data );

Query data

select * from  Table name ;
select * from  Table name  where  Field =' Conditions ';

Update data

update  Table name  set  Field =' Modified data ' where  Field =' Selected fields ';

Delete data

delete from  Table name  where  Field =' Selected fields ';
 matters needing attention :
 without where,MySQL Will delete all data .

LIKE( Fuzzy query )

select * from  Table name  where name LIKE 'java%';( Query to Java Information at the beginning )
select * from  Table name  where name LIKE '%java';( Query to Java The message at the end )
select * from  Table name  where name LIKE '%java%';( The query contains Java Information about )
 Indexes :% Fuzzy queries at the beginning will invalidate the index .
%: Multiple characters .
_: Single character .

UNION(select Connect )

select  Field  from  Table name 1 union(distinct | all) select  Field  from  Table name 2;
DISTINCT: Delete duplicate data in result set . By default  UNION  The operator has removed the duplicate data .
ALL: Return all result sets , Contains duplicate data .

ORDER BY( Sort )

select * from  Table name  order by  Field  ASC;( Sort by ascending field )
select * from  Table name  order by  Field  desc;( Sort by field in reverse order )
 Sort ascending by default , therefore ASC It can be omitted .

GROUP BY( grouping )

select  Field 1, Field 2 from  Table name  group by  Field 1;
select  Field 1, Field 2 from  Table name  group by  Field 1 WITH ROLLUP;
WITH ROLLUP: With null To summarize .

DISTINCT( To repeat )

select distinct * from  Table name ;

LIMIT( Pagination )

select * from table1 limit  The current page , Number of data 

Table joins

INNER JOIN( Internal connection or equivalent connection ): Get the records of field matching relationship in two tables .
LEFT JOIN( Left connection ): Get all the records in the left table , Even if the right table does not have a matching record .
RIGHT JOIN( The right connection ): Get all the records in the right table , Even if the left table does not have a matching record .

select * from  Table name 1 a INNER JOIN  Table name 2 b on a. Field 1=b. Field 2;
select * from  Table name 1 a LEFT JOIN  Table name 2 b on a. Field 1=b. Field 2;
select * from  Table name 1 a RIGHT JOIN  Table name 2 b on a. Field 1=b. Field 2;

INNER JOIN( Internal connection ): Take the intersection of two tables 
LEFT  JOIN( Left connection ): Take the intersection of the left table and the two tables 
RIGHT JOIN( The right connection ): Take the intersection of the right table and the two tables 

 Multi-table query :
select * from  surface 1 a inner join  surface 2 b on a. Field =b. Field  
left join  surface 3 c on b. Field =c. Field ;
Internal connection Left connection The right connection
image.png image.png image.png

Database type

value type

type size purpose
TINYINT 1byte Small integer values
SMALLINT 2byte Big integer values
MEDIUMINT 3byte Big integer values
INT or INTEGER 4byte Big integer values
BIGINT 8byte Maximum integer value
FLOAT 4byte Single precision floating point values
DOUBLE 8byte Double precision floating point value
DECIMAL Yes DECIMAL(M,D) , If M>D, by M+2 Otherwise D+2 Small value

The date type

type size (byte) Format purpose
DATE 3 YYYY-MM-DD Date value
TIME 3 HH:MM:SS Minutes and seconds
YEAR 1 YYYY The year is worth
DATETIME 8 YYYY-MM-DD HH:MM:SS Date time
TIMESTAMP 4 YYYYMMDD HHMMSS Date time

String type

type size purpose
CHAR 0-255 bytes Fixed length string
VARCHAR 0-65535 bytes Variable length string

MYSQL Business

1、mysql Only used in innodb The database or table of the database engine supports transactions .
2、mysql Medium myisam The database engine does not support transactions .
3、 Transactions can be used to maintain database integrity , Make sure that there are lots of SQL Statement or all execution ,
 Or none at all .
4、 Affairs are used to manage insert、update、delete

Transactions must be satisfied 4 Conditions (ACID):

1、 Atomicity : Or it's all done , Or not at all .
2、 Uniformity : Before and after transaction start , The integrity of the database is not compromised .
3、 Isolation, : There is no interaction between transactions .
4、 persistence : At the end of the transaction , A permanent preservation of data .

The isolation level of the transaction :

1、 Read submitted 
2、 Read uncommitted 
3、 Repeatable 
4、 Serialization 

MySQL Transaction processing implementation :

BEGIN        Start a transaction 
ROLLBACK     Transaction rollback 
COMMIT       Transaction confirmation 

MYSQL Indexes

1、mysql Indexes are sorted data structures (B+ Tree structure ).
2、 Index files that take up disk space .
3、 For frequently modified data , Not suitable for indexing , It will break the ring B+ Tree structure .

Indexes

 Create a normal index : No restrictions 
create index  Index name  on  Table name ( Field );

 Create unique index : The value of the index column must be unique , Null value allowed .
create unique index  Index name  on  Table name ( Field );

 Delete index 
drop index  Index name  on  Table name ;

 Show index information 
show index from  Table name ; 

MYSQL Sub database and sub table

vertical ( The longitudinal ) segmentation

level ( The transverse ) segmentation

版权声明
本文为[LLL333]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/01/20210105180251792s.html

Tags mysql
Scroll to Top