Database review

Database Overview

  • What is a database: a database is a warehouse for storing data; It is essentially a file system that stores data in a specific format. You can add, delete, change and check the data in the database through SQL statements.
  • Databases and tables: not explained.

The use of MySQL

  • MySQL installation (omitted on Windows and Linux)
    • Next step…
    • Start/stop the MySQL service command
      • Net start mysql
      • Net stop mysql

      • Log in to the mysql command: Go to the bin directory of the mysql installation directory, and enterMysql -u root -p Password

The SQL statement

  • What are SQL statements?
    • Structured Query Language.
    • International standard for relational databases.
    • Each database vendor supports ISO database standard: Mandarin.
    • Individual database vendors have made their own extensions to the standard: dialects.

Classification of SQL

DDL

  • Database Definition Language
  • Used to define database objects
    • The database
    • table
    • field
  • The keyword
    • create
    • drop
    • alter

DML

  • Database Manipulation Language
  • Used to update, delete, and insert table records in the database
  • The keyword
    • insert
    • delete
    • update

DQL

  • Datebase Query Language
  • Used to query records of tables in a database
  • The keyword
    • select
    • from
    • where

DCL

  • Database Control Language
  • Defines access permissions and security levels for a database
  • The keyword
    • grant

Database operations

Creating a database

  • Create database Specifies the database name.
  • Create database Database name character set;

Viewing a Database

  • View all databases in the database server:show databases;

  • View information about a database definition:Show create database Database name;

Deleting a Database

  • Drop database Specifies the database name.

Switching databases

  • Use database name;

View the database in use

  • To view the database in use:select database();

Table operation

Create a table

  • Single table constraints
    • Primary key constraint
      • primary key
      • Requires that the constrained field be non-empty and unique
    • The only constraints
      • unique
      • Requires that the bound field be unique
    • Not null constraint
      • not null
      • Requires the constrained field to be non-empty
  • Add foreign key constraints
    • The constraint constraint_name foreign key references (foreign key field name) the main table (foreign key field name) on delete action (restrict | cascade | set null | no action) on update action(restrict | cascade | set null | no action)
    • example
      • When creating indexes, you can specify RESTRICT, CASCADE, SET NULL, and NO ACTION that can be performed on child tables when the parent table is deleted or updated.

RESTRICT is the same as NO ACTION to RESTRICT that the parent table cannot be updated if the child table has associated records. CASCADE: When the parent table is updated or deleted, the records corresponding to the child table are updated or deleted. SET NULL: When the parent table is updated or deleted, the corresponding fields of the child table are SET NULL. For the two tables created above, the foreign keys of the subtables are set to ON DELETE RESTRICT ON UPDATE CASCADE. Therefore, when the primary table deletes records, the subtables cannot be deleted if they have corresponding records. When the primary table updates records, the subtables cannot be deleted if they have corresponding records. The sub-table is updated.

