SQL overview

Time:2020-10-28

Database XX language: DDL, DML, DQL, DCL

1 database

1.1 connecting to the database

Command line connection

mysql -uroot -p

update  mysql.user  Set password = password ('xxx ') where user ='root' and host ='localhost '; -- modify user password
Flush privileges; -- refresh permissions
-----------------------------------------------

show tables;
Description Table

Exit; -- exits the connection

1.2 operation database

Create database -- name of database
Drop database [if exists] database name -- delete
Use database name -- use / switch if the database name is a keyword, add``
Show databases; -- View

Data type:

Value:

  • Integer: tinyint 1b, smallint 2B, int 4b, bigint 8B
  • Floating point: float 4b, double 8b, decimal

character string

  • Char fixed size 0 ~ 255,
  • Varchar variable 0 ~ 65535
  • Tiny text 2 ^ 8 – 1
  • Texttext string 2^16-1

Time and date

  • date YYYY- MM- DD
  • time HH: mm: ss
  • datetime YYYY- MM – DD HH: mm: ss
  • Timestamp timestamp, milliseconds since January 1, 1970
  • Year

NULL

Each table must have the following five fields:

  • ID primary key
  • `Version ‘optimistic lock
  • Is_ Delete pseudo delete
  • gmt_ Create creation time
  • gmt_ Update modification time

Table 2

2.1 create table

CREATE TABLE [IF NOT EXISTS] `student`{
    `id` INT(4) [NOT NULL AUTO_ Increment comment 'student number'],
    ...
    PRIMARY KEY(`id`)
}[ENGINE=INNODB DEFAULT CHARSET=utf-8]

View create statement

Show create database school -- View create database statements
Show create table student -- View CREATE TABLE statement
Desc student -- display table structure

Type of data table

InnoDB (default)

MyISAM (early use)

MYISAM INNODB
Transaction support I won’t support it support
Data row locking I won’t support it support
Foreign key constraint I won’t support it support
Full text index support I won’t support it
Table space size less Larger, about 2 times

Routine operation:

  • MyISAM: save space and speed
  • InnoDB: high security, transaction processing, multi table and multi-user operation

All the database files are stored in the data directory, and the essence is file storage

Differences between MySQL engine and physical files

  • InnoDB has only *. Frm file in database table and ibdata1 file in superior directory
  • MyISAM corresponding file

    • *. frm – definition file for table structure
    • *. MyD data file (data)
    • *. MYI index file

2.2 modify and delete table

Alter

Alter table teacher rename as teacher1 -- modify table name
Alter table teacher1 add age int (11) -- add field

--Modify the fields of a table
Alter table teacher1 modify age varchar (11) -- modify constraints
Alter table teacher1 change age age1 varchar (1) -- rename

--Delete a field from a table
ALTER TABLE teacher1 DROP age1

delete

--Delete table
DROP TABLE [IF EXISTS] teacher1

3. MySQL data management

3.1 foreign keys

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

The above operations are physical foreign keys, database level foreign keys, not recommended to use, to avoid too many databases causing trouble

3.2 DML language

Database meaning: data storage, data management

add to:Insert into table name [(field 1,2,3...)] values (value 1,2,3...) [, (), ()...]

Revision:Update table name set field 1 = value 1 [, field 2 = value 2...] where condition

Delete:Delete from table name where condition

Truncate table name: completely empty a database table, the table structure and index constraints will not change!

The truncate is reset and the column is incremented automatically. The counter will return to zero without affecting the transaction

WHERE clause

=, >, <, < =, > = < > or! = (not equal), between X and Y ([x, y])

AND, OR

Note: if no condition is written, all records of the table will be modified!

4 DQL query data

DQL:Data QueryLanguage Data query language

Select field [as alias] from table [as alias]

function

concat(a,b)

De duplication distinctSelect distinct field

Expressions that can be selected: text value, column, null, function, evaluated expression, system variable

4.1 fuzzy query

is null,like, in

--Inquire about the student surnamed Liu
--Like combined with% represents 0 to any character_ Represents a character
select `StudentNo`,`StudentName` from `student`
Where studentname like 'Liu%'

--Query student No. 100110021003
select `StudentNo`,`StudentName` from `student`
where StudentNo in (1001,1002,1003)

4.2 joint table query

Where equivalent query

Join on join query

SQL overview

Table search ideas:

  1. Analysis requirements: which tables are the fields of the analysis query from
  2. Determine which join queries to use (7)
  3. Determine the intersection (judgment condition: student.studentNo = grade.studentNo )
--Query missing students
select s.StudentNo,studentName,SubjectNo,StudentResult
from student s
left join result r
on s.studentNo = r.studentNo
where StudentResult is null

Self linking

Core: split a table into two identical tables

Select A. ` CategoryName ` as' parent column, B. 'CategoryName ` as' sub column'
from `category` as a,`category` as b
where a.`categoryid` = b.`pid`

