mysql数据库基础入门

最新推荐文章于 2022-08-05 16:26:22 发布

学习做游戏中

最新推荐文章于 2022-08-05 16:26:22 发布

阅读量439

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_50645221/article/details/125827846

版权

基础了解

mysql是开源免费的关系型数据库（relational database/RDBMS），数据库支持表达式提取数据，相较于excel，它支持海量数据和多表的联合查询，它是通过sql语句来实现curd的。
在关系模型中，数据分类存放，数据之间有联系，如员工信息、员工岗位、员工考核，对于不同的系统，大多有成熟的数据架构。关系型数据库可以有效组织和管理大量复杂数据，应用在教育、商业、医疗等领域。mysql也支持集群扩展。
与关系型数据库互补的还有NOSql数据库，指对数据分类存放，但数据之间没有关联关系的数据系统，常用于秒杀库存、登录信息、消息通知、新闻内容。对于秒杀系统，多线程同时操作会发生超卖、超买，因此单线程的redis（基于内存）就可以解决这个问题。nosql数据库没有严格的表解构，非常适合存放通知信息等不太重要的数据
文中代码[]代表可选填字段

mysql安装和配置

对于新手非常推荐使用xampp或国产的phpstudy，也可以参考我之前的文章安装mysql。数据库推荐用mysql 8.0以上的，因为支持json数据格式等新的特性

mysql的数据保存在mysql安装目录下的data文件夹中，里面有和逻辑空间对应的文件夹名，数据表对应的是里面的.ibd文件
配置信息文件保存在my.ini文件中，之前我的安装mysql文章中，也是通过ini文件来启动mysql数据库的
忘记密码的话，phpstudy可以在软件里方便的修改密码。如果是解压缩安装的mysql，就需要创建txt文件ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'；停止mysql服务，net stop 定义的服务名，mysqld --defaults-file="ini配置文件地址" -init-file="之前定义的txt地址" --console 实现root密码的覆盖，之后可以ctrl+c关闭终端，再新开一个终端页面，net start 定义的mysql服务名 来启动服务
mysql的ini配置文件可以设置字符集、端口号、目录地址等，总体分为3大块，客户端配置信息：client（图形界面配置，客户端的默认连接端口等）、mysql（命令行配置，如禁止报错时主板蜂鸣器响）。数据库配置信息：mysqld（比较多）
- 存储引擎默认配置INNODB，因为这个引擎支持事务，可用于保存重要数据
- sql-mode：配置是否严格存储模式，开启后，数字类型就不能存储到字符类型的字段中了
- slow-query-log：慢查询日志，开启后，可用于分析语句执行速度慢的原因
  - long_query_time：查询超过该时间则判定为慢查询
- server-id：数据库集群的时候有用
- thread_cache_size：线程数量，提高sql读写速度

数据库管理工具推荐navicat，非常好用

点击软件上面的用户选项卡，可以创建用户，并能非常精细的分配权限。用root账户新建用户后，并分配权限后，可以看到用新建的账户登录时，只能看到自己有权限的表了

数据库的原则范式

构造数据库必须遵循一定的原则，这种规则就是范式

关系型数据库共有6种范式，一般满足前3条即可：

原子性：这是数据库的基本要求，不满足这一点就不是关系数据库。数据表的每一列都是不可分割的基本数据项，同一列中不能有多个值，也不能存在重复属性
唯一性：数据表中的每条记录必须是唯一的，为了实现区分，通常要为表加上一列用于存储唯一标识，这个唯一属性列被称为主键列
关联性：满足了第三范式也意味着同时满足了第1、2范式。每列都与主键有直接关系，表中非主键的字段之间不要存在传递依赖（利于检索数据，否则即使创建，提取数据也麻烦）。依照此范式，数据可柴扉保存到不同的数据表，彼此保持关联

字段约束有4种：

主键约束：PRIMARY KEY，字段值唯一，且不能为NULL。建议主键一定要使用数字类型，因相对于字符串，数字的检索速度非常快，也能很快的判断出是否重复。如果设为数字类型，可设置自动增长id INT PRIMARY KEY AUTO_INCREMENT, 此时插入数据的时候，可以不用给主键值，数据库会使用计数器自动生成主键
非空约束：NOT NULL，字段值不能为NULL。NULL为没有值，而不是""(空字符串)，xx BOOLEAN NOT NULL DEFAULT FALSE，可设置默认值
唯一约束：UNIQUE，字段值唯一，且可以为NULL
外键约束：FOREIGN KEY，保持关联数据的逻辑性。按照依赖关系分为父表（部门）和子表（员工），外键约束的定义是写在子表上的，FOREIGN KEY (子表字段) REFERENCES 父表名 (父表字段)。删除的时候，要先删除子表数据（员工）后才能删除父表。因此外键约束一旦形成闭环，将无法删除任何一张表的记录。真实的环境中，有上百张数据表都是正常的，所以一般情况下避免使用外键

