2020-2-25-数据库的基本使用-2

一.数据库的使用

（1）DQL语句

1.语法
select
　　字段名
from
　　表名
where
　　条件列表
having
　　分组之后的条件
order by
　　排序
limit
　　分页限定
　　
２.基础查询
常用的语句，例子如下：

SELECT * FROM USER;查询全部
SELECT NAME FROM USER;条件查询
SELECT DISTINCT NAME FROM USER;去重查询（注意空格、格式）
SELECT math,english,math+english FROM user;计算查询列的和（！=null） 
SELECT math,english,math+IFNULL(english,0) FROM user;
SELECT math,english,math+IFNULL(english,0) AS total FROM user;
**AS 关键字，起别名(AS 关键字可以忽略)。**

3.条件查询
1）where后跟查询条件

- SELECT * FROM USER WHERE id > 2;

2）常用运算符

 - > < + - = /   <> != ‘and’ ‘between’ ‘or’ ‘in’  ‘is’ ‘is not’
SELECT * FROM USER WHERE id >= 2 AND id <=4;
SELECT * FROM USER WHERE id BETWEEN 2 AND 4;
SELECT * FROM USER WHERE id =3 OR id =2 OR id =4;
SELECT * FROM USER WHERE id IN(3,2,4);

3）Like关键字（模糊查询）占位符 _单个字符 %多个字符

SELECT * FROM USER WHERE NAME LIKE'z%';查询‘z’开头
SELECT * FROM USER WHERE NAME LIKE '_h%';查询第二个字符为h
SELECT * FROM USER WHERE NAME LIKE '___';查询三个字符的数据

4）排序查询（DESC降序，默认ASC升序）

SELECT * FROM USER ORDER BY id DESC;
SELECT * FROM USER ORDER BY id ASC;
SELECT * FROM USER ORDER BY id DESC ,math DESC;多个排序条件，第一个条件不满足选择之后的条件，直到满足为止。

5）聚合函数——将一列数据作为一个整体，进行column的计算

1. count： 计算个数
2. max： 计算最大值
3. min： 计算最小值
4. sum： 求和
5. avg：计算平均值

SELECT COUNT(NAME) FROM USER; **聚合函数默认排除null的值**
SELECT COUNT(IFNULL(NAME),0) FROM USER; （使用ifnull）
SELECT MAX(id) FROM USER;
SELECT MIN(id) FROM USER;
SELECT AVG(id) FROM USER;
SELECT SUM(id) FROM USER;

6）分组查询（关键字group by）

SELECT sex,AVG(math) FROM USER GROUP BY sex;
SELECT sex,AVG(math) FROM USER WHERE math>70 GROUP BY sex;
SELECT sex,AVG(math) FROM USER WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;

- where和having的区别？
where在分组之前限定，满足条件才分组。having在分组之后限定，满足条件才查询。

7）分页查询（关键字LIMIT）

公式开始的索引：开始的索引=（当前的页码-1）× 每页显示的条数
第一页索引为0

SELECT * FROM USER LIMIT 0,3;
SELECT * FROM USER LIMIT 3,3;

（2）约束

• 概念：对表中的数据进行绑定，保证数据的正确性、有效性和完整性。
• 分类：
  1. 主键约束：primary key

CREATE TABLE stu1(
    id INT PRIMARY KEY, #给id添加主键
    NAME VARCHAR(20)
);
ALTER TABLE stu1 ADD PRIMARY KEY (id); #创建完表之后添加主键
DROP TABLE stu1 DROP PRIMARY KEY;#删除主键

##-----------主键约束的自动增长，配合int类型的主键----------------------
CREATE TABLE stu1(
    id INT PRIMARY KEY AUTO_INCREMENT, #给id添加主键,自增长
    NAME VARCHAR(20)
);
#去除主键自动增长
ALTER TABLE stu1 MODIFY id INT;

   2. 非空约束： not null
   3. 唯一约束： unique
   　　创建表时添加约束：

