数据库中的操作(语法)

1.介绍

1、数据查询语言（DQL: Data Query Language）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。
保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。
这些DQL保留字常与其它类型的SQL语句一起使用。 

2、数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。
它们分别用于添加、修改和删除。  

3、数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。
在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；
为表加入索引等。 

2.DDL语言

其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；
为表加入索引等。 mysql是一个关系型数据库，库里面包含若干个表，
而每一张表都是由行和列组成。

2.1 关于数据库操作的命令

 -- 创建数据库 create database 数据库名称
	create database mydb;
-- 看到当前具有的数据
	show databases;
-- 切换到指定的数据库 use 数据库名
	use mydb;
-- 删除数据库  drop database 数据库名称  
	drop database mydb;
	注意: 删除的数据库必须是你自己创建 不要删除系统自带的数据库 ，
	而且数据库中不区分大小写。

2.2 关于表的操作

1. 创建表结构的语法:
   create table 表名(
        列名 数据类型,
        列名 数据类型,
        列名 数据类型
        .....
   );
   
 注意: 最后一列不在使用逗号(,)  逗号必须是英文状态下。
 
 -- 创建表 
create table student(
        id int primary key auto_increment,
		name varchar(20),
		age int,
		address varchar(30)
);
int 数据类型整形
varchar(长度)  字符串类型(字符串的长度)
primary key: 表示该列为主键列。 特点该列的值不能为null 也不能重复。一张表中只能有一个primary key;
auto_increment: 表示该列的值可以递增。该列的类型必须为int类型。  
-- 显示当前数据库中具有的表
show tables;
-- 显示表结构:
desc student;

-- 删除表
drop table student;

2.3 修改表结构

-- (1) 增加一列
alter table student add column sex varchar(2) 
 -- (2) 修改列的名称
alter table student change column sex aaa varchar(2); 
 -- (3) 修改列的数据类型。
alter table student modify column aaa int; 
 -- (4) 删除某一列
alter table student drop column aaa; 

3. DML语言

数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。  
上面已经把表结构创建好；
 但是我们应该表中添加数据。

3.1 添加 insert

-- 添加数据 注意: 字符串必须用 '' 不能使用双引号 ""
 -- (1) 添加所有字段的数据.
 insert into student(id,name,age,address) values(1,'张三',18,'北京');
 -- (2) 如果你添加所有列的值 那么可以省略列的名称
 insert into student  values(2,'李四',19,'郑州');
 -- (3) 因为主键为递增模式 所以我们在添加数据时可以不为主键指定值
 insert into student values(null,'王五',22,'杭州');
 -- (4) 添加部分列.
 insert into student(name,age) values('赵六',19);
 -- (5) 添加多条记录
 insert into student values(null,'刘德华',22,'香港'),(null,'张学友',13,'商丘'),(null,'黎明',26,'驻马店')

3.2 删除 delete

-- 删除记录
  -- (1) 根据条件删除 delete from 表名 where 条件; 关于条件的介绍我们会在后面详细讲解。
	delete from student where id=2;

3.3 修改语句

-- 修改记录 update  表名  set 列名='值',列名=值 where 条件
  update student set name='闫克起',age=18 where id=3;

-- 条件
  -- 范围删除。
	delete from student where age>15;
	-- 逻辑符连接多个条件 and  or 
	delete from student where age<15 or name='刘德华'  
	

3.4 查询语句

首先建立一个表

use mydb;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生表的主键列',
  `name` varchar(255) ,
  `age` int(11) ,
  `address` varchar(255),
  PRIMARY KEY (`id`) USING BTREE
) ;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 18, '北京');
INSERT INTO `student` VALUES (2, '李四', 19, '北京');
INSERT INTO `student` VALUES (3, '王五', 19, '北京');
INSERT INTO `student` VALUES (4, '钱七', 19, '北京');
INSERT INTO `student` VALUES (5, '关羽', 25, '南京');
INSERT INTO `student` VALUES (6, '张飞', 25, '南京');
INSERT INTO `student` VALUES (7, '赵云', 28, '南京');
INSERT INTO `student` VALUES (8, '项羽', 19, '南京');
INSERT INTO `student` VALUES (9, '刘备', 30, '上海');
INSERT INTO `student` VALUES (10, '曹操', 40, '许昌');
INSERT INTO `student` VALUES (11, '夏侯', 36, '许昌');
INSERT INTO `student` VALUES (12, '诸葛亮', 38, '上海');
INSERT INTO `student` VALUES (13, '周瑜', 78, '江东');
INSERT INTO `student` VALUES (14, '小乔', 56, '江东');
INSERT INTO `student` VALUES (15, '孙尚香', 59, '江东');

