MySQL using notes

Database
I. Database Overview (! Master)
1. Database: Warehouse where data is stored (understanding)
Hierarchical database, network-based database, relational database

Installation and configuration of 2.MYSQL database
The installed path does not have Chinese or white space
Do not change the port number, keep the default

3. Login or exit the MySQL client command
Login: mysql-u root-p123-h localhost;
-U: The following root is the user name, which is used by the Super Administrator Root;
-P: The following 123 is the password, which is the password that was specified when MySQL was installed;
-H: The following is the hostname of the server, it can be omitted, for example: Mysql-u root-p 123;
Exit: Quit or exit;

To start or close the MySQL service command:
Startup: net start MySQL service name
Close: net stop MySQL service name

4.MYSQL Common Concepts (understanding)
Database server (MySQL software)
Database
Database tables
Table Records

Two. SQL language (! Master)
SQL is a common language for operation-relational databases
SQL non-procedural language
SQL is a standard, and each major database vendor provides implementations based on this standard.
Database vendors to enhance the functionality of the database, added some non-standard SQL, called the database "dialect."

Three. Use SQL statements to manipulate the database (! Master)
1. Create a database
Grammar:
CREATE DATABASE [IF not EXISTS] db_name [create_specification [, Create_specification] ...]

Create_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name
Where Charset_name is the default character set specified for the database
Collate is the default proofing rule specified for the database
Proofing rules are a set of rules that are used to compare characters within a character set, and you can control where the case is sensitive when a select query is a rule.
Practice:
~ Create a database called MYDB1.
Create Database mydb1;
~ Create a MYDB2 database that uses the GBK character set.
Create DATABASE mydb2 character Set GBK;
~ 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
Grammar:
Show all databases:
SHOW DATABASES
To display the database creation statement:
SHOW CREATE DATABASE db_name

Practice:
~ 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. Modify the Database
Grammar:
ALTER DATABASE [IF not EXISTS] db_name [alter_specification [, Alter_specification] ...]

Alter_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name
Practice:
~ View the database in the server and modify the MYDB2 library's character set to UTF8
ALTER DATABASE MYDB2 character set UTF8;

4. Deleting a database
Grammar:
DROP DATABASE [IF EXISTS] Db_name
Practice:
~ Delete the previously created MYDB1 database
DROP DATABASE mydb1;
5. Select a database
Grammar:
Select database: Use Db_name;
Query the currently selected data: Select database ();

Do not exit the database command, if you want to exit the current database into another data, directly use the other database can be

Four. Use SQL statements to manipulate database tables (! Master)
Common data types in 0.MySQL
(1) string
a) char (n) fixed length string Stuid char (10)
b) varchar (n) variable length string username varchar (10) 255 65535 (after 5.0)

(2) numeric type
TINYINT: occupies 1 bytes, relative to byte in Java
SMALLINT: occupies 2 bytes, relative to short in Java
int: occupies 4 bytes, relative to int in Java
BIGINT: Occupies 8 bytes, relative to Long in Java
Float:4 byte single-precision floating-point type, relative to float in Java
Double:8 byte double-precision floating-point type, as opposed to double in Java

(3) Big data types
Blob:
Binary type, can be stored in binary type of data, through this field, the picture, audio, video and other data can be stored in binary form into the database. The maximum is 4GB.

TEXT:
Large text, which is declared as a field of this type, can hold a large number of character data, up to a maximum of 4GB.

Note: text belongs to the MySQL dialect and is clob type in other databases
(4) Date type
Date: 2015-11-05
Time: Format ' HH:MM:SS ' 19:19:19
DATETIME: DateTime 2015-11-05 19:19:19 Year range: 1000~9999
TIMESTAMP: Timestamp 2015-11-05 19:19:19 year range: 1970~2038

The difference between **datetime and timestamp?
* DateTime and timestamp display the same results, are fixed "YYYY-MM-DD HH:MM:SS" format
* The range of datetime support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. The timestamp display range is ' 1970-01-01 00:00:00 ' to 2037, and its actual storage value is the number of milliseconds from 1970 to the current time.
* When building a table, the date type listed as timestamp can set a default value, and DateTime does not.
* When updating a table, you can set the timestamp type of column auto-update time to the current time.

(5) Logic type
Bit 0/1

1. Field constraints (!!) Important
PRIMARY KEY constraint: Primary key value must be unique and cannot be empty auto_increment autogrow
To add a PRIMARY KEY constraint: such as: ID int primary KEY

Unique constraint: Unique value must be unique
To add a unique constraint: such as: Name varchar (unique)

Non-empty constraint: NOT NULL value cannot be null
Add non-null constraint: gender bit NOT NULL

