MySQL 5.7 笔记及常用错误收集

目录

学习笔记

有关MySQL的常见bug收集

更新丢失问题解决：

条件一一定存在，条件二不一定存在的SQL语句

Data truncated for column...

mysqldump程序导出sql文件中文乱码

The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

---

此为我的学习笔记,使用到的样式表SQL资源链接如下：

学生信息表：https://pan.baidu.com/s/1KPHU521crIVtJUGnYvHBmg

学习笔记

#修改数据库字符集
ALTER DATABASE studentinfo CHARACTER SET utf8;

#查询数据库创建
SHOW CREATE DATABASE studentinfo;

#查询表创建
SHOW CREATE TABLE student_info;

#查询表结构
DESC student_info;

#增加字段
ALTER TABLE student_info
    ADD student_home char(10) NOT NULL;

#修改字段
ALTER TABLE student_info
    MODIFY student_home char(20) NOT NULL;

#删除字段
ALTER TABLE student_info
    DROP student_home;

#查询表
SELECT*
FROM student_info;

#where语句
SELECT *
FROM student_info
WHERE student_gender != '男';

#bettwen
SELECT *
FROM student_info
WHERE student_id BETWEEN '081106' AND '081204';

#and or
SELECT *
FROM student_info
WHERE student_gender = '女'
   OR student_id < '081110';

#in
SELECT *
FROM student_info
WHERE student_name NOT IN ('李芳芳', '张伟');

#NULL
SELECT *
FROM student_info
WHERE student_name IS NOT NULL;

#模糊查询
SELECT *
FROM student_info
WHERE student_name LIKE '___';

SELECT *
FROM student_info
WHERE student_name LIKE '王%';

#查询结果去重复
SELECT DISTINCT student_gender
FROM student_info;

#concat用法
SELECT concat(student_name, ':', student_id) AS 'name:id'
FROM student_info;

#升序，默认
SELECT *
FROM student_info
ORDER BY student_id;

#降序
SELECT *
FROM student_info
ORDER BY student_id DESC;

#聚合函数，纵向，null值不参与其中
SELECT *
FROM course;
SELECT count(course_class_hour)
FROM course;
SELECT max(course_class_hour)
FROM course;
SELECT min(course_class_hour)
FROM course;
SELECT sum(course_class_hour)
FROM course;
SELECT avg(course_class_hour) AS avgrage
FROM course;

#group by,having,（获取学分大于1的课程，然后依据学时进行分组，最后将个数大于1的组取出）
SELECT course_class_hour, count(*)
FROM course
WHERE course_credit > 1
GROUP BY course_class_hour
HAVING count(*) > 1;

#限制limit(从第三个数据开始，查四行）
SELECT *
FROM course
LIMIT 2,4;

#引用完整性约束（删除有引用关系的表，要先删除从表，再删除主表）
#添加外键
ALTER TABLE student_course
    ADD CONSTRAINT fk_student_course_student_id FOREIGN KEY (student_id) REFERENCES student_info (student_id);
ALTER TABLE student_course
    ADD CONSTRAINT fk_student_course_course_id FOREIGN KEY (course_id) REFERENCES course (course_id);
SHOW CREATE TABLE student_course;

#唯一约束（unique）数据不能重复，可以为null
#设置自动增长(AUTO_INCREMENT)，要和主键配合使用并且列为数值类型;

#多表查询
#合并结果集(UNION会去除重复记录，UNION ALL不会）
SELECT *
FROM student_info
UNION
SELECT *
FROM student_info_two;

#连接查询
#笛卡尔积（错误）
SELECT *
FROM student_info,
     student_info_two;

#关系表连接(内连接）
#MySQL方言
SELECT student_name, student_course_achievement, course_name
FROM student_info,
     student_course,
     course
WHERE student_info.student_id = student_course.student_id
  AND student_course.course_id = course.course_id;

#SQL普通话
SELECT student_name, student_course_achievement, course_name
FROM student_info
         INNER JOIN
     student_course
         INNER JOIN
     course
     ON student_info.student_id = student_course.student_id
         AND student_course.course_id = course.course_id;

#关系表连接(外连接）
#左连接,以左表为主
SELECT student_name, course_id, student_course_achievement
FROM student_info
         LEFT JOIN student_course
                   ON student_info.student_id = student_course.student_id;

