Common simple mysql database operations

To start a database:

Mysql-uroot-proot (username, password)

Library (database) operations:

View all the databases.
show databases;
Creating a Database
Create database dbname;
Create the database and specify the encoding
Create database dbname Character Set Urf8;
Show variables like ' character% ';(view all encodings of the database)
Modify the encoding set character_set_client = GBK; (client-side encoding used)
Set character_set_results = GBK; (Result set encoding)
Delete Database
Drop database dbname;
Drop database if exists dbname;
Modify Database Encoding
Alter database dbname character set UTF8;
Working with databases
Use dbname;
Backing up a database
The first is to exit the database
Mysqldump-uroot-proot dbname > E:\dbname.sql
Backup dbname to DBNAME.SLQ in e-disk
Restore Database
Delete database drop db if exists dbname;
Creating database Create DB dbname;
Use the database using DBName;
Import data Source E:\mytest.sql

Table actions:

Changes to the table:

Add a column (image)
Alter table tablename add image blob;
modifying columns
Alter Table tablename Modify job varchar (20);
Modify Table Name
Rename table TableName to Newtablename;
Modifying the character set of a table
Alter table tablename character set UTF8;
modifying column names
Alter table tablename Change column name username varchar (20);
View all Tables
Show tables;
To view the creation statement for a table
Show CREATE TABLE tablename;
View the structure of a table
DESC TableName;
Delete Table
Drop table TableName;

Second, the statement: Delete and change the search
Create a table
Create Table TableName (
Id int,
Name varchar (20)
);

Insert statement
Insert into TableName (id,name) VALUES (1, ' Sensen ');

UPDATE statement
Update TableName Set salary = 1000 where name= ';
Update TableName Set salary = salary+1000 where name= ' l% ';
Update TableName Set salary=1000 where name= ' L_ ';

Delete statement
Delete from TableName where Name= ';
Delete from tablename (delete all records in a table)

Truncate tablename (delete table and create table)

SELECT statement

Select * FROM TableName;
SELECT * from student where;
Select distinct 中文版 from student (filter table for duplicate data.) )
Select Name,chinese c,english e from student (use aliases to represent student scores.) )
SELECT * FROM student where 中文版 between (Between...and ...)
SELECT * FROM student where the math in (89,90,30);(in..)

6. Fuzzy query
SELECT * FROM student where name like ' li% ';(query all surnamed Li's student scores. )
SELECT * FROM student where name like ' li _ ';(query all surnamed Li, whose name is two-word student scores. )

7.order BY clause
SELECT * FROM student order by math; (default is ascending ASC)
SELECT * FROM student order by math desc;

Count
Select COUNT (*) from student;
You can add a Where condition query later

Max min
Select Max (Chinese) from student;
Select min (Chinese) from student;

Sum
Select sum (math) from student;

Avg
Select AVG (Chinese) from student;

Group BY (collation)
SELECT * FROM orders group by product;

Primary Key settings:

Not empty
NOT NULL

Only
Unique

Primary KEY = NOT NULL + unique
Primary Key

Primary keys, self-growing
Primary Key Auto_increment

Multiple table Associations
One
More to one
Create a foreign key on more than one side
Many to many
Need to create an intermediate table description relationship
The middle table has two fields that are foreign keys referencing the primary key columns of two tables, and the two columns are union primary keys