SQL statements are common command syntax for all relational databases, and JDBC API is just a tool to execute SQL statements. JDBC allows the same programming interface to execute SQL statements for different platforms and different databases

Relational database basic concepts and MySQL basic commands

A database is simply a place to store user data. When users access and manipulate data in the database, they need the help of the database management system. The full name of Database Management System is Database Management System, referred to as DBMS. The database and database management system is generally called the database, which usually includes the storage of user data, but also includes the management system of the database

DBMS is the knowledge base of all data, it is responsible for managing data storage, security, consistency, concurrency, recovery and access operations. A DBMS has a data dictionary (also known as a system table) that stores information about each transaction it owns, such as name, structure, location, and type. This data about the data is also known as metadata

In the history of database development, the following types of database systems mainly appeared in chronological order:

  • Network database

  • Hierarchical databases

  • Relational database

  • Object-oriented database

An instance of a MySQL database can contain multiple databases at the same time. MySQL uses the following command to check how many databases are contained in the current instance



show databases;Copy the code

Creating a new database



create database [IF NOT EXISTS] database name;Copy the code

Deleting a specified database



delete databaseDatabase name;Copy the code

Enter the specified database



useDatabase name;Copy the code

Query how many tables are contained under the database



show tables;Copy the code

View the table structure of the specified data table



Desc show;
Copy the code

MySQL databases generally support the following two storage mechanisms:

  • MyISAM: This was MySQL’s early default storage mechanism and wasn’t good enough for transactions

  • InnoDB: InnoDB provides transaction-safe storage. InnoDB storage mechanism. If you don’t want to use InnoDB tables, you can use the skip-InnoDB option

  • ENGINE = MyISAM — Force MyISAM

  • ENGINE = InnoDB — InnoDB is mandatory

SQL Statement Basics

Sructured Query Language is the full name of SQL. SQL is the standard language for manipulating and retrieving relational databases. Standard SQL statements can be used to manipulate any relational database

Using SQL statements, programmers and database administrators (DBAs) can accomplish the following tasks:

  • Retrieve information in the database

  • Update the database information

  • Change the structure of the database

  • Change the system security Settings

  • Add or revoke a user’s permission on a database or table

Standard SQL statements can be categorized into the following types:

  • Query statement: The query statement is mainly completed by the SELECT keyword. The query statement is the most complex and has the most rich functions in SQL statements

  • Data Manipulation Language (DML) statements: These statements are made by inset, UPDATE, and DELETE

  • Data Definition Language (DDL) statements: The DDL statements consist of create, ALTER, DROP, and TRUNCate keywords

  • Data Control Language (DCL) statements: The grant and REVOKE keywords are used

  • Transaction control statements: Commit, rollback, savepoint

Naming rules for identifiers:

  • Identifiers must usually start with a letter

  • Identifiers include letters, numbers, and three special characters (# _ $)

  • Do not use the keywords and reserved words of the current database system. It is usually recommended to use multiple words separated by _

  • Objects in the same schema should not have the same name. The schema here refers to the external schema

DDL statements

DDL statements are statements that manipulate database objects, including create, delete, and alter database objects

Several common database objects in a database

Syntax for creating tables



createTable [schema name.]It is possible to define multiple column definitions
    columnName1 datatype [default expr],...).Copy the code

Each column definition is separated by a comma (,). The last column definition does not need a comma, but ends with parentheses

The column name comes first and the column type comes after. If you want to specify a default value for a column, use the default keyword instead of the equal sign (=)

Subqueries are used to build table clauses that allow you to insert data while building a table. Table syntax for subqueries



create tableTable name [column[, cloumn...] ]as subquery;
Copy the code


Create premium_info table, which is exactly the same as user_INFO, with the same datacreate table premium_info
as
select * from user_info;
Copy the code

Modify table structure syntax

Modifying a table structure Using ALTER TABLE, modifying a table structure includes adding, modifying, deleting, and renaming columns. Most string values in SQL statements are enclosed in single quotes instead of double quotes



The alter table table nameadd
(
    You can have multiple column definitions
    column_name1 datatype [default expr] ,
    ...
)Copy the code

If you just add a column, you can omit the parentheses and simply follow the add with a column definition



Select * from premium_info where pre_id = int;
alter table premium_info
add pre_id int;
Select * from premium_info where name and duration are varchar(255);
alter table premium_info
add
(
name varchar(255) default 'Jimmy',
duration varchar(255))Copy the code

Modify the syntax of column definitions



alter tableThe name of the tablemodify column_name datatype [default expr] [first|after col_name];Copy the code

First or after col_name specifies that the target needs to be changed to the specified location. This change statement can change only one column definition at a time



Select * from premium_info where pre_id = varchar(255The alter) typetable premium_info
modify pre_id varchar(255); Alter premium_info duration column to int altertable premium_info
modify duration int;Copy the code

If you want MySQL to support changing multiple column definitions at once, you can use multiple modify commands after alter table

Syntax for deleting columns



alter tableThe name of the tabledrop column_name   
Copy the code


Alter table premium_info alter table premium_info
alter talbe premium_info
drop name;Copy the code

Deleting a column definition from a database usually succeeds, removing the column’s data from each row and freeing up the space that the column occupies in the data block. So deleting a field in a large table takes a long time because space needs to be reclaimed

MySQL has two special syntax: rename tables and completely change column definitions

Syntax for renaming data tables



alter tableThe name of the tablerename toThe new name of the tablealter table premium_info
rename to premium;Copy the code

Syntax for the change option



alter tableThe name of the tablechange old_column_name new_column_name type [default expr] [first|after col_name];
Copy the code


alter table premium_info
change duration time int;
Copy the code

Delete table syntax



drop tableThe name of the table.Copy the code


Alter table droptable premium_info;
Copy the code

The effect of deleting a table is as follows

  • The table structure is deleted and the table object no longer exists

  • All the data in the table is also deleted

  • All indexes and constraints related to the table are deleted

