Introduction

It's time to review the grammar of a wave of SQL statements. It doesn't need to be too deep, but it has to be used.

grammar

Step by step, from shallow to deep, all of them are used here. MySQL Yes.

Basics

Connect to the database

mysql -h 10.20.66.32 -u root -p 123456

• 1

• 1

- After h is the address of MySQL server, - u is the user name, and - p is the password.

view the database

show databases;

• 1

• 1

Using databases

use test;

• 1

• 1

View Table

show tables;

• 1

• 1

View table structure

desc winton

• 1

• 1

Building tables

create table t1(
    id int not null primary key, 
    name char(20) not null
    );

• 1
• 2
• 3
• 4

• 1
• 2
• 3
• 4

Syntax create table table table name (field name segment type field descriptor, field name segment type field descriptor);

Delete tables

drop table test;

• 1

• 1

Grammar: drop table table table table name;

Amendment table

Adding fields

alter table t1 add(score int not null);

• 1

• 1

Syntax: alter table indicates add (field name type descriptor);

Remove fields

alter table t1 drop column score;

• 1

• 1

Syntax: alter table table table name drop colun field name, drop colun field name;

Change field

alter table t1 change name score int not null;

• 1

• 1

Syntax: alter table table table name change old field name new field name new field descriptor

insert

Full field insertion

insert into winton values(001,'zww'),(002,'rs');

• 1

• 1

Syntax: insert into table name values (field 1 value, field 2 value,...) ) (Field 1 value, field 2 value,...) );

Individual field insertion

insert into winton(id) values(004);

• 1

• 1

 
Look at the results illustrated below, as shown in the figure above.  
Syntax: insert inton table name (field name) values (value 1), (value 2);

General Query

Full field query of single form

select * from t1;

• 1

• 1

Syntax: select * from table name;

Individual field query of form

select id from t1;

• 1

• 1

Syntax: select field 1, field 2 from table name;

multi-table query

select t1.id,t1.score,winton.name from t1,winton;

• 1

• 1

 
Grammar: select table one field, table two field, table three field,... from Table 1, Table 2, Table 3,... ;

Conditional Query

Single Form Conditional Query

select * from t1 where socre>90;

• 1

• 1

Syntax: select field 1, field 2 from table name where condition;

Multi-table Conditional Query

select t1.id,t1.score,winton.name from t1,winton where t1.id=winton.id;

• 1

• 1

 
Syntax: select Table 1 field, Table 2 field from table 1, Table 2 where condition;

nested queries

select name from winton where id=(select id from t1 where score=90);

• 1

• 1

 
Syntax: select field 1, field 2... from table name where condition (query);

And inquiry

(select id from t1 )union(select id from winton);

• 1

• 1

Submit queries

select id from t1 where id in (select id from winton);

• 1

• 1

delete

delete from winton where id=4;

• 1

• 1

Grammar: delete from table name where condition;

To update

update t1 set score=69 where id=2;

• 1

• 1

Syntax: The field name changed by the update table name set = the value where condition;

Common Functions

Summation

select sum(score) from t1;

• 1

• 1

Note: sum (field) is invalid for strings and time

Average Value

select avg(score) from t1; 

• 1

• 1

Note: AVG (field) is invalid for strings and time

count

select count(*) from t1;

• 1

• 1

Note: count (field name) does not contain NULL;

Maximum

select max(name) from winton;

• 1

• 1

Note: max(colunm) returns the largest alphabetical order and the largest numerical value

Find the Minimum

select min(name) from winton;

• 1

• 1

Note: min(colunm) returns the lowest alphabetical order and the lowest numerical value

Common modifiers

distinct field with unique median

select distinct name from winton;

• 1

• 1

Limit query result limit

select * from winton limit 2;

• 1

• 1

order by sort

select * from winton order by name;

• 1

• 1

Note: The default is ascending order.

Descending desc ending order

slelect * from winton order by name desc;

• 1

• 1

Ascending order

select * from winton order by name asc;

• 1

• 1

group by grouping

select name from winton group by name;

• 1

• 1

Indexes

Create a common index

create index wintonIndex on winton (name);

• 1

• 1

Syntax: create index index index name on table name (field 1, field 2,...) );

Create a unique index

create unique index wintonIndex on winton (id);

• 1

• 1

Syntax: create unique index index index name on table name (field 1, field 2,...) );
ps:unique index requires that the data in the column be unique and not duplicated.

Remove index

drop index wintonIndex on winton;

• 1

• 1

Syntax: drop index index index name on table name;

Ending

Well, it's worth a lot to think about. It's the most basic and commonly used.

Origin: http://blog.csdn.net/wenwen091100304/article/details/49368019