CREATE TABLE stu(id INT,　NAME VARCHAR(20) NOT NULL);
ALTER TABLE stu ADD phone INT(12) UNIQUE;#添加一个字段，唯一约束
INSERT INTO stu (id,NAME,phone) VALUES(1,'xiaosan',1849);
INSERT INTO stu (id,NAME,phone) VALUES(2,'xiaosan',1849);#重复插入报以下错误
＃Duplicate entry '1849' for key 'phone'
ALTER TABLE stu DROP INDEX phone;#去除约束条件

   4. 外键约束： foreign key

、
　　将上图中红色框框中的数据成为“冗余”的数据，不符合数据库的设计准则。
需要进行表的拆分为两个表。sql语句如下：

CREATE TABLE dep1(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) CHARACTER SET utf8mb4,
    location VARCHAR(20) CHARACTER SET utf8mb4
);
CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) CHARACTER SET utf8mb4,
    age INT,
    dep_id INT, 
    #外键语句
    CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES dep(id)
);

添加相应的数据

INSERT INTO dep VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');

INSERT INTO emp (NAME,age,dep_id) VALUES('张三',20,1);
INSERT INTO emp (NAME,age,dep_id) VALUES('李四',10,1);
INSERT INTO emp (NAME,age,dep_id) VALUES('王五',29,1);
INSERT INTO emp (NAME,age,dep_id) VALUES('找流',20,2);
INSERT INTO emp (NAME,age,dep_id) VALUES('三七',21,2);
INSERT INTO emp (NAME,age,dep_id) VALUES('四十一',41,2);

删除外键

#ALTER TABLE **表名** DROP FOREIGN KEY **外键名称**;
ALTER TABLE emp DROP FOREIGN KEY emp_dep_id;

添加外键

#ALTER TABLE **表名** ADD CONSTRAINT **外键名** FOREIGN KEY (**外键字段名称**) REFERENCES dep(**主表列名称**);
ALTER TABLE emp ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES dep(id);

重点级联操作

#添加外键并设置级联更新
ALTER TABLE emp ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES dep(id) ON UPDATE CASCADE;
#添加外键，设置级联更新，设置级联删除
ALTER TABLE emp ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES dep(id) ON UPDATE CASCADE  ON DELETE CASCADE;

数据库设计的规范

多表之间的关系：

1. 一对一的关系（了解）
   *如：人与身份证
   

2. 一对多（多对一）
   *如：部门和员工
   

3. 多对多的关系
   如：学会说呢过和课程，一个学生可以学多门课程，一门课程可以让多名学生选择。
   使用中间表，最少有两个字段。
   
   下面是一对一，多对多关系的实例：

 #创建分类表
CREATE TABLE t_category(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL UNIQUE
);

#创建路线表
CREATE TABLE t_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100)CHARACTER SET utf8mb4 NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT ,
    FOREIGN KEY (cid) REFERENCES t_category(cid)
);

#创建用户表
CREATE TABLE t_user(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) CHARACTER SET utf8mb4 UNIQUE NOT NULL,
    PASSWORD VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
    uname VARCHAR(10),
    birthday DATE,
    sex CHAR(1) CHARACTER SET utf8mb4 DEFAULT '男',
    phone VARCHAR(11) CHARACTER SET utf8mb4,
    email VARCHAR(100)CHARACTER SET utf8mb4
);

CREATE TABLE t_favorite(
    rid INT,
    DATE DATETIME,
    uid INT,
    -- 创建组合主键
    PRIMARY KEY(rid,uid),
    FOREIGN KEY (rid) REFERENCES t_route(rid),
    FOREIGN KEY (uid) REFERENCES t_user(uid)
);

5. 范式

   1. 第一范式(1NF)：每一列都是不可分割的原子数据项
   2. 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码
   3. 第三范式(3NF)：在2NF基础上，任何非主属性不依赖其他非主属性(在2NF基础上消除传递依赖)

6. 数据库的备份与还原

   1. 命令行
      备份：mysqldump -u用户名 -p 密码 > 保存的路径
      还原：
      1. 登录数据库
      2. 创建数据库
      3. 使用数据库
      4. 执行文件。source 文件路径
         一些遇到的问题，sqlyog失去了架构设计按钮：
         点击帮助–>更改注册详细信息–>更换注册码重新注册即可