第一次学习数据库的时候做的笔记,整理了一下发出来。
目录
1、创建库
create database 库名;
2、修改表名
alter table 原表名 rename to 新表名;
3、添加新字段
①默认在最后添加新字段:
alter table 表名 add 新字段名 数据类型(约束条件)
#<数据类型> 为所要添加的字段能存储数据的数据类型;
②若想在开头添加字段,则用first关键字:
alter table 表名 add 新字段名 数据类型(约束条件) first;
③若想在中间添加新字段,则用after关键字:
alter table 表名 add 新字段名 数据类型(约束条件) after 已存在的字段名;
4、MySQL服务的启动与停止
方式一:通过命令行
net start 服务名
net stop 服务名
方式二:计算机--右击--管理--服务
5、MySQL服务的登录和退出
登录:mysql (-h主机名 -p端口号)-u用户名 -p密码
#若连接本机或端口号为3306,括号中的可省略
退出:exit或ctrl+c
6、修改字段名
alter table 表名 change 旧字段名 新字段名 数据类型;
7、修改字段类型
alter table 表名 modify 字段名 数据类型;
8、删除字段
alter table 表名 drop 字段名;
9、删除数据表
drop table 数据表名;
10、删除数据库
drop database 数据库名;
11、数据表中插入数据并对指定字段赋值
insert into 表名(字段1,字段2) values(表达式1,表达式2);
12、数据标准插入数据并对所有字段赋值
insert into 表名 values(表达式1,表达式2,....); (注意一一对应)
13、数据表中插入多条数据
insert into 表名(字段1,字段2,...)
values('表达式11','表达式12',...),
('表达式21','表达式22',...),
.......
14、在表中修改单条数据的单个字段的值
update 表名 set 字段1=表达式1 【where 条件】; 没有where时更新所有数据
15、删除数据表的某一行数据
delete from 表名 where 字段名=值;
16、删除所有数据但保留表结构:
delete from 表名
17、数据库相关查询操作及案例
1.查看当前所有的数据库:
show databases;
2.打开指定的库:
use 库名;
3.查看当前库的所有表:
show tables from 库名;
4.查看其他库的所有表:
show tables from 库名;
5.创建表:
create tables 表名(
列名1 数据类型 字段属性,
列名2 数据类型 字段属性,
…
列名n 数据类型 字段属性
)
6.查看表结构:
desc 表名;
7.查看服务器的版本:
方法1:登录到mysql服务器:
select version( );
方法2:未登录到mysql服务器:
mysql --version
或
mysql --V
8.`字段` #用于区分字段,避免系统混淆,列又叫字段
9.基础查询:
语法:select 查询列表 from 表名;
特点:①查询列表可以是:表中的字段、常量值、表达式、函数
②查询的结果是一个虚拟的表格
1)查询表employees中的单个字段(举例):
select last_name from employees;
2)查询表中的多个字段:
select last_name,salary,email from employees;
3)查询表中的所有字段:
select * from employees;
4)查询常量值:
select 100;
select 'john';
#注:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5)查询表达式:
select 100%98;
6)查询函数:
select 函数名(参数列表);
#例:select version(3);
#必须有返回值
7)起别名:
方法一:使用as:
select last_name as 姓,first_name as 名 from employees;
方法二:使用空格:
select last_name 姓,first_name 名 from employees;
#案例:查询salary,显示结果为 out put(因为out为关键字,所以需要双引号)
select salary as "out put" from employees;
8)去重:
#案例:查询员工表中涉及到的所有部门的编号:
select distinct department_id from employees;
9)+号的作用:运算符
①select 100+90; #两个操作数都为数值型,则做加法运算
②select '123'+90; #其中一方为字符型,则系统会试图将字符型数值转换成数值型
如果转换成功,则继续做加法运算;
即:select '123'+90; 结果为213
如果转换失败,则将字符型数值转换成0
即:select 'john'+90; 结果为90
③select null+10; #只要其中一方为null,则结果肯定为null
#案例:查询员工名和姓连接成一个字段,并显示为 姓名:
select concat(last_name,first_name) as 姓名 from employees;
10)concat函数:拼接字符
select concat(字符1,字符2,...);
11)ifnull函数:判断某字段或表达式是否为null,如果为null,返回指定的值,否则返回原本的值
select ifnull(commission,0) from employees;
12)isnull函数:判断某字段或表达式是否为null,如果是,则返回1,否则返回0;
10.进阶查询:
语法:
select
查询列表
from
表名
where
筛选条件; #判断为true,则显示,否则pass掉
#执行顺序为:表名👉筛选条件👉查询列表
分类:
1.按条件表达式筛选:
①条件运算符:> < = != <>(不等于) >= <= <=>(安全等于)
2.按逻辑表达式筛选:
①逻辑运算符:&& || ! (Java)
and or not (MySQL)
3.模糊查询:
like:
between and:
in:
is null /is not null:
1)按条件表达式筛选:
#案例1:查询工资>12000的员工信息:
select
*
from
employees
where
salary>12000;
#案例2:查询部门编号不等于90号的员工名和部门编号:
select
last_name,
department_id
from
employees
where
department_id<>90;
2)按逻辑表达式筛选:
#案例1:查询工资在10000到20000之间的员工名、工资以及奖金:
select
last_name,
salary,
commission_pct
from
employees
where
salary>=10000 and salary<=20000;
#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息:
select
*
from
employees
where
not(department_id>=90 and department_id<=110) or salary>15000;
3)模糊查询:
①like :
特点:一般和通配符搭配使用
通配符: % 任意多个字符,包含0个字符
_ 任意单个字符
#案例1:查询员工名包含字符a的员工信息:
select
*
from
employees
where
last_name like '%a%';
#案例2:查询员工名中第三个字符为n,第五个字符为l的员工名和工资:
select
last_name,
salary
from
employees
where
last_name like '__n_l%';
#案例3:查询员工名中第三个字符为_的员工名:
select
last_name
from
employees
where
last_name like '_\_%'; #转义字符:\
(或者这样写:last_name like '_$_%' escape '$'; #即自定义转义字符)
②between and:
#案例1:查询员工编号在100到120之间的员工信息:
select
*
from
employees
where
employees_id between 100 and 120;
③in: 用于判断某字段中的值是否属于in列表中的某一项
案例1:查询员工的工种编号是:IT、AD、VP中的一个员工名和工种编号:
select
last_name,
job_id
from
employees
where
job_id in( 'IT' , 'AD' , 'VP' ); #in列表的值必须为同类型或兼容
④is null: =或<>不能用于判断null值
is null 或 is not null 可以判断null值
#案例1:查询没有奖金的员工和奖金率:
select
last_name,
commission_pct
from
employees
where
commission_pct is null; #不能直接用=,因为=不能判断null值
(若查询有奖金的,改为:commission_pct is not null;)
⑤安全等与 <=>(即为等于):既可以判断null值,又可以判断普通数值
#案例1:查询没有奖金的员工和奖金率:
select
last_name,
commission_pct
from
employees
where
commission_pct <=> null;
#案例2:查询工资>12000的员工信息:
select
*
from
employees
where
salary <=> 12000;
11.排序查询:
引入:select 字段名 from 表名;
语法:
select 查询列表
from 表名
【where 筛选条件】
order by 排序列表 【asc/desc】
特点:
①asc升序、desc降序,若不写,默认升序
②order by子句中可以支持单个字段、多个字段、表达式、函数、别名
③order by子句中一般是放在查询语句的最后面,limit字句除外
#案例1:查询员工信息,要求工资从高到低排序:
select * from employees order by salary desc;
#案例2:查询部门编号>=90的员工信息,按入职时间的先后进行排序【添加筛选条件】
select
*
from
employees
where
department_id>=90
order by
hiredate asc;
#案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
select
*,
salary*12*(1+ifnull(commission_pct,0)) 年薪
from
employees
order by
salary*12*(1+ifnull(commission_pct,0)) desc;
#案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
select
*,
salary*12*(1+ifnull(commission_pct,0)) 年薪
from
employees
order by
年薪 desc;
#案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
select
length(last_name) 字节长度,
last_name,
salary
from
employees
order by
length(last_name) desc;
#案例6:查询员工信息,要求先按工资升序,再按员工编号降序【按多个字段排序】
select
*
from
employees
order by
salary asc,
employees_id desc;
#以前面的为主以此排序
12.常见函数:
概念:类似java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
调用:select 函数名(实参列表) 【from 表】;
#若参数列表用到了表中的字段,则需要加中括号中的语句
分类:
①单行函数(处理) 如 concat、length、ifnull等
②分组函数(统计):又称统计函数、聚合函数、组函数
1)字符函数:
①length():获取参数值的字节个数
#案例1:select length('john'); #4
#案例2:select length('张三丰hahaha'); #15,汉字占3个字节
②concat():拼接字符串
#案例1:select concat(last_name,'_',first_name) 姓名 from employees;
③upper()转大写、lower()转小写:
#案例1:select upper('john'); #JOHN
#案例2:将姓转换成大写,名转换成小写,然后拼接:
select concat(upper(last_name),lower(first_name)) 姓名 from employees;
④substr()/substring():⭐索引从1开始
#案例1:截取从指定索引处开始到后面所有字符:
select substr('123456789',7) out_put; #789
#案例2:截取从指定索引处指定字符长度的字符:
select substr('123456789',1,3) out_put; #123
#案例3:姓名中首字符大写,其他字符小写,并用_拼接,显示出来:⭐#有疑问
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) 姓名
from employees;
⑤instr():返回字串第一次出现的索引,若找不到则返回0
#案例1:select instr('122334','3'); #4
⑥trim():去掉前后空格或指定字符
#案例1:select trim(' xxx '); #xxx
#案例2:select trim('1' from '111xx11x1111'); #xx11x
⑦lpad():用指定的字符实现左填充指定长度
#案例1:select lpad('123',5,'*'); #**123
#案例2:select lpad('123',2,'*'); #12
⑧rpad():右填充
#案例1:select rpad('123',5,'*'); #123**
⑨replace():替换
#案例1:select replace('12345','12','27'); #27345
2)数学函数:
①round():四舍五入
#案例1:select round(1.65); #2
#案例2:select round(1.567,2); #1.57,保留两位小数
②ceil():向上取整,返回>=该参数的最小整数
#案例1:select ceil(-1.02); #-1
③floor():向下取整,返回<=该参数的最大整数
#案例1:select floor(-9.99); #-10
④truncate():截断
#案例1:select truncate(1.669,1); #1.6,从第一个小数开始截断,后面的舍去
⑤mod():取余,与%一样
#mod(a,b) :a-a/b*b ⭐a/b 没有浮点数,结果为整数
#案例1:select mod(10,3); #1
3)日期函数:
①now():返回当前系统日期+时间
②curdate():返回当前系统日期,不包含时间
③curtime():返回当前时间,不包含日期
④可以获取指定的年、月、日day、时hour、分minute、秒:
#案例1:select year(now()) 年;
#案例2:select year('2002-1-27') 年;
#案例3:select year(字段名) 年 from 表名;
#案例4:select month(now()) 月;
#案例5:select monthname(now()) 月; ⭐结果为月份的英文
⑤str_to_date:将日期格式的合法字符转换成指定格式的日期
#str_to_date('7-29-2022','%m-%d-%Y); #2022-7-29
⭐:
%Y:四位的年份
%y:两位的年份
%m:月份(01,02...12)
%c:月份(1,2...12)
%d:日(01,02,....)
%H:小时(24小时制)
%h:小时(12小时制)
%i:分钟(00,01,...59)
%s:秒(00,01,...59)
#案例1:查询入职日期为1992-4-3的员工信息:
①select * from employees where hirdate = '1992-4-3';
②select * from employees where hirdate = str_to_date('4-3 1992','%c-%d %Y');
⑥date_format():将日期转换成字符
#date_format('2022/7/29','%Y年%m月%d日'); #2022年7月29日
4)流程控制函数:
①if函数:实现 if else 的效果
# select if(表达式,'true时返回的值','false时返回的值');
②case函数:
#语法1:case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
......
esle 要显示的值n或语句n;
end
⭐如果是值就不用加分号
没有其他条件的话else可省略
⭐案例:查询员工的工资,要求:
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门显示的工资为原工资
select salary 原工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;
#语法2:case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
......
else 条件n then 要显示的值n或语句n;
end
⭐如果是值就不用加分号
没有其他条件的话else可省略
select salary ,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;
5)分组函数:用于统计使用,又称聚合函数或统计函数或组函数
#分类:sum,avg 平均值,max,min,count 计算个数
#特点:
①sum、avg一般用于处理数值型、max、min、count可以处理任意类型
②以上分组函数都忽略null值
③可以和distinct搭配
④一般使用count(*)用作统计行数
⑤和分组函数一同查询的字段要求是group by后的字段
13.分组查询:
语法:
select 分组函数 列(要求出现在group by的后面)
from 表名
【where 筛选条件】
group by 分组的列表
【order by 字句】
注意:查询列表必须特殊,要求是分组函数和group by 后出现的字段
特点:
①分组前查询:数据源为原始表、位置在group by 字句前面、关键字为where
②分组后查询:数据源为分组后的结果集、位置在group by 字句后面、关键字为having
③group by 字句支持单、多个字段分组,逗号隔开,无顺序
④可添加排序(放在最后)
⭐分组函数做条件一定放在having子句中
#优先考虑分组前筛选
案例:
1)查询每个工种的最高工资:
select
max(salary),
job_id
from
employees
group by
job_id;
2)查询每个位置上的部门个数:
select
count(*),
location_id
from
departments
group by
location_id;
3)查询邮箱中包含a字符的,每个部门的平均工资
select
avg(salary),
department_id
from
employees
where
email like '%a%'
group by
location_id;
4)查询每个领导手下有奖金的员工的最高工资:
select
max(salary),
manager_id
from
employees
where
commission_pct is not null
group by
manager_id;
5)查询哪个部门的员工个数>2(需要先查询每个部门的员工个数,在此基础上查询哪个>2)
①
select
count(*),
department_id
from
employees
group by
manager_id
having⭐
count(*)>2;
⭐where是对分组前的每一行数据进行筛选
⭐having是对分组后的每一行进行筛选,筛选条件是每一组的群体特征,如:一组的总和、平均值
6)查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
①查询每个工种有奖金的员工的最高工资
select
max(salary),
job_id
from
employees
where
commission_pct is not null
group by
job_id;
②根据①结果继续筛选,最高工资>12000
select
max(salary),
job_id
from
employees
where
commission_pct is not null
group by
job_id
having
max(salary)>12000;
7)查询领导编号>102且其手下员工的最低工资>5000的领导编号是什么,以及其中的最低工资
①查询每个领导手下员工的最低工资:
select
min(salary),
manager_id
from
employees
group by
manager_id;
②领导编号>102:
select
min(salary),
manager_id
from
employees
where
manager_id>102
group by
manager_id;
③最低工资>5000:
select
min(salary),
manager_id
from
employees
where
manager_id>102
group by
manager_id
having
min(salary)>5000;
#按表达式或函数分组:
案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
①查询每个长度的员工个数:
select
count(*),
length(last_name) 名字长度
from
employees
group by
length(last_name);
②员工个数>5:
select
count(*),
length(last_name) 名字长度
from
employees
group by
length(last_name) (支持别名)
having count(*)>5; (支持别名)
#按多个字段查询:
案例:查询每个部门中每个工种的员工的平均工资(可添加排序):
select
avg(salary),
department_id,
job_id
from
employees
group by
job_id,
department_id;
⭐⭐⭐⭐⭐查差距用difference
如果给表起了别名,则查询的字段就不能使用原来的表名去限定
14.连接查询:
含义:又称多表查询。当查询的字段涉及多个表时可用
笛卡尔乘积现象:表1 m行,表2 n行,则结果有m*n行
分类:
1)等值连接:
①多表等值连接的结果为多表的交集部分
②n表连接,至少需要n-1个连接条件
③建议为表起别名
#案例:查询员工名和对应的部门名
select last_name,department_name
from empolyees,departments
where employees.department_id=department.department_id;
2)非等值连接
3)自连接:
#案例:查询 员工名和其上级的名字
select e.employees_id, e.last_name, m.employees_id, m.last_name
from employees e, employees m
where e.manager_id = m.employees_id;
⭐sql99语法:
select 查询列表
from 表1 别名 【连接类型:内、外等】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
1)内连接:inner(可省略)(交集部分)
分类:等值连接、非等值连接、自连接
①等值连接:
案例1:查询部门个数>3的城市名和部门个数(添加分组和筛选)
select city, count(*) 部门个数
from location l
inner join departerment d
on d.location_id=l.location_id
group by city
having count(*)>3;
案例2:查询哪个部门的员工个数>3的部门名和员工个数,按个数降序
select count(*) 部门个数,department_name
from employees e
inner join departerments d
on d.departerment_id=e.departerment_id
group by department_name
having count(*)>3
order by count(*) desc;
案例3:查询员工名、部门名、工种名,按部门名降序(三表连接)
select last_name,department_name,job_title
from employees e
inner join departerments d on d.departerment_id=e.departerment_id
inner join jobs j on j.job_id=e.job_id
order by department_name desc;
②非等值连接:
案例:查询每个工资级别个数>20的个数,按工资降序
select count(*),grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc;
③自连接:
案例:查询 姓名中包含字符k的员工名和其上级的名字
select e.last_name, m.last_name
from employees e
join employees m
on e.manager_id = m.employees_id
where e.last_name like '%k%';
2)外连接:用于查询一个表中有,另一个表没有
特点:
①外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有与它匹配的,则显示null值
外连接查询结果=内连接结果+主表中有而从表中没有的记录
②左外连接:left join 左边的是主表
右外连接:right join 右边的是主表
③左外和右外交换两个表的顺序,可以实现同样的效果
左外:left 【outer 可省略】
查询哪个部门没有员工
select d.*,e.employees_id
from departments d
left outer join employees e
on d.department_id=e.department_id
where e.employee_id is null;
右外:right 【outer】
查询哪个部门没有员工
select d.*,e.employees_id
from employees e
right outer join departments d
on d.department_id=e.department_id
where e.employee_id is null;
全外:full 【outer】(两表并集+内连接结果)
3)交叉连接:cross (结果为笛卡尔乘积)
15.子查询:出现在其他语句中的select语句
分类:
按子查询出现的位置:
select后面:只支持标量子查询
from后面:支持表子查询(将子查询结果充当一张表,要求必须起别名)
where或having后面:支持标量子查询(单行)⭐、列子查询(多行)⭐、行子查询
exists后面(相关子查询):表子查询
exists(完整的查询语句),结果为1或0
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集为一列多行)
行子查询(结果集为一行多列)
表子查询(结果集一般为多行多列)
特点:
标量子查询,一般搭配单行操作符。如:> < >= <= = <>
列子查询,一般搭配多行操作符。如:in/not in any/some all
子查询优先于主查询执行,主查询的条件用到子查询结果
案例1:where或having后面
⭐标量子查询:
1:谁的工资比Abel高?
①查询Abel工资
select salary
from employees
where last_name='Abel';
②查员工,满足salary>①结果
select * from employees
where salary >
(
select salary
from employees
where last_name='Abel'
);
⭐列子查询(多行)
1:返回location_id是1400或1700部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
select department_id
from departments
where location_id in(1400,1700);
②查询员工姓名,要求部门号是①列表中的某一个
select last_name
from employees
where department_id in
(
select department_id
from departments
where location_id in(1400,1700)
);
行子查询(结果为一行多列或多行多列)
1:查询员工编号最小并且工资最高的员工信息
select *
from employees
where (employees_id,salary)=
(
select min(employees_id),max(salary)
from employees
);
案例2:在select后面
①查询每个部门的员工个数
select d.*,
(
select count(*)
from employees e
where e.department_id=d.department_id
) 个数
from department d;
案例3:在from后面
查询每个部门的平均工资的工资等级
①查询每个部门的平均工资:
select avg(salary),department_id
from employees
group by departmrnt_id;
②连接①的结果集和job_grades表,筛选条件平均工资between lowest_sal and highest_sal
select ag_dep.*,g.grade_level
from
(
select avg(salary),department_id
from employees
group by departmrnt_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;
案例4:在exists后面(相关子查询)(是否存在)
----------------------------我是一条分割线-----------------------------