3.4.1 查询语句入门

1. select * from student; 
   查询表中所有的记录，注意select后根了* 表示通配，所有的列。而在实际开发中不建议大家使用*，因为*会导致索引失效。而是把每一个列名一一列出。
 select id,name,age,address from student;  
 
2. 我们也可以查询部分列。
select name,age from student;
注意: 查询的结果，被放入到一个临时表中。
   
3.可以把查询的列名起别名
-- 为查询的列名起别名
select name as 姓名, age as 年龄 from student;
-- 这里的as可以省略
select name 姓名, age 年龄 from student;  

3.4.2 去除重复的值

-- 使用distinct 去除重复字段值 要求查询的所有列值 都必须相同时。
select distinct address from student;

3.4.3条件查询

1. 判断
select * from student where age>19;
select * from student where age>=19;
select * from student where age!=19;
select * from student where age<19;
select * from student where age<=19;

2. 多条件查询  and(与)  or(或)
select * from student where age>=19 and address='上海'

select * from student where age<=19 or address='上海'

3. 范围语句
select * from student where age>=19 and age<=30;

select * from student where age between 19 and 30; -- 等价于上面

4. 使用in
-- 此时我们有这样一个需求  查询年龄是 18 或者 25 或者 30 或者 36 或者 48 的学生
select * from student where age=18 or age=25 or age=30 or age=36 or age=48

-- 上面这种方式比较麻烦  我们可以使用in来替换上面的模式
select * from student where age in(18,25,30,36,48)


5. 模糊搜索 like 
  通配符: _统配一个字符    %统配n个字符。

-- 模糊查询
 -- 查询名字中第二个字符为三 _ 
 select * from student where name like '_三'; 
 
 -- 查询名字姓李  %统配任意字符
 select * from student where name like '李%'
 
 select * from student where name like '%三%'

3.4.4排序查询

 -- 排序查询  order by 列名 desc:降序  asc:升序(默认)
 select * from student order by age ;
 
 -- 根据多列进行排序年龄按照降序  id按照升序。
 -- (按照年龄进行排序 如果年龄相同  再按照id进行排序)
 select * from student order by age desc, id asc;


-- 注意: 既有where 又有order by  那么谁在前 谁在后。
-- where在前  order by在后。
select * from student where age>19 order by age desc;

 

3.4.5聚合函数

SQL 结构化查询语言 也是一种编程语言，所以也有函数 并且有 内置函数(官方) 和自定义函数（程序员）

-- count(列名) count(1)表中第1列但是它不能使用distinct去重  count(*)表中所有列 统计记录条数  这三个有啥区别

select count(distinct name),count(2),count(*) from t_student;


聚合函数总共有5个:
   max(列名):求某列的最大值。
   min(列名):求某列的最小值。
   sum(列名):求某列的和
   avg(列名):求某列的平均值
   count(列名):求某列的个数。
   
-- 求最大的年龄
select max(age) from student;

-- 求最小的年龄
select min(age) from student;

-- 求年龄的和
select sum(age) from student;

-- 求学生的个数.
select count(id) from student;

-- 求年龄平局值
select avg(age) from student;

3.4.6分组查询

在sql中有个 group by  语句  将某一列相同数据 视为一组 然后进行查询 与聚合函数连用。

-- 求各个地区的人数
select count(*),address from t_student group by address;

-- 查询 各个地区的平均年龄
select avg(age),address from t_student group by address;

可以使用 having 对分组进行条件检索。 

