MYSQL(二)

2.6数据表的类型

设置数据表的类型

CREATE TABLE 表名(
    -- 省略一些代码
    -- Mysql注释
    -- 1. # 单行注释
    -- 2. /*...*/多行注释
)ENGINE = MYISAM(or InnoDB)
-- 查看mysql所支持的引擎类型(表类型)
SHOW ENGINES;

MySQL的数据表的类型:MyISAM,InnoDB，HEAP,BOB,CSV等...

常见的MyISAM与InnoDB类型:

名称	MyISAM	InnoDB
事物处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	不支持	支持
表空间大小	较小	较大，越2倍

经验(适用场合):

适用MyISAM:节约空间及相应速度
适用InnoDB:安全性，事务处理及多用户操作数据表

数据表的存储位置

MySQL数据表以文件方式存放在磁盘中
- 包括表文件，数据文件，以及数据库的选项文件
- 位置:Mysql安装目录\data\下存在数据表.目录名对应数据库名，该目录下文件名对应数据表
注意:
- InnoDB类型数据表只有一个*.frm文件，以及上一级目录的ibdata1文件
- MyISAM类型数据表对应三个文件:
  - *.frm --表结构定义文件
  - *.MYD --数据文件(data)
  - *.MYI --索引文件(index)

设置数据表字符集

我们可为数据库，数据表，数据列设定不同的字符集，设定方法:

创建时通过命令来设置,如: CREATE TABLE 表名() CHARSET = utf8;
如无设定，则根据MySQL数据库配置文件my.ini中的参数设定

2.7修改数据库

修改表(ALTER TABLE)

修改表名: ALTER TABLE 旧表名 RENAME AS 新表名

添加字段: ALTER TABLE 表名 ADD字段名列属性[属性]

修改字段:

ALTER TABLE 表名 MODIFY 字段名列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名新字段名列属性[属性]

删除字段: ALTER TABLE 表名 DROP 字段名

删除数据表

语法: DROP TABLE [IF EXISTS] 表名

IF EXISTS为可选,判断是否存在该数据表
如删除不存在的数据表会抛出错误

其他

1.可用反引号(`)为标识符(库名、表名、字段名、索引、别名)包裹,以避免与关键字重名！中文也可以作为标识符!
2.每个库目录存在一个保存当前数据库的选项文件db.opt.
3.注释
  单行注释 # 注释内容
  多行注释 /* 注释内容 */
  单行注释 -- 注释内容 (标准SQL注释风格，要求双破折号后一空格符(空格、TAB、换行等))
4. 模式通配符:
  - 任意单个字符
  % 任意多个字符，甚至包括零字符
  单引号需要进行转义\`
5. CMD命令行内的语句结束符可以为";","\G","\g",仅影响显示结果。其他地方还是用分号结束。delimiter可修改当前对话的语句结束符。
6.SQL对大小写不敏感(关键字)
7. 清除已有语句:\c

3.MySQL数据管理

3.1 外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据，约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

创建外键

建表时指定外键约束

-- 创建外键的方式一:创建子表同时创建外键
-- 年级表(id\年级名称)
-- 创建外键的方式一:创建子表同时创建外键
-- 年级表(id\年级名称)
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
-- 学生信息表(学号，姓名，性别，年级，手机，地址，出生日期，邮箱，身份证号)
CREATE TABLE `student` (
  `studentno` INT(4) NOT NULL COMMENT '学号',
  `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名,',
  `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
  `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
  `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
  `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
  `borndate` DATETIME DEFAULT NULL COMMENT '生日',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`studentno`),
  KEY `FK_fradeid` (`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`);

删除外键

操作:删除grade表，发现报错

注意:删除具有主外键关系的表时，要先删子表，后删除主表

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的，索引还在，所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student  DROP INDEX FK_gradeid;

3.2 DML语言

数据库意义:数据存储、数据管理

管理数据库数据方法:

通过SQLyog等管理工具管理数据库数据
通过DML语句管理数据库数据

DML语言: 数据操作语言

用于操作数据库对象中所包含的数据
包括:
- INSERT(添加数据语句)
- UPDATE(更新数据语句)
- DELETE(删除数据语句)

3.3 添加数据

INSERT命令

语法:

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

注意:

字段或值之间用英文逗号隔开
'字段1，字段2...' 该部分可省略，但添加的值务必于表结构，数据列，顺序相对应，且数量一致。
可同时插入多条数据，values后用英文逗号隔开。

-- 使用语句如何增加语句?
-- 语法: INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');
-- 主键自增,那能否省略呢?
INSERT INTO grade VALUES ('大二');
-- 查询:INSERT INTO grade VALUE('大二')错误代码:1136
Column count doesn't match value count at row 1
-- 结论:'字段1,字段2...'该部分可省略,但添加的值务必与表结构，数据列，顺序想对应，且数量一致.
-- 一次插入多条数据
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

