Advanced version of database grammar, including exercise questions and answers

Keywords: Java Database MySQL

Database advanced

After learning database related knowledge, please be sure to see the precautions.

Precautions for database statements:

1, Database integrity

As the name suggests, the data stored in the database must be valid, that is, validity and accuracy.

Ensure data integrity = add constraints to the table when creating the table (use spaces between multiple constraints)

Classification of integrity:

-Entity integrity (row integrity)

-Domain integrity (column integrity)

-Referential integrity (associated table integrity)

Primary key constraint: primary key
 Unique constraint: unique [key]
Non empty constraint: not null
 Default constraint: default
 Automatic growth: auto_increment
 Foreign key constraint: foreign key

It is suggested that these constraints should be set when creating tables

Here is an example, which can be used:

## Create a student list
create table student(
	studentno int primary key auto_increment,
	loginPwd varchar(20) not null default '123456',	
	studentname varchar(50) not null,
	sex char(2) not null,
	gradeid int not null,
	phone varchar(255) not null,
	address varchar(255) default 'Student dormitory',
	borndate datetime,
	email varchar(50)

1. Entity integrity (line integrity)

What is an entity?

Entity: that is, a row (a record) in the table represents an entity

Function of entity integrity: identify that each row of data is not duplicate.

Constraint types mainly used for entity integrity:

primary key constraint

Unique constraint


1.1 primary key constraint

Note: each table must have a primary key. The data is unique and cannot be null


The first way to add
## Set the primary key directly on the column name to be set
CREATE TABLE student( id int primary key, name varchar(50) );
The second addition method
## The advantage of this method is that you can create a federated primary key
## Indicates the column name that needs to be set as the primary key
CREATE TABLE student( id int, name varchar(50), primary key(id) );
CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );
The third addition method
## Add a primary key to the table by modifying
CREATE TABLE student( id int, name varchar(50) );
1.2 unique constraint

Feature: data cannot be repeated.

For example, the common ID number can not be repeated.

## Set ID number only
CREATE TABLE student( Id int primary key, Name varchar(15),idcardnum varchar(18) unique );
1.3 auto_increment

sqlserver database (identity - Identification column)

oracle database (sequence sequence)

Add auto growing value to the primary key. The column can only be of integer type

## Set the student id to grow automatically
CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values('tom');

2. Domain integrity

What is a domain?

Field represents the current cell

Function of field integrity: restrict the correctness of data in this cell and do not compare with other cells in this column

Domain integrity constraints: data types

Non NULL constraint (not null)

Default value constraint (default)

check constraint (not supported by mysql)

This check constraint indicates that there can only be these options

check(sex ='male 'or sex ='female') ## can only be male or female check(sex ='male 'default or sex ='female');

2.1 non null constraints

not null

## Setting name cannot be empty
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) ); 
insert into student values(1,null,'male');  ## This will fail because the name cannot be empty
INSERT INTO student values(2,'tom',null);
2.2 default settings


CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) default 'male' );insert intostudent1 values(1,'tom','female');insert intostudent1 values(2,'jerry',default);

3. Citation integrity

The premise is based on the relationship between tables.



## The effect of this is, score In the table sid Columns, by student of id Content composition. suffer student(id)Contain.## For example, student of id There are three students 1, 2,3;  that score of sid Only(1,2,3),Otherwise, an error will be reported. CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default 'male' );create table score(	id int primary key,	score int,	sid int ,	constraint fk_score_sid foreign key(sid) references student(id) );	## constraint custom foreign key name foreign key references primary key table name

The data type of the foreign key column must be consistent with that of the primary key

The second addition method:

## Modify the score1 table to add a foreign key alter table score add CONSTRAINT fk_student_score foreign key(sid) REFERENCES student(id);

2, Multi table query

1. Multi table relationship

First, you should confirm that there are several relationships between tables.

1.1 one to many / many to one

Customers and orders (one to many), departments and employees (one to many), books and shelves (many to one)

One to many table creation principle: create a field on the multiple side, and the field is used as the primary key of the foreign key pointing to one side

1.2 many to many relationship

Students and schedules

Many to many table building principle: the third table needs to be created. There are at least two fields in the middle table. These two fields are used as foreign keys to point to the primary key of each party

1.3 one to one relationship

It is not widely used in practical development because one-to-one can be created into a table

Unique foreign key correspondence: assuming that one-to-one is a one-to-many relationship, create a primary key of the party whose foreign key points to one on the many side, and set the foreign key to unique

Primary key correspondence: allows one - to - one primary keys of both parties to establish a relationship

2. Multi table query

There are several types of multi table query:

1. Merge result set: UNION, UNION ALL

