MySQL语言

MySQL语言

1.外键管理

作用，将表与表建立关系 一个主键只能被一个表的外键引用 创建外键 方法1：创建表的时候创建外键 方法2：创建表之后创建外键

#创建表的时候创建外键 CREATE TABLE IF NOT EXISTS student1( stuID INT(10) PRIMARY KEY AUTO_INCREMENT, stuname VARCHAR(20) NOT NULL DEFAULT'匿名', sex TINYINT(1) NOT NULL DEFAULT'1', gradeid INT(4), photo VARCHAR(50) NOT NULL, address VARCHAR(100), brondate DATETIME, emile VARCHAR(50), IDcard VARCHAR(18) NOT NULL, #创建表的时候创建外键 CONSTRAINT FK_gradeid FOREIGN KEY(gradeid) REFERENCES grade(gradeID) ); #创建表之后创建外键 CREATE TABLE IF NOT EXISTS student2( stuID INT(10) PRIMARY KEY AUTO_INCREMENT, stuname VARCHAR(20) NOT NULL DEFAULT'匿名', sex TINYINT(1) NOT NULL DEFAULT'1', gradeid INT(4), photo VARCHAR(50) NOT NULL DEFAULT'110', address VARCHAR(100), brondate DATETIME, emile VARCHAR(50), IDcard VARCHAR(18) NOT NULL DEFAULT'1211' ); ALTER TABLE student2 ADD CONSTRAINT FK_gradeid FOREIGN KEY(gradeid) REFERENCES grade(gradeID);

删除表 删除具有主外键关系的表时，要先删除子表，后删主表 删除外键 步骤1.删除外键 步骤2.删除索引

#删除外键 ALTER TABLE student2 DROP FOREIGN KEY FK_gradeid; ALTER TABLE student2 DROP INDEX FK_gradeid;

2.数据库数据管理（通过DML语言）

添加数据

INSERT INTO grade(gradeName)VALUES('大一'),('大二'),('大三'),('大四');

更新数据

UPDATE student2 SET brondate=NOW(),photo='12345678' WHERE stuID BETWEEN 0 AND 5; 使用函数 #使用函数 UPDATE student2 SET stuname =CONCAT("姓名",stuname);

删除数据

#删除数据 DELETE FROM student2 WHERE stuID BETWEEN 1 AND 4;

删除表的全部数据 DELETE FROM grade 在添加值时，自增值从原有基础上进行 TRUNCATE TABLE grade; 在添加值时，自增值从初始值进行（完全清空表数据，不能被恢复） 重启数据库服务之后 DELETE FROM grade INNODB的表，自增列从初始值开始 MYISAM的表，自增列从上一次自增数据基础上开始

数据查询语言（DQL语言）

查询取别名

#取别名 SELECT StudentNo AS 学号,studentname 姓名 FROM student; #表取别名 SELECT StudentNo AS 学号,studentname 姓名 FROM student AS s;

#使用AS为查询结果取一个新名字

SELECT CONCAT(‘姓名’,studentname) AS 新name FROM student ;

查询去除重复项

#查询哪些学生参加考试的（学号）-去除重复项(distinct) SELECT DISTINCT studentno FROM result;

查询中使用表达式 SELECT @@auto_increment_increment AS 步长 SELECT VERSION() AS 版本; SELECT 100*9-88 AS 计算;

模糊查询 between and\like\in\null

like

#like结合使用通配符：%（0到任意个字符）_(一个字符) SELECT studentno,Studentname FROM student WHERE studentname LIKE '李%'; SELECT studentno,Studentname FROM student WHERE studentname LIKE '李_'; SELECT studentno,Studentname FROM student WHERE studentname LIKE '李__'; SELECT studentno,Studentname FROM student WHERE studentname LIKE '%文%'; #自定义转移符 SELECT studentno,studentname FROM student WHERE studentname LIKE '%:%%' ESCAPE ':';

between and

SELECT studentno,studentresult FROM result WHERE StudentResult BETWEEN 95 AND 100;

in

SELECT studentno,studentname FROM student WHERE studentno IN(1000,1001)

null

#null is null #空的出生日期 SELECT studentno,studentname FROM student WHERE BornDate IS NULL SELECT studentno,studentname FROM student WHERE BornDate IS NOT NULL #查询空字符串和null SELECT studentno,studentname FROM student WHERE address =' ';

连接查询

内连接 inner join 查询两个表中的结果集中的交集

#查询参加考试学生信息（学号，学员姓名，科目编号，分数） /* 思路：（1）分析需求：结果来源于两个表 student result 连接查询 （2）确定使用哪一种连接？ */ #内连接 SELECT s.`StudentNo`,studentname,subjectno,studentresult FROM student AS s INNER JOIN result AS r ON r.`StudentNo`=s.`StudentNo **

外连接 out join 左外连接lift join 以左表为基准，右边一一匹配，匹配不上的，返回左表记录，右边以null填充（left左边的为左表）

**

#左连接 SELECT s.`StudentNo`,studentname,subjectno,studentresult FROM student AS s LEFT JOIN result AS r ON r.`StudentNo`=s.`StudentNo`

