MySQL - 基础笔记

一、为何学习数据库
二、数据库相关概念
三、数据库存储数据的特点
四、常见的数据库管理软件
五、DQL语言的学习 (Data query Language)
六、DML语言学习（Data Manipulation(操纵) Language）】
- 6.1 插入语句
七、DDL语言学习（Data Define Language）
- 7.1 创建用户
  - 7.1.1 How To Grant Different User Permissions
八、TCL语言（Transaction control Language）
- 8.1 事务和事务处理
九、DCL语言（权限控制语言）
十、视图讲解
十一、流程控制

一、为何学习数据库

有序存储数据

二、数据库相关概念

DB
- 数据库（Database) 存储数据的仓库
DBMS
- 数据库管理系统（Database manager System）
- 数据库通过DBMS创建和操作的容器
SQL
- 结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言
DBMS
- 基于共享文件系统的DBMS（Access）（？）
- 基于客户机-服务器（C/S）的DBMS（MySQL、Oracle、SqlServer）
每个表具有唯一性

三、数据库存储数据的特点

表中有一个或多个列，列又称为‘字段’

[========]

四、常见的数据库管理软件

4.0 MySQL

4.0.1 优点

开源、免费、成本低
性能强、移植性好
体积小、便于安装

4.1 初始MySQL

TCP/IP 默认端口：3306
Strict Mode: 严格语法模式

4.1.1 安装

C/S 架构软件，安装服务端

4.1.2 服务启动与停止

net start MySQL服务名
net stop MySQL服务名

4.2 服务器登录与登出

mysql -h hostname -P port -u username -p password

不指定host 默认 localhost,Port 默认 3306

mysql -u root -p

退出

exit
quit
ctrl+c

4.3 配置环境变量 MySQL

计算机 > 属性 > 高级属性 > 环境变量 > PATH > 编辑

4.4 MySQL常见命令

查询所有的数据库

show databases;

打开指定数据库

use test;

查看当前库所有表

show tables;

select database(); 返回 当前使用的数据库

创建表

create table stuinfo (
	id int,
	name varchar(20)
	);

查询表结构

desc stuinfo;

查询数据

select * from stuinfo;

显示 MySQL 版本

select version()
mysql --version

语法规范

建议关键字大写，表名，列名小写
每条命令最好用分号结尾
每条命令根据需要，可以进行缩进、或换行
注释
- 单行注释 #注释文字
- 单行注释 -- 注释文字
- 多行注释 /* 注释文字 */

五、DQL语言的学习 (Data query Language)

# 指定使用的库
USE employees;

/*
1.查询列表可以是: 表中字段、常量值、表达式、函数
2。查询的结果是一个虚拟的表格
*/

# 查询单个字段（列）
Select last_name FROM employees;

# 查询多个字段
SELECT last_name,salary,email FROM employees;

SELECT `last_name`,`salary`,`email` FROM employees;

5.1 起别名

1. 便于理解
2. ‘查询字段有重名的情况，使用别名区分

方式一：使用AS

SELECT 100%98 AS 结果；

方式二：使用空格

SELECT last_name 姓 FROM employees;

案例：查询salary，显示结果为out put

SELECT salary AS "out put" FROM employees;

去重

SELECT DISTINCT department_id FROM employees;

+号的作用

案例：查寻员工名和姓连接成一个字段，并显示姓名

SELECT last_name+first_name  "姓名" FROM employees；

mysql 中的 + 号：
- 仅有一个功能：运算符
- select 100+90
- select '123'+90 试图将字符型转换为数值型，如果成功，则继续做加法运算
  如果转换失败，将字符型转换为0
- select 'john' + 90;
- select null + 0；只要其中一方为null，则结果肯定为null

字符串拼接

SELECT CONCAT('a','b','c')  结果;

SELECT CONCAT(last_name,first_name) 姓名 from employees;

进阶2：条件查询

SELECT 查询列表 FROM 表明 WHERE 筛选条件；

按条件表达式筛选
- 条件表达式： >, <, =, !=, <>, >=, <=
按逻辑表达式筛选
- 逻辑运算符： &&, ||, !
- ```
        AND, OR, NOT
```
- 模糊查询：like, between and , in, is null

按条件表达式筛选

案例1：

安全等于 <=>

查询没有奖金的

案例1：查询没有奖金的员工名和奖金率