索引机制：

索引可以提高数据的查询速度，所以要给查找的字段加上索引，数据库会给对应字段排序生成二叉树进行二分查找
建表时索引：CREATE TABLE 表名称 (......, INDEX [索引名称] (字段), ......);，默认的索引名称是字段名
对已经存在的表添加索引：CREATE INDEX 索引名称 ON 表名（字段）； 或者ALTER TABLE 表名称 ADD INDEX [索引名]（字段）；
查看索引：SHOW INDEX FROM 表名; ，主键自带排序索引，所以执行后，肯定会显示主键
删除索引：DROP INDEX 索引名称 ON 表名;
使用原则：

数据量大，经常被查询的数据表，挑选常用的检索字段设置索引
插入量特别多，大于查询量，不适合索引，如记录日志
索引只添加在经常被用作检索条件的字段上面，因为二叉树的维护成本很高
不要在大字段上创建索引，因为字符越多，排序的时间越长

起名规范：

在数据库中有些表是真实存在的（true），有些是不存在的（数据库中称为view），t_开头为真实存在的，否则为v_开头

sql语言（DDL）

DML（数据操作语言）：增删改查
DCL（数据控制语言）：用户、权限、事务
DDL（数据定义语言）：定义逻辑库、数据表、视图、索引

sql语句不分大小写，但字符串分大小写
sql必须以分号结尾
对空白和换行没有限制，但不能破坏语法
注释：# 或/* 多行注释 */
创建逻辑库、数据表：
- CREATE DATABASE 逻辑库名称;
- SHOW DATABASES;
- DROP DATABASE 逻辑库名称;
- USE 逻辑库名称;
- DESC 数据表名; # 查看表结构
- SHOW CREATE TABLE 数据表名; # 数据表在创建时的sql语句
- DROP TABLE 数据表名;

新建数据表

CREATE TABLE数据表(
	列名1 数据类型 [约束] [COMMENT注释]，
	列名2 数据类型 [约束] [COMMENT注释]，
	......
	)[COMMENT=注释]；

插入数据

INSERT INTO 表名 VALUES （XX，XX）;

添加字段

ALTER TABLE 表名称
	ADD 列1 数据类型 [约束] [COMMENT 注释],
	ADD 列2 数据类型 [约束] [COMMENT 注释],
......;

修改字段类型和约束

ALTER TABLE 表名称
	MODIFY 列1 数据类型 [约束] [COMMENT 注释],
	MODIFY 列2 数据类型 [约束] [COMMENT 注释],
......;

修改字段名称

ALTER TABLE 表名称
	CHANGE 列1 新列名1 数据类型 [约束] [COMMENT 注释],
	CHANGE 列2 新列名2 数据类型 [约束] [COMMENT 注释],
......;

删除字段

ALTER TABLE 表名称
	DROP 列1,
	DROP 列2,
......;

数据类型：

ENUM：枚举类型
BOOLEAN：在mysql中，会把布尔映射成tinyint，分别为0，1
VARCHAR：非固定长度，需要传入最大长度；CHAR：固定长度字符串

数据库的查询（DML）

使用navicat，在逻辑空间中右键-运行sql文件，可以导入数据库
执行优先级：FROM（决定数据来源）->SELECT->GROUP BY->SELECT->ORDER BY->LIMIT（先排序再分页）

基本查询

