The article directories

• MySQL based
• • Start and stop of services
  • Operations related to the database
  • Operations related to database tables
  • Operations related to data in a table
  • Sorting and paging
  • Grouping query
  • League table query
• MySQL Transaction Basics
• • Introduction of the transaction
  • The transaction operations
  • Transaction Characteristics (ACID)
  • Transaction security Risk
• MySQL > select * from database;
• • The index profile
  • The primary key index
  • The only index
  • Normal index
  • Composite index
  • The full text indexing

These days I am learning the basic knowledge of MySQL. Today I will sum up what I have learned for the convenience of review in the future.

MySQL based

Start and stop of services

net stop mysql
Copy the code

net start mysql
Copy the code

Operations related to the database

Connecting to a Database

mysql -uroot -P # Press Enter and enter your passwordCopy the code

To change the password, put your own password in the last single quotation mark

alter user 'root'@'localhost' identified by 'root';
Copy the code

Creating a database

createDatabase Specifies the database name.Copy the code

Create a database and check that no re-creation exists

create database if not existsDatabase name;Copy the code

Displays all databases, four of which are default

show databases;
Copy the code

Using a database

Use database name;Copy the code

Deleting a Database

dropDatabase Specifies the database name.Copy the code

Delete the database and check whether it exists

drop database if existsDatabase name;Copy the code

Operations related to database tables