2. Connection query

2.1 inner join on

2.2 external join on

- left outer join

- left outer join

- FULL JOIN (not supported by MySQL)


3. Sub query

1. Merge result set

Function: merge result set is to merge the query results of two select statements together!

There are two ways to merge result sets:

UNION: remove duplicate records, for example: SELECT* FROM t1 UNION SELECT * FROM t2;

UNION ALL: do not remove duplicate records, for example: SELECT * FROM t1 UNION ALL SELECT * FROM t2.

Note: the number of columns and column type of the two merged results must be the same.

2. Connection query

Join query is to find the product of multiple tables. For example, t1 joins t2, and the query result is t1*t2.

Join query will generate Cartesian product. Assuming set A={a,b} and set B={0,1,2}, the Cartesian product of the two sets is {(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}. It can be extended to multiple sets.

So many table queries do not produce such results, so how to remove duplicate and unwanted records? Of course, it is through conditional filtering. Usually, there is an association relationship between multiple tables to be queried, so the Cartesian product is removed through the association relationship.

For example, you can actually practice

CREATE TABLE dept1(	deptno int primary key,	dname varchar(14),	loc varchar(13));insert into dept1 values(10,'Service Department','Beijing');insert into dept1 values(20,'R & D department','Beijing');insert into dept1 values(30,'Sales Department','Beijing');insert into dept1 values(40,'Competent department','Beijing');CREATE TABLE emp1(	empno int,	ename varchar(50),	job varchar(50),	mgr int,	hiredate date,	sal double,	comm double,	deptno int);insert into emp1 values(1001,'Zhang San','Clerk',1006,'2019-1-1',1000,2010,10);insert into emp1 values(1002,'Li Si','programmer',1006,'2019-2-1',1100,2000,20);insert into emp1 values(1003,'Wang Wu','programmer',1006,'2019-3-1',1020,2011,20);insert into emp1 values(1004,'Zhao Liu','sale',1006,'2019-4-1',1010,2002,30);insert into emp1 values(1005,'Meng Zhang','sale',1006,'2019-5-1',1001,2003,30);insert into emp1 values(1006,'Xie Na','executive director',1006,'2019-6-1',1011,2004,40);

There will be problems with direct query

select * from emp,dept;

If the Cartesian product is found, the main foreign key relationship is used as a condition to remove useless information

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

Query the specified column

SELECT emp.ename,emp.sal,emp.comm,dept.dnameFROM emp,deptWHERE emp.deptno=dept.deptno;
2.1 connection query


select Listing from Table 1 inner join Table 2 on Table 1.Listing=Table 2.Listing //Relationship between foreign key columns where

Equivalent to:

select Listing from Table 1,Table 2 where Table 1.Listing=Table 2.Listing and ...(Other conditions)


<1> The order of tables 1 and 2 is interchangeable

<2> When finding the equivalence relationship between two tables, find the columns representing the same meaning as the equivalence relationship.

<3> The dot operator represents "of", format: table name. Column name

<4> You can use as to alias the table name. Note that after the alias is defined, the alias is used uniformly

Sample version:

//Query the student name in the student table and the score in the score table. Select name, scorefrom student as Sinar join scores as con s.studentid = is equivalent to: select name, scorefrom student as s, scores as cwhere s.studentid =

The above is between two tables. What if there are more than three tables?

Syntax: select Listing from Table 1 inner join Table 2 on Table 1.Listing=Table 2.Listing inner join Table 3 on Table 1 or table 2.Listing=Table 3.Listing where Equivalent to: select Listing from Table 1,Table 2,Table 3 where Table 1.Listing=Table 2.Listing and Table 1/Table 2.Listing=Table 3.Listing

Let's have an exercise

practice: student2 ---Student list stuid student id int Primary key, self growth stuname Student name varchar(10) password Login password varchar(10) birthday date of birth date  Data: 1 111111 1990-02-09 2 Juvenile 222222 1989-03-12 3 Xiao Hu 333333 1989-09-12 subject2 ---Chart of accounts subjectid Account number int Primary key, self growth subjectName Account name varchar(10)Data: 1 java 2 mysql  3 html   scores2 ---Score table sid Fractional primary key int Primary key, self growth score fraction int  subjectid subject int studentid Student number int  Data: 1 89 1 2 90 2 1 3 87 2 4 98 3
Demand: 1.The test results of Hua'er and the corresponding subjects 2 are displayed.Display the information of all exam students 3.Find out mysql Note: students are only students and do not involve examination subjects and scores. If you need subjects and test scores, you can use students directly id Search the corresponding table. four.Find out the total score of the examinee (because it is the total score, no subject is required) 5.Query the average score of each subject. It's best to practice. After writing five, check the answer 1. The list shows the grades and subjects. The grades and subjects are three tables, and the condition is the student's name=floret select score,subjectname from subject2,scores2,student2where subject2.subjectid=scores2.subjectid and student2.stuid=scores2.studentid and student2.stuname='floret';2. Information is*   Student chart, chart of subjects, score table, all students(The default is all students, so you can not write)select * from subject2,scores2,student2where subject2.subjectid=scores2.subjectid and student2.stuid=scores2.studentid;3. Examination information* , Subjects, scores. Condition is account=mysqlselect * from subject2,scores2where subject2.subjectid=scores2.subjectid and subjectname='mysql';Note: students are only students, not related to test subjects and scores. If you need subjects and test scores, you can use students directly id Search the corresponding table. four. Query total score(Aggregate function sum)   Student score (because it is the total score, no subject is required) select stuname,sum(score) from scores2,student2where student2.stuid=scores2.studentidgroup by stuname;5. select studentname,avg(score) from scores2,subject2where scores2.subject=subject2.subjectidgroup by subjectname;
2.2 connection query

Including left external connection and right external connection. The characteristics of external connection: the query results may not meet the conditions.

– outreach query

– left outreach: select column name from primary table left join secondary table on primary table. Column name = secondary table. Column name

– 1. Display all the data in the primary table, display the data in the secondary table, display the data that can be matched, and display null if the matching is unsuccessful

– 2. The position of the primary meter and the secondary meter cannot be changed at will

Usage scenario: it is generally used as a subquery statement

## This statement uses the alias method   student t  ## The alias of student is tselect depname,name from(select e.*,d.depname from department d left join employee e on e.depid=d.depid) aa where is null;

– right outreach: select column name from secondary table right join primary table on primary table. Column name = secondary table. Column name

a. Left outer connection

SELECT * FROM emp eLEFT OUTER JOIN dept dON e.deptno=d.deptno;

Left join is to query the left table first (i.e. mainly the left table), and then query the right table. Those that meet the conditions are displayed in the right table, and those that do not meet the conditions are displayed as NULL.

b. Right outer connection

Right join is to query all records in the right table first, and then display the left table if it meets the conditions, and display NULL if it does not meet the conditions.

SELECT * FROM emp eRIGHT OUTER JOIN dept dON e.deptno=d.deptno;
3. Natural connection

NATURAL INNER JOIN: natural join is a special equivalent join. It requires that the same attribute columns (with the same name) be connected in the two relationship tables, there is no need to add connection conditions, and duplicate attribute columns are eliminated in the results.

Here are some examples.


select * from emp e natural join dept d;


16: C

17: D

First, judge the main table. From the generation results, CX CY and data are all main tables (non main tables, if there is no main table, it will be displayed as null)

In terms of statements, the left main table is on the left and the right main table is on the right.

3. Sub query

A select statement contains another complete select statement.

To put it bluntly, a subquery is a nested query, that is, a SELECT contains a SELECT. If there are two or more selections in a statement, it is a subquery statement.

Where general subqueries appear:

a. After where, it is a part of the query condition as a bar;

b. After from, make a table;

When the subquery appears after where as a condition, you can also use the following keywords:

a. any

b. all

Form of sub query result set:

a. Single row and single column (for condition)

b. Single row and multiple columns (for conditions)

c. Multiple rows and single column (for conditions)

d. Multiple rows and columns (for tables)


  1. Employees with higher wages than JONES.

Analysis: salary > JONES salary, where JONES salary needs a sub query.

Step 1: query the salary of JONES

SELECT sal FROM emp WHERE ename='JONES';

Step 2: query employees whose wages are higher than those of JONES

SELECT * FROM emp WHERE sal > (First step result);


SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
  1. Query employees in the same department as SCOTT.

Query criteria: Department = SCOTT's department number, where SCOTT's department number requires a sub query.

Step 1: query SCOTT's department number

SELECT deptno FROM emp WHERE ename='SCOTT';

Step 2: query employees whose department number is equal to SCOTT's department number

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');
  1. Employee information of department owner whose salary is higher than No. 30

Query criteria: the salary is higher than the salary of 30 Department owners, of which the salary of 30 Department owners is a sub query. Above all, you need to use the all keyword.

Step 1: query the salary of 30 Department owners

SELECT sal FROM emp WHERE deptno=30;

Step 2: query the employee information whose salary is higher than 30 Department owners

SELECT * FROM emp WHERE sal > ALL (First step)


SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

Finally, give an exercise address

SQL advanced grammar exercise (a mall database storage):

Posted by double on Wed, 01 Dec 2021 15:59:04 -0800