1、dql:单表查询
--创建表student: 名字 性别 年龄 是否党员 照片 分数 饭卡钱数 生日
CREATE TABLE student(
sname VARCHAR(10),
sex CHAR(1),
sage INT,
sdy BOOLEAN,
sphoto BLOB,
score FLOAT(4,1),
smoney DECIMAL(9,1),
sbirthday DATE
);
-- 添加10条记录
INSERT INTO student(
sname,
sex,
sage,
sdy,
sphoto,
score,
sbirthday) VALUES(
SUBSTR(UUID(),1,4),
IF(RAND()>0.5,"男","女"),
TRUNCATE(RAND()*8+18,0),
RAND()>0.5,
LOAD_FILE(CONCAT("C:/Users/Administrator/Desktop/imgs/",(TRUNCATE(RAND()*7+1,0)),".jpg")),
TRUNCATE(RAND()*100,1),
CONCAT((TRUNCATE(RAND()*8+1996,0)),"-",(TRUNCATE(RAND()*12+1,0)),"-",(TRUNCATE(RAND()*31+1,0)))
);
-- 再添加10条记录
INSERT INTO student(
sname,
sex,
sage,
sdy) VALUES(
SUBSTR(UUID(),1,4),
IF(RAND()>0.5,"男","女"),
TRUNCATE(RAND()*8+18,0),
RAND()>0.5
);
---单表查询：dql
---1:查询所有列
SELECT * FROM student;
---2:查询指定列
SELECT sname,sage,score FROM student;
---3:条件查询
---WHERE条件可以使用的运算符1：比较运算符：
---= > < != >= <=
SELECT * FROM student WHERE sex="男";
SELECT * FROM student WHERE sage>=11;
SELECT * FROM student WHERE sage <> 11;
---WHERE条件可以使用的运算符2：OR/AND
SELECT * FROM student WHERE sex="男" OR sage >11;
SELECT * FROM student WHERE sex="男" AND sage<11;
---WHERE条件可以使用范围关键字3：BETWEEN a AND b
SELECT * FROM student WHERE sage BETWEEN 18 AND 21;
SELECT * FROM student WHERE sage>=18 AND sage <=21;
---WHERE条件可以使用范围关键字4：IN
SELECT * FROM student WHERE sage IN(18,25,20);
SELECT * FROM student WHERE sage=18 OR sage=20 OR sage=25;
---4: 空和非空判断：IS NULL,IS NOT NULL
SELECT * FROM student WHERE sphoto IS NULL;
SELECT * FROM student WHERE sphoto IS NOT NULL;
---5: 聚合函数：
---5.1:COUNT(列)
---5.2:SUM(列)
---5.3:MAX(列)
---5.4:MIN(列)
---5.5:AVG(列):获取指定列非空记录的平均值
SELECT COUNT(*) FROM student;/*获取总记录数*/
SELECT COUNT(sphoto) FROM student;/*获取sphoto值不为null的记录数*/
SELECT MAX(score),AVG(score),MIN(score),SUM(score) FROM student;
SELECT AVG(score),SUM(score),COUNT(*),COUNT(score) FROM student;
SELECT AVG(score),SUM(score)/COUNT(*),SUM(score)/COUNT(score) FROM student;
---6:NULL转换 IFNULL(列,值)
SELECT CONCAT("我叫",sname,",考了",score,"分！") FROM student;
---注意所有数据与NULL进行运算或者字符串拼接 结果都是NULL
SELECT score,IFNULL(score,0) FROM student;
SELECT CONCAT("我叫",IFNULL(sname,"无名氏"),",考了",IFNULL(score,0),"分！") FROM student;
---7:起别名：AS
SELECT CONCAT("我叫",IFNULL(sname,"无名氏"),",考了",IFNULL(score,0),"分！") AS "自我介绍" FROM student;
SELECT CONCAT("我叫",IFNULL(sname,"无名氏"),",考了",IFNULL(score,0),"分！") "自我介绍" FROM student;/*as可以省略*/
SELECT sname 名字,sage 年龄,score 分数,sphoto 照片 FROM student;
SELECT sname AS 名字,sage AS 年龄,score AS 分数,sphoto AS 照片 FROM student;
---8:模糊查询：LIKE ：： %表示>=0个任意字符 _表示1个任意字符
SELECT * FROM student WHERE sname LIKE "8%";/*名字以8开头*/
SELECT * FROM student WHERE sname LIKE "%8%";/*名字中含有8*/
SELECT * FROM student WHERE sname LIKE "_8%";/*第二个字符是8*/
---9:去除重复行：DISTINCT
SELECT sage FROM student;
SELECT DISTINCT sage FROM student;
SELECT DISTINCT sage,sex FROM student;
SELECT sage,sex FROM student;
---10:字符串拼接
SELECT CONCAT(1,2,sname) FROM student;
---11:分页查询：LIMIT a,b :a表示起始索引从0开始 b表示获取的总记录数
SELECT * FROM student;
SELECT * FROM student LIMIT 0,3;
SELECT * FROM student LIMIT 1,6;
---12: 根据结果集创建表：
DROP TABLE stu;
CREATE TABLE stu
AS
SELECT sname 名字,sage 年龄,score
FROM student
WHERE score <50;
SELECT * FROM stu;
---13: 排序：ORDER BY 列明 ASC/DESC
SELECT * FROM student ORDER BY score;/*默认是asc*/
SELECT * FROM student ORDER BY score ASC;/*升序*/
SELECT * FROM student ORDER BY score DESC;/*倒序*/
SELECT * FROM student ORDER BY sage DESC,score ASC;/*先按sage倒序 sage相同再按score升序*/
---14: 分组：GROUP BY 列名
---分组时 只有分组列的信息和聚合函数是有意义的
SELECT sage,MAX(score),COUNT(*),MIN(score),AVG(score),SUM(score)
FROM student
GROUP BY sage;
1.1、单表查询的语句格式
SELECT 列
FROM 表
WHERE 查询条件
GROUP BY 分组列
HAVING 组条件
ORDER BY 排序列1 DESC/ASC,排序列2 DESC/ASC,
LIMIT 起始索引 获取的记录数
分数及格的所有学生 按年龄分组 只要组平均分大于70的组的信息
按最高分 倒着排序 只要前三组
SELECT sage 年龄,
MAX(score) 最高分,
MIN(score) 最低分,
SUM(score) 总分,
COUNT(*) 人数,
AVG(IFNULL(score,0)) 平均分
FROM student
WHERE score >=60
GROUP BY sage
HAVING AVG(IFNULL(score,0))>66
ORDER BY MAX(score) DESC
LIMIT 0,3;
1.2、单表查询练习
1 创建一个学生表：学号 名字 年龄 性别 分数 生日
2 获取在2000年之前出生的所有学生的信息
SELECT *,EXTRACT(YEAR FROM sbirthday) FROM student
WHERE EXTRACT(YEAR FROM sbirthday)>=2000;
SELECT *,YEAR(sbirthday) FROM student
WHERE YEAR(sbirthday)>=2000;
3 获取班级中所有的学生的姓氏
SELECT *,SUBSTR(sname,1,1) 姓 FROM student
4 获取女生和男生的最高分，最低分，总人数
SELECT sex,
MAX(score) 最高分,
MIN(score) 最低分,
COUNT(*) 总人数
FROM student
GROUP BY sex;
5 获取每个月过生日的总人数 按月份倒序
SELECT IFNULL(MONTH(sbirthday),"不详") 生日月份,COUNT(*) 总人数
FROM student
GROUP BY MONTH(sbirthday)
ORDER BY MONTH(sbirthday) ;
6 获取所有及格的学生中不同年龄的情况：年龄 人数 平均分
SELECT sage 年龄,COUNT(*) 人数,AVG(IFNULL(score,0)) 真实平均分,AVG(score) 参加考试学生的平均分
FROM student
WHERE score>=60
GROUP BY sage
7 获取所有分数及格的20岁以上的男生并且名字中含有"a"的学生的信息
SELECT * FROM student WHERE score>=60 AND sage>=20 AND sex="男" AND sname LIKE "%8%";
8 获取下个月过生日的所有学生的信息
SELECT *,NOW() FROM student WHERE MONTH(sbirthday)=MONTH(NOW())+1;
9 让所有的同学进行自我介绍：我叫xxx,今年xx岁，考了xx分，请叫我美女/帅哥
SELECT CONCAT("我叫",IFNULL(sname,"无名氏"),",今年",IFNULL(sage,0),"岁，考了",IFNULL(score,0),"分，请叫我",IF(sex="男","帅哥",IF(sex="女","美女","妖怪")))
FROM student;
2、约束
constraints：约束，在定义表时 给表设置的检查规则 用于保证数据库的完整性
数据库完整性：
1 数据完整性： 某个数据准确无误
2 域完整性：同一列的值 和谐 统一
3 实体完整性：同一行的数据 必须主键有值
4 参照完整性：表与表的关联 保证主表中的数据与从表（被参照表）中数据的一致性
2.1、非空约束：not null
-- 非空约束：not null
CREATE TABLE stu(
sname VARCHAR(11) NOT NULL, /*此列的值不能为空*/
sage INT
);
INSERT INTO stu VALUES("韩梅梅",11);
INSERT INTO stu VALUES(NULL,11);/*error:Column 'sname' cannot be null*/
INSERT INTO stu VALUES("韩梅梅",NULL);
2.2、默认值：default
-- 默认值约束：default 值
CREATE TABLE stu(
sname VARCHAR(11) DEFAULT "无名氏",
sage INT
);
INSERT INTO stu VALUES("韩梅梅",11);
INSERT INTO stu VALUES(NULL,11); /*指定sname的值 即使指定为null 就不使用默认值*/
INSERT INTO stu(sage) VALUES(11);/*不指定sanme的值 使用默认值*/
SELECT * FROM stu;
2.3、检查约束：check
-- 检查约束：check
-- 注意5.1的mysql不支持check
CREATE TABLE stu(
sname VARCHAR(11),
sage INT,
CHECK(sage>0 AND sage <150) /*mysql不支持check约束*/
);
INSERT INTO stu VALUES("韩梅梅",-11);
2.4、唯一约束：unique
-- 唯一约束：unique
CREATE TABLE stu(
sname VARCHAR(11) UNIQUE,
sage INT
);
INSERT INTO stu VALUES("韩梅梅",-11);
INSERT INTO stu VALUES("韩梅梅",11);/*error:Duplicate entry '韩梅梅' for key 'sname'*/
2.5、主键约束：primary key
主键列：当本列/多列组合 可以唯一标识当前记录
拿出一个主键值 对应的就是唯一的一条记录
-- 主键约束：primary key
CREATE TABLE stu(
sid INT PRIMARY KEY, /*在定义表时 定义主键约束方式1：*/
sname VARCHAR(11),
sage INT
);
CREATE TABLE stu(
sid INT,
sname VARCHAR(11),
sage INT,
PRIMARY KEY(sid)/*在定义表时 定义主键约束方式2：*/
);
INSERT INTO stu VALUES(1,"韩梅梅",11);
-- 主键约束特点1：值不能重复: 唯一
INSERT INTO stu VALUES(1,"韩梅梅2",12);/*error:Duplicate entry '1' for key 'PRIMARY'*/
-- 主键约束特点2：值不能为空：非空
INSERT INTO stu VALUES(NULL,"韩梅梅2",12);/*error:Column 'sid' cannot be null*/
-- 主键约束特点3：可以被从表的外键列引用
DESC stu;/*主键列的key 显示为PRI*/
ALTER TABLE stu DROP PRIMARY KEY;/*删除主键约束*/
ALTER TABLE stu ADD PRIMARY KEY(sid);/*创建表后 添加主键约束方式1：add*/
ALTER TABLE stu CHANGE sid sid INT PRIMARY KEY;/*创建表后 添加主键约束方式2：change*/
ALTER TABLE stu MODIFY sid INT PRIMARY KEY;/*创建表后 添加主键约束方式3：modify*/
-- 多列作为主键
CREATE TABLE score(
sid INT,
kecheng VARCHAR(11),
fenshu FLOAT(4,1),
PRIMARY KEY(sid,kecheng)/*把学号和课程作为联合主键*/
);
2.6、自增约束：auto_increment
-- 自增约束：auto_increment
-- 自增约束 只适用于int类型的主键列
DROP TABLE stu;
CREATE TABLE stu(
sid VARCHAR(11) AUTO_INCREMENT, /*error:Incorrect column specifier for column 'sid'*/
sname VARCHAR(11),
sage INT
);
CREATE TABLE stu(
sid INT AUTO_INCREMENT, /*error:Incorrect table definition; there can be only one auto column and it must be defined as a key*/
sname VARCHAR(11),
sage INT
);
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT, /*error:Incorrect table definition; there can be only one auto column and it must be defined as a key*/
sname VARCHAR(11),
sage INT
);
INSERT INTO stu VALUES(NULL,"韩梅梅6",11);/*如果没有赋值：mysql内部有个变量记录至今id的最大值，赋值为最大值+1*/
INSERT INTO stu VALUES(5,"韩梅梅5",11);/*如果指定值 使用指定值*/
2.7、外键约束：foreign key
在A表中要引用B表中的记录 可以在A表中定义一列作为外键列 此列来引用B表的主键列
A表:从表
B表:主表
外键：实现表与表之间的关联关系
-- 外键约束：foreign key
-- 创建主表
DROP TABLE teacher;
CREATE TABLE teacher(
tid INT PRIMARY KEY,
tname VARCHAR(11),
tsex CHAR(1)
);
-- 创建从表
DROP TABLE student;
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(11),
score FLOAT(4,1),
stid INT,
CONSTRAINT fk_s_t FOREIGN KEY(stid) REFERENCES teacher(tid) /*创建表时定义外键约束方式1：*/
);
DESC student;
INSERT INTO student VALUES(1001,"韩寒",11,NULL); /*外键约束特点1：可以为null*/
INSERT INTO teacher VALUES(1,"张老师","女");
INSERT INTO teacher VALUES(2,"高老师","女");
INSERT INTO student VALUES(1004,"韩非子",11,1);/*外键约束特点2：值可以重复*/
INSERT INTO student VALUES(1003,"韩信",11,1);
INSERT INTO student VALUES(1005,"韩雪",22,4); /*外键约束特点3：外键列的值如果不为null 此值必须在主表的主键列存在*/
/*error:Cannot add or update a child row: a foreign key constraint fails (`db_1`.`student`, CONSTRAINT `fk_s_t` FOREIGN KEY (`stid`) REFERENCES `teacher` (`tid`))*/
SELECT * FROM student;
SELECT * FROM teacher;
DELETE FROM student;
DELETE FROM teacher WHERE tid=2;
DELETE FROM teacher WHERE tid=1;/*外键约束特点4: 删除主表记录时 要删除的记录不能被从表引用*/
/*error:Cannot delete or update a parent row: a foreign key constraint fails (`db_1`.`student`, CONSTRAINT `fk_s_t_2` FOREIGN KEY (`stid`) REFERENCES `teacher` (`tid`))*/
/*一个从表中可以定义多个外键约束 来引用多个主表*/
DESC teacher;
DESC student;
DROP TABLE student;
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(11),
score FLOAT(4,1),
stid INT
);
DESC student;
ALTER TABLE student ADD CONSTRAINT fk_s_t_2 FOREIGN KEY(stid) REFERENCES teacher(tid); /*创建表后添加外键约束*/
ALTER TABLE student DROP FOREIGN KEY fk_s_t_2;/*删除外键约束：fk_s_t_2是外键约束名*/
3、表与表之间的关系
3.1、一对多
1个老师对应n个学生 1个学生只有1个老师
把n方设置为从表 定义外键列 来引用1方的主键列
DROP TABLE student;
DROP TABLE teacher;
-- 1对n的关系
-- 把n方定义为从表 1方定义为主表 在从表中定义外键列 来引用主表的主键列
CREATE TABLE teacher(
tid INT PRIMARY KEY,
tname VARCHAR(11)
);
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(11),
sex CHAR(1),
stid INT,
CONSTRAINT fk_11 FOREIGN KEY(stid) REFERENCES teacher(tid)
);
INSERT INTO teacher VALUES(1,"张老师");
INSERT INTO teacher VALUES(2,"张老师");
INSERT INTO student VALUES(1001,"韩信1","男",1);
INSERT INTO student VALUES(1002,"韩信2","男",1);
INSERT INTO student VALUES(1003,"韩信3","男",2);
INSERT INTO student VALUES(1004,"韩信4","男",2);
3.2、一对一
1个妻子对应1个丈夫 1个丈夫对应1个妻子
-- 1个妻子对应1个丈夫 1个丈夫对应1个妻子
-- 解决方案：根据先后顺序或者重要程度 分区出来主表和从表：把从表的主键列定义为外键 来引用主表的主键列
DROP TABLE husband;
DROP TABLE wife;
CREATE TABLE husband(
hid INT PRIMARY KEY,
hname VARCHAR(11),
hmoney DECIMAL(9,1)
);
INSERT INTO husband VALUES(1,"老张",1000);
INSERT INTO husband VALUES(2,"老李",2000);
INSERT INTO husband VALUES(3,"老王",3000);
INSERT INTO husband VALUES(4,"老田",3000);
CREATE TABLE wife(
wid INT PRIMARY KEY,
wname VARCHAR(11),
wyanzhi INT,
CONSTRAINT fk_w_h FOREIGN KEY(wid) REFERENCES husband(hid)
);
INSERT INTO wife VALUES(1,"王太太",80);
INSERT INTO wife VALUES(2,"李太太",70);
INSERT INTO wife VALUES(3,"王太太",90);
3.3、多对多
1个学生可以选n门课程 1门课程可以被n个学生选
-- 1个学生可以选n门课程 1门课程可以被n个学生选
DROP TABLE student;
DROP TABLE kecheng;
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(11)
);
CREATE TABLE kecheng(
kid INT PRIMARY KEY AUTO_INCREMENT,
kname VARCHAR(11)
);
-- 定义一个关系表：关系表中定义两列作为联合主键 并且这两列都定义为外键 来分别引用两张表的主键
CREATE TABLE stu_kc(
sk_sid INT,
sk_kid INT,
PRIMARY KEY(sk_sid,sk_kid),/*定义联合主键*/
CONSTRAINT fk_sk_1 FOREIGN KEY(sk_sid) REFERENCES student(sid),
CONSTRAINT fk_sk_2 FOREIGN KEY(sk_kid) REFERENCES kecheng(kid)
);
INSERT INTO student VALUES(1001,"张三");
INSERT INTO student VALUES(1002,"李四");
INSERT INTO student VALUES(1003,"王五");
INSERT INTO kecheng VALUES(1,"java");
INSERT INTO kecheng VALUES(2,"c");
INSERT INTO kecheng VALUES(3,"python");
INSERT INTO stu_kc VALUES(1001,1);
INSERT INTO stu_kc VALUES(1001,2);
INSERT INTO stu_kc VALUES(1001,3);
INSERT INTO stu_kc VALUES(1002,1);
INSERT INTO stu_kc VALUES(1002,2);
INSERT INTO stu_kc VALUES(1002,3);
INSERT INTO stu_kc VALUES(1003,1);
INSERT INTO stu_kc VALUES(1003,2);
SELECT * FROM stu_kc;
-- 联合主键特点1：两个值不能同时相同
INSERT INTO stu_kc VALUES(1003,2);/*error:Duplicate entry '1003-2' for key 'PRIMARY'*/
-- 联合主键特点2：任何一个都不能为null
INSERT INTO stu_kc VALUES(NULL,2);/*error:Column 'sk_sid' cannot be null*/
