数据库相关知识以及命令语句(一)

数据库

1. 什么是数据库
   存储数据的仓库，本质上是一个文件系统，还是文件的方式存在服务器的电脑上的。所有的关系型数据库都可以使用通用的SQL语句进行管理DBMS(DataBase Management System)。
2. 数据库的组成
   数据库管理系统：数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理系统访问数据库中表内的数据。
   所谓数据库服务器：是指在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
   表：为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
   

数据库登陆：mysql -u用户名 -p密码;
退出：quit(或exit);

3. 范式----具有最小冗余的表结构。
   a、第一范式(1st NF －First Normal Form) 第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元（也称为最小的原子单元）， 则满足第一范式（1NF）。
   b、第二范式(2nd NF－Second Normal Form)如果一个关系满足 1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式（2NF），第二范式要求每个表只描述一件事情。
   c、第三范式(3rd NF－ Third Normal Form)如果一个关系满足 2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）。

SQL 分类

1. DDL(Data Definition Language)数据定义语言 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2. DML(Data Manipulation Language)数据操作语言 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3. DQL(Data Query Language)数据查询语言 用来查询数据库中表的记录(数据)。关键字：select, where 等
4. DCL(Data ControlLanguage)数据控制语言(了解)用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
   查看所有数据库：show databases;
   创建数据库：create database 数据库名;
   (创建数据库并指定默认字符集)：create database 数据库名 default character set utf8;
   查看数据状态：show create database 数据库名;
   删除数据库：drop database 数据库名;
   修改数据库名：alter database 数据库名 default character set 字符集;
   使用数据库：use 数据库;
   数据约束：用户操作表的数据进行约束，包括默认值，非空，唯一，主键，自增长，外键。

• 默认值：用户对使用默认值的字段不插入值的时候，就使用默认值。
  列名 数据类型 DEFAULT 默认值,
• 非空： 限制字段必须赋值
  列名 数据类型 NOT NULL,
• 唯一：对字段的值不能重复
  列名 数据类型 UNIQUE
  主键：非空+唯一
  1）通常情况下，每值表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
  2）建议不要选择表的包含业务含义的字段作为主键，建议给每值表独立设计一个非业务含义的 id 字段。
• 自增长：自动递增
  列名 INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
• 外键：约束两种表的数据
  –主表
CREATE TABLE 主表(
列名 1 数据类型 PRIMARY KEY,
列名 2 数据类型 VARCHAR(20)
)
–副表/从表
CREATE TABLE 从表(
列名 1 数据类型 PRIMARY KEY,
列名 2 数据类型 PRIMARY KEY,
CONSTRAINT 外键名字 FOREIGN KEY(列名【外键】) REFERENCES 主表(主键) )

注意：
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！
2）主表的参考字段通用为主键！
3）添加数据： 先添加主表，再添加副表
4）修改数据： 先修改副表，再修改主表
5）删除数据： 先删除副表，再删除主表

级联操作
问题： 当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表，但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现。
级联修改： ON UPDATE CASCADE
级联删除： ON DELETE CASCADE

管理表

查看所有表：show tables;
创建表：create table 表名(
字段名称 1 字段类型 primary key；–主键
字段名称 2 字段类型；
);
查看表结构：desc 表;
删除表：drop table 表;
添加字段：alter table 表 add 字段名 1 类型(,add 字段名 2 类型);
修改字段类型： alter table 表 modify column 列名 类型;
修改字段名称：alter table 表 change column 列名 新列名 数据类型;
删除字段： alter table 表 drop column 列名;
修改表名：alter table 表 rename to 新表名;

数据操作

