MYSQL数据库笔记

本导图是学习SQL期间整理的个人笔记，知识梳理清楚，结构清晰，逻辑严密，非常实用，非常适合SQL的初级学习者。

MySQL

• 六大约束
  • primary key 主键。唯一不重复，非空，可被外键引用
    • ①联合主键，主键是由一张表中多个字段组成的。 PRIMARY KEY [列名1, 列名2, ...] 1062[err]：Duplicate entry '...' for key 'PRIMARY ②主键自增长，主键值无需用户输入，由系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。 列名 数据类型（整数型） PRIMARY KEY AUTO_INCREMENT
  • unique 唯一。唯一不重复，非空，不可被外键引用
  • check (条件) 检查。对域的限制
    • s_sex char(2) check (s_sex in ('男','女')), s_age int(2) check (s_age>=18 and s_age<=35),
  • not null 非空
  • default 'xxx' 缺省
    • 注意含中文时数据库属性：utf-8
  • references 外键。引用于/来自于主表，数据类型与主键一致，数据为NULL或者与主表一致 列名 int, CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 主表名(主表列名)
    • 删除表时需先删除外键表再删除被引用的主键表，建表反之
• 数据类型
  • int 长整数，4字节
  • float 浮点型
  • numeric(p,d) 定点数。由p位数组成，小数点后有d位数字
  • char(n) 定长字符型
  • varchar(n) 变长字符型。最大为n
  • date 日期，'年-月-日'
  • time 时间，时:分:秒
• DDL对表操作
  • 建立表：CREATE TABLE 表名(属性名 数据类型 约束，...);
  • 修改表：ALTER TABLE 原表名 RENAME 新表名;
  • 删除表：
    • DROP TABLE 表名; 删除整个表
• DDL对列操作 ALTER TABLE 表名 +
  • 增加列：add 列名 数据类型；
  • 删除列：drop 列名；
  • 修改列：change 原列名 新列名 新数据类型；
  • 修改列数据类型：modify 列名 数据类型；
  • 增加约束：ADD PRIMARY KEY(列名);
  • 删除约束：DROP PRIMARY KEY； DROP FOREIGN KEY 外键名;
  • 修改约束类型：
    • 外键：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 主表名(主表列名);
    • 主键：ALTER TABLE 表名 ADD PRIMARY KEY(列名);
