MySQL基础学习自学笔记002_Real

MySQL基础学习自学笔记002_Real

一、SQL语句分类

1、DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构。
2、DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录：增删改查表记录。
3、DCL（Data Control Language）：数据库控制语言，迎来定义访问权限和安全级别。
4、DQL（Data Query Language）：数据查询语言，用来定义查询记录。

二、socket介绍

1、客户端和服务端是使用socket链接的。
2、socket中文翻译：插座，专业学术名：套接字，其实就是计算机和计算机之间连接的方式。
3、一个计算机使用ip+端口使用某种协议就能和另外一个计算机的ip+端口连接通信（TCP/UDP）
4、MySQL使用的是默认端口3306，一个客户端使用一个端口，就能连接上服务。
5、服务器的ip+端口是固定的，客户端往往是随机的。

三、DCL（数据控制语言）

该语言用来定义访问权限
一个项目创建一个用户，一个项目对应的数据库只有一个，这个用户只能对这个数据库有权限，其他数据库该用户操作不了。

1、创建用户：
用户只能在指定IP地址上登陆MySQL：create user 用户名@IP地址 identified by '密码';
用户可以在任意IP地址登录：create user 用户名@'%' identified by '密码'

2、给用户授权：
语法：grant 权限1, ..., 权限n on 数据库.* to 用户名@IP地址; 其中权限1，2，3可以直接用all关键字代替

3、撤销授权：
语法：create revoke 权限1, ..., 权限n on 数据库.* from 用户名@IP地址;撤销指定用户在指定数据库上的指定权限

4、查看权限：
产看执行用户的权限：show grants for 用户名@IP地址;

四、MySql常见的建表约束（Create Table COnstraints）

1、MySQL约束类型：
NOT NULL：非空约束
UNIQUE：唯一约束，取值不允许重复
PRIMARY KEY：主键约束（主关键字），自带非空，唯一，索引
FOREIGN KEY：外键约束（外关键字）
DEFAULT：默认值（缺省值）

五、MySQL常用函数介绍

1、聚合函数：
聚合函数是平时比较常用的一类函数：
（1）COUNT（col）统计查询结果的行数
（2）MIN（col）查询指定列的最小值
（3）MAX（col）查询指定列的最大值
（4）SUM（col）求和，返回指定列的总和
（5）AVG（col）求平均值，返回指定列数据的平均值
2、数值型函数
数值型函数主要是对数值型数据进行处理，得到我们想要的结果。
（1）CEILING（x）返回大于x的最小整数值，向上取整
（2）FLOOR（x）返回小鱼x的最大整数值，向下取整
（3）ROUND（x，y）返回参数x欸但四舍五入的有y位效数的值 四舍五入
（4）TRUNCATE（x，y） 返回数字x截短为y位小数的结果
（5）PI（）返回PI的值（圆周率）
（6）RAND（）返回0到1内的随机值，可以通过提供一个参数使RAND（随机数生成器生成一个指定的）
3、字符串函数
字符串函数可以对字符串类型进行出处理，在程序应用种用处还是比较大的。
（1）LENGTH（s）计算字符串长度函数，返回字符串的字节长度
（2）CONCAT（s1, s1, …, sn）合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或者多个
（3）LOWER(str)将字符串种的字幕转换为小写
（4）UPPER（str）将字符串中的字母转换为大写
（5）LEFT（str, x）返回字符串str中最左边的x个字符
（6）RIGHT（str, x）返回字符串str中最右边的x个字符
（7）TRIM（str）删除字符串左右两侧的空格
（8）REPLACE字符串替换函数，返回替换后的新字符串，REPLACE（name， ‘白’， ‘黑’）
（9）SUBSTRING截取字符串，返回从指定位置开始的指定长度的字符串
（10）REVERSE（str）返回点到字符串str的结果

六、日期函数

