数据库学习笔记

数据库的好处

1.持久化数据到本地
2.可以实现结构化查询，方便管理

数据库相关概念

1.DB:数据库，保存一组有组织的数据的容器
2.DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3.SQL：结构化查询语言，用于和DBMS通信的语言

数据库存放数据的特点

1.将数据放到表中，表再放到库中
2.一个数据库中可以有多个表，每个表都有一个自己的名字，用来标识自己，表明具有唯一性。
3.表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的设计
4.表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似Java中的“属性”
5.表中的数据是按行存储的，每一行类似于Java中的对象

MySQL服务的启动和停止

方式一：计算机-右击管理-服务
方式二：通过管理员身份运行
net start 服务名（启动服务）
net stop 服务名（停止服务）

MySQL服务的登录和退出

方式一：通过mysql自带的客户端
只限于root用户

方式二：通过windows自带的客户端
登录：
mysql 【-h 主机名 -p端口号】 -u 用户名 -p密码

退出：
exit 或 ctrl+c

MySQL的常见命令

1.查看当前所有的数据库

show databases;

2.打开指定的库

use 库名

3.查看当前库的所有表

show tables

4.查看其他库的所有表

show tables from 库名;

5.创建表

creat table 表名(
	列名 列类型,
	列名 列类型,
	...
	);

6.查看表结构

desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登陆到mysql服务端
mysql --version
或
mysql --V

MySQL的语法规范

1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令很具需要，可以进行缩进或换行
4.注释

	单行注释：#注释文字
	当行注释：-- 注释文字
	多行注释：/*注释文字*/

---

进阶1：基础查询

/*
语法：
select 查询列表
form 表名；
类似于：System.out.println(打印东西);
特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格

*/

USE myemployees;

1.查询表中的单个字段

SELECT last_name FROM employees;

2.查询表中的多个字段

SELECT last_name,salary,email FROM employees;

3.查询表中的所有字段

SELECT
employee_id,
first_name,
last_name,
email,
phone_number,
job_id,
salary,
commission_pct,
manager_id,
department_id,
hiredate
FROM
employees ;

SELECT * FROM employees;

4.查询常量值

SELECT 100;
SELECT ‘john’;

5.查询表达式

SELECT 100%98;

6.查询函数

SELECT VERSION();

7.起别名

/*
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#方式一：使用AS
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#案例：查询salary,结果显示为 out put
SELECT salary AS ‘out put’ FROM employees;

8.去重

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

9. +号的作用

/*
java中的+号
1.运算符
2.连接符，只要有一个操作数为字符串

mesql中的+号
仅仅只有一个功能：运算符
select 100+90;两个操作数都为数值型，则做加法运算
select ‘123+90’;其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select ‘jhon’+90; 如果转换失败，则将字符型数值转换成0
select null+10;只要其中一方为null，则结果肯定为null
*/
#案例：查询员工名和姓连接成一个字段，并显示为 姓名

SELECT CONCAT(‘a’,‘b’,‘c’) AS 结果;

SELECT CONCAT(last_name,first_name)AS 姓名
FROM employees;

SELECT
last_name+first_name AS 姓名
FROM employees;

---

1.下面的语句是否可以执行成功
SELECT last_name,job_id,salary AS sal
FROM employees;

2.下面的语句是否可以执行成功

SELECT * FROM employees;

3.找出下面语句中的错误
SELECT employee_id,last_name,
salary *12 AS “ANNUAL SALARY”
FROM employees;

4.显示表departments的结构，并查询其中的全部数据

DESC departments;
SELECT * FROM departments;
5.显示出表employees中的全部job_id(不能重复)

SELECT DISTINCT job_id FROM employees;

6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

SELECT IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM employees;

SELECT
CONCAT(first_name,’,’,last_name,’,’,job_id,’,’,IFNULL(commission_pct,0)) AS out_put
FROM
employees;

---

进阶2：条件查询

/*
语法：
select
查询列表
from
表名
where
筛选条件;

分类：
一、按条件表达式筛选
简单条件运算符：><= != <> >= <=
二、按逻辑表达式筛选
逻辑运算符：&& || ！
and or not
三、模糊查询
like
between and
in
is null
*/

按条件表达式筛选

#案例1：查询工资>1200的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;

#案例2、查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <>90;

按逻辑表达式筛选

#案例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;

模糊查询

/*
like
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符,包含0个字符
_任意单个字符
between and
in
is null|is not null
*/
1.like
#案例1：查询员工名中包含字符a的员工信息

SELECT
*
FROM
employees
WHERE
last_name LIKE ‘%a%’;#abc

#案例2：查询员工命中第三个字符为e，第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE ‘__n_l%’;

#案例3：查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’;

2.between and

