_ Rui_ ao 2022-02-13 07:07:09 阅读数:807
preparation ： install MySQL database
mysql -u root -p create database CLASS; use CLASS; create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text); desc member; insert into member values (1,'zhangsan','123','111111','nanjing','this is vip'); insert into member values (4,'lisi','1234','444444','nanjing','this is normal'); insert into member values (2,'wangwu','12345','222222','benjing','this is normal'); insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip'); insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip'); select * from member;
Create a table ：member Field one ：id integer Maximum display length 10 position Field 2 ：name varchar（10） most 10 character Field 2 ：cardid varchar（18） most 18 character Field four ：phone varchar（11） most 11 character Field five ：address varchar（50） most 50 character Field 6 ：remark text： The text type Can store about 64kb
Is a table member Insert new data content
Side effects of indexing
Indexing can speed up database queries , But it's not always appropriate to create an index . Because the index itself consumes system resources , With an index , The database will perform index query first , Then navigate to the specific data row , If the index is not used properly , On the contrary, it will increase the burden of the database .
The most basic index type , There's no such thing as uniqueness .
Create index directly
CREATE INDEX Index name ON Table name ( Name [(length)]);
#( Name (length))：length Optional. . If you ignore length Value , Then use the value of the entire column as the index . If specified, use the... Before the column length Characters to create the index , This helps to reduce the size of the index file . # The index name is suggested to be “_index” ending .
example ：create index phone_index on member (phone); select phone from member; show create table member;
Modify tables to create
ALTER TABLE Table name ADD INDEX Index name ( Name ); example ：alter table member add index id_index (id); select id from member; select id,name from member;
Specify index when creating table
CREATE TABLE Table name ( Field 1 data type , Field 2 data type [,...],INDEX Index name ( Name )); example ：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id)); show create table test;
Create a unique index directly
CREATE UNIQUE INDEX Index name ON Table name ( Name ); example ：select * from member; create unique index address_index on member (address); create unique index name_index on member (name); show create table member;
Modify tables to create
ALTER TABLE Table name ADD UNIQUE Index name ( Name ); example ：alter table member add unique cardid_index (cardid); Modify table member increase unique unique index Index name ：cardid——index（ Field name ）
When creating a table, specify
CREATE TABLE Table name ( Field 1 data type , Field 2 data type [,...],UNIQUE Index name ( Name )); example :create table amd2 (id int,name varchar(20),unique id_index (id)); show creat table amd2;
When creating a table, specify
CREATE TABLE Table name ([...],PRIMARY KEY ( Name )); example ：create table test1 (id int primary key,name varchar(20)); create table test2 (id int,name varchar(20),primary key (id)); show create table test1; show create table test2;
primary key Specify... When creating a table , There are two ways One is to directly specify... In the field The second is to specify... After the end of the field
Modify tables to create
It can be an index created on a single column , It can also be indexes created on multiple columns .
CREATE TABLE Table name ( Name 1 data type , Name 2 data type , Name 3 data type ,INDEX Index name ( Name 1, Name 2, Name 3)); select * from Table name where Name 1='...' AND Name 2='...' AND Name 3='...'; example :create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name)); show create table amd1; insert into amd1 values(1,'zhangsan','123123'); select * from amd1 where name='zhangsan' and id=1;
It is suitable for fuzzy query , Can be used to retrieve text information in an article . stay MySQL5.6 Version before FULLTEXT The index can only be used for MyISAM engine , stay 5.6 After the version innodb The engine also supports FULLTEXT Indexes . Full text indexing can be done in CHAR、VARCHAR perhaps TEXT Create on column of type . Only one full-text index per table is allowed .
Create index directly
CREATE FULLTEXT INDEX Index name ON Table name ( Name ); example ：select * from member; create fulltext index remark_index on member (remark);
Modify tables to create
ALTER TABLE Table name ADD FULLTEXT Index name ( Name );
Specify index when creating table
CREATE TABLE Table name ( Field 1 data type [,...],FULLTEXT Index name ( Name ));
# The data type can be CHAR、VARCHAR perhaps TEXT
Use full text index queries
SELECT * FROM Table name WHERE MATCH( Name ) AGAINST(' Query content '); example ：select * from member where match(remark) against('this is vip');
show index from Table name ; show index from Table name \G; Display table index information vertically show keys from Table name ; show keys from Table name \G; example ：show index from member;
The meaning of each field is as follows ：
|Table||The name of the table|
|Non_unique||If the index can't include repetition , Then for 0; If possible , Then for 1.|
|Key_name||Name of index .|
|Seq_in_index||Column ordinal in index , from 1 Start .|
|Column_name||Column name .|
|Collation||How columns are stored in indexes . stay MySQL in , Valuable ‘A’（ Ascending ） or NULL（ No classification ）.|
|Cardinality||An estimate of the number of unique values in an index .|
|Sub_part||If the column is only partially indexed , Is the number of characters indexed . If the entire column is indexed , Then for NULL.|
|Packed||Indicates how keywords are compressed . If it's not compressed , Then for NULL.|
|Null||If the column contains NULL, It contains YES. without , The column contains NO.|
|Index_type||Used indexing methods （BTREE, FULLTEXT, HASH, RTREE）.|
Delete index directly
DROP INDEX Index name ON Table name ; example ：drop index name_index on member;
Modify the table to delete the index
ALTER TABLE Table name DROP INDEX Index name ;
example ：alter table member drop index id_index; show index from member;
Delete primary key index
ALTER TABLE Table name DROP PRIMARY KEY;
To put it bluntly , Transaction , It's a sequence of operations , These operations are either performed , Either not , It is an indivisible unit of work .
ACID, In a reliable database management system （DBMS） in , Business (transaction) It should have four characteristics ： Atomicity （Atomicity）、 Uniformity （Consistency）、 Isolation, （Isolation）、 persistence （Durability）. These are the characteristics of a reliable database .
Atomicity ： A transaction is an indivisible unit of work , Either the operations in the transaction occur , Or none at all .
A transaction is a complete operation , The elements of a transaction are indivisible . All elements in a transaction must be committed or rolled back as a whole . If any element in the transaction fails , Then the whole transaction will fail .
Uniformity ： Before and after the transaction , The database integrity constraint is not broken .
When the transaction is complete , The data must be in a consistent state . Before the transaction starts , The data stored in the database is in a consistent state . In an ongoing transaction , The data may be in an inconsistent state . When the transaction completes successfully , The data must return to the known consistent state again .
Isolation, ： In a concurrent environment , When different transactions manipulate the same data at the same time , Each transaction has its own full data space .
All concurrent transactions that modify data are isolated from each other , Indicates that the transaction must be independent , It should not in any way depend on or affect other transactions . A transaction that modifies data can access that data before another transaction that uses the same data starts , Or access the data after another transaction using the same data ends .
persistence ： After the transaction is completed , Changes made by the firm to the database are persisted in the database , It will not be rolled back .
No matter whether the system fails or not , The result of the transaction is permanent . Once the transaction is committed , The effect of the transaction is permanently preserved in the database .
There are several kinds of interactions between transactions , Respectively ：
1、 Dirty reading ： One transaction reads uncommitted data from another transaction , And it's possible to roll back this data .
2、 It can't be read repeatedly ： Two identical queries in a transaction return different data . This is caused by the commit of other transaction modifications in the system during query .
3、 Fantasy reading ： A transaction modifies the data in a table , This modification involves all data rows in the table . meanwhile , Another transaction also modifies the data in this table , This modification is to insert a new row of data into the table . that , The user operating the previous transaction will find that there are no modified data rows in the table , It's like an illusion .
4、 Lost update ： Two transactions read the same record at the same time ,A Change the record first ,B Also modify the record （B I do not know! A A modified ）,B After submitting the data B The result of the modification covers A Modification results of .
mysql The default transaction level is repeatable read , and Oracle and SQL Server yes read committed .
show global variables like '%isolation%'; SELECT @@global.tx_isolation;
Both methods can view the global transaction isolation level
You can see mysql Default transaction level REPEATABLE-READ
show session variables like '%isolation%'; SELECT @@session.tx_isolation; SELECT @@tx_isolation;
SELECT @@global.tx_isolation; set global transaction isolation level read committed; SELECT @@global.tx_isolation;
set session transaction isolation level read committed;
BEGIN or START TRANSACTION： Open a transaction explicitly . COMMIT or COMMIT WORK： Commit transaction , And make all changes that have been made to the database permanent . ROLLBACK or ROLLBACK WORK： Rolling back ends the user's transaction , And undo all pending changes . SAVEPOINT S1： Use SAVEPOINT Allows you to create a rollback point in a transaction , There can be more than one... In a transaction SAVEPOINT;“S1” Represents the name of the rollback point . ROLLBACK TO [SAVEPOINT] S1： Roll back the transaction to the marked point .
create database SCHOOL; use SCHOOL; create table CLASS( id int(10) primary key not null, name varchar(40), money double ); insert into CLASS values(1,'A',1000); insert into CLASS values(2,'B',1000); select * from CLASS;
begin; update CLASS set money= money - 100 where name='A'; select * from CLASS; commit; quit mysql -u root -p use SCHOOL; select * from CLASS;
begin; update CLASS set money= money + 100 where name='A'; select * from CLASS; rollback; quit mysql -u root -p use SCHOOL; select * from CLASS;
begin; update CLASS set money= money + 100 where name='A'; select * from CLASS; SAVEPOINT S1; update CLASS set money= money + 100 where name='B'; select * from CLASS; SAVEPOINT S2; insert into CLASS values(3,'C',1000); select * from CLASS; ROLLBACK TO S1; select * from CLASS;
4. Use set Set control transaction
SET AUTOCOMMIT=0; # Disable auto submit SET AUTOCOMMIT=1; # Turn on auto submit ,Mysql The default is 1 SHOW VARIABLES LIKE 'AUTOCOMMIT'; # see Mysql Medium AUTOCOMMIT value
use SCHOOL; select * from CLASS; SET AUTOCOMMIT=0; SHOW VARIABLES LIKE 'AUTOCOMMIT'; update CLASS set money= money + 100 where name='B'; select * from CLASS; quit mysql -u root -p use SCHOOL; select * from CLASS;
MyISAM Unsupported transaction , Foreign key constraints are not supported either , Full text indexing only , Data files and index files are kept separately
Fast access , There is no requirement for transaction integrity
MyISAM Suitable for inquiry 、 Insertion based applications
MyISAM Store three files on disk , The file name and table name are the same , But the extensions are ：
.frm Definition of file storage table structure
The extension of the data file is .MYD (MYData)
The extension of the index file is .MYI (MYIndex)
Table level locking form , Lock the entire table when the data is updated
Databases block each other in the process of reading and writing :
It will block the reading of user data in the process of data writing
It will also block the user's data writing in the process of data reading
Data is written or read separately , The process is fast and takes up less resources
Business doesn't need the support of business
Read or write data unilaterally
MyISAM The storage engine reads and writes data frequently, which is not suitable for the scenario
Using read-write concurrency to access relatively low business
Business with relatively little data modification
Services that do not require very high consistency of data services
The server hardware resources are relatively poor
Support transactions , Support 4 Transaction isolation levels
MySQL from 5.5.5 Version start , The default storage engine is InnoDB
Read and write blocking is related to transaction isolation level
It can cache index and data very efficiently
Tables and primary keys are stored in clusters
Support partition 、 Table space , similar oracle database
Support for foreign key constraints ,5.5 Full text indexing is not supported before ,5.5 Full text index is supported after
The requirement for hardware resources is relatively high
Row level locking , But all Table scanning will still be table level locking , Such as
update table set a=1 where user like ‘%lic%’;
InnoDB The number of rows in the table is not saved , Such as select count() from table; when ,InnoDB You need to scan the entire table to calculate how many rows there are , however MyISAM Simply read out the number of saved lines . It should be noted that , When count() The statement contains where When the conditions MyISAM You also need to scan the entire table
For self growing fields ,InnoDB Must contain only the index of this field , But in MyISAM You can create a composite index with other fields in the table
When you empty the entire table ,InnoDB It's line by line deletion , Efficiency is very slow .MyISAM The table is rebuilt
show table status from Library name where name=' Table name '\G; example ：show table status from SCHOOL where name='CLASS'\G;
use Library name ; show create table Table name ; example ：use SCHOOL; show create table CLASS;
adopt alter table modify
use Library name ; alter table Table name engine=MyISAM; example ：use SCHOOL; alter table CLASS engine=myisam; show create table CLASS;
By modifying the /etc/my.cnf The configuration file , Specify the default storage engine and restart the service
quit vim /etc/my.cnf [mysqld] default-storage-engine=INNODB systemctl restart mysqld.service # Be careful ： This method only changes the configuration file and restarts mysql The newly created table is valid after service , The existing table will not be changed . systemctl restart mysqld.service Remember to restart after modification mysql service
notes ： This method only changes the configuration file and restarts mysql The newly created table is valid after service , The existing table will not be changed .
adopt create table Specify the storage engine when creating the table
use Library name ; create table Table name ( Field 1 data type ,...) engine=MyISAM; example ：mysql -u root -p use SCHOOL; create table hellolic (name varchar(10),age char(4))engine=myisam;
copyright：author[_ Rui_ ao]，Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707063416.html