master MySQL Basic database of , Tables and operations recorded in tables
master MySQL Multi table creation and multi table query in
master MySQL Analyze the relationship between tables in and create tables correctly
understand MySQL Use of visual interface
* List content
1.1 Introduction to database
1.1.1MySQL An overview of
What is a database ?
A database is a file system that stores data , Need to pass standards SQL Statement .
Common databases ：
MySQL： Open source free database , Small and medium sized databases . Has been Oracle Acquired .MySQL6.x The version also began to charge .
Oracle： Large database of charges .Oracle Products of the company .Oracle Acquisition SUN company
DB2 ：IBM Company's database products , rechargeable . It's used more in the banking system
SQLServer：MS company . Charging for a medium-sized database .
SyBase： No more , But it provides a very professional data modeling tool PowerDesigner.
SQLite: Embedded small database , Application on mobile phone , such as android and ios
Java Related databases ：MYSQL,Oracle．
What is a relational database ?
Entities in a database and their relationships .
MySQL Database server structure
1.1.2MySQL Install and uninstall
See installation diagram for installation
When uninstalling, delete the files under both paths , Otherwise, the installation will fail
a.. double-click mysql-installer-community-220.127.116.11.msi Start installation , click I accept the license
tems（ I accept these terms ）, click next Move on to the next step
b click Custom custom installation
c. Then select MySQL Server 8.0.11-X64, Then click Advanced Options Configure the path
e. click Execute Start Setup , Until it appears Complete until , And then keep clicking next Arrival configuration MySQL dialog box
f. Choose the first one standalone mysql server/classic mysql
replication： independent mysql The server / classical mysql copy , The second is innodb cluster sandbox thst setup(for
testing only)：innodb Cluster sandbox thst set up （ Test only ）
g. Set the server configuration type and connection port ： continue next,Config Type: choice Development Machine, Enough for small and learning .Port
number： input 3306
h. Select here “Use Legacy Password
Encryption”, Use previous data encryption , Otherwise use navicat connect mysql When , An error is reported that authentication cannot be loaded .
i. input root password ,6 A bit is enough , Make sure you remember yourself and don't forget it
j. to configure mysql stay windows Name in the system , Do you want to select boot mysql service , Others have not been modified , click “Next”
k. send X agreement / MySQL Store as file , This is MySQL5.7.12 New protocol support added , Check at the same time Open windows firewall port
for network access Open for network access Windows Firewall port
l. click Execute Start the configuration you just made , Until all the options are green √, Finally, click finish->next->next Complete the installation
m. Finally, type in the command line window ：mysql -u root -p, Then enter the password mysql database
If prompted, the MySQL Of bin The directory is configured in the system environment variable path in , Then try again
1.1.3 Possible problems after installation
If you use mysql -u root -p It doesn't work. , You can mysql Of bin Directory configuration to path Values in variables
If the password is entered, then the system will flash back , have access to services.msc see MySQL Is the service started , If it doesn't start, just start it
1.1.4MySQL Of SQL sentence
What is? SQL?
SQL： Structured query language (Structured Query Language) abbreviation SQL( pronunciation ：/ˈes kjuː ˈel/
“S-Q-L”), Is a special purpose programming language , Is a database query and programming language , It is used to access data and query , Updating and managing relational database systems ; It is also an extension of the database script file .
SQL Classification of ：
Data definition language (DDL), for example ：CREATE,DROP,ALTER Etc .
Data manipulation language (DML), for example ：INSERT（ insert ）,UPDATE（ modify ）,DELETE（ delete ） sentence .
Data query language (DQL), for example ：SELECT sentence .（ Generally, they do not belong to the same category alone , Because there's only one statement ）.
Data control language (DCL), for example ：GRANT,REVOKE Etc .
Transaction control language （TCL）, for example ：COMMIT,ROLLBACK Etc .
SQL Language consists of four main types of programming language statements ： Data definition language (DDL), Data manipulation language (DML) And data control language (DCL) There is also the transaction control language （TCL）.
1.2SQL Use of statements
1.2.1 use SQL Statement to the database CRUD operation
【 Create database 】
create database Database name ;
create database Database name character set character set ;
【 view the database 】
View all databases in the database server :show databases;
View information about the definition of a database :show create database Database name ;
【 Delete database 】
drop database Database name ;
【 modify the database 】
alter database Database name character set character set ;
【 Other database operation commands 】
Switch database ：use Database name ;
View databases in use :select database();
1.2.2 use SQL Statement to the table in the database CRUD Operation of
【 Create table 】
create table Table name (
Field name type ( length ) constraint ,
Field name type ( length ) constraint ,
【MySQL Data type of 】
char/String char/varchar(char Fixed length string ,varchar Variable length string )
file type BLOB,TEXT TEXT Refers to a text file BLOB Binary file
【 Single table constraint 】
Primary key constraint ( There can be only one primary key in a table and the value must be unique )：primary key
auto_increment Automatic growth , from 1 start
MySQL Of auto_increment Property can only be used on primary keys
Unique constraint ( Value must be unique , However, a table can have multiple unique fields )：unique
Nonempty constraint ( Value cannot be empty )：not null
【 Create a commodity classification table 】
classification id int Type primary key Automatic growth （auto_increment）
Classification name String type length 20
create table category(category_id int primary key auto_increment,name
be careful ： Be sure to select a database before creating a table
use Database name ;
【 View table 】
View all tables in the database ：show tables;
View table structure ：desc Table name ;
View table creation information ：show create table Table name ;
【 Delete table 】
drop table Table name ;
【 Modification table 】
alter table Table name add Listing type ( length ) constraint ; Add column
alter table Table name modify Listing type ( length ) constraint ; Modify column type length and constraints
alter table Table name change Old column name New listing type ( length ) constraint ; Modify column name , type , length , constraint
alter table Table name drop Listing ; Delete column
rename table Table name to New table name ; Modify table name
alter table Table name character set character set ; Modify the character set of a table
1.2.3 use SQL Statement on the data in the table CRUD operation
Create product table ：
id, name , Price , classification id
【 insert record 】
insert into Table name ( Listing 1, Listing 2, Listing 3..) values ( value 1, value 2,..); Insert some columns into the table
//insert into table values ();
insert into Table name values ( value 1, value 2, value 3..); Insert all columns into the table
be careful ：
1. The number of column names and values The number of subsequent values is equal
2. The order of the columns should be consistent with the data type of the inserted value
3. The insertion value cannot exceed the maximum length .
4. If the value is a string or a date, it needs to be added ’’
【 Modification record 】
update Table name set Field name = value , Field name = value [where condition ];
be careful ：
1. The type of column name should be consistent with the modified value
2. The maximum length cannot be exceeded when the modification is worth
3. If the value is a string or a date, it needs to be added ’ ’
【where After the condition of writing 】
like： Using placeholders _ and % _ Represents a character % Represents any character .
in： Get a value in a range .
and： Multiple conditions can be spliced together
【 Delete record 】
delete from Table name [where condition ];
be careful ：
Delete all records in the table delete from Table name ; Or use it truncate table Table name ;
Deletion method ：delete Delete one by one . and truncate Delete table structure records directly .
Transaction control DML(transaction), and delete belong to DML. If in a transaction ,delete data , These data can be retrieved .truncate The deleted data cannot be found .
start transaction; Open transaction
commit; Commit transaction
rollback; Rollback transaction
【 Inquiry record 】
select [distinct]*[ Listing , Listing ] from surface [where condition ].
1. Query all products :
//select * from table;
2. Query product name and price :
//select name,price from table;
3. Alias query . The keywords used are as.as Can be omitted .
3.1 Table alias :
//select * from product as p;
3.2 Column alias ：
//select name full name ,price Price from product;
4. Query with duplicate values removed ：
//select distinct name,price from product;
select Operational query
1. Price of all goods +20 To display .
//select price+20 p from product;
1. Query the product name as xxx Product information ：
2. Query commodity price >xxx All commodity information of yuan :
3. Query commodity price >xxx And the name contains ‘ mobile phone ’ Product information for ：
4. View product id be equal to 1 perhaps 3 perhaps 5 Product information for
(order by) Sort query :
1. Query all products , Sort by price .(asc- Ascending order ,desc- Descending order )
2. The query name is xxx And sort them in descending order of price .
1. Get the sum of the prices of all the goods ：
//select sum(price) from product;
2. Get the average price of all goods ：
//select avg(price) from product;
3. Get the number of all products ：
//select count(*) from product;
（ Classify each item first ） use group by Grouping and having Condition query
1. according to category_id Field grouping , Count the number of goods after grouping ：
//select count(*),category_id from product group by category_id;
2. according to category_id grouping , Group statistics of the average price of each group of goods and the average price > 3000 Of category_id：
//select avg(price) p,category_id from product group by category_id having
SQL The order in which the query statements appear ：select…from…where…group…having…order by…
1.3SQL Creating multiple tables and their relationship constraints
Is there a relationship between the classification table and the commodity table ? If it does , How to represent this relationship in a database .
1.3.2 Technical analysis and Implementation
【 Foreign key constraint 】
Multiple tables are related , So, who will maintain the relationship ?
Foreign key constraint writing ：
Table already has a foreign key constraint ：
alter table product add foreign key (category_id) references
Direct foreign key constraints when creating tables ：
create table product(product_id int primary key auto_increment,name
varchar(20),price float,category_id int,foreign key (category_id) references
【 Relations of multiple tables 】
One to many relationship ： Customers and orders , Categories and commodities …
One to many table building principle ： Create a field on the more side , Field as a foreign key points to the primary key of one .
Many to many relationship ： Students and courses …
Principle of establishing tables for many to many relationships ： You need to create a third table , At least two fields in the middle table , These two fields are referred to as foreign keys respectively Primary key to each party .
One to one relationship ： It is not widely used in actual development . Because one to one can be created as a table .
Unique foreign key correspondence ： Suppose one to one is a one to many relationship , Create a foreign key on the side of many to point to the master of the one party key , Set foreign key to unique.
Primary key correspondence ： Let the primary keys of one-to-one parties establish a relationship .
1.4 multi-table query
Some data need to be combined with multiple tables to find the desired data , Multi table query is divided into cross join query , Inner join query , External join query , Subquery , Paging query, etc …
Cross join query ( Basically can't use - What you get is the product of two tables ) understand
grammar ：select * from A,B;
Inner join query ( Keywords used inner join – inner It can be omitted )
Implicit inner join ：select * from A,B where condition ;
Show internal connections ：select * from A inner join B on condition ;
External join query ( Keywords used outer join – outer It can be omitted )
Left external connection ：left outer join
select * from A left outer join B on condition ;
Right outer connection ：right outer join
select * from A right outer join B on condition ;
【 Subquery 】
query “ Mobile phone products ” Classified commodity information
【MySQL Paging query for 】
select * from product limit a,b;
a: Where to start ,b: How many queries .