第四章第四章 数据库和表的操纵

 
 
第四章 数据库和表的操纵
一、创建库、创建表、构造SQL语句的学习环境
1、操纵体系范例
客户端操纵体系：windows 98/windows 2000、windows xp、windows 7\8、windows 10、windows 11
服务器操纵体系：windows server NT、windows server 2000、windows server 2003、 windows server 2008、windows server 2012、windows server 2016、windows server 2019 
2、C/S通信方式
client 客户端:客户端应用程序     eg：网游客户端
server端：为客户提供服务的计算机硬件体系、软件体系   eg：腾讯服务器
3、学习环境
虚拟机：server端
物理机：windows 10或11
4、创建数据库
下令行方式创建数据库
creata database 数据库名  character set utf8 collate utf8_general_ci;
菜单方式创建数据库
选择主机---->右键--->选择新建数据库---->输入数据库名
5.删除数据库
下令行方式删除数据库
 drop database 数据库名；
菜单方式
客户端工具：下令行客户端、navicate.SQL Manager      选择要删除的数据库--->右键---->删除
6、数据库中创建表
下令行方式
 creata table 表名（字段名1  范例  （宽度），字段名2  范例  （宽度），......）engine =innoDB default
 
二、字符集、排序规则（字符序）
1、mysql 卸载 （针对使用设置方式安装mysql 8.0的情形，而非使用领导方式的安装）
制止mysql 的服务：  net stop MySQL     、 win+R--->service.msc--->选择MySQL-->右键---->制止
删除MySQL服务 ：  win+R--->cmd--->sc delete mysql
删除安装目录
清理注册表：  win+R--->regedit--->注册表
2、MySQL连接方式
3、准备数据库、表、表记录
 
 
 
2、准备知识
- 字符  character    eg：abcd  1234  , . (   )
- 字符集合：charset  一组字符     
  Ascii字符集合(7bit，最高位是0),标准的----可打印的  
   
  扩展Ascii字符集合（8bit）  拉丁语系，扩展----不可打印的字符  
   
  GB2312   简体中文
  BIG5   繁体中文
  GBK   包含简体中文和繁体中文
- 字符编码  给字符集合中的每1个字符指定1个二进制数字来标识。
- 字符集：字符集合+编码=字符集
- 字符序：字符的排序规则   1个字符集，可以有多个排序规则即多个字符序
以_ci结尾，大小写不敏感，不区分大小写
以_cs结尾,大小写敏感，区分大小写
3、常见的字符集
- Ascii字符集
- 扩展Ascii字符集   latin1   8位二进制  包括Ascii字符集中的全部字符
- GB2312  BIG5  GBK    16位二进制 
- unicode  字符集  全球语言   16位二进制
- 扩展Ascii字符集  latin1  latin2
- unicode字符集    unicode编码   一个字符2个字节
                             utf8    一个英文字符  占一个字节
                                      一个中文字符使3个字节
- utf8mb3   utf8mb4    more bit
- utf-8是一种变长的字节编码方式，对于某一个字符的utf-8编码，假如只有一个字节则其最高位二进制位为0；假如是多字节，其第一个字符节从最高位开始，一连的二进制位值为1的个数决定了其编码的位数，其余各字节均以10开头，utf8最多可以用到6个字节。
示例：
1字节   0xxx xxxx
2字节   110x xxxx 10xx xxxx
3字节   1110 xxxx 10xx xxxx 10xx xxxx
4字节    1111 0xxx 10xx xxxx 10xx xxxx 10xx xxxx
5字节    1111 10xx 10xx xxxx 10xx xxxx 10xx xxxx 10xx xxxx
6字节    1111 110x  10xx xxxx 10xx xxxx 10xx xxxx 10xx xxxx 10xx xxxx
刘
1110 0101 1000 1000 1001 1010
E58896
A
0100 0001
41
示例：
新建文本文件，使用utf-8编码来保存，验证字符的字节数。
GBK字符集，一个英文占1个字节，一个汉字占2个字节。
 
 
 
SQL先容
SQL是访问和操纵数据库的标准语言。Structure query language。
SQL能做什么

• SQL可以对数据库执行查询
  
• SQL可以从数据库中检索数据
  
• SQL可以给数据库表插入记录
  
• SQL可以更新数据库表的记录
  
• SQL可以从数据库表中删除记录
  
• SQL可以创建新的数据
  
• SQL可以在数据库中创建新表
  
• SQL可以在数据库创建存储过程
  
• SQL可以在数据库中创建视图
  
• SQL可以设置表、过程和视图的权限
  

数据的元操纵
CRUD操纵

• C:create 创建数据
  