4.3 paging and sorting

limit & order by

Sort: ascending ASC descending desc

--Paging: starting value, page size
limit start(0-),size
Limit 0,5 - Page 1
Limit 5,5 - Page 2
...
Limit (n-1) * PageSize, PageSize -- page n

4.4 nested query

Select field from table

Where (subquery)

--From inside to outside
select StudentNo,StudentName from student where StudentNo in (
    select StudentNo from result where StudentResult>80
    and SubjectNo = (
        Select subjectno from 'subject' where 'subjectname' ='advanced mathematics-2 '
    )
)

5 MySQL functions

Official website:https://dev.mysql.com/doc/ref…

5.1 common functions

--Mathematical function
select abs(-8) -- 8
select ceiling(9.4) -- 10
select floor(9.4) -- 9
select rand() -- 0~1
select sign(-10/0/10) -- -1,0,1

--String function
select char_length('xxxxx')
select concat('a','b','c')
select insert('xxx',pos,length,'yyy')
select lower('XXX')
select upper('xxx')
Select instr ('xyz ','x') -- returns the index of the first occurrence of a substring
select replace('abc','b','d')
select substr('abcdefg',pos,length)
select reverse('cba')

--Check the student surnamed Zhou and replace it with Zou
Select replace (studentname,'zhou '' Zou ') from student
Where studentname like 'week%'
               
--Time and date functions (remember)
select current_date()
select curdate()
Select now() -- current time
select localtime()
select sysdate()

--System
select system_user()    
select user()
select version()

5.2 aggregate function

