MySQL Basic Operations Command

Database additions and deletions change

Create a database
Create Database School;
Create a table
CREATE table info (ID int not NULL PRIMARY key Auto_increment,name char (TEN) not

Null,score Decimal (5,2), hobby int (2));
#primary Key PRIMARY key auto_increment self-increment column
View table Structure
DESC info;
Multi-table Query (association 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;

Aggregation functions
Statistics count (): can be changed to 1
Select COUNT () from info;
Average avg ()
Select AVG (score) from info;
View Database
show databases;
Enter the database
Use MySchool;
To view the tables in MySchool
Show tables;
View the data in info
Select from info;
Inserting data in Info
Insert into info (id,name,score) VALUES (' Tianqi ', "), #前后匹配, if blank: 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 '; #整行删除
Delete a table, database
drop table info; Drop Database School;
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

Database indexes, transactions, views

索引：快速查询数据  条件：数据数目大于两千条 相当于一本书前的目录页

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 Index Wrapping 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); Adding columns

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, speaking two conditions together

Inquire

事务：一组操作共同执行或者都不执行，结果保持一致

Give me a chestnut: Bank transfer
Condition: Transfer condition balance greater than 0
Name Balance
Zhang 3,100
Lee 4,200

Zhang San transfer to John Doe

Begin

Updata Bank set money=money-100 where name= ' Zhangsan '

Updata Bank set money=money+100 where name= ' Lisi '

Commit Commit

SavePoint S1; Set the rollback point

Rollback to savepoint S1; Back to S1 back to the rolling point

Set autocommit=0 prohibit auto-commit
Set autocommit=1 turn on auto-commit
Rollback rollback

Atomic indivisibility

Consistent results before and after consistency

Isolation between isolated transactions, non-impact

Persistence cannot be changed once the execution is successful

视图      数据库中的虚拟表

Role: Data from a table or multiple tables provides access to different privileged users

CREATE VIEW name as

SELECT statement

SELECT * from info where score > 80; View people greater than 80 points

CREATE VIEW Score_view as SELECT * from info where score >80; Form a view to view

SELECT * from Score_view; View View

MySQL Basic Operations Command