mysql笔记二----mysql数据库乱码出现的原因、解决办法和表之间的设计，增加约束

最新推荐文章于 2022-08-31 21:14:03 发布
最新推荐文章于 2022-08-31 21:14:03 发布
阅读量1k 收藏 2
点赞数 1
分类专栏： mysql数据库 mysql数据库 文章标签： mysql 解决方案 乱码 select 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wangjian_an/article/details/52133697
版权

INSERT INTO sstud(sno,sname,sage,saddress,ssex) VALUES(‘1013’,’Big’,25,’自己人’,’男’);
DELETE FROM sstud WHERE sno=’1013’;
UPDATE sstud SET saddress=’中国北京’ WHERE sno=’1013’;
SELECT * FROM sstud ;
SELECT COUNT(*) FROM sstud GROUP BY saddress;
解决中文乱码，如下三个编码保持一致就可以。
character_set_client、character_set_connection、character_set_results
show VARIABLES WHERE Variable_name LIKE ‘character%’;查看数据库的编码信息
SET character_set_results =utf8 //更改的是某个客户端的编码，其他客户端不影响

CASE-WHEN THEN END
SELECT sno,sname,saddress ,(CASE ssex WHEN ‘1’ THEN ‘男’ WHEN ‘0’ THEN ‘女’ ELSE ‘其他’ END) AS 性别 FROM sstud;//对表中了数据进行重命名

※无关子查询
//查询具有相同年龄的人个数
SELECT sname FROM sstud GROUP BY sage HAVING COUNT(sage) > 1 ORDER BY COUNT(*) DESC
※相关子查询
//查询不但具有相同年龄，而且性别是男的那些人

SELECT * FROM sstud GROUP BY sage HAVING COUNT(sage)>1 AND ssex=’1’;

表与表之间的关系（尽量满足第三范式)
1、一对一
需要两个表。当然做项目时为了省空间，通常只建一个表，如果要实现一对一的查询，可以建两个视图。示例如下：
1)建物理表，初始化数据
CREATE TABLE person(
id INT,
NAME VARCHAR(10),
sex CHAR(1),
wife INT,
husband INT
);
INSERT INTO person VALUES(1,’小花’,’0’,0,3);
INSERT INTO person VALUES(2,’玉芬’,’0’,0,4);
INSERT INTO person VALUES(3,’张三’,’1’,1,0);
INSERT INTO person VALUES(4,’李四’,’1’,2,0);
INSERT INTO person VALUES(5,’王五’,’1’,0,0);
2) 建立两个视图
CREATE VIEW women AS SELECT * FROM person WHERE sex=’0’;
CREATE VIEW man AS SELECT * FROM person WHERE sex=’1’;
3) 查询夫妻信息
SELECT women.NAME AS 妻子,man.name AS 丈夫 FROM women,man WHERE women.husband=man.id;
//采用内联接–98以后的新方式–效率更高
SELECT women.NAME AS 妻子,man.NAME AS 丈夫 FROM women INNER JOIN man WHERE women.husband=man.id;

2、一对多,多对一

数据库设计分析
※案例：一个人可以拥有多辆汽车，要求查询出某人所拥有的所有汽车。
方案一(差的设计)：
编号 姓名 性别 年龄 汽车编号 车型 排量 价格
P001 Jack 男 25 C001 BMW 12L 80w
P001 Jack 男 25 C002 Benz 12L 100w
P001 Jack 男 25 C003 Benz 12L 90w
P002 Tom 男 25 C004 BMW 12L 80w
P002 Tom 男 25 C005 Benz 12L 100w
P003 Rose 女 25 C006 Benz 12L 100w

方案二(好的设计)：
1）把一方单独建个表
编号 姓名 性别 年龄
P001 Jack 男 25
P002 Tom 男 25
P003 Rose 女 25
2）把多方也建个表(依赖一方,通过外键–补一个字段)
外键:位于依赖一方，它是被依赖一方是主键
汽车编号 车型 排量 价格 车主
C001 BMW 12L 80w P001
C002 Benz 12L 100w P001
C003 Benz 12L 120w P001
C004 BMW 12L 80w P002
C005 Benz 12L 100w P002
C006 Benz 12L 100w P003
3) 代码实现
CREATE TABLE person2(
id VARCHAR(4) PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
sex VARCHAR(1) DEFAULT ‘男’,
age INT
)

