Database foundation
1. Three paradigms of database
1. No duplicate columns
2. Each instance or row in the data table must be uniquely distinguished
3. One data table does not contain the non primary keyword information already in other data tables
2.SQL language
2.1 general
SQL: Structure Query Language
SQL is a standardized language that allows you to perform operations on the database, such as creating projects, querying content, updating content, and deleting entries.
Create, Read, Update, and Delete are commonly referred to as CRUD operations.
2.2SQL statement classification
DDL (Data Definition Language): Data Definition Language, which is used to define database objects: libraries, tables, columns, etc.
DML (data management language): data operation language, which is used to define the addition, deletion and modification of database records (data).
DCL (Data Control Language): Data Control Language used to define access rights and security levels.
DQL (Data Query Language): a Data Query Language used to query records (data).
be careful:
1.sql statement; ending
2. The keywords in MySQL are not case sensitive
2.3 DDL operation (Definition)
2.3.1DDL operation database
F:\mysql\bin>mysql -u root -p//Connect to the database and enter the password mysql> create database mydb1;//Create database mydb1 mysql> show databases;//Display database +--------------------+ | Database | +--------------------+ | homework | | information_schema | | mydb1 | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.03 sec) mysql> drop database mydb1;//Delete database mysql> use sys;//Connect to a database mysql> select database();//View the database currently in use
2.3.2DDL operation table
The CREATE TABLE statement is used to create a new table
Syntax:
CREATE TABLE table name(
Column name 1 data type [constraint],
Column name 2 data type [constraint],
Column name n data type [constraint]
);
be careful:
The table name and column name are user-defined, but the data type should be careful;
Multiple columns are separated by commas, and the comma of the last column is not written; [constraints] optional.
Common data types:
int: integer
Double: floating point type. For example, double(5,2) represents a maximum of 5 digits, in which there must be 2 decimal places, that is, the maximum value is 999.99; Rounding is supported by default
char: fixed length string type; char(10) 'aaa' occupies 10 places
varchar: variable length string type; varchar(10) 'aaa' occupies 3 places
text: string type, such as novel information
blob: byte type, saving file information (video, audio, picture)
Date: date type, format: yyyy mm DD
Time: time type in the format of: hh:mm:ss timestamp: timestamp type yyyy MM DD HH: mm: ss will be assigned automatically
datetime: date time type yyyy MM DD HH: mm: SS
mysql> create database yhp;//Create database yhp mysql> use yhp;//Connect to database yhp mysql> create table student(name varchar(5),age int,sex char(1)); //Create the data table student and initialize its field properties mysql> show tables;//Presentation data sheet +---------------+ | Tables_in_yhp | +---------------+ | student | +---------------+ 1 row in set (0.32 sec) mysql> desc student;//View the field information of the table +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | name | varchar(5) | YES | | NULL | | | age | int | YES | | NULL | | | sex | char(1) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> drop table student;//Delete data table mysql> show tables;//Presentation data sheet Empty set (0.00 sec)//Display empty after deletion mysql> alter table student add image blob; //Add a field in the data table student: Avatar iamge mysql> alter table student change name studentname varchar(10); //Modify the name field in the data table student to change the name to studentname mysql> alter table student drop image; //Discards the image field in the table student mysql> alter table student rename user; //Change the table student name to user mysql> show create table user;//View table creation details +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `studentname` varchar(10) DEFAULT NULL, `age` int DEFAULT NULL, `sex` char(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.29 sec)
2.4 DML operation
DML is used to add, delete and modify data in a table. Don't confuse it with DDL.
It mainly includes INSERT, UPDATE and DELETE
Tips:
In mysql, both string type and date type, that is, non numeric type, should be enclosed in single quotation marks.
Null value: null
2.4.1DML_insert
Syntax: insert into table name (column name) values (data value);
tips:
1 multiple columns and column values are separated by commas
2. The column name should correspond to the column value one by one
3. The column name can be omitted when adding data
4. When adding data to all columns, the sequence of column values is executed according to the sequence of columns in the data table
Examples where column names can be omitted:
insert into student values('li Si ', 12,' 1111 ', 189.98,' 2000-1-1 ',' male ',' 2007-1-1 ');
2.4.2DML_update
Chestnuts: update emp set salary=5000; //Revise the salary of all employees to 5000 yuan. update emp set salary=3000 where name='zs'; //Revise the salary of the employee named 'zs' to 3000 yuan. update emp set salary=4000,resume='ccc' where name ='aaa'; //The salary of the employee named 'aaa' is revised to 4000 yuan, and resume is changed to ccc. update emp set salary=salary+1000 where name='wu'; //Increase wu's salary by 1000 yuan on the original basis.
2.4.3DML_delete
Syntax: DELETE from table name [WHERE column name = value]
delete from emp where name='zs';//Delete the record named 'zs' in the table mysql> select * from emp;//Displays the specific information in table emp +----+----------+--------+------------+----------+------------+----------+ | id | name | gender | birthday | salary | entry_date | resume | +----+----------+--------+------------+----------+------------+----------+ | 1 | zhangsan | female | 1990-05-10 | 10000.00 | 2015-05-05 | goodgirl | | 2 | lisi | male | 1995-05-10 | 10000.00 | 2015-05-05 | good boy | | 3 | Hello | male | 1995-05-10 | 10000.00 | 2015-05-05 | good boy | +----+----------+--------+------------+----------+------------+----------+ 3 rows in set (0.00 sec) mysql> delete from emp;//Delete information from table emp Query OK, 3 rows affected (0.39 sec) mysql> select * from emp;//The specific information in the table emp is displayed. If it is deleted, empty is displayed Empty set (0.00 sec) mysql> show tables;//Presentation data sheet //delete from emp; --> The information in the table is deleted, and the table emp is still in +---------------+ | Tables_in_yhp | +---------------+ | emp | +---------------+ 1 row in set (0.00 sec) mysql> truncate table emp;//Another deletion method other than delete Query OK, 0 rows affected (1.29 sec) mysql> show tables;//Presentation data sheet //truncate table emp; --> DROP the table directly, and then create the same new table //The table structure is still there, and the deleted data cannot be retrieved. The execution speed is faster than DELETE. +---------------+ | Tables_in_yhp | +---------------+ | emp | +---------------+ 1 row in set (0.00 sec)
Comparison of two deletion methods
- DELETE deletes the data in the table, and the table structure is still in use; The deleted data can be retrieved
- TRUNCATE deletion is to DROP the table directly, and then create a new table with the same table structure. Deleted data cannot be retrieved. The execution speed is faster than DELETE.
2.5DQL data Query
Syntax:
SELECT column name FROM table name [where -- > group by – > having – > order by]
- Represents all columns
SELECT the name of the column to query
FROM table name
WHERE qualification / line condition/
GROUP BY grouping_columns / group results/
HAVING condition / line condition after grouping/
ORDER BY sorting_columns / group results/
LIMIT offset_start, row_count / result limit/
Database advanced
1. Data integrity
1.1 database integrity
Ensure data integrity = add constraints to the table when creating the table
- Entity integrity (row integrity):
- Domain integrity (column integrity):
- Referential integrity (associated table integrity):
Primary key constraint: primary key
Unique constraint: unique [key]
Non NULL constraint: not null
Default constraint: default
Auto growth: auto_increment
Foreign key constraint: foreign key
tips:
It is suggested that these constraints should be set when creating tables; Use spaces between multiple constraints.
1.2 physical integrity
Entity: that is, a row (a record) in the table represents an entity
Entity integrity: each row of data identified is not duplicate.
1.2. 1 primary key constraint
Each table must have a primary key. The data is unique and cannot be null.
eg.CREATE TABLE student( id int primary key, name varchar(50) );
1.2. 2 unique constraint
Data cannot be duplicated.
eg.CREATE TABLE student( Id int , Name varchar(50) unique);
1.2. 3 auto_increment
Add auto growing value to the primary key. The column can only be of integer type
eg.CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
1.3 domain integrity
Function of field integrity: restrict the correctness of data in this cell and do not compare with other cells in this column
Field represents the current cell
Domain integrity constraints:
data type
Non NULL constraint (not null)
Default value constraint (default)
Check constraint (not supported by mysql) check(sex = 'male' or sex = 'female')
1.4 referential integrity
FOREIGN KEY constraint: FOREIGN KEY
The data type of the foreign key column must be consistent with that of the primary key
eg.ALTER TABLE modified table name ADD CONSTRAINT custom foreign key constraint name foreign key (foreign key column) REFERENCES associated table name (primary key column of associated table);
Foreign key column: a column that stores the primary key values of other tables
*All the previous statements can be operated with the mouse in Navicat without memory
2. Multi table query
There is a relationship between multiple tables. Who maintains the relationship?
Multi table constraints: foreign key columns
2.1 multi table relationship
2.1. 1 one to many / many to one relationship
Customers and orders, categories and commodities, departments and employees
One to many table creation principle: create a field on the multiple side, and the field is used as the primary key of the foreign key pointing to one side
2.1. 2 many to many relationship
Students and courses
Many to many table building principle: the third table needs to be created. There are at least two fields in the middle table. These two fields are used as foreign keys to point to the primary key of each party
2.1. 3 one to one relationship
It is rarely used in actual development, because one-to-one can be created into a table.
Two table building principles:
1. Unique foreign key correspondence: assuming that one-to-one is a one to many relationship, create a primary key of the party whose foreign key points to one on the many side, and set the foreign key to unique
2. Primary key correspondence: allow one-to-one primary keys of both parties to establish a relationship
2.2 multi table query
There are several types of multi table query:
- Merge result set: UNION, UNION ALL
- join query
2.1 inner join on
2.2 external join on
-Left outer join
-RIGHT [OUTER] JOIN
-FULL JOIN (not supported by MySQL)
2.3 NATURAL JOIN - Subquery
Natural query subquery is less used
2.2. 1 merge result set
Function: merge result set is to merge the query results of two select statements together!
There are two ways to merge result sets:
| UNION: remove duplicate records | SELECT* FROM t1 UNION SELECT * FROM t2； |
|---|---|
| UNION ALL: do not remove duplicates | SELECT * FROM t1 UNION ALL SELECT * FROM t2; |
Note: the number of columns and column type of the two merged results must be the same.
2.2. 2 connection query
Join query is to find the product of multiple tables. For example, t1 joins t2, and the query result is t1*t2.
Join query will generate Cartesian product. Assuming set A={a,b} and set B={0,1,2}, the Cartesian product of the two sets is {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}. It can be extended to multiple sets.
Remove duplicate and unwanted records through conditional filtering. Usually, there is an association relationship between multiple tables to be queried, so the Cartesian product is removed through the association relationship.
eg. use the primary foreign key relationship as a condition to remove useless information
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
eg. the above query results will query all the columns of the two tables. Maybe you don't need so many columns, then you can specify the columns to query
SELECT emp.ename,emp.sal,emp.comm,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;
| Inner connection | External connection |
|---|---|
| Query results must meet the conditions | The query results may not meet the conditions (generally, you need to query again later) |
2.2. 3 sub query
A select statement contains another complete select statement.
A subquery is a nested query, that is, a SELECT contains a SELECT. If there are two or more selections in a statement, it is a subquery statement.
Advanced database
1. Business acquaintance
Transaction is a program execution logic unit composed of a series of operations to access and update data in the system.
1.1 transaction syntax:
- start transaction; begin;
- commit; Make the current modification confirmed
- rollback; Make the current modification abandoned
1.2 ACID characteristics of transactions:
- Atomicity
The originality of a transaction means that the transaction must be an atomic operation sequence unit.
All operations contained in the transaction are either executed successfully or failed during the "second execution". After the transaction starts, all operations are either completed or not done. It is impossible to stagnate in the intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction. All operations are like no transaction. In other words, affairs are an inseparable whole, just like atoms learned in chemistry, which is the basic unit of matter. - Consistency
Transaction consistency means that the execution of a transaction cannot destroy the integrity and consistency of database data. Before and after the execution of a transaction, the database must be in consistency state.
Indicates that when an operation fails in a transaction, all changed data must be rolled back to the state before modification. - Isolation
Transaction isolation means that concurrent transactions are isolated from each other in a concurrent environment.
In other words, when different transactions operate on the same data concurrently, each transaction has its own complete data space. The operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other. Isolation is divided into four levels, which will be introduced below. - Persistence (Duration)
Transaction persistence means that once a transaction is committed, the data in the database must be permanently saved. Even if the server system crashes or the server goes down. As long as the database is restarted, it must be able to recover to the state after the successful completion of the transaction.
2. Transaction concurrency
2.1 dirty reading
Uncommitted data was read.
Transaction A reads the data updated by transaction B, and then transaction B rolls back the operation. Then the data read by transaction A is dirty data.
2.2 non repeatable reading
The same command returns different result sets (Updates).
Transaction A reads the same data many times, and transaction B modifies, updates and commits the data during the multiple reading of transaction A, resulting in inconsistent results.
###2.3 unreal reading
In the process of repeated query, the amount of data changes (insert, delete)
JDBC
Introduction:
JDBC (Java database connectivity) is a Java API for executing SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language.
JDBC provides a benchmark to build more advanced tools and interfaces so that database developers can write database applications.
Architecture:
JDBC practice:
public class Demo1 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp?serverTimezone=UTC";
connection = DriverManager.getConnection(url,userName,passWord);
//3. Define sql and create status channels (send sql statements)
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from emp2");
//4. Retrieve the result set information
while(resultSet.next()){//Judge whether there is next data
//Fetch data: resultset Getxxx ('column name '); XXX indicates the data type
System.out.println(
"full name:"+resultSet.getString("ename")
+"，Salary:"+resultSet.getDouble("sal")
+"，Date of employment:"+resultSet.getDate("hiredate"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5. Close resources
try {
if(resultSet != null){
resultSet.close();
}
if(resultSet != null){
statement.close();
}
if(resultSet != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
SQL injection (modify step 3)
//3. Define sql and create status channels (send sql statements)
statement = connection.createStatement();
String sid="S_1001";
String password=" '' or 1=1";
resultSet = statement.executeQuery(
" select * from stu where sid='"+sid+"' and password="+password);
//executeQuery (sql) executes the query
try {
if(resultSet.next()){
System.out.println("Login succeeded");
}else{
System.out.println("Login failed");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
Pre status channel (modify step 3, 4 and 5)
//3. Define sql and create pre status channels (send sql statements)
String sql ="select * from stu where sid = ? and password = ?";
pps = connection.prepareStatement(sql);
String sid="S_1001";
String password=" '' or 1=1";
//Assign a value (subscript, memory) to the placeholder, starting with 1
pps.setString(1,sid);
pps.setString(2,password);
//Execute sql
resultSet = pps.executeQuery();
try {
if(resultSet.next()){
System.out.println("Login succeeded");
}else{
System.out.println("Login failed");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
//4. Retrieve the result set information
while(resultSet.next()){//Judge whether there is next data
//Fetch data: resultset Getxxx ('column name '); XXX indicates the data type
System.out.println("full name:"+resultSet.getString("ename")
+"，Salary:"+resultSet.getDouble("sal")
+"，Date of employment:"+resultSet.getDate("hiredate"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5. Close resources
try {
if(resultSet != null){
resultSet.close();
}
if(resultSet != null){
pps.close();
}
if(resultSet != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
All parameters in JDBC are controlled by? Symbols, which are called parameter markers. You must provide a value for each parameter before executing the SQL statement.
The setXXX() method binds the value to the parameter, where XXX represents the Java data type to be bound to the value of the input parameter. If you forget to provide a value, you will receive an SQLException.
Each parameter tag is referenced by its sequential position. The first mark represents position 1, the next position 2, and so on. This method is different from Java array index, starting from 0.
Compare statement with PreparedStatement:
(1)statement belongs to the state channel and PreparedStatement belongs to the pre state channel
(2) The pre status channel compiles sql statements first and then executes them, which is more efficient than statement execution
(3) Pre status channels support placeholders?, When assigning a value to a placeholder, the position starts with 1
(4) The pre status channel can prevent sql injection. The reason is that the pre status channel processes values in the form of strings
dao layer, precompiled channel, transaction, batch processing, database connection pool