Database advanced
After learning database related knowledge, please be sure to see the precautions.
Precautions for database statements: https://blog.csdn.net/qq_43098690/article/details/121661835
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
Auto_increment
1.1 primary key constraint
Note: each table must have a primary key. The data is unique and cannot be null
Example
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) ); ALTER TABLE student ADD PRIMARY KEY (id);
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
default
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.
FOREIGN KEY constraint: FOREIGN KEY
Example:
## 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)
2.3 NATURAL JOIN
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
Syntax:
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)
Note:
<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 = c.study is equivalent to: select name, scorefrom student as s, scores as cwhere s.studentid = c.study
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 aa.name 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.
sentence:
select * from emp e natural join dept d;
answer:
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)
Example:
- 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);
synthesis
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
- 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');
- 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)
synthesis
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): https://blog.csdn.net/qq_43098690/article/details/121664964