Select count from table -- ignore null value, have primary key, count (field) query efficiency is high
Select count (*) from table
Select count (1) from table
Select sum (` studentresult ') as sum from result
Select AVG (` studentresult ') as average score from result
Select max (` studentresult ') as the highest score from result
Select min (` studentresult ') as the lowest score from result

--Grouping: query the average score, highest score and lowest score of different courses
Select subjectname, AVG (studentresult) as average score, max (studentresult) as the highest score, min (studentresult) as the lowest score
from result r
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
Group by r.subjectno -- group field
Having average score > 80

5.3 MD5 encryption

Irreversibility

The principle of cracking: violence cracking, try one by one

MD5(pwd)

The password passed in by the user is encrypted with MD5, and then the encrypted value is compared

6 affairs

Put a set of SQL into a batch for execution

Principle: acid (atomicity, consistency, isolation, persistence)

Atomicity

Either they succeed or they fail

Consistency

The data integrity before and after the transaction should be consistent

Isolation

Things should be isolated from each other

Durability

Once the transaction is committed, it is irreversible and persistent in the database

Problems caused by isolation: dirty read (read uncommitted data from another transaction), non repeatable read (multiple reads with different results), phantom read (reading data inserted by other transactions)

--MySQL enables transaction auto commit by default
Set autocommit = 0 -- close
Set autocommit = 1 -- on (default)

--Handle transactions manually
set autocommit = 0
--Transaction open
start transaction
insert XXX
insert yyy
--Submit
commit
--Roll back
rollback
--End of transaction
set autocommit = 1

--Understanding
Save point
rollback to Save point
release Save point

7 index

The official definition of index in MySQL is: index is a data structure to help MySQL obtain data efficiently. By extracting the sentence trunk, we can get the essence of index: index is data structure.

7.1 classification of indexes

  • Primary key
  • Unique key
  • General index (key / index)
  • Full text index
--Use of index
--1. Add indexes to fields when creating tables
--2. After creating, add the index

--Displays all index information
Select index from table name

--Add a full-text index
Alter table database. Table name add fulltext index index name (field name)

--Explain to analyze the status of SQL execution
Explain select * from student -- non full text index
Explain select * from student where match (studentname) against ('liu ')

7.2 test index

Insert 1 million data test indexes

--Insert 1 million pieces of data
Delimiter $$$-- must be written before writing function, flag

create function mock_data()
returns int
begin
    declare num int default 1000000;
    declare i int default 0;
    while i<num do
        --Insert statement
        insert into app_ User ('name ','email','phone ','gender','password ','age') values (concat ('user ', I),' [email protected] ',concat('18',floor(rand()*(999999999-100000000))+100000000),floor(rand()*2),uuid(),floor(rand()*100));
        set i = i+1;
    end while;    
    return i;
end;
select mock_data();

--Test index
select * from app_ User where `name'=' user 9999'; -- 0.0993 sec
explain select * from app_ User where ` name '='user 9999'; -- Query 990000 pieces of data

--Create a normal index
--Create index index name on table (field)
--Index name: ID_ Table name_ Field name
create index id_app_user_name on app_user(`name`);

select * from app_ User where ` name '='user 9999'; -- 0.001 sec
explain select * from app_ User where ` name '='user 9999'; -- Query 1 piece of data

Summary: indexes are not useful when there is a small amount of data, but when it comes to big data, the difference is very obvious

7.3 indexing principles

  • The more indexes, the better
  • Do not index frequently changing data
  • A table with a small amount of data does not need to be indexed
  • An index is usually added to a field that is frequently queried

Data structure of index

Hash type index

BTREE: InnoDB default

http://blog.codinglabs.org/ar…

8 rights management and backup

8.1 authority management

SQL command operation: the essence of SQL command operation is user table mysql.user Make changes

--Create user
create user xxx identified by '123456'
--Change password
set password [for xxx] = password('123456')
--Rename
rename user xxx to yyy
--User authorization library. Table
Grant all privileges on *. * to XXX
--Query authority
show grants for xxx
show grants for [email protected]
--Revocation of authority
revoke all privileges on *.* from xxx
--Delete user
drop user xxx

8.2 MySQL backup

Objective: to ensure that data is not lost and transferred

Methods:

  • Copy physical files directly
  • Manual export in a visualization tool like Navicat
  • Exporting mysqldump from the command line
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

#Import
#In the case of login, switch to the specified database
#Source backup file
source d:/a.sql

MySQL - U user name - P password database name < backup file

9 standard database design

Database design: analysis requirements → outline design (E-R diagram)

Three paradigms:

  • First normal form (1NF): each column of the database table is required to beinseparableAtomic data item for.

    1NF ensures that each column cannot be subdivided

  • The second normal form (2NF): non code attributes must satisfy 1NFabsolute dependenceOn candidate codes (eliminating partial functional dependence of non primary attributes on principal codes based on 1NF)

    2NF ensures that each table describes only one thing

  • The third normal form (3NF): under the premise of 2NF, any non principal attribute does not depend on other non principal attributes (on the basis of 2NF)Eliminating transitive dependency

    3NF guarantees that each column of data in the table is directly related to the primary key

Specification and performance issues:

There must be no more than three tables associated with the query

  • Considering the needs and objectives of commercialization (cost, user experience), the performance of the database is more important
  • When it comes to specification performance, it is necessary to consider the normalization properly
  • Intentionally adding redundant fields to some tables. (multi table query → single table query)
  • Intentionally adding some calculation columns (query with large amount of data → query with small amount of data)

10 JDBC

Java operation database

The program operates the database through the database driver

You need to import a database driver package: mysql-connector-java-x.x XX.jar

public class JDBCFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. Load driver
        Class.forName (" com.mysql.cj . jdbc.Driver "); // fixed write method, auto load DriverManager.registerDriver (new  com.mysql.jdbc ,Driver());
        //2. User information and URL
        String url = " jdbc:mysql // localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true "; // protocol( jdbc:mysql ): // host address: port number / database name? Parameter
        String username = "root";
        String password = "1031";
        //Connection, connection object 3
        Connection connection = DriverManager.getConnection(url,username,password);
        //4. Statement executes SQL object
        Statement statement = connection.createStatement();
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql);
        //5. View the returned results
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("=================");
        }
        //6. Release the connection, consume resources, and turn off when used up
        resultSet.close();
        statement.close();
        connection.close();
    }
}

Java uses JDBC to connect to the database

  1. Load driver
  2. Connect to database drivermanager
  3. Get the object statement of executing SQL
  4. Gets the returned result set
  5. Release the connection

Statement & ResultSet

