mysql-学习笔记

-- 根据需求创建表（设计合理的数据类型、长度）
-- 设计一张员工信息表，要求如下：
-- 1.编号（纯数字）
-- 2.员工工号（字符串类型，长度不超过10位）
-- 3.员工姓名（字符串类型，长度不超过10位）
-- 4.性别（男/女，存储一个汉字）
-- 5.年龄（正常人年龄，不可能存储负数）
-- 6.身份证号（二代身份证号均为18位，身份证中有X这样的字符）
-- 7.入职时间（取值年月日即可）

-- 创建表：
create table if not exists employee(
id int comment "编号",
workno varchar(10) cmment "工号",
name varchar(10) comment "姓名",
gender char(1) comment "性别",
age tinyint unsigned comment "年龄",
idcard char(18) comment "身份证号",
workaddress varchar(30) comment "工作地址",
entrydate date comment "入职时间"
) comment "员工表";

-- 添加数据：
insert into employee (id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
(2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
(3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),
(4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),
(5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
(6, '6', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),
(7, '7', '范遥', '男', 40, '123456789212345670', '北京', '2005-05-01'),
(8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
(9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
(10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
(11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),
(12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
(13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
(14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
(15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
(16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');

-- DDL-数据库操作：
-- 1.查询
-- 查询所有数据库--show databases;

-- 查询当前操作的数据库--select database();

-- 2.创建数据库
-- create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

-- 3.删除数据库
-- drop database [if exists] 数据库名;

-- 4.使用数据库
-- use 数据库名;

-- DML数据操作语言：
-- 添加数据（insert）
-- 1.给指定字段添加数据
-- insert into 表名（字段名1，字段名2，...）values （值1，值2，...）;

-- 2.给全部字段添加数据
-- insert into 表名 values （值1，值2...);

-- 3.批量添加数据
-- insert into 表名（字段名1，字段名2，...）values （值1，值2，...), (值1，值2，...), (值1，值2，...);
-- insert into 表名 values （值1，值2，...), (值1，值2，...), (值1，值2，...);

-- 修改数据（update）
-- update 表名 set 字段名1=值1，字段名2=值2，...[where 条件];
-- 1.修改id为1的数据，将name修改为ithema
update employee set name = "ithema" where id = 1;

-- 2.修改id为1的数据，将name修改为小昭，gender修改为“女”
update employee set name = "小昭", gender = "女" where id = 1;

-- 3.将所有员工的入职日期修改为2008-01-01
update employee set entrydate = "2008-01-01";

-- 删除数据（delete）
-- delete from 表名 [where 条件]; --->只删除表的内容。
-- truncate table 表名; --->类似于drop table，可以理解为先将整个表删除，然后再创建该表。

-- 1.删除gender为女的员工
delete from employee where gender = "女";

-- 2. 删除所有员工数据
delete from employee;

-- 删除字段名（列）
-- 语法：alter table 表名 drop column 字段名;
-- 删除employee表中的birthday字段
alter table employee drop column birthday;

-- DQL数据查询语言：
-- 查询关键字：select
-- DQL语法：select--字段列表 from--表名列表 where--条件列表 group by--分组字段列表
-- havng--分组后条件列表 order--排序字段列表 limit--分页参数
-- 基本查询
-- 1.查询多个字段
-- select 字段1，字段2，字段3... from 表名;
-- select * from 表名;
-- 2.设置别名
-- select 字段1 [as 别名1]，字段2 [as 别名2]... from 表名;
-- 3.去除重复记录
-- select distinct 字段列表 from 表名;

-- 条件查询（where）
-- 语法--select 字段列表 from 表名 where 条件列表;
-- 1.查询年龄等于88的员工
select * from employee where age = 88;

-- 2.查询年龄小于20的员工
select * from employee where age < 20;

-- 3.查询年龄小于等于20的员工
select * from employee where age <= 20;

-- 4.查询没有身份证号的员工信息
select * from employee where idcard is null;

-- 5.查询有身份证号的员工信息
select * from employee where idcard is not null;

-- 6.查询年龄不等于88的员工信息
select * from employee where age <> 88;
select * from employee where age != 88;

-- 7.查询年龄在15岁（包含）到20岁（包含）的员工信息
select * from employee where age >= 15 and age <= 20;
select * from employee where age >= 15 && age <= 20;
select * from employee where age between 15 and 20;

-- 8.查询性别为女，且年龄小于25岁的员工信息
select * from employee where gender = "女" and age < 25;

-- 9.查询年龄等于18或20或40的员工信息
select * from employee where age = 18 or age = 20 or age = 40;
select * from employee where age in (18, 20, 40);

-- 10.查询姓名为两个字的员工信息
select * from employee where name like '___';

-- 11.查询身份证号最后一位是X的员工信息
select * from employee where idcard like "%X";

-- 聚合函数（count, max, min, avg, sum）
-- 语法：select 聚合函数(字段列表) from 表名;
-- 1.统计该企业员工数量
select count(*) from employee;
select count(idcard) from employee;

-- 2.统计该企业员工的平均年龄
select avg(age) from employee;

-- 3.统计该企业员工的最大年龄
select max(age) from employee;

-- 4.统计该企业员工的最小年龄
select min(age) from employee;

-- 5.统计西安地区员工的年龄之和
select sum(age) fom employee where workaddress = "西安";

-- 分组查询（group by）
-- 语法：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
-- where与having的区别
-- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
-- 判断条件不同：where不能对聚合函数进行判断，而having可以。
-- 注意：
-- （1）执行顺序：where > 聚合函数 > having
-- （2）分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
-- 1.根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from employee group by gender;

-- 2.根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from employee group by gender;

-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) from employee where age < 45 group by workaddress having count(*) >= 3;

-- 排序查询（order by）
-- 语法：select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
-- 排序方式：
-- ASC：升序（默认值）
-- DESC：降序
-- 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
-- 1.根据年龄对公司的员工进行升序排序
select * from employee order by age asc;

-- 2.根据入职时间，对员工进行降序排序
select * from employee order by entrydate desc;

-- 3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select * from employee order by age asc, entrydate desc;

-- 分页查询（limit）
-- 语法：select 字段列表 from 表名 limit 起始索引，查询记录数;
-- 注意：（1）起始索引从0开始，起始索引=（查询页码-1）× 每页显示记录数，
-- （2）分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是limit,
-- （3）如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

-- 1.查询第一页员工数据，每页显示10条记录
select * from employee limit 0, 10;
select * from employee limit 10;

-- 2.查询第二页员工数据，每页显示10条记录----->（页码-1）× 显示记录数
select * from emplotee limit 10, 10;

-- 案例：按照需求完成如下DQL语句编写
-- 1.查询年龄为20，21，22，23岁的女性员工信息
select * from employee where gender = "女" and age in(20, 21, 22, 23);

-- 2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工
select * from employee where gender = "男" and (age bewteen 20 and 40) and name like "___";

-- 3.统计员工表中年龄小于60岁的，男性员工和女性员工的人数
select gender, count(*) from employee where age < 60 group by gender;

-- 4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
select name, age, entrydate from employee where age <= 35 order by age asc, entrydate desc;

-- 5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
select * from employee where gender = "男" and (age between 20 and 40) order by age asc, entrydate asc limit 0, 5;

-- DCL数据控制语言
-- DCL-管理用户
-- 1.查询用户
use mysql;
select * from user;

-- 2.创建用户
create user "用户名"@"主机名" identified by "密码";

-- 3.修改用户密码
alter user "用户名"@"主机名" identified with mysql_native_password by "新密码";

-- 4.删除用户
drop user "用户名"@"主机名";

-- 创建用户itcast，只能够在当前主机localhost访问，密码123456
create user "itcast"@"localhost" identified by "123456";

-- 创建用户heima，可以在任意主机访问该数据库，密码123456
create user "heima"@"%" identified by "123456";

-- 修改用户heima的访问密码为1234
alter user "heima"@"%" identified with mysql_native_password by "1234";

-- 删除itcast@localhost用户
drop user "itcast"@"localhost";

-- DCL-权限控制
-- 注意：多个权限之间，使用逗号分隔；授权时，数据库名和表名可以使用*进行通配，代表所有。
-- 1.查询权限
-- show grants for "用户名"@"主机名";

-- 2.授予权限
-- grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";

-- 3.撤销权限
-- revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";

-- 字符串函数
-- concat(s1, s2, s3, ......sn) --->字符串拼接
-- lower(str) --->将字符串str全部转为小写
-- upper(str) --->将字符串str全部转为大写
-- lpad(str, n, pad) --->左填充，用字符串pad对str的左边进行填充，达到n个字符的长度
-- rpad(str, n, pad) --->右填充，用字符串pad对str的右边进行填充，达到n个字符的长度
-- trim(str) ---> 去掉字符串str头部和尾部的空格
-- substring(str, star, len) --->返回从字符串str从star位置起的len个长度的字符串

-- 练习：根据需求完成以下SQL编写
-- 由于业务需求变更，企业员工的工号统一为5位数，目前不足5位数的全部在前面补0，比如：1号员工的工号应该为00001
update employee set workno = lpad(workno, 5, "0");

-- 数值函数
-- ceil(x) --->向上取整
-- floor(x) --->向下取整
-- mod(x, y) --->返回x/y的模
-- rand() --->返回0~1以内的随机数
-- round(x, y) --->求参数x的四舍五入的值，保留y位小数

-- 练习：根据需求完成以下SQL编写
-- 通过数据库的函数，生成一个6位数的随机验证码
select lpad(round(rand()*1000000, 0), 6, "0");
select rpad(round(rand()*1000000, 0), 6, "0");

-- 日期函数
-- curdate() --->返回当前日期
-- curtime() --->返回当前时间
-- now() --->返回当前日期和时间
-- year(date) --->获取指定date的年份
-- month(date) --->获取指定date的月份
-- day(date) --->获取指定date的日期
-- date_add(date, INTERVAL expr type) --->返回一个日期/时间值加上一个时间间隔expr后的时间值
-- datediff(date1, date2) --->返回起始时间date1和结束时间date2之间的天数

-- 练习：根据要求完成以下SQL编写
-- 查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as "entrydays" from employee order by entrydays desc;

-- 流程函数
-- if(value, t, f) --->如果avlue为true,则返回t，否则返回f
-- ifnull(value1, value2) --->如果value1不为空，返回value1，否则返回value2
-- case when [value1] then [res1] ...... else[default] end --->如果value1为true，返回res1，......否则返回default默认值
-- case [expr] when [value1] then [res1] ...... else [default] end --->如果expr的值等于value1,返回res1，......否则返回default默认值

-- 约束
-- 自增长约束(auto_increment) --->自动增长
-- 非空约束(not null) --->限制该字段的数据不能为null
-- 唯一约束(unique) --->保证该字段的所有数据都是唯一、不重复的
-- 主键约束(primary key) --->主键是一行数据的唯一标识，要求非空且唯一
-- 默认约束(default) --->保存数据时，如果未指定该字段的值，则采用默认值
-- 检查约束(check) ---> 保证字段值满足某一个条件
-- 零填充约束(zerofill) --->默认填充数字"0"
-- 外键约束(foreign key) --->用来让两张表的数据之间建立连接，保证数据的一致性和完整性

-- 约束演示
-- 1.创建表时添加约束
create table if not exists user(
id int primary key auto_increment comment "主键（自动增长）",
name varchar(10) not null unique comment "姓名（非空唯一）",
age int check(age > 0 and age <=120) comment "年龄",
status char(1) default "1" comment "状态",
gender char(1) comment "性别") comment "用户表";

-- 2.在定义字段之后再指定主键
-- 语法：create table 表名(...... [constraint<约束名>] primary key [字段名]);
create table emp1(
id int,
name varchar(20),
deptid int,
salary double,
constraint pk1 primary key(id) -- constraint pk1 可以省略
);

-- 3.在创建表之后再添加主键约束
-- 语法1：添加单个主键 alter table 表名 add primary key(字段名);
-- 语法2：添加多个主键（联合主键）alter table 表名 add primary key(字段1, 字段2, 字段3......);

-- 外键约束
-- 添加外键
-- create table 表名(字段名 数据类型, ......[constraint] [外键名称] foreign key (外键字段名称) references 主表 (主表列名));
-- alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);

-- 删除外键
-- alter table 表名 drop foreign key 外键名称;