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

This article mainly introduces what the mysql operation commands are, which are introduced in great detail and have certain reference value. Friends who are interested must finish reading them.

/ / create a database create database school

/ / create a table

Create table info (id int not null primary key auto_increment,name char (10) not)

Null,score decimal (5Pol 2), hobby int (2))

# primary key primary key auto_increment self-incrementing

/ / View the table structure

Desc info

/ / Multi-table query (associated table)

Select * from info inner join hob where info.hobby=hob.id

Select info.name,score,hob.hobname from info inner join hob where

Info.hobby=hob.id

/ / Alias query

Select i.name,score,h.hobby from info as i inner join hob as h where i.hobby=h.id

/ / aggregate function

Statistics count (): can be changed to 1

Select count () from info

Average avg ()

Select avg (score) from info

/ / View the database

Show databases

/ / enter the database

Use myschool

/ / View the tables in myschool

Show tables

/ / View the data in info

Select from info

/ / insert data into info

Insert into info (id,name,score) values ('tianqi',55); # match before and after, if empty: null

/ / filter information

Mysql > select from table name where id=2 [and name=?] [or name=?]

/ / Update information

Update info set score=75 where id=6

/ / Delete information

Delete from info where name='test'; # entire line deletion

/ / Delete tables and databases

Drop table info; drop database school

/ / 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

Database indexes, transactions, views

Index: quick query data condition: the number of data is more than 2,000, which is equivalent to the catalogue page in front of a book.

Create index Index name on tablename column

Id name score address hobby

Create index id_index on info (id); create a normal index

Show index from info\ G; view the index fold display

Drop index id_index on info; delete index

Create unique index id_index on info (id); create a unique index

Alter table info add primary key (id); primary key index

Alter table info add column age int (3); add column

Alter table info drop column age; delete column

Create table infos (descript TEXT,FULLTEXT (descript)); full-text index, descript column name description

Create index multi_index on info (name,address); multi-page index, in which two conditions are combined.

Query

Transaction: a set of operations are executed together or not at all, and the results are consistent

Take a chestnut: bank transfer

Condition: the balance of transfer condition is greater than 0

Name balance

Zhang San 100

Li Si 200

Zhang San transferred 100 to Li Si

Begin start

Updata bank set money=money-100 where name='zhangsan'

Updata bank set money=money+100 where name='lisi'

Commit submission

Savepoint S1; set the rollback point

Rollback to savepoint S1; return to S1 rollback point

Set autocommit=0 forbids automatic submission

Set autocommit=1 enables auto-submission

Rollback rollback

Atomicity is indivisible

The results are consistent before and after.

Isolated transactions are isolated and do not affect each other

Once the persistence is successful, it cannot be changed

Virtual tables in the view database

Function: data in one or more tables provide access to different authorized users

Create view View name AS

Select statement

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

Create view score_view as select * from info where score > 80; form a view to view

Select * from score_view; View View

The above is all the contents of this article "what are the mysql operation commands?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.