SELECT last_name,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

案例2：查询奖金为12000的员工

SELECT last_name, commission_pct
FROM employees
WHERE salary <=> 12000;

is null / <=>
- is null: 仅判断NULL值，可读性高
- <=>: 既可以判断NULL值，又可以判断普通的数值，可读性较差

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪

进阶3：排序查询

SELECT * FROM employees 
WHERE 筛选条件
ORDER BY 排序列表 ASC|DESC;
ASC 升序 | DESC 降序
默认 ASC 升序

案例: 员工信息，按工资从高到低排序；

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;

进阶4：常见函数

功能：类似于 Java中方法
- 隐藏实现细节
- 提高代码重用性

SELECT 函数名（实参列表） [FROM 表];

特点：1.方法名 2. 功能

一字符函数

length

SELECT length('JHAN');
SELECT length('张三丰'); 一个汉字占3个字节

concat 字符串拼接

SELECT concat(last_name,first_name) from employees;

upper lower

SELECT UPPER（'john'）；
SELECT lower（'JOHN'）；
select concat（UPPER(last_name),lower(first_name)） from employees;

substr,substring 截取字符
- 截取从索引处，指定长度的字符

SELECT substr('ABC+def',5) out_put;
SELECT substr('ABC+def',1,3) out_put;

instr 返回起始索引

SELECT INSTR('qwe+asd','asd') FROM employees;

二数学函数

round 四舍五入

SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

ceil 向上取整

SELECT CEIL(-1.02); #-1

floor 向下取整

SELECT Floor(9.99); # 9

truncate 截断保留多少位小数

SELECT truncate(1.69999,1);

mod 取余

SELECT MOD(10,3); # 10 - 10 / 3 * 3
SELECT 10%3;

三日期函数

now() 返回当前系统日期+时间

SELECT NOW();

curdate 返回系统日期,不包含时间

SELECT CURDATE();

curtime 返回当前时间,不包含日期

SELECT CURTIME();

可以获取指定的部分, 年,月,日,小时,分钟,秒

SELECT YEAR(NOW());
SELECT YEAR('1998-1-1'); # 1998
SELECT YEAR(hiredate) 年 FROM　ｅｍｐｌｏｙｅｅｓ；
SELECT MONTH(NOW()); # 5
SELECT MONTHNAME(NOW()); # MaY

str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS OUT_PUT;
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;

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

select last_name,DATE_FORMAT(hiredate,'%c月/%d日 %Y年') FROM employees WHERE commission_pct is not null;

四其他函数

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

五流程控制函数

if函数 if/else

SELECT IF(10>5,'大','小');
SELECT last_name,IF(commission_pct is null, '没奖金','有奖金') FROM employees;

case函数

java 中
switch (变量\表达式){
	case 常量1 : 语句1;break;
}

case 要判断的变量或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

部门号=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 newsalary
from
employees;

case 函数使用二类似于多重if