create table city_innodb(
 city_id int NOT NULL AUTO_INCREMENT,
 city_name varchar(50) NOT NULL,
 country_id int NOT NULL,
 primary key(city_id),
 key idx_fk_country_id(country_id),
 CONSTRAINT 'fk_city_country' FOREIGN KEY(country_id) 
 REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE 
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

See the table

  • Query all tables in the current database:show tables;

  • View table structure:Desc table name;

Delete table

  • Drop table table name;

Modify the table

  • Add field:Alter table table_name add table name type (length) constraint

  • Delete field:Alter table drop name;

  • Change field name:Alter table alter table name alter table name alter table name alter table name
    • To change the field name, always have an old field name and a new field name.

  • Modify field type (length) and constraint:Alter table table name modify field name Type (length) constraint;

  • Alter table character set;Alter table name character set;

  • Alter table name:Rename table name to new table name;

  • Note: This does not alter table, directly rename.

Insert table record

  • Insert statement
    • Insert into table name (columns 1, 2,...... , field n) values(value 1, value 2,...... And the value of n);
    • Insert into values(1, 2,...... And the value of n);
      • Values are written in the same order as when the table was created, and all fields are inserted.

    • Solve the problem of Chinese garble
      • Method 1: Modify the my.ini file in MySQL and restart the server (this method is not recommended).

      • Method 2: Enter set names GBK in the DOS window. Valid only for the current window;

  • Pay attention to the point
    • The field name corresponds to the following value one by one;
    • The value should be quoted if it is a string or a date (usually single quoted);

Update table record

  • statements
    • Update table_name set table_name = table_name;
      • This is an unconditional update, meaning that all records in the table are updated.

    • Select * from table_name where table_name = 1;
  • Pay attention to the point
    • The value must be quoted if it is a string or date.

Delete table record

  • statements
    • Delete from table name;
      • This is an unconditional delete, which means that all records in the table are deleted

    • Delete from table name where condition;

  • Delete from table name and TRUNCate table name delete records.
    • Delete the way
    1. Delete indicates that the number of auto_INCREMENT records is deleted one by one without clearing the auto_increment records.
    2. Truncate deletes the entire table, then creates a new table with the same value, and sets the auto_INCREMENT record to zero, and starts counting again.

    • affairs
    1. If a transaction is started, records deleted using DELETE can be recovered by rollback.

2. Records deleted using TRUNCATE cannot be restored by rollback.

  • Pay attention to
    • Truncate TABLE name;In this way, you cannot add the WHERE condition, but can only delete all.

Query table records

  • A simple query
    • Statement:Select distinct (field name) | | * field name 1, 2, the field name... Select * from table_name where table_name [select * from table_name]
    • Query all goods
      • Select * from table_name;

    • Query the product name and price
      • select pname,price from product;

    • The alias query
      • Table alias
        • select * from product [as] p;

      • Column alias
        • select pname [as] p1,price [as] p2 from product;

      • Note:As in an alias can be omitted, usually.
    • Remove duplicate value queries
      • Select distinct from table name;

    • Arithmetic query (the query result is an expression)
      • Example: all commodity prices plus 10 yuan display
      • select price+10 from product;

  • Conditions of the query
    • The operator
      • Comparison operator
        • More than:>
        • Greater than or equal to:> =
        • Less than:<
        • Less than or equal to:< =
        • Is equal to:=
        • Is not equal to:<>
        • Values in a range (including head and tail) :between a and b
        • Values in the IN list:In (23,34,45)
        • Fuzzy query:like
          • A placeholder
            • _: Indicates a character
            • %: represents any character (can be 0 characters)
        • Check whether it is empty:is null
      • Logical operator
        • and: Multiple conditions are valid simultaneously
        • or: Any one of multiple conditions is true
        • not: The condition is not valid.
          • select * from product where not (price>10): Queries the product information whose price is greater than 10.
    • example

The sorting

  • Select * from table name [] where clause for the order by the field name asc | desc
  • Asc is incremental sort; Desc is in descending order.
  • Pay attention to the point
    • Order must be written after the WHERE condition statement
    • If you do not write ASC or DESC, the default is ascending order

Aggregation function

  • Common aggregate functions
    • Sum (field name): the sum
    • Avg (field name): Take the average
    • Count (field name): Calculates the number. Count () can be either the field name or the *
  • Note:Aggregate functions do not count null values.

grouping

  • Select * from table_name where table_name group by table_name having table_name;
  • Group criteria must be written after HAVING, not where, where is the query criteria.
  • example
    • Group the commodities according to the CID field, and count the number of commodities after grouping:select cid,count(*) from product group by cid;

    • According to cid grouping, the average price of each group is calculated, and the average price is more than 20000 yuan:select cid,avg(price) from product group by cid having avg(price) > 20000;
      • Note: “and average price > 20,000 yuan” is a grouping condition, not a query condition.

Query Statement Summary

  • Select * from table name where the query conditions group by the field name having order grouping conditions by the field name asc | desc;

  • Always write order by at the end.
  • Grouping and sorting are written after where (grouping, sortingSort at the end)

JDBC

JDBC is introduced

  • Full name: Java DataBase Connectivity
  • JDBC is a Java API for executing SQL statements.
  • JDBC provides a unified access point for multiple relational databases.
  • JDBC consists of a set of Java utility classes and interfaces.

JDBC theory

  • SUN provides the access database specification as JDBC, and manufacturers provide the implementation class of the specification as the driver.

JDBC Development Steps

  1. Registration drive
  • Class.forName("com.mysql.jdbc.Driver");This is the recommended way of writing
  • DriverManager.registerDriver(new com.mysql.jdbc.Driver())This writing is not recommended because it has disadvantages
    • If you use the Class. ForName () method, the parameters inside the parentheses are only read from the configuration file. You only need to modify the configuration file.
    • 02. This method is registered twice because the com.mysql.jdbc.driver class has a static code block that registers the Driver again.

  1. Obtain a connection
  • Connection conn=DriverManager.getConnection(url,username,password);

3. Write SQL statements and obtain the executor

  • Write SQL statements:

  • The Statement object is used to execute SQL statements:
    • Statement stmt=conn.createStatement();

  1. Execute SQL statement
  • int executeUpdate(String sql);: Performs insert, update, and delete operations and returns the number of affected rows
  • ResultSet executeQuery(String sql);: Performs the query operation and returns the queried result set
  • boolean execute(String sql);:
    • Return true from the query operation: If true is returned, ResultSet getResultSet() is used to obtain the query result
    • Insert, update, delete returns false: if false, int getUpdateCount() is used to get the number of affected rows
    • Generally this method is used less, use the first two more.
  1. The processing results
  • A ResultSet is actually a two-dimensional table with a “cursor” in it. Initially, the “cursor” is above the first row. You can use the next() method to move the “cursor” to the next row. The first time the next() method is called, the “cursor” moves to the first line. You can get the data for the current row using the getXXX() method.
  • boolean next()This method returns Boolean data, false if the traversal reaches the end of the line, true otherwise.
  • Gets the data for the current row
    • The “parameter” in the getXXX(parameter) method can be written in two ways
      • ColumnName:resultSet.getInt("cid");(Get data with field name cid)
      • The column in which the field resides (columnIndex) :resultSet.getInt(1);(Obtain the first column of the current row, columnIndex starting from 1)
      • The common getXXX() method
        • Object getObject(String columnName | int columnIndex)GetObject () returns an Object if you don’t know what type of data is being retrieved.
        • int getInt(String columnName | int columnIndex)You can use this method if you know the data to fetch is of type int. Be sure to get data of int type, same as below.
        • String getString(String columnName | int columnIndex)You can use this method if you know the String type of the retrieved data.
        • double getDouble(String columnName | int columnIndex)You can use this method if you know the type double when retrieving data.
  1. Release resources
  • As with IO streams, used resources need to be closed
  • The principle of releasing resources is: first get, then close, and then get, first close.
  1. JDBC database query data complete writing method
@Test public void test1(){ Connection conn=null; Statement stmt=null; ResultSet resultSet=null; Try {// Register Driver class.forname (" com.mysql.jdbc.driver "); / / the connection conn = DriverManager. GetConnection (" JDBC: mysql: / / localhost: 3306 / w eb08 ", "root", "pgmx0835"); String SQL ="select * from category"; stmt=conn.createStatement(); resultSet = stmt.executeQuery(sql); While (resultSet.next()){// Obtain the query data Integer cid= resultSet.getint (1); String cname=resultSet.getString(2); System.out.println("cid="+cid+",cname="+cname); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); }finally{try {// Release resources // Note: Release resources in reverse order of creation if (resultSet! = null) { resultSet.close(); } if(stmt! =null){ stmt.close(); } if(conn! =null){ conn.close(); } }catch(SQLException e){ e.printStackTrace(); }}}Copy the code

SQL Injection Problems

  • Implementation of Login Function
    • Implementation principle: Query the database based on the name and password. The query condition is that the name and password are both name and password. If the query is found, the login succeeds; otherwise, the login fails.
    • Implementation method:
      • Methods a
        • Use string concatenation to concatenate name and password into SQL statements.
        • Implementation code:

        • Problems with this approach: If the name passed in has an OR, the login may also succeed; That is, there is no user name for the name argument passed in.

        • Workaround: Use a prepared PreparedStatement (see Option 2)
      • Way 2
        • How to implement it: Prepare preparedStatements, use placeholders when writing SQL statements, and then set values for parameters to solve the above problems.
        • The implementation code

        • PreparedStatement summary
          • 01. Create PreparedStatement:PreparedStatement prepareStatement(String sql)When creating, pass in the SQL statement because it will be preprocessed.

          • 02. Write SQL statements: After using preprocessing, write SQL statements using placeholders.

          • 03. Setting parameters: To set parameters because placeholders are used,setXXX(int parameterIndex,Object value)

          • 04. Execute SQL statements: Execute SQL statements in the same way as Statement statements except that you do not need to pass in the SQL Statement.

limit[m],n

  • Parameter interpretation
    • M: m indicates the number of records to start the query. Note that the index starts from 0. For example, if m is 0, the query starts from the first record. The m argument is optional, that is, write or not write. If m does not write, the query starts from the first record by default.
    • N: n indicates to query several records. For example, if n is 3, three records are queried.
  • Example:Select * from table_name limit 2,5;: indicates that five records are queried starting from record 3. That is, query records 3,4,5,6,7.