/*
①使用between and 提高见解读
②包含临界值
③两个临界值不要调整顺序

*/

#案例1：查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id>=100 AND employee_id<=120;

#------------------------------

SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;

3.in
/*
含义：用于判断某字段的值是否属于in列表中的每一列
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③
*/
#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id=‘IT_PROT’ OR job_id=‘AD_VP’ OR job_id=‘AD_PRES’;

#-----------------------
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN(‘IT_PROT’,‘AD_VP’,‘AD_PRES’);

4、is null
/*
=或<>不能用于判断null值
is null 或is not null 可以判断null值
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;

#案例1：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;

安全等于 <=>
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct<=>NULL;
#案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary<=>12000;
is null pk <=>
IS NULL:仅仅可以判断NULL值，可读性高
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低

---

进阶3：排序查询

/*
引入:
select*from employees;
语法：
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点：
1、asc代表的升序，desc代表的降序,如果不写，默认是升序
2、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by 子句一般是放在查询语句的最后面，但limit子句除外

*/
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary DESC;
#案例2：查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序
SELECT * FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;

#案例3：按年薪的高低显示员工的信息和 年薪【按表达式排序】
SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC;

#案例4：按年薪的高低显示员工的信息和 年薪【按别名排序】

SELECT ,salary12*(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,employee_id DESC;

---

进阶4：常见函数

/*
功能：类似java的方法，将我们的一组逻辑语句封装在方法体中，对外暴露方法名

好处：1、隐藏了实现细节2、提高代码的重用性

调用：select 函数名（实参列表）【from 表】；

特点：
①叫什么（函数名）
②干什么（函数功能）

分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数：

字符函数：
length
concat
substr
instr
trim
upper
lower
lpad
rpad
replace

数学函数：
rond
ceil
floor
truncate
mod

日期函数：
now
curdate
curtime
yer
month
monthname
day
hour
minute
second
str_to_date
date_formate

其他函数：
version
database
user

控制函数
if
case

单行函数

*/

一、字符函数

1.length 获取参数值的字节个数
SELECT LENGTH(‘john’);
SELECT LENGTH(‘张三丰hahaha’);
SHOW VARIABLES LIKE ‘%char%’

2.concat 拼接字符串
SELECT CONCAT(last_name,’_’,first_name) 姓名 FROM employees;

3.upper、lower
SELECT UPPER(‘john’);
SELECT LOWER(‘JoHn’);
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) out_put;

#案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),’_’,LOWER(SUBSTR(last_name,2))) out_put
FROM employees;

5.instr 返回字串第一次出现的索引，如果找不到返回0

SELECT INSTR(‘杨不殷六侠悔爱上了殷六侠’,‘殷六侠’) AS out_put

6.trim
SELECT LENGTH(TRIM(’ 张翠山 ')) AS out_put;

SELECT TRIM(‘aa’ FROM ‘aaaaaaa张aaaaaaaaaa翠山aaaaaaaa’) AS out_put;

7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD(‘殷素素’,10,’*’) AS out_put;

8.rpad 用指定的字符实现左填充指定长度
SELECT RPAD(‘殷素素’,12,‘ab’) AS out_put;

9.replace 替换
SELECT REPLACE(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) AS out_put;

二、数学函数

round 四舍五入
SELECT ROUND(1.456,2);

ceil 向上取整,返回该参数的最小整数
SELECT CEIL(1.52);

floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);

truncate 截断

SELECT TRUNCATE(1.65,1);

mod 取余
/*
mod(a,b): a-a/b*b;
*/
SELECT MOD(10,3);
SELECT MOD(-10,3);

三、日期函数

now 返回当前系统日期
SELECT NOW();

curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

curdate 返回当前系统时间，不包含日期
SELECT CURTIME();

可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR(‘1998-1-1’) 年;
SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 年;
SELECT MONTHNAME(NOW()) 月;

str_to_date 将字符转换成日期
SELECT STR_TO_DATE(‘1998-3-2’,’%Y-%c-%d’) AS out_put;

查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate = ‘1992-4-3’;
SELECT * FROM employees WHERE hiredate = STR_TO_DATE(‘4-3-1992’,’%c-%d-%Y’);

date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;

查询有奖金的员工名和入职日期(xx月/xx日 x年)

SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y’) 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数

1.if函数： if else 的效果
SELECT IF(‘10>5’,‘大’,‘小’);

SELECT last_name,commission_pct,IF(commission_pct IS NULL,‘没奖金，呵呵’,‘有奖金，哈哈’) 备注
FROM employees;

2.case 函数的使用一： switch case的效果
/*

switch(变量或表达式){
case 常量1：语句1；break;
…
default:语句break;
}

mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
*/

#案例：查询员工的工资，要求
/*
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50࿰