MySQL Basic Operations Command Daquan (pure dry)

After installing MySQL we need to be familiar with the basic operation of MySQL, this article mainly introduces the database and table additions and deletions, as well as indexes, transactions, views.
First, MySQL database and table creation and additions and deletions to change

To create and view a database:
Create Database School; Create a database School
show databases; View Database

Creating tables in the database
Use school; Using the School database
CREATE table list (ID int (3) primary key Auto_increment,name char (5) Not Null,score decimal (5,2)); Create a list database
DESC list; View the list table structure

Inserting data into the table
INSERT into List (Name,score) values (' Zhang San ', 90); Insert Zhang San, score 90;
(Note: Auto_increment is a self-increment column, no additional ID columns are added when inserting)
SELECT * from list; View all information in the list table

Conditional filter Query
Select from list where id = 1;//filter in List table id=1 this row of data
Select from list where score >= 80; Filter the SCORE>=80 data in a list table

Condition filter Query and form a table
CREATE TABLE score Select from list where score >=80;
Select from Score; View information in the new table score

Modify the information in a table
INSERT into List (Name,score) VALUES (' Zhao Liu ', null); Add Zhao Liu, result is null
Update list Set score = where name = ' Zhao Liu ';//modify score to 95

Filter Data Descending ascending view
Select from list where 1=1 order by score;//view by score ascending order
Select from list where 1=1 order by score Desc; View by score Descending

View the number of rows in a table, and averaging
Select COUNT (*) from list; See a few rows of data in the list table
Select AVG (score) from list; About score columns averaging

Adding columns
ALTER TABLE list add Column hobby char (5); Add Hobby Column
DESC list; View the list table structure

Delete rows, tables, and databases
Delete from list where name = ' Harry '; Delete a row with name Harry
drop table list; Delete List table
Drop Database School; Delete School Database

Ii. creation and deletion of indexes

索引的作用相当于一本书的目录，当数据信息大于2000条时，索引可以使我们搜索数据的速度大大提升，下面我们来学习如何创建以及删除索引。`

Create a normal index
CREATE index Name_index on list (name);
Show index from list;

Create a unique index
Create unique index index ID_INDEX on list (ID);

Create a primary key index
1, create the table when the specified column is created as primary key (previously created table when the ID is specified as the primary key index, not repeat)
2. Create the table after creating the specified column as primary key
ALTER TABLE list add primary key (ID); Modify ID column Primary key index

To create a full-text index
CREATE table lists (Descript text,fulltext (descript));

Create a multi-page index
CREATE index Mutil_index on list (id,name); Create a multi-page index with Id,name as a field

Delete Index
1. Delete the index created by the method at the beginning of the Create
Drop index id_index on list;
2. Delete the index created by alter at the beginning of the method
ALTER TABLE list drop PRIMARY key;

Third, view
The role of the view can be an analogy, when the company query performance, the employee can be based on the permissions to view his view of the corresponding performance, and can not query other people's performance view. A view is not a table in nature, but a part of a map table.

Create a View
Create VIEW score as SELECT * from list where score >=80;

Delete a view

Iv. Business

事务：一组操作共同执行或者都不执行，结果保持一致事务具有一下4点性质：1、原子性  不可分割2、一致性  前后结果保持一致3、隔离性  事务之间隔离，互不影响4、持久性   一旦执行成功，不可更改

Begin Start a transaction
Update list set score =80 where name= ' John Doe '; Update John Doe score is 80
SavePoint S1; Set the rollback point S1
Update list set score =70 where name= ' Harry '; Update Harry score is 70
SavePoint S2; Set the rollback point S2
Rollback to savepoint S1; Roll back to S1
Commit Submit

MySQL Basic Operations Command Daquan (pure dry)