Truncate table syntax

Truncate is called “truncate” a table — deleting all data in the table while preserving the table structure. Compared with the DELETE command in DML, TRUNCate is much faster. Unlike delete, TRUNCate can only delete all records of the entire table at one time. Syntax of the truncate command:



truncateThe name of the tableCopy the code

Database constraints

Constraints are data verification rules enforced on tables. Constraints are mainly used to ensure the integrity of data in a database. In addition, when the data in the table is interdependent, the related data can be protected from deletion

Most databases support the following five types of integrity constraints:

  • NOT NULL: Specifies that a column cannot be NULL

  • UNIQUE: specifies that a column or combination of columns cannot be repeated

  • PRIMARY KEY: The PRIMARY KEY that specifies the value of the column that uniquely identifies the record

  • FOREIGN KEY: a FOREIGN KEY that specifies a record that belongs to the main table for referential integrity

  • CHECK: Specifies a Boolean expression that specifies that the value of the corresponding column must satisfy this expression

MySQL does not support the CHECK constraint

According to constraints on data columns, they can be divided into the following two categories:

  • Single-row constraints: Each constraint constrains only one column

  • Multi-row constraints: Each constraint constrains more than one data column

There are two opportunities to specify constraints on a data table:

  • Specify constraints for the corresponding data columns while building the table

  • Created after the table is created to modify the table to add constraints

NOT NULL constraint

A non-null constraint is used to ensure that a specified column cannot be null. A non-null constraint is a special constraint that can only be used as a column level

NULL values in SQL

  • The value of all data types can be null, including int, float, and Boolean

  • Similar to Java, empty strings do not equal null, and 0 does not equal null

To specify a non-null constraint:



create tableNull_test (# establishes a non-null constraint, which means user_id cannot be usednull
    user_id int not null# MySQL cannot specify the name user_name for a non-null constraintvarchar(255) default 'lin' not nullUser_location can be null by defaultvarchar(255) null
)

Copy the code

You can also add or remove non-null constraints when changing a table with ALTER TABLE:



# add a non-null constraint
alter table null_test
modify user_location varchar(255) not null;
Remove the non-null constraint
alter table null_test
modify user_name varchar(255) null;
Remove the non-null constraint and specify a default value
alter table null_test
modify user_location varchar(255) default 'Nantes' null;
Copy the code

The UNIQUE constraint

The uniqueness constraint is used to ensure that a specified column or combination of specified columns does not allow duplicate values, but multiple NULL values can occur (because null is not null in the database).

Unique constraints can be created using either the column-level constraint syntax or the table-level constraint syntax. If you need to build a composite constraint for multiple columns, or if you need to specify a constraint name for a unique constraint, you can only use table-level constraint syntax

When a unique constraint is created, MySQL creates a unique index on the column or column combination of the unique constraint. At least if no unique constraint is given, the unique constraint defaults to the same column name

Creating a unique constraint using the column-level constraint syntax is as simple as adding the unique keyword to the column definition



test_name varchar(255) unique
Copy the code

If you want to create a unique constraint for multiple column combinations, or you want to specify the constraint name yourself, you need to use the table-level constraint syntax. The table-level constraint syntax is as follows:



[Constraint constraint name]The constraint definitionCopy the code


Create a table with unique constraint syntax
create table unique_test2
(
    Create a non-null constraint, which means test_id cannot be null
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    Create a unique constraint using table and constraint syntax
    unique (test_name),
    Create a unique constraint using table level constraint syntax, and specify the constraint name
    constraint test_uk unique(test_pass)}
);
    Copy the code

Create a unique constraint for test_name and test_pass, which means that the two columns cannot be empty



Create a unique constraint on a table
create table unique_test3
(
    Create a non-null constraint, which means test_id cannot be NULL
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    Create a unique constraint using table level constraint syntax, specifying that two column combinations cannot be empty
    constraint test3_uk unique(test_name, test_pass)
);
Copy the code

Unique_test2 requires that both test_name and test_pass columns cannot be duplicated. Unique_test3 requires that only the combination of test_name and test_pass columns cannot be duplicated

You can modify the table structure to add unique constraints



Add a unique constraint
alter table unique_test3
add unique(test_name, test_pass);
Copy the code

You can use the modify keyword when modifying a table to add unique constraints by using column-level constraints for a single column



Alter table NULl_test add unique constraint ALTER table null_test add unique constraint ALTERtable null_test
modify user_name varchar(255) unique;
Copy the code

For most databases, drop constraints are dropped after the ALTER TABLE statement by using the drop index constraint name syntax. MySQL does not use this method



Alter table unique_test3 alter table unique_test3 alter table unique_test3table unique_test3
drop index test3_uk;
Copy the code

PRIMARY KEY constraint

Primary key constraints are equivalent to non-null and unique constraints, that is, the columns of primary key constraints are not allowed to have duplicate values or null values. If a primary key constraint is imposed on a multi-column combination, then each column contained in the multi-column combination cannot be empty, but only that the column combination cannot be repeated

A maximum of one primary key is allowed per table, but this primary key constraint can be composed of multiple data columns. A primary key is the only field or combination of fields in a table that identifies a row

When creating primary key constraints, you can use either column-level or table-level constraints. When you need to create combined primary key constraints for multiple fields, you can only use table-level constraint syntax. When you create a constraint using table-level constraint syntax, you can specify a constraint name for that constraint. MySql always names all PRIMARY key constraints PRIMARY

Create primary key constraint using primary key

Create a primary key constraint with column level constraint syntax:



create tablePrimary_test (# set primary key constraint test_idint primary key,
    test_name varchar(255));Copy the code

Create primary key constraints when creating a table, using table-level constraint syntax



create table primary_test2
(
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255Mysql > select primary key from test2_pk; select primary key from test2_pkconstraint test2_pk primary key(test_id)
);
Copy the code

Create a primary key constraint when creating a table. Create a composite primary key with multiple columns. Only table level constraint syntax can be used



