Mysql _ MySQL

Mysql operation bitsCN.com

Mysql management
Create TABLE
1. create table [if not exists] database. tablename (custom content); for example:
Mysql> create table NAME1 (
Mysql> id int unsigned AUTO_INCREMENT not null primary key, # set the primary key and automatically add
Mysql> name VARCHAR (30) not null,
Mysql> age tinyint unsigned not null,
Mysql> Gender ENUM ('M', 'F') not null default 'M', # specify the range and DEFAULT value
Mysql> INDEX index_score (score), # create an INDEX
Mysql> UNIQUE (name), # create a UNIQUE key
Mysql> );
2. create table tab_name select col_name,... from tab_name2 where ---; copy specific content from tab_name2 to generate a new table
3. create table tab_name like tab_name2; create a table with empty content according to the table format of tab_name2

Query data
Query data in a table
SELECT
Select col_name from table_name;: filter column
WHERE condition: filter rows
<> = Like between... AND ..
<>! = Not equal
Where id in (1, 2) IN a specific set );
Is null, IS NOT NULL
REGEXP = RLIKE regular expression
+-*/% WHERE id + 1> 4
AND &, OR |, NOT!
LIKE wildcard
% Arbitrary
_ Any single
LIMIT limits the displayed rows after the select statement, as shown in figure
Select * from tab_name limit num; displays the first num row
Select * from tab_name limit num1, num2; display num2 rows backward based on num1
Data sorting
Order by clo_name
Select * from test order by col_name [desc | asc]; sort by data in col_name
DESC descending ASC ascending by default, do not write
Data Group
Group by cloumn GROUP display, having group combination, HVAING filter condition
SELECT column, COUNT (*) FROM table group by column HVAING> = 10;
Modify the displayed name
SELECT column AS name -----;
Modify table structure
Alter table table_name
1. add new columns
ADD col_name col_type AFTER col_name1; ADD
FIRST; add to FIRST
2. modify fields
CHANGE old_col_name new_col_name nre-col-definition;
MODIFY col_name col_defination; MODIFY the table definition
3. add an index
ADD {INDEX | KEY} [index_name] (col_name );
Delete field
DROP col_name
Delete key
DROP {INDEX | KEY} index_name
DROP primary key
Rename
Rename table table_name TO new_table_name
Delete table
Drop table [if exists] table_name
Insert data
Insert into table_name (col1, col2) VALUES ('val1', 'val2 ');
SET col_name = '----';
Call a function
Mysql> insert into pets (name, sex, birth) values ('dog', 'F', now ());
Insert multiple rows at a time
Insert into table_name (NAME) VALUES ('--'),('--'),('--');
REPLACE overwrites a primary key if it already exists.

Modify data UPDATE
UPDATA table_name SET col_name = '---' [WHERE condition] LIMIT 2;

Delete data
Delete from table_name [SHERE condition];
Clear a table id and start from new
Truncate table table_name

Author: "residual snow"

BitsCN.com