#又连接，以右边为主
SELECT student_name, course_id, student_course_achievement
FROM student_info
         RIGHT JOIN student_course
                    ON student_info.student_id = student_course.student_id;

#子查询
#FROM语句后
SELECT student_name, course_name, student_course_achievement
FROM (SELECT student_name, student_course_achievement, course_name
      FROM student_info
               INNER JOIN
           student_course
               INNER JOIN
           course
           ON student_info.student_id = student_course.student_id
               AND student_course.course_id = course.course_id) AS child_table
WHERE course_name = '离散数学'
  AND student_course_achievement < 70;

#WHERE语句后
#单行单列
SELECT student_id, course_id, student_course_achievement
FROM student_course
WHERE student_id = (SELECT student_id FROM student_info WHERE student_name = '王刚');

#多行单列
#ALL（全部满足） ANY（只要有满足的就可以）
SELECT student_id, course_id, student_course_achievement
FROM student_course
WHERE student_id = ANY (SELECT student_id FROM student_info WHERE student_name IN ('王刚', '程明'));

#事务
START TRANSACTION;#开始事务
UPDATE course
SET course_class_hour=100,
    course_credit=8
WHERE course_name = '程序语言设计';
UPDATE course
SET course_class_hour=98,
    course_credit=7
WHERE course_name = '离散数学';
COMMIT;#提交（只有提交后的数据，在数据库中才真正更行，否则回滚就前功尽弃）
ROLLBACK;#回滚
SELECT*
FROM course;

#时间处理函数
SELECT curdate();#显示年月日
SELECT curtime();#显示时分秒
SELECT now();#显示当前时间
SELECT week(now());#显示当前是第几周
SELECT year(now());#返回当前的年份
SELECT hour(now());#返回当前的时
SELECT minute(now());#返回当前的分
SELECT datediff('2021-12-4', now());#计算后一个日期距离前一个日期的天数
SELECT adddate(now(), 10);
#计算在当前日期后加上十点后的日期

#字符串处理函数
SELECT concat('qian', 'qian');
SELECT insert('hello world', 7, 5, 'baby');
SELECT upper('hello');
SELECT lower('WORLD');
SELECT substring('hello world', 3, 5);

#创建用户
CREATE USER 'qianqian'@'localhost"' IDENTIFIED BY '12345';
#授权
GRANT ALL ON studentinfo.* TO 'qianqian'@'localhost"';
#撤权
REVOKE ALL ON studentinfo.* FROM 'qianqian'@'localhost"';
#删除用户
DROP USER 'qianqian'@'localhost"';

#视图
#创建视图(单表视图可更新，不建议使用，多表视图和聚合函数视图不可更新）
CREATE VIEW student_info_view
AS
SELECT student_name, student_course_achievement, course_name
FROM student_info,
     student_course,
     course
WHERE student_info.student_id = student_course.student_id
  AND student_course.course_id = course.course_id;

#视图的替换
#方法一
CREATE OR REPLACE VIEW student_info_view
AS
SELECT *
FROM course;
#方法2
    ALTER VIEW student_info_view
    AS
        SELECT student_name, student_course_achievement, course_name
        FROM student_info,
             student_course,
             course
        WHERE student_info.student_id = student_course.student_id
          AND student_course.course_id = course.course_id;
#查询视图
SELECT *
FROM student_info_view;

#删除视图
DROP VIEW student_info_view;

有关MySQL的常见bug收集

更新丢失问题解决：

悲观锁（不推荐）：for update (将事务的查询语句锁上，牺牲了并发功能）

当第一个事务提交后，其他事务的查询语句才会被执行

乐观锁（推荐）：version (非锁，一种机制）

为每一行数据添加一个version列，每次更新此值都会加一，表示跟新一次，此方法可确保每个事务的查询和更新操作衔接（中间无其他数据插入。

条件一一定存在，条件二不一定存在的SQL语句

SELECT *FROM student_info WHERE student_major='通信工程' AND student_gender=(1=2)

 

Data truncated for column...

解决方案：

检查SQL语句，不能插入"" ,要用null代替

 

mysqldump程序导出sql文件中文乱码

解决方案：mysqldump -u root -p --default-character-set=gbk mydatabase studentsinfo >aaa.sql;  （将导出的编码方式设置为GBK)（后来发现用cmd就都没问题了。。。不用PowerShell）

 

The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

解决方案：在my.ini 中的[mysqld]下设置secure-file-prive=   即可