练习题目

自己使用INSERT语句为课程表subject添加数据.使用到外键.

3.4 修改数据

update命令

语法:

UPDATE 表名 SET column_name=value [,column_name2 = value2,...] [WHERE condition];

UPDATE grade SET gradename = '大五' WHERE gradeid = 4

注意:

column_name为要更改的数据列
value为修改后的数据,可以为变量，具体指，表达式或者嵌套的SELECT结果
condition为筛选条件，如不指定则修改该表的所有列数据

where条件子句

可以简单的理解为:有条件地从表中筛选数据

运算符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
BETWEEN	在某个范围之间	BETWEEN 5 AND 10
AND	并且	5>1 AND 1>2	false
OR	或	5>1 OR 1>2	true

测试:

-- 修改年级信息
UPDATE  grade SET gradename = '高中' WHERE gradeid <=2;

3.5 删除数据

DELETE命令

语法:

DELETE FROM 表名 [WHERE condition];

注意:condition为筛选条件,如不指定则删除该表的所有列数据

-- 删除最后一个数据
DELETE FROM grade WHERE gradeid = 4

TRUNCATE命令

作用:用于完全清空表数据,但表结构，索引，约束等不变;

语法:

TRUNCATE [TABLE] table_name;

-- 清空年级表
TRUNCATE grade

注意:区别于DELETE命令

相同:都能删除数据，不删除表结构，但TRUNCATE速度更快
不同:
- 使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响(事务后面讲)

测试:

-- 创建一个测试表
CREATE TABLE `test`(
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `coll` VARCHAR(20) NOT NULL,
   PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- 删除表数据(不带where条件的delete)
DELETE  FROM test;
-- 结论:如不指定where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.
-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.
-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB:自增列从初始值重新开始(因为是存储在内存中，断电即失)
-- MyISAM: 自增列依然从上一个自增数据基础上开始(存在文件中,不会丢失)

4.使用DQL查询数据(最重要)

4.1 DQL语言

DQL(Data Query Language数据查询语言)

查询数据库数据,如SELECT语句
简单的单表查询或多表的复杂查询和嵌套查询
是数据库语言中最核心,最重要的语句
使用频率最高的语句

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2] -- 联合查询
    [WHERE ...] -- 指定结果需满足的条件
    [GROUP BY ...] -- 指定结果按照哪几个字段来分组
    [HAVING] -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    -- 指定查询的记录从哪条至哪条

注意:[]括号代表可选的,{}括号代表必选得

导入素材提供的SQL

4.2 指定查询字段

-- 查询表中所有的数据列结构,采用**"\*"** 符号;但是效率低,不推荐.
-- 查询所有学生信息
SELECT * FROM student;

-- 查询指定列(学号,姓名)
SELECT studentno,studentname FROM student;

AS 子句作为别名

作用:

可给数据列取一个新别名
可给表取一个新别名
可把经计算或总结的结果用另一个新名称来代替

-- 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
-- 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

INSERT  INTO student(`studentno`,`studentname`,`gradeid`,`phoneNum`,`address`,`email`,`idCard`) VALUES(1,'张三',1,'1392837557','陕西铜川','39274@qq.com','1');
INSERT  INTO student(`studentno`,`studentname`,`gradeid`,`phoneNum`,`address`,`email`,`idCard`) VALUES(2,'张四',2,'1392837567','陕西西安','39273@qq.com','2');
INSERT  INTO student(`studentno`,`studentname`,`gradeid`,`phoneNum`,`address`,`email`,`idCard`) VALUES(1,'张五',3,'1392838557','陕西宝鸡','29274@qq.com','3');
INSERT  INTO student(`studentno`,`studentname`,`gradeid`,`phoneNum`,`address`,`email`,`idCard`) VALUES(1,'张六',4,'1392832557','陕西铜川','59274@qq.com','4');

DISTINCT关键字的使用

作用:去掉SELECT查询返回的记录结果中重复的记录(返回所有列的值都相同),只返回一条

-- #查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项,(默认是ALL)

使用表达式的列

数据库中的表达式:一般由文本值,列值,NULL，函数和操作符等组成

应用场景:

SELECT语句返回结果列中使用
SELECT语句中的ORDER BY,HAING等子句中使用
DML语句中的where条件语句中使用表达式

-- select查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

避免SQL返回结果中包含'.','*'和括号等干扰开发语言程序.

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 95 AND 100;

-- 除了10号同学，要其他同学的成绩
SELECT Studentno,StudentResult FROM result WHERE Studentno !=10;

-- 使用NOT
SELECT Studentno,StudentResult FROM result WHERE NOT Studentno = 10;

模糊查询: 比较操作符

操作符名称	语法	描述
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....中的某一个，则结果为真

注意:

