MySQL Basic operation

Database operations

New data:
Create database name Charset=utf8;
To modify the database character set:
ALTER DATABASE name Charset=utf8;
Delete the database;
drop database name;
Querying the database currently in use;
Select Database ();
Query CREATE DATABASE SQL statement:
Select Create database name;

Data table Operations

New data table:
create table table name [chaeset=] field; #新建数据表必须有数据字段
Modify the table name;
ALTER TABLE name rename to new table name;
To delete a data table:
drop table name;
Delete from table name;
Querying data tables:
Show tables;
Query create data Table SQL statement:
Show create table table name;

Data table field Operations

To add a field to a table:
ALTER TABLE name add Field field type;
Specify where the field is inserted:
ALTER TABLE table name add field name segment type after a field;
Modify Field Name:
ALTER TABLE name change field name New name new field type;
To modify a field field type:
ALTER TABLE table name modify field name new field type;
To delete a field:
ALTER TABLE name drop field name;

Delete operation:
Physically delete a record:
Delete from table name;
Delete from table name [where condition];
To delete a record in a logical way; isdelete
Equivalent to adding a row of marker fields
ALTER TABLE name add isdelete bit default "0";
Update table Name Set Isdelete = value where condition;

Data manipulation

To add a data record:
Add all fields
Insert into table name values ();
Specify the field to add
Insert into table name (field name,) values (value,);
Specify fields to insert, non-empty fields must be filled in
Updating data in a data table
Updata table name Set field = Update value where record condition
Query all data records:
SELECT * from table name;
Querying the specified field data
Select field name from table name;
Query specified field data by criteria
Select field name from table name where condition;
Query field alias as (can be omitted)
Select field name as Alias [field name alias] from table name;
Fuzzy query like,% represents multiple characters, _ denotes one character
Select field name from table name where condition [like '%8_8 ']

Field type:

Constraints (primary key primary key, foreign key Foreigh key, uniqueness unique, NULL NULL, non-null NOT NULL)
Auto-tensioning auto_increment
Delete the associated data in multiple tables----set FOREIGN key to set null

conditional query data;
SELECT [* Field name] from table name WHERE [conditional statement]
Condition followed by the following logical operator or combination of judgment:
Less than:<
Greater than:>
Less than equals: <=
Greater than or equal to: >=
Not equal to:! = <>
And: And
Or: OR
exists in the collection: in ()
Not in the collection: Not In ()
Yes: is
Not: Isn't is
Between the two: Between...and ...

Query data sort order by;
SELECT [* field] from table name ORDER BY field ASC (Ascending) desc (descending)

Query data grouping group by;
SELECT [* field] from table name Group By field;
You can output a grouping by Group_concat ()

data query paging limit start position, Count number
SELECT * from table name limit start position, display count;

Page N; Imit (n-1) * Display count, Count

Aggregation functions:
Max ()
Minimum min ()
Sum sum ()
Averaging avg ()
Counts Count ()
Keep decimal round (values, decimal places), and you can nest the rest of the aggregate functions using

Not to be continued ....

MySQL Basic operation