MySQL Beginner Learning

First, the operation of the database
1. Create a database
CREATE DATABASE [IF not EXISTS] db_name [create_specification [, Create_specification] ...]
Create_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name

~ Create a database called MYDB1.
CREATE DATABASE mydb1;
~ Create a MYDB2 database that uses the UTF8 character set.
Create database mydb2 character set UTF8;
~ Create a MYDB3 database that uses the UTF8 character set and with the proofing rules.
Create database mydb3 character set UTF8 collate utf8_bin;
2. View the database
Show Database statements: View all databases in the current database server
SHOW DATABASES
To display the database creation statement:
SHOW CREATE DATABASE db_name
~ View all databases in the current database server show databases;
~ View the definition information for the MYDB2 database created earlier show create Database mydb2;
3. Deleting a database
DROP DATABASE [IF EXISTS] Db_name

~ Delete the MYDB1 database that was created earlier, drop DB mydb1;
Drop database mydb1;

4. Modify the Database
ALTER DATABASE [IF not EXISTS] db_name [alter_specification [, Alter_specification] ...]
Alter_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name

View the database in the server and modify the character set of one of the libraries to GBK;
5. Switching the database
Select database: Use Db_name;
View selected databases: Select database ();

Second, the Operation table
1. Create a table
CREATE TABLE table_name
(
Field1 datatype,
Field2 datatype,
FIELD3 datatype,
) Character set character Set collate proofing rules

~ Create an Employee Information table
CREATE TABLE Employee (
ID int primary KEY auto_increment,
Name varchar (unique),
Gender bit NOT NULL,
Birthday date,
Entry_date date,
Job varchar (255),
Salary Double,
Resume text
);

2. View the table
View table structure: desc tab_name;
View Create statement for table: Show create TABLE tab_name;
View all tables in the current database: show tables;

3. Modify the table
ALTER Table Table Add/modify/drop/character set/change (column datatype [DEFAULT expr] [, column datatype] ...);
Modify table name: Rename table name to the new name;

~ An image column is basically added to the employee table above.
ALTER TABLE employee add image blob;
~ Modify the job column so that it is 60 in length.
ALTER TABLE employee Modify job varchar (60);
~ Delete the Gender column.
ALTER TABLE employee drop gender;
~ Table name changed to user.
Rename table employee to user;
~ The character set of the modified table is GBK
ALTER TABLE user character set GBK;
~ Column Name name modified to username
ALTER TABLE user change name username varchar (30);
4. Delete a table
drop table tabname;
~ Delete User Table
drop table user;

Third, the Operation table record

MySQL garbled:
The encoding that the client uses when sending the data and the client that the server thinks is different, the encoding and the encoding used when the message is sent are not garbled.
Solution: The client can use set names GBK at the same time, the command notifies the server what encoding is used when the current client sends data, so that the server interacts with the current client using the specified encoding to resolve the garbled problem.
We can also modify the My.ini file under the MySQL installation directory, and by modifying the Default-character-set value, you can set the client encoding that the server defaults to.

1. Adding records
INSERT into table [(column [, Column ...])] VALUES (value [, value ...]);
~ Insert three records into the employee information sheet
INSERT into employee (Id,name,gender,birthday,entry_date,job,salary,resume) VALUES (null, ' Zhang Fei ', 1, ' 1990-09-09 ', ' 1990-10-01 ', ' thugs ', 998.00, ' really good to play ');