数值数据类型的记录之间才能进行算术运算;
相同数据类型的数据之间才能进行比较;

测试:


-- 模糊查询 between and \ like \in \null
-- 查询姓刘的同学的学号及姓名
-- 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 '%三%';

-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'

-- ========================================
-- IN
-- ========================================
-- 查询学号为10,11,12的学生姓名
SELECT studentno,studentname FROM student WHERE studentno IN(10,11,12);


-- 查询地址在铜川，渭南,延安的学生
SELECT studentno,studentname,address FROM student WHERE address IN ('陕西铜川','陕西渭南','陕西延安');

-- ========================================
-- NULL 空
-- ========================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL,这是代表错误的,用 is null
SELECT studentname FROM  student WHERE borndate IS NULL;

-- 查询出生日期填写的同学
SELECT studentname FROM student WHERE borndate IS NOT NULL;

-- 查询没有写家庭地址的同学(空字符串不等于null)
SELECT studentname FROM student WHERE address='' OR address IS NULL;

4.4 连接查询

JOIN对比

操作符名称	描述
INNER JOIN	如果表中有至少一个匹配,则返回行
LEFT JOIN	即使右表中没有匹配,也从左表中返回所有的行
RIGHT JOIN	即使左表中没有匹配,也从右表中返回所有的行

测试

/*
连接查询
    如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
    查询连个表中的结果集中的交集
外连接 outer join
    左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
    右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
       
等值连接和非等值连接

自连接
*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*
思路:
(1):分析需求,确定查询的列来源于两个类,student ,result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/

SELECT s.studentno,studentname,subjectno,studentresult FROM student AS s
INNER JOIN result AS r
ON r.studentno = s.studentno

-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,studentresult FROM student AS s
RIGHT JOIN result AS r
ON r.studentno = s.studentno

-- 等值连接
SELECT s.studentno,studentname,subjectno,studentresult FROM student AS s
,result AS r WHERE r.studentno = s.studentno

-- 左连接(查询了所有所有同学，不考试的 也会查询出来)
SELECT s.studentno,studentname,subjectno,studentresult FROM student AS s
LEFT JOIN result AS r
ON r.studentno = s.studentno

-- 查一下缺考的同学(左连接 应用场景)
SELECT s.studentno,studentname,subjectno,studentresult FROM student AS s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE studentresult IS NULL

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,studentresult FROM student AS s
INNER JOIN result r ON r.studentno = s.studentno 
INNER JOIN `subject` sub ON sub.subjectno = r.subjectno

自连接


/*
自连接
    数据表与自身进行连接
    
需求:从一个包含栏目ID,栏目名称和父栏目ID的表中
   查询父栏目名称和其他子栏目名称 
*/

-- 创建一个表
CREATE TABLE `category` (
   `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT `主题id`,
   `pid` INT(10) NOT NULL COMMENT `父id`,
   `categoryName` VARCHAR(50) NOT NULL COMMENT `主题名字`,
   PRIMARY KEY (`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category` (`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来(父栏目名称,子栏目名称)

-- 核心思想:把一张表看成两张表一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目' , b.categoryName AS '子栏目' FROM category AS a,category AS b
WHERE a.`categoryid` = b.`pid`

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,studentresult FROM student AS s INNER JOIN result AS r
ON r.studentno = s.studentno INNER JOIN `subject` AS sub
ON sub.subjectno = r.subjectno

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s INNER JOIN grade g ON s.`GradeId` = g.`GradeID`


-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称 FROM SUBJECT sub 
INNER JOIN grade g ON sub.gradeid = g.gradeid

-- 查询 信息技术的所有考试结果(学号,学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,studentresult FROM student AS s INNER JOIN result AS r ON r.studentno = s.studentno 
INNER JOIN `subject` AS sub ON r.subjectno = sub.subjectno WHERE subjectname='信息技术'

4.5 排序和分页

测试

/* 
========================排序=======================
语法:ORDER BY 
     ORDER BY  语句用于根据指定的列对结果集进行排序。
     ORDER BY  语句默认按照ASC升序对记录进行排序。
     如果您希望按照升序对记录进行排序，可以使用DESC关键字。
*/

-- 查询  信息技术的所有考试结果(学号 学生姓名  科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,studentresult 
FROM student AS s
INNER JOIN result AS r
ON r.studentno = s.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname  = '信息技术'
ORDER BY studentresult DESC

/* 
======================分页===========================
语法:SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处: (用户体验,网络传输，查询压力)

推到:
    第一页: limit 0,5
    第二页: limit 5,5
    第三页: limit 10,5
    ......
    第N页: limit(pageNo-1)*pageSize,pageSize
    [pageNo:页码,pageSize:单页面显示条数]
*/

-- 每页显示5条数据

posted @ 2022-07-12 22:46 kalibb 阅读(37) 评论(0) 收藏举报

刷新页面返回顶部

flag