Introduction database system
Purpose of this paper:
Carry out the experiment according to the experimental contents listed in the exercise analysis and experimental guidance of introduction to database system (5th Edition), record the relevant contents, and be familiar with the common operations of the database.
reference material:
Introduction to database system (5th Edition), Wang Shan, SA Shixuan, higher education press;
Introduction to database system (5th Edition), exercise analysis and experimental guidance, Wang Shan, Zhang Jun, higher education press.
Experimental environment:
OS: ubuntu 16.04 server;
DBMS: MariaDB
Example source:
https://dev.mysql.com/doc/index-other.html;
https://github.com/datacharmer/test_db (2020.2.12 copy);
- Introduction database system
- 0 preparation
- 1 database definition and operation language
- 2 security language
- 3 integrity language
- 4 trigger
- 5 database design
- 6 stored procedure
- 7 database application development
- 8 database design and application development
- 9 database monitoring and performance optimization
- 10 database recovery technology
- 11 concurrency control
0 preparation
0.1 query
0.1.1 limit the maximum number of queries
MYSQL:
- Query 10 records from the employee table
SELECT * FROM employees LIMIT 10;
0.2 SQL syntax
0.2.1 back quotation mark, single quotation mark
Backquotes are used to distinguish reserved words, such as for attribute names.
Single quotation marks are used to delimit a string or to escape characters from single quotation marks.
1 database definition and operation language
1.1 database definition language
1.1.1 experimental purpose
Understand and master the database DDL language, and be able to skillfully use SQL DDL statements to create, modify and delete databases, schemas and basic tables.
1.1.2 test contents and requirements
Understand and master the syntax of SQL DDL statements, especially the specific meaning and usage of various parameters; Use SQL statements to create, modify, and delete databases, schemas, and basic tables. Master the debugging methods of common syntax errors in SQL statements.
1.1.3 key and difficult points of experiment
Experimental focus: create database and basic table.
Experimental difficulties: when creating a basic table, select appropriate data types for different columns, and correctly create table level and column level integrity constraints, such as whether column values are allowed to be empty, main code and integrity restrictions. Note: data integrity constraints can be defined when creating a basic table, or you can create a table first and then define integrity constraints. Due to the limitation of integrity constraints, the referenced table must be created first.
1.1.4 test report
1.1.4.1 brief description of experiment contents
This experiment establishes the understanding of employees database, analyzes the relationship and constraints of each table, and analyzes the semantics of each column in the table.
1.1.4.2 database structure
Database name: employees
surface | meaning |
---|---|
departments | Department table |
employees | Employee table |
dept_emp | Employee department table |
dept_manager | Department Manager table |
titles | Position table |
salaries | wages |
CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender ENUM ('M','F') NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (emp_no) ); CREATE TABLE departments ( dept_no CHAR(4) NOT NULL, dept_name VARCHAR(40) NOT NULL, PRIMARY KEY (dept_no), UNIQUE KEY (dept_name) ); CREATE TABLE dept_manager ( emp_no INT NOT NULL, dept_no CHAR(4) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,dept_no) ); CREATE TABLE dept_emp ( emp_no INT NOT NULL, dept_no CHAR(4) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,dept_no) ); CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,title, from_date) ); CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date) );
1.2 basic data query experiment
1.2.1 experimental purpose
Master the basic specification of SQL programming, and skillfully use SQL language to realize basic data query, including single table query, grouping statistical query and connection query.
1.2.2 test contents and requirements
Design various forms query SQL statements and grouping statistics query statements for TPC-H database; Design the connection query of a single watch pin to itself, and design the connection query of multiple tables. Understand and master the characteristics and functions of each word of SQL query statement, write specific SQL query statement according to SQL programming specification, and debug it.
Note: in short, SQL programming specification includes SQL keyword capitalization, table name, attribute name, stored procedure name and other identifiers, mixed case, SQL program writing and compact arrangement.
1.2.3 key and difficult points of experiment
Experimental key points: grouping statistical query, single table self connection query and multi table connection query.
Experimental difficulties: distinguish tuple filter conditions and grouping filter conditions; Determine the connection properties and correctly design the connection conditions.
1.2.4 test content record
1.2.4.1 single table query (query)
- Query department information.
SELECT dept_no, dept_name FROM departments;
1.2.4.2 single table query (selection)
- Query the information of all male employees whose employment date is January 1, 1990.
SELECT * FROM employees WHERE hire_date='1990-01-01' AND gender='M';
1.2.4.3 grouping statistics query without grouping filter conditions
- Query the payroll of all employees.
SELECT emp_no, SUM(salary) FROM salaries GROUP BY emp_no;
1.2.4.4 grouping statistics query with grouping filter conditions
- Query the payroll of all employees whose payroll is no less than 1 million.
SELECT emp_no, SUM(salary) FROM salaries GROUP BY emp_no HAVING SUM(salary) >= 1000000;
1.2.4.5 two table connection query (common connection)
- Check the positions of all employees who joined in 1990.
SELECT DISTINCT e.emp_no, first_name, last_name, hire_date, title FROM employees e, titles t WHERE e.emp_no=t.emp_no AND hire_date BETWEEN '1990-01-01' AND '1990-12-31';
1.3 advanced data query experiment
1.3.1 experimental purpose
Master the design methods of various advanced queries such as SQL nested query and set query.
1.3.2 test contents and requirements
For TPC-H database, correctly analyze user query requirements, and design various nested queries and set queries.
1.3.3 experimental key points and difficulties
Experimental focus: nested query.
Experimental difficulties: related sub query and multi-layer EXIST nested query.
1.3.4 test content record
1.3.4.1 IN nested query
- Query the salary records of the manager of the finance department.
SELECT s.emp_no, first_name, last_name, salary, s.from_date, s.to_date FROM salaries s, employees e WHERE s.emp_no=e.emp_no AND s.emp_no IN ( SELECT dm.emp_no FROM dept_manager dm, departments d WHERE d.dept_name='Finance' AND d.dept_no=dm.dept_no );
1.3.4.2 single layer EXISTS nested query
Note: subqueries with EXISTS predicates may not be equivalent to other forms of subqueries.
- Count the number of employees (once) in the finance department.
Using EXISTS predicate can be understood as finding an employee whose department is financial tuple.
SELECT COUNT(*) FROM dept_emp de WHERE EXISTS ( SELECT * FROM departments d WHERE d.dept_name='Finance' AND d.dept_no=de.dept_no );
If two tables are used to connect queries, similar semantics can be realized (there are subtle differences in semantics. The former is existential semantics and the latter is counting semantics).
SELECT DISTINCT COUNT(de.emp_no) FROM dept_emp de, departments d WHERE de.dept_no=d.dept_no AND d.dept_name='Finance';