SELECT ename, sal *12 AS "annual salary" FROM t_emp; 通过AS 重命名
SELECT......FROM…LIMT 起始位置，偏移量； 起始位置默认为0，可以省略
SELECT ...FROM ...ORDER BY 列名 [ASC|DESC];默认mysql不会对查询的结果集排序，ORDER默认为升序ASC。如果是数字就按数字大小排序，日期按日期大小排序，字符串按字符集序号排序，如果2条排序字段内容相同则默认按主键升序排序
- 可以规定首要排序条件和次要排序条件，SELECT empno,ename,sal,hiredate FROM t emp ORDER BY deptno, sal DESC,hiredate ASC LIMIT 0,5;
SELECT DISTINCT 字段 FROM ...;去除重复，其中使用DISTINCT的SELECT子句只能查询一列数据，如果查询多列，去除重复记录就会失效。且DISTINCT关键字只能再SELECT子句中使用一次，必须写在第一个字段的前面
SELECT .....FROM ...... WHERE 条件 [AND|OR] 条件.....;
条件查询会用到4种运算符：
- 数学运算符：+、-、*、/、%
- 比较运算符：>、>=、<、<=、=、!=、IN、IS NULL、IS NOT NULL、BETWEEN AND、LIKE（占位符有%、_等）、REGEXP（"^[\\4e00-\\u9fa5]{2,4}"）
- 逻辑运算符
- 按位运算符
  SELECT * FROM t_emp WHERE deptno=10 AND (sal+IFNULL(comm,0))>=1500 AND DATEDIFF(NOW(),hiredate)/365>=20; 任何数字*或+NULL都是NULL，所以需要用IFNULL做转换

聚合函数

是用来做统计的函数，如对某一列求和、最大值、最小值、平均值等，如果不加GROUP BY ，聚合函数就对整张表做统计，加上就先对数据分组，然后再对每组进行统计。特殊注意：聚合函数永远不能出现在WHERE子句中，因为WHERE用来做筛选，确定哪些数据要保存下来，再由SELECT做汇总统计，WHERE没执行完，聚合函数做统计也没有意义，范围没确定好就用聚合函数是错误的
- SELECT AVG (sa1+IFNULL (comm,0)) FROM t_emp; 查询平均收入，所以聚合函数只能返回一条记录
- SUM函数用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加
- MAX函数用于获得非空值的最大值，所以空值NULL最好用IFNULL处理一下
- MIN
- AVG用于获取非空值的平均值，非数字数据统计结果为0，所以根据情况用IFNULL处理
- COUNT(*)，用于获得包含空值的记录数,COUNT(列名)用于获得包含非空值的记录数

分组查询

