MySQL数据管理

1.外键

方式一、在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade`(
    `gradeid`  INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）reference引用
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `brithday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `gradeid` INT(10)NOT NULL COMMENT '学生的年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50)DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二、创建表后，增加约束（麻烦，比较复杂）

-- 创建表没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

以上操作都是物理外键，不建议使用，之后用程序实现，数据库层面最好解耦。

二.DML语言（全部记住）

数据管理语言

  • insert
  • update
  • delete

2.1、添加(INSERT)

-- 插入多个字段
insert into `grade`(`gradename`)values('大二'),('大一') 
insert into `student`(`name`)values('张三')
insert into `student`(`name`,`pwd`,`sex`)values('李四','32556','男')

语法：insert into 表名([字段名1,字段名2,字段名3])values('值1'),('值2'),...

2.2、修改(UPDATE)

-- 修改学员的名字,不指定where会改所有数据
update `student` set `name`='狂神' where id=1;
-- 语法:UPDATE 表名 set column_name = value where [条件]
-- 修改多个属性
UPDATE `student` set `name`='狂神',email='3213124@qq.com' where id=2;
-- 语法:UPDATE 表名 set column_name = value,[column_name = value] where [条件]

条件:where子句运算符id等于某个值，大于某个值，在某个区域修改。

操作符 含义 范围 结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
BETWEEN...AND... 在某个范围内 [2,5]
AND 我和你&& 5>1AND1>2 false
OR 我或你|| 5>1or1>2 true 
-- 通过多个条件定位数据
UPDATE `student` SET `name`='狂神1' WHERE sex='女' AND pwd='32556';

语法:UPDATE 表名 SET column_name = value WHERE [column_name = value] AND [column_name = value];

2.3、删除(DELETE)

-- 删除数据
DELETE FROM `student` WHERE id=1
-- 清空
TRUNCATE `student`

DELETE和TRUNCATE区别在于后者自增会归零。

三.DQL查询语言（重点）

3.1、指定查询字段

简单查询

-- 查询全部学生 SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字 AS 可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 名字 FROM student

-- 函数 Concat（a,b）
SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM student

语法:SELECT 字段...FROM 表

去重

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result-- 查询全部考试成绩
SELECT `studentno` FROM result -- 查询哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result -- 发现重复数据，去重

数据库的列（表达式）

-- 查看系统版本
SELECT VERSION()-- 查询版本（函数）
SELECT 100*3-1 AS 计算结果-- 用来计算（表达式）
SELECT @@auto_increment-- 自增的步长（变量）

-- 学员考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 AS '提分' FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量...

3.2、where条件子句

作用：检索数据中符合条件的值

逻辑运算符

操作符 语法 描述
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
Not ! not a !a 逻辑非 
-- ======================where======================
SELECT `studentno`,`studentresult` FROM result

-- 查询考试成绩在95~100分之间
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>=85 AND `studentresult`<=100

-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`BETWEEN 85 AND 100

-- 除了1000号学生之外的同学成绩
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno`!=1000

3.3、模糊查询

运算符 语法 描述
IS NULL a is null 如果操作符为NULL，结果为真
IS NOT NULL a is not null 如果操作符不为NULL，结果为真
BETWEEN a between b and c 如果a在b和c之间，则结果为真
Like a like b SQL匹配，如果a匹配b，则结果为真
In a in(a1,a2,a3...) 假设a在a1，或者a2...其中一个值中，结果为真 
-- ===============模糊查询===============
-- 查询姓刘的同学
-- 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 '%嘉%'

-- ===============in(具体的一个或者多个值)====================
-- 查询1001,1002,1003号学员
SELECT `studentname`,`studentno` FROM student
WHERE studentno IN(1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('安徽');

-- ==== null not null====
-- 查询地址为空的学生 null
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL

-- 查询有出生日期的同学不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

3.4、联表查询

JOIN对比

sql七种理论.jpg
-- ============联表查询join===============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
select * from student
select * from result

select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno=r.studentno

-- Right Join
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno=r.studentno

-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
left JOIN result AS r
ON s.studentno=r.studentno
操作符 描述
Inner join 如果表中至少有一个匹配，就返回匹配的值
left join 会从左表中返回所有的值，即使右表中没有匹配
right join 会从右表中返回所有的值，即使左表中没有匹配

3.5、分页和排序

排序

-- 排序：升序（ASC）和降序（DESC）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname LIKE '高等数学%'
ORDER BY `studentresult` ASC

分页

-- 分页：每页只显示五条数据
-- 语法：limit当前页，页面的大小
-- LIMIT 第n页每页k条数据 （n-1）*k,k
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
-- WHERE subjectname LIKE '高等数学%'
-- ORDER BY `studentresult` ASC
LIMIT 0,5

3.6、子查询

本质在where语句中签到一个子查询语句

SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80 AND `subjectno`=（
    SELECT `subjectno` FROM `subject`
    WHERE `subjectname`='高等数学-2'
）

四.MySQL函数

4.1、常用函数

-- ========常用函数==========
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT SIGN(-10) -- 判断一个数的符号
SELECT RAND() -- 返回一个0~1的随机数

-- 字符串函数
SELECT CHAR_LENGTH('小汉堡') -- 字符串长度
SELECT CONCAT('我爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级爱') -- 插入替换,从1处替换2个长度
SELECT LOWER('KuanSheng') -- 小写字母
SELECT UPPER('KuanSheng') -- 大写字母
SELECT INSTR('KuanSheng','u') -- 返回出现索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换指定字符串
SELECT SUBSTR('坚持就能成功',1,6) -- 从1处开始截取6个
SELECT REVERSE('坚持就能成功') -- 反转字符串

-- 查询姓周的同学，改为㑇
SELECT REPLACE(studentname,'张','章') FROM `student`
WHERE `studentname` LIKE '张%'

-- 时间和日期
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE()
SELECT NOW()

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT VERSION()

4.2、聚合函数（常用）

函数名称 描述
COUNT() 计数
SUM 求和
AVG 平均值
MAX() 最大值
MIN() 最小值
...

5.3、数据库级别的MD5加密

主要增强复杂度不可逆性

CREATE TABLE `testMD5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUE(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd)

-- 插入的时候加密
INSERT INTO `testmd5` VALUE(4,'xiaoming',MD5('123456'))

-- 如何校验，将用户传递进来的密码进行md5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='xiaoming' AND pwd=MD5('123456')
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,132评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,802评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,566评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,858评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,867评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,695评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,064评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,705评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,915评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,677评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,796评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,432评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,041评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,992评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,223评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,185评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,535评论 2赞 343