青春由磨砺而出彩,人生因奋斗而升华
2020年5月30日
开发者为瑞典MySQLAB公司,08年1月16日被SUN收购,SUN09年有被Oracle收购。
DBMS也叫数据库管理系统(DataBase Messagement System),数据库是通过DBMS创建和操作的容器。
SQL也叫结构化查询语言(Structure Query Language),专门用来和数据库软件通信的语言。
为什么要学习数据库?
1、持久化数据
2、方便检索
3、存储大量数据
4、共享、安全
5、通过组合分析,获取新的数据
数据库存储数据的特点
1、数据先放在表中,表再放入库中
2、一个库可以有多张表。每张表都有自己唯一的标识名
3、一张表的设计,类似于java中类的设计
表中字段的设计,类似于属性的设计
表中的单条记录,类似于对象
表中所有的记录,类似于对象的集合
orm (object relation mapping)对象关系映射
mysql 特点:
①体积小、安装较方便
②开源、免费
③性能高、稳定性好
④兼容性好
-
关于MySQL的卸载
MySQL的卸载,windows+R输入cmd 再输入services.msc找到MySQL,如下图所示
随后在控制面板中卸载。在自己安装文件目录下清除残余文件,如果当初是默认安装的,在C磁盘里找到program.data文件(改文件为隐藏文件),可在下图中查看操作
-
安装
找到安装包如下:双击打开
下一步就可以了,安装目录切记不要出现中文!
安装完成之后,需要配置,如果没有点击配置的话,找到刚才安装文件的目录,找到bin/MySQLInstanceConfig.exe双击,下一步选择详细配置(Detailed Configuation),下一步选择电脑类型,(操作系统会给你分配不同的内存),下一步数据库的功能,下一步支持连接人数,下一步遵循TCP/IP网络环境,选择默认端口3306,下一字符集,默认是拉丁,手动支持UTF-8,下一步给服务起一个名字,默认为MySQL,添加环境变量;下一步密码和确认密码,下一步验证安装配置是否成功,Execute.如果未响应,打开任务管理器重新配置。
显示上图即为成功。 -
启动MySQL的两种方法
1.计算机右键+管理+服务和应用程序+服务+mysql
2.(以管理员身份运行)cmd命令+net start mysql(此处mysql为自己配置是起的名字)
net stop mysql 为停止 -
mysql客户端登录
cmd命令+mysql -hlocalhost -p3360 -uroot(root为我的用户名,可填写你自己的用户名) -p +mysql密码
其中;和\g在mysql中为命令结束符。
-客户端退出
-
mysql常用命令
1.show databases;
一些自己的数据库
2.use mysql;
3.show tables;
4.查看当前数据库 select database();
5.建立一张表,存储学员信息(学号,姓名,性别,出生日期)
使用show tables;显示stuinfo
6.查看新建表的描述 desc stuinfo; (此处的desc可理解为describe)
7.查看表数据 select * from stuinfo;
此时未添加数据,为空。
8.插入数据
insert into stuinfo values(,‘程序员’,‘男’,‘1999-5-30’);
可能会出现字符集错误问题,使用 set names gbk;后 在运行insert into stuinfo values(,‘程序员’,‘男’,‘1999-5-30’);
查看数据如下图:
9.更改其中的某条信息,例如修改程序员的性别
update stuinfo set gender=‘女’ where stuid=1;
10.删除某条信息(例如删除程序员)
delete from stuinfo where stuid=1;
11.修改表的结构(添加一个邮箱)
alter table stuinfo add column email varchar(20);
12.干掉这个表stuinfo
drop table stuinfo;
13.整个操作结束
exit;或者Ctrl+c
14.注释1.#单行注释 2.--+空格 3./* */
分类:
DDL也叫数据定义语言(Data Definition Language),用来定义数据对象:库,表列等;create /drop/alter
DML也叫数据操作语言(Data Manipulation Language),用来定义数据库记录;insert / update/delete
DCL也叫数据控制语言(Data control Language),用来定义访问权限和安全级别
DQL也叫数据查询语言(Data Query Language),用来查询记录 select
-
基础查询语句
进阶一:
select 查询列表 from 表名;
特点:
1.查询的结果集,是一个虚拟表
2.select 查询列表 类似于System.out.println(打印内容);区别是select后面跟的查询列表,可以有多个部分组成,中间用逗号隔开 例如:select 字段1,字段2,表达式 from 表;
3.执行顺序
①from字句
②select 字句
4.查询列表可以是字段、变量、表达式、常量、函数等查询常量:select 100; 查询表达式:100%3; 查询单个字段:select stuid from stuinfo; 查询多个字段:select stuid,stuname,gender from stuinfo; 查询所有的字段:select * from stuinfo;用*号语义性不强 F12遇到多查询的时候自动对齐格式,比较美观。 查询函数(调用函数,获取返回值) select database();查询数据库 select version();查询数据库版本 select user();查询当前用户 起别名: 方式一使用as关键字 select user() as 用户名; select user() as '用户名'; select user() as "用户名"; 例如:什么时候用双引号,select stuname as "姓 名" from stuinfo; 方式二使用空格 select user() 用户名; select user() '用户名'; select user() "用户名"; 例如:什么时候用双引号,select stuname "姓 名" from stuinfo; 查询first_name和last_name拼接成的全名,最终起一个别名为:姓名 mysql中+只有加法运算作用,如果无法转换,则直接当做0处理;此时用concat拼接函数 select concat(first_name,last_name) as "姓 名" from stuinfo; distinct(意思为明显的,独特的)的使用:把重复的去掉 需求:查询员工涉及到的部门编号有哪些 select distinct department_id from employees; 查看表的结构:desc (describe) 和show columns show columns from stuinfo; desc stuinfo;
案例:显示出表stuinfo的全部列,各个列之间用逗号连接,列头显示为 OUT_PUT
select concat(stuid,',',stuname,',',gende,',',email) as OUT_PUT
form stuinfo;
结果可能有空值,为了摆脱这种null的困扰,此时用ifnull
select concat(stuid,',',stuname,',',gender,',',ifnull(email,'')) as OUT_PUT
form stuinfo;
ifnull(表达式1,表达式2)
表达式1:可能为null的字段或表达式
表达式2:如果表达式1为null,则最终结果显示的值
功能:如果表达式1为null,则显示表达式2;否则显示表达式1
进阶二:条件查询
语法:select 查询列表
from 表名
where 筛选条件;
执行顺序:
①from子句
②where子句
③select子句
特点:
1、按条件表达式筛选
关系运算符有:> < >= <= = <>(不等于和!=一样)可见SQL的兼容性强,不建议使用
案例:查询部门编号不是100的员工信息
select *
from employees
where department_id <> 100;
案例:查询工资<15000的姓名、工资
select last_name,salary
from employees
where salary < 15000;
2、按逻辑表达式筛选
逻辑运算符:and or not (&& || !)
案例:查询部门编号不是50-100之间的员工姓名、部门编号、邮箱
方式一:
select last_name,department_id,email
from empoyees
where department_id<50 or department_id>100;
方式二:
select last_name,department_id,email
from empoyees
where not(department_id>=50 and department_id<=100);
案例:查询出奖金率>0.03 或者 员工编号在60-110之间的员工信息
select *
from employees
where commission_pct>0.03 or (employee_id >=60 and emloyee_id <= 110);
3、模糊查询
like (一般和通配符搭配使用,用于字符型的数据进行查询)
not like
常见的通配符:_ %
_任意单个字符
% 任意多个字符
案例:查询姓名中包含字符a的员工信息
select *
from emplyees
where last_name like '%a%';
案例:查询姓名中最后一个字符为e的员工信息
select *
from emplyees
where last_name like '%e';
案例:查询姓名中第一个字符为e的员工信息
select *
from emplyees
where last_name like 'e%';
案例:查询姓名中第三个字符为x的员工信息
select *
from emplyees
where last_name like '__x%';
案例:查询姓名中第二个字符为_的员工信息
select *
from emplyees
where last_name like '_\_%';(此处\为转义字符)
select *
from emplyees
where last_name like '_$_%' escape '$';(此处意为以$为转义字符)
in(用于查询某字段的值是否属于指定的列表之内)
a in(常量值1,常量值2,常量值3,......)
a not in(常量值1,常量值2,常量值3,......)
in /not in
案例:查询部门编号是30/50/90的员工名、部门编号
方式一:
select last_name,departmnet_id
from employees
where department_id in(30,50,90);
方式二:
select last_name,departmnet_id
from employees
where department_id =30
or department_id = 50
or department_id = 90;
案例:查询工种编号不是ST_CLERK或IT_PROG的员工信息
方式一:
select *
from employees
where job_id not in('ST_CLERK','IT_PROG');
方式二:
select *
from employees
where not (job_id='ST_CLERK'
or job_id='IT_PROG' );
between and(判断某个字段的值是否介于XX之间)
between and/not between and
案例:查询部门编号是30-90之间的部门编号、员工姓名
方式一:
select department_id,last_name
from employees
where department_id between 30 and 90;
方式二:
select department_id,last_name
from employees
where department_id >=30 and deparyment_id<=90;
案例:查询年薪不是100000-200000之间的员工姓名、工资、年薪
方法一:
select last_name,salary,salary*12*(1+ifnull(commision_pct,0)) 年薪
from employees
where salary*12*(1+ifnull(commision_pct,0))<100000 or salary*12*(1+ifnull(commision_pct,0))>200000;
方法二:
select last_name,salary,salary*12*(1+ifnull(commision_pct,0)) 年薪
from employees
where salary*12*(1+ifnull(commision_pct,0)) not between 100000 and 200000;
is null/is not null
案例:查询没有奖金的员工信息
select *
from employees
where commision_pct is null;
案例:查询有奖金的员工信息
select *
from employees
where commision_pct is not null;
=只能判断普通的内容
is 只能判断null值
<=>安全等于,既能判断普通内容,也能判断null值;
查询工资大于12000的员工姓名和工资
select last_name,salary
from employees
where salary>12000;
查询员工号为176的员工的姓名和部门号和年薪
select last_name,department_id,salary12(1+ifnull(commission_pct,0)) 年薪
from employees
where employee_id=176;
选择工资不在5000到12000的员工的姓名和工资
select last_name,salary
from employees
where salary not between 5000 and 12000;
选择在20或50号部门工作的员工姓名和部门号
select last_name,department_id
from employees
where department_id in(20,50);
选择公司中没有管理者的员工姓名及job_id
select last_mame,job_id
from emplyees
where manger_id is null;
选择公司中有奖金的员工姓名,工资和奖金级别
select last_name,salary,commission_pct
from employees
where commission_pct is not null;
选择员工姓名第三个字母为a的员工姓名
select last_name
from employees
where last_name like ‘__a%’;
选择姓名中有字母a和e的员工姓名
select last_name
from employees
where last_name like ‘%a%’ and last_name like ‘%e%’;
显示出表employees 表中first_name以‘e’结尾的员工信息
select *
from employees
where first_name like ‘%e’;
显示出表employees部门编号在80-100之间的姓名、职位
select last_name,job_id,department_id
from employees
where department_id between 80 and 100;
显示出表employees的manager_id是100,101,110的员工姓名、职位
select last_name,job_id,department_id
from employees
where manager_id in (100,101,110);
进阶三 :排序查询
语法: select 查询列表
from 表名
【where 筛选条件】
order by 排序列表
执行顺序:
①from子句
②where子句
③select子句
④order by子句
举例:
select last_name,salary
from employees
where salary>10000
order by salary;
特点:
1、排序列表可以是单个字段,多个字段,表达式,函数,列表、以及以上的组合
2、升序 通过asc,默认行为
降序通过desc
一、单个字段排序
案例:将员工编号>120的员工信息进行工资的升序
select *
from employees
where employee_id>120
order by salary asc;
案例:将员工编号>120的员工信息进行工资的降序
select *
from employees
where employee_id>120
order by salary desc;
二、按表达式排序
案例:对有奖金的员工,按年薪降序
select *,salary*12*(1+ifnull(commisson_pct,0)) 年薪
from employees
where commission_pct is not null
order by salary*12*(1+ifnull(commisson_pct,0)) desc;
三、按别名排序
案例:对有奖金的员工,按年薪降序
select *,salary*12*(1+ifnull(commisson_pct,0)) 年薪
from employees
where commission_pct is not null(此句在此可以删除)
order by 年薪 desc;
四、按函数的结果排序
案例:按姓名的字数长度进行升序
select legth(last_name),last_name
from employees
order by length(last_name);
五、按多个字段排序
案例:查询员工的姓名、工资、部门编号,先按工资升序,再按部门编号降序
select last_name,salary,department_id
from employees
order by salary asc,department_id desc;
六、按列数排序
案例:想按照第二列进行排序
select *
from employees
order by 2;
赢得比赛不是靠你的身高,而是你的内心——Jerry Sloan
2020年5月31日
常见函数的介绍
进阶四:常见函数
函数类似java中学过的”方法“
为了解决某个问题,将编写的一系列的命令集合封装在一起,对外仅仅暴露方法名,供外部调用。
1、自定义方法(函数) 2、调用方法(函数)
一、字符函数
conccat 拼接字符
select concat('hello,',first_name,last_name) 备注 from employees;
length 获取字节长度
select length('hello,程序员');
显示结果为15,一个汉字在utf-8中占用三个字节
char_length 获取字符长度(个数)
select char_length('hello,程序员');
显示结果为9,一个字母一个汉字都是一个字符
substring 截取子串
select substr('张三丰爱上了郭襄',1,3);
其中1代表从第一个位置开始截取,3代表截取的长度
即substr(str,起始索引,截取的字符长度)
substr(str,起始索引) 在截取字符长度没写的情况下表示默认起始索引到整个字符串结束
instr 获取字符第一次出现的索引
select instr('三打白骨精aaa白骨精bb白骨精','白骨精');
显示结果为3
trim 去前后指定的字符,默认空格
select trim(' 虚 竹 ') as a;
select trim('x' from 'xxxxxxu虚 竹xxxxxxxxxxxxxx') as a;
lpad/rpad 左填充/右填充
select lpad('木婉清',10,'a');
select rpad('木婉清',10,'a');
upper/lower 变大写/变小写
select lower
案例:查询员工表中的姓名,要求格式:姓首字符大写,其他字符小写,名所有字符大写,且姓和名之间用_分割,最后起别名“OUT_PUT”
select concat(upper(substr(first_name,1,1)),lower(sustr(first_name,2),'_',upper(last_name)) "OUT_PUT"
from emloyees;
strcmp 比较两个字符的大小
select strcmp('abc','aaa');
显示结果为1(前面大)
select strcmp('abc','aca');
显示结果为-1(前面下)
select strcmp('abc','abc');
显示结果为0(相等)
left/right 截取子串
select left('程序员',1);
显示结果为程
select right('程序员',2);
显示结果为序员
二、 数学函数
abs 绝对值
select abs(-22);
结果为22
ceil 向上取整,返回值为>=该参数的最小整数
select ceil(1.09);
显示结果2
select ceil(1.00);
显示结果1
select ceil(-1.09);
显示结果-1
floor 向下取整,返回值<=该参数的最大整数
select floor(1.09);
显示结果1
select floor(1.00);
显示结果1
select floor(-1.09);
显示结果-2
round 四舍五入
select round(1.898978);
显示结果为2
select round(1.898978,2);
显示结果为1.89,此时2为保留小数点后2位
truncate 截断
select truncate(1.32323,0)
显示结果为1,0表示截断小数点后0位
select truncate(1.32323,2)
显示结果为1.32,2表示截断小数点后2位
mod 取余 被除数的正负取决了结果的正负!!
select mod(-10,3);
结果显示为-1;
三、日期函数
now 当前日期
select now();
curdate 只获取日期
select curdate();
curtime 只获取当前时间
select curtime();
datediff 获得两个日期所差的天数
select datediff('1998-7-16','2019-7-13');
显示结果为7667
date_format
select date_format('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;
select date_format(hiredate,'%Y年%m月%d日 %H小时%i分钟%s秒') 入职日期
from employees;
str_to_date 按照指定格式解析字符串为日期类型
select str_to_date('3/15 1998','%m/%d %Y');
四、流程控制函数
if函数
select if(100>9,'好','坏');
案例:如果有奖金,则显示最终奖金,如果没有,则显示0
select if(commission_pct is null,0,salary*12*commission_pct) 奖金
from employees;
case函数
情况1:类似于switch语句,可以实现等值判断
case 表达式
when 值1 then 结果1
when 值2 then 结果2
......
else 结果n
end
案例:
部门编号是30,工资显示为2倍
部门编号是50,工资显示为3倍
部门编号是60,工资显示为4倍
显示 部门编号,新工资,旧工资
select department_id,salary,
case department_id
when 30 then salary*2
when 50 then salary*3
when 60 then salary*4
else sasary
end newsalary
from employees;
情况二:类似于多重if语句,实现区间判断
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
....
else 结果n
end
案例: 工资>20000,显示级别A
工资>15000,显示级别B
工资>10000,显示级别C
否则,显示D
select salary,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end
as grade
from employees;
练习:
显示系统时间(注:日期+时间)
select now();
查询员工号,姓名,工资,以及工资提高百分之20%后的结果 (new salary)
select employee_id,last_name,salary,salary*1.2 “new salary”
from employees;
将员工的姓名按首字母排序,并写出姓名的长度(length)
select length(last_name) 长度
from employees
order by substr(last_name,1,1) asc;
进阶五:分组函数
说明:分组函数往往用于实现将一组数据进行统计计算,最终得到一个值,又称为聚合函数或统计函数
常见的分组函数:
sum(字段名):求和
avg(字段名):求平均数
max(字段名):求最大值
min(字段名):求最小值
count(字段名):计算 非空字段值的个数
案例:查询员工信息表中,所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
select sum(saalary),avg(salary),min(saary),max(salary),count(salary)
from employees;
分组查询:当需要分组查询时需要使用group by 子句,例如查询每个部门的工资和,这说明要是用部分
进阶六:分组查询
语法:
select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;
执行顺序:
①from子句
②where子句
③group by子句
④having子句
⑤select子句
⑥order by子句
特点:
①查询列表往往是分组函数和被分组的字段
②分组查询中的筛选分为两类
筛选的基表 使用的关键字 位置
分组前筛选 原始表 where group by的前面
分组后筛选 分组后的结果集 having group by的后面
即分组函数只可能放在having后面
简单的分组案例:
查询每个工种的员工平均工资
select avg(salary),job_id
from employees
group by job_id
查询每个领导手下的人数
select count(*),manager_id
from employees
where manager_id is not null
group by manager_id;
查询出邮箱中包含a 字符的 每个部门的最高工资
select max(salary) 最高工资,department_id
from employees
where email like '%a%'
group by department_id;
查询有奖金的每个领导手下员工的平均工资
select avg(salary),manager_id
from empolyees
where comission_pct is not null
group by manager_id;
查询那个部门的员工个数>5
分析一:查询每个部门的员工个数
select count(*) 员工个数,department_id
from employees
goup by department_id;
分析二:在刚才的结果基础上筛选那个部门的员工个数>5
count(*)>5
select count(*) 员工个数,department_id
from employees
goup by department_id
having count(*)>5;
每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select job_id,max(salary)
from employees
where comission_pct is not null
group by job_id
having max(salary)>12000;
领导编号>102的每个领导手下的最低工资大于5000的的领导编号和最低工资
select manager_id,min(salary) 最低工资
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;
每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
select job_id,max(salary) 最高工资
from employees
where comission_pct is not null
group by job_id
having max(salary)>6000
order by max(salary) asc;
查询每个工种每个部门的最低工资,并按最低工资降序
提示:工种和部都一样,才是 一组
select min(salary) 最低工资,job_id,department_id
from employees
group by job_id,department_id
order by min(salary) desc;
-------------------------------------------- SQL92语法-------------------------------------------------------
一、内连接
等值连接
语法:
select 查询列表
from 表名1 别名1,表名2 别名2,,...
where 等值连接的连接条件
特点:
1、为了解决多表中的字段名重名问题,往往为表起别名,提高语义性
2、表的顺序无要求
①简单的两表连接
案例:查询员工名和部门名
select last_name,departmen_name
from employees e,departments d
where e.department_id=d.department_id;
②添加筛选条件
案例:
查询部门编号>100的部门名和所在的城市名
select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and d.department_id>100;
查询有奖金的员工名、部门名
select last_name,department_name
from departments d,employees e
where d.department_id=e.department_id
and e.commission_pct is not null;
查询城市名中第二个字符为o的字符名和城市名
select department_name,city
from departments d,locations l
where d.department_id=e.department_id
and city like '_o%';
③添加分组+筛选
案例:
查询每个城市的部门个数
select count(*) 部门个数,l.city
from departments d,locations l
where d.location_id=l.location_id
group by l.city;
④添加分组+筛选+排序
非等值
自连接
-------------------------------------------- SQL99语法-------------------------------------------------------
一、内连接
语法:
select 查询列表
from 表名1 别名
[inner] join 表名2 别名
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表;
SQL92和SQL99 的对比:
SQL99,使用join 关键字替代了之前的逗号,并且将连接条件和筛选条件进行了分离,提高阅读性!!
等值连接
①简单的两表连接
案例:查询员工名和部门名
select last_name,departmen_name
from employees e
inner join departments d
on e.department_id=d.department_id;
②添加筛选条件
案例:
查询部门编号>100的部门名和所在的城市名
select department_name,city
from departments d
inner join locations l
on d.location_id=l.location_id
where d.department_id>100;
③添加分组+筛选
案例:
查询每个城市的部门个数
select count(*) 部门个数,l.city
from departments
inner join locations l
on d.location_id=l.location_id
group by l.city;
④添加分组+筛选+排序
案例:
查询部门中员工个数>10的部门名,并按个数降序
select count(*) 员工个数,d.departmnet_name
from employees e
join departments d
on e.department_id=d.department
group by d.department_id
having 员工个数>10
order by 员工个数 desc;
二、非等值连接
案例:
查询部门编号在10-90之间的员工的工资级别,并按级别进行分组
select count(*) 个数,grade
from employees e
join sal_grade g
on e.salary between g.min_salary and g.max_salary
where e.departmen_id between 10 and 90
group by g.grade;
三、自连接
案例:查询员工名和对应的领导名
select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id=m.employees_id ;
今天就先写到这里,大家可以点个关注,后续更新可看