INSERT into employee values (null, ' Guan Yu 1 ', 0, ' 1980-08-08 ', ' 1980-10-10 ', ' Big Broadsword ', 9000.00, ' CEO's pro-brother, although only playing knives, but earning a lot of ');

INSERT into employee values (null, ' Liu Bei ', 1, ' 1970-08-08 ', ' 1970-10-10 ', ' CEO of the company ', 900000.00, ' company boss ' basic very busy '), (null, ' Zhao Yun ', 1, ' 1999-08-08 ', ' 1999-10-10 ', ' Security chiefs ', 90.00, ' Mister Bodyguard ');

2. Modify the Record
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]

~ Change the salary of all employees to 5000 yuan.
Update employee set salary=5000;
~ The salary of the employee whose name is ' Zhang Fei ' is revised to 3000 yuan.
Update employee set salary=3000 where Name= ' Zhang Fei ';
~ The salary of the employee whose name is ' Guan Yu ' has been changed to 4000 yuan and the job changed to CCC.
Update employee set salary=4000,job= ' CCC ' where name= ' Guan Yu ';
~ Liu Bei's salary will be increased by 1000 yuan on the original basis.
Update employee set salary=salary+1000 where Name= ' Liu Bei ';

3. Deleting records
Delete from Tbl_name [WHERE where_definition]

~ Delete the record in the table named ' Zhang Fei '.
Delete from employee where Name= ' Zhang Fei ';
~ Delete all records in the table.
Delete from employee;
~ Use truncate to delete records in the table.
Truncate employee;

4. Query records
To create a table:
CREATE TABLE Exam (
ID int primary KEY auto_increment,
Name varchar () is not NULL,
Chinese double,
Math Double,
中文版 Double
);

INSERT INTO exam values (null, ' Guan Yu ', 85,76,70);
INSERT INTO exam values (null, ' Zhang Fei ', 70,75,70);
INSERT INTO exam values (null, ' Zhao Yun ', 90,65,95);

(1) SELECT [DISTINCT] *| {column1, Column2. Column3 ...} from table;

~ Query The information of all students in the table.
SELECT * from exam;
~ The names of all students in the enquiry form and the corresponding English scores.
Select Name,english from exam;
~ Filter duplicate data in the table.
SELECT distinct 中文版 from exam;
-Add 10 points to all student scores.
Select name,english+10,chinese+10,math+10 from exam;
~ Count Each student's total score.
Select name, english+chinese+math from exam;
~ Use aliases to indicate student totals.
Select name as name, English+chinese+math as total from exam;
Select name Name, English+chinese+math total from exam;
Select name 中文版 from exam;

(2) Filtering queries using the WHERE clause
~ Query the student's grade named Zhang Fei
SELECT * FROM exam where name= ' Zhang Fei ';
~ Query students with English scores greater than 90 points
Select Name,english from exam where 中文版 > 90;
~ Search all students with a total score greater than 230
Select name Name, Math+english+chinese total from exam where math+english+chinese>230;
~ Check the English score between 80-100 students.
Select Name,english from exam where 中文版 between and 100;
~ Query Math scores for 75,76,77 's classmates.
Select Name,math from exam where math in (75,76,77);
~ Check all the student's grades with Zhang's surname.
SELECT * from exam where name is like ' Zhang% ';
SELECT * from exam where name is like ' Zhang __ ';
~ Query Math >70, Chinese >80 students.
SELECT * from exam where math>70 and Chinese > 80;
(3) Sort query
SELECT Column1, Column2. Column3.
Fromtable
Order BY Column Asc|desc

~ Output after sorting the English scores.
Select Name,english from Exam order by 中文版;
~ Sorting by total score output from highest to lowest order
Select name Name, Math+english+chinese total score from exam order by total: desc;
~ Sort the output of the student's grade of Zhang surname
Select name Name, Math+english+chinese total score from exam where name like ' sheet% ' order by total score Desc;descend
(4) Aggregation function
~1.count () Returns a column, the total number of rows
~ How many students are there in a class?
Select COUNT (*) from exam;
~ How many students with a statistical math score greater than 70?
Select COUNT (*) from exam where math > 70;
~ What is the number of people with total statistics greater than 250?
Select COUNT (*) from exam where math+english+chinese>230;
The ~2.sum function returns the rows that satisfy the Where condition and
~ Statistic A class math total?
Select sum (math) from exam;
~ Statistics of a class of Chinese, English, mathematics, the total scores of each department
Select SUM (Chinese), sum (中文版), sum (math) from exam;
~ Statistics of a class of Chinese, English, math scores sum
Select SUM (ifnull (chinese,0) +ifnull (english,0) +ifnull (math,0)) from exam;
~ Statistic The average score of a class's Chinese score
Select SUM (Chinese)/count (*) from exam;
~3.avg the average of a column that matches a condition
~ Ask for a class math average score?
Select AVG (math) from exam;
~ to ask for a class score average?
Select AVG (ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) from exam;
The ~4.max/min function returns the maximum/minimum value of a column that satisfies the Where condition
~ Ask for class highest score and lowest score
Select Max (Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) from exam;
Select min (ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) from exam;

(5) Group query

To create a table:
CREATE TABLE Orders (
ID int,
Product varchar (20),
Price float
);

INSERT into orders (Id,product,price) VALUES (1, ' TV ', 900);
INSERT into orders (Id,product,price) VALUES (2, ' washing machine ', 100);
INSERT into orders (Id,product,price) VALUES (3, ' detergent ', 90);
INSERT into orders (Id,product,price) VALUES (4, ' oranges ', 9);
INSERT into orders (Id,product,price) VALUES (5, ' detergent ', 90);

SELECT Column1, Column2. Column3. Fromtable GROUP BY column have ...

~ Display the total price of each category of goods after sorting the items in the order form
Select Product,sum (Price) from the orders group by product;
~ Inquire about the purchase of several kinds of goods, and each kind of total price more than 100 of goods
Select Product,sum (Price) from the orders group by product have sum (price) >100;

The difference between where and having:
Where is used to filter before grouping, having to filter after grouping
Aggregate functions can be used in the HAVING clause, which is not available in the WHERE clause
In many cases having an alternative to where

-Check the name of a product with a unit price less than 100 but a total price greater than 150
Select Product,sum (Price) from the orders where price<100 group by product have sum (price) >150;

!!!!! The order in which SQL statements are written:
Select from where GroupBy have an
!!!!! Order of execution of SQL statements:
From where select group by have order by
Iv. Backup/Restore Database
Backup:
In the cmd window, mysqldump-u root-p db_name>c:/1.sql
Recovery:
Create a database
Back to the cmd window, mysql-u root-p db_name<c:/1.sql

Or

Create a database
Under MySQL command line, enter the database, source C:/1.sql

Five, multi-table design
FOREIGN KEY constraints:
CREATE TABLE Dept (
ID int primary KEY auto_increment,
Name varchar (20)
);
INSERT INTO dept values (NULL, ' Finance Department '), (null, ' HR '), (null, ' Sales department '), (null, ' administration Department ');

CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name varchar (20),
dept_id int,
Foreign KEY (dept_id) references dept (ID)
);
INSERT into EMP values (NULL, ' Obama ', 1), (null, ' Harry Potter ', 2), (null, ' Bin Laden ', 3), (null, ' Park ', 3);