查看表的数据：select * from 表;
查看部分列：SELECT 列 1,列 2 FROM 表;
查看时给指定列别名：SELECT 列名 AS ‘别名’, 列名 AS ‘别名’ FROM 表名;
(as可以省略)
插入数据(所有列)：insert into 表 values(值1,值2…);
插入部分列：insert into 表(列名1,列名2…) values(值,1值2…);
修改数据：UPDATE 表 SET 列名=值,列名 1=值 1，列名 2=值 2;
删除数据：DELETE FROM 表;
删除所有数据：TRUNCATE TABLE 表名;
查询时重复数据：SELECT DISTINCT 列名 FROM 表;
条件查询：SELECT * FROM 表名 WHERE 列名 1= 值 1 AND且(OR或) 列名 2=值 2;
比较条件： > < >= <= = between and
SELECT * FROM 表名 WHERE 别名 BETWEEN 值 1 AND 值 2;
null： 没有数据。 判断 null： is null,判断不为 null： is not null
SELECT * FROM 表名 WHERE 列 IS NULL;
模糊条件like：
– like 后面跟上符合
– %: 表示任意多个字符（0-多个）
– _: 表示一个字符
SELECT * FROM 表名 WHERE 列名 LIKE ‘值%’;
SELECT * FROM 表名 WHERE 列名 LIKE ‘%值%’;
SELECT * FROM 表名 WHERE 列名 LIKE '值 ';

drop、truncate和delete的区别

1. delete语句，是DML语句，truncate语句通常被认为是DDL语句。
2. delete语句，后面可以跟where子句，通常指定where子句中的条件表达式，只删除满足条件的部分记录，而truncate语句，只能用于删除表中的所有记录。
3. truncate语句，删除表中的数据后，向表中添加记录时，自动增加字段的默认初始值重新从1开始，而使用delete语句，删除表中所有记录后，向表中添加记录时，自动增加字段的值，为删除时该字段的最大值加1，也就是在原来的基础上递增。
4. delete语句，每删除一条记录，都会在日志中记录，而使用truncate语句，不会在日志中记录删除的内容，因此，truncate语句的执行效率比delete语句高。
5. 在速度上，一般来说，drop> truncate > delete。
6. 在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
7. 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；如果和事务有关，或者想触发trigger，还是用delete； 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

聚合函数

-- 返回某列的最大值
max(column)
-- 返回某列的最高值
min(column)
-- 返回某列的总行数
count(column)
-- 返回表的总行数
count(*)
-- 返回某列的相加总和
sum(column)
-- 返回某列的平均
avg(column)

注意： 聚合函数，会排除 null 值的数据，在聚合函数使用时注意空值的情况，要配合 ifnull 函数使用。例：select avg(comm),avg(ifnull(comm,0)) from emp。

GROUP BY

GROUP BY 语句根据一个或多个列对结果集进行分组（默认按照 升序 排列，如果需要按照 降序 对记录进行排序，可以使用 DESC 关键字）。在分组的列上我们通常配合 COUNT、SUM、AVG等函数一起使用。

-- 查询每个部门中的员工数量、平均工资和平均工作年限：
select deptno,count(empno),avg(sal),avg(year(now()) - year(hiredate)) from emp group by deptno

-- 统计每个部门中各工种的人数与平均工资
select job,count(empno),avg(sal),deptno from emp group by deptno,job

-- 查询各个部门的详细信息以及部门人数、部门平均工资
select dept.*,count(emp.empno),avg(sal) from dept inner join emp on dept.deptno = emp.deptno group by emp.deptno

-- 查询人数最多的部门信息
select * from dept where deptno =(select deptno from emp group by deptno order by count(empno) desc  limit 0,1)

-- 查询所有员工中工资排序在5到10名之间的员工信息
select * from emp order by sal desc limit 4,6

对于 group_concat 函数是将归类后的名字以逗号连接成字符串。如图所示：

HAVING

HAVING用于 分组后 的再次筛选,只能用于分组。

-- 查询部门人数大于5的部门的员工信息
select * from emp where deptno in (select deptno from emp group by deptno having count(empno)>5)

-- 查询部门人数大于5的部门的员工信息
select * from emp where deptno in (select deptno from emp group by deptno having count(empno)>5)

-- 查询所有员工工资都大于1000的部门的信息
select * from dept where deptno in (select deptno  from emp group by deptno having min(sal)>1000)

-- 查询所有员工工资都在900～3000之间的部门的信息
select * from dept where deptno in (select deptno from emp group by deptno having min(sal)>900 and max(sal)<3000)