• DML/DQL对数据操作
  • 增：
    • ① 一 一对应 ②未出现值取NULL，非空、主键、唯一等不可为NULL
    • 批量添加：INSERT INTO 表名 VALUES('列值1','列值2', ...);
    • 部分添加：INSERT INTO 表名 （'列名1', '列名3'）VALUES (列值1', '列值3');
    • 复制数据：INSERT INTO A表(列名a1,列名a2) SELECT (列名b1,列名b2) FROM B表;将查询记录插入表中
      • SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。
  • 删：
    • 清空表内数据：TRUNCATE TABLE 表名; 保留表结构
    • 删除单行数据：DELETE FROM 表名 [WHERE子句]
      • 不带条件子句时删除所有数据，保留表结构。 T：作用于DDL，直删除原来的表，再重新创建一个一模一样的新表，执行快。D：作用于DML，逐行删除，记录日志，可回滚恢复数据。
  • 改：
    • UPDATE 表名 SET 列名=新值, ... [WHERE子句]
      • ①数据存在时update，不存在时insert ②SET子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值（DEFAULT）
    • 修改单列数据：UPDATE 表名 SET 列名=列值;当列值为NULL时清空该列数据
  • ⭐查：
    • SELECT 目标表达式1, 目标表达式2，... AS 列别名 FROM 表1, 表2 … AS 表别名 WHERE 条件1，条件2，... [GROUP BY 列名：按其分组 [HAVING 条件] ] [ORDER BY 列名：按其排序]（ASC升序|DESC降序） LIMIT ...
    • SELECT * FROM 表名;查看全表
    • 条件/运算符：
      • b比较：>, <, = ,!= ,>= ,<=
      • k空值：is (NOT)NULL
      • f范围：列名(not) between...and...
      • j集合：where 列名 (not) in ...
        • in是多个or的缩写，查找列值属于指定集合的元组
      • l逻辑：and与，or或，not/! 非
      • s算术：+，-，*，/，%
        • SELECT 工资+提成 FROM emp;
      • h函数：
        • 用于select子句和having子句中，不可用于where子句
        • 字符长度：length(列名)=字节长度
        • j聚合：count（统计查询结果的行数），sum（列值总和），avg（平均值），max，min
          • count(*) 统计元组个数
        • q去重：distinct
        • ALL：
          • all关键字必须与一个【比较操作符】一起使用，对于子查询返回的列中的所有值，如果比较结果为true，则返回true
        • LIMIT 初始位置，记录数
          • 从0开始
    • 模糊查询：列名 (not) like '匹配串'
      • 查找列值与匹配串相匹配的元组 其中ASCⅡ汉字2个字符，GBK汉字1个字符
      • %：替换任意长度字符
      • _：替换单个字符
        • 本身含通配符%或者-时，末尾加escape'/'（换符字符） eng：SELECT * FROM emp WHERE ename LIKE '/%ol'ESCAPE'/';替换%
    • z子查询/嵌套查询：select-from-where(子查询select-...)
      • eng：查询(工资高于(30号部门所有人的工资))的员工信息 select * from emp where sal > all(select sal from emp where deptno=30) 等同于 select * from emp where sal > (select max(sal) from emp where deptno=30)
    • f⭐分组查询：
      • GROUP BY(列名)：“每个、各个、不同的”
      • HAVING子句： where：过滤数据行，根据分组前/表中字段直接过滤，不可使用聚合函数 having：过滤分组，根据分组后/已查询出的字段过滤（select或group by），可使用聚合函数
        • ①SELECT sal,job,deptno FROM emp WHERE deptno > 10; 等同于 SELECT sal,job,deptno FROM emp HAVING deptno > 10; ②SELECT sal,job FROM emp GROUP BY deptno HAVING deptno > 10; SELECT sal FROM emp GROUP BY job HAVING deptno > 10; -- 报错 ③查询(每个部门)的平均工资，最高工资，平均工资大于2000（分组后对组的过滤） 并且按照平均工资升序排列 SELECT deptno,AVG(sal),MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000 ORDER BY AVG(sal); ④查询(每个职位)的最高工资，最低工资，和员工数目，不包含工资低于100的员工（分组前对表进行过滤） SELECT job,MAX(sal),MIN(sal),COUNT(ename) FROM emp WHERE sal > 1000 GROUP BY job;
    • l⭐连接查询：
      • d笛卡尔积：SELECT * FROM 表... ;
      • n内连接：SELECT 列名 FROM 表1 INNER JOIN 表2 ON 连接条件; 交集，等同于 SELECT 列名 FROM 表名1,表名2, ... WHERE 连接条件;
      • w外连接：
        • z左外连接：SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 连接条件; 左表所有数据。即以左表为基准表，右表没有的话补空
        • y右外连接：SELECT 列名 FROM 表1 RIGHT JOIN 表2 ON 连接条件; 右表所有数据。即以右表为基准表，左表没有的话补空
      • q全连接：SELECT 列名 FROM 表1 FULL JOIN 表2 ON 连接条件; 并集
        • MySQL没有全连接，用UNION把左右连接并起来
      • z自连接 → 子查询
        • 查询al的主管姓名 自连接： SELECT e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno AND e1.ename='al'; 子查询 ： SELECT e2.ename FROM emp e2 WHERE e2.empno = (SELECT e1.mgr FROM emp e1 WHERE e1.ename='al');
      • z自然连接：去掉内连接里的重复属性列
• DCL权限操作
  • 一些常用命令行操作：
    • d登录和退出服务器：
      • mysql -h 主机名|主机IP -p port -u 用户名 -p 密码 指定数据库;
      • quit;退出
    • 查看表结构（数据类型、约束）：DESC 库名.表名;
    • 以SQL形式查看表结构（数据类型、约束）：SHOW CREATE TABLE 表名;
    • 查看所有数据库：SHOW DAT
  • c创建用户：create user '用户名'@'主机IP' identified by '密码';
  • c查看用户权限：show grants user '用户名'@'主机IP';
  • x修改用户名字和密码：
    • rename user '用户名'@'主机IP' to '用户名'@'主机IP';仅root可操作
    • set password for '用户名'@'主机IP' = password ('密码');仅root可操作
    • set password = password('新密码');
  • s删除用户：drop user '用户名'@'主机IP';
  • f赋权：
    • q权限内容：列、表、库、用户（逐级上升）
    • grant 权力 on 库.表 to '用户名'@'主机IP';
      • 权力：ALL - 全部权力 库.表：权限对象。*.* - 全部库和表
    • c撤销权限：remoke 权力 on 库.表 from '用户名'@'主机IP';

Linux

• 目录结构-根目录（/）
  • /bin：存放系统命令，二进制目录，普通用户和 root 都可以执行。放在 /bin 下的命令在单用户模式下也可以执行，bin：二进制可执行文件
  • /sbin：保存与系统环境设置相关的命令，只有 root 可以使用这些命令进行系统环境设置，但也有些命令可以允许普通用户查看
  • /usr：（Unix Software Resource）
    • /usr/bin/：存放系统命令，普通用户和超级用户都可以执行。这些命令和系统启动无关，在单用户模式下不能执行
    • /usr/sbin/：存放根文件系统不必要的系统管理命令，如多数服务程序，只有 root 可以使用
    • /usr/lib：应用程序调用的函数库保存位置
    • /usr/local：手工安装的软件保存位置。我们一般建议源码包软件安装在这个位置
  • /lib：系统调用的函数库保存位置
  • /boot：系统启动目录，保存与系统启动相关的文件，如内核文件和启动引导程序（grub）文件等
  • /etc：默认配置文件保存位置。系统内所有采用默认安装方式（rpm 安装）的服务配置文件全部保存在此目录中，如用户信息、服务的启动脚本、常用服务的配置文件等
  • /home：普通用户家目录，如/home/kon
  • /root：root用户的家目录，直接在 / 下
  • /opt：第三方安装的软件保存位置。这个目录是放置和安装其他软件的位置，手工安装的源码包软件都可以安装到这个目录中。
  • /tmp：系统存放临时文件的目录，在该目录下，所有用户都可以访问和写入。建议此目录中不能保存重要数据，最好每次开机都把该目录清空
  • /dev：硬件设备文件保存位置
  • /var：存储动态数据，例如缓存、日志文件、软件运行过程中产生的文件等
    • /var/lib：程序运行中需要调用或改变的数据保存位置。如 MySQL 的数据库保存在 /var/lib/mysql/ 目录中
  • /lost+found：每个分区都有的，意外关机/崩溃时产生的碎片文件
  • /media、/mnt、/misc：挂载目录
  • /run：运行目录，存放系统运行时的数据
  • /srv：服务数据目录。一些系统服务启动之后，可以在这个目录中保存所需要的数据
• 命令：
  • 基本格式：[用户@主机名 ~]# 命令 [-选项] [参数/操作对象]
    • ~：代表用户当前所在的目录，此例中用户当前所在的目录是家目录。/ 是根目录 #：命令提示符，Linux 用这个符号标识登录的用户权限等级。如果是超级用户，提示符就是 #；如果是普通用户，提示符就是 $。
  • cd：- 回到上次所在目录 . 回到当前目录 .. 回到上级目录
  • 文件处理：