create table primary_test3
(
    test_name varchar(255),
    test_pass varchar(255Select * from table where primary key();test_name, test_pass)
);
Copy the code

To drop primary key constraints for a specified table, use the DROP primary_key statement after the ALTER TABLE statement



Alter table primary_test3 ALTER table primary_test3 alter table primary_test3drop primary key;
Copy the code

If you need to add a primary key constraint to a specified table, modify the column definition to add a primary key constraint using the column-level constraint syntax or add to add a primary key constraint using the table-level constraint syntax



Add primary key constraints using column level constraint syntax
alter table_primary_test3
modify test_name varchar(255) primary key;
Copy the code


Add primary key constraints using table level constraint syntax
alter table primary_test3
add primary key(test_name,test_pass);
Copy the code

MySQL uses only auto_increment to set autoincrement



create tablePrimary_test4 (# create primary key constraint, use autoincrement test_idint auto_increment primary key,
    test_name varchar(255),
    test_pass varchar(255));Copy the code

FOREIGN KEY constraints

Foreign key constraints mainly guarantee referential integrity between one or two tables. Foreign keys are the referential relationships built between two fields of a table or two fields of two tables. Foreign keys ensure that the two related fields are referenced: the value of the foreign key column of the child (slave) table must be within the value range of the referenced column of the primary table, or be null.

When a primary table is referenced by a secondary table, the primary table cannot be deleted. You can delete the primary table only after all the records referenced from the secondary table have been deleted

Only the primary key column or unique key column of the primary table can be referenced from the foreign key of the table. In this way, the secondary table record can be accurately located to the referenced primary table record. You can have multiple foreign keys in the same table

Foreign key constraints are commonly used to define one-to-many, one-to-one associations between two entities. For one-to-many associations, it is common to add a foreign key column to the many end. For a one-to-one association, either party can be selected to add a foreign key column. A table that adds a foreign key column is called a slave table. For many-to-many associations, an additional join table is required to record their associations.

The column-level constraint syntax and table-level constraint syntax can also be used to establish foreign key constraints. If you only set up foreign key constraints for individual data columns, you can use column-level constraint syntax. If you need to create a foreign key constraint for a multi-column combination, or if you need to name a foreign key, you must use table-level constraints

Use the references keyword to specify which table this column refers to and which column of the main table