1-to-many: design a foreign key on a multi-party to save one's primary key
1 to 1: Design the foreign key on either side to save the other party's primary key
Many-to-many: design a third-party relationship table to hold the correspondence between two tables ' primary keys

Six, multi-table query
CREATE TABLE Dept (
ID int primary KEY auto_increment,
Name varchar (20)
);
INSERT INTO dept values (NULL, ' Finance Department '), (null, ' HR '), (null, ' Sales department '), (null, ' administration Department ');

CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name varchar (20),
dept_id int
);
INSERT into EMP values (NULL, ' Obama ', 1), (null, ' Harry Potter ', 2), (null, ' Bin Laden ', 3), (null, ' Park ', 3);

Cartesian product query:
The result of multiplying the records in two tables is that if there are N records in the left table and M records in the right table, the N*m records will be investigated. Cartesian product queries often contain a lot of wrong data, so we don't usually use it.
SELECT * from Dept,emp;

Internal connection query: Only the left and right tables can find the corresponding relationship records
SELECT * from dept,emp where emp.dept_id = dept.id;
SELECT * FROM dept INNER join EMP on emp.dept_id =dept.id;
Outer JOIN query:
Left OUTER JOIN query: Add the left table on the inner join and the right table cannot find the record of the corresponding record
SELECT * FROM dept LEFT join EMP on emp.dept_id=dept.id;
Right outer join query: Add a record on the right side of the table without the left table, based on the internal connection
SELECT * FROM dept right join EMP on emp.dept_id = dept.id;
Full outer JOIN query: Add the upper left table on the basis of the inner join and the right table does not have the record and the right table has the record which the left table does not have.
SELECT * FROM dept full join EMP on emp.dept_id=dept.id; --MySQL does not support full external connections

SELECT * FROM dept LEFT join EMP on emp.dept_id=dept.id
Union
SELECT * FROM dept right join EMP on emp.dept_id=dept.id;

MySQL Beginner Learning