数据库的增删改，DQL查询数据(where、联表查询等)

3、MySQL数据管理

3.1、外键(了解即可)
创建表成功后，添加外键约束
CREATE TABLE IF NOT EXISTS `grade`(
     `gradeid` INT(10) NOT NULL COMMENT '年级id',
      PRIMARY KEY(`gradeid`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
​
CREATE TABLE IF NOT EXISTS `student1`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
   `gradeid` VARCHAR(30) NOT NULL COMMENT'年级号码',
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
​
-- 创建表的时候没有外键关系
ALTER TABLE `student1`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(那个字段);

以上的操作都是物理外键了解即可

最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

  • 我们想使用多张表的数据，想使用外键（程序去实现）

3.2、DML语言(全部记住)

数据库意义：数据存储，数据管理

  • insert

  • update

  • delete

3.3、添加
-- 插入语句（添加）
-- insert into 表名([字段1，字段2])values('值1'）,（'值2'）,......)
-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配） 
INSERT INTO `student`(`pwd`)VALUES('520')
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多个字段
INSERT INTO `student`(`name`,`pwd`)VALUES('张三','123456'),('李四','321')

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

注意事项：

  1. 字段和字段之间使用 英文逗号 隔开

  2. 字段是可以省略的，但是后面的值必须要一一对应

例：INSERT INTO `student` VALUES('5','张三','123456','男','2000-01-01','西安')

  1. 可以同时插入多条数据，values后面的值，需要使用逗号隔开即可

3.4、修改
-- 修改学员名字
UPDATE `student` SET `name`='张无' WHERE id =1 ;
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='张四'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='狂神',`sex`='女' WHERE id = 2
-- 语法：
-- UPDATE 表名 SET colnum_name=value WHERE [条件] ;
UPDATE `student` SET `name`='哈登' WHERE id BETWEEN 2 AND 5 ; -- 修改2到5名字

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

操作符会返回 布尔值

操作符含义范围结果
= 等于 5=6 false
<>或！= 不等于 5<>6 true
>      
<=      
>=      
between...and... 在某个范围内 [2，5]  
AND 我和你&& 5>1 and 1>2 false
OR 我或你|| 5>1 or 1>2 true
        
-- 通过多个条件定位数据
UPDATE `student` SET `name`='长江'  WHERE `name` = '哈登' AND `pwd`='321'

语法： UPDATE 表名 SET colnum_name =value where [条件]

注意：

  • colnum_name 是数据的列，尽量带上``

  • 条件，筛选的条件，如果没有指定，则会修改所有的列

  • value，是一个具体的值，也可以是一个变量

  • 多个设置的属性之间，使用英文逗号隔开

UPDATE `student` SET `birthday`=CURRENT_TIME  WHERE `name` = '哈登' AND `pwd`='321'

 

3.5、删除
delete 命令

语法： delete from 表名 [where 条件]

-- 删除数据 （避免这样写，会完全删除）
delete from `student`
​
-- 删除指定数据
delete from `student` where id =1；

 

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

 -- 清空 student 表
 TRUNCATE `student`

 

delete 和 TRUNCATE 区别

  • 相同点：都能删除数据，都不会删除表机构

  • 不同：

    • TRUNCATE 重新设置 自增列 计数器会归零

    • TRUNCATE 不会影响事务

delete 和 TRUNCATE 区别
CREATE TABLE `test3`(
  `id` INT(20) NOT NULL AUTO_INCREMENT COMMENT'哈哈',
  `grade` VARCHAR(15) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8
​
INSERT INTO `test3`(`grade`)VALUES('a'),('b'),('c')
​
DELETE FROM `test3` -- 不会影响自增
​
TRUNCATE TABLE `test3`  -- 自增会归零

了解即可： delete删除的问题， 重启数据库，现象

  • InnoDB 自增列会从1开始（存在内存当中的，断电即失）

  • MylSAM 继续从上一个自增量开始（存在文件中的，不会丢失）

 

DQL查询数据（重点）

（Data Query Lauguage：数据查询语言）

  • 所有的查询操作都用它 Select

  • 简单的查询，复杂的查询它都能做~

  • 数据库中最核心的语言，最重要的语句

  • 使用频率最高的语句

4.1select语法

 

4.2指定查询字段
-- 查询全部的学生  select 字段 from 表
SELECT * FROM student
​
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
​
-- 别名，给结果起一个名字  As  可以给字段起别名，也可以给表起别名 （是给上面查出来的结果取别名不是给原始表取别名）
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
​
-- 函数  Concat(a,b) 
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

语法： select 字段,..... from 表

有的时候，列名字不是那么的见名知意。我们起别名 AS   字段名 as 别名， 表名 as 别名

 

去重 distinct

作用：去除select查询出来的结果中重复的数据

SELECT * FROM student 
SELECT `name` FROM student    -- 查询全部的同学
SELECT DISTINCT `name` FROM student   -- 发现重复姓名，去重

 

数据库的列（表达式）
​
SELECT VERSION()-- 查询系统版本
SELECT 100*3-1  AS 计算结果  -- 用来计算
SELECT @@auto_increment_increment -- 查询自增的步长
-- 学员考试成绩 +1 分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后'  FROM result

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

select 表达式 from 表

 

4.3 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>=95 AND StudentResult<=100
​
-- and &&
SELECT `studentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100
​
-- 模糊查询（区间）
SELECT `studentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 90 AND 100
​
-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo != 1000
​
--  !=  not
SELECT studentNo,`StudentResult` FROM result
WHERE NOT studentNo = 1000

 

模糊查询：比较运算符
运算符语法描述
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`,`StudentResult`,`StudentName` FROM result1
WHERE  StudentName LIKE '李_'
​
-- 查询姓刘的同学，名字中间有国字的
SELECT `studentNo`,`StudentResult`,`StudentName` FROM result1
WHERE  StudentName LIKE '%国%'
​
-- 查询 1001，1002，1003 号学员
SELECT `studentNo`,`StudentResult`,`StudentName` FROM result1
WHERE  `StudentNo` IN (1001,1005,1004)
​
-- 查询在北京的学生
SELECT `studentNo`,`StudentResult`,`StudentName` FROM result1
WHERE  `Address` IN ('北京')
​
-- 查询地址为空的学生 null ''
SELECT `studentNo`,`StudentResult`,`StudentName` FROM result1
WHERE  address ='' OR address IS NULL
​
-- 查询科目号码不为空的学生 is not null
SELECT `studentNo`,`StudentResult`,`StudentName` FROM result1
WHERE  `SubjectNo` IS NOT NULL

 

4.4、联表查询

-- join on 连接查询

-- where 等值查询

 

 

操作描述
inner join 如果表中至少有一个匹配，就返回行
left join 会从左表中返回所有的值，即使右表中没有匹配
right join 会从右表中返回所有的值，即使左表中没有匹配

左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据。

简言之 Left Join影响到的是右边的表

Right Join

select * from tbl1 Right Join tbl2 where tbl1.ID = tbl2.ID

检索结果是tbl2的所有数据和tbl1中满足where 条件的数据。

简言之 Right Join影响到的是左边的表。

-- 我要查询哪些数据 select ...
-- 从那几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

 

自连接

自己的表和自己的表连接，核心： 一张表拆为两张一样的表即可

 

 

 

-- 查询了参加 数据结构-1 考试的同学信息：学号，学生姓名，科目名，分数
-- 需要联立3张表  
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`SubjectResult`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN  `subject`  sub 
ON r.SubjectNo = sub.SubjectNo
WHERE `SubjectName`='数据结构-1'
​

 

 

posted @ 2021-09-15 21:24  敲代码的体育生  阅读(183)  评论(0)    收藏  举报