查询 平均年龄大于30的地区人数
select address,count(*),avg(age) from t_student group by address having avg(age)>30;

查询 最大值小于30 的地区 人数 和 平均年龄
select address,count(*),avg(age),max(age) from t_student group by address having max(age)<30;
查询 人数大于3的地区 最大年龄

查询每个地区 25岁以上人数的数量
select address,count(*) from t_student where age>25 group by address;


注意: 如果使用了group by 那么select后只能根分组的条件列和聚合函数。
select id,address,count(*),avg(age),max(age) from t_student group by address having max(age)<30;

这种是错误的，因为select后根了id 而id不是分组的条件。

3.4.7 分页查询

当数据库表数据量比较大   例如： 1000w行数据 如果我们执行 select * from student 此时有可能数据库卡死 -- 拿出1000w数据到内存里，你的内存可能会不够。导致电脑卡死。
如果在java中 有可能内存直接溢出 所以实际开发中 都是分页查询 （部门查询）

分页使用: limit 
select * from student limit 3,5; //从第3条记录查询 查询5条

select * from t_student limit 3,5; -- 从第三条记录查询 查询5条记录。
select * from t_student limit 0,5; -- 从第0条记录查询  查询5条记录。

--- 分页: 
select * from 表名 limit (n-1)*m,m; -- 查询第n页得m条记录。
--- n表示页码  m:表示每页得条数，
第1页每页显示5条记录: select * from t_student limit 0,5;
第2页每页显示5条记录: select * from t_student limit 5,5;
第3页每页显示5条记录: select * from t_student limit 10,5;
第4页每页显示5条记录: select * from t_student limit 15,5;

3.4.8 sql优先级（面试题）

sql语法:
select distinct * from 表名
where 条件
group by 分组
having 分组条件
order by 排序
limit 分页。

上面为他得语法结构。顺序不能乱。

mysql执行引擎:执行得顺序。

3.4.9 字符串函数-- 操作字符串

-- 字符串拼接函数
select name from t_student;

select concat('我的名字叫:',name,",年龄:",age) as n from t_student;

-- 字符串转换为大写和小写
select name,upper(name),LOWER(name) from t_student;

-- 去除前后空格
select address,trim(address) from t_student;

-- 找到姓李得学生 字符串得截取
select * from t_student where name like '李%';

select * from t_student where substr(name,1,1)='李'

3.4.10 数字函数

-- 向上取整 5.1你给定得值
select ceil(5.1),floor(5.9),mod(10,3),abs(-5.9) from t_student;

--面试题
-- 要求如果status为0变为1 为1变为0  update
update aaa set status=abs(status-1); 

3.4.11 时间函数

-- CURDATE()返回当前日期 后面可以不用跟表名
select curdate();
-- 可以用在where
select * from tb_emp where curdate()-entrydate=1

-- CURTIME()返回当前时间
select curtime() ;

-- NOW()返回当前日期和时间
select now();

-- YEAR(date)获取指定date的年份
select year('2022-04-25');

select * from tb_emp where year(entrydate)=2022

-- DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
SELECT DATE_ADD('2020-04-25', INTERVAL 3 YEAR);

-- DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
select datediff('2020-05-25','2020-04-25');
-- 入职30天得员工.
select * from tb_emp where DATEDIFF(now(),entrydate)>30

3.4.12 流程函数

-- 如果年龄>30妇女 否则少女
select age,if(age>30,'妇女','少女') from tb_emp;

-- ifnull 如果为null，则
select job,ifnull(job,'工作待定') from tb_emp;

4.约束

什么是约束? 就是在表中为某些列添加约束，使该列得值必须符合这个约束。

