今日内容

1. MySql基本查询

--ifnull(字段名称,预期值)-如果两个int类型数据求和,其中一个为null,那么结果就是null,使用ifnull来解决

-- 需求--查询姓名和成绩和
-- 创建表
CREATE TABLE test(id INT , -- 编号NAME VARCHAR(20) , -- 姓名age INT , -- 年龄math INT , -- 数学成绩english INT  -- 英语成绩
)
-- 输入数据
INSERT INTO test 
VALUES (1,'钟离',500,80,NULL) ,(2,'胡桃',18,90,100) ;
-- 查询姓名以及数学和英语成绩之和
SELECT NAME '姓名', -- 添加说明,AS可省略(math+english) '和' 
FROM test;

-- 解决方案,使用ifnull
SELECTNAME '姓名',(math+IFNULL(english,0)) '和'
FROMtest;

查询年龄是500或者年龄是18的所有信息--where条件可以使用or 或者java语言||in(值1,值2.....)

-- where条件可以使用 -or 或者 -java语言|| -in(值1,值2.....)
-- or
SELECT*
FROMtest
WHEREage=500 OR age=18 ;
-- ||
SELECT*
FROMtest
WHEREage=500 || age=18 ;
-- in
SELECT*
FROMtest
WHEREage IN(500,18) ;

查询某个字段为null的信息---->is null
查询字段不为null的信息---->is not null

-- 需求:查询英语成绩是null的所有信息
SELECT*
FROMtest
WHEREenglish IS NULL ;

查询某个字段不等于某个值 ---使用!=---mysql的 <>  

-- 需求:查询年龄不是500岁的所有信息
SELECT*
FROMtest
WHEREage != 500 ;
SELECT*
FROMtest
WHEREage <> 500 ;

2. DQL

2.1 DQL之模糊查询

模糊条件查询关键字--like语法select 字段列表 from 表名 where 字段名称 like '%XX%' --xx是查询条件%代表任意多个字符--开发中经常使用_代表任意一个字符'XX%' 或者 '%XX' 都是可以的

-- 需求:查询表中所有名字里带桃的人的信息
SELECT*
FROMtest
WHERENAME LIKE '%桃%' ;

-- 查询学生的姓名是两个字符的人的信息
SELECT*
FROMtest
WHERENAME LIKE '__' ; --两个下划线

2.2 DQL之聚合查询

DQL之聚合查询 ---结果是单行单例的COUNT(字段名称) ---使用最多  统计表的记录count里面 字段名称:一般是非业务字段--如果字段某个值是null,不会统计max(列名称):求最大值min(列名称):求最小值   avg(列名称):求当前列的平均值sum(列名称):求当前这列的总和聚合函数---完成sql语句嵌套 (单表操作/多表都可以)SELECT * FROMtest WHERE math > (SELECT AVG(math) FROMtest) ;

2.3 DQL之排序查询

DQL语句之排序查询 order by单独使用order by--select 字段列表 from 表名  order by  字段名称 排序规则;排序规则默认asc--升序排序desc--降序如果有条件where和order by同时存在--先满足where条件,再排序!

SELECT*
FROMtest
WHEREmath>70
ORDER BYmath ASC ,age DESC ;

2.4 DQL之分组查询

DQL语句之分组查询group by注意1)分组查询里面可以select 查询分组字段 2)分组group by后面不能使用聚合函数单独使用--select 字段列表 from 表名 group by 分组字段名称;如果分组查询带where条件--where条件和group by同时存在,where条件在group by的前面--group by的后面不能使用where,先满足where条件,才参与分组!

2.5 筛选

筛选having注意having的后面可以跟聚合函数having在group by后面 where是gruop by的前面

2.6 分页查询

分页查询limit语法select 字段列表 from 表名 limit 起始行数,每页显示的条数;起始行数= (当前页码数-1)*每页显示的条数;如果复合查询有where条件,还有limit,where在limit前面

3. 约束

约束约束用户操作数据库的非法行为

3.1 默认约束

-- 默认约束default
-- 没有插入字段的值默认值null,为了防止这种数据出现,可以在创建表的时候加入默认约束default,当没有插入这个字段,默认约束起作用!
CREATE TABLE testdefault(id INT ,NAME VARCHAR(20) ,sex VARCHAR(3) DEFAULT '女'  -- 性别不填写的话默认女
) ;
INSERT INTO testdefault (id , NAME) VALUES (1 , '钟离') ;
SELECT * FROM testdefault ;

3.2 非空约束

-- 非空约束 not null
-- 当前这个值不能为null,不能直接添加数据给一个null
CREATE TABLE testnotnull(id INT NOT NULL , -- 序号不能是空,写入值的时候必须赋值NAME VARCHAR(20) ,sex VARCHAR(3)
);
-- INSERT INTO testnotnull (NAME,sex) VALUES ('钟离','男') ;
-- Field 'id' doesn't have a default value
INSERT INTO testnotnull VALUES (1,'钟离','男') ;
SELECT * FROM testnotnull ;

