MySQL数据管理
1.外键（了解）
方式一：在创建表的时候，增加约束
CREATE TABLE`grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename`VARCHAR(50 NOT NuLL COMMENT "年级名称",
PRIMARY KEY ( 'gradeid' )
)ENGINE=INNOD DEFAULT CHARSET=utf8
--学生表的gradeid字段要去引用年级表的gradeid
--定义外键key
-- 给这个外键添加约束(执行引用）references引用
CREATE TABLE IF NOT EXISTs `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT "学号",
`name` VAROHAR(30) NOT NULL DEFAULT '匿名’ COMMENT "姓名",
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT "密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女’COMENT "性别",
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期",
`gradeid` INT(10) NOT NULL CONMMENT "学生的年级",
`address` VARCHAR(100) DEFAULT NULL COMMENT “家庭住址",
`emai7` VARCHAR(50)DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(id ),
KEY FK gradeidT ( gradeid`),
CONSTRAINT ‘FK_gradeid’FOREIGN KEY ( 'gradeid') REFERENCES grade '( gradeid')
)ENGINE=INNOD8 DEFAULT CHARSET=utf8
方式二 ：创建表成功后，添加外键约束
CREATE TABLE`grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename`VARCHAR(50 NOT NuLL COMMENT "年级名称",
PRIMARY KEY ( 'gradeid' )
)ENGINE=INNOD DEFAULT CHARSET=utf8
--学生表的gradeid字段要去引用年级表的gradeid
--定义外键key
-- 给这个外键添加约束(执行引用）references引用
CREATE TABLE IF NOT EXISTs `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT "学号",
`name` VAROHAR(30) NOT NULL DEFAULT '匿名’ COMMENT "姓名",
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT "密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女’COMENT "性别",
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期",
`gradeid` INT(10) NOT NULL CONMMENT "学生的年级",
`address` VARCHAR(100) DEFAULT NULL COMMENT “家庭住址",
`emai7` VARCHAR(50)DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(id ),
KEY FK_gradeidT ( gradeid`),
CONSTRAINT ‘FK_gradeid’FOREIGN KEY ( 'gradeid') REFERENCES grade '( gradeid')
)ENGINE=INNOD8 DEFAULT CHARSET=utf8
-- 创建时候没有外链关系
ALTER TABLE `student`
ADD CONSTRAINT ‘FK_gradeid’ FCREIGN KEY( gradeid ) REFERENCES‘grade ( gradeid ');
--ALTER TABLE 表 ADD CONSTRAINT约束名FCREIGN KEY(作为外键的列)REFERENCES那个表(哪个字段)
以上都是物理外键，数据库外键，不建议使用！（避免数据库过多造成困扰）
最佳实践 ：
-
数据库就是单纯的表，只用来存数据，只有行（数据）和列(字段)。
-
我们想使用多张表的数据，想使用外键(程序去实现)
2.DML语言（必须全部记住）
数据库意义：数据储存，数据管理
DML语言：数据操作语言
1. insert
1. update
1. delete
3.添加
insert 语句
-- 插入语句（添加）
-- insert into 表名（字段名，字段1，字段2，字段3）values('值1'),('值2'),('值3'...)
INSERT INTO `school`.`grade` (`gradename`) VALUES ('211');
-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）报错
INSERT INTO `school`.`grade` VALUES ('111');
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多条数据
INSERT INTO `school`.`grade`(`gradename`) VALUES ('nihao'),('tahao');
INSERT INTO `school`.`student`(`id`,`name`,`pwd`,`sex`) VALUES (1,'张三','aaa','男');
INSERT INTO `school`.`student`(`id`,`name`,`pwd`,`sex`) VALUES (2,'张4','aaa','男'),(3,'张5','aaa','男');
语法：insert into 数据库名.表名（字段名，字段1，字段2，字段3）values ('值1','值2','值3'),（'值1'，'值2'，'值3'）
注意事项
-
字段和字段之间使用英文 逗号 隔开。
-
字段可以省略的，但是后面的值必须要一一对应，不能少
-
可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可 VALUES('','','')
4.修改
update 修改谁（条件）set 原来的值 = 新值。
-- 修改学员名字
UPDATE `school`.`student` SET `name` ='zhang7' WHERE id = 3;
-- 不指定条件情况下，会改动所有表
UPDATE `school`.`student` SET `name` ='zhang789';
-- 修改多个属性,逗号隔开
UPDATE `school`.`student` SET `name`='zhang1',`pwd`='bbb' WHERE id = 1;
-- 语法：
-- UPDATE `数据库名`.`表名` set column_name = value,[column_name2 = value2,...... ] where [条件]
条件：where字句 运算符 id 等于某个值，大于某个值，在某个值区间内修改......
操作符会返回
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <> 或 != | 不等于 | 5<>6 | true |
| > | |||
| < | |||
| <= | |||
| >= | |||
| BETWEEN....AND...... | 在某个范围内 | [2,5] | [2,5] |
| AND | 我和你&& | ||
| OR | 我或你 || |
-- 通过多个条件定位数据
UPDATE `school`.`student` SET `name`='长江7号' WHERE `name` = 'zhang7' AND sex = '男';
UPDATE `school`.`student` SET `name`='长江7号' WHERE `name` = 'zhang7' OR sex = '男';
语法：UPDATE 数据库名.表名 set column_name = value,[column_name2 = value2,...... ] where [条件]
注意：
-
column_name 是数据库得列，尽量带上``;
-
条件，筛选条件，如果没有指定，则会修改所有得列。
-
value，是一个具体得值，也可以是一个变量。
-
多个设置的属性之间，使用英文逗号隔开。
-
UPDATE `school`.`student` SET `birthday`=CURRENT_TIME WHERE `name` = 'zhang7' OR `sex` = '男';
5.删除
delete 命令
语法：delete from 数据库.表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `school`.student;
-- 删除指定数据
DELETE FROM `school`.`student` WHERE id = 4;
TRUNCATE 命令
TRUNCATE `school`.student;
作用：完全清空一个数据库表，表的结构和索引约束不会变
delete的truncate区别：
-
相同点：都能删除数据，都不会删除表结构。
-
不同：
-
TRUNCATE 重新设置 自增列 计数器会归零
-
TRUNCATE 不会影响事务
-- 测试delete 和 truncate 区别
-- 创建表
INSERT INTO `school`.`test`(`id`,`coll`) VALUES (3,'ni'),(4,'ta');
DELETE FROM `school`.`test` -- 不会影响自增
TRUNCATE TABLE `school`.`test` -- 自增会归零了解即可： Delete 删除问题，重启数据库，现象
-
InnoDB 自增列会从1开始（存在内存当中，断电即失）
-
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）
-
6.DQL查询数据（最重点）
-
（Data Query Language：数据查询语言）
-
所有的查询都用它 Select
-
简单的查询，复杂的查询它也能做~
-
== 数据库种最核心的语言，最重要的语句 ==
-
使用频率最高的语言
-
-
指定查询字段
-
-- 查询全部的学生 slect 字段 from 表
SELECT * FROM `school`.`student`;
-- 查询指定字段
SELECT `name`,`sex` FROM `school`.`student`;
-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `id` AS `学号`,`name` AS `学生姓名` FROM `school`.`student` AS `学生`;
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',`name`) AS `新名字` FROM `school`.`student`;
-- 根据某一项查某一条
SELECT * FROM `school`.`student` WHERE `id` = 1;语法：slect 字段 ，...... from 表
有的时候，列名字不是那么的见名知意，我们起别名 AS 字段名 AS 别名 表名 AS 别名
-
去重：distinct
作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条。
-- 查询哪些同学参加了考试，成绩
SELECT * FROM `school`.`result`; -- 查询全部成绩
-- 查询哪些同学参加了考试
SELECT `id` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据，去重
SELECT DISTINCT `id` FROM result; -
数据库列（表达式）
-- 查询系统版本
SELECT VERSION()
-- 用来计算
SELECT 100*3-1 AS 计算结果;
-- 查询自增的步长
SELECT @@auto_increment_increment;
-- 学员考试成绩+1分 查看
SELECT `id`,`name`,`score`+1 AS '提分后' FROM `school`.`result`;数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量。。。。
select 表达式 from 表;
-
where条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果 布尔值！
逻辑运算符
运算符 语法 描述 and && a and b a&&b 逻辑与，两个为真，结果为真 or || a or b a||b 逻辑或，其中一个为真，则结果为真 Not ！ not a ！a 逻辑非，真为假，假为真！ == 尽量使用英文字母 ==
-
-- ==============where============
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `score`>5 && `score`<11;
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `score`>5 AND `score`<11;
-- 模糊查询（区间）
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `score` BETWEEN 5 AND 8;
-- 除了id为2学生之外的成绩
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `id` != 2;
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE NOT `id` = 2;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符为not 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....其中的某一个值中，结果为真 |
-- ===== 模糊查询====
-- 查询姓s的同学
-- Like结合 %(代表0到任意个字符) _（一个字符）
SELECT * FROM `school`.`student` WHERE `name` LIKE 's%';
SELECT * FROM `school`.`student` WHERE `name` LIKE 's_';
-- Like结合 __（一个字符）
SELECT * FROM `school`.`student` WHERE `name` LIKE 's__';
-- 查询名字中间有a字的同学 %a%
SELECT * FROM `school`.`student` WHERE `name` LIKE '%a%';
-- ========in(具体的一个或者多个值)=======
-- 查询 id 为1，2，3的学员
SELECT * FROM `school`.`student` WHERE `id` IN (1,2,3);
-- ==== null not null ======
SELECT * FROM `school`.`student` WHERE `address`='' OR `address` IS NULL;
-- 查询有出生日期 不为空
SELECT * FROM `school`.`student` WHERE `birthday`='' OR `birthday` IS NULL;
5.联表查询
join对比
-- ======联表查询======
-- 查询参加考试的同学（学号，姓名）
SELECT * FROM student
SELECT * FROM result
/* 思路
1.分许需求，分析查询字段来自哪些表，（连接查询）
2. 确定使用哪种查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 id = 成绩表 id
*/
-- join（连接的表） on（判断的条件） 连接查询
-- where 等值查询
-- INNER JOIN 交集查询
SELECT s.`id`,s.`name`,`score` FROM `school`.`student`AS s INNER JOIN result AS r WHERE s.id = r.id
-- RIGHT Join 右表查询
SELECT s.`id`,s.`name`,`pwd` FROM `school`.`student` AS s RIGHT JOIN result AS r ON s.id = r.id;
-- LEFT Join 左表查询
SELECT s.`id`,s.`name`,`pwd` FROM `school`.`student` AS s LEFT JOIN result AS r ON s.id = r.id;
-- 我要查询哪些数据 select ...
-- 从哪几个表中查，From表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询。慢慢来，先查两张表再慢慢增加条件
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 也会从左表中返回所有值，即使右表中没有匹配 |
| right join | 也会从右表中返回所有值，即使左表中没有匹配 |