先WHERE确定范围，再分组，用GROUP BY子句，通过一定的规则，将一个数据集划分为若干个小的区域，然后针对每个小区域分别进行数据汇总处理。支持多列分组条件逐级分组SELECT deptno,job,COUNT (*), AVG(sal) FROM t emp GROUP BY deptno,job;查询语句中如果含有GROUP BY，那么SELECT子句中可以包括聚合函数，或者GROUP BY子句的分组列，其余内容均不可以出现在SELECT子句中
- 后面加上WITH ROLLUP对分组结果集再次做汇总运算
- GROUP_CONCAT：把分组查询中的某个字段拼接成一个字符串
- SELECT deptno,COUNT (*FROM t_emp GROUP BY 1 ，按照SELECT中的第一个字段做分组字段

HAVING子句

与WHERE作用差不多，也可以把筛选条件放在HAVING中（但是不能这么干，会导致数据量大，正常思维都是先筛选再分组），只不过要放在GROUP BY后面，联合才能使用，可以解决聚合函数用于判断条件的问题，在分组后执行SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG (sal)>=2000;
- HAVING中用聚合函数和某个具体的数做条件判断是没问题的，但与某个具体字段做条件判断不可以（HAVING sal>=AVG(sal)是错误的），需要表连接来实现

表连接

外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录
JOIN是内连接
SELECT e.empno,e.ename,d.dname FROM t_emp e LEFT JOIN t dept d ON e.deptno d.deptno; 左外连接是保留左表所有记录，与右表连接，如果右表没有符合条件的，就用NULL值连接
容易出错的地方是SELECT d.dname,COUNT (d.deptno) FROM t dept d LEFT JOIN t_emp ON d.deptno=e.deptno GROUP BY d.deptno; ，这里如果用COUNT(*)的话，是统计所有有效记录，会把NULL的也算上，这是错误的
UNION关键字可以将多个查询语句的结果进行合并(查询语句) UNION (查询语句) UNION (查询语句) ...... 排除相同的内容，结果集的字段数量和名称要相同 (SELECT d.dname,COUNT (e.deptno) FROM t dept d LEFT JOIN t_emp e ON d.deptno=e.deptno GROUP BY d.deptno) UNION (SELECT d.dname,COUNT (*) FROM t_dept d RIGHT JOIN t emp e ON d.deptno=e.deptno GROUP BY d.deptno); ，要灵活使用COUNT(*)

多行子查询

子查询的效率比表连接的效率低
可以使用IN、NOT IN、ALL、ANY、EXISTS、NOT EXISTS处理多行条件判断，查询比FORD和MARTIN底薪都高的员工信息？ SELECT ename FROM t_ emp WHERE sal > ALL (SELECT sal FROM t_emp WHERE ename IN ("FORD","MARTIN"))

修改数据

INSERT写入多条记录时，通过IGNORE关键字可以忽略约束条件引起的报错，而不影响写入
INSERT支持子查询，根据条件查询出数据后再插入
UPDATE也支持子查询，如查询工资高于指定值的员工降薪，只有mysql中的UPDATE支持表连接语法，ORACLE不支持

通过表连接更新数据。把底薪低于公司平均底薪的员工，底薪增加150元 UPDATE t_emp e JOIN (SELECT AVG(sal)AS avg FROM t_emp) t ON e.sal<t.avg SET e.sal=e.sal+150;,也支持外连接UPDATE t emp e LEFT JOIN t dept d ON e.deptno=d.deptno SET e.deptno=20 WHERE e.deptno IS NULL OR (d.dname="SALES"AND e.sal<2000);
DELETE也支持表连接
WHERE子查询效率低，FROM子查询效率高

mysql函数

数据库也会把一些复杂的功能封装到函数里供调用，也可以自定义函数

数字函数

ABS：绝对值
ROUND：四舍五入
FLOOR：强制舍位到最近的整数
CEIL：强制进位到最近的整数
POWER：幂函数
LOG：对数函数
LN：对数函数
SQRT：开平方
PI：圆周率
SIN：三角函数
COS：三角函数
TAN：三角函数
COT：三角函数
RADIANS：角度转换弧度
DEGREES：弧度转换角度

字符函数

LOWER：转换小写字符
UPPER：转换大写字符
LENGTH：字符数量
CONCAT：连接字符串
INSTR：字符出现的位置
INSERT：插入/替换字符，INSERT("插入字符",插入位置,替换几个字符,"插入的字符")
REPLACE：替换字符
SUBSTR：截取字符串 SUBSTR("字符",开始位置，结束位置)
SUBSTRING：截取字符串 SUBSTRING("字符",开始位置，截取数量)
LPAD：左侧填充字符 SELECT LPAD (SUBSTRING ("13312345678",8,4),11,"*");
RPAD：右侧填充字符 SELECT RPAD(SUBSTRING("李晓娜"，1,1)，LENGTH("李晓娜")/3，"*")； 因为汉字占3位
TRIM：去除首尾空格

日期函数

NOW()：函数能获得系统日期和时间，格式yyyy-MM-dd hh:mm:ss
CURDATE()：函数能获得当前系统日期，格式yyyy-MM-dd
CURTIME()：函数能获得当前系统时间，格式hh:mm:ss
DATE_FORMAT()：函数用于格式化日期，返回用户想要的日期格式
- %Y只返回年份，%m 月份，%d日期，%w星期（数字），%W星期（名称），%j本年第几天，%U本年第几周，%H小时（24），%h小时（12），%i分钟，%s秒，%r时间（12），%T时间（24）
- 查询上半年 SELECT COUNT (*FROM t_emp WHERE DATE_FORMAT (hiredate,"%Y")=1981 AND DATE_FORMAT (hiredate,"%m")<=6;
- mysql中，两个日期不能直接加减，日期也不能与数字加减
DATE_ADD()：日期偏移计算 DATE ADD(日期，INTERVAL 偏移量时间单位) 偏移6个月，再偏移3天 SELECT DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), INTERVAL -6 MONTH), INTERVAL -3 DAY), "%Y/%m/%d");
DATEDIFF：计算日期相差

条件函数

IFNULL ：处理空值
IF：与三元运算符非常像SELECT e.empno,e.ename,d.dname, IF(d.dname="SALES","礼品A","礼品B") FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
条件判断语句：

CASE
	WHEN 表达式THEN 值1
	WHEN 表达式 THEN 值2
	......
	ELSE 值N
END

在update等语句中都可以用

UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN (SELECT deptno,AVG(sal)AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno
SET e.sal=(
	CASE
		WHEN d.dname="SALES"AND DATEDIFF (NOW (),e.hiredate)/365>=20
		THEN e.sal*1.1
		WHEN d.dname="SALES"AND DATEDIFF (NOW (),e.hiredate)/365<20
		THEN e.sal*1.05
END