• R:retrieve/read 获取数据/读取数据
  
• U:update/alter 更新数据/修改数据
  
• D:delete/drop删除数据
  

 
数据库的下令
先容数据库的先容数据库的CRUD操纵
检察所有数据库
语法
show databases；
创建数据库——C操纵
语法
create database数据库名 charset 字符集；
切换数据库——R操纵
语法
use 数据库名；
检察当前所选数据库—R操纵
语法
select database（）；
修改数据库——U操纵
语法
alter database 数据库名 [default] charcter set 字符集 [default] collate 字符序；
示例：
alter database db_test default character set gb2312 default collate gb2312_Chinese_ci；
检察数据库编码检察数据库编码
语法
show create database 数据库名；
检察mysql支持的所有编码范例
语法
show character set；
删除数据库——D操纵
语法
drop database 数据库名；
表下令
1、检察所有表
show tables;
2、 创建表
  前提：切换数据库，需要明确在哪个数据库中新建表
CREATE TABLE table_name (  -- table_name 表名   特别阐明：数据库名、表名都应该有一意义
column1_name data_type constraints, -- 列名   范例   属性
column2_name data_type constraints,
   ....
)ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 存储引擎   字符集
3、示例：
    创建一个用户表
CREATE TABLE users ( -- 在DB数据库下新建一个user表
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL, -- null值 表示是一个不确定的值
birth_date DATE,
phone VARCHAR(11) NOT NULL UNIQUE
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
主键：primary key  在一个表中至少应该有如许一个字段，这个字段所对应的值一不能有重复，二不能为空，这个字段所对应的值能够起到唯一标识表中每一条记录的作用。
上面的语句创建了一个名为users的表，其中包含5个字段id、name、birth_date和phone。注意，每个字段背面都有一个数据范例声明，表示该字段将存储何种范例的数据，例如：整数、字符串、日期等。
 
MySQL支持的常用数据范例
 

• 数字型
  

数据范例	宇节	范围(无符号)unsigned	范围(有符号)signed(默认)	描述
tinyint	1	（0,225）	(-128， 127)	小整数值
decimal	2	（0,65535）	(-32768，32767)	大整数值
mediumint	3	(0，16777215)	(-8 388 608, 8 388 607)	大整数值
int	4	(0，4294967 295)	(-2 147 483 648, 2 147 483647)	大整数值
bigint	8	(0，18 446744 073709 551615)	(-9,223.372.036,854.775.8089 223 372 036 854 775 807)	极大整教值
FLOAT(M,D)	4	0.(1.175494 351 E-38，3.402823 466E+38)	(-3.402 823 466 E+38，-1.175494 351 E-38).0，(1.175 494351 E-38, 3.402 823 466 351E+38)	单精度浮点数值M是数字总个数，D是小数点后个数
double(M,D)	8	0，（2.225073 858 507 201 4E-308,1.797 693 134 862 315 7E+308)	(1.797 693 134 862 315 7E+308,-2.225 073 858 507201 4E-308),0，(2.225 073858 507 201 4 E-308, 1.797693 134 862 315 7 E+308)	双精度浮点数值M是数字总个数，D是小数点后个数
decimal(M,D)	对 DECIMAL(M,D)假如M>D 为M+2否则为 D+2	依赖于M和D的值	依赖于M和D的值	M是表示有用数字数的精度。M 范围为1一65 。D 是表示 小数点后的位数。D的范围是0~30 MySQL要求 D 小于或够于(<=)M。

（一）整型
1.tinyint 示例：性别  男1 女0   数据范例选择tinyint
2.decimal 示例：价格 与财务有关的字段设置为该范例
3.mediumint 示例：博客访问数设置为该范例
4.int  示例：员工的编号、门生学号设置为该
5. bigint 示例：网站访问数设置为该类
（二）小数

• float 示例：float（5.2） 范園（-999.99,999.99）桌子的长度、物理测理的度量值
  
• double 示例：偏差比float低  科学计算、物理丈量的值
  
• decimal 示例：decimal（7.2） M=7（包含整数部门和小数部门，小数点也要算作1位）D=2小数点后数字的位数 如某一商品的价格是¥1234.56
  
•  
  

单精度和双精度差别
1、在内存中占有的字节数差别
2、有用数字位数差别
3、所能表示数的范国差别
 

• 字符串
  

范例	字节	阐明
char	0-255字节	定长字符串
varchar	0-65535字节	变长字符串
text	0-65535字节	长文本数据

• 日期
  

范例	大小	范围	格式	阐明
date	3	1000.01.01/9999.12.31	YY-MM.DD	日期
time	3	'838:59:59/838:59:59'	HH:MM:SS	时间
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YY-MM.DDHH:MM:SS	日期时回
year	1	1901/2155	YYYY	年份范例

