MySQL
为什么要学数据库
之前学过的保存数据的容器
数组、集合、文件……
数据库的好处
实现数据持久化
结构话查询
使用完整的管理系统同意管理,便于查询
数据库的相关概念
标记: 优先级 4
DB(database):存储数据的仓库。保存了一系列有组织的数据
DEMS(database Management System)数据库管理系统:数据库是通过DBMS创建和操作的容器。换句话说是用于管理DB中的数据
SQL(Structure Query Language)结构化查询语言:专门用来与数据库(DBMS)通信的语言
SQL的优点:
不是某个特定的数据库供应商专有的语言,几乎所有的DBMS都支持SQL
简单易学
是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作
数据库存储数据的特点
将数据库放到表中,表再放到库中
一个数据库中可以有多个表,每个表都有一个名字,用来标致自己。表名具有唯一性
表具有一些特性,这些特性定义了数据在表中如何存储,类似java中“类”的射虎
表由列组成,我们也称为字段。所有的表都是有一个或者多个列组成的,每一列类似java中的“属性”
表中的数据是涵行存储的,每一行类似于java中的“对象”
初始MySQL
优点
成本低:开源,一般可以免费使用
性能高:执行很快
简单:容易安装和使用
体积小,便于安装
DBMS分两类:
基于共享文件系统的DBMS(access)
基于客户机——服务器的DBMS(MySQL、Oracle、SQLserver)
mysql服务端的登录和退出
登录之前要先启动
方式一:通过mysql自带的客户端只能登入root用户
方式二:通过Windows自带的客户端
登录:打开管理员命令提示符(运行窗口)、mysql -h localhost -P 3306 -u root -p -h主机名、-P端口号 -u用户名 -p密码
退出:ctrl+C 或者 exit
常见命令
show database(显示所有的数据库)
use 指定库名(查看里面有哪些表)
show tables from 另外一个库的库名(查看另外一个库里面有哪些表)
select database() (调用一个函数)
create table 表名() (创建表)
select version() (mysql的查看版本已经登录到mysql服务端)
mysql --V / mysql --version (未登录到mysql服务器的查看版本方式)
语法规范
不区分大小写,但是建议我们关键字大写,表名列名小写
每条命令最好用分号结尾,执行多条代码时没有分号会出错
每条命令根据需要,可以进行缩进或者换行
注释
单行注释:#注释文字
单行注释:-- 注释文字(横线和文字之间要空一个空格)
多行注释:/注释文字/
SQL语言的学习
标记: 优先级 1
用F12格式化,自动美化界面格式
查询
基础查询
1、查询表中的单个字段 要在后面加上来自哪个表
2、查询多个字段:逗号隔开
3、查询所有的字段:用*代表(顺序和表格顺序是一样的)
4、查询常量值;select 100; select ‘John’
5、查询表达式:select 100%98;
6、查询函数: select version()
7、起别名
a、便于理解
b、如果要查询的字段有重名的情况,使用别名可以去分开
方式一:使用as:select 100%98 as 你要取的名字
方式二:使用空格
案例:查询salary,显示结果为out put:select salary as “out put” from employees
8、去重
案例:查询员工表中涉及到所有的部门编号
selectdistinct department_id from employees
9、+号的作用
a、java中的+号
1)运算符,两个操作数都为数值型
2)连接符,只要有一个操作数为字符串
b、mysql中的+号
仅仅只有一个功能:运算符 select 100+90;两个操作数都为数值型,则作加法运算
select ‘123’ +90; 其中一方为字符型,视图字符型数值转换成数值型,如果转换成功,则继续做加法运算,如果转换失败,则将字符型数值转换0
只要其中由乙方为null,则结果肯定为null
案例:查询员工名和型连接成一个字段,并显示为姓名
select last_name+first_name from
也可以使用contact拼接
select contact(字段名们用逗号隔开) as 别名 from 表名
用contact拼接可能出现值为空(比如说员工奖金):解决办法
select ifnull(奖金(可能为null的字段名),0(此处0表示为你想更改null为什么)) as 奖金率,奖金 from 表名
细节
着重号可以去掉也可不去
要执行谁就要选中谁;格式化也是如此
语法:select 要查询的东西(列) from 表名;类似于:system.out.println(要打印的东西)
特点
查询列表可以是:表中的字段、常量值、表达式、函数
查询的结果是一个虚拟的表格
条件查询
语法:select 查询列表 from 表名 where 筛选条件;
分类
1、按条件表达式筛选:条件运算符: > < = ! != <> >= <=
select * from employees where salary>12000
select last_name,salary from employees where salary!=90
2、按逻辑表达式筛选:逻辑运算符:&& || | and or not
作用:连接条件表达式
&&和and:两个条件中只要一个条件为true,结果就为true 反之为false
||和not;如果连接的条件本身为false,结果为true,反之为false
select lastname,salary fromusers where salary>=20000 and salary<=30000;
select lastname,salary,firstname from users where not(emplyeeid>=90 and userid>=112) or salary >19000;
3、模糊查询:like between and in is null
like:想什么样子
特点:一般和通配字符搭配使用
%表示任意多个字符,包含0个字符
_表示任意单个字符
查询员工名中包含字符a的员工信息:select * from last_name like ‘%a%’; %表示任意字符,字符串用单引号
查询员工名中第三份字符为a,第五个字符为a的员工和工资:select salary,lastname from employees where lastname like ‘__a__a%’
查询员工名中第二个字符为_的员工名:select lastname from emplyees where lastname like ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’;
查询员工名中第二个字符为_的员工名:select lastname from emplyees where lastname like ‘KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’;其中escape ‘某字符号’ 表示该字符为转义字符
也可以这样表示:select lastname from emplyees where lastname like '_%’ ;
\为转义符号
between and
特点:
使用betweenand可以提搞语句的简介度
包含临界值
两个临界值不要调换顺序
查询员工编号在100 到120之间的员工信息:select *from employees where employeeid100 and employee<=120;
或者select * from employees where employeeid between 100 and 120;
不能换顺序,这个如果换顺序则表示大于等于120小于等于100之间的数值
in
特点
使用in提高缘佛那个语句简洁度
in列表的值烈性必须一致或者兼容
查询员工的工种编号是aa,vv,bb中的一个员工名和工种编号:
select lastname,jobid ftom employees where jobid=‘aa’ or jobid=‘vv’ or jobid=‘bb’;
而用in
select lastname,jobid ftom employees where jobid in(‘aa’,‘vv’,‘bb’)
is null
没有奖金的员工名和奖金率:
select lastname, 奖金率 from employees where 奖金率 is null;或者is not null
特点
=或者<>不能判断null的值。is null或者 is not null可以判断null的值
<=>(安全等于)也可以判断null值和普通数值,但是可读性较低建议使用is null(仅仅可以判断null的值,但是可读性高)
测试题:
查询部门表中设计到那些位置编号?
select distinct 位置编号 from departments
查询没有奖金,且工资小于18000的salary,lastname
select salary,lastbame from employees where 奖金率 isnull and salary<18000;
查询employees表中,jobid不为’IT‘或者工资不为12000的员工信息
select * from wmployees where jobid<> ‘IT’ or salary=12000;
查询表的结构
desc 表名;
访问select * from emloyees ;和访问select * from employees where 奖金率 like ‘%%’ and lastname like ‘%%’;结果是否一样?
不一样,因为奖金率会出现为NULL的情况;可以用ifnull更改,select * from employees where ifnull(奖金率,0) like ‘%%’ and lastname like ‘%%’;但是如果用or连接,则两种情况是一样的。如下select * from employees where 奖金率 like ‘%%’ and lastname like ‘%%’;
排序查询
语法:select 查询的列表from 表名 where筛选条件 order 不要排序列表(asc|desc)
select *from employees order by salary desc;
select *from employees order by salary asc;
默认asc为升序 desc为降序
特点
1、asc代表的是升序,desc代表降序,如果不写。默认为升序
2、order by 子句中可以支持单个字段,多个字段,表达式,函数,别名
3、order by子句一般是放在查询语句的最后面
查询部门>=90 的员工信息,按入职时间的先后进行升序
select *from employees where id>-90 order by hiredate asc;
hiredate为入职时间
按年薪的高低显示员工的信息和年薪(按表达式排序)
select ,salary12*(1+ifnull(commission_pct,0)) 年薪 from employees order by salary12(1+ifnull(commision_pct,0)) desc;
其中commission_pct表示奖金率
按年薪的高低显示员工信息和年薪(按照别名排序)
select ,salary12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc
按姓名的长度显示员工的信命和工资(按函数排序)
select length(lastname) 字节长度,lastname,salary from employees order by length(lastname) desc;
查询员工信息,要求先按工资升序,再按员工编号降序(按多个字段排序)
select * from employees order by salary asc ,employeeid desc;
测试题
查询员工的姓名和部门号和年薪,按照年薪j降序,按姓名升序
select lastname,id,salary12(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc,lastname asc
选择工资不在8000到17000的员工和工资,按照工资降序
select employee, salary from employees where salry not between 17000 and 8000 order by salary desc;
查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
select * from employees where email like ‘%e%’ order by length(email) desc,id asc;
函数
常见函数
功能:类似于java的方法:将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节 2、提法哦代码的重用性
调用:select 函数名(实参列表)【from 表名】
特点
1、叫什么(函数名)
2、干什么(函数功能)
分类
1、单行函数:如concat。length。ifnull等
2、分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数
字符函数
length获取参数值的字节个数:select length(‘jhon’);select length(‘张丹峰hahaha’);
张三丰hahaha这个长度为15(一个汉字占3个字节,一个字母占1个字节)
show variables like ‘%char%’;此段代码作用时:查询编码集:如UTF-8
concat连接字符:select(字符一,字符二,字符三)
upper,lower:将字符串转换为大写或者小写select upper(‘john’);select lower(‘john’)
示例:将姓便大写,名便小写。然后拼接
select concat(upper(lastname),lower(firstname)) 姓名 from employees
substr.substring:索引从1开始
截取从指定索引出后面所有的字符select substr(’李莫愁爱上了陆展元‘,7) output;
截取从指定索引出指定字符长度的字符:select substr(‘李莫愁爱上了陆展元’,1,3) output
案例:姓名中首字母字符大写,其他字符小写探后用_拼接显示出来
select concat(upper(substr(lastname,1,1)),’_’,lower(substr(lastname,2))) output from employees;
instr:返回淄川第一次出现的索引,如果找不到,返回0
select instr(‘杨不悔爱了殷六侠’,‘殷六侠’) as output;
trim:去空格/去除前后某个字符
select length(trim(’ jbbakbgfh ')) as output
select trim(’ a’ from ‘aaaaaaaaaaaaaaaaa嚣张aaaaaaaaaaaaaaaaaa’)as output;
lpad 用指定的字符实现右边填充指定长度
select lpad(‘盘大球’,2,’*’) as output
rpad 用指定的字符实现左边填充指定长度
replace :替换
select replace(‘爬大切诺基逢年过节案例积分’,‘过节’,‘积分’) as output;
函数
数学函数
round:四舍五入
select round(-1.45)
select round(1.234,2)重载,这个意思是小数保留两位
ceil:向上取证,返回>=该参数的最小整数
select ceil(-1.23);
floor:向下取整,返回<=改参数的最大整数
truncate:截断
select truncate(1.69999,1);
mod:取余
select mod(a,b);
日期函数
now:返回当前系统日期+时间
curtime:返回当前时间,不包含日期
curdate:返回当前日期,不包含时间
示例:获取指定的部分:年、月、日、小时、分钟、秒
select year(now()) 年;
select year(‘1988-1-1’) 年;
select year(diredate) 年 from employes;
select month(now()) 月;
select monthname(now()) 月’;
str_to_date 将字符通过指定的格式转换成日期
查询入职日期为指定日期的员工信息
select * from users where hiredate = str_to_date(‘m-d yyy’,’%c-%d %y’)?
select * from users where hiredate = =‘yyyy-m-d’;
date_format:将日期转换为字符
(now:返回当前系统日期+时间, curtime:返回当前时间,不包含日期, curdate:返回当前日期,不包含时间, 示例:获取指定的部分:年、月、日、小时、分钟、秒, str_to_date 将字符通过指定的格式转换成日期, date_format:将日期转换为字符)
其他函数
select version()
select datacase()
select user()
流程控制函数
if函数:if else的效果
select if(10<5,‘大’,‘小’);如果10小于5,传出信息为大,否则为小
select lastname,奖金率,if(奖金率 is null ,‘没奖金,呜呜!’,’有奖金,嘻嘻~‘) 别名;
case函数的使用一:switch case的效果
java 中:
switch(变量或者表达式){
case 常量1:语句1:break;
……
default:语句n;break;
}
mysql中:
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
……
else 要显示的值n或者语句n
end
案例:查询呢员工的工资,要求:部门号为30,显示的工资为1.1倍;部门号为40,显示的工资为1.2倍;部门号为50,显示的工资为1.3倍;其他部门,显示的工资为原工资
selec salary 原始工资,department_id
case departmentid
when 30 then salary1.1
when 40 then salary1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees
case函数的使用二:类似于多重if
java中:
if(条件){
语句1;
}else if(条件2){
语句2;
}
……
else{
语句n
}
mysql中:
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
……
else 要显示的值n或语句n
end
案例:查询员工的工资的情况;如果工资>20000,显示A级别;如果工资>15000,显示B级别;如果工资>10000,显示C级别;否则显示D级别
select salary,
case
when salary >20000 then ‘A’
when salary >15000 then ‘B’
when salay >10000 then ‘C’
else ‘D’
end as 工资级别
from employees;
练习测试
1、显示系统时间(注:时期+时间)
select now();
2、查询员工号:姓名,工资,以及工资提高百分之二十后的结果(newsalary)
select employeeid,lastname,salary,salary*1.2 “new salary” from employees
3、将员工的姓名按首字母排序,并写出姓名的长度(length)
select length(lastname) 长度,substr(lastname,1,1) 首字母,lastname from employees order by lastname
4、做一个查询,产生下面的结果 earns monthly but wants <salary*3> Dream Salary King earns 24000 monthly but wants 7200
select concat(lastbane,‘earns’,salary,‘monthly but wants’,salary*3) as “Dream Salary”
from employees
where salary=24000;
5、使用case—when,按照下面的条件:job grade AD_pres A st_man B IT_prog C 产生下面的结果:lastname jobid crade king AD_pres A
select lastname,jobid as job,
case jobid
when ‘AD_pres’ then ‘A’
when ‘ST_man’ then ‘B’
end as Grade
from wmployees
where jobid = ‘AD_pres’;
分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
简单函数:
select sum(salary) from users;
select avg(salary) from users;
select min(salary) from users;
select max(salary) from users;
select count(salary) from users;
selec sum(salary) 和,avg(salary) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数 from users;
参数支持哪些类型
特点
1、sum avg 一般用于处理数值型;max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的效果
4、count函数的单独介绍
select count(salary) from users;
select count(*) from users:查询所有的行数,比较常用
select count(1) from users; 统计1的个数
子主题 4
5、和分组函数一同查询的字段要求是group by 后面的字段
练习测试
1、查询公司员工资的最大值、最小值、平均值、总和
select max(salary) mx_sal,min(salary) mi_sal,round(avg(salary),2) ag_sal,sum(salary) sm_sal from employees;
2、查询员工表中的最大入职时间和最小入职时间的相差天数(diffrence)
3、查询部门编号为90的员工个数
分组查询
select 分组函数,分组后的字段 from 表 where 筛选条件 grope by 分组字段 having 分组后的筛选 order by 排序列表
连接查询
等值连接:多表的等值连接的结果为多表的文集部分、n表连接至少需要n-1个链接条件、多表的顺序没有要求、一般需要为表起别名、可以搭配前面介绍的所有子句使用(比如排序、分组、筛选)
为表起别名:AS 或者空格 加上一个简单的别名代表表名(查询的字段只能用别名限定):提高了语句的简洁度、区分多个重名的字段
可以加筛选、已经用了关键字where的时候,后面加筛选条件可以使用AND添加条件
两个表的顺序可以转换位置
可以加分组
实现多表查询
非等值连接
连接两个表,找到两个表之间的关系(比如薪水在什么范围内)
自连接(自己连接自己)
把一个表当做两个表使用
题目
答案
子主题 5
子查询
分页查询
union联合查询
连接语法
外链接(join)
内连接(inner join)
交叉连接(cross)
SQL语言的学习
插入语句
修改语句
删除语句
DOL语言的学习
库和表的管理
常见数据类型介绍
常见约束
TCL语言的学习
事务和事务处理