1.数据库概念
1.DB
database 数据库
数据库有多个表组成
2.DBS
Database System 数据库系统
数据库系统由数据库和数据库管理软件构成
3.DBMS
Database Management System 数据库管理系统
数据库管理系统 :操作数据库和管理数据库的一个系统
常见的有 mysql oracle db2 sqlserver
4.sql
sql 是结构化数据查询语言
DQL(Data Query Language):数据查询语言
select
DML(Data Manipulate Language):数据操作语言
insert 、update、delete
DDL(Data Define Languge):数据定义语言
create、drop、alter
TCL(Transaction Control Language):事务控制语言
commit、rollback
5.基础指令
-
服务
net start mysql 开启服务
net stop mysql 关闭服务
-
登陆
mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
mysql -u root -proot
-
退出
mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
2 数据库安装
sc delete mysql 清空服务 重装
解压安装包并设置环境变量
A:\1WorkF\Application\MySql\mysql-5.7.19\bin;
创建my.ini文件
A:\1WorkF\Application\MySql\mysql-5.7.19\my.ini
设置my.ini
[mysqld]
basedir=A:\1WorkF\Application\MySql\mysql-5.7.19\
datadir=A:\1WorkF\Application\MySql\mysql-5.7.19\data\
port=3306
skip-grant-tables #绕过密码登录
管理员方式运行cmd
左击左下角win10图标下拉 选择Windows 系统 右击命令行 选择更多
首先将路径切换至mysql下的bin目录
cd /d A:\1WorkF\Application\MySql\mysql-5.7.19\bin
然后
1 安装mysql
mysqld –install
2 初始化数据文件
mysqld --initialize-insecure --user=mysql
2.1 初始化数据文件 会多一个data目录
A:\1WorkF\Application\MySql\mysql-5.7.19\data
3 启动mysql服务
net start mysql
4 进入mysql 密码不要输 my.ini设置绕过了 直接回车
mysql –u root –p
5 更改root密码
update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost';
6 刷新权限
flush privileges;
删除my.ini文件中最后一行绕过密码
[mysqld]
basedir=A:\1WorkF\Application\MySql\mysql-5.7.19\
datadir=A:\1WorkF\Application\MySql\mysql-5.7.19\data\\
port=3306
#skip-grant-tables
重启
exit
net stop mysql
net start mysql
【其他配置】
[mysqld]
# 设置mysql的安装目录 单斜杠出错用双斜杠\\
basedir=A:\1WorkF\Application\MySql\mysql-5.7.19\
# 设置mysql数据库的数据的存放目录
datadir=A:\1WorkF\Application\MySql\mysql-5.7.19\data\\
port=3306
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
3.数据类型
数值型
整数
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
小数
- 浮点型
float(M,D)
double(M,D) - 定点型
dec(M,D)
decimal(M,D) 字符串形式的浮点数一般用于金融计算场景
M 全部的个数 D小数部分个数
如果是decimal,则M默认为10,D默认为0==
字符型
-
短的文本:
char(M) M:最大的字符数,char可以省略,默认为1
varchar(M) varchar不可以省略 -
较长的文本:
text
blob(较大的二进制)
日期型
date 保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间 1000——9999 受时区影响
timestamp 时间戳。保存日期+时间 1970-2038
4 搜索引擎
MyISAM和InnoDB(默认)的区别
表锁行锁:查询数据时将表/行锁住,表锁(MyISAM)影响查询速率
MyISAM | InnoDB | |
---|---|---|
事务处理 | 不支持 | 支持 |
数据行锁定 | 不支持(表锁) | 支持(行锁) |
外键约束 | 不支持 | 支持 |
全文检索 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约2倍 |
适用场合 :
- MyISAM : 节约空间及相应速度
- InnoDB : 安全性高 , 支持事务处理及多用户操作数据表
存储位置
-
MySQL数据表以文件方式存放在磁盘中
-
包括表文件 , 数据文件 , 以及数据库的选项文件
-
位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
表文件详解:
- . frm – 表结构定义文件
- . MYD – 数据文件 ( data )
- . MYI – 索引文件 ( index )
InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
MyISAM类型数据表对应三个文件 :
1.DQL
DQL:Query Select Language 最为重要的便是查询语句与之对应的还有
DML:Data Manipulation Language 数据操纵语言
DDL:Data Definition Language 数据定义语言
TCL:Transaction Control Language 事务控制语言
1.基础查询
查询关键字,后面数字对应执行顺序
数据查询语言 Data Query Language
select 字段 6
from 表名 1
where 筛选条件 2
and 筛选条件 3
group by 分组条件 4
having 分组后的结果筛选 5
order by 排序 desc大到小 asc 7
limit 分页查询 8
------------------------------------------------------
常用关键字
limit 【offset,】size; 起始索引 条数
常用:limit (page-1)*size, size;
size = 10
第一页起始索引为 0 1页-1*10=0
第二页起始索引 10 2页-1*10=10
1. AS或空格 为表/字段 起别名
SELECT last_name AS 姓,first_name AS 名
FROM employees;
2. distinct 字段名 去重
SELECT DISTINCT department_id
FROM employees;
3. max(distinct 字段名) 分组函数搭配去重
select min(distinct salary)
from employees;
4.concat (字段1,字段2) 连接函数★★★
select concat(last_name,first_name) 姓名
from employees;
5.ifnull(字段,值1) 函数里该字段如果为空则取值为值1
比如查询是否有奖金 没有为0
SELECT ifnull(commission_pct,0) AS 奖金率,commission_pct
from employees;
6.is null /is not null 判断是否为空
SELECT
last_name
FROM
employees
WHERE
commission_pct IS NOT NULL;
7.between and x>20 x<60 介于
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 20 AND 60;
8.like 模糊查询
1. % 多字符的通配符 2 _ 单字符通配符
3 escape 自定义通配符
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
where 和 having ★★★
where 筛选原表
having 对分组后的结果进行筛选
常见函数
1、字符函数
concat 拼接
substr 截取子串
upper 转换成大写
lower 转换成小写
trim 去前后指定的空格和字符
ltrim 去左边空格
rtrim 去右边空格
**replace** 替换
lpad 左填充
rpad 右填充
instr 返回子串第一次出现的索引
length 获取字节个数
2、数学函数
round 四舍五入
rand 随机数
floor 向下取整
ceil 向上取整
mod 取余
truncate 截断
3、日期函数
now 当前系统日期+时间
curdate 当前系统日期
curtime 当前系统时间
str_to_date 将字符转换成日期
date_format 将日期转换成字符
4、流程控制函数
if 处理双分支
case语句 处理多分支
情况1:处理等值判断
情况2:处理条件判断
5、其他函数
version 版本
database 当前库
user 当前连接用户
6 分组函数
sum 求和
max 最大值
min 最小值
avg 平均值
count 计数
特点:
1、以上五个分组函数都忽略null值,除了count(*)
2、sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果
4、count的参数可以支持:
字段、*、常量值,一般放1
建议使用 count(*)
2.联合查询
union 联合 合并:将多条查询语句的结果合并成一个结果
-
要求查询结果的列数一致
-
要求查询语句的查询的每一列类型和顺序最好一致
-
union关键字默认去重 union all 包含所有(避免去重)
查询部门编号》90或邮箱包含a的员工信息 查询的结果都为员工信息 列数一致 SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90;
3.连接查询
连接查询按年代分类
- sql192 标准 仅仅支持内连接
- sql199标准 支持内连接和做外连接(左右)+交叉连接
连接查询按功能分类
-
内连接
- 等值连接 on 连接条件多为 =
- 非等值连接 on 连接条件 多为 between and
- 自连接 一个表和自身连接 管理者又是员工
-
外连结
内连接的等值连接:查询出表中所有符合条件的
外连接的左右:查询出符合条件的和不符合条件的 ?
主表的概念 查询出主表中所有的信息 包含等值连接和不符合条件的 不服和条件的用null 填充
- 左外连接 left join 左边为主表
- 右外连接 right join 右边的为主表
- 全外连接 无主表或都为主表
-
交叉链接
select 查询列表
from 表1 连接类型(inner|left|right|full)
join 表2
on 连接条件
where 筛选条件
group by 分组条件
having 分组后筛选条件
order by 排序字段/条件
limit ;
-
n表连接至少需要n-1个条件
-
连接查询表要起别名
一 内连接
一 内连接
1 等值连接 inner可以省略
SELECT last_name,department_name
FROM departments d
inner JOIN employees e
ON e.`department_id` = d.`department_id`;
2 非等值连接
SELECT salary,grade_level
FROM employees e
inner JOIN job_grades g
ON salary
BETWEEN g.`lowest_sal` AND g.`highest_sal`
WHERE g.`grade_level` = 'A';
3 自连接 一定要起别名 查询 员工名和上级的名称
SELECT e.last_name, m.last_name
FROM employees e
inner JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
二 外连接
左外 查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
2 全外
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;
3 交叉
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
4.子查询
出现在其他语句中的select语句,称为子查询或内查询
放于(里面)
出现的位置分类
-
select后面 标量子查询
-
from from表 支持表子查询
-
where/having 标量/列子/行子(少)
-
exists(相关子查询) 表子查询
-
标量子查询 一行一列
-
列子查询 一列多行
-
行子查询 一行多列 搭配 in any/some all 使用
其中 any 要大于最小值 小于最大值 > min < max
-
表子查询 类似一张表 多行多列
一 where 后面的子查询
标量子查询
谁的工资比 Abel 高?
1 查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
2 条件为> Abel 的 salary 即
select *
from employees
where salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
)
2 查询job_id与141号员工相同,salary比143号员工多的员工
1 查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
2 查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
3 条件满足上面两条
SELECT *
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id = 143
);
3
列子查询
查询location_id是1400或1700的部门中的所有员工姓名
1 查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
查询员工姓名,要求部门号是上面列表中的某一个
SELECT last_name,department_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
2 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
查询员工号、姓名、job_id 以及salary,salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
行子查询
查询员工编号最小并且工资最高的员工信息
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
#②查询最高工资
SELECT MAX(salary)
FROM employees
#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
二 select 后面
查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d
GROUP BY department_id;
三、from后面
将子查询结果充当一张表 ,将表与别的表连接
查询每个部门的平均工资的工资等级
1 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
2 将该表连接登记表 一定要起别名
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exists后面(相关子查询)
exists(完整的查询语句)
结果:
1或0
SELECT EXISTS(
SELECT employee_id
FROM employees
WHERE salary=300000
);