【MySQL】学习笔记（三）—— 常用命令

一、复习关系型数据库候选码、主码、主属性关系的完整性约束外键可引用的字段类型1主键2unique非主键所以不是只能引用主键哦为什么阿里开发明确不让用数据库级别的外键而是一律改为应用级别的外键因为级联操作。 比如说student表的主键student_id被B,C,D三个表当做了外键。我们修改student_id的时候不仅要修改student表还要查看B,C,D三个表用到这个student_id的地方这就是级联操作。同理删除也是。关系代数基础查询语句依赖的数学基础二、复习SQL Server数据库××语言DDL 定义DML 操作DQL 查询DCL 控制学习MySQL的sql语句之前先看一下几个综合性的sql server的查询语句。1. 综合查询查询各个学院开设的所有课程的学生选课名单及成绩并按成绩降序排列①实验代码错误/*查询各个学院开设的所有课程的及格学生数量大于10的名单及成绩并按成绩降序排列*/ SELECT D.DeptName,A.CourseName,C.GradName,B.Score FROM TB_Course_Info A,TB_Select_Course B,TB_Student_Info C,TB_Dept_Info D WHERE A.CourseIDB.CourseID AND B.GradIDC.GradID AND A.DeptIDD.DeptID AND B.Score60 //4个表需要3个连接点 GROUP BY D.DeptName,A.CourseName //先按照院系分组再按照课程名分组 HAVING Count(*)10 ORDER BY B.Score DESC上面的代码违反了“SELECT的字段要么都聚类要么不聚类”。SELECT的4个字段应该都出现在GROUP BY的后面才对但是这样的话Count(*)10就不对了。所以只能查学生个数没法把学生都列出来呀SELECT D.DeptName,A.CourseName,Count(*) FROM TB_Course_Info A,TB_Select_Course B,TB_Student_Info C,TB_Dept_Info D WHERE A.CourseIDB.CourseID AND B.GradIDC.GradID AND A.DeptIDD.DeptID AND B.Score60 //4个表需要3个连接点 GROUP BY D.DeptName,A.CourseName //SELECT的字段要么都聚类要么不聚类 HAVING Count(*)10 ORDER BY B.Score DESC2. 嵌套查询查询政治面貌为’中国共产党党员’的学生信息相关子查询/不相关子查询/连接查询分别设计一个①实验代码相关子查询SELECT A.* FROM TB_Student_Info A WHERE EXISTS( SELECT * FROM TC_Policate B WHERE A.PolicateIDB.Policateid AND B.Policatename中国共产党党员 )不相关子查询SELECT A.* FROM TB_Student_Info A WHERE A.PolicateID IN( SELECT B.Policateid FROM TC_Policate B WHERE B.Policatename中国共产党党员 )连接查询SELECT A.* FROM TB_Student_Info A,TC_Policate B WHERE A.PolicateIDB.Policateid AND B.Policatename中国共产党党员IN嵌套都可以用EXISTS的子循环替代。连接查询都可以转化为子循环但有的子循环无法转化成连接查询。存疑哈插入、更新、删除操作就比较简单了。视图操作也比较简单。就不说了二、MySQL常用命令我们学的时候就不去区分DDL 定义、DML 操作、DQL 查询、DCL 控制中的哪一种了哈。2.1 基本命令--1.SQL的单行注释是-- /* 多行注释: SQL是忽略大小写的 */ --2.登录 mysql -uroot -p123456 --3.修改密码(mysql数据库的user表的authentication_string属性 UPDATE mysql.user SET authentication_stringpassword(123456) WHERE userroot AND hostlocalhost; --4.更新权限 flush privileges; --5.查看mysql版本 SHOW VERSION(); --6.查看有哪些数据库 show databases; --7.切换到db_school数据库 use db_school; --8. 查看当前数据库下有哪些基本表 show tables; --9.查看student表的信息 describe student; -- 等同于 desc student; --10.输错命令强行终止 ctrlc2.2 操作数据库的命令2.3 操作基本表的命令1. sql的数据类型注意一因为以后要用java程序存取数据我们就要注意sql的数据类型对标什么java数据类型。注意二注意理解给每一列设置的“长度”是什么意思。比如说student表中的id列我们已经设置为int数据类型了也知道int类型占4个字节那还去设置它的长度是在做什么这里的长度其实是“显示长度”。啥子意思1数值tinyint 十分小的数据 1个字节smallint 较小的数据 2个字节mediuint 介于较小和标准之间 3个字节int 标准的整数 4个字节常用---------对标java的intbigint 较大的数据 8个字节---------对标java的longfloat 单精度浮点数 4个字节---------对标java的floatdouble 双精度浮点数 8个字节常用---------对标java的doubledecimal 字符串形式的浮点数用于金融计算无精度损失常用2字符串char 固定长度的字符串多了会截断少了补空格 0~255---------Stringvarchar 可变长度字符串多了会截断少了不补 0~65535---------Stringtinytext 微型文本 2^8-1---------Stringtext 文本串保存大文本2^16-1---------Sringvarchar和text的区别字节面试题【MySQL】varchar和text类型的区别_玛丽莲茼蒿的博客-CSDN博客PS论文这样的大文本一般用nosql数据库保存哦~这里的text数据类型相当于一直权宜之计3时间date YYYY-MM-DD 日期格式---------java.util.Datetime HH:mm:ss 时间格式---------java.util.Datedatetime YYYY-MM-DD HH:mm:ss 最常用的日期-时间格式---------java.util.Datetimestamp 时间戳 1970年1月1日到现在的毫秒数---------java.util.Dateyear 年份---------java.util.Date4null没有值未知注意不要用null进行运算无意义2. 列的属性重点1Unsigned 无符号整数该列数据类型必须是整数才能设为Unsigned声明了该列不能为负数2zerofill 零填充不足位数的用0来填充比如int3我们输入5自动填充为0053自增自动在上一条记录上1默认必须是整数类型不然没法1通常用来设计唯一的主键可理解为index可以自定义自增步长4NOT NULL 非空不填值会报错主键肯定NOT NULL某个属性一旦被选为主键了不用特意表明NOT NULL5default 默认值不填值会有默认值3. 基本表的类型——引擎InnoDB和MyISAM的区别MyISAM是mysql早期5.5之前默认使用的引擎InnoDB是mysql现在默认使用的引擎所以肯定InnoDB更好嘛~ 。比较一下二者不用死记硬背因为随着版本的更新MYISAM也支持事务了innodb也支持全文索引了MYISAMINNODB事务支持不支持不安全所以被淘汰了嘛支持数据行锁定不支持效率低所以被淘汰了嘛支持外键不支持所以被淘汰了嘛支持全文索引索引类型支持这是比INNODB强的一点非聚簇索引需要回表效率低不支持聚簇索引不需要回表表空间的大小较小这是比INNODB强的一点较大浪费空间InnoDB和MyISAM在物理存储上的区别其实就是聚簇索引和非聚簇索引INNODB在数据库文件夹中只存储一个表结构定义*.frm文件真正的数据和缩影存储在上级目录的ibdata1文件中。MYISAM在数据库文件夹中存储3个文件*.frm 表结构的定义文件*MYD 存储数据的文件(data)*.MYI 索引文件index总结一下MYISAM 节约空间速度较快但是不支持事务安全性差而且每次锁定都是锁定一个表效率低。INNODB相反《MySQL 高性能》上面有一句话这样写到:不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈这个结论往往不是绝对的。在很多我们已知场景中InnoDB 的速度都可以让 MyISAM 望尘莫及尤其是用到了聚簇索引或者需要访问的数据都可以放入内存的应用。4. mysql的字符集编码mySQL默认编码是Latin1不用记只知道反正不支持中文就行 所以我们要把mysql的默认编码换成utf-8PS: 注意我们设置的是mysql的默认编码不是sqlyog的。方法一CREATE表的时候在英文括号后面加上DEFAULT [CHARSETutf8]方法二不推荐在mysql的核心配置文件my.ini中加上一行character-set-serverutf8为什么不推荐呢因为把代码发给别人执行的时候如果那个人没有在my.ini里配置字符集那么发给别人的代码可能产生乱码而方式一在表级别去配置把代码发给别人不会乱码。5. 操作基本表的命令1. 创建基本表和sql server一样注意表名和属性名最好用括起来防止我们自己起的名字和关键字冲突。CREATE TABLE [IF NOT EXISTS] 表名( id varchar(12) [PRIMARY KEY][注释], --主键 name int(20) [属性][属性][注释] , , 最后一行不写逗号 )[引擎][字符集]主键和外键的设置和SQL Server一模一样CREATE TABLE tb_teacher( id VARCHAR(12) PRIMARY KEY, -- 主码设置方式一 name VARCHAR(20), -- PRIMARY KEY(ud) -- 主码设置方式二 FOREIGN KEY(name)REFERENCES student(name) -- 外码设置 )ENGINEINNODB DEFAULT CHARSETutf8如果建完表后少了一个外码约束条件解决方法alter table 从表 add constraint 约束名 foreign key(关联字段) references 主表(关联字段但是我们不建议这样设置外键以后也不用这种方式去设置外键。为什么呢? 因为数据必须满足一致性如果要删除主表必须先删除其从表。当数据库很庞杂时捋不清造成困扰。解决办法以上操作是设置数据库级别的外键我们不这样做而是用程序去设置外键。这样表和表之间就没有引用关系了清爽又干净小技巧可以用手动创建基本表然后用SHOW CREATE TABLE 表名去查看sqlyog怎么写的代码SHOW CREATE DATEBASE db_school --查看创建数据库的语句 SHOW CREATE TABLE student --查看创建student表的语句 DESC student --查看表的结构 等同于 DESCRIBE student2. 修改基本表注意CHANGE 用来给属性/字段改名MODIFY用来修改属性的约束这两个关键字的区别面试会问到。/* 1.基本表改名*/ ALTER TABLE student RENAME AS student22; /*2. 增加——给student表中增加一个属性同 SqlServer*/ ALTER TABLE student ADD sex VARCHAR(2); /*3. 增加———在表student的属性name加上取唯一值的约束条件同 SqlServer*/ ALTER TABLE student ADD UNIQUE(name); -- PS: 增加一个外键也使用同样的方法 /*4. 修改——给属性重命名*/ ALTER TABLE student CHANGE name stu_name VARCHAR(12); /*5. 修改——给属性修改约束 区别于mysql ALTER TABLE student ALTER COLUMN name CHAR(20); */ ALTER TABLE student MODIFY name CHAR(20);3. 删除基本表--同 SqlServer DROP TABLE [IF EXISTS] student;2.4 增删改查——查询模板:1. 单表查询简单操作/*查询所有学生的信息*/ SELECT * FROM TB_Student_Info /*只显示某几个字段*/ /*查询年级为2013级的学生姓名email及家庭住址*/ SELECT GradName, Email,Homeland FROM TB_Student_Info WHERE Grade2013 /*给查询结果起别名*/ SELECT GradName AS 学生姓名, Email AS 学生邮箱, Homeland AS 学生家庭地址 FROM TB_Student_Info /*contact()函数给查询结果连上一个字符串在SQL SERVER里没见过*/ SELECT CONCAT(姓名,GradName ) AS 学生姓名 FROM TB_Student_Info /*DISTINCT去重*/ /*消除取值重复的行DISTINCT查询选修了课程的学生学号*/ SELECT DISTINCT GradID FROM TB_Select_CourseWHERE的运算符 等于! 不等于 (或者 大于BETWEEN ... AND...IN()NOT IN()ANDORNOTLIKE 模糊查询通配符‘%’代表0~n个字符通配符_’代表1个字符IS NULLIS NOT NULL/*查询选修课程成绩大于80分的学生学号*/ SELECT GradID FROM TB_Select_Course WHERE Score80 /*查询民族代码为“01”“02”“03”的学生信息*/ SELECT * FROM TB_Student_Info WHERE NationID IN (01,02,03) /*询选修了课程但还没有成绩的学号及课程流水号*/ SELECT GradID,CourseID FROM TB_Select_Course WHERE Score IS NULL /*查询学号为’ 2220121764’,’ 2220121900’’ 2220122080’的学生信息*/ SELECT * FROM TB_Student_Info A WHERE GradID IN(2220121764,2220121900,2220122080) /*查询民族代码不是’01’,’02’的学生信息*/ SELECT A.* FROM TB_Student_Info A WHERE A.NationID NOT IN(01,02) -- 将上述语句转化为带有比较运算的查询语句。 SELECT A.* FROM TB_Student_Info A WHERE A.NationID !01 AND A.NationID !02 -- -------------------------模糊查询 /*查询课程名称含有“计算机”的课程信息*/ SELECT * FROM TB_Course_Info WHERE CourseName LIKE %计算机% /*查询教师姓名中第二个字为“德”的教师姓名*/ SELECT * FROM TB_Tutor_Info WHERE TutorName LIKE _德%2. SELECT的特殊用法SELECT VERSION(); -- 查询【函数】 SELECT (200*7)/3 AS 计算结果; -- 查询【表达式】 SELECT AUTO_INCREMENT_INCREMENT; -- 查询【变量】 -- 显示1分后的成绩 SELECT GradID,grade1 AS 1分后的成绩 FROM TB_Course_Info3. 排序、分页升序不写默认、降序/*按学生课程代码升序、成绩降序排列学生选课信息*/ SELECT * FROM TB_Select_Course ORDER BY CourseID ,Score DESC为什么要分页一下子查出来100万条数据能全放在一页给用户吗分页给客户的体验更好SELECT * FROM tb_test LIMIT 0,6 -- LIMIT 起始值 页面大小前端传入参数的推导例题4. 聚合函数、分组过滤一个例子彻底认识HAVING关键字以及和WHERE的区别:WHERE是分组前的过滤条件HAVING是分组后依然有过滤需求的条件。-- 查询不同课程所有及格分数(60)的平均分最高分最低分并且过滤出平均分大于80的记录 SELECT courseName, AVG(grade) AS 平均分,MAX(grade),MIN(grade) FROM tb_course A, tb_select_course B WHERE A.courseIDB.courseID AND B.grade60 -- WHERE实现分组前过滤 GROUP BY A.courseName HAVING 平均分80 -- HAVING实现分组后再过滤统计各民族或政治面貌在’2017’级各个学院的学生人数情况/*统计各民族在’2017’级各个学院的学生人数情况*/ SELECT A.NationName,B.DeptName,COUNT(GradID) FROM TC_Nation A,TB_Dept_Info B,TB_Student_Info C, TB_Speciality_Info D WHERE A.NationIDC.NationID AND B.DeptIDD.DeptID AND C.SpecialityNoD.SpecialityNo GROUP BY A.NationName,B.DeptName //SELECT字段要么都聚类要么不聚类COUNT之类函数用到的字段除外 HAVING C.Grade2017统计每个学院所开设课程学生的选修情况每门课程选修人数最高分最低分及平均成绩等/*统计每个学院所开设课程学生的选修情况每门课程选修人数最高分最低分及平均成绩等*/ SELECT A.DeptName,B.CourseID,COUNT(GradID) AS Number,MAX(Score) AS Maxscore,Min(Score) AS MinScore,AVG(Score) AS Average FROM TB_Dept_Info A,TB_Select_Course B, TB_Course_Info c WHERE A.DeptIDC.DeptID AND B.CourseIDC.CourseID GROUP BY A.DeptName,B.CourseID5. 多表连接查询连接的数学基础就是代数里的笛卡尔乘积一、JOIN的三种方式BUT虽然理论模型上是只有笛卡尔积这种连接方式但是实际工程实践中各个优化器基本上都有三种连接方式1循环嵌套 JOIN就是笛卡尔乘积时间复杂度O(m*n)。如果俩表都很小笛卡尔积的开销比哈希JOIN的开销要小。特殊情况如果被驱动表两个表中的大表在JOIN字段有索引的话循环嵌套就不是笛卡尔积了而是外层循环全表扫描小表的每一行内层直接走大表的索引去定位被驱动表的数据。2哈希JOIN时间复杂度O(mn哈希计算的时间)。如果两个表都是大表的话笛卡尔积就是灾难性的此时哈希JOIN比循环嵌套JOIN开销要小。选两个表里数据量小的表作为驱动表大表作为被驱动表全表扫描遍历小表计算JOIN字段的哈希值填入哈希篮子里再全表扫描遍历大表计算大表JOIN字段的哈希值看能跟哈希篮子里哪个匹配上。3sort-merge JOIN这个有点不伦不类吃力不讨好所以优化器一般不用这个如果两个表一大一小的话1如果大表在join字段上有索引选循环嵌套2如果大表在join字段上没有索引看小表的行数1如果小表只有一行选循环嵌套。因为循环嵌套虽然是做笛卡尔积但由于小表只有1行所以只需要对大表全表扫描一遍。而HASH JOIN也需要大表扫描一遍但还加上了哈希计算的开销2如果小表1行选HASH JOIN。二、谓词下推再就是上面说的大表和小表指的不是数据库整个表的数据量指的是优化器给过滤之后的。比如SELECT FROM batch A INNER JOIN unit B ON A.noB.no WHERE A.imp_time20260101 AND B.no2026554622;优化器不是按照SQL先JOIN再WHERE的书写顺序“老老实实”先连接再过滤而是先用索引过滤再连接这叫“谓词下推”。注意只有where字段有索引的时候才会谓词下推。拿上面这个SQL来说在JOIN之前先把A表用索引过滤出一个子表A只不过A.imp_time没有索引的时候AA。同理过滤出一个子表BB.no没有索引的时候BB。然后此时的A‘和B就是上面说的“大表小表”再去参与上面JOIN的选择。可能会先按照where后面的A.imp_time20260101把A表过滤出一个临时子表只有几万条数据不是200万了。但不一定会过滤喔优化器也得看你的A.imp_time字段上有没有索引如果A.imp_time字段上没有索引的话那不过滤JOIN之前暂时保留200万不变。就算是200万的数据按照A.imp_time20260101过滤后只剩几千个了那么A.imp_time没有索引的时候也不会采用全表扫描的方式去过滤因为参与JOIN选择的时候可以选HASH JOIN那时候再走全表扫描也不迟。三、优化器的执行逻辑所以优化器的执行逻辑是先进行上面的“二、谓词下推”再去执行上面的“一、JOIN的三种选择”四、优化器还有哪些优化方式比如某个字段虽然有索引但是where查询的时候这个条件能查出来90%的数据此时索引的消耗比全表扫描还要大优化器就会选择全表扫描。比如查询用户表里where name匿名占了90%。内连接Inner Join 等值连接WHERE不能连接NULL看下图。二者在功能上完全等价优化器在执行过程中也没有任何区别。只不过写法不同。等值连接也叫隐式内连接是一种老写法把用来连接的字段和用来筛选的字段都混在where里可读性太差现在不采用了。而人家inner join把用来连接的条件放在ON后面把用来筛选的条件放在where后面清晰多了。最佳实践里一律用inner join外连接可以连接NULL看下图Left Join: 从左表中返回所有的值即使右表没有匹配到或者直接理解为Left Join以左表为核心Right Join: 从右表中返回所有的值即使左表没有匹配到或者直接理解为Right Join以右表为核心Full Out Join全外连接。返回左表所有行 右表所有行匹配不上的补 NULL自身连接不用解释其实就3种其他4种是这3种的特殊情况。PS: 红色部分理解为表用来等值匹配的某个字段不要理解为一个表。比如Left joinA和B相交的部分是红色的意为着Left join可以返回A和B某个字段等值匹配的结果A减B的部分是红色的意为着A和B某个字段没有匹配上也能返回A的所有值把B置为NULL。/*等值查询 相当于 内查询*/ SELECT A.*, B.* FROM 表1 AS A, 表2 AS B WHERE A.id B.id SELECT A.*, B.* FROM 表1 AS A INNER JOIN 表2 AS B ON A.id B.id /*查询所有学生的选课情况包括学号姓名年级课号成绩一节课没选的同学也要查出来*/ SELECT A.GradID,A.GradName,A.Grade,B.CourseID,B.Score FROM TB_Student_Info AS A LEFT JOIN TB_Select_Course AS B ON A.GradIDB.GradID /*只查出来一节课也没选的学生*/ SELECT A.GradID,A.GradName,A.Grade FROM TB_Student_Info AS A LEFT JOIN TB_Select_Course AS B ON A.GradIDB.GradID WHERE B.GradID IS NULL /*查询所有教师的教师代码姓名所教授课程的代码一节课没教的老师也要查出来*/ /*因为TB_Select_Course表中有重复的课程所以这里用了DISTINCT去重*/ SELECT DISTINCT A.TutorID,A.TutorName,B.CourseID FROM TB_Tutor_Info AS A LEFT JOIN TB_Select_Course AS B ON A.TutorIDB.TutorID /*三表连接查询*/ /*查询“信息学院”2013级所有学生的学号姓名性别学生所在专业名称*/ SELECT GradID,GradName,Sex,SpecialityName FROM TB_Dept_Info AS A,TB_Student_Info AS B,TB_Speciality_Info AS C WHERE A.DeptName信息科学技术学院 AND A.DeptID C.DeptID AND B.SpecialityNoC.SpecialityNoAND Grade2013多表连接除了上面WHERE A.id B.id, B.id C.id, C.id D.id这种写法还可以像下面这样写自身连接/*查询与“张三”在同一个专业的学生的姓名电话email家庭住址*/ SELECT B.GradName,B.tel,B.email,B.homeland FROM TB_Student_Info AS A,TB_Student_Info AS B WHERE A.GradName张三 AND A.SpecialityNoB.SpecialityNo /*数据库原理的先修课程*/ SELECT B.* FROM TB_Course_Info AS A,TB_Course_Info AS B WHERE A.CourseName数据库原理 AND A.PCourseIDB.CourseID你以为你需要手动优化其实根本不需要因为优化器会替你做一切就算你写的写法一执行起来也是写法二/*查询选了‘数据库原理’这门课的学生姓名*/ //写法一先全连接再选择 SELECT A.sanme FROM t_student A INNER JOIN t_course B ON A.sidB.sid WHERE B.course数据库原理 //写法二先选择再全连接效率更高 SELECT A.sanme FROM t_student A INNER JOIN( SELECT B.sid FROM t_course B WHERE B.course数据库原理 ) ON A.sidB.sid /*查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩*/ //写法一先全连接再选择 SELECT A.sid, A.sname, AVG(B.score) FROM t_student A INNER JOIN t_score B ON A.sidB.sid GROUP BY A.sid HAVING AVG(B.score)60 //写法二先选择再全连接效率更高 SELECT A.sid, A.sname, avgScore FROM t_student A INNER JOIN ( SELECT sid,AVG(score) AS avgScore FROM t_score GROUP BY sid HAVING AVG(score)60 )AS B ON A.sid B.sid6. 嵌套子查询嵌套子查询的效率比连接查询效率快因为它没有连接操作笛卡尔积的计算WHERE后面跟的常见的有以下三种带有IN谓词的子查询带有比较运算符的子查询 ! 带有EXISTS的子查询连接查询都能转化为嵌套子查询 嵌套子查询貌似不一定能转化成连接查询。/*1.不相关子查询由内向外*/ SELECT A.* FROM TB_Student_Info A WHERE NationID IN( SELECT NationID FROM TC_Nation WHERE NationName IN (回族,蒙古族) ) /*2.相关子查询*/ SELECT A.* FROM TB_Student_Info A WHERE EXISTS( SELECT * FROM TC_Nation B WHERE B.NationName IN(回族,蒙古族) AND A.NationIDB.NationID) /*3.带有比较运算符的子查询*/ /*查询除了“张德珍”老师以外其他各个老师所讲授的课程信息*/ SELECT DISTINCT A.* FROM TB_Course_Info A,TB_Select_Course B WHERE A.CourseIDB.CourseID AND B.TutorID!( SELECT C.TutorID FROM TB_Tutor_Info C WHERE C.TutorName张德珍)多层嵌套/*查询为“软件工程”专业讲授课程的教师基本信息*/ SELECT A.* FROM TB_Tutor_Info A WHERE EXISTS( SELECT * FROM TB_Select_Course B WHERE A.TutorIDB.TutorID AND EXISTS (SELECT * FROM TB_Student_Info C WHERE C.GradIDB.GradID AND EXISTS (SELECT * FROM TB_Speciality_Info D WHERE C.SpecialityNoD.SpecialityNO AND D.SpecialityName软件工程 )))7. 交、并、差UNION 等价于 ORINTERSECT 等价于 ANDEXCEPT 等价于 AND !/*查询学生“德”和“培”都选的课程信息*/ SELECT C.* FROM TB_Student_Info A,TB_Select_Course B,TB_Course_Info C WHERE A.GradIDB.GradID AND B.CourseIDC.CourseID AND A.GradName德 INTERSECT SELECT C.* FROM TB_Student_Info A,TB_Select_Course B,TB_Course_Info C WHERE A.GradIDB.GradID AND B.CourseIDC.CourseID AND A.GradName培 /*查询学生“德”和“培”选的全部课程信息*/ SELECT C.* FROM TB_Student_Info A,TB_Select_Course B,TB_Course_Info C WHERE A.GradIDB.GradID AND B.CourseIDC.CourseID AND A.GradName德 UNION SELECT C.* FROM TB_Student_Info A,TB_Select_Course B,TB_Course_Info C WHERE A.GradIDB.GradID AND B.CourseIDC.CourseID AND A.GradName培 /*查询选修过“珍”老师的课程却没有选修老师“东”课程的学生信息*/ SELECT A.* FROM TB_Student_Info A,TB_Select_Course B,TB_Tutor_Info C WHERE A.GradIDB.GradID AND B.TutorIDC.TutorID AND C.TutorName珍 EXCEPT SELECT A.* FROM TB_Student_Info A,TB_Select_Course B,TB_Tutor_Info C WHERE A.GradIDB.GradID AND B.TutorIDC.TutorID AND C.TutorName东8. mysql常用函数-- 数值函数 SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回一个 0~1 之间的随机数 SELECT SIGN(10) -- 判断符号 整数返回1 负数返回-1 0返回0 -- 字时间和日期函数记住 SELECT CURRENT_DATE() -- 获取当前日期 2023-01-19 SELECT CURDATE() -- 获取当前日期 2023-01-19 SELECT NOW() -- 获取当前的时间 2023-01-19 23:36:37 SELECT LOCALTIME() -- 获取本地时间 2023-01-19 23:37:10 SELECT SYSDATE() -- 获取系统时间 2023-01-19 23:37:23 SELECT YEAR(NOW()) -- 2023 SELECT MONTH(NOW()) -- 1 SELECT DAY(NOW()) -- 19 SELECT HOUR(NOW()) -- 23 SELECT MINUTE(NOW()) -- 39 SELECT SECOND(NOW()) -- 57 -- 系统 SELECT SYSTEM_USER() -- 相当于SELECT USER() SELECT VERSION()函数的形参可以是一个值也可以是一列值。拓展MD5函数实现MD5加密更安全的操作是插入的时候就加密-- 插入的时候加密 INSERT INTO tb_testmd5 VALUES(4,xiaoming,MD5(123456)) -- 如何校验将用户传递进来的密码进行md5加密对比加密后的值 SELECT * FROM tb_testmd5 WHERE namexiaoming AND pwdMD5(123456)2.5 增删改查——插入模板同SQL ServerINSERT INTO 表名([属性1],[属性2],[属性3],[属性4]) -- 可以不写但是VALUES顺序要和属性列以一对姓 VALUES(), (), ()/*插入一条数据*/ INSERT INTO TB_Course_Info VALUES(15,软件工程,32,2,考试,01,05) /*插入两条数据*/ INSERT INTO TB_Course_Info VALUES(15,软件工程,32,2,考试,01,05), (22,大数据分析,32,2,考查,02,06) /*指明属性列*/ INSERT INTO TB_Tutor_Info(TutorID,TutorName,Tel) VALUES(20,王暂时,55556666)不填的数据默认插入default 默认值一般是null2.6 增删改查——修改模板同SQL ServerUPDATE 表名 SET 列名值列名值 [WHERE] -- 不写条件会改动整个表的所有记录/*不带where改动整个表*/ /*修改所有学生的课程成绩Score使其提高10%*/ UPDATE TB_Select_Course SET ScoreScore*1.1 /*修改“滴滴”同学的电话号码Tel及email*/ UPDATE TB_Student_Info SET TEL18103050057 , EMAIL1111 WHERE GRADNAME滴滴 /**/2.7 增删改查——删除模板同sql serverDELETE FROM 表名 [WHERE] -- 不写清空整个表用法/*删除所有选课记录表TB_Select_Course*/ DELETE FROM TB_Select_Course /*删除学号为’1120170001’所选的课程号为’2003’的选课记录*/ DELETE FROM TB_Select_Course WHERE GradID1120170001 AND CourseID2003清空整个表的两种方式及区别DELETE FROM 表名 不加 WHERE不建议TRUNCATE 表名建议重新设置 自增列 计数器会归零不会影响事务自增量测试例子/*创建测试表*/ CREATE TABLE tb_test( id INT(4) PRIMARY KEY AUTO_INCREMENT, -- 主键自增 test_name VARCHAR(20) )ENGINEINNODB DEFAULT CHARSETutf8 /*插入3条数据*/ INSERT INTO tb_test(test_name) VALUES(today),(is),(1/19); SELECT * FROM tb_test; /*查询结果 id test_name 1 today 2 is 3 1/19 */ /*DELETE方式删除所有数据*/ DELETE FROM tb_test; /*再插入3条数据*/ INSERT INTO tb_test(test_name) VALUES(today),(is),(1/19); SELECT * FROM tb_test; /*查询结果自增量从4开始而不是清零后从1重新开始 id test_name 4 today 5 is 6 1/19 */ TRUNCATE tb_test; /*再插入3条数据*/ INSERT INTO tb_test(test_name) VALUES(today),(is),(1/19); SELECT * FROM tb_test; /*查询结果自增量从1重新开始 id test_name 1 today 2 is 3 1/19 */三、