java中
if(条件1){
	语句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 salary > 10000 then 'C'
else 'D'
end as 评级
from employees;

分组函数

聚合函数统计函数组函数
sum 求和 avg 平均值 max 最大值 min 最小值 count 计算个数
sum avg 一般用于处理数值型
sum

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT MIN(salary) FROM employees;

count

SELECT COUNT(salary) FROM employees;

忽略null

SELECT SUM(commission_pct）, avg(commission_pct) , SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;

SELECT MAX(commission_pct), MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;

4. 和distinct 搭配

SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;

5. count函数详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

效率：
- MYISAM： count(*) 效率高
- INNODB: count(*)和count（1）效率差不多比count（‘字段’）效率高；

6. 和分组函数一同查询的字段有限制

SELECT AVG(salary), employee_id FROM employees; # 没有意义

案例：查询最大入职与最小入职时间天数差

SELECT DATEDIFF（‘2017-10-1’，‘2017-9-29’）；

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

进阶5：分组查询

语法

 select 分组函数，列（要求出现在 group by 的后面）
 from 表
 where 筛选条件
 group by 分组的列表
 order by 子句

特点

1. 分组查询中的筛选条件分为两类
		数据源          位置			关键字
分组前筛选    原始表           group by 子句的前面	where
分组后筛选    分组后的结果集    group by 子句的后面	having

查询每个部门平均工资

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

案例1：查询工种最高工资

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

案例2：查询每个位置部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
WHERE email like '%a%'
GROUP BY department_id;

查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),manager_id
FROM employees
WHERE  commission_pct is not null
group by manager_id;

添加复杂查询条件

哪个部门员工个数大于2

1. 查询每个部门员工个数
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;

2. 那个部门的员工个数大于2
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING count(*)>2

每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

1. 查询每个工种有奖金的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct is not null
GROUP BY job_id;

2. 筛选最高工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct is not null
GROUP BY job_id
HAVING MAX(salary) > 12000;

案例3 ：领导编号》102 每个领导手下的最低工资》5000的领导编号是哪个以及最低工资

1. 查询每个领导手下最低工资
SELECT MIN(SALARY),manager_id
FROM
WHERE manager_id > 102
GROUP BY manager_id;
2. 最低工资大于5000
SELECT MIN(SALARY),manager_id
FROM
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(SALARY)>5000;

按表达式分组函数分组

按员工姓名长度分组。查询每一组的员工个数，筛选员工个数 > 5 的有哪些

1. 查询每个长度的员工个数
SELECT count(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);
2. 员工数大于5
SELECT count(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;

按多个字段分组

查询每个部门每个工种平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

添加排序

查询每个部门每个工种平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

进阶6：连接查询

多表查询
- 查询的字段来自多个表，就会用到连接查询
- 如果表有别名，查询字段就不能使用原来的表名
  笛卡尔乘积：总共表一*表二行
  添加有效的连接条件

分类：按年代分类：
sql92 sql99

按功能分类：
- 内连接：等值连接非等值连接自连接
- 外连接：左外连接右外连接全外连接
- 交叉连接：

案例1 查询员工名，部门名

SELECT last_name,department_name
FROM employees,departments
WHERE `department_id` = departments.`department_id`;

为表起别名

案例2：员工号工种号工种名

SELECT last_name,department_name
FROM employees e,departments d
WHERE `department_id` = d.`department_id`;

两个表顺序是否可以调换

案例2：员工号工种号工种名

SELECT last_name,department_name
FROM employees e,departments d
WHERE `department_id` = d.`department_id`;

可以加筛选？

案例：查询有奖金的员工名、部门名

SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` is not null;

查询城市名中第二个字符为o的部门名和城市名

可以加分组？

查询城市中部门个数

三表查询

员工名、部门名和所在城市

SELECT last_name,department_id,city
FROM employees e,departments d,locations l
WHERE

非等值连接

案例1：

SELECT salary, grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接

案例1：查询员工名和上级的名称

SELECT *
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

进阶6

语法

SELECT 查询列表
FROM 表1 别名
JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组条件]
[HAVING 筛选条件]
[ORDER BY 排序料件]

特点
1. 添加排序、分组、筛选
2. inner可以省略
3. 筛选条件放在where 后面，连接条件放在 ON 后面
4. inner join 与 sql92 中等值连接效果一致
内连接： inner
外连接
- 左外连接： left [outer]
- 右外：right [outer]
- 全外： full [outer]
交叉连接:cross

一内连接

语法

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件；

案例1：员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

案例2：查询名字中包含e的员工名和工种名

案例3：查询部门个数》 3的城市名称和部门个数

案例4：查询每个部门的员工个数>3的部门名和员工个数，并按个数降序

案例5：查询员工名、部门名、工种名、并按部门名降序 (三表联查)

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;

二）非等值连接

查询员工的工资级别

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

查询工资级别个数》20

SELECT count(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY grade_level
HAVING COUNT(*)>20;

三）自连接

案例1：按员工名，上级名

二、外连接

一个表有，另一个表中没有的记录
特点
1. 外连接的查询结果为主表中所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有匹配的。显示NULL
  外连接查询结果=内连接+主表中有而从表中没有的记录
2. 左外连接，left join 左边的是主表
  右外连接，right join 右边是主表
3. 左外和右外交换两个表顺序，可以实现相同效果
4. 全外连接 = 内连接的结果+表1中有但表2没有的+表2中有但表1中没有的
语法：
SELECT 查询列表
FROM 表1 别名

交叉连接（笛卡尔乘积）

SELECT *
FROM 表1 别名1
CROSS JOIN 表2 别名2;

案例1：查询哪个部门没有员工

六、DML语言学习（Data Manipulation(操纵) Language）】

6.1 插入语句

七、DDL语言学习（Data Define Language）

7.1 创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

At this point newuser has no permissions to do anything with the databases. In fact, even if newuser tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

7.1.1 How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the SELECT command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users’ privileges

To provide a specific user with a permission, you can use this framework:

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

Note that when revoking permissions, the syntax requires that you use FROM, instead of TO as we used when granting permissions.

You can review a user’s current permissions by running the following:

SHOW GRANTS FOR 'username'@'localhost';

八、TCL语言（Transaction control Language）

8.1 事务和事务处理

九、DCL语言（权限控制语言）

十、视图讲解

十一、流程控制

posted @ 2021-05-13 22:44 7bGWFm 阅读(69) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

7bGWFm

MySQL - 基础笔记

一、为何学习数据库

二、数据库相关概念

三、数据库存储数据的特点

四、常见的数据库管理软件

4.0 MySQL

4.0.1 优点

4.1 初始MySQL

4.1.1 安装

4.1.2 服务启动与停止

4.2 服务器登录与登出

4.3 配置环境变量 MySQL

4.4 MySQL常见命令

语法规范

五、DQL语言的学习 (Data query Language)

5.1 起别名

方式一： 使用AS

方式二： 使用空格

案例： 查询salary，显示结果为out put

去重

+号的作用

案例：查寻员工名和姓连接成一个字段，并显示姓名

字符串拼接

进阶2： 条件查询

按条件表达式筛选

案例1：

安全等于 <=>

查询没有奖金的

案例2： 查询奖金为12000的员工

进阶3：排序查询

案例: 员工信息，按工资从高到低排序；

案例2: 部门编号 大于等于 90 的 员工信息 按照入职时间排序；

进阶4： 常见函数

一 字符函数

二 数学函数

三 日期函数

四 其他函数

五 流程控制函数

分组函数

忽略null

4. 和distinct 搭配

5. count函数详细介绍

6. 和分组函数一同查询的字段有限制

案例：查询最大入职与最小入职时间天数差

进阶5： 分组查询

查询每个部门平均工资

案例1：查询工种最高工资

案例2：查询每个位置部门个数

查询邮箱中包含a字符的，每个部门的平均工资

查询有奖金的每个领导手下员工的最高工资

添加复杂查询条件

哪个部门员工个数大于2

每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

案例3 ： 领导编号 》102 每个领导手下的最低工资》5000的领导编号是哪个以及最低工资

按表达式分组函数分组

按员工姓名长度分组。查询每一组的员工个数，筛选员工个数 > 5 的有哪些

按多个字段分组

添加排序

进阶6： 连接查询

案例1 查询员工名，部门名

为表起别名

案例2：员工号 工种号 工种名

两个表顺序是否可以调换

案例2：员工号 工种号 工种名

可以加筛选？

案例：查询有奖金的员工名、部门名

查询 城市名中第二个字符为o的部门名和城市名

可以加分组？

查询 城市中部门个数

三表查询

员工名、部门名和所在城市

非等值连接

案例1：

自连接

案例1： 查询员工名和上级的名称

进阶6

一 内连接

案例1：员工名、部门名

案例2：查询名字中包含e的员工名和工种名

方式一：使用AS

方式二：使用空格

案例：查询salary，显示结果为out put

进阶2：条件查询

案例2：查询奖金为12000的员工

案例2: 部门编号大于等于 90 的员工信息按照入职时间排序；

进阶4：常见函数

一字符函数

二数学函数

三日期函数

四其他函数

五流程控制函数

进阶5：分组查询

案例3 ：领导编号》102 每个领导手下的最低工资》5000的领导编号是哪个以及最低工资

进阶6：连接查询

案例2：员工号工种号工种名

案例2：员工号工种号工种名

查询城市名中第二个字符为o的部门名和城市名

查询城市中部门个数

案例1：查询员工名和上级的名称

一内连接

案例3：查询部门个数》 3的城市名称和部门个数

案例4：查询每个部门的员工个数>3的部门名和员工个数，并按个数降序

案例5：查询员工名、部门名、工种名、并按部门名降序 (三表联查)

交叉连接（笛卡尔乘积）

六、DML语言学习（Data Manipulation(操纵) Language）】

七、DDL语言学习（Data Define Language）

八、TCL语言（Transaction control Language）

九、DCL语言（权限控制语言）