INSERT INTO person2(id,NAME,sex,age) VALUES(‘P001’,’小花’,’0’,25);
INSERT INTO person2 VALUES(‘P002’,’张三’,’1’,22);
INSERT INTO person2 VALUES(‘P003’,’Jack’,’1’,24);
INSERT INTO person2 VALUES(‘P004’,’Rose’,’0’,25);

CREATE TABLE car (
id VARCHAR(4) PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
prices NUMERIC(6,2),
pid VARCHAR(4),
/为字段pid定义一个外键约束(来自person2表的字段id)/
CONSTRAINT fk_car FOREIGN KEY(pid) REFERENCES person2(id)
)
ALTER TABLE car DROP COLUMN prices;
ALTER TABLE car ADD COLUMN prices NUMERIC(6,2) ;
INSERT INTO car(id ,NAME,prices,pid) VALUES(‘C001’,’BWM’,80.8,’P001’);
INSERT INTO car(id ,NAME,prices,pid) VALUES(‘C002’,’Benz’,200.8,’P001’);
INSERT INTO car(id ,NAME,prices,pid) VALUES(‘C003’,’BWM’,100.8,’P001’);
INSERT INTO car(id ,NAME,prices,pid) VALUES(‘C004’,’Benz’,800.8,’P002’);
INSERT INTO car(id ,NAME,prices,pid) VALUES(‘C005’,’BYD’,8.8,’P002’);
INSERT INTO car(id ,NAME,prices,pid) VALUES(‘C006’,’BIKE’0.8,’P003’);
DELETE FROM person2 WHERE id=’p001’;
DROP TABLE person2;
DROP TABLE car;

3、多对多
数据库设计分析
※案例：一个人可以选择多门课程，一门课程又可以被很多人选择。
方案一(差的设计)：
1)学生表
编号 姓名 性别 年龄 电话 …
P001 Jack 男 25
P002 Tom 男 25

P003 Rose 女 25

2)课程表
编号 名称 教材 学分… 学生
S001 Java … …… P001
S001 Java … …… P002
S001 Java … …… …
S002 数据库 … …… P001
S002 数据库 … …… P002
……

方案二(好的设计:两个实体表+一个关系表)：
1)学生表(独立)—实体
编号 姓名 性别 年龄 电话 …
P001 Jack 男 25
P002 Tom 男 25
P003 Rose 女 25
2)课程表(独立)—实体
编号 名称 教材 学分…
S001 Java … ……
S002 数据库 … ……
S003 XML … ……
3)选课表(专为体现多对多的关系而新增的表)–关系
课程编号 学生编号
S001 P001
S001 P002
…
S002 P002
S002 P003
…
S003 P001
…
外键 外键
| |
|━━━━|
|
联合主键

4) 代码实现

CREATE TABLE stud (
id VARCHAR(5) PRIMARY KEY,
NAME VARCHAR(10) UNIQUE,
age INT
)

CREATE TABLE course(
id VARCHAR(5) PRIMARY KEY,
NAME VARCHAR(10) NOT NULL
)
CREATE TABLE sc(
sid VARCHAR(5),
cid VARCHAR(5)
)
//单独添加约束(注意顺序: 要先添加联合主键，再添加外键)
//创建联合主键
ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);//增加主键
ALTER TABLE sc DROP PRIMARY KEY ;//删除主键。不需要其他参数
ALTER TABLE sc ADD CONSTRAINT fk_stud FOREIGN KEY(sid) REFERENCES stud(id);//添加外键
ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);添加外键
ALTER TABLE sc DROP FOREIGN KEY fk_stud;/删除外键/
ALTER TABLE sc DROP FOREIGN KEY fk_course;//删除外键

INSERT INTO stud(id,NAME,age) VALUES(‘P001’,’小孩’,25);
INSERT INTO stud VALUES(‘P002’,’Jack’,23);
INSERT INTO stud VALUES(‘P003’,’Tom’,24);
INSERT INTO stud VALUES(‘P004’,’张三’,24);
INSERT INTO stud VALUES(‘P005’,’赵子龙’,26);