右外连接 right join 以右表为基准，左边一一匹配，匹配不上的，返回右表记录，左边以null填充（right右边的表为右表）

#右连接 SELECT s.`StudentNo`,studentname,subjectno,studentresult FROM student AS s RIGHT JOIN result AS r ON r.`StudentNo`=s.`StudentNo`

自连接：必须起别名 一个表中查询

#自连接必须取别名 CREATE TABLE IF NOT EXISTS category( categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, pid INT(10) NOT NULL, categoryname VARCHAR(10) NOT NULL, PRIMARY KEY(categoryid) ) SELECT * FROM category INSERT INTO category VALUES (2,1,'美术设计'), (3,1,'软件开发'), (4,3,'数据库基础'), (5,2,'PS设计'), (6,2,'色彩搭配'), (7,3,'JAVA基础'); SELECT a.categoryname AS '父栏目',b.categoryname AS '子栏目' FROM category AS a,category AS b WHERE a.`categoryid`=b.`pid`

等值连接

#等值连接等同内连接 SELECT s.`StudentNo`,studentname,subjectno,studentresult FROM student AS s,result AS r 非等值连接 返回m*n行

【三表查询】

#三表查询 SELECT s.studentno,studentname,subjectname,studentresult FROM student AS s INNER JOIN result AS r ON s.studentno=r.studentno INNER JOIN SUBJECT AS sub ON r.subjectno=sub.`SubjectNo`

ORDER BY 排序 默认ASC 升序 DESC #降序

分页（只使用mySQL数据库

LIMIT （当前页数-1）页容量，页容量

#查询（C语言-1）前5名学生信息：姓名，学号，成绩 SELECT student.studentno,studentname,StudentResult FROM student INNER JOIN result ON student.`StudentNo`=result.`StudentNo` INNER JOIN SUBJECT ON result.`SubjectNo`=subject.`SubjectNo` WHERE subjectname='C语言-1' ORDER BY StudentResult DESC LIMIT 0,5;

子查询

执行顺序<从里到外>，要使用子查询，要查询的内容必须在同一个表里

#查询郭靖所在的年级名称 SELECT studentname,gradename FROM student INNER JOIN grade ON student.`GradeId`=grade.`GradeID` WHERE studentname='郭靖' SELECT gradename FROM grade WHERE gradeid=(SELECT gradeid FROM student WHERE studentname='郭靖')

3.MySQL函数

数字函数

SELECT ABS(-4); #绝对值 SELECT CEILING(9.8) #大等于我的最小整数 SELECT FLOOR (9.8) #小等于我的最大整数 SELECT RAND()# 返回0~1之间随机数 SELECT RAND(10)#以某个数作为种子，返回重复随机数 SELECT SIGN(-1200)#符号函数，正数1，负数-1,0返回0

字符串函数

SELECT CHAR_LENGTH('我爱旅行') #返回字符串长度 SELECT CONCAT('I','LOVE','U')#连接 SELECT INSERT('我爱旅行',1,2,'痴迷')#替代，从某个位置开始，替换某个长度 SELECT INSERT('我爱旅行',10,2,'痴迷')#起始位置超过长度，返回原字符串 SELECT LOWER('I LOVE YOU')#变小写 SELECT UPPER('i love you')#变大写 SELECT LEFT('I LOVE YOU',3)#从左边开始截取3位 SELECT RIGHT('I LOVE YOU',3)#从右边开始截取3位 SELECT REPLACE('我爱旅行','我','你们')#将指定字符转换 SELECT SUBSTR('我爱旅行',3,2)#截取，从某个位置开始截取，截取多长 SELECT REVERSE('我爱旅行')#反转

日期时间函数

SELECT NOW();#获得当前时间 SELECT LOCALTIME() SELECT SYSDATE() SELECT CURRENT_DATE()#获取当前日期 SELECT CURDATE() SELECT YEAR(NOW())#获取当前日期的部分 SELECT MONTH(NOW()) SELECT DAY(NOW()) SELECT HOUR(NOW()) SELECT MINUTE(NOW()) SELECT SECOND(NOW())

**

系统信息函数

SELECT VERSION() SELECT USER()

聚合函数

SELECT COUNT(borndate)FROM student SELECT SUM(StudentResult) 总分 FROM result SELECT AVG(StudentResult) 平均分 FROM result SELECT MAX(StudentResult)最高分 FROM result SELECT MIN(StudentResult)最低分 FROM result

综合练习

#查询不同科目的最高分，平均分，最高分，最低分 SELECT subjectname,SUM(StudentResult) 总分,AVG(StudentResult) 平均分,MAX(StudentResult)最高分,(StudentResult)最低分 FROM result INNER JOIN SUBJECT ON result.`SubjectNo`=subject.`SubjectNo` GROUP BY subjectname #查询不同科目的最高分，平均分，最高分，最低分 SELECT subjectname,SUM(StudentResult) 总分,AVG(StudentResult) 平均分,MAX(StudentResult)最高分,(StudentResult)最低分 FROM result INNER JOIN SUBJECT ON result.`SubjectNo`=subject.`SubjectNo` GROUP BY subjectname HAVING 平均分>80 ORDER BY 平均分 DESC LIMIT 0,5;

五月 23, 2019. Created by XMind