数据库的增删改查

DDL（Data Definition Languages）语句：即数据库定义语句

用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。

DML（Data Manipulation Language）语句：即数据操纵语句

用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。

DCL（Data Control Language）语句：即数据控制语句

用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。

TCL（Transaction Control Language）语句：事务控制语句

用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

基本概念

数据库-库

# 可以操作的数据库有哪些
show DATABASES;
# 切换数据库
use lianxi;
# 查看 lianxi 数据库中所有的表；
show tables;
# 查看 lianxi_zh 库中所有的表；
use lianxi_zh;
SHOW TABLES;

创建表

create table

-- 创建表  Create Table 后跟表名
-- ( 定义字段名 )
CREATE TABLE `user` (
`id`  int(10) NOT NULL AUTO_INCREMENT COMMENT '用户的编号' ,
`name`  varchar(50) NOT NULL COMMENT '用户姓名' ,
`age`  int(3) NOT NULL COMMENT '年龄' ,
PRIMARY KEY (`id`)  -- 主键
)
;

修改表

alter table

添加列

add column

ALTER TABLE `user_0000`
ADD COLUMN `work_date`  datetime NULL COMMENT '入职日期' AFTER `age`;

修改列名

CHANGE COLUMN

-- 将 work_date 修改为 join_date 类型为 date
ALTER TABLE `user_0000`
CHANGE COLUMN `work_date` `join_date`  date NULL DEFAULT NULL COMMENT '入职日期' AFTER `age`;

删除列

ALTER TABLE `user_0000`
DROP COLUMN `age`;

插入数据

insert into

INSERT INTO `user_0000` (`name`, `join_date`) VALUES ('张三', '2021-08-04')

练习

创建一个表， 表名为 employee_0000

1. 创建表
2. 添加5条数据

更改数据

UPDATE `user_0000` SET `name`='李四' WHERE (`id`='5')

-- user_0000 表中所有的name为张三 该为 name=李四
update user_0000 set name="李四" where name="张三"

主键-外键

主键，外键主要是对表的数据进行约束。

比如现在两张表
部门表 dept_0000

员工表 emps_0000

再添加员工数据的时候， dept_id 部门id 依赖 部门表中 id

创建两张表

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `emps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立外键约束

没有建立外键约束之前我可以随意添加数据，
比如

emps 表中的部门id可以随意写。

建立外键约束

因为 emps中dept_id 依赖 部门表

员工表依赖部门表 员工是下游。
要在 员工上创建外键约束。

使用外键约束添加数据

1. dept表中添加数据

1. 在 emps 员工表中添加数据

dept_id 字段可以自动从 dept表中获取。

总结

外键约束主要目的就是 添加数据的时候保证数据完整性和正确性。

主键 在 表中 唯一，不重复。
外键 在引用的时候需要另外一张表中的数据唯一。

在设计表的时候，正常来说：
都会给每个表设置一个 id 字段，这个字段设置为主键，递增。

练习

创建3张表

student表

course 课程表

score 成绩表

之间的关系:

成绩表依赖学生表： 成绩表中 s_id 依赖学生表中的id

下游（score成绩表） score.s_id --> student.id

成绩表依赖课程表： 成绩表中 c_id 依赖课程表中的id

下游（score成绩表） score.c_id --> course.id

1. 先把三个表创建好
2. 根据表关系添加外键约束
3. 分别给三个表添加数据

删除

删除表分为两种

1. 删除所有的数据以及表结构。
2. 只删除数据，保留表结构。

drop table 删除整个表 所有的都删掉；

drop table 表名；

TRUNCATE TABLE 清空表；

TRUNCATE TABLE course

作业

学生表.sql
课程表.sql
成绩表.sql

1. 查询每个同学的成绩，显示 姓名，科目，成绩
2. 查询每个同学的总成绩 显示 姓名，总成绩 ```sql SELECT 学生表.s_name,c_name,score from 学生表 LEFT JOIN 成绩表 on 学生表.id = 成绩表.s_id LEFT JOIN 课程表 on 成绩表.c_id = 课程表.id

SELECT 学生表.s_name,sum(score) from 学生表 LEFT JOIN 成绩表 on 学生表.id = 成绩表.s_id LEFT JOIN 课程表 on 成绩表.c_id = 课程表.id GROUP BY 学生表.s_name

3. 查询语文成绩高于80 或者数学成绩高于80 的同学姓名，科目，成绩
3. 查询总分最高的同学姓名，总成绩
```sql
SELECT 学生表.s_name,c_name,score from 学生表
LEFT  JOIN 成绩表 
on 学生表.id = 成绩表.s_id
LEFT  JOIN 课程表
on 成绩表.c_id = 课程表.id
where (c_name="语文" and score>80)
or (c_name = "数学" and score>80)
-------
SELECT 学生表.s_name,sum(score) from 学生表
LEFT  JOIN 成绩表 
on 学生表.id = 成绩表.s_id
LEFT  JOIN 课程表
on 成绩表.c_id = 课程表.id
GROUP BY 学生表.s_name
-- 找到最高成绩
SELECT MAX(总成绩) from (SELECT 学生表.s_name,sum(score) as 总成绩 from 学生表
LEFT  JOIN 成绩表 
on 学生表.id = 成绩表.s_id
LEFT  JOIN 课程表
on 成绩表.c_id = 课程表.id
GROUP BY 学生表.s_name)as aa
SELECT 学生表.s_name,sum(score) from 学生表
LEFT  JOIN 成绩表 
on 学生表.id = 成绩表.s_id
LEFT  JOIN 课程表
on 成绩表.c_id = 课程表.id
GROUP BY 学生表.s_name
HAVING sum(score) = (
    SELECT MAX(总成绩) from (SELECT 学生表.s_name,sum(score) as 总成绩 from 学生表
    LEFT  JOIN 成绩表 
    on 学生表.id = 成绩表.s_id
    LEFT  JOIN 课程表
    on 成绩表.c_id = 课程表.id
    GROUP BY 学生表.s_name)as aa
)