To ensure that the primary table referenced from the table exists, it is usually necessary to create the primary table firstcreateTable teacher_table1 (#auto_increment: teacher_id int AUTO_increment, teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table1
(
student_id int auto_increment primary key,
student_name varchar(255Java_teacher int References Teacher_table1 (teacher_id));Copy the code

But it is worth pointing out that, although the mysql support using column level grammar to establish foreign key constraints, but the column level grammar to establish the foreign key constraints will not take effect, the column level constraints on mysql grammar just to keep good compatibility, and standard SQL if you want to use the foreign key constraints to take effect in the mysql, should use a table syntax level constraints



To ensure that the primary table referenced from the table exists, it is usually necessary to build the primary table firstcreateTeacher_id int AUTO_INCREMENT, teacher_name vARCHar (#auto_increment)255),
    primary key(teacher_id)
);

create table student_table
(
    student_id int auto_increment primary key,
    studnet_name varchar(255), # specify javA_teacher reference to Teacher_table teacher_ID column javA_teacher int, foreignkey(java_teacher) references teacher_table(teacher_id)
);
Copy the code

If you use table-level constraints, you need to use a foreign key to specify the foreign key column of the table, and references to specify which table to refer to and which data column to refer to the main table. Table level constraint syntax allows you to specify a constraint name for a foreign key. If no constraint name is specified when creating a foreign key constraint, MySQL assigns the constraint name table_name_ibfk_n, where table_name is the table name and n is an integer starting from 1

If you need to explicitly name a foreign key constraint, you can use the constraint to specify the name



create tableTeacher_table2 (#auto_increment: Represents the automatic encoding policy for the database and is usually used as the logical primary key for the data table teacher_idint auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table2
(
    student_id int auto_increment primary key,
    studnet_name varchar(255), # specify javA_teacher with reference to teacher_table teacher_ID column java_teacherintStudent_teacher_fk create a foreign key constraint using table level constraint syntaxconstraint student_teacher_fk foreign key(java_teacher) 
    references teacher_table(teacher_id)
);
Copy the code

If you need to establish foreign key constraints for multi-column combinations, you must use table-level constraint syntax



create table teacher_table
(
    teacher_name varchar(255),
    teacher_pass varchar(255), # create a primary key with two columnskey(teacher_name, teacher_pass)
);

create tableStudent_table (# create primary key constraint student_id for this tableint auto_increment primary key,
    student_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255), # create a foreign key constraint using table-level constraint syntax, specifying the joint foreign key foreign of the two columnskey(java_teacher_name, java_teacher_pass)
    references teacher_table(teacher_name, teacher_pass)
);
Copy the code

Alter TABLE alter table drop foreign key constraint name



Alter table student_talbe_IBkf_1 alter table student_table3 student_TALbe_IBkf_1 alter table student_talbe_IBkf_1table student_table
drop foreign key student_table_ibkf_1;
Copy the code

To add a foreign key constraint, run the add foreign key command as follows



Student_table add a foreign key constraint
alter table student_table
add foreign key(java_teacher_name, java_teacher_pass)
references teacher_table(teacher_name, teacher_pass);
Copy the code

It is worth pointing out that foreign key constraints can refer not only to other tables but also to themselves, which is often referred to as self-correlation



Create a foreign constraint key using table-level constraint syntax and reference it directly to itself
create table foreign_test
(
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    Use the refer_id of this table to refer to the foreign_id column of this table
    refer_id int,
    foreign key(refer_id) references foreign_test(foreign_id)
);
Copy the code

If you want to define that when a primary table record is deleted, the secondary table record is also deleted, you need to add on DELETE CASCADE or on DELETE SET NULL after the foreign key constraint is created. The first is to delete the master table records, the reference to the master table records from all cascaded delete; The second is to specify that when a primary table record is dropped, the foreign key of the secondary table record that references the primary table record is set to NULL



create table teacher_table
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create tableStudnet_table (student_id # for this table primary key constraintsint auto_increment primary key,
    studnet_name varchar(255),
    java_teacher intCreate a foreign key constraint using table level constraint syntaxkey(java_teacher) references teacher_table(teacher_id)
    on delete cascade# can also be usedon delete set null
);
Copy the code

The CHECK constraint

The syntax for setting up the CHECK constraint is as simple as adding CHECK to the column definition of the table under construction



create table check_test
(
    emp_id int auto_increment,
    emp_name varchar(255),
    emp_salary decimal#, createCHECKThe constraintcheck(emp_salary>0));Copy the code

The index

An index is a database object stored in a Scheme, and although it is always subordinate to a table, it is also a database object like a table. The only purpose of creating an index is to speed up queries to the table. Indexes reduce disk I/O by using fast access methods to quickly locate data

An index, as a database object, is stored independently in a data dictionary, but cannot exist independently. It must belong to a table

There are two ways to create an index

  • Automatic: When primary key constraints, unique constraints, and foreign key constraints are defined on a table, the system automatically creates indexes for the data column

  • Manual: You can create index… Statement to create an index

There are also two ways to drop an index

  • Automatic: When a data table is deleted, the indexes in the table are automatically deleted

  • Manual: Users can drop index… Statement to drop the specified index on the specified data table

The syntax for creating an index is as follows:



create index index_name
on table_name (column[,column]...). ;Copy the code

The following index will speed up queries on the Employees table based on the last_name field



create index emp_last_name_index
on employees(last_name);
Copy the code

Index multiple columns simultaneously as shown in



Select last_name and first_name from employees; select last_name from employees
create index emp_last_name_index
on employees(first_name.last_name);
Copy the code

To delete an index in MySQL, you need to specify a table in the following syntax format



drop indexIndex nameonThe name of the tableCopy the code

The following SQL statement drops the index of emp_LAST_name_IDx in the employees table



drop index emp_last_index on employees;
Copy the code

The advantage of indexes is to speed up queries, but indexes also have the following disadvantages:

  • Similar to book catalogs, the database system needs to maintain indexes as records are added, deleted, or modified in a data table, so there is some system overhead

  • Storing index information requires disk space

view

A view looks a lot like a table, but it is not a table because it does not store data. A view is just a logical display of the data in one or more tables

Advantages of using attempt:

  • Access to data can be restricted

  • Can make complex queries simple

  • Provides data independence

  • Provides different displays of the same data

Because a view is just a logical display of the data in a table — that is, a query result — creating a view is about associating the view name with the query statement. As follows:



create or replace viewView nameas
subquery
Copy the code

As you can see from the syntax above, you can use the syntax to create and modify views. The syntax above means that if the view does not exist, create the view; If a view with the specified view name already exists, the new view replaces the original view. The subQuery that follows is a query statement that can be very complex.

Once a view is created, using a table with that view doesn’t make the same difference, but it usually just queries the view data and doesn’t modify the data in the view because the view itself stores no data



create or replace view view_test
as
select teacher_name, teacher_pass from teacher_table;
Copy the code

Most of the time, we don’t recommend changing the view’s data directly, because the view doesn’t store data, it’s just a named query statement. MySQL allows the with check option clause to be used when creating a view. This clause cannot be used when creating a view:



create or replace view view_test
as
select teacher_name form teacher_table
Specifies that the view's data is not allowed to be modified
with check option;
Copy the code

Delete a view using the following statement:



drop viewView nameCopy the code

The following SQL statement deletes the name of the view you just created



drop view view_test;
Copy the code

Syntax of DML statements

Unlike DDL, WHICH operates on database objects, DML mainly operates on data in tables. DML can be used to perform three tasks:

  • Insert new data

  • Modify existing data

  • Delete unnecessary data

DML statements consist of insert into, update, and delete from commands.

The insert into statement is

Insert into is used to insert data into a data table. For standard SQL statements, only one record can be inserted at a time. Insert into syntax is as follows:



insert into table_name [(column[,column..] )]values(value,[,vlaue...] );Copy the code

When an insert operation is performed, the column names of all values to be inserted can be listed in parentheses after the table name and the corresponding values to be inserted can be listed in parentheses after the value. Such as:



insert into teacher_table2 value ('Vincent');
Copy the code

If you do not want to list all columns in parentheses after the table, you need to specify values for all columns; If the value of a column cannot be determined, a NULL value is assigned to the column



insert intoTeacher_table2 # usenullReplace the value of the primary key columnvalues(null.'Pigeau');
Copy the code

However, at this point, the primary key column recorded to be 2 is where where the PRIMARY key column is recorded instead of null, as the SQL inserts, because the primary key column is self-growing and will be automatically assigned to it

According to the foreign key constraint rules, the foreign key column must be the value of the reference column, so the primary table should be inserted before the records from the secondary table. Otherwise, the foreign key column of the secondary table must be NULL. Now insert records from table student_table2



insert intoStudent_table2 when interpolating into a foreign key column, the foreign key column must be a value that is already in the referenced columnvalues (null.'Mars'.2);Copy the code

In special cases, we can use the string query insert statement, which can insert more than one record at a time



insert intoStudent_table2 (student_name) # insert with the value of the subqueryselect teacher_name from teacher_table2;
Copy the code

MySQL allows multiple records to be contained in parentheses after values. Multiple parentheses are separated by commas (,)



insert intoTeacher_table2 # Insert multiple values at oncevalues (null.'Paris'),
(null.'Nantes');
Copy the code

The update statement

The UPDATE statement is used to modify the records of a data table, and you can modify more than one record at a time, limiting which records are modified by using the WHERE clause. The absence of a WHERE clause means that the value of the WHERE expression is always true, meaning that all records in the table will be modified. The syntax format of the UPDATE statement is as follows:



update teacher_table 
set column1 = value1[,column=value2]...
[WHERE condition];
Copy the code

Update allows you to modify not only multiple records but also multiple columns at once. Column1 =value1,column2=value2, column1= value2,column2=value2… To change the values of multiple columns separated by English commas (,)



update teacher_table2 
set teacher_name = 'the king';
Copy the code

You can also specify that only specific records are modified by adding a WHERE condition, as follows



update teacher_table
set teacher_name = 'darling'
where teacher_id > 1;
Copy the code

The delete from statement

The DELETE from statement is used to delete records from a specified data table. You do not need to specify the column name when deleting using the DELETE FROM statement because the entire row is always deleted. The DELETE FROM statement allows you to delete more than one row at a time, which rows are restricted by where clauses, and only records that meet the WHERE condition are deleted. The absence of a WHERE clause will delete all entries in the table

The syntax of the delete from statement is as follows:



delete from table_name
[WHERE condition];
Copy the code

SQL > delete all records from student_table2;



delete from studnet_table2;
Copy the code

You can also use the WHERE condition to restrict the deletion of only specified records, as shown in the following SQL statement:



delete form teacher_table2 
where teacher_id > 2;
Copy the code

When a primary table record is referenced by a secondary table record, the primary table record cannot be deleted. A primary table record can be deleted only after all the records that are referenced from the primary table record are deleted. On DELETE NULL is used to specify that when the primary table record is deleted, the value of the foreign key column is set to NULL from the secondary table record

Single table query

The function of the SELECT statement is to query data. The SELECT statement is also the most functional STATEMENT in THE SQL statement. The SELECT statement can not only execute a single table query, but also execute multiple table join queries, and can also carry out sub-queries. The SELECT statement is used to select a specific row or the intersection of a specific column from one or more data tables

The simplest function of the SELECT statement is shown in the figure below

The syntax of the SELECT statement for a single table query is as follows:



select colimn1 colimn2 ... 
formThe data source [WHERE condition]
Copy the code

The data sources in the syntax above can be tables, views, and so on. The select list is used to select columns. The WHERE condition is used to determine which rows are selected. Only records that meet the WHERE condition are selected. If there is no WHERE condition, all rows are selected by default. If you want to select all columns, use an asterisk (*) to represent all columns

The following SQL statement will select all rows and columns in the Teacher_table table.



select * 
from teacher_table;
Copy the code

If the WHERE condition is added, only the records that meet the WHERE condition are selected. The following SQL statement selects the values of the records whose javA_teacher value is greater than 3 in the student_table column



select student_name 
from student_table 
where java_teacher > 3;
Copy the code

When you use the SELECT statement for query, you can also use the arithmetic operators (+, -, *, /) in the SELECT statement to form arithmetic expressions: The rules for using arithmetic expressions are as follows

  • You can create expressions for numeric columns, variables, and constants using the arithmetic operators (+, -, *, and /)

  • You can use partial arithmetic operators (+, -, and) to create expressions for date-type data columns, variables, and constants. You can subtract between two dates, and add or subtract between dates and values

  • Operators can operate not only between columns and constants and variables, but also between two columns

The following SELECT statement uses the arithmetic operator



The data column can actually be treated as a variable
select teacher_id + 5 
from teacher_table;
Select teacher_id * 3 from teacher_table where teacher_id * 3 is greater than 4
select * 
from teacher_table 
where teacher_id * 3 > 4;
Copy the code

It should be pointed out that the select can not only be a column of data, can also be an expression, can also be a variable, constant, etc



The data column can actually be treated as a variable
select 3*5.20 
from teacher_table;
Copy the code

The precedence of arithmetic characters in SQL is exactly the same as the precedence of Java operators. MySQL uses the concat function for string concatenation.



# select teacher_name and'xx'The result of a string concatenation
select concat(teacher_name, 'xx') 
form teacher_table;
Copy the code

For MySQL, if null is used in an arithmetic expression, the return value of the entire arithmetic expression will be NULL. Null in the string concatenation operator will result in null after concatenation



select concat(teacher_name, null) 
from teacher_table;
Copy the code

If you do not want to use the column name as the column title, you can create an alias for the data column or expression. The alias is immediately followed by the data column, separated by Spaces, or separated by the AS keyword



select teacher_id + 5 as MY_ID 
from teacher_table;
Copy the code

If special characters (such as Spaces) are used in the column alias, or you need to enforce case sensitivity, you can do this by adding double quotes to the alias



# You can alias the selected column. Aliases include single quotation marks, so enclose the alias in double quotation marks
select teacher_id + 5 as "MY'id"
from teacher_table;
Copy the code

If multiple columns need to be selected and aliased, separate the columns from each other by commas (,), and separate the columns from the column name by Spaces



select teacher_id + 5MY_ID, teacher_name Teacher namefrom teacher_table;
Copy the code

You can alias a table as well as a column or expression, and the syntax for aliasing a table is exactly the same as for aliasing a column or expression



select teacher_id + 5MY_ID, teacher_name Teacher name# alias teacher_table t
from teacher_table t;
Copy the code

Column names can be treated as variables, so operators can also operate between columns



select teacher_id + 5 MY_ID, concat(teacher_name, teacher_id) teacher_name
from teacher_table
where teacher_id * 2 > 3;    
Copy the code

Select by default selects all eligible records, even if two rows are exactly the same. If you want to remove duplicate rows, you can use the distinct keyword to remove duplicate rows from the query results and compare the results of the following two SQL statements:



Select all records, including duplicate lines
select student_name, java_teacher 
from student_table;

# remove duplicate rows
select distinct student_name, java_teacher 
from student_table;
Copy the code

Note: When using distinct to remove duplicate rows, the distinct keyword is followed by the SELECT keyword, which removes duplicate values from subsequent combinations of fields regardless of whether the corresponding record is duplicate in the database

You’ve already seen the where clause in action: you can control the selection of only specified rows. Because the WHERE clause contains a conditional expression, you can use the basic comparison operators >, >=, <, <=, =, and <>. Comparison operators in SQL can compare not only values but also strings and dates

The SQL comparison operator to determine whether two values are equal is single equal =, and the comparison operator to determine whether two values are equal is <>; The assignment operator in SQL is not equal, but the colon equal (:=).

Special comparison operators supported by SQL

The operator meaning
expr1 between expr2 and expr3 Require expr1 >= expr2 and expr2 <= expr3
expr1 in(expr2,expr3,expr4,…) Expr1 is required to be equal to the value of any of the following expressions in parentheses
like String matching. Wildcard characters are supported
is null Requires a value equal to null

The following SQL statement selects all the records whose student_id is greater than or equal to 2 and less than or equal to 4.



select * 
from student_table 
where student_id between 2 and 4; Student_id = 2; java_teacher = 2; student_id = 2select * 
from student_table 
where 2 between java_teacher and student_id;
Copy the code

When using the IN comparison operation, one or more values must be listed in parentheses after IN, which requires that the specified column be equal to any of the values in the IN parentheses



Student_id; javA_teacher; javA_teacher; student_id; javA_teacher
select * 
from student_table 
where student_id in(2.4);
Copy the code

Similarly, values in parentheses can be constants, variables, or column names



Student_id; javA_teacher; student_id; javA_teacher
select * 
from student_table 
where 2 in(student_id,java_teacher);
Copy the code

The like operator is mainly used for fuzzy queries. For example, if you want to query all records whose names start with “Sun”, you need to use the fuzzy query. In the fuzzy query, you need to use the like keyword. Two wildcards can be used in SQL statements: underscore (_) and percentage sign (%), where the underscore can represent any character and the percentage sign can represent any number of characters. The following SQL statement will query all students whose names start with “sun”



select * 
from student_table
where student_name like 'sun %';
Copy the code

The following SQL statement will find all students with two character names



select * 
fromStudent_table # use two underscores to represent a characterwhere student_name like '__';
Copy the code

In some special cases, you may need to use underscores or percent signs in query conditions. You do not want SQL to use underscores or percent signs as wildcards, so you need to use escape characters. MySQL uses backslash (/) as escape characters



Select all students whose names begin with an underscore
select 8
from student_table
where student_name like '\ _ %';
Copy the code

Is null is used to check whether a value is null. You cannot use =null to check whether a value is null because null in SQL returns NULL. The following SQL statement selects all records whose student_name is null in the student_table table



select * 
from student_table
where student_name is null;
Copy the code

If multiple conditions must be combined after the where statement, SQL provides the and and or logical operators to combine two conditions, and provides not to check the logical expression. The following SQL statement selects all records where the student name is 2 characters, and the student_id is greater than 3.



select * 
from student_table
where student_name like '__' and studnent_id > 3;
Copy the code

The following SQL statement selects all records in the student_table table whose names do not start with an underscore.



select * 
fromStudent_table # usenotrightwhereTake no conditionswhere not student_name like '/ _ %'; 
Copy the code

The precedence of comparison operators and logical operators in SQL

The order by the statement

By default, the results of the query are sorted by insertion order. If you want to sort the query results by the size of a column value, you can use the order by clause

The ORDER BY statement is used to sort the result set BY the specified column. BY default, the ORDER BY statement sorts records in ascending ORDER. If you want to sort the records in descending order, use the DESC keyword

The syntax of the order by sentence is as follows:



order by column_name1 [desc], column_name...
Copy the code

The default sorting mode is ascending. If descending sorting is mandatory, use desc at the end of the column (the corresponding keyword is ASC, which has the same effect as ascending sorting). In the syntax above, the column name, sequence name, and column alias can be used for sorting. The following SQL statement selects all records in the student_table table and sorts them in ascending order in the JavA_teacher column.



select * 
from student_table
order by java_teacher;
Copy the code

If you want to sort by multiple columns, the ASC and DESC of each column must be set separately. If more than one row is specified, the first row is the primary row, and the second row only works if more than one of the same values exists in the first column. If the SQL statements are sorted in descending order by javA_teacher column, the SQL statements are sorted in ascending order by student_NAME column if the javA_teacher column has the same value



select * 
from student_table
order by java_teacher desc, student_name;
Copy the code

Database function

Each database extends functions on top of standard SQL for data processing or complex computations, usually through a set of data, to get the desired output. A function usually has one or more inputs, called parameters, that are evaluated and evaluated internally, and only one value is returned. Functions can appear anywhere in an SQL statement. The most common place is in the WHERE clause after a SELECT

Function is divided into single-line function and multi-line function according to the way of processing multi-line data. Single-line function calculates the input value of each line separately, and each line gets a calculation result back to the user. A multiline function evaluates the entire value of a multiline input and yields only one result

Functions in SQL are similar to methods in the Java language, but functions in SQL are independent program units. That is, functions are called without using any classes or objects as callers. Instead, functions are executed directly. As follows:



function_name(arg1,arg2...)
Copy the code

Multi-line functions, also known as aggregation functions and grouping functions, are mainly used to complete some statistical functions and are basically the same in most databases. However, single-line functions in different databases vary greatly. Single-line functions in MySQL have the following characteristics

  • Arguments to a single-line function can be variables, constants, or columns of data. A single-line function can take multiple arguments and return only one value

  • Single-line functions operate separately for each line, and each line (which may contain multiple arguments) returns a result

  • You can change the data type of an argument using a single-line function. Single-line functions can be nested, that is, the return value of the inner function is the argument of the outer function

MySQL’s single-line function classification is shown in the figure below

The data types of MySQ database can be roughly divided into numeric type, character type, and date-time type. So mysql provides corresponding functions separately. The conversion function is mainly responsible for type conversion, other functions are roughly divided into the following categories

  • A function

  • Flow control function

  • Encryption and decryption function

  • Information function



Select teacher_TABLE teacher_NAME from teacher_table
select char_length(teacher_name) 
from teacher_table;

Calculate the sin value of the teacher_NAME column's character length
select sin(char_length(teacher_name)) 
from teacher_table;

# add a time to the specified date. In this usage interval is the keyword and requires a number and a unit
select DATE_ADD('1998-01-02', interval 2 MONTH);

Get the current date
select CURDATE();

Get the current time
select curtime();

MD5 is the MD5 encryption function
select MD5('testing');
Copy the code

MySQL provides the following functions to handle NULL

  • Ifnull (expr1, expr2) : Returns expr2 if expr1 is null, otherwise returns expr1

  • Nullif (expr1, expr2) : Return NULL if expr1 and expr2 are equal, otherwise return expr1

  • If (expr1, expr2, expr3) : sort of like? : Ternary operator that returns expr2 if expr1 is true, not equal to 0, and not equal to null, otherwise returns expr3

  • Isnull (expr1) : checks whether expr1 isnull. If it isnull, return true; otherwise return false



# Return if student_name is null'No name'
select ifnull(student_name, 'No name')
from student_table;

# if CTO_name column'wu bureau', returns null
select nullif(CTO_name, 'wu bureau')
from CTO_table;

# Return if student_name is null'No name'Otherwise return'Have a name'
select if(isnull(student_name), 'No name'.'Have a name')
from student_table; 
Copy the code

Case function

Case function, flow control function. The case function has two uses

Syntax for the first use of the case function



case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end
Copy the code

The case function uses value and compare_value1, compare_value2,… If value is equal to compare_value1, return the corresponding result1, otherwise return result after else



# if java_teacher is1, the return'Java teachers'for2return'Spring teachers'Otherwise return'Other Teachers'
select student_name, case java_teacher
when 1 then 'Java teachers'
when 2 then 'Spring teachers'
else 'Other Teachers'
end
from student_table;
Copy the code

Syntax for the second use of the case function



case
when condition1 then result1
when condition2 then result2
...
else result
end   Copy the code

Condition Returns a conditional expression of Boolean value



# Those with id less than 3 are junior engineers, those with id from 3 to 6 are intermediate engineers, and those with id less than 3 are senior engineers
select employees_name, case
when employees_id <= 3 then 'Junior Engineer'
when employees_id <= 6 then 'Intermediate Engineer'
else 'Senior Engineer'
end
from employees_table;

Copy the code

Grouping and group functions

The group function, also known as the multi-line function mentioned earlier, evaluates a group as a whole and returns one result per group of records, rather than one result per record

  • Avg ([distinct | all] expr) : calculating the average multi-line expr, including expr can be variables, constants, or data column, but it must be a numeric data type. Using distinct indicates that no duplicate values are counted; All indicates that repeated values need to be computed

  • [distinct count ({* | | all] expr}) : calculation of the total number of article line expr, including expr can be variables, constants, or data column, but it must be a numeric data type. An asterisk (*) is used to indicate the number of rows in the table

  • Max (expR) : Calculates the maximum value of expR for multiple rows

  • Min (expR) : Calculates the minimum value of expR for multiple rows

  • The sum ([distanct | all] expr) : to calculate the sum of multi-line expr



# count the number of records in the student_table tableselect count(*) # calculate the total number of values in the javA_teacher columnselect count(distinctJava_teacher) # count the total of all student_idselect sumStudent_id = student_id = student_id = student_id20* The number of rows recordedselect sum(20Select the maximum value of student_id from student_tableselect max(student_id) # Select the minimum value of student_id in the student_table tableselect min(student_id) # becausesumWhere expr is constant23, so each line has the same value # usedistinctIt is forced not to calculate duplicate values, so the following calculation is23
select sum(distinct 23#) to usecountCount the number of recorded rows,nullIt doesn't countselect count(student_name) # For possible occurrencesnullColumn, can be usedifnullCompute the average of all the records in the JavA_teacher columnselect avg(ifnull(java_teacher, 0))

fromstudent_table; # distinct and * cannot be used togetherCopy the code

Group by statement

The group function treats all records as a group. To explicitly group records, you can use the group by clause after the SELECT statement, usually followed by one or more column names, to indicate that the query results are grouped by one or more columns — the system treats the records as a group when the values of one or more columns are exactly the same

SQL GROUP BY syntax



SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
Copy the code


# countSelect count(*) for each group
fromStudent_table # treat records with the same javA_teacher column value as a groupgroup by java_teacher;
Copy the code

If you group multiple columns, the columns must have identical values to be considered a group



# countSelect count(*) for each group
fromStudent_table # Set javA_teacher and student_name as a group only when the values of the javA_teacher and student_NAME columns are exactly the samegroup by java_teacher, student_name;
Copy the code

Having statements

If you need to filter groups, you should use the HAVING clause, which is also followed by a conditional expression. Only groups that meet the conditional expression are selected. The HAVING clause and the WHERE clause are very confusing. They both have filtering capabilities, but they have the following differences

  • Groups cannot be filtered in the WHERE clause, which is only used to filter rows. Filter groups must use the HAVING clause

  • You cannot use group functions in a WHERE clause. You can only use group functions in a HAVING clause

The reason for adding a HAVING clause to SQL is that the WHERE keyword does not use the SQL HAVING syntax with the aggregate function



SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Copy the code


select *
from student_table
group byJava_teacher # Filter the grouphaving count(*) >2;

Copy the code

Multi-table join query

The following data table for book and student:

Cross join

Cross connections do not require any connection conditions. Returns all rows in the left table, each row in the left table combined with all rows in the right table. A cross join is also called a Cartesian product



select * 
from book as a 
# cross joinCross join is equivalent to the generalized Cartesian productcross join stu as b 
order by a.id
Copy the code

A natural connection

A natural join will be based on the same names in two tables as a join condition; If two tables do not have the same name, a natural join is exactly the same as a cross join — there are no join conditions.



select s.*, teacher_name
from student_table s
# natural joinA natural join uses the same name in both tables as the join conditionnatural join teacher_table t;
Copy the code

The equal (=) operator is used in the join condition to compare the column values of the joined columns, but it uses the select list to indicate the columns included in the query result set and to remove duplicate columns in the join table

Connections using the using clause

The using clause can specify one or more columns that display the same names in two specified tables as join conditions. Assume that there is more than one column of the same name in two tables. If you use Natural Join, all the same names are treated as join conditions. Using the using clause shows which co-names are specified to use as join conditions



select s.*, teacher_name
from student_table s
# joinJoin another tablejoin teacher_table t
using(teacher id);
Copy the code

Join using the ON clause

The most common connection method, and each ON clause specifies only one connection condition. This means that if N table joins are required, n-1 joins are required… On the



select s.*, teacher_name
from student_table s
# joinJoin another tablejoinTeacher_table t # useonTo specify connection conditionson s.java_teacher = t.teacher_id;
Copy the code

In addition to equivalent conditions, the connection conditions of ON clauses can also be non-equivalent conditions



select s.*, teacher_name
from student_table s
# joinJoin another tablejoinTeacher_table t # useonTo specify connection conditionson s.java_teacher > t.teacher_id;
Copy the code

  • Equivalent join: The join condition uses the equal sign (=) operator to compare the column values of the joined columns. The query result lists all columns in the joined table, including duplicate columns

  • Unequal join: Use a comparison operator other than the equal operator to compare the column values of the joined columns in join conditions. These operators include >, >=, <=, <,! >,! < and < >

Full outer link or left and right outer link

Left [outer]join left[outer]join right[outer]join full[outer]join left[outer]join right[outer]join full[outer]join left[outer]join left[outer]join right[outer]join full[outer]join

L join

Use the left table as the benchmark, join the data of a.stuid = B. stuid, and display the corresponding items that are not in the left table, and the column of the right table is null



select * 
from book as a 
left join stu as b 
on a.sutid = b.stuid
Copy the code

The right connection

Select * from table a.stuid = b.stuid; select * from table A. stuid = B. stuid



select * 
from book as a 
right join stu as b 
on a.sutid = b.stuid  
Copy the code

All connection

A full external join returns all rows in the left and right tables. When a row has no matching row in another table, the selection list column in the other table contains null values. If there are matching rows between tables, the entire result set row contains the data values of the base table

The subquery

Subqueries are queries that nest another query within a query statement. Subqueries can support multiple levels of nesting. For a normal query statement, subqueries can occur in two places

  • The use of the form statement as a table is also called inline view because the subquery is essentially a temporary view

  • The value of the filter condition after the WHERE condition

Considerations when using subqueries

  • Subqueries are enclosed in parentheses

  • Subqueries can be aliased when used as data tables (appearing after FROM), and must be aliased when used as prefixes to qualify data columns

  • When using subqueries as filtering criteria, placing subqueries to the right of the comparison operator enhances the readability of the query

  • When subqueries are used as filtering criteria, single-line subqueries use single-line operators, and multi-line subqueries use multi-line operators



select *
Treat subqueries as tables
from (select * from student_table) t
where t.java_teacher > 1;
Copy the code

  • Think of a subquery as a value in a WHERE condition, and if the subquery returns a single-row, single-column value, it is used as a scalar value, so you can use the single-row record comparison operator



select *
from student_table
where java_teacher > 
Subqueries that return a single row or column can be used as scalar values
(select teacher_id 
from teacher_table 
where teacher_name = 'Pigeau');
Copy the code

  • If the subquery returns multiple values, keywords such as in, any, and all are required

  • In can be used in isolation, where multiple values returned by a subquery can be treated as a list of values



select *
from student_table
where student_id in
(select teacher_id
from teacher_table);
Copy the code

  • Any and all can be used with the >, >=, <, <=, <>, and = operators. Combined with any, the value is greater than, greater than or equal to, less than or equal to, not equal to, or equal to any of the values. Combined with all, the value is greater than, greater than or equal to, less than or equal to, not equal to, or equal to all

  • =any has the same effect as in



select *
from student_table
where student_id =
any(select teacher_id
from teacher_table);
Copy the code

  • <ANY is less than the maximum value in the list of values and >ANY is greater than the minimum value in the list of values. <ALL requires less than the minimum value in the value list, and >ALL requires greater than the maximum value in the value list



Student_table student_id is greater than teacher_id in teacher_tableselect *
from student_table
where student_id >
all(select teacher_id
from teacher_table);
Copy the code

  • There is also a subquery that can return multiple rows and columns, in which case there should be corresponding columns in the WHERE clause, and parentheses are used to group the columns



select *
from student_table
where (student_id, student_name)
=any(select teacher_id, teacher_name
from teacher_table);

Copy the code

Set operations

In order to perform a set operation on two result sets, the two result sets must satisfy the following conditions

  • The two result sets must contain the same number of data columns

  • The data types of the data columns contained in the two result sets must also be one-to-one

The union operation

Syntactic format for union operations



The select statementunion selectstatements
Copy the code

Query information about all teachers and students whose primary key is less than 4



Select * from varchAR; select * from varchar; select * from varchar
select * from teacher_table
union
select student_id , student_name from student_table;
Copy the code

Minus operation

Minus is the syntax of the minus operation, which is not supported by MySQL



selectstatementsminus selectstatementsCopy the code

If the teacher records with the same ID and name are subtracted from all student records, the following minus calculation can be performed



select student_id, student_name from student_table
minus# The number of data columns in the two result sets is equal, and the data type is one-to-oneminusoperationselect teacher_id, teacher_name from teacher_table;
Copy the code

Intersects operation

Intersect syntax format for the operation



selectstatementsintersect selectstatementsCopy the code

Find the student record with the same ID and name as the teacher’s record



select student_id, student_name from student_table
intersect# The number of data columns in the two result sets is equal, and the data type is one-to-oneintersectoperationselect teacher_id, teacher_name from teacher_table;Copy the code