FOREIGN KEY constraint: (Put to the back of the single talk)
2. New Table
Grammar:
CREATE TABLE table_name
(
Field1 datatype,
Field2 datatype,
FIELD3 datatype
) [Character set character set] [collate proofing rules]

Field: Specify column name datatype: Specify column type

Note: When you create a table, you create the appropriate columns based on the data you want to save, and you define the appropriate column types based on the type of data.
When you create a table, you generally do not need to specify character set encoding and collation rules, and the database remains consistent.
Practice:
~ Create Employee table
CREATE TABLE Employee (
ID int,
Name varchar (50),
Gender bit,
Birthday date,
Entry_date date,
Job varchar (100),
Salary Double,
Resume text
);
==========================
CREATE TABLE Employee (
ID int primary KEY auto_increment,
Name varchar (unique),
Gender bit NOT NULL,
Birthday date,
Entry_date date,
Job varchar (100),
Salary Double,
Resume text
);

3. View the table
Grammar:
To view the table structure:
Desc tabname
View all current tables:
Show tables
View the current database table build Table statement
Show CREATE TABLE tabname;

4. Modify the table
Grammar:
Add Columns:
ALTER TABLE tabname ADD (column datatype [DEFAULT expr][, column datatype] ...);

To modify a column:
ALTER TABLE tabname MODIFY (column datatype [DEFAULT expr][, column datatype] ...);

To delete a column:
ALTER TABLE tabname DROP (column);

Modify Table Name:
ALTER TABLE old_tabname RENAME to New_tabname;
Or
RENAME TABLE old_tabname to New_tabname;

To modify a column name:
ALTER TABLE tabname Change [column] Old_col_name new_col_name datatype

To modify the order of columns:
ALTER TABLE tabname MODIFY col_name1 datatype after col_name2;

To modify the character set of a table:
ALTER TABLE tabname CHARACTER SET character_name;
Practice:
~ Add an image column based on 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.
ALTER TABLE employee Rename to user;
Or
Rename table user to employee;
~ Column Name name modified to username
ALTER TABLE employee change name username varchar (255);
~ Inserts an image after the ID column
ALTER TABLE employee Modify image blob after ID;
~ The character set of the modified table is UTF8
ALTER TABLE employee character set UTF8;
5. Delete a table
Grammar:
DROP TABLE tabname;
Practice:
~ Delete User Table
drop table user;

Five. Using SQL statements to manipulate database table records
1.INSERT (! important)
Grammar:
INSERT into TabName [(column [, Column ...])] VALUES (value [, value ...]);
Attention:
The data you insert should be the same as the data type of the field
The size of the data should be within the specified range of the column
The data locations listed in values must correspond to the arranged positions of the columns being joined
string and date format data are enclosed in single quotes
If you want to insert all the fields, you can save the list of columns by writing the list of values directly in the table's field order

Practice:
~ Insert three data into the employee table
INSERT into employee values (null, ' Zhang Fei ', 1, ' 1988-1-1 ', ' 1999-1-1 ', ' Kill the Pig ', 8888.88, ' play with a good knife ~ ~ ');
INSERT into employee values (null, ' Guan Yu ', 0, ' 1989-1-1 ', ' 1998-1-1 ', ' sell rice ', 6666.66, ' sell the hand good rice ~ ');
INSERT into employee values (null, ' Liu Bei ', 1, ' 1979-1-1 ', ' 1980-1-1 ', ' CEO ', 100000, ' play Master ');

* * Garbled problem:
* * Garbled problem: The data sent by the client is GBK, and the server uses the Utf-8 encoding to handle the data sent by the client, and the coding inconsistency between the two ends causes garbled characters.
* * Workaround 1: The client can pass set names xxx; Notifies the server to use the specified code table to process data sent by the client. This method is only valid for the current CMD command window and is set once for each new window.
* * Workaround 2: You can specify the code table used on the server side by modifying the configuration in the My.ini file in the MySQL installation directory (57 line encoding GBK). This way once and for all.

2.UPDATE (! important)
Grammar:
UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]
The update syntax can update the columns in the original table row with the new values.
The SET clause indicates which columns to modify and which values to give.
The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated.
Practice:
~ 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 ';
~ will be named ' Guan Yu ' employee salary revised to 4000 yuan, job changed to "Play Broadsword."
Update employee set salary=4000,job= ' Play Broadsword ' 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 ';
Update employee set salary+=1000 where Name= ' Liu Bei ';//Error

3.DELETE (! important)
Grammar:
Delete from Tab_name [WHERE where_definition]
Where is used to filter the records to be deleted, and if you do not use the WHERE clause, all data in the table is deleted.
Delete statement cannot delete a column's value (can use update)
The DELETE statement deletes only records and does not delete the table itself. To delete a table, use the DROP TABLE statement.

