数据库基础与实战

数据库基础

数据库

数据库事务的 4 个特性是：原子性、一致性、持续性、隔离性
原子性：事务是数据库的逻辑工作单位，它对数据库的修改要么全部执行，要么全部不执行。
一致性：事务前后，数据库的状态都满足所有的完整性约束。
隔离性：并发执行的事务是隔离的，一个不影响一个。如果有两个事务，运行在相同的时间内，执行相同的功能，同一时间仅有一个请求用于同一数据。设置数据库的隔离级别，可以达到不同的隔离效果。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

零散知识点：

数据库系统的主要特点：数据结构化、数据度的冗余度小、较高的数据独立性。
关系数据模型的三个组成部分：数据结构，完整性，数据模型
数据库管理系统的主要功能：数据定义，数据操作，数据库的运行管理，数据组织、存储与管理，数据库的保护和维护，通信
数据库系统：由数据库、数据库管理系统以及其开发工具、应用系统、数据库管理员构成。其中，数据库系统软件包括数据库管理系统、开发工具和数据库应用系统。

参考 数据库基础知识整理

数据库实战

史上最全SQL基础知识总结(理论+举例)
Info.sql

#source Info.sql
#创建数据库：Info
#[史上最全SQL基础知识总结(理论+举例)](https://blog.csdn.net/PILIpilipala/article/details/113798383)
CREATE DATABASE Info;
USE Info;
#学生表
CREATE TABLE stu (
	sid CHAR(6), 
	sname VARCHAR(50), 
	age INT, 
	gender VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

#雇员表
CREATE TABLE emp(
	empno INT, 
	ename VARCHAR(50), 
	job VARCHAR(50), 
	mgr INT, 
	hiredate DATE,
	sal DECIMAL(7,2), 
	comm decimal(7,2), 
	deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

#部门表：dept
CREATE TABLE dept(
	deptno INT, 
	dname varchar(14),
	loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON'); 

SELECT 查询

基础查询

mysql> SELECT * FROM stu;
+--------+----------+------+--------+
| sid    | sname    | age  | gender |
+--------+----------+------+--------+
| S_1001 | liuYi    |   35 | male   |
| S_1002 | chenEr   |   15 | female |
| S_1003 | zhangSan |   95 | male   |
| S_1004 | liSi     |   65 | female |
| S_1005 | wangWu   |   55 | male   |
| S_1006 | zhaoLiu  |   75 | female |
| S_1007 | sunQi    |   25 | male   |
| S_1008 | zhouBa   |   45 | female |
| S_1009 | wuJiu    |   85 | male   |
| S_1010 | zhengShi |    5 | female |
| S_1011 | xxx      | NULL | NULL   |
+--------+----------+------+--------+
11 rows in set (0.00 sec)
mysql> SELECT sid, sname, age FROM stu;
+--------+----------+------+
| sid    | sname    | age  |
+--------+----------+------+
| S_1001 | liuYi    |   35 |
| S_1002 | chenEr   |   15 |
| S_1003 | zhangSan |   95 |
| S_1004 | liSi     |   65 |
| S_1005 | wangWu   |   55 |
| S_1006 | zhaoLiu  |   75 |
| S_1007 | sunQi    |   25 |
| S_1008 | zhouBa   |   45 |
| S_1009 | wuJiu    |   85 |
| S_1010 | zhengShi |    5 |
| S_1011 | xxx      | NULL |
+--------+----------+------+
11 rows in set (0.00 sec)

条件查询

mysql> SELECT COUNT(*) FROM emp WHERE sal>2500;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sal) FROM emp;
+----------+
| SUM(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(comm), COUNT(mgr) FROM emp;
+-------------+------------+
| COUNT(comm) | COUNT(mgr) |
+-------------+------------+
|           4 |         13 |
+-------------+------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sal) FROM emp;
+----------+
| SUM(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sal), SUM(comm) FROM emp;
+----------+-----------+
| SUM(sal) | SUM(comm) |
+----------+-----------+
| 29025.00 |   2200.00 |
+----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
+-------------------------+
| SUM(sal+IFNULL(comm,0)) |
+-------------------------+
|                31225.00 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sal), COUNT(sal) FROM emp;
+----------+------------+
| SUM(sal) | COUNT(sal) |
+----------+------------+
| 29025.00 |         14 |
+----------+------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sal)/COUNT(sal) FROM emp;
+---------------------+
| SUM(sal)/COUNT(sal) |
+---------------------+
|         2073.214286 |

mysql> SELECT AVG(sal) FROM emp;
+-------------+
| AVG(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(sal), MIN(sal) FROM emp;
+----------+----------+
| MAX(sal) | MIN(sal) |
+----------+----------+
|  5000.00 |   800.00 |
+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
+--------+----------+
| deptno | SUM(sal) |
+--------+----------+
|     20 | 10875.00 |
|     30 |  9400.00 |
+--------+----------+
2 rows in set (0.00 sec)