MySQL Database basic operations Daquan

After the MySQL database is familiar and installed, the basic operations of the database are described below, and all of the following database statements are executed in the "mysql>" operating environment.

First, MySQL---additions and deletions to check
Increase:

Create a database
Create Database School;
Create a table
CREATE table info (id int not NULL auto_increment primary key,name e char (Ten) not Null,score decimal (5,2), hobby int (2));
Note: Primary key primary key auto_increment self-increment column

Add Content in table
Insert into info (id,name,score,hobby) VALUES (1, ' Zhangsan ', 89, 1); #注意前后匹配
Add columns
ALTER TABLE info add column age int (3);

Check:

View Database
mysql> show databases;
View table Structure
Mysql> desc Info;
View tables in the database
Mysql> Show tables;
View the contents of a table
Mysql> Select from info;
View specific entries in a table
Select from table name where Id=2[and name=? ] [or name=?];

Please refer to the first section of MySQL to create a section of the operation, not to repeat here, we focus on multi-table query.

Multi-Table Query association table (the primary key of the schedule is the key of the main table)
SELECT * FROM info inner join hobby where info.id=hobby.id;
Select Info.name,score,hobby.hobname from info inner join hobby where info.id=hobo.id=hobby;
Select I.name,score,h.hobname from info as I inner join hobby as H where i.id=h.id;

The first one is two related tables and the second one is the query demo

Change:

Change the data in a table
Update info set score=75 where id=6;

By deleting:

Delete entire row
Delete from info where name= ' test ';
Delete Column
ALTER TABLE info drop column age;
Delete a table
drop table info;
Database
Drop Database School;

The following is a simple demonstration of deleting rows, columns, and other

Second, MySQL---sorting, aggregation functions
Sort:

Select from info where 1=1 order by score; asc--Ascending, can not write #默认升序
Select from info where 1=1 order by score Desc; desc--Descending

Function:

Statistics count (); Can be changed to 1
Select COUNT (*) from info;
Average avg ()
Select AVG (score) from info;

Third, MySQL---index
Brief introduction
An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query.
Type
Normal index: The most basic index, without any restrictions
Unique index: Similar to "normal index", the difference is that the value of the indexed column must be unique, but a null value is allowed.
Primary KEY index: It is a special unique index and is not allowed to have null values.
Full-Text indexing: Available only for MyISAM tables, generating full-text indexes is a time-consuming space for larger data.
Combined index: For more MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle. When you create a composite index, you should place the most commonly used (frequency) constraint on the leftmost column, descending in turn.

Create a normal index
CREATE index Id_index on info (ID);
Create a unique index
Create unique index Id_index on info (ID);

Create a primary key index
ALTER TABLE info add primary key (ID);

To create a full-text index
CREATE TABLE Infos (descript text,fulltext (descript));

Create a multi-page index
CREATE index Multi_index on info (name,address);

//View index: The above procedure already shows the
Show index from info;
Show index from info \g; portrait

• Unique 1 is not unique
  unique 0 unique
  //delete index (primary key, full-text Index Delete command is special)
  Drop index id_index on info; #普通/UNIQUE index
  Alter tab Le info drop primary key; #主键索引
  drop table infos; #全文索引

Four, MySQL---transaction
Introduction
A transaction is a set of SQL statements that cannot be executed, and can be undone if necessary. Bank Transfer is a classic example of interpreting business. User A to User B transfer 500 Yuan The main steps can be summarized in the following two steps.
First, the account a account minus 500 yuan;
Second, the account B account increased by 500 yuan;
These two steps are either successful or not successful, otherwise they can result in inconsistent data. This makes it possible to use transactions to ensure that a distributed transaction is required for transfers between different banks. The mechanism of
nature
Transactions is generally summarized as the "ACID" principle, i.e. atomicity (A), stability (C), Isolation (I), and Persistence (D).
Atomicity: All operations that make up a transaction must be a single logical unit, either all executed or not executed at all.
Stability: The database must be stable before and after the transaction is executed.
Isolation: Transactions do not affect each other.
Persistence: The transaction must be fully written to disk after execution succeeds.
Transaction Method
1, implement
begin with begin, ROLLBACK, Commit, start a transaction
ROLLBACK transaction rollback
COMMIT transaction Acknowledgement
2, Use set directly to change the MySQL autocommit mode:
Set autocommit=0 prohibit autocommit
Set autocommit=1 open autocommit
instance demo
The first graph is the original table before the transaction is set to compare

is a specific procedure

v. MySQL---view
introduction
View (view) is a virtual existence of a table, is a logical table, Does not contain data in itself. Saved as a SELECT statement in the data dictionary.
Through the view, you can represent part of the data in the base table, the view data from the tables that are used in the query that defines the view, and dynamically generated using the view.
function
A table or data from multiple tables provides access to different privileged users and is safe and effective.

Create a View
CREATE VIEW name as
SELECT statement
Select from info where score > 80; View people greater than 80 points
Form a view to view
CREATE View Score_view as select from info where score >80;
View View
SELECT * from Score_view;

MySQL Database basic operations Daquan