mysql知识点总结

一、mysql基础
- 1)mysql存储结构：数据库->表->数据 sql语句
- 2)管理数据库
增加：create database 数据库 default character utf8;
删除：drop database 数据库;
修改：alter database 数据库 default character gbk;
查询：show databases / show create database 数据库;
- 3)管理表
选择数据库：use 数据库
增加：create table 表 (字段名1 字段类型，字段名2 字段类型......);
删除：drop table 表;
修改：添加字段：alter table 表 add[column]字段名 字段类型;
删除字段：alter table 表 drop[column]字段名;
修改字段类型：alter table 表 modify 字段名 新的字段类型;
修改字段名称：alter table 表 change 旧字段名 新字段名 字段类型
修改表名称：alter table 表 rename[to] 新表名;
查询：show tables / desc tableName;
- 4)管理数据：
增加：insert into 表(字段1，字段2，。。。)values(值1，值2…..);
删除：delete from 表 where 条件;
修改：update 表 set 字段 1 = 值 1，字段 2=值 2…..where 条件;
查询：
4.1）所有字段：select * from 表;
4.2）指定字段：select 字段1,字段2…..from 表;
4.3）指定别名：select 字段1 as 别名 from 表;
4.4）合并列：select(字段1+字段2)from 表;
4.5）去重：select distinct 字段 from 表;`
4.6）条件查询：

• a）逻辑条件：and（与） or（或）select * from 表 where 条件 1 and/or 条件2;

• b）比较条件：> < >= <= = <> between and(在……之间)select * from 表 where servlet >= 90;

• c）判空条件：判断 null：is null / is not null
  判断空字符串：=" / <>"

• d）模糊条件：like
  %：替换任一个字符
  —：替换一个字符
  4.7）分页查询：limit 起始行，查询行数
  起始行从0开始
  4.8）排序：order by 字段 asc/desc
  asc：正序
  desc：倒序
  4.9）分组查询：group by 字段
  4.10）分组后筛选：having 条件
  二、SQL语句的分类：

• DDL：数据定义语言create/drop/alter
• DML:数据操作语句insert/delete/update/truncate

• DQL:数据查询语言select/show
  三、数据约束：对用户操作表的数据进行约束

• 3.1默认值
  作用：当用户对使用默认值的字段不插入值的时候，就使用默认值
  注意：
  1）对默认值字段插入null是可以的
  2）对默认值字段可以插入非null

• 3.2非空
  作用：限制字段必须赋值
  注意：
  1）非空字符必须赋值
  2）非空字符不能赋null
  举例–需求：gender字段必须有值（不为null）
  CREATE TABLE student(
id INT,
name VARCHAR(20),
gender VARCHAR(2)NOT NULL--非空
)
--非空字段必须赋值
INSERT INTO student (id,name) VALUES(1,'李四');
--非空字符不能插入null
INSERT INTO student(id,name,gender) VALUES(1,'李四',NULL);

• 3.3唯一UNIQUE
  作用：对字段的值不能重复
  注意：
  1）唯一字段可以插入null
  2）唯一字段可以插入多个null

• 3.4主键PRIMARY KEY
  作用：非空+唯一
  注意：
  1）通常情况下，每张表都会设置一个主键字段，用于标记表中每条记录的唯一性
  2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段

• 3.5自增长 AUTO_INCREMENT
  --不能影响自增长约束
DELETE FROM student;
--可以影响自增长约束
TRUNCATE TABLE student;


• 3.6外键FOREGIN KEY
  注意：
  1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上
  2）主表的参考字段通用为主键
  3）添加数据：先添加主表，再添加副表
  4）修改数据：先修改副表，再修改主表
  5）删除数据：先删除数据，再删除主表

 --声明一个外键约束
 CONSTRAINT 外键名 FOREIGN KEY(外键) REFERENCES 参考表（参考字段）ON UPODATE CASCADE ON DELETE CASCADE;
 级联修改： ON UPDATE CASCADE
 级联删除： ON DELETE CASCADE

四、关联查询（多表查询）
多表查询规则：1）确定查询哪些表 2）确定哪些字段 3）表与表之间连接条件（规律：连接条件数量是表数量-1）
- 4.1交叉连接查询（不推荐，产生笛卡尔积现象）
- 4.2内连接查询：只有满足条件的结果才会显示
- 4.3左外连接查询：使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
注意：左外连接：左表的数据一定会完成显示

• 4.4右外连接查询：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
• 注意：右外连接：右表的数据一定会完成显示

五、数据库设计
设计原则：建议设计的表尽量遵守三大范式

• 第一范式：要求表的每个字段必须是不可分割的独立单元
• 第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖

• 第三范式：在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系
  六、存储过程：带有逻辑的sql语句

• 6.1存储过程特点
  1）执行效率非常快，存储过程是在数据库的服务端执行的
  2）移植性很差，不同数据库的存储过程是不能移植

• 6.2存储过程语法
  DELIMITER $ --声明存储过程的结束符
CREATE PROCEDURE pro_test() --存储过程名称（参数列表）
BEGIN --开始
--可以写多个sql语句； --sql语句+流程控制
END $ --结束 结束符
--执行存储过程
CALL pro_test(); --CALL存储过程名称（参数）
参数：IN：表示输入参数，可以携带数据到存储过程中
OUT：表示输出参数，可以从存储过程中返回结果
INOUT：表示输入输出参数，既可以输入也可以输出

带有输入参数的存储过程
--需求：传入一个员工id，查询员工信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT) --IN输入参数
BEGIN
SELECT * FROM employee WHERE id=eid;
END $
--调用
CALL pro_findById(4);
--删除存储过程
DROP PROCEDURE pro_findById;


  • 6.3 mysql的变量
    全局变量（内置变量）：mysql数据库内置的变量（所有连接都起作用）
    –查看所有全局变量：show variables
    –查看某个全局变量：select @@变量名
    –修改全局变量：set 变量名=新值
    –character_set_client:mysql服务器的接收数据的编码
    –character_set_results:mysql服务器输出数据的编码
    会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。如果断开，那么会话变量全部丢失
    –定义会话变量：set@变量=值
    –定义一个会话变量name，使用name会话变量接收存储过程的返回值
    CALL pro_testOut(@NAME);
    –查看会话变量：select@变量
    SELECT @NAME;
    局部变量：在存储过程中使用的变量叫局部变量，只要存储过程执行完毕，局部变量就丢失

--带有条件判断的存储过程
--需求：输入一个整数，如果1，则返回“星期一”，如果2，返回“星期二”。其他数字返回“错误输入”
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
     IF num=1 THEN
             SET str='星期一'；
     ELSEIF num=2 THEN
             SET str='星期二'；
     ELSE
             SET str='输入错误';
     END IF;
END $
CALL pro_testIf(4,@str);
SELECT @str;

--带有循环功能的存储过程
--需求：输入一个整数，求和。例如，输入100，统计1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
     --定义一个局部变量
     DECLARE i INT DEFAULT 1;
     DECLARE vsum INT DEFAULT 0;
     WHILE i<=num DO
            SET vsum=vsum+i;
            SET i=i+1;
     END WHILE;
     SET result=vsum;
END $
DORP PROCEDURE pro_testWhile;
CALL pro_testWhile(100,@result);
SELECT @result;

--使用查询的结果赋值给变量（INTO）
DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20))
BEGIN
     SELECT empName INTO vname FROM employee WHERE id=eid;
END $
CALL pro_findById2(1,@NAME);
SELECT @NAME;

七、触发器
作用：当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成

--需求：当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
--创建触发器（添加）
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW   --当往员工表插入一条记录时
INSERT INTO test_log(content)VALUES('员工表插入了一条记录');
--插入数据
INSERT INTO employee(id,empName,deptId)VALUES(7,'张三',1);
INSERT INTO employee(id,empName,deptId)VALUES(8,'张三2',1);
--创建触发器（修改）
CREATE TRIGGER tri_empUpd AFTER UPDATE ON emplyee FOR EACH ROW  --当在员工表修改一条记录时
        INSERT INTO test_log(content)VALUES('员工表修改了一条记录');
--创建触发器（删除）
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW  --当在员工表删除一条记录时
       INSERT INTO test_log(content)VALUES('员工表删除了一条记录');

八、mysql权限问题
root：拥有所有权限

--修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
--分配权限账户
GRANT SELECT ON day16.employee TO 'eric'@'localhost'IDENTIFIED BY'123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost'IDENTIFIED BY'123456';