目录
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= 即可