INSERT INTO course VALUES(‘C001’,’Java’);
INSERT INTO course VALUES(‘C002’,’JavaEE’);
INSERT INTO course VALUES(‘C003’,’XML’);
INSERT INTO course VALUES(‘C004’,’数据库’);
INSERT INTO course VALUES(‘C005’,’JQuery’);

INSERT INTO sc VALUES(‘P001’,’C001’);
INSERT INTO sc VALUES(‘P001’,’C003’);
INSERT INTO sc VALUES(‘P002’,’C001’);
INSERT INTO sc VALUES(‘P002’,’C002’);
INSERT INTO sc VALUES(‘P002’,’C003’);
INSERT INTO sc VALUES(‘P003’,’C001’);
INSERT INTO sc VALUES(‘P004’,’c002’);
INSERT INTO sc VALUES(‘P004’,’c003’);
DELETE FROM sc WHERE sid=’p004’ AND cid=’c003’//能删除
DELETE FROM stud WHERE id=’p004’;//外键约束，不能删除

关联(连接)：左关联(LEFT JOIN) 右关联(RIGHT JOIN) 内关联(INNER JOIN)
mysql不支持: 全关联(FULL JOIN) 外关联(outter JOIN)
//关联，可理解成把几个合成一个新的表，然后在新表中进行查询

//1查询哪些人选了哪些课
– 92标准
SELECT stud.name,stud.age,course.name FROM stud,sc,course WHERE stud.id=sc.sid AND sc.cid=course.id;
– 96标准
SELECT stud.name,stud.age,course.name FROM stud INNER JOIN sc ON stud.id=sc.sid
INNER JOIN course ON course.id=sc.cid ;
//2查询哪些人没有选课
//92标准
SELECT stud.name AS 姓名 FROM stud WHERE stud.id NOT IN (SELECT sc.sid FROM sc);
//96标准
SELECT * FROM stud LEFT JOIN sc ON sc.sid=stud.id LEFT JOIN course ON sc.cid=course.id WHERE sc.cid IS NULL;//左关联
//3查询哪些课程没人选
//96标准–左关联
SELECT course.name FROM course LEFT JOIN sc ON sc.cid=course.id LEFT JOIN stud ON stud.id=sc.sid WHERE stud.name IS NULL;
//96标准–右关联
SELECT course.name FROM stud RIGHT JOIN sc ON stud.id=sc.sid RIGHT JOIN course ON sc.cid=course.id WHERE stud.name IS NULL;

※演示自动增长列与字段值唯一性约束
CREATE TABLE aa(
id INT AUTO_INCREMENT PRIMARY KEY,
nm VARCHAR(32) UNIQUE
);
INSERT INTO aa(nm) VALUES(‘uuu’);
INSERT INTO aa(nm) VALUES(‘2uuu’);

sql增加约束
–主键约束(Primary Key constraint):要求主键列的数据唯一，并且不允许为空。

–唯一约束(Unique Constraint)：要求该列唯一，允许为空，但只能出现一个空值。

–检查约束(Check Constraint):某列取值范围限制、格式限制等，如有关年龄的约束。

–默认约束(Default Constraint):某列的默认值，如我们的男性同学较多，性别默认为男。

–外键约束(Foreign Key):用于在两表之间建立关系需要制定引用主表的哪一列。

语法如下

alter table 表名

add constraint 约束名 约束类型具体的约束说明

示例：

–添加主键约束

alter table stuInfo

add constraint PK_stuNo primary key(stuNo)

–添加唯一键约束

alter table stuInfo

add constraint UQ_stuID unique(stuID)

–添加默认约束

alter table stuInfo

add constraint DF_stuAddress default(‘地址不详’) for stuAddress

–添加检查约束

alter table stuInfo

add constraint CK_stuAge check(stuAge between 15 and 40)

–添加外键约束

alter table stuInfo

add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)

删除约束

alter table 表名 drop constraint 约束名

