DML: data operation (add insert, delete and modify)
DQL: Data Query (select)
DCL: data control (management database: user permissions,grand，revoke）
Database operation
Open MySQL
one
mysql -u root -p
show database
one
SHOW DATABASES;
Create Database
one
CREATE DATABASE database name
Use Database
one
Name of USE database;
'' can avoid keywords. To name the database with keywords, you can use ''
Backup database
Run in shell
The backup file is a series of sql statements entered by the user
one two three four
//Backup database Mysqldump - u user name - p - B database 1 database 2 database n>file.sql //Backup Table Mysqldump - u username - p database 1 table 1 table 2>file.sql
Restore Database
Statements do not use semicolons
one
Source Backup folder path
Delete Database
one
DROP DATABASE database name;
data type
type
int
char
date
Table Operation
establish
one two three four
CREATE TABLE name（ Field type, Field Type ）Character set character set collate proofreading rule engine engine
Character set and collate: if not specified, the character set of the database will prevail
see
View all tables in the database
one
SHOW TABLES;
View Table Structure (Columns)
one
DESC table name
View all data in the table
one
SELECT * FROM table name
Modify alter
Modify table name
one
Rename table name to new table name
Modify table name character set
one
Alter table table name character set character set
Add Column
one
Alter table table name add (field type, field type)
Modify Column Type
one
Alter table table name modify (field type, field type)
Modify Column Name
one
Alter table table name change column name new column name type
Filter select
View a column
one
SELECT column name FROM table name;
View qualified data in a column
one
SELECT column name FROM table name WHERE column operator value;
Data de duplication
one
Select distinct column name FROM table name;
Alias the column
one
Select column name as alias from table name
and or
in
In Collection
In (100200) in set {1,2}
between and
Between
between 80 and 90 Between 80-90, including two ends
like
Fuzzy query
"Han%" Query the students whose surname is Han%, indicating any character _ indicating any character
sort
The table is sorted by the value of a column
one two three four
//Ascending (ascending from top to bottom), the default is ascending SELECT * FROM table name OREDRS OREDR BY column name //Descending order SELECT * FROM table name OREDRS OREDR BY column name desc
Add insert
Insert a row of data
one
Insert into table name values (data 1, data 2, data n)
Insert data on some columns
one
Insert into table name (column 1, column 2) values (column 1 data, column 2 data)
Add multiple values to a column
one
Insert into table name (column name) values (data), (data), (data)
modify
Modify the value of the whole column
one
Update table name set column name=value
Modify the values of some columns that meet the conditions
one
Update table name set field 1=value, field 2=value where column operator value
delete
Delete all data
one
Delete from table name
Delete Row Data
one
Delete from table name where column operator value
Delete Column
one
Alter table table name drop column name
function
Count function - returns the total number of rows
one two three four
//1。Count (*) returns the number of qualified records Select count (*) from table name //2. count (column name) counts the number of elements in a column that meet the conditions, but will exclude NULL Select count (column name) from table name
Sum function – returns the sum of the rows of where condition, which only works on numeric columns
one
Select sum (numeric column) from table name where column operator value
Average avg – Average the column
one
Select avg (column name) from table name
Maximum max/min – column maximum
one
Select max (column name) from table name
Table Replication
one
Delete duplicate records
one two three
#Use distinct to filter data into temporary tables #Empty the table to delete duplicate records #Put the data of the temporary table into the operation table
Grouping function
grouping
one
Select column 1, column 2... from table group by column
Filtering after grouping (having)
one
Select column 1, column 2... from table group by column having column operator value
String function
Mathematical function
Time function
Get the current timestamp (MM/DD/YYYY, HH/MM/Sec)
one
now()
Get the current date
one
current_date()
Date addition and subtraction
one two three four
//Plus date_add(date,interval value year|month|day|hour|minute|second) //Less date_sub(date,interval value year|month|day|hour|minute|second)
Calculate day difference
one
dateiff(date1,Date2) -- date is the date
Unix timestamp – 1970-1-1 to present
one
unix_timestamp()
Convert the unix timestamp to the time in the specified format
one
Form_unixtime (unix_timestamp(), '% Y -% m -% d% H:% i:% s')//year month day hour: minute: second
Encryption and System
Query users using sql
one
User() -- returns the user name and IP address
Query the name of the database currently in use
one
datebase()
MD5 encrypts the string. The MD5 encrypted string is a 32-bit string, so the password type is commonly char (32)
one
MD5(str)
Cryptographic function
one
password(str)
Process control function
If the name is Jack, replace Jack with Jack
Judge whether it is NULL, use is instead of==
multi-table query
paging
one two
#Starting from the start+1 line, get the rows line select ... limit start,rows_value
Cartesian product
one
Select * from emp, det; each line of # emp is spliced with each line of det, and the result lines=number of emp lines * number of det lines
Self Connect
one two
#Use the same table as two tables, but alias the two tables select worker.name ,boss.ename from emp as worker,emp as boss