Connection connection = DriverManager.getConnection(url,username,password);
Statement statement = connection.createStatement();
String sql = "select * from users";
ResultSet resultSet =  statement.executeQuery (SQL); // the query operation returns resultset
statement.execute (); // execute any SQL
statement.executeUpdate (); // update, insert, delete, and return the number of affected rows

// ResultSet
resultSet.getObject ("Id") // do not know the return type
resultSet.getString ("Id") // know the return type
//Traverse pointer (cursor)
resultSet.next () // move to next line
resultSet.beforeFirst () // move to front
resultSet.afterLast () // move to last
resultSet.previous () // move to previous line
resultSet.absolute (row) // move to the specified line

10.1 encapsulating JDBC( JDBCUtils.java )

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try{
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1. The driver only needs to be loaded once
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //Get connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //Release the connection
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The test uses JDBC encapsulation class to operate the database

import com.zhg.lesson02.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(4,'zhg','123456',' [email protected] ','2020-01-01') "; // to delete or modify, just change the SQL statement
            int i = statement.executeUpdate(sql);
            if(i > 0){
                System.out.println ("inserted successfully! "";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,resultSet);
        }
    }
}
import com.zhg.lesson02.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String SQL = "select * from users"; // to delete or modify, you only need to change the SQL statement
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("name"));
                System.out.println("pwd="+resultSet.getObject("password"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birthday="+resultSet.getObject("birthday"));
                System.out.println("=================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

10.2 SQL injection

SQL injection means that the web application does not judge the validity of the user input data or filter it not strictly. Attackers can add additional SQL statements at the end of the query statements defined in advance in the web application program, and realize illegal operation without the administrator’s knowledge, so as to cheat the database server to execute unauthorized arbitrary query, so as to obtain further results Corresponding data information.

import com.zhg.lesson02.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLInjection {
    public static void main(String[] args) {
        Login ("ZHG", "123456"); // log in normally
        Login ("'or'1 = 1", "'or'1 = 1"); // SQL injection: splicing SQL statements with or
    }
    //Login service
    public static void login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String SQL = "select * from users where 'name' = '+ username +"'and' password '=' + password + "'; // to delete or modify, just change the SQL statement
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("name"));
                System.out.println("pwd="+resultSet.getObject("password"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birthday="+resultSet.getObject("birthday"));
                System.out.println("=================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

10.3 Preparedstatement object

Preparedstatement prevents SQL injection

import com.zhg.lesson02.utils.JDBCUtils;

import java.sql.*;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            //Use the? Placeholder instead of the parameter
            String SQL = "insert into users (ID, ` name ', ` password', ` email ', ` birthday') values (?,?,?,?)"; // to delete or modify, you only need to change the SQL statement
            statement =  connection.prepareStatement (SQL); // for precompiled SQL, write SQL first and then do not execute it
            //Assign parameters manually
            statement.setInt(1,5);
            statement.setString(2,"hugh");
            statement.setString(3,"123456");
            statement.setString(4,"[email protected]");
            //Note: sql.Date     Database NEW java.sql.Date (timestamp)
            //         util.Date    Java new date(). Gettime() gets the timestamp
            statement.setDate(5,new java.sql.Date(new Date().getTime()));
            int i = statement.executeUpdate();
            if(i > 0){
                System.out.println ("inserted successfully! "";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

Preparedstatement can prevent the essence of SQL injection: the parameters passed in are treated as characters. If there is an escape character, it will be escaped directly

10.4 database connection pool

Database connection → execution → release is a waste of system resources

Pooling Technology: prepare some pre prepared resources and connect them when they come

Write the connection pool and implement the interface datasource

Implementation of open source data source

DBCP

C3P0

Druid: Ali

After using these database connection pools, we don’t need to write database code in project development!

DBCP, jar package required: commons-dbcp-1.4, commons-pool-1.6

C3p0, jar package required: c3p0-0.9.5.5, mcarge-commons-java-0.2.19

No matter what data source is used, the essence is the same. The datasource interface will not change, and the method will not change

Recommended Today

Blog based on beego, go blog

Go Blog A beego based development, can quickly create personal blog, CMS system Include functions see Official website of go bloggo-blog.cn Demo siteleechan.online Update log time function January 23, 2020 New top post function February 2, 2020 New custom navigation function February 4, 2020 New site announcement function February 6, 2020 New link module February […]