wangjian_an
关注
专栏目录
Centos7中给Postgresql11安装PostGis_对比Mysql和Postgresql11中Geometry数据_同步Pg空间数据到mysql和hbase---PostGis工作笔记001
添柴程序猿的专栏
06-26 396
1.因为项目中需要在postgresql和mysql中进行同步数据,所有的数据都需要从postgresql中同步到mysql中,那么这个时候,如果postgresql中有空间类型的数据,这个时候,就需要特殊处理,如何把postgresql中的空间类型数据,转换到mysql和hbase中存储。
MySQL学习笔记--C++接口调用
stm32_newlearner的博客
04-12 623
Mysql学习 今天，老大让我搞搞mysql，因此，立贴在一个星期内，把mysql的基本用法及环境弄清楚。 环境安装 参考菜鸟教程，安装目录为basedir=C:\web\mysql-8.0.15； 初始化数据库： A temporary password is generated for root@localhost: EU:oVki;9k-a 控制命令 启动： cd c:/mysql/bin ...
数据库设置表的check约束出现乱码
weixin_33895016的博客
08-16 260
采用默认的方式见了一个数据库，但是有个表里需要建一个check约束。将约束保存之后再打开看到中文成了？？。后来查了一下是数据库排序规则除了问题。 详见两图即可明白： 这里的约束中文显示乱码： 按下图设置数据库即可正常显示： 转载于:https://www.cnblogs.com/zhouxiuquan/p/3261285.html...
向mysql数据库中插入数据时，数据库出现中文乱码问题（windows）
qq_39455664的博客
03-19 960
1、首先win+R，输入cmd，打开命令行窗口 2、转到你存放mysql安装文件的bin目录下 3、（1）输入：net start mysql (2) 输入：mysql -u root -p 再输入你的数据库密码 （3）输入：show variables like 'character%'; 如图所示： mysql> show variables ...
mysql 创建数据表时 字符乱码_mysql 乱码解决办法---创建数据库时指定字符集
weixin_35715629的博客
01-19 224
mysql 创建数据库时指定编码很重要，很多开发者都使用了默认编码，但是我使用的经验来看，制定数据库的编码可以很大程度上避免倒入导出带来的乱码问题。 我们遵循的标准是，数据库，表，字段和页面或文本的编码要统一起来 很多mysql数据库工具(除了phpmyadmin，我偶尔用，功能强速度慢)都不支持创建时指定数据库编码，当然可以改my.ini来解决这个问题,但是需要重新启动mysql，不过用下...
解决MySQL向表中增加数据插入中文乱码问题
cyaya6的博客
07-14 5660
解决MySQL向表中增加数据插入中文乱码问题
MySQl乱码问题解决方式
蔚蓝
08-31 5677
在MySQL 8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0 开始，数据库的默认编码改为 utf8mb4 ，从而避免了上述的乱码问题。mysql是我们项目中非常常用的数据型数据库。但是当我们需要在数据库保存中文字符，就经常会遇到数据库乱码情况。下面就来介绍一下如何彻底解决数据库中文乱码情况...
tedu斌-MySql笔记2112-2
weixin_39344835的博客
03-14 1万+
课程回顾: 数据库相关SQL 查询所有 show databases; 创建 create database db1 charset=utf8/gbk; 查看数据库信息: show create database db1; 删除数据库: drop database db1; 使用: use db1; 表相关SQL 创建表: create table t1(name varchar(20),age int) charset=utf8/gbk; 查询所有: show tables; 查询表信息:
Spring Boot学习笔记（十七）整合 mysql、mybatis-plus 使用redis进行缓存（带工程源码）
TUTsmile的博客
08-19 1228
文章目录1. 给springboot 工程添加pom依赖2. 在application中进行配置3. 启用缓存机制4. 开发缓存注解4.1 编写实体类SpringBootUser4.2 编写mybatis-plus相关文件4.3 编写调用方法 1. 给springboot 工程添加pom依赖 <dependency> <groupId>mysql</groupId> <artifactId>mys
使用MySQL数据库使用CHECK语句约束出现错误
wr456wr的博客
06-24 6428
使用MySQL数据库使用CHECK语句约束出现错误 造成错误的原因未知 问题描述： 创建一个表单使用check语句进行约束，当check语句内的列名为中文时出现错误。 问题情况： mysql> create table a4 ( 问题 char(5) CHECK(问题 ='已解决' or '为解决')); ERROR 1054 (42S22): Unknown column '闂??' in 'check constraint a4_chk_1 expression' 当将列名改为英文的时候未报错
mysql语句解决查询乱码_MySQL中文乱码问题解决方案
weixin_36431157的博客
02-08 331
我就废话不多说了，大家还是直接看代码吧~create or replace function aa1(a1 integer[],a2 bigint) returns void AS $$declare ii integer;declare num integer; begin II:=2021-01-16 17:49:22条件查询-模糊匹配PostgreSQL和SQL Server的模糊匹配lik...
mysql查找所有外键_查询mysql所有外键约束
weixin_29866423的博客
01-20 516
SELECT C.TABLE_SCHEMA 拥有者,C.REFERENCED_TABLE_NAME 父表名称 ,C.REFERENCED_COLUMN_NAME 父表字段 ,C.TABLE_NAME 子表名称,C.COLUMN_NAME 子表字段,C.CONSTRAINT_NAME 约束名,T.TABLE_COMM...
mysql left join 因编码不一致，导致的 Using join buffer (Block Nested Loop)
wodedaxuelv的博客
09-03 1251
mysql left join 导致的 Using join buffer (Block Nested Loop) 描述：mysql做关联时，已经在关联字段上建立了所以，但还是走全表扫描 分析： 查看索引、确实建立的索引，还是全表扫描 定位，原来是坐标用utf8，而右表用了utf8mb4编码，导致索引失效 办法：不想改表结构，但用在SQL中使用转换编码函数时，还是全表扫描，很慢 解决： 只有用alter table t1 convert to charset utf8mb4;才是正确的。...
Mysql字符集设置
weixin_30517001的博客
10-27 465
最近，在项目组使用的mysql数据库中，插入数据出现乱码，关于这个问题做了下总结，我们从最基本的地方说起，到错误产生的深层次原因和解决办法。 基本概念 • 字符(Character)是指人类语言中最小的表义符号。例如’A'、’B'等；• 给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码(Encoding)。例如，我们给字符’A'赋予数值0，给字符’B'赋予...
mysql left 中文_MySql 解决中文乱码问题
weixin_32498069的博客
01-19 328
关于数据库中文乱码问题在 mysql 中输入中文, 乱码, 尝试了修改数据库, 数据表的字符编码集, 都没有用, 最后发现问题如下:修改数据库编码集12345678910111213141516showvariableslike'%char%';+--------------------------+----------------------------+|Variable_name|Value...
MySQL数据库创建表时出现乱码解决方案
m0_51260564的博客
05-01 1833
MySQL数据库创建表时出现乱码解决方案
解决Mysql中文乱码问题（多方面考虑，彻底解决乱码问题）
热门推荐
weixin_48223757的博客
01-10 1万+
前言 今天在做java前后端分离项目的时候遇到了读取mysql中文乱码问题，首先在项目中的要求是通过springboot和mybatis-plus构建访问数据库，后面在通过浏览器访问固定地址读取查询表内容的时候（select * from hospital_set），出现的乱码问题（如下图所示）。 解决办法 可能性分析（1）： 首先出现这种情况，你可能是因为使用了navicat软件，你直接在这个软件上面对创建的表进行了数据填写（如下图）。 因为是在这个软件上面直接填写的，所以你填写中文的格
constraint用法总结
02-06 2247
主要就是增加约束的以下几种约束 、并 一一列举：1.主键约束：要对一个列加主键约束的话，这列就必须要满足的条件就是分空因为主键约束：就是对一个列进行了约束，约束为（非空、不重复）以下是代码  要对一个列加主键，列名为id,表名为emp格式为：alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）例子：alter table emp add
MySQL数据库中文乱码解决：原理与实践
“该资源是一份关于Linux运维的MySQL数据库中文乱码问题的学习笔记，主要探讨了MySQL数据库中文乱码的原理以及提供了相应的解决乱码方案。” 在MySQL数据库中，中文乱码问题通常涉及到字符编码设置不一致的情况。这...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值