The TRUNCATE table Tab_name statement can also delete data in the table, which differs from delete.
Delete is a deleted record, truncate is to destroy the whole table and rebuild the same structure of the table, truncate more efficient.

Practice:
~ 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 TABLE employee;

4.SELECT (!!! Important
(1) Basic inquiry
Grammar:
SELECT [DISTINCT] *| {column1, Column2. Column3 ...} Fromtable_name;
SELECT specifies which columns of data are queried.
column specifies the list name.
The * number represents the query for all columns.
from specifies which table to query.
Distinct optional, indicates whether duplicate data is excluded when the result is displayed
Practice:
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);
INSERT INTO exam values (null, ' Zhang Sanfeng ', 82,79,null);

~ 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, 中文版 from exam;
~ Filter duplicate data in the table.
SELECT distinct 中文版 from exam;
-Add 10 points to all student scores.
Select Math+10 as mathematics, chinese+10 as language, english+10 as English from exam;
~ Count Each student's total score.
Select Math+chinese+ifnull (中文版, 0) as total from exam;

~ Use aliases to indicate student totals.
Select Math+chinese+ifnull (中文版, 0) as total from exam;

(2) query using the WHERE clause
Grammar:
Select *| column name from TableName [WHERE Where_definition]

Practice:
~ Search for students whose names are Guan Yu's grades
SELECT * FROM exam where name= ' Guan Yu ';
~ Query students with English scores greater than 90 points
SELECT * from exam; where english>90;
~ Search all students with a total score greater than 230
Select ID, name, ifnull (English, 0) +ifnull (math, 0) +ifnull (chinese,0) as total score from exam where ifnull (中文版, 0) +ifnull (math , 0) +ifnull (chinese,0) >230;
~ Check the language score between 80-100 students.
SELECT * FROM exam where Chinese between and 100;
~ Query Math scores for 75,76,77 's classmates. And then the students who are not in this range are being queried.
SELECT * from exam where math in (75, 76, 77);
SELECT * from exam where math=75 or math=76 or math=77;

SELECT * FROM exam where math isn't 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
Grammar:
SELECT Column1, Column2. Column3. From table order by column Asc|desc;
Order BY specifies the sorted column, which can be the column name in the table, or the column name specified after the SELECT statement.
ASC Ascending, desc descending
The ORDER by clause should be at the end of the SELECT statement.

Practice:
~ Output after sorting the English scores.
Select ID, name, 中文版 from exam order by 中文版;
~ Sorting by total score output from highest to lowest order
Select ID, Name, ifnull (math,0) +ifnull (Chinese, 0) +ifnull (english,0) as total score from exam order by total: desc;
~ Sort the output of the student's grade of Zhang surname
Select ID, Name, ifnull (math,0) +ifnull (Chinese, 0) +ifnull (english,0) as total score from exam where name like ' sheet% ' ORDER BY total desc ;
(4) Aggregation function
Grammar:
To find the number of records in a specified column in a record that matches a condition
Select count (column name) ... from TableName [WHERE Where_definition]
To specify the value of a column in a record that matches a condition
Select sum (column name) ... from TableName [WHERE Where_definition]
To find the average of a specified column in a record that matches a condition
Select avg (column name) ... from TableName [WHERE Where_definition]
The maximum value of the specified column in a record that matches the criteria
Select Max (column name) ... from TableName [WHERE Where_definition]
To find the minimum value for the specified column in a record that matches a condition
Select min (column name) ... from TableName [WHERE Where_definition]
Practice:
~ How many students are there in a class?
Select COUNT (*) from exam;
~ How many students with a statistical math score greater than 75?
Select COUNT (*) from exam where math>75;
~ What is the number of people with total statistics greater than 230?
Select COUNT (*) as number from exam where (Ifnull (math,0) +ifnull (english,0) +ifnull (chinese,0)) >230;
~ Statistic A class math total?
Select sum (math) from exam;
Select SUM (中文版) from exam;
~ Statistics of a class of Chinese, English, mathematics, the total scores of each department
Select sum (math) Math, sum (Chinese) language, sum (English) from exam;
~ Statistics of a class of Chinese, English, math scores sum
Select sum (math) + sum (Chinese) +sum (中文版) as the total score from exam;

Select SUM (math+chinese+ifnull (english,0)) from exam;
~ Statistic The average score of a class's Chinese score
Select AVG (Chinese) from exam;
Select SUM (Chinese)/count (Chinese) from exam;
~ Ask for a class math average score?
Select AVG (math) from exam;
~ to ask for a class score average?
Select AVG (math+chinese+ifnull (中文版, 0)) from exam;
~ Ask for the highest score and the lowest score of the class score
Select Max (math+chinese+ifnull (中文版, 0)) from exam;
Select min (math+chinese+ifnull (中文版, 0)) from exam;
(5) Group query
Grammar:
SELECT Column1, Column2. Column3. Fromtable GROUP BY column have ...