create table t_teacher(
    id int primary key auto_increment, -- 要求该列得值必须唯一且不能为null.
	  tname varchar(20) unique, -- unique 唯一约束 表示该列得值必须唯一。但是可以运行多个null
		age int not null, -- not null 非空约束 表示该列得值不能为null.
		sex char(2) default '男' -- default 默认约束 表示如果该列没有给定值 则默认为男
)
select * from t_teacher;
insert into t_teacher(id,tname,age,sex) values(null,'刘德华',15,'女');
-- Duplicate entry '刘德华' for key 'tname' 由于tname具有唯一约束 表中有名字为刘德华 所以不能在添加刘德华名称
insert into t_teacher(id,tname,age,sex) values(null,'刘德华',18,'男');
-- Column 'age' cannot be null 因为age设置了非空约束所以 添加数据时年龄必须有值。
insert into t_teacher(id,tname,age,sex) values(null,'张学友',null,'男');

-- 因为sex有默认约束 所以在添加数据时可以不为sex设置值，则采用默认值。
insert into t_teacher(id,tname,age) values(null,'张学友',22);

4.1 外键约束

外键约束用来让两张图的数据之间建立连接，保证数据的一致性和完整性。 
-- 外键
create table t_class(
    cid int primary key auto_increment,
		cname varchar(20)
);
create table t_stu(
     sid int primary key auto_increment,
		 sname varchar(20),
		 classid int -- 表示外键列。
);
-- add contraint 约束得名称 foregin key 外键得列名 REFERENCES 主表名(主键) 

-- alter table 从表名 add contraint 约束名随便起 foreign key 外键列名 references 主表(主键)
alter table t_stu add CONSTRAINT fk_stu_class FOREIGN key (classid) REFERENCES t_class(cid);

insert into t_class values(null,'QY145'),(null,'QY151');

insert into t_stu values(null,'李青华',2);
-- 因为classid=3 这个值 在我们得班级表中不存在。所以不能添加，但是
-- classid=1和为3 就可以添加
insert into t_stu values(null,'张青',1);

5. 连表查询

1.如果查询得结果在一张表中无法获取，需要关联多张表，
那么这时就需要连表查询。

2.连表查询大多数都作用在外键得基础上。--- 表与表之间有关联。

5.1 表与表之间存在得关系

1. 一对多: 在多得一方添加外键列
2. 多对多: 需要在创建一个中间表，该表中至少有两个外键列。

5.2 连表查询介绍

5.3 内连接

内连接演示—结果都是一样，只是语法不同。看个人习惯用哪个
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)

2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)
先建立两张表

/* Navicat Premium Data Transfer Source Server : qy151 Source Server Type : MySQL Source Server Version : 80011 Source Host : localhost:3306 Source Schema : mydb Target Server Type : MySQL Target Server Version : 80011 File Encoding : 65001 Date: 26/04/2022 15:32:17 */

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_emp
-- ----------------------------
DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '员工年龄',
  `job` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '岗位',
  `salary` int(11) NULL DEFAULT NULL COMMENT '薪水',
  `entrydate` date NULL DEFAULT NULL COMMENT '员工入职时间',
  `managerid` int(11) NULL DEFAULT NULL COMMENT '员工领导编号',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '员工所在部门编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_emp