having 和 where 的区别

1. having是分组后，where是分组前。
2. where不用使用聚合函数，having可以使用聚合函数。
3. where在分组之前就会进行筛选，过滤掉的数据不会进入分组。

SQL连接（JOIN）

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

-- 查询所有员工的姓名及其直接上级的姓名
select a.ename,b.ename as mgpename from emp a inner join emp b on a.mgp = b.empno

-- 查询在SALES部门工作的员工的姓名信息
select ename from emp inner join dept on emp.deptno = dept.deptno where dept.dname = 'SALES'

-- 查询入职日期早于其直接上级领导的所有员工信息
select a.ename,a.hiredate,b.ename,b.hiredate from emp a inner join emp b on a.mgp = b.empno and a.hiredate < b.hiredate

-- 查询所有部门及其员工信息，包括那些没有员工的部门
select * from dept left join emp on dept.deptno = emp.deptno

-- 查询所有员工及其部门信息，包括那些还不属于任何部门的员工
select dept.deptno,dept.dname,dept.loc,emp.empno,emp.ename from dept  right join emp on dept.deptno = emp.deptno

• 表中存在至少一个匹配时返回行：inner join on
• UNION 操作符用于合并两个或多个 SELECT 语句的结果集。注意: UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。UNION

-- 查询其他部门中工资、奖金与30号部门某员工工资
select sal,comm from emp where deptno!=30 union (select sal,comm from emp where deptno=30  LIMIT 0,1);

排序

1. 语法：order by
2. 排序方式：asc（升序 默认） desc（降序）
3. order by 排序字段1 排序方式1, 排序字段2 排序方式2... **注意：**如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

分页

1. 语法：limit开始的索引，每页查询的条数；
2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

例子（源码）

CREATE TABLE dept(
 deptno INT(11) PRIMARY KEY,
 dname VARCHAR(20),
 loc VARCHAR(20)
);

CREATE TABLE emp(
 empno INT(11) PRIMARY KEY,
 ename VARCHAR(20) NOT NULL,
 job VARCHAR(20) CHECK (job in('CLERK','SALESMAN','MANAGER','SALESMAN','ANALYST')),
 mgp INT(11) ,
 hiredate DATETIME ,
 sal DECIMAL(10,2),
 comm DECIMAL(10,2),
  deptno INT
);

alter table emp add FOREIGN key(deptno) REFERENCES dept(deptno)
INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',1640,NULL,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',11400,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',5200,500,30);
insert into emp values(7566,'JOENS','MANAGER',7839,'1981-4-2',7015,NULL,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',5200,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',5900,NULL,30);
insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2470,NULL,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3040,NULL,20);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1980-12-17',6200,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'1981-9-8',2240,NULL,20);
insert into emp values(7900,'JAMES','CLERK',7698,'1987-5-23',4000,NULL,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3040,NULL,20);
insert into emp values(7934,'MILLER','CLERK',7782,'1982-12-3',2620,NULL,10);

-- 表dept：
mysql> select * from dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|     10 | ACCOUNTING | NEWTORK |
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+
-- 表emp：
mysql> select * from emp;
+-------+--------+----------+------+---------------------+----------+---------+--------+
| empno | ename  | job      | mgp  | hiredate            | sal      | comm    | deptno |
+-------+--------+----------+------+---------------------+----------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 00:00:00 |  1640.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 00:00:00 | 11400.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 00:00:00 |  5200.00 |  500.00 |     30 |
|  7566 | JOENS  | MANAGER  | 7839 | 1981-04-02 00:00:00 |  7015.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 |  5200.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 00:00:00 |  5900.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 00:00:00 |  2470.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 00:00:00 |  3040.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN | 7698 | 1980-12-17 00:00:00 |  6200.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1981-09-08 00:00:00 |  2240.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1987-05-23 00:00:00 |  4000.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 00:00:00 |  3040.00 |    NULL |     20 |
|  7934 | MILLER | CLERK    | 7782 | 1982-12-03 00:00:00 |  2620.00 |    NULL |     10 |
+-------+--------+----------+------+---------------------+----------+---------+--------+