Practice:
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);

~ Display the total price of each category of goods after sorting the items in the order form
SELECT * FROM orders group by product;
Select COUNT (*) from the orders group by product;
Select SUM (Price) total 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 * FROM Orders GROUP by product have sum (price) >100;
-Query the name of the item with a unit price less than 100 and a total price greater than 100.
SELECT * FROM orders where price <100 GROUP by product have sum (price) >100;

SELECT * FROM orders have price <100;

Both the **where clause and the HAVING clause can be filtered, but the usage scenarios are different:
* WHERE clause filters data before grouping, cannot use aggregate functions and aliases
* Having clauses filter the data after grouping, you can use aggregate functions and aliases
* WHERE clause can be used where there is a have substitution, but the use of the same place is generally not used where to replace

Six. Database backup and Recovery (! Master)
(1) Back up the database in the CMD window:
Mysqldump-uroot-p db_name > D:/1.sql

Mysqldump-uroot-p mydb3 > D:/1.sql

(2) Recovering data to a database
Mode one: In the cmd window:
Mysql-uroot-p Db_name < D:/1.sql

Mysql-uroot-p MYDB3 < D:/1.sql
Mode two: in the MySQL client
SOURCE D:/1.sql

Seven. FOREIGN KEY constraint (!!) Important
Foreign keys: Used to inform database tables and table fields of the corresponding relationship, and let the database to help us maintain the relationship between the key is called the foreign key
FOREIGN key role: maintain data integrity consistency
Add foreign key: foreign key
Example: Foreign key (dept_id) references dept (ID)
Case:
CREATE TABLE Dept (
ID int primary KEY auto_increment,
Name varchar (20)
);

INSERT INTO dept values (NULL, ' Finance Department ');
INSERT INTO dept values (NULL, ' personnel ');
INSERT INTO dept values (NULL, ' Science Department ');
INSERT INTO dept values (NULL, ' Sales 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, ' Zhang San ', 1);
INSERT into EMP values (NULL, ' John Doe ', 2);
INSERT into EMP values (NULL, ' Lao Wang ', 3);
INSERT into EMP values (null, ' Zhao Si ', 4);
INSERT into EMP values (NULL, ' Liu Can ', 4);

Eight. Multi-table design (!!) Important
1-*: Add a column on a multi-party to save one's primary key to save the relationship between two tables as a foreign key
1-1: Add a column on either side save the other party's primary key as a foreign key to save the relationship between the two tables
*-*: In a third-party table to save two tables of the primary key as a foreign key to save the relationship between the two tables, you can split the many-to-many relationship into two one-to-many relationship to understand

Nine. Multi-table query (!!) Important
Case
CREATE TABLE Dept (
ID int primary KEY auto_increment,
Name varchar (20)
);

INSERT INTO dept values (NULL, ' Finance Department ');
INSERT INTO dept values (NULL, ' personnel ');
INSERT INTO dept values (NULL, ' Science Department ');
INSERT INTO dept values (NULL, ' Sales department ');

CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name varchar (20),
dept_id int
);

INSERT into EMP values (null, ' Zhang San ', 1);
INSERT into EMP values (NULL, ' John Doe ', 2);
INSERT into EMP values (NULL, ' Lao Wang ', 3);
INSERT into EMP values (NULL, ' Liu can ', 5);

Requirements: Query The Department information and Department of the corresponding employee information
SELECT * from dept, EMP;
Cartesian product query: The result of multiplying two tables. If the left table has m records and there are N records on the right, the result of the query is the M*n bar. These query results contain a large number of incorrect results, and typically do not use this query.

Inner JOIN query: The left table has a record on the right table.
SELECT * from Dept,emp where emp.dept_id=dept.id;
SELECT * FROM dept INNER join EMP on emp.dept_id=dept.id;

Left OUTER JOIN query: On the basis of the internal connection query, plus the table on the left and the right table does not have records
SELECT * FROM dept LEFT join EMP on emp.dept_id=dept.id;
Right outer join query: On the basis of the internal connection query, plus the table on the right side and the left table does not have records.
SELECT * FROM dept right join EMP on emp.dept_id=dept.id;

Full outer JOIN query: On the basis of the internal connection query, plus the table on the left side and the right table does not have records and the right table has the left table does not have records.
SELECT * FROM dept full join EMP on emp.dept_id=dept.id; #mysql不支持

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;

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 using notes