char（50）宽度是固定，若占不完，空格补齐，查询效率高，浪费存储空间
varchar（50）宽度是自动的，用多少宽度分配多少宽度，查询效率低，节约空间
text 长文本范例 示例：简历text范例
 
MySQL支持的常用约束

修饰符	描述
primary key	主键
not null	非空
unique	唯一
default	默认
foreign key	外键

主键: 门生表中的学号、身份证号;课程表中的课程号;在一个表中至少应该有如许一个字段，这个字段所对应的值一不能有重复，二不能有空间，它能够起到唯一标识表中记录的作用。
not null 不能空值  示例:某个商品的价格是nul，只能说这个商品价格尚未确定，不是免费的。
unique: 满足主键条件的字段 设置为unique的字段，此字段所对应的值是不能有重复
foreign key外键:在一个表中某一个字段它不是当前这个表的主关键字，但是它却是另外一个表的主关键，我们在当前这个表，把此字段称为是当前表的外部关键字。
 
存储引擎
MySQL中的数据用各种差别的技术存储在文件(大概内存)中。这些技术中的每一种技术都使用差别的存储机制、索引技巧、锁定程度而且终极提供广泛的差别的功能和能力。通过选择差别的技术，你能够获得额外的速度大概功能，从而改善你的应用的团体功能。
存储引擎:就是我们对数据进行增、制、改、查或CRUD操纵的一种技术方式。
show engines;
检察表布局
desc 表名;
检察表的创建语句
show create table"表名";
更改表名称
rename table 原表名 to 新表名；
修改表
alter table 表名 add|changeldrop 列名范例;
更新约束
先创建一个表,除了主键，不加其他约束,
CREATE TABLE USerS1(
Id INT NOT NULL PRIMARY KEY AUTO INCREHENT.
name VARCHAR(50)，
birth_date DATE,
phone VARCHAR(11)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
给手机号添加唯一约束
ALTER TABLE Users1 ADD UNIQUE(phone);
删除唯一约束
ALTER TABLE USerS1 DROP INDEx phone;
给名字添加非空约束
ALTER TABLE uSers1 modify name VARCHAR(50) not null;
制除非空约束
ALTER TABLE users1
CHANGE COLUNn'name''name'VARCHAR(5O)NULL;
给生日添加默认约束
ALTER TABLE USerS1 ALTER birth_date SET DEFAULT '1992-05-11';
删除默认约束
插入数据
语法:
insert into 表名(字段名1,字段名2,…)values(值1,值2,….);

• 缺省插入
  

示例:
给user表新增1条记录?
insert into user(name,birthday,phone,age)values('老王','1954-10-5','13012345678',70);

• 全列插入
  

给user表再新增1条记录
insert into user values(1000,'老张','1964-10-7',13112345678,60);
insert into user values(0,'老李','1974-10-7',13212345678,50);
阐明:
给表中所有字段插入记录的时候，id自动增长型主键的值从0开始。
差别：
主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入乐成后以现实数据为准
查询数据
语法:
select*from 表名;-- 检察表记录
验证float、double、 decimal偏差
、、、新建测试表 tbl test
create table tbl test(
id int not null primary key auto_increment,
price1 float(9,3)not null,price2 double(9,3)nod null,
price3 decimal(9,3)not null
)engine=innodb default charset=utf8;
SQL语句
准备数据
阐明:
在schoolDB数据库下，在3张表:门生表tstudent、分数表tscore.课程表tsubject
 
   
一、简单查询
1、查询所有字段

• 可以代表所有字段
  
• 示例:
  

查询门生表中的所有门生信息?
select studentid,sname,sex,cardid,birthday,email,class.entertime
from tstudent;
from tstudent;
回忆:
投影:从源表中选择符合条件的列(字段、属性)
选择:从源表中选择符合条件的行(记录、元组)
2、查询指定字段

• 查询单个字段
  
• 查询多个字段
  

示例:
查询门生表中的姓名、性别、邮箱?
select sname,sex,email from tstudent;--投影操纵
3、查询指定记录
查询满足条件的元组(记录)，使用where子句
示例:
查询班级是java开发的门生的姓名、性别、邮箱和专业班级?
select sname,sex,email,class
from tstudent
where class='java开发';--投影 操纵
4、带in关键字的查询
示例:
查询专业班级为java开发、大数据和网络与网站开发的门生的信息?
select *from tstudent
where class in('java开发','大数据','网络与网站开发');
select *from tstudent
where class='java开发'or class='大数据'or class='网络语网络开发';
5、带between .. and ..关键字的范围查询
示例:
查询1983年至1987年之间出生的所有门生的信息?
select*from tstudent
where birthday >='1983-1-1'and birthday<='1987-12-31';
select *from tstudent
where birthday between
'1983-1-1'and '1987-12-31';
6、like关键字
含糊査询
通配符:
表示恣意1个字符
%表示恣意多个字符
示例:
查询门生表中所有姓“王”的门生的信息?
select *from tstudent
where sname like'雷%';
查询姓名中有“军”这个字的门生的信息?
select *from tstudent
where sname like '%军%';
查询1985年出生的门生的信息?
select * from tstudent where birthday like '1985%';
7、空值查询
空值:nul值 不确定的值 空值不是0，也不是空字符串示例:查询奖金或补助为空值的雇员信息?
select*from emp where comm is null;
select * from emp where comm is not null;
8、and 而且
示例:
查询学号小于18的，且性别为男的，而且专业班级为java开发的的信息。
select*from tstudent
where studentid<18 and sex='男'and class='java开发';
9、or 大概
示例:
查询姓名中含有'生”字的，或专业班级为'大数据”的门生
select*from tstudent
where sname like'%生%'or class ='大数据';
 
select sname,sex,birthday,class
from tstudent
where class ='大数据'or class='.net'or class='java开发';
select sname,sex,birthday,class
from tstudent
where class in('大数据','.net','java开发');
10、去掉重复值
distinct 去掉重复值
示例:查询门生表的专业班级有哪些?
select distinct classfrom tstudent;
11、使用limit限制查询结果的数目
limit[位置偏移量,]行数
功能:返回指定位置的指定条数条记录
示例:查询门生表中的前5名门生的信息?
select*from tstudent limit 5;
select*from tstudent limit 4,6;-- 从第4+1条的位置开始取,取6条记录
 
12、union合并查询结果
union集合的合并
要求第一个SQL语句返回的列和第二条SQL语句返回的列数相同
select studentid,sname,sex from tstudent where studentid=00013
union
select studentid,sname,sex from tstudent where sname like '雷%'；
13、为表和字段取别名
字段名[as]别名
select studentid as学号,sname as 姓名,birthday as出生日期
from tstudent;
表名 [as] 别名
select s.studentid 学号,s.sname as 姓名,s.birthday as 出生日期
from tstudent as s;
小结:
用户感兴趣的数据来自于1个表
语法:
select 字段名1 as 别名1,字段名2 别名2,…
from 表名[as]别名
where 条件
连接查询
1、内连接查询
inner join
   
 

• 父表:主关键字所在的表
  
• 子表:外部关键字所在的表
  
• 主关键字:门生表中的学号在一个表中至少应该有1个字段，这个字段所对应的值一不能有重复、二不能是空值，它能起到唯一标识这一条记录的作用
  
• 外部关键字:结果表中的学号 在某一张表中某个字段它不是当前这个表的主关键字，但是它是另外一个表的主关键字。
  

注意:门生表与结果表的关联字段 学号
示例:
查询门生的学号、姓名、科目和结果?
select s.studentid,s.sname,sub.subjectname,sc.mark
from tstudent s,tscore sc,tsubject sub
where s.studentid=sc.studentid andsc.subjectid=sub.subjectid;
补充:
SQL99标准语法:
表1 inner join 表2 on 连接条件
示例:
查询门生的学号、姓名、科目和结果?
select s.studentid,s.sname,sub.subjectname,sub.subjectid,sc.markfrom tstudent
 as s inner join tscore as sc on s.studentidsc.studentid
  inner join tsubject as sub on sc.subjectid=sub.subjectid;
连接查询：去掉重复属性的等值相连。
提示：“两个表都有的字段，一般要保存父表的”
2、外连接查询

• 左连接(以左边的表为主)
  
•    
  

• 右链接（以右边的表为主）
  

 
   
 
--左连接
select s.*,sc.*
from tstudent s left join tscore sc
on s.studentid=sc.studentid;
---右连接
select s.*,sc.*
from tstudent s left join tscore sc
on s.studentid+sc.studentid;
 
3、自连接查询
 
   
插入15000条记录
call addStudent(15000);
查询门生表中同名的门生的学号和姓名？----使用自连接查询
select a.studentid,a.sname,b.studentid,b.sname
from tstudent a inner join tstudent b
on a.sname=b.sname
where a.studentid<>b.studentid;
 
 
select a.studentid,a.sname,b.studentid,b.sname
from tstudent a,tstudent b
where a.sname=b.sname and a,studentid<>b.studentid;
 
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。