create tableTable name (column name1The data type1And the column name2The data type2. Column name n Data type n,primaryKey column name (primary key);Copy the code

show tables;
Copy the code

descThe name of the table.Copy the code

drop tableThe name of the table.Copy the code

alter tableThe table name renametoThe new name of the table;Copy the code

alter tableThe name of the tableaddColumn name data type;Copy the code

alter tableThe name of the tabledropThe column name.Copy the code

Operations related to data in a table

Add data

insert intoTable name (column name1And the column name2. The column n)values(value1And the value2. The value of n); The column name and value must correspond.Copy the code

insert intoThe name of the tablevalues(value1And the value2. The value of n); The value must contain all columns in the table.Copy the code

Delete the data

delete fromThe name of the tablewhereConditions; Delete the data that meets the condition.Copy the code

delete fromThe name of the table. Select * from table where condition ();Copy the code

Modify the data

Update the table namesetThe column name1 = 值1And the column name2 = 值2.whereConditions; Modify the data that meets the condition.Copy the code

Update the table namesetThe column name1 = 值1And the column name2 = 值2. ; If no condition is set, modify all data.Copy the code

Query data

selectThe column namefromSelect * from table_name where table_name = 1;Copy the code

select * fromSelect * from table_name;Copy the code

select distinctThe column namefromSelect * from table_name where table_name = 1;Copy the code

Conditional statement (WHERE)

For the sake of demonstration, the results of the query are replaced with *.

Query the data that satisfies both conditions 1 and 2.

select * fromThe name of the tablewhereconditions1 andconditions2 
Copy the code

Query the data that meets condition 1 or condition 2.

select * fromThe name of the tablewhereconditions1 orconditions2 
Copy the code

Query the data that does not meet condition 1.

select * fromThe name of the tablewhere notconditions1 
Copy the code

Query data for an empty column.

select * fromThe name of the tablewhereThe column nameis null; 
Copy the code

Query non-empty data in a column.

select * fromThe name of the tablewhereThe column nameis not null; 
Copy the code

Query data in a column between values 1 and 2.

select * fromThe name of the tablewhereThe column namebetween 值1 and 值2; 
Copy the code

Query all data starting with hello, using like with %, where % represents 0 to any character.

select * fromThe name of the tablewhereThe column namelike 'hello%'; 
Copy the code

Query all data that starts with hello and is followed by only one character, like with _, _ for 1 character.

select * fromThe name of the tablewhereThe column namelike 'hello_'; 
Copy the code

Query the data in a column in a range.

select * fromThe name of the tablewhereThe column namein(value1And the value2,...). ;Copy the code

Sorting and paging

select * fromThe name of the tableorder byThe column name.Copy the code

select * fromThe name of the tableorder byThe column namedesc;
Copy the code

select * fromThe name of the table limitoffset,pagesize;
Copy the code

select* from user limit 0.10;
Copy the code

select* from user limit 10.10;
Copy the code

Grouping query

The keyword for grouped queries is group by, which can also be used with aggregate functions (count(), sum(), Max (), and so on), as illustrated by a user table.

selectThe field namefromThe name of the tablegroup byThe field nameCopy the code

selectThe field namecount(Field name)fromThe name of the tablegroup byThe field nameCopy the code

select user_type from user group by user_type;
Copy the code

select user_type,count(user_type) from user group by user_type;
Copy the code

League table query

MySQL Transaction Basics

Introduction of the transaction

The transaction operations

Open the transaction

start transaction;
Copy the code

Commit transactions, which write data to disk

commit
Copy the code

Roll back the transaction to the original state

rollback
Copy the code

Check whether automatic commit is enabled for the transaction

show variables like 'autocommit';
Copy the code

As you can see in MySQL transactions are committed automatically by default. To demonstrate transactions, first we need to turn off auto-commit.

Turn off automatic transaction commits

set autocommit=off;
Copy the code

The following illustrates the transaction with a bank transfer example, where account A transfers 100 yuan to account B



The following two commands must both succeed or fail at the same time, which is a transaction

update bank set money=700 where id=1;
update bank set money=600 where id=2;
Copy the code

So you need to start the transaction before you execute these two sentences

start transaction;
Copy the code

And then execute the transaction

update bank set money=700 where id=1;
update bank set money=600 where id=2;
Copy the code

Last commit transaction

commit;
Copy the code

If only the first operation succeeds, the data is stored in memory and not persisted to the database. If the second operation succeeds, the transaction can be committed to disk. If the second statement fails, the transaction can be persisted to disk. You can rollback to the original state with rollback, which requires transactions.

Transaction Characteristics (ACID)

Transaction security Risk

There is isolation between transactions, and there are four levels of isolation

• Read Uncommitted: Transaction B reads data that transaction A did not commit. This is also called read dirty data
• Read Committed, transaction B reads the data committed by transaction A (dirty reads are resolved, which is Oracle’s default isolation level). That is, transaction A commits data, and then transaction B can read it, committed once, but only once. This is also called unrepeatable reads
• Repeatable read (repeatable read), transaction A commits data, transaction B can read it repeatedly (solve dirty read and unrepeatable read, which is the default isolation level of MySQL), however, the result of two reads may be different, which causes: magic read
• Serialization (serializable)When transaction A processes data, transaction B can only queue (solve the dirty read, non-repeatable read and unreal read), that is, it is serialized, cannot be concurrent, low efficiency
  
  
  
  View the isolation boundaries for the transaction

sqlselect @@transaction_isolation; MySQL is repeatable by default.Copy the code

Set the isolation level to Read Uncommitted

set session transaction isolation level read uncommitted;
Copy the code

Set the quarantine sector to read committed

set session transaction isolation level read committed;
Copy the code

Set the isolation level to repeatable read

set session transaction isolation level repeatable read;
Copy the code

Sets the isolation boundary to serializable

set session transaction isolation level serializable;
Copy the code

MySQL > select * from database;

The index profile

Indexes are designed to improve database query efficiency. Indexes are suitable for fields with many queries and few modifications to improve query efficiency. If you have a lot of changes, using an index can be inefficient because it takes time to maintain the index table. Index is divided into primary key index, unique index, common index, composite index, full text index. The following illustrates the difference between using an index and not using one.

View the amount of data in the table

select count(*) fromThe name of the table.Copy the code

View the index in the table

show index fromThe name of the table.Copy the code

As you can see, there is only one primary key index (userId). The username field is not an index field.

Select * from user where username = 70000; select * from user where username = 70;



You can clearly see that indexing is fast.

Remove the index

dropThe index index nameonThe name of the table.Copy the code

By dropping the primary key index, the field is deleted

alter tableThe name of the tabledropPrimary key field name;Copy the code

The primary key index

create table test(
   id int(11),
   name varchar(25),
   primary key (id)
);
Copy the code

alter table test add constraint id primary key(id);
Copy the code

The only index

Unique index, the value in the index field must be unique, but null values are allowed.

Create a unique index when creating a table

create table test(   
	id int(11),   
	name varchar(25),   
	unique index_unique_test_name (name)
);
Copy the code

Create a unique index after the table is created

create unique index index_unique_test_name on test(name);
Copy the code

Change the table structure to a unique index

alter table test add unique index index_unique_test_name (name);
Copy the code

Normal index

create table test(   
	id int(11),   
	name varchar(25),   
	index index_test_name (name)
);
Copy the code

create index index_test_name on test(name);
Copy the code

alter table test add index index_test_name (name);
Copy the code

Composite index

Indicates an index created on multiple fields. The index is used only when the first field is used in the query condition. Follow the leftmost prefix set when using composite indexes.

create table test(   
	id int(11),   
	name varchar(25),   
	index index_test_id_name (id,name)
);
Copy the code

Create the composite index after the table is created

create index index_test_id_name on test(id,name);
Copy the code

Change the table structure to a normal index

alter table test add index index_test_id_name (id,name);
Copy the code

The full text indexing

create table test(   
	id int(11),   
	name varchar(25),   
	content text,
	fulltext (text)
);
Copy the code

create fulltext index index_content on test(content);
Copy the code

alter table test add fulltext index index_content (content);
Copy the code