Shulou(Shulou.com)06/01 Report--

After you are familiar with and install the MySQL database, the following describes the basic operation of the database, and all the following database statements are executed in the "mysql >" operating environment.

I. addition, deletion, modification and search of mysql

Increase:

/ / create a database

Create database school

/ / create a table

Create table info (id int not null auto_increment primary key,name e char (10) not null,score decimal (5Jing 2), hobby int (2))

Note: primary key primary key auto_increment self-incrementing

/ / the contents of the newly added table

Insert into info (id,name,score,hobby) values (# attention to match before and after)

/ / add columns

Alter table info add column age int (3)

Check:

/ / View the database

Mysql > show databases

/ / View the table structure

Mysql > desc info

/ / View the tables in the database

Mysql > show tables

/ / View the contents of the table

Mysql > select from info

/ / View specific entries in the table

Select from table name where id=2 [and name=?] [or name=?]

For the above entries, please refer to the creation section of the first part of mysql. I will not repeat it here, but we will focus on the multi-table query.

/ / query associated tables with multiple tables (the primary key of the schedule is the accessory key of the primary 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 screenshot is two related tables, and the second screenshot is a query demonstration.

Change:

/ / change the data in the table

Update info set score=75 where id=6

Delete:

/ / delete the whole line

Delete from info where name='test'

/ / Delete columns

Alter table info drop column age

/ / Delete the table

Drop table info

/ / Database

Drop database school

The following is a simple demonstration of deleting rows and columns, and the rest are the same.

2. Mysql--- sorting and aggregate function

Sort:

Select from info where 1 to 1 order by score; asc-- ascending, but not writing # default ascending

Select from info where 1 to 1 order by score desc; desc-- descending order

Function:

Statistics count (); can be changed to 1

Select count (*) from info

Average avg ()

Select avg (score) from info

III. Mysql--- Index

Brief introduction

Indexes are special files (indexes on InnoDB data tables are part of the table space) that contain reference pointers to all records in the data table. More generally, the database index is like the directory in front of a book, which can speed up the query speed of the database.

Types

General index: the most basic index, without any restrictions.

Unique index: similar to a normal index, except that the value of the index column must be unique, but null values are allowed.

Primary key index: it is a special unique index and no null values are allowed.

Full-text index: can only be used for MyISAM tables, for larger data, the generation of full-text index is time-consuming and space-consuming.

Combinatorial index: in order to improve the efficiency of mysql, we can establish combinatorial index and follow the principle of "leftmost prefix". When creating a composite index, the columns that are most commonly used (frequency) as constraints should be placed on the far left, decreasing in turn.

/ / create a general 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)

/ / 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 the index: the above process has been shown

Show index from info

Show index from info\ G; portrait display

Unique 1 is not the only one

Unique 0 unique

/ / Delete index (primary key, full-text index delete command is special)

Drop index id_index on info; # normal / unique index

Alter table info drop primary key; # Primary key Index

Drop table infos; # full text Index

IV. Mysql--- transaction

Brief introduction

A transaction is a collection of SQL statements that cannot be split and executed, and can be undone if necessary. Bank transfer is a classic example of explaining affairs. The main steps of transferring 500 yuan from user A to user B can be summarized as the following two steps.

First, account A minus 500 yuan

Second, the B account will be increased by 500 yuan

These two steps are either successful or unsuccessful, or both will lead to data inconsistencies. This can be guaranteed by transactions, and distributed transactions are needed if transfers are made between different banks.

Nature

The mechanism of transactions is usually summarized as the "ACID" principle, namely atomicity (A), stability (C), isolation (I), and persistence (D).

Atomicity: all operations that make up a transaction must be a logical unit that either executes all or none at all.

Stability: the database must be stable before and after the transaction is executed.

Isolation: transactions do not interact with each other.

Persistence: all transactions must be written to disk after successful execution.

Transaction processing method

1. Implement it with BEGIN, ROLLBACK and COMMIT.

BEGIN starts a transaction

ROLLBACK transaction rollback

COMMIT transaction confirmation

2. Directly use SET to change the automatic submission mode of MySQL:

SET AUTOCOMMIT=0 forbids automatic submission

SET AUTOCOMMIT=1 enables auto-submission

Example demonstration

The first diagram is the original table before the transaction is set up for comparison

The following figure shows the specific operation process.

5. Mysql--- view

Brief introduction

A view is a virtual table, a logical table that itself does not contain data. Stored in the data dictionary as a select statement.

Through the view, you can display part of the data of the base table; the view data is used to customize the table used in the query of the view, which is generated dynamically using the view.

Action

The data in one or more tables provides access to different authorized users, which is secure and effective.

/ / create a view

Create view View name AS

/ / select statement

Select from info where score > 80; check people with scores greater than 80

/ / form a view to view

Create view score_view as select from info where score > 80

/ / View the view

Select * from score_view

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.