MySQL2-----表增删改查，字段增删改查，DML，DQL里的连表查询，RIGHTJOIN,INNERJOIN,LEFTJOIN

最新推荐文章于 2021-08-19 16:59:43 发布

Srwici

最新推荐文章于 2021-08-19 16:59:43 发布

阅读量121

点赞数

分类专栏： MySQL 文章标签： mysql

原文链接：https://editor.csdn.net/md/?articleId=108374741

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

int 后的数字是指的最大显示宽度，和数值的范围无关
varchar 后的数字则是和其长度有关，比如varchar(8），就最多只能保存八个字符的字符串。输入的字符串长度可变，但不能超过最大位数限制，存储空间根据存入字符串长度决定。
char 后的数字是固定存储空间大小，不管你输入多大的字符串（要小于等于最大值），都是会消耗一份设置最大值的存储空间。可用于md5加密。

表名和字段名尽量用`` 括起来。避免和关键字冲突。
字符串都要用 ‘’ 括起来，（英文单引号）。
数据类型和名字要在前面。
自增：Auto_Increment
注释:comment 后加 ‘’

2.4 创建一个数据库表

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(8) NOT NULL DEFAULT '匿名'  COMMENT '姓名',
	`pwd` VARCHAR(15) NOT NULL DEFAULT '123456' COMMENT '密码', 
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`email` VARCHAR(52)  DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

create table [if not exit] `表名`(
	`属性名` 列类型 [属性] [索引] [注释],
	`属性名` 列类型 [属性] [索引] [注释],
	 ...
	`属性名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

常用命令

show create databese `school`； --查看创建school库的语句 
show create table `student`; --查看创建student表的语句
desc `student`; --查看表的结构

2.5 数据表的类型(数据库引擎)

/* InnoDB是MySQL默认使用的
	MyISAM 早些年使用的
*/

项目	MYISAM	INNODB
事务支持	不支持	支持
事务行锁定	不支持	支持
外键	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为前者的两倍

常规操作

MYISAM：节约空间，操作速度较快。
INNODB：安全性高，支持事务的处理，多表多用户操作。

在物理空间存在的位置

所有的数据库文件都存在 data 目录下。data 目录下的一个文件夹就代表一个数据库。存在的形式本质上还是文件的存储。

MySQL 数据库引擎在物理空间上的区别：

INNODB会在 data目录下的创建一个 *.frm 文件和 *.frm文件的上级目录中创建一个 ibdata1 文件。
MYISAM 会在 data 目录下创建三个文件，分别是：
- *.frm：表结构的定义文件。
- *.MYD：数据文件。
- *.MYI：索引文件。

设置数据库的字符集编码：

charset=utf8

不设置的话，使用mysql默认的字符集编码是不支持中文的。
MySQL默认的编码是 latin1 。

2.6 修改和删除表

修改

--修改表名  alter table `旧表名` rename as `新表名`;
alter table `student` rename as `student1`;

--增加属性, alter table `表名` add `属性名` `列类型` [属性];
ALTER TABLE `student1` ADD `test` VARCHAR(50);

--修改表的字段,分成两种 : 1.对字段进行重命名. 2.对字段的约束进行修改
--1.重命名,alter table `表名` change `旧字段名` `新字段名` [属性]; 
ALTER TABLE `student1` CHANGE `test` `test1` VARCHAR(3);--重命名,也可以改约束.
--2.修改约束.alter table `表名` modify `字段名` 新约束;
ALTER TABLE `student1` MODIFY `test` INT(5); --修改约束

--修改删除表的字段
ALTER TABLE `student1` DROP `test1`;

删除

--删除表
DROP TABLE IF EXISTS `lst`;

所有的创建和删除尽量带上判断,避免报错,虽然还有警告~

注意点:

所有的字段名尽量用``括起来
注释使用 – , /**/ .
sql 关键字大小写不敏感

3.MySQL的数据管理

3.1 外键

方式一,在创建表的时候,增加外键约束

/*
	1.定义外键 
	  KEY `fk_字段名`(`在当前表中的字段名`),
	2.把外键添加到表的约束里 
	  constraint `fk_字段名` references `外键表名`(`字段名`)
*/
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(8) NOT NULL DEFAULT '匿名'  COMMENT '姓名',
	`pwd` VARCHAR(15) NOT NULL DEFAULT '123456' COMMENT '密码', 
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`email` VARCHAR(52)  DEFAULT NULL COMMENT '邮箱',
	`gradeId` INT(10) NOT NULL COMMENT '年纪id',
	PRIMARY KEY(`id`),
	KEY `fk_gradeId`(`gradeId`),
	CONSTRAINT `fk_gradeId` FOREIGN KEY (`gradeId`) REFERENCES `grade`(`gradeId`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

ps:

在删除存在外键关系的表时,要先删除引用别人字段的表,在删除被引用的表.

方式2:在已经建好的表里再建新的外键关系.

--前提已执行.
--学生表
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(8) NOT NULL DEFAULT '匿名'  COMMENT '姓名',
	`pwd` VARCHAR(15) NOT NULL DEFAULT '123456' COMMENT '密码', 
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`email` VARCHAR(52)  DEFAULT NULL COMMENT '邮箱',
	`gradeId` INT(10) NOT NULL COMMENT '年纪id',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
--年纪表
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeId` INT(10) NOT NULL AUTO_INCREMENT  COMMENT '年纪id',
	`gradeName` VARCHAR(10) NOT NULL COMMENT '年级姓名',
	PRIMARY KEY (`gradeId`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

执行完后,进行添加外键关系.

ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`);

以上都是数据库级别的外键,不建议使用!!!
最好呢

数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段).
想使用多张表的数据,采用程序去实现外键

3.2 DML语言(全部记住)

数据库的意义:数据存储,数据管理
DML:数据操作语言.

增加:insert
修改:update
删除:delete

3.3 增加

insert into 表名(字段1,字段2,字段3,…) values(‘值1’,’‘值2’,‘值3’,…)[,(‘值1’,’‘值2’,‘值3’,…)…];

注意事项:

插入时如果不写表的字段名,就会默认一一匹配.
在不同字段之间要有逗号隔开。
没有默认填写的字段,在插入时必须要进行填写。
在values 后可以同时插入多个值,但是必须用逗号隔开.values(),(),()…
后面的值不管插入几个，每一个都必须用括号括起来。

3.4修改

Update 修改谁(表) set 哪个字段= 新值 where(设置条件);

--UPDATE 表名 
UPDATE `student` SET `name`='swrici' WHERE id='1';
--如果不指定条件的话,会将所有字段都进行修改
UPDATE `student` SET `gradeId`='2';
--修改多个属性,逗号隔开
UPDATE `student` SET `name`='狗哥',`sex`='天下第一' WHERE id='2';

--语法
UPDATE 表名 SET column_name1=value[,column_name1=value,...] WHERE[条件]

条件也就是where子句.

操作符	含义	范围	结果
=	等于	等号两边是否相等	相等/不相等
<>或!=	不等于	除了条件外的其他都成立
>	大于	比条件大的数	比条件大的数被改
<	小于	比条件大的数	比条件小的数被改
>=	大于等于	同理	同理
<=	小于等于	同理	同理
between…and…	在什么之中	在两个数之间包含两个边界值	边界内的值都被修改
and	和	and两边都成立	两边都成立或者都不成立
or	或	或两边需要至少一边为true	两边都不成立或者都成立

语法：
UPDATE 表名 SET column_name1=value[,column_name1=value,...] WHERE[条件]
注意：

column是表里面的列，尽量加上``。
条件是筛选表的条件如果没有条件就会对所有列进行修改。
vlaue可以是具体的值，也可以是变量，例如current_time,当前日期变量。
set后可以加多个属性，但是必须要用逗号隔开。

3.5删除

delete

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

--删除数据 ,会删除所有数据，避免这样写
DELETE FROM `student`

--删除指定数据
DELETE FROM `student` WHERE id = '3';

TRUNCATE命令

语法：
TRUNCATE 表名;

--truncate命令，清空数据，表的结构,索引和约束不会改变。
TRUNCATE `student`;

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

delete命令和truncate命令的区别

相同点：
- 两者都能删除数据，都不会破坏表结构。
不同点：
- truncate 命令能重新设置自增列，计数器回归0.不会影响事务。
- delete命令没有重启数据库的话是不会改变自增量的值的，如果重启了数据库：
  - 在INNODB中，自增量保存在内存中，如果重启，自增了则会重新从1开始。
  - 在MyISAM中，则重启自增了也不发生改变，因为这个自增量是保存在文件中。

4 DQL

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

所有的查询操作都要用到。
既包含了简单的查询，又包含复杂的查询。
数据库中最核心的语言
使用频率最高的语言。

4.2 指定查询字段

--指定查询字段 SELECT 字段 FROM 表名
SELECT * FROM `student`;

--查询所有的成绩
SELECT * FROM `result`;

--查询 具体字段
SELECT  `studentno`,`subjectno` FROM `result`;

--给查询出来的列名取名字 用 AS，表也可以取别名 也是用as
SELECT  `studentno` AS '学生学号',`subjectno` AS '学生科目' FROM `result` AS `s`;
--取别名的时候 AS 也可以省略 
SELECT  `studentno`  '学号',`subjectno`  '科目' FROM `result`;

--mysql里面也存在函数，拼接字符串函数,CONCAT(a,b)
SELECT CONCAT('学号:',`studentno`) FROM `result`;

语法： select 字段,... from 表名;
select完整的语法:

SELECT
	[ALL|DISTINCT |DISTINCTROW]
	[HIGH_PRIORITY]
	[STRAIGHT_JOIN]
	[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
	[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
	select_expr [,select_expr...]
	[from table_references
		partition partition_list]
	[left | right |inner join on table_name2] -- 联合查询
	[where where_condition] -- 指定结果需要满足的条件
	[group by {col_name | expr | position } -- 指定结果按哪个字段进行分组
		[ASC | DESC],...[WITH ROLLUP]]
	[HAVING WHERE_CONDITION] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY {col_name | expr | position} -- 指定查询记录按一个或多个进行分组
		[ASC | DESC],...]
	[LIMIT {[OFFSET,] row_count | row_count OFFSET offset}] -- 指定记录从哪条到哪条
	[PROCEDURE procedure_name(argument_list)]
	[into outfile 'file_name'
		[character set charset_name]
		export_options
		| into dumpfile 'file_name'
		| into var_name [,var_name]]
	[for update | lock in share mode]]

有时列名不容易辨认出是什么意思，可以采用别名输出，
字段名 [as] 别名1
表名 [as] 别名2

去重

--去重 DISTINCT
--查询哪些学生参加了考试
SELECT `studentno` FROM `result`;
--去重查询哪些学生参加了考试
SELECT DISTINCT `studentno` FROM `result`;

问题，如果查询结果两列的话，如果上下两行没有完全重复则会继续显示。

数据库的列（表达式）

--查询mysql的版本，不是大小写敏感！！！(函数)
SELECT VERSION();

--做计算 （表达式）
SELECT 100*99-89+6 AS '100*99-89+6表达式的计算结果';

--查询自增的步长（变量）
SELECT @@auto_increment_increment;

--表达式可以包含字段名
SELECT `studentno`,`studentresult` '原分数',`studentresult`+1 '分数+1' FROM `result`;

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

4.3 where条件子句

作用：检索数据中符合条件的值
条件就是由一个或者多个表达式组成的，结果为布尔值。

逻辑运算符

运算符	语法	结果描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or \|\|	a or b a\|\|b	逻辑或，有一个为真，结果为真
Not ！	not a ! a	逻辑非，a 为假，结果为真

尽量使用英文符号。

-- ==============where==============
SELECT `studentno`,`studentresult` FROM `result`;
-- 查询成绩在95~100之间的
SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=95 AND `studentresult`<=100;
-- 使用&& 表示逻辑与
SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=95 && `studentresult`<=100;

-- 模糊查询（区间）between...and...
SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult` BETWEEN 95 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	如果a为空，结果为真
IS NOT NULL	a is not null	如果a不为空，结果为真
Not ！	not a ! a	逻辑非，a 为假，结果为真
between … and …	a between b and c	若a在b和c之间，结果为真，b>=c
like	a like b	SQL匹配，如果a匹配b，则结果为真，小红 like 红
in	a in (a1,a2,a3…)	如果a被在其中的某一个值中，则结果为真

-- =================模糊查询================
-- 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(具体的一个或者多个值，不能和like的通配符混用) =====
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1001,1002,1003);


-- ============= IS NULL ==============
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` = '' OR `address` IS NULL;
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL;


-- ============= IS NOT NULL ==============
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL;

4.4 连表查询

join

七种Join理论图片
ps:
在重命名字段名，表名的时候，``内不能包含. ,要认识到两个``是为了避免使用到关键字的时候用到的。

/*
   1.分析需求，使用到哪些表
   2.确定连接方式 ，七种
   3.确定交叉点，相等的条件（哪些数据是共同拥有的）。
*/
-- ======== 连表查询 ========
-- ======== inner join =======
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`;

-- ======== right join ========
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`;

-- ======= left join =========
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`;

-- =========== left join where ========
-- ===== 查询缺考的同学 =======
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL;


-- JOIN ON  连接查询
-- where 等值查询

-- ====== 查询参加了考试的学生 ====
SELECT s.`studentno`,`studentname`,r.`subjectno`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno` 
LEFT JOIN `subject` j
ON  r.`subjectno` = j.`subjectno`;

-- 多张表查询，先两张两张慢慢找

操作	描述
INNER JOIN	如果表至少有一个匹配，则返会查询
LEFT JOIN	从左表返回所有的值，即使右表没有匹配
RIGHT JOIN	从右表返回所有的值，即使左表没有匹配