- MySQL Basics
- Introduction to database
- Installation and uninstallation of database
- Start and login of database service
- Concept of SQL
- DDL operation database
- DDL operation table structure
- Data in DML operation table
- Data in DQL query table
- MySQL table constraints and database design
- DQL query statement
- Database backup and restore
- Constraints on database tables
- Relationship between tables
- Database design
- MySQL multi table query and transaction operation
Introduction to database
Storage method of data
Create object in Java: Student s = new Student(1, "Zhang San") exists in memory
Learned Java IO flow: save data to a file.
It cannot be saved permanently. The data is temporary.
Data can be saved permanently
It is inconvenient to operate data and query some data.
(1) The data can be saved permanently (2), the query speed is fast (3), and the data management is convenient
It occupies resources and needs to be purchased.
Concept of database
What is a database
- Warehouse for storing data
- In essence, it is a file system, or it is stored on the computer of the server in the form of files.
- All relational databases can use common SQL
Statement to manage DBMS DataBase Management System
Common database ranking
MySQL: an open source free database, a small database, has been acquired by Oracle. MySQL6.x also starts to charge. Later, Sun Corporation acquired mysql, and Sun Corporation was acquired by Oracle.
Oracle: a large database charged by Oracle company.
DB2: a database product of IBM, which is charged. It is often used in the banking system.
SQL Server: a medium-sized database charged by MicroSoft. C#,. net and other languages are often used.
SQLite: a small embedded database, applied to mobile phones, such as Android.
Installation and uninstallation of database
The installation process is divided into two parts:
- File decompression and copying process, default installation directory:
- After installation, the MySQL server must be configured
The name of the administrator in mysql.
Installation of database
Open the downloaded MySQL installation file, double-click to extract it, and run "mysql-5.5.40-win64.msi".
Select the installation type. There are three options: "Typical", "Complete" and "Custom". Select "Custom" and press "next" to continue.
Click Browse to manually specify the installation directory.
4. Fill in the installation directory. My name is "d:Program Files (x86)MySQLMySQL Server 5.0", and press "OK" to continue.
Confirm the previous settings. If there is any error, press "Back" to return to redo. Press "Install" to start the installation.
The installation is in progress. Please wait until the following interface appears to complete the installation of MYSQL.
After the database is installed, you need to configure the database to use the configuration of MYSQL.
After installation, the following interface appears to enter the mysql configuration wizard.
Select the configuration method, "Detailed Configuration" and "Standard Configuration". We select "Detailed Configuration" to familiarize ourselves with the configuration process.
8. Select the server type, "Developer Machine (development test class, mysql takes up little resources)", "Server Machine (server type, mysql takes up more resources)", "Dedicated MySQL Server Machine (special database server, mysql takes up all available resources)
9. Select the general purpose of mysql database, "multi-functional database (general multi-functional, good)", "Transactional Database Only (server type, focusing on transaction processing, general)", "non Transactional Database Only (non transactional, simple, mainly used for monitoring and counting, and the support for MyISAM data type is only non transactional)", and click "Next" to continue.
11. Select the number of concurrent connections to the website, and the number of simultaneous connections: Decision Supportt(DSS)/OLAP (about 20) "," Online Transaction "
Processing(OLTP) (about 500) and "Manual Setting".
- Whether to enable TCP/IP connection and set the port. If not, you can only access the MySQL database on your own machine. On this page, you can also select "Enable Strict Mode", so that MySQL will not allow minor syntax errors. If you are new, it is recommended that you cancel the standard mode to reduce trouble. However, after you are familiar with MySQL, try to use the standard mode, because it can reduce the possibility of harmful data entering the database. Press "Next" to continue.
- This is to set the default database language encoding of mysql (important). Generally, select UTF-8, and press "Next" to continue.
- Select whether to install MySQL as a Windows service. You can also specify the Service Name and whether to add the bin directory of Mysql to the Windows PATH (after adding, you can directly use the files under the bin without pointing out the directory name. For example, connect, "mysql.exe -uusername -ppassword;" It's OK. Don't point out mysql Exe, which is very convenient). I checked all of them here, but the Service Name remains unchanged. Press "Next" to continue.
- Ask if you want to change the password of the default root user (super management). "Enable root access from remote machines (whether the root user is allowed to log in on other machines, do not tick it if it is safe, and tick it if it is convenient)". Finally, "create an anonymous account" (create an anonymous user, the anonymous user can connect to the database, but can't operate data, including queries) "is generally not checked. After setting, press" Next "to continue.
- Confirm that the settings are correct, and press "Execute" to make the settings effective, that is, to complete the installation and configuration of MYSQL.
Note: after setting, a common error occurs when you press "Finish", that is, you can't "Start service", which usually occurs on servers that have previously installed mysql. The solution is to ensure that the previously installed MySQL servers are completely uninstalled; If not, check whether the previous password has been modified according to the above step, and follow the above operation; If it still doesn't work, back up and delete the data folder under the MySQL installation directory. After the installation is completed, delete the data folder generated by the installation, move the backed up data folder back, and restart the MySQL service. In this case, you may need to check the database and repair it once to prevent data errors.
Uninstallation of database
- Stop the MySQL service of window. Find "this computer" - > "management" - > "service" and stop the MySQL background service.
- Uninstall the MySQL installer. Find "control panel" - > "programs and functions" and uninstall the MySQL program.
- Delete all files in the MySQL installation directory.
- Delete the MySQL directory in the ProgramDate directory on disk c. Path: c: programdatamysql (it is a hidden file and needs to be displayed)
- Delete the MySQL directory in the ProgramDate directory on disk c. Path: c: programdatamysql (it is a hidden file and needs to be displayed)
Start and login of database service
There are two ways to start the MySQL server:
- Start automatically through service
- Manual start mode
Windows service mode startup
Find "this computer" - > "management" - > "service" and click start.
DOS command mode startup
Open cmd with administrator
net start mysql : start-up mysql Service net stop mysql:close mysql service
Console connection database
MySQL is a database that requires an account name and password to log in. It is used after logging in. It provides a default root account. You can log in with the password set during installation
4.3.1 login format
// Mode 1 mysql -u user name -p password // Mode 2 mysql -hip address -u user name -p password // Mode 3 mysql --host=ip address --user=user name --password=password
quit or exit
SQLyog graphical tool - client
SQLyog is a simple, efficient and powerful graphical MySQL database management tool produced by the famous Webyog company in the industry. Using SQLyog, you can quickly and intuitively maintain the remote MySQL database from any corner of the world through the network.
Log in to the database using SQLyog
Database management system
DataBase Management System (DBMS): refers to a kind of large-scale software for operating and managing databases, which is used to establish, use and maintain databases, and uniformly manage and control databases to ensure the security and integrity of databases. Users access the data in the tables in the database through the DataBase Management System.
Database management system, relationship between database and table
A database management program (DBMS) can manage multiple databases. Generally, developers will create a database for each application. In order to save the data of the entity in the application, multiple tables are generally created in the database to save the data of the entity User in the program.
The relationship among database management system, database and table is as shown in the figure:
Concept of SQL
What is SQL
Structured Query Language
1. It is a query specification for all relational databases. Different databases support it.
2. A common database operation language that can be used in different databases.
3. There are some differences between different database SQL statements
SQL statement classification
1. Data Definition Language (DDL) such as: database and table building
2. Data Manipulation Language(DML), such as adding, deleting and modifying records in tables
3. Data Query Language(DQL), such as query operations on tables
4. Data Control Language(DCL), such as setting user permissions
1. Each statement ends with a semicolon, if it is not mandatory in SQLyog.
2. SQL is case insensitive, and upper case and lower case are considered the same in keywords
3. 3 Notes:
Syntax of comments
This is a unique annotation method of mysql
DDL operation database
Several ways to create a database
CREATE DATABASE Database name;
Judge whether the database already exists. If it does not exist, create the database
CREATE DATABASE IF NOT EXISTS Database name;
Create database and specify character set
CREATE DATABASE Database name CHARACTER SET character set;
-- Create database directly db1 create database db1; -- Determine whether it exists. If it does not exist, create a database db2 create database if not exists db2; -- Create a database and specify the character set as gbk create database db3 character set gbk;
view the database
View all databases
View the definition information of a database
show create database db3; show create database db1;
modify the database
Modify the default character set of the database
ALTER DATABASE Database name CHARACTER SET character set;
Change the character set of db3 database to utf8
alter database db3 character set utf8;
Syntax for deleting a database
DROP DATABASE Database name;
View the database in use
SELECT DATABASE(); --One used mysql Global functions in
Use / switch database
USE Database name;
DDL operation table structure
Create table format
CREATE TABLE Table name ( Field name 1 field type 1, Field name 2 field type 2 );
Key to create the table
MySQL data type
The commonly used data types are as follows:
The created student table contains ID, name and birthday fields
create table student ( id int, -- integer name varchar(20), -- character string birthday date -- Birthday, no comma at the end );
View table structure
DESC Table name;
View the SQL statement that created the table
SHOW CREATE TABLE Table name;
View all tables in db1 database
use db1; show tables;
View student's create table SQL statement
show create table student;
Quickly create a table with the same table structure
CREATE TABLE New table name LIKE Old table name;
Create s1 table. The structure of s1 table is the same as that of student table
-- Create a s1 Table and student Same structure create table s1 like student; desc s1;
Delete table directly
DROP TABLE Table name;
Judge whether the table exists, and delete the table if it exists
DROP TABLE IF EXISTS Table name;
-- Delete table directly s1 surface drop table s1; -- Determine whether the table exists and delete it s1 surface drop table if exists `create`;
Modify table structure
ADD table column ADD
ALTER TABLE Table name ADD Column name type;
MODIFY column type MODIFY
ALTER TABLE Table name MODIFY Column name (new type);
Modify column name CHANGE
ALTER TABLE Table name CHANGE Old column name new column name type;
Delete column DROP
ALTER TABLE Table name DROP Column name;
Modify table name
RENAME TABLE Table name TO New table name;
Modify character set
ALTER TABLE Table name character set character set;
Data in DML operation table
It is used to add, delete and modify records in the table
INSERT [INTO] Table name [Field name] VALUES (field value)
Insert all fields:
Write all the field names
INSERT INTO Table name (Field name 1, Field name 2, Field name 3) VALUES (Value 1, Value 2, Value 3);
Do not write field name
INSERT INTO Table name VALUES (Value 1, Value 2, Value 3);
Insert partial data
INSERT INTO Table name (Field name 1, Field name 2, ...) VALUES (Value 1, Value 2, ...);
-- Insert all columns into the student table insert into student (id,name,age,sex) values (1, 'Sun WuKong', 34, 'male'); insert into student (id,name,age,sex) values (2, 'Gohan ', 16, 'male');
Precautions for insert:
- The inserted data should be of the same data type as the field
- The size of the data should be within the specified range of the column. For example, a string with a length of 80 cannot be added to a column with a length of 40.
- The data positions listed in values must correspond to the arrangement positions of the added columns. value can be used in mysql, but it is not recommended to
Yes, the function is the same as values.
- Character and date data should be enclosed in single quotation marks. Double quotation marks can also be used as separators in MySQL.
- If no column is specified or null is used, null value is inserted.
What is worm replication
Copy data from an existing table to another table.
Copy all columns in table name 2 to table name 1
INSERT INTO Table name 1 SELECT * FROM Table name 2;
Copy only some columns
INSERT INTO Table name 1(Column 1, Column 2) SELECT Column 1, Column 2 FROM student;
Update table record
UPDATE Table name SET Column name=value [WHERE Conditional expression]
Modify data without conditions
UPDATE Table name SET Field name=value; --Modify all rows
Modify data with conditions
UPDATE Table name SET Field name=value WHERE Field name=value;
-- Modify data without conditions and change all gender to female update student set sex = 'female'; -- Modify data with conditions, and id Change the gender of student No. 2 to male update student set sex='male' where id=2; -- Modify multiple columns at once, and id The age of the student who is 3 is changed to 26, address Changed to Beijing update student set age=26, address='Beijing' where id=3;
Delete table record
DELETE FROM Table name [WHERE Conditional expression]
Delete data without conditions
DELETE FROM Table name;
Delete data with conditions
Delete data with conditions
Use truncate to delete all records in the table
TRUNCATE TABLE Table name;
The difference between truncate and delete:
truncate is equivalent to deleting the table structure and creating a new table.
-- Delete data with conditions, delete id Record of 1 delete from student where id=1; -- Delete data without conditions,Delete all data in the table delete from student;
Data in DQL query table
Query does not modify the data in the database. It is just a way to display the data.
SELECT Column name FROM Table name [WHERE Conditional expression]
Use * for all columns
SELECT * FROM Table name;
Query all students:
select * from student;
Query the data of the specified column, with multiple columns separated by commas
SELECT Field name 1, Field name 2, Field name 3, ... FROM Table name;
Query the name and age columns in the student table
select name,age from student;
Specify the alias of the column to query
Advantages of using aliases: new names are used for display without modifying the table structure.
1. Assign an alias to a column
SELECT Field name 1 AS alias, Field name 2 AS alias... FROM Table name;
2. Specify aliases for both columns and tables
SELECT Field name 1 AS alias, Field name 2 AS alias... FROM Table name AS Table alias;
-- Use alias select name as full name,age as Age from student; -- Tables: using aliases select st.name as full name,age as Age from student as st
Clear duplicate values
Query the specified column and no duplicate data appears in the result
SELECT DISTINCT Field name FROM Table name;
-- Find out where the students come from select address from student; -- Remove duplicate records select distinct address from student;
Query results participate in calculation
A column of data and fixed value operation:
SELECT Column name 1 + Fixed value FROM Table name;
A column of data and other column data participate in the operation:
SELECT Column name 1 + Column name 2 FROM Table name;
--Add math and English score columns,Add the corresponding math and English scores to each record, and add the math and English scores when querying select * from student; -- Add 5 points to all math select math+5 from student; -- query math + english And select * from student; select *,(math+english) as Total score from student; -- as Can be omitted select *,(math+english) Total score from student;
Why do you want to query by criteria
If there are no query criteria, all rows will be queried each time. In practical application, it is generally necessary to specify query conditions. Filter records.
Syntax of conditional query:
SELECT Field name FROM Table name WHERE condition; --Process: take out each data in the table, return the records that meet the conditions, and do not return the records that do not meet the conditions
< > in SQL, it means not equal to. It can also be used in mysql= No==
Within a range, for example, between 100 and 200 is equivalent to the condition between 100 and 200, with both the header and the tail
Collection represents multiple values, separated by commas
LIKE ' %'
Query the NULL value of a column. Note: cannot write = NULL
- Create presentation table - CREATE TABLE student3 ( id int, -- number name varchar(20), -- full name age int, -- Age sex varchar(5), -- Gender address varchar(100), -- address math int, -- mathematics english int -- English ); INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'Sun WuKong',55,'male','Hangzhou',66,78), (2,'Qiqi',55,'female','Shenzhen',98,87), (3,'Gohan ',35,'male','Hong Kong',56,77), (4,'Bulma ',30,'female','Chengdu',76,65), (5,'Goten ',28,'male','Beijing',86,NULL), (6,'Feliza',572,'male','Hong Kong',85,97), (7,'Xiaofang',21,'female','Hong Kong',99,99), (8,'Begita',62,'male','Zhengzhou',56,65), (9,'Majin Buu ',432,'male','Wuhan',83,75), (10,'Babe',12,'male','Guangzhou',95,63) ); -- query math Students with scores greater than 80 select * from student3 where math>80; -- query english Students with scores less than or equal to 80 select * from student3 where english <=80; -- query age Equal to 20-year-old students select * from student3 where age = 20; -- query age Not equal to 20-year-old students, note: not equal to two ways of writing select * from student3 where age <> 20; select * from student3 where age != 20;
Or & & and, the former is recommended in SQL, but the latter is not universal.
-- query age Students over 35 and male(Both conditions are satisfied at the same time) select * from student3 where age>35 and sex='male'; -- query age Students over 35 or male(One of the two conditions is satisfied) select * from student3 where age>35 or sex='male'; -- query id Students who are 1 or 3 or 5 select * from student3 where id=1 or id=3 or id=5;
SELECT Field name FROM Table name WHERE field in (Data 1, Data 2...);
-- query id Students who are 1 or 3 or 5 select * from student3 where id in(1,3,5); -- query id Students who are not 1 or 3 or 5 select * from student3 where id not in(1,3,5);
BETWEEN Value 1 AND Value 2
Query students with english scores greater than or equal to 75 and less than or equal to 90
select * from student3 where english between 75 and 90;
SELECT * FROM Table name WHERE Field name LIKE 'Wildcard string';
Match any number of strings
Match one character
-- Inquire about students surnamed sun select * from student3 where name like 'Sun%'; select * from student3 where name like 'Sun'; -- The query name contains:'cloth'A student of Chinese characters select * from student3 where name like '%cloth%'; -- Query students whose surname is Bei and whose name has two characters select * from student3 where name like 'shellfish_';
MySQL table constraints and database design
DQL query statement
adopt ORDER BY Clause to sort the query results(Sorting is only a display method and will not affect the order of data in the database) SELECT Field name FROM Table name WHERE field=value ORDER BY Field name [ASC|DESC]; ASC: Ascending, default DESC: Descending order
Single column sorting: sort by only one field.
-- Query all data,Sort by age in descending order select * from student order by age desc;
Combined sorting: multiple fields are sorted at the same time. If the first field is equal, the second field is sorted, and so on.
Combined sort syntax:
SELECT Field name FROM Table name WHERE field=value ORDER BY Field name 1 [ASC|DESC], Field name 2 [ASC|DESC]; -- Query all data,On the basis of descending order of age, if the age is the same, it will be sorted in ascending order of math scores select * from student order by age desc, math asc;
The queries we have done before are all horizontal queries, which are judged line by line according to the conditions. The query using aggregate functions is a vertical query, which calculates the value of a column and then returns a result value. The aggregate function ignores NULL.
Five aggregate functions:
Aggregate functions in SQL
Max (column name)
Find the maximum value of this column
Min (column name)
Find the minimum value of this column
AVG (column name)
Average this column
Count (column name)
Count the number of records in this column
Sum (column name)
Sum this column
SELECT Aggregate function(Column name) FROM Table name; -- Query the total number of students select count(id) as Total number from student; select count(*) as Total number from student;
-- query id Field, if it is null，Use 0 instead select ifnull(id,0) from student; select count(ifnull(id,0)) from student;
-- Query the total number of people older than 20 select count(*) from student where age>20; -- Query math score select sum(math) Total score from student; -- Average score of query mathematics select avg(math) average from student; -- Query the highest score in Mathematics select max(math) Highest score from student; -- Query the lowest score of Mathematics select min(math) Lowest score from student;
Group query refers to grouping the query information using the GROUP BY statement, with the same data as a group
SELECT Field 1,Field 2... FROM Table name GROUP BY Group field [HAVING condition];
GROUP BY takes the same content in the result of grouping field as a group and returns the first piece of data of each group, so grouping alone is useless. The purpose of grouping is for statistics. Generally, grouping is used together with aggregation functions.
-- Group by gender and find the average score of boys and girls in Mathematics select sex, avg(math) from student3 group by sex;
Use having to query people over 25 years old, group them by sex, count the number of people in each group, and only display the data with the number of men and women greater than 2:
-- Filter the results of grouping query SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
1. Before grouping the query results, remove the rows that do not meet the where criteria, that is, filter the data before grouping, that is, filter first and then group. 2. Aggregate function cannot be used after where
1. The having clause is used to filter the groups that meet the conditions, that is, filter the data after grouping, that is, grouping first and then filtering. 2. Aggregate functions can be used after having
Function of limit:
LIMIT means LIMIT, so the function of LIMIT is to LIMIT the number of query records.
LIMIT syntax format:
LIMIT offset,length --offset: Starting number of lines, counting from 0. If omitted, the default is 0 --length: Number of returned rows
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (11,'Tang Monk',25,'male','Chang'an',87,78), (12,'Sun WuKong',18,'male','Huaguo Mountain',100,66), (13,'Zhu Bajie',22,'male','Gao Laozhuang',58,78), (14,'Monk Sha',50,'male','Liusha River',77,88), (15,'Baigujing',22,'female','Baihuling',66,66), (16,'spider goblin',23,'female','Coiled wire hole',88,88); -- Query the data in the student table and display it from item 3 to 6. select * from student3 limit 2,6; -- Finally, if there are less than 5, how many will be displayed
Database backup and restore
Application scenario of backup
Data failure may occur when data transmission, data storage and data exchange are performed on the server. Such as unexpected shutdown or damage of storage media. At this time, if data backup and data recovery methods and measures are not taken, data loss will be caused, and the resulting loss is irreparable and incalculable.
Backup and restore statements
Backup format: under DOS, when not logged in. This is an executable exe in the bin folder.
mysqldump -u user name -p Password database > Path to file
Restore format: commands in mysql can only be operated after login
USE database SOURCE Path to import file;
-- backups day21 Data in database to d:a.sql In the file: mysqldump -uroot -proot day21 > d://a.sql --reduction use day21; source d://a.sql;
Graphical interface backup and restore
Back up the data in the database
- Select the database and right-click backup / export
- Specify the export path and save it as sql file
Restore data in the database
- Delete database
- Right click execute SQL script in the database list area to specify the SQL file to execute
Constraints on database tables
Overview of Database Constraints
Role of constraints:
Limit the data in the table to ensure the correctness, validity and integrity of the data. If constraints are added to a table, incorrect data cannot be inserted into the table. It is appropriate to add constraints when creating tables.
check (Note: mysql does not support)
Primary key constraint
Primary key functions:
It is used to uniquely identify each record in the database
Which field should be the primary key of the table?
Usually, the business field is not used as the primary key. Each table is designed with an id field, and the id is used as the primary key. Primary keys are used for databases and programs, not for final customers. Therefore, it doesn't matter whether the primary key has meaning, as long as it is not duplicate and non empty.
Create primary key:
Primary key keyword: primary key
Features of primary key:
- not null
Create primary key method:
When creating a table, add a primary key to the field
Field name field type PRIMARY KEY
Add a primary key to an existing table
ALTER TABLE table name add primary key (field name)
-- Create table student table st5, Include fields(id, name, age)take id As primary key create table st5 ( id int primary key, -- id Is the primary key name varchar(20), age int ) desc st5; -- Insert duplicate primary key value insert into st5 values (1, 'Guan Yu', 30); -- Error code: 1062 Duplicate entry '1' for key 'PRIMARY' insert into st5 values (1, 'Guan Yunchang', 20); select * from st5; -- insert NULL Primary key value of, Column 'id' cannot be null insert into st5 values (null, 'Guan Yunchang', 20); -- delete st5 Primary Key alter table st5 drop primary key; -- Add primary key alter table st5 add primary key(id);
Self increment of primary key:
If we add the primary key ourselves, it is likely to repeat. We usually want the database to automatically generate the value of the primary key field every time a new record is inserted.
AUTO_INCREMENT Indicates automatic growth(Field type must be integer type) -- insert data insert into st6 (name,age) values ('Sun Quan',17); insert into st6 (name,age) values ('Sun CE',25); -- Another way of writing insert into st6 values(null,'Sun Jian',36); select * from st6;
Modify the default value of self growth (starting value)
Auto by default_ The starting value of increment is 1. If you want to modify the starting value, please use the following SQL syntax
CREATE TABLE Table name( Column name int primary key AUTO_INCREMENT ) AUTO_INCREMENT=Starting value;
-- Specify a starting value of 1000 create table st4 ( id int primary key auto_increment, name varchar(20) ) auto_increment = 1000; insert into st4 values (null, 'kong ming'); select * from st4;
What is unique constraint: a column in a table cannot have duplicate values.
Basic format of unique constraint:
Field name field type UNIQUE
-- Create student table st7, Include fields(id, name),name Set unique constraint for this column,Students with the same name cannot appear create table st7 ( id int, name varchar(20) unique ) -- Add a student with the same name insert into st7 values (1, 'Zhang San'); select * from st7; -- Duplicate entry 'Zhang San' for key 'name' insert into st7 values (2, 'Zhang San'); -- Insert multiple repeatedly null insert into st7 values (2, null); insert into st7 values (3, null);
Non empty constraint
What is a non empty constraint: a column cannot be null.
Basic syntax format of non empty constraint:
Field name field type NOT NULL -- Create table student table st8, Include fields(id,name,gender)among name Cannot be NULL create table st8 ( id int, name varchar(20) not null, gender char(1) ) -- Add a record in which the name is not assigned insert into st8 values (1,'Zhang Sanfeng','male'); select * from st8; -- Column 'name' cannot be null insert into st8 values (2,null,'male');
Field name field type DEFAULT Default
-- Create a student table st9，Include fields(id,name,address)， The default address is Guangzhou create table st9 ( id int, name varchar(20), address varchar(20) default 'Guangzhou' ) -- Add a record,Use default address insert into st9 values (1, 'Li Si', default); select * from st9; insert into st9 (id,name) values (2, 'Li Bai'); -- Add a record,Do not use default address insert into st9 values (3, 'Li Siguang', 'Shenzhen');
Foreign key constraint
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- Add data INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Zhang San', 20, 'R & D department', 'Guangzhou'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Li Si', 21, 'R & D department', 'Guangzhou'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Wang Wu', 20, 'R & D department', 'Guangzhou'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Lao Wang', 20, 'Sales Department', 'Shenzhen'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('king', 22, 'Sales Department', 'Shenzhen'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('Xiao Wang', 18, 'Sales Department', 'Shenzhen');
Disadvantages of the above data table:
- data redundancy
- Later, there will be problems of addition, deletion and modification
-- Solution: divided into 2 tables -- Create department table(id,dep_name,dep_location) -- One side, main table create table department( id int primary key auto_increment, dep_name varchar(20), dep_location varchar(20) ); -- Create employee table(id,name,age,dep_id) -- Multi party, slave table create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int -- The foreign key corresponds to the primary key of the primary table ) -- Add 2 departments insert into department values(null, 'R & D department','Guangzhou'),(null, 'Sales Department', 'Shenzhen'); select * from department; -- Add employee,dep_id Indicates the Department of the employee INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Wang Wu', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Wang', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiao Wang', 18, 2); select * from employee;
Question: when we DEP in employee_ id
Enter the Department that does not exist, and the data can still be added However, there is no corresponding department, so this cannot happen in practical application. Dep of employee_ The data in ID can only be
id existing in department table
Target: dep needs to be constrained_ id can only be an id that already exists in the department table
Solution: use foreign key constraint
What is a foreign key constraint:
What is a foreign key: the column in the slave table that corresponds to the primary key of the master table, such as DEP in the employee table_ id
A table used by one party to bind others
Slave table: a multi-party, constrained table
Syntax for creating constraints:
1. Add foreign key when creating a new table:
[CONSTRAINT] [Foreign key constraint name] FOREIGN KEY(Foreign key field name) REFERENCES Primary table name(Primary key field name)
2. Adding foreign keys to existing tables:
ALTER TABLE From table ADD [CONSTRAINT] [Foreign key constraint name] FOREIGN KEY (Foreign key field name) REFERENCES Main table(Primary key field name);
-- 1) Delete secondary table/From table employee drop table employee; -- 2) Create from table employee And add foreign key constraints emp_depid_fk -- Multi party, slave table create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- The foreign key corresponds to the primary key of the primary table -- Create a foreign key constraint constraint emp_depid_fk foreign key (dep_id) references department(id) ) -- 3) Add data normally INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Wang Wu', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Wang', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiao Wang', 18, 2); select * from employee; -- 4) Failed to add data with wrong department -- Insert department that does not exist -- Cannot add or update a child row: a foreign key constraint fails INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Zhang', 18, 6);
Delete foreign key:
ALTER TABLE From table drop foreign key Foreign key name;
-- delete employee Tabular emp_depid_fk Foreign key alter table employee drop foreign key emp_depid_fk; -- stay employee Add a foreign key when an expression exists alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);
Cascade of foreign keys:
New problems arise
select * from employee; select * from department; -- We should take the id If the value is changed from 2 to 5, can I update it directly? -- Cannot delete or update a parent row: a foreign key constraint fails update department set id=5 where id=2; -- To delete a department: id Department equal to 1, Can I delete it directly? -- Cannot delete or update a parent row: a foreign key constraint fails delete from department where id=1;
What is cascading operation:
When modifying and deleting the primary key of the primary table, the foreign key value of the secondary table is updated or deleted at the same time, which is called cascade operation.
Cascade operation syntax
ON UPDATE CASCADE
Cascading updates can only be used to create cascading relationships when creating tables. Update the primary key in the primary table, and the foreign key columns in the secondary table are automatically updated synchronously
ON DELETE CASCADE
-- delete employee Table, recreate employee Tables, adding cascading updates and deleting cascading drop table employee; create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- The foreign key corresponds to the primary key of the primary table -- Create a foreign key constraint constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade ) -- Add data to employee table and department table again INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Wang Wu', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Wang', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiao Wang', 18, 2); -- Delete department table? Can I delete it directly? drop table department; -- Put the Department in the table id The Department equal to 1 is changed to id Equal to 10 update department set id=10 where id=1; select * from employee; select * from department; -- Delete the Department whose department number is 2 delete from department where id=2;
Data constraint summary:
1. Unique 2. Non empty
If a column has no value, use the default value
This column must have a value
This column cannot have duplicate values
The primary key column in the master table and the external key column in the slave table
Relationship between tables
Concept of table relation
In real life, there must be relationships between entities, such as husbands and wives, departments and employees, teachers and students. Then when we design tables, we should reflect this relationship between tables!
Three relationships between tables
- One to many: the most commonly used relationship departments and employees
- Many to many: student selection list and student list, one course can be selected by multiple students, and one student can select multiple courses
- One to one: relatively less used. Employee form resume form citizen form passport form
One to many
One to many (1:n) for example: classes and students, departments and employees, customers and orders, classifications and commodities
One to many table creation principle: create a field in the slave table (multi-party), and the field points to the primary key of the master table (one party) as a foreign key
Many to many
Many to many (m:n) for example: teachers and students, students and courses, users and roles
Principle of creating many to many tables: you need to create a third table. There are at least two fields in the middle table. These two fields are used as foreign keys to point to the primary keys of their respective parties.
One to one (1:1) is not widely used in actual development, because one to one can be created into a table.
Two kinds of table building principles:
One to one table building principle
Foreign key unique
The primary key of the master table and the foreign key (UNIQUE) of the slave table form a master foreign key relationship. The foreign key is UNIQUE
The foreign key is a primary key
The primary key of the master table and the primary key of the slave table form a primary foreign key relationship
What is a paradigm:
A good database design will have an important impact on data storage performance and later program development. To establish a scientific and standardized database, we need to meet some rules to optimize the design and storage of data. These rules are called paradigms.
At present, there are six paradigms in relational databases: the first normal form (1NF), the second normal form (2NF), the third normal form (3NF), the bath Codd normal form (BCNF), the fourth normal form (4NF) and the fifth normal form (5NF, also known as the perfect normal form).
The normal form satisfying the minimum requirement is the first normal form (1NF). The second normal form (2NF) that further satisfies more specification requirements on the basis of the first normal form is called the second normal form, and so on. Generally speaking, the database only needs to meet the third normal form (3NF).
Each column of a database table is an indivisible atomic data item, and cannot be a set, array or other non atomic data item. That is, when a column in a table has multiple values, it must be split into different columns. In short, each column of the first normal form cannot be split, which is called atomicity.
1. There is very serious data redundancy (duplication): name, department name, department head
2. There is a problem in adding data: when adding a new department and department head, the data is illegal
3. Problems in data deletion: Zhang Wuji graduated and deleted the data of the Department.
On the premise that the first normal form is satisfied, each field in the table is completely dependent on the primary key.
The so-called complete dependency means that there can be no columns that depend on only a part of the primary key. In short, the second normal form is based on the first normal form, and all columns are completely dependent on primary key columns. When a composite primary key contains multiple primary key columns, the second normal form is not met. For example, if a primary key has two columns, it cannot have such an attribute. It only depends on one of the columns, which is inconsistent with the second normal form.
Characteristics of the second paradigm:
- A table only describes one thing.
- Each column in the table is completely dependent on the primary key
1. (has been solved)
2. There is a problem in adding data: when adding a new department and department head, the data is illegal
3. Problems in data deletion: Zhang Wuji graduated and deleted the data of the Department.
Under the premise of satisfying the second normal form, each column in the table directly depends on the primary key, rather than indirectly relying on the primary key through other columns.
In short, the third normal form is that all columns do not depend on other non primary key columns, that is, on the basis of meeting 2NF, any non primary column must not pass the dependency on the primary key. The so-called transitive dependency means that if there is A decisive relationship of "A → B → C", then C transitively depends on A. Therefore, the third norm is satisfied
The database table of type should not have the following dependencies: primary key column → non primary key column x → non primary key column y
Student information form:
Summary of three paradigms
Atomicity: each column in the table cannot be split.
No local dependency, a table only describes one thing
There is no transitive dependency, and each column in the table directly depends on the primary key. Instead of relying on the primary key indirectly through other columns.
MySQL multi table query and transaction operation
Table connection query
What is multi table query
# Create department table create table dept( id int primary key auto_increment, name varchar(20) ) insert into dept (name) values ('Development Department'),('Marketing Department'),('Finance Department'); # Create employee table create table emp ( id int primary key auto_increment, name varchar(10), gender char(1), -- Gender salary double, -- wages join_date date, -- Entry date dept_id int, foreign key (dept_id) references dept(id) -- Foreign key, related department table(Primary key of department table) ) insert into emp(name,gender,salary,join_date,dept_id) values('Sun WuKong','male ',7200,'2013-02-24',1); insert into emp(name,gender,salary,join_date,dept_id) values('Zhu Bajie','male ',3600,'2010-12-02',2); insert into emp(name,gender,salary,join_date,dept_id) values('Tang Monk','male',9000,'2008- 08-08',2); insert into emp(name,gender,salary,join_date,dept_id) values('Baigujing','female ',5000,'2015-10-07',3); insert into emp(name,gender,salary,join_date,dept_id) values('spider goblin','female ',4500,'2011-03-14',1);
Functions of multi table query:
For example, if we want to query the name of Monkey King and the name of his department, we need to use multi table query.
If a SQL statement queries multiple tables, the query results are in multiple different tables. Each table takes one or more columns.
- There are two sets a and B. take all the components of these two sets.
- To complete multi table query, you need to eliminate useless data
Classification of multi table query:
Internal connection query
Implicit inner connection: eliminating useless data with where condition
– query all employee information and corresponding department information
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
– query the name and gender of the employee table. Name of department table
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;
t1.name, – name of employee table
t1.gender, -- gender of employee table
t2.name – name of the Department table
t1.dept_id = t2.id;
Explicit inner connection:
Syntax: select field list from table name 1 [inner] join table name 2 on condition
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
Internal connection query:
1. Which tables to query data from
2. What are the conditions
3. Which fields to query
External link query
Left outer connection:
select field list from table 1 left [outer] join table 2 on condition;
All the data in the left table and its intersection are queried.
-- Query all employee information. If an employee has a department, the Department name will be queried. If there is no department, the Department name will not be displayed SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
Right outer connection:
Syntax: select field list from table 1 right [outer] join table 2 on condition;
All the data in the right table and its intersection are queried.
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
Concept: nested queries in queries are called subqueries.
-- Query the information of the highest paid employee -- 1 How much is the highest salary? 9000 SELECT MAX(salary) FROM emp; -- 2 Query employee information, and the salary is equal to 9000 SELECT * FROM emp WHERE emp.`salary` = 9000; -- One sql This completes the operation. Subquery SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
Different situations of sub query
1. The results of sub query are single line and single column:
Subquery can be used as a condition, and operators can be used to judge. Operator: > > = < < ==
-- Query employees whose salary is less than the average salary SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
The results of the subquery are multi row and single column:
Subquery can be used as a condition, and the operator in is used to judge
– query all employee information of 'finance department' and 'marketing department'
SELECT id FROM dept WHERE NAME = 'finance department' OR NAME = 'marketing department';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'finance department' OR NAME = 'marketing department');
The results of the subquery are multi row and multi column:
A subquery can participate in the query as a virtual table
– query employee information and department information of employees whose entry date is after November 11, 2011
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2
WHERE t1.id = t2.dept_id;
– common internal connection
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11'
Basic introduction to transactions
If a business operation with multiple steps is managed by transactions, these operations will either succeed or fail at the same time.
- Start transaction: start transaction;
2. Rollback: rollback;
3. Submit: commit;
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
– add data
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
– Zhang San transfers 500 yuan to Li Si
– 0. Start transaction
– 1. Zhang San's account-500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
– 2. Li Si account + 500
– something went wrong
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
– if there is no problem in execution, submit the transaction
– if a problem is found, roll back the transaction
Transactions in MySQL database are automatically committed by default
There are two ways to commit a transaction:
1. Auto submit:
mysql is automatically submitted
A DML statement will automatically commit a transaction.
2. Manual submission:
By default, the Oracle database commits transactions manually
You need to start the transaction before committing
Modify the default commit method of transactions:
1. View the default commit method of transactions:
SELECT @@autocommit; -- 1 Represents automatic submission, 0 represents manual submission
2. Modify the default submission method:
set @@autocommit = 0;
Four characteristics of transactions
1. Atomicity: it is the smallest inseparable unit of operation. It can succeed or fail at the same time. 2. Persistence: after a transaction is committed or rolled back, the database will persistently save data. 3. Isolation: between multiple transactions. Independent of each other. 4. Consistency: the total amount of data remains unchanged before and after the transaction operation
Isolation level of the transaction
Concept: multiple transactions are isolated and independent. However, if multiple transactions operate on the same batch of data, some problems may arise. These problems can be solved by setting different isolation levels.
1. Dirty read: one transaction reads uncommitted data in another transaction
2. Non repeatable reading (virtual reading): in the same transaction, the data read twice is different.
3. Phantom reading: if one transaction operates all the records in the DML data table and another transaction adds a piece of data, the first transaction cannot query its own modifications.
1. read uncommitted: read uncommitted
Problems: dirty reading, non repeatable reading, unreal reading
2. read committed: read committed (Oracle)
Problems: non repeatable reading and unreal reading
3. repeatable read: repeatable read (MySQL default)
Problem: unreal reading
4. serializable: serialization
Can solve all problems
Note: the isolation level increases from small to large, with higher security but lower efficiency
Isolation level of database query:
Database setting isolation level:
set global transaction isolation level string;
set global transaction isolation level read uncommitted; start transaction; -- Transfer operation update account set balance = balance - 500 where id = 1; update account set balance = balance + 500 where id = 2;
DBA: Database Administrator
DCL: manage users, authorize
1. Add user:
CREATE USER 'user name'@'host name' IDENTIFIED BY 'password';
2. Delete user:
DROP USER 'user name'@'host name';
3. Modify user password:
UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'user name'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; SET PASSWORD FOR 'user name'@'host name' = PASSWORD('New password'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
Forgot the password of the root user in mysql?
1. cmd -- > net stop mysql stop it mysql service * An administrator is required to run the cmd 2. Start with no authentication mysql Services: mysqld --skip-grant-tables 3. Open new cmd window,Direct input mysql I command you to knock back. You can log in successfully 4. use mysql; 5. update user set password = password('Your new password') where user = 'root'; 6. Close both windows 7. Open Task Manager and end manually mysqld.exe Process of 8. start-up mysql service 9. Log in with a new password.
– 1. Switch to mysql database
– 2. Query the user table
SELECT * FROM USER;
* Wildcard: % Indicates that users can log in to the database on any host
1. Query permission: -- Query permission SHOW GRANTS FOR 'user name'@'host name'; SHOW GRANTS FOR 'lisi'@'%';
-- Grant permission grant Permission list on Database name.Table name to 'user name'@'host name'; -- Grant all permissions to user Zhang San on any database or table GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. Revoke authority:
-- Revoke permission: revoke Permission list on Database name.Table name from 'user name'@'host name'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
I know that most junior and middle-level Java engineers want to improve their skills, often by groping for growth or signing up for classes, but for training institutions, the tuition fee is nearly 10000 yuan, which is really a lot of pressure. The self-study effect that is not systematic is inefficient and long, and it is easy to encounter the stagnation of ceiling technology!
Therefore, I have collected and sorted out a complete set of learning materials for Java development and sent it to you. The original intention is also very simple, that is, I hope to help friends who want to improve themselves by self-study but do not know where to start, and at the same time reduce everyone's burden.
Xiaobian has been encrypted: ahr0chm6ly9kb2nzlnfxlmnvbs9kb2mvrvrvm9asgxqzuvstlkwunc = = for security reasons, we have encoded the website through base64. You can get the website through base64 decoding.