（1）CURDATE和CURRENT_DATE两个函数作用相同，返回当前系统的日期值
（2）CURTIME和CURRENT_TIME返回当前系统的时间值
（3）NOW和SYSTEM两个函数的作用相同，返回当前的日期和时间值
（4）UNIX TIMESTAMP获取UNIX时间戳函数，返回一个以UNIX时间戳为基础的无符号整数
（5）FROM UNIXTIME将UNIX时间戳转换为时间格式，与UNIX TIMESTAMP互为反函数
（6）MONTH获取指定日期中的月份
（7）MONTHNAME获取指定日期的月份英文名称
（8）DAYNAME获取指定日期对应的星期几的英文名称
（9）DAYOFWEEK获取指定日期对应的一周的索引位置值
（10）WEEK获取指定日期是一年中的第几周，返回值的范围是否为0到52或1~53
（11）DAYOFYEAR获取指定日期是一年中的第几天，返回值是1~366
（12）DAYOFMONTH获取指定日期是一个月中的第几天，返回值是1~31
（13）。。。

七、流程控制函数

流程控制类函数可以进行条件操作
（1）IF(test, t, f)如果test是真，返回t，否则返回f。
（2）IFNULL(arg1, arg2)如果arg1不是空，返回arg1，否则返回arg2。
（3）NULLIF(arg1, arg2)如果arg1=arg2返回NULL，否则返回arg1。
（4）CASE WHEN[test1] THEN[result1]…ELSE[default]END如果testN是真，则返回resultN，否则返回default。
（5）CASE [test] WHEN[val1] THEN [result]…ELSE[default]END 如果test和valN相等，则返回resultN，否则返回default。
（6）CASE 列名
（7）WHEN condition THEN result

行转列

SELECT
	student_name “姓名”,
	MAX(case course WHEN "语文" THEN score ELSE 0 END) "语文",
	MAX(case course WHEN "数学" THEN score ELSE 0 END) "数学",
	MAX(case course WHEN "英语" THEN score ELSE 0 END) "英语",
FROM
	scoreset
GROUP BY user_name;

八、hash函数和md5函数

经常在密码存储的时候用到的一个函数，md5加密

select md5('123456') '加密后的密码' from dual;

九、MySQL视图入门

视图相当于从原来的数据表中获取数据，然后新建一个只可创建，查询和删除的信标来存放这些数据（一般情况下），可以理解为把想要的数据部分截图下来保存，供以后查询使用，此时视图只是为了满足某些数据查询而建立的对象。
当然，也存在可以修改的视图，并且当修改视图数据的时候，原来表里的数据也会一同修改。此时视图的作用就是为了保护数据的安全性而只把部分数据展示出来供他人使用。

CREATE VIEW employees_out(id, first_name, last_name) as
select employee_id, first_name, last_name from employees;

十、数据库设计：三范式

内容：
第一范式：要求有主键，并且要求每一个字段都具有原子性不可再分
第二范式：要求所有非主键字段完全以来主键，不能产生部份依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

十一、常见表关系

一对一 用的不多
一个表和另一个表存在的关系是一对一，此设计不常用，一般都合并为一张表
举例：
学生信息表可以分为基本信息表，和详细信息表
可能有这种需求，需要给某个账户，并对学生表进行操作处理，但是有些私密的信息不能暴露
解决方案：
第一种方案：分为两张表存储，共享主键
第二种方案：分为两张表，外键唯一

一对多
一个表和另一个表存在的关系是一对多
举例：
计算机专业只有一个，但是学计算机专业的学生很多，这就形成了一对多的关系
解决方案：
分为两张表存储，再多的以放添加外键
这个外键字段引用的是另一方中的主键字段

多对多
一个表和另一个表之间存在的关系是多对多
举例：
一个学生会学习很多门课程，一门课程会被很多学生学习
解决方案：
分为三张表，在学生表里面存储学生信息，在课程表里面存储课程信息
然后再建立第三章成绩表，里面有向何生id和课程id，以及对应的成绩。