3.3 唯一约束

-- 唯一约束 unique (当前这个值不能重复)
-- 限制id字段/有效身份信息(邮箱/手机号/身份证...)
CREATE TABLE testunique(id INT ,NAME VARCHAR(20) ,number INT UNIQUE -- 这个值输入时不能有重复!
);
-- insert into testunique values (1,'钟离',100773429),(2,'离',100773429) ;
-- Duplicate entry '100773429' for key 'number'
INSERT INTO testunique VALUES (1,'钟离',100773429),(2,'离',100773430) ;
SELECT * FROM testunique ;

3.4 主键约束

-- 主键约束primary key 
-- 特点非空且唯一
CREATE TABLE testprimarykey(id INT PRIMARY KEY , -- 主键,非空且唯一NAME VARCHAR(20) ,age INT
);
INSERT INTO testprimarykey VALUES (1,'钟离',15) ;
-- INSERT INTO testprimarykey VALUES (1,'钟',15) ;
-- Duplicate entry '1' for key 'PRIMARY'
-- INSERT INTO testprimarykey VALUES (null,'钟离',15) ;
-- Column 'id' cannot be null

3.5 自增长约束

-- 自增长约束 auto_increment
-- 一般自增长约束都是在主键字段上,保证唯一
-- 指定插入数据的值,下次在之前的值上继续自增1
CREATE TABLE testauto_increment(id INT PRIMARY KEY AUTO_INCREMENT , -- 一般主键和自增连用NAME VARCHAR(20) 
);
INSERT INTO testauto_increment (NAME) VALUES ('钟离');
INSERT INTO testauto_increment (NAME) VALUES ('钟离');
INSERT INTO testauto_increment (NAME) VALUES ('胡桃');
SELECT * FROM testauto_increment ;
​
-- mysql自带函数---查找数据库表中最后一次自增主键的值是多少
SELECT LAST_INSERT_ID();

3.6 外键约束

-- foreign key
/*问题:1)查询员工的所有信息---部门名称字段 冗余度大(重复度高)2)一张表描述了两个事情(员工信息,又有部门信息!)解决方案将这张表拆分两张表一张表员工信息一张表描述部门信息
*/
-- 创建一张部门表
CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号NAME VARCHAR(10) -- 部门名称
);
INSERT INTO dept(NAME) VALUES('往生堂'),('屋漏茶室'),('烟花店');
-- 创建一个员工表
CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号NAME VARCHAR(10) , -- 员工姓名gender VARCHAR(3) , -- 性别dept_id INT -- 部门编号
);
INSERT INTO employee(NAME,gender,dept_id)
VALUES
('钟离','男',1),
('胡桃','女',1),
('夜阑','女',2),
('申鹤','女',2),
('宵宫','女',3) ;
-- 插入了一个条数据:没有4号部门,但是依然能插入进去!
-- 防止出现问题,添加外键约束---让两个产生关联关系
-- 创建员工表的时候,同时添加外键约束!
/*外键作用的表---从表另一张表---主表创建表的时候添加外键constraint (声明)外键名foriegn key (从表的字段名称)references -- (关联)主表名(主键字段的名称)
*/
CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT, -- 编号NAME VARCHAR(10),  -- 姓名gender VARCHAR(3), -- 性别dept_id INT ,-- 部门编号CONSTRAINT -- 声明dept_emp_fk -- 外键名称FOREIGN KEY(dept_id) -- 作用在指定外键字段上REFERENCES -- 关联dept(id) -- 主表的主键字段);
-- 有了外键之后,直接修改或者删除主表数据,前提需要让从表的数据跟主表没有关联
-- 这个时候才能修改和删除主表数据!
​
-- 所以我们引出了!!!
​
-- 级联操作cascade
-- 级联删除/级联修改 on delete cascade /on update casade
-- 当修改/删除主表的数据,从表数据随之改动 
​
​
-- 将上面这个语句放在外键的后面
-- 添加外键的同时---添加级联修改和级联删除
ALTER TABLE employee 
ADD CONSTRAINT dept_emp_fk 
FOREIGN KEY (dept_id) 
REFERENCES dept(id) 
ON UPDATE CASCADE 
ON DELETE CASCADE ;
​
-- 直接修改主表数据,
-- 将1号部门改成2号部门,这个时候1号部门的员工,它部门编号变成2
UPDATE dept SET id = 2 WHERE id =1 ;
-- 直接删除主表数据,从表随之变化
-- 将3号部门解散
DELETE FROM dept WHERE id = 3 ;