-- ----------------------------
INSERT INTO `tb_emp` VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5);
INSERT INTO `tb_emp` VALUES (2, '张无忌', 20, '项目经理', 12500, '2005-12-01', 1, 1);
INSERT INTO `tb_emp` VALUES (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1);
INSERT INTO `tb_emp` VALUES (4, '韦一笑', 48, '开发', 11000, '2002-03-05', 2, 1);
INSERT INTO `tb_emp` VALUES (5, '常豫川', 43, '开发', 10500, '2004-09-01', 2, 1);
INSERT INTO `tb_emp` VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
INSERT INTO `tb_emp` VALUES (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3);
INSERT INTO `tb_emp` VALUES (8, '周芷若', 60, '会计', 48000, '2006-06-01', 7, 3);
INSERT INTO `tb_emp` VALUES (9, '丁敏君', 19, '出纳', 4000, '2009-06-01', 7, 3);
INSERT INTO `tb_emp` VALUES (10, '赵敏', 23, '市场总监', 14000, '2009-06-01', 1, 2);
INSERT INTO `tb_emp` VALUES (11, '鹿仗客', 56, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO `tb_emp` VALUES (12, '鹤比翁', 19, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO `tb_emp` VALUES (13, '方东白', 19, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO `tb_emp` VALUES (14, '张三丰', 88, '销售总监', 14000, '2004-06-01', 1, 4);
INSERT INTO `tb_emp` VALUES (15, '玉莲舟', 38, '销售', 4600, '2009-06-01', 14, 4);
INSERT INTO `tb_emp` VALUES (16, '宋远桥', 40, '销售', 4600, '2009-06-01', 14, 4);
INSERT INTO `tb_emp` VALUES (17, '陈友谅', 42, NULL, 2000, '2022-04-25', 1, NULL);

SET FOREIGN_KEY_CHECKS = 1;

/* Navicat Premium Data Transfer Source Server : qy151 Source Server Type : MySQL Source Server Version : 80011 Source Host : localhost:3306 Source Schema : mydb Target Server Type : MySQL Target Server Version : 80011 File Encoding : 65001 Date: 26/04/2022 16:27:20 */

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_dept
-- ----------------------------
DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_dept
-- ----------------------------
INSERT INTO `tb_dept` VALUES (1, '研发部');
INSERT INTO `tb_dept` VALUES (2, '市场部');
INSERT INTO `tb_dept` VALUES (3, '财务部');
INSERT INTO `tb_dept` VALUES (4, '销售部');
INSERT INTO `tb_dept` VALUES (5, '总经办');

SET FOREIGN_KEY_CHECKS = 1;

-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
-- 连表查询时，如果不使用连表条件则出现笛卡尔集。
-- 所谓笛卡尔集 就是A表中每一条记录关联B中中得每条记录
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
 -- 如果表的名字很长 可以为表起别名
select * from tb_emp e, tb_dept d where e.dept_id=d.id;
 

-- 显示连接: A表 inner join B表 on 连表条件。 
2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)
select * from tb_emp  inner join tb_dept on tb_emp.dept_id=tb_dept.id;
select * from tb_emp e inner join tb_dept d on e.dept_id=d.id;

-- 上面的 inner可以省略。
select * from tb_emp e  join tb_dept d on e.dept_id=d.id;

5.4 外连接

外连接演示
–1.查询emp表的所有数据, 和对应的部门信息(左外连接)

–2．查询dept表的所有数据，和对应的员工信息(右外连接)

-- 语法: select 查询列集 from A表 left join B表 on 连表条件
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- 2．查询dept表的所有数据，和对应的员工信息(右外连接)
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

5.5 自联查询

自己和自己相连接查询。
select * from A表 join A表 on 连表条件。
自连接
–1．查询员工及其所属领导的名字。

–2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来

-- 1．查询员工及其所属领导的名字。你要查询的结果再一张表中，但是还不能使用单表查询得到结果。
select a.name,b.name from tb_emp a join tb_emp b on a.managerid=b.id;
 
-- 2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

5.6 嵌套查询

一个查询的结果 作为另一个查询的条件 或者 临时表。
-- 查询市场部的员工信息-----
-- 子查询返回的结果一列一条记录。 这个时候可以用=
select * from tb_emp where dept_id=(select id from tb_dept where name='市场部')

-- 查询市场部和研发部员工的信息。in 
 -- -- 查询市场部和研发部员工的信息。
   -- a) 查询市场部和研发部的编号
	 select id from tb_dept where name in('市场部','研发部')

   -- b) 再员工表中根据部门编号查询员工信息
	 select * from tb_emp where dept_id in (select id from tb_dept where name in('市场部','研发部'))


-- 查询在“方东白”入职之后的员工信息
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白')

-- -- 查询比财务部所有人工资都高的员工信息。
  a)求出财务部中最高的工资。
	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部'
	
	b) 根据财务部最高工资查询其他员工信息
	select * from tb_emp where salary>(	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部')

5.7 组合查询

多个查询的结果 组合到一起。
sql union sql —>把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql—>把这两条sql查询的结果组合到一起。如果有重复记录，不合并。

注意: 这两条sql返回的字段必须一样。

select name from tb_emp where salary>8000
UNION 
select * from tb_emp where age>40;