数据库(未完成)

声明:以下笔记来源于韩顺平视频https://www.bilibili.com/video/BV1fh411y7R8?p=1，笔记为楼主亲手劳动，劳动不易，转载请标明谢谢。

数据库

[

注释快捷键 ctrl + shift + C，取消注释 ctrl + shift + R 在 SQLyog 中

ctrl + / 注释 在Navicat中

]

mysql的安装

1. 732 https://www.bilibili.com/video/BV1fh411y7R8?p=732&spm_id_from=pageDriver

2. mysql5.5 mysql5.6 mysqI5.7 (稳定) mysqI8 更高版本

3. 安装 https://dev.mysql.com/downloads/mysql/

4. 注意点

   • 1. MYSQL5.5 及之前默认生成my.ini文件,用于配置如端口号字符集以及目录等，但MYSQL5.7 后要手动安装，但MySQL8之后并不需要my.ini,会自动的生成data文件夹在解压之 后的文件，端口默认3306,。若有这个文件，则初始化mysq不成功。
     2. 自己若新建并设置了my.ini文件，有data文件的话， 在初始化之前要删除。然后再初始化
     3. 在初始化之后会自动生成密码，要记下来，后续登上mysq|需改密码之后才可后续操作。
     4. 要更改加密规则(就是修改密码)，不然无法使用工具连接mysq|

5. 参考教程

   • https://blog.csdn.net/weixin_40034633/article/details/106932982?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164835814616780261924053%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=164835814616780261924053&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-1-106932982.142^v5^control,143^v6^control&utm_term=MySQL%E5%8D%B8%E8%BD%BD&spm=1018.2226.3001.4187
     

6. 初始化mysql(一定要记住随机生成的用户root以及密码)

   • mysqld --initialize --console
     

7. 安装 mysqld 服务

   • mysqld --install
     

8. 开启/关闭服务

   • net start mysql
     net stop mysql
     

命令行连接到MySQL

1. 登录前保证服务启动

2. 连接到Mysq|服务(Mysq|数据库)的指令

   • mysq| -h主机IP -P端口-u 用户名-p密码

3. 注意事项

   • 1. -p密码不要有空格
     2. -p后面没有写密码，回车会要求输入密码
     3. 如果没有写-h主机，默认就是本机
     4. 如果没有写-P端口，
     5. 默认就是3306
     6. 在实际工作中3306一般修改(3306太过普遍，容易遭黑客攻击)

图形化工具

1. Naxicat

   • 安装教程

     https://blog.csdn.net/weixin_45764675/article/details/107882887?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164833700416782248576295%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=164833700416782248576295&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-3-107882887.142^v5^control,143^v6^control&utm_term=Navicat&spm=1018.2226.3001.4187
     

2. SQLyog

   • 下载地址

     https://sqlyog.en.softonic.com/
     

   • 问题

     • 1. SQLyog连接8.0以上的MySQL提示2058错误

   • 解决

     https://blog.csdn.net/kongsuhongbaby/article/details/84671909?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164836555716782184614365%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=164836555716782184614365&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-1-84671909.142^v5^control,143^v6^control&utm_term=sqlyog%E8%BF%9E%E6%8E%A5mysql%E9%94%99%E8%AF%AF%E7%A0%812058&spm=1018.2226.3001.4187
     

     ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
     #修改密码
     

数据库三层结构

1. 所谓安装Mysq|数据库，就是在主机安装一个数据库管理系统(DBMS)， 这个管理程序可以管理多个数据库。DBMS(database manage system)

2. 一个数据库中可以创建多个表，以保存数据(信息)。

3. 数据库管理系统(DBMS)、数据库和表的关系如图所示:

   • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jfj3ewBq-1648481828448)(D:/Typora%20Data/Img/image-20220327110026933.png)]

创建数据库

1. CREATE DATABASE [IF NOT EXISTS] db_ name
   [create_ specification [, create_ specification] ..]
   create_ specification: ;
   [DEFAULT] CHARACTER SET charset_ name
   [DEFAULT] COLLATE collation_ name
   

2. 1. CHARACTER SET:指定数据库采用的字符集，如果不指定字符集,默认utf8

   2. COLLATE:指定数据库字符集的校对规则(常用的utf8_bin [区分大小写]

      utf8_ general_ci [不区分大小写] (注意默认是utf8_ general_ci)

   3. CREATE DATABASE dp01
      CREATE DATABASE dp02 CHARACTER SET utf8
      CREATE DATABASE dp03 CHARACTER SET utf8 COLLATE utf8_bin
      CREATE DATABASE dp04 CHARACTER SET utf8 COLLATE utf8_general_ci
      
      DROP DATABASE dp01
      DROP DATABASE dp02
      DROP DATABASE dp03
      DROP DATABASE dp04
      

查询数据库

1. 查看当前数据库服务器中的所有数据库

2. 查看前面创建的book数据库的定义信息

3. 在创建数据库/表的时候，为了规避关键字，可以使用反引号``解决

4. 删除前面创建的book数据库

5. SHOW DATABASES
   
   SHOW CREATE DATABASE `book`
   
   DROP DATABASE `book`
   

备份恢复数据库

1. 备份数据库(注意:在DOS执行)命令行

   • mysqldump -u用户名-Pr -B数据库1数据库2数据库n >文件名.sql

   • mysqldump -u root -p -B book educ > d:\\test\\bak.sql
     

2. 恢复数据库(注意:进入SQLyog再执行,就是进入数据库中，而不是在命令行上)

   • 1. Source 文件名.sql
     2. 或者直接复制bak.sql里面的内容到图形化工具Navicat或SQLyog中执行

   • source d:\\test\\bak.sql
     

3. 备份数据库中某一/多张指定的表

   • mysqldump -u用户名-p密码 数据库表1 表2 表n > d:\ \文件名.sqI(少了个 -B)

   • mysqldump -u root -p123 book adminstor > d:\\test\\bak.sql
     

创建表

CREATE TABLE table name
(
field1 datatype,
field2 datatype,
field3 datatype
)character set字符集
collate校对规则engine存储引擎
field:指定列名
datatype:指定列类型(字段类型)
character set :如不指定则为所在数据库字符集
collate:如不指定则为所在数据库校对规则
engine:引擎(这个涉及内容较多，后面单独讲解)

CREATE TABLE `user` (
`id` INT,
`name` VARCHAR(255),
`password` VARCHAR(32),
`birthday` DATE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

修改表

1. 添加列

   • ALTER TABLE tablename ADD
     (column data type [DEFAULT expr]
     [， column datatype] . ..) ;
     

2. 修改列

   • ALTER TABLE tablename MODIFY
     ( column data type [DEFAULT expr]
     [，(column datatype] ...
     );
     

3. 删除列

   • ALTER TABLE tablename DROP (column) ;
     

4. 修改表名 【Rename table 表名 to 新表名】

5. 修改表的字符集 【alter table 表名 character set 字符集】

6. 查看表的结构 【desc表名; --可以查看表的列】

7. -- 员工表emp的上增加一一个image列， varchar类型(要求在resume后面)
   ALTER TABLE `emp` 
   	ADD `image` VARCHAR(60)
   	NOT NULL DEFAULT ''
   	AFTER `resume`;
   
   -- 修改job列，使其长度为60。
   ALTER TABLE `emp` 
   	MODIFY `job` VARCHAR(60)
   	NOT NULL DEFAULT '';
   
   -- 删除sex列。
   ALTER TABLE `emp`
   	DROP `sex`;
   
   -- 表名改为employee.
   RENAME TABLE `emp` TO `employee`;
   
   -- 修改表的字符集为utf8
   ALTER TABLE `employee` CHARACTER SET utf8;
   
   -- 列名name修改为user_ name
   ALTER TABLE `employee` CHANGE `name` `user_name` VARCHAR(32) NOT NULL DEFAULT '';
   
   desc `emp`;
   desc `employee`;
   
   
   

mysql 常用数据类型

[使用规范:在能够满足需求的情况下，尽量选择占用空间小的类型]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zDCKZyJS-1648481828451)(D:/Typora%20Data/Img/image-20220328093256572.png)]

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期与时间

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符窜类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

bit 列类型的使用

1. bit(m) m在1-64

2. 添加数据范围按照你给的位数来确定，比如m=8表示一一个字节0~255

3. 显示按照bit

4. 查询时，仍然可以按照数来查询

5. 细节说

   • 1. bit字段显示时，按照位的方式显示,查询的时候仍然可以用使用添加的数值,如果-一个值只有0, 1可以考虑使用bit(1) ,可以节约空间位类型。M指定位数，默认值1,范围1-64使用不多.

6. CREATE TABLE `t05` (
   num BIT(8)
   )
   INSERT INTO `t05` VALUES(1)
   SELECT * FROM t05;
   

float/double/decimal 小数列类型

1. FLOAT/DOUBLE [UNSIGNED] Float单精度精度，Double 双精度.

2. DECIMAL[M,D] [UNSIGNED]

   • 1. 可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。
     2. 如果D是0,则值没有小数点或分数部分。M最大65。D最大是30。
     3. 如果D被省略，认是0。如果M被省略，默认是10。
     4. 建议:如果希望小数的精度高，推荐使用decimal

3. CREATE TABLE `t06` (
   num1 FLOAT,
   num2 DOUBLE,
   num3 DECIMAL(30,20)
   )
   INSERT INTO `t06` VALUES(88.12345678912345, 88.12345678912345, 88.12345678912345)
   SELECT * FROM `t06`;
   

char 与 varchar 字符窜列类型

1. CHAR(size) 固定长度字符串最大255 字符

2. VARCHAR(size) 0~65535 字节

3. 可变长度字符串最大65532字节 [ utf8编码最大21844字符 1-3个字节用于记录大小]

4. 如果表的编码是utf8 varchar (size),size = (65535-3) / 3 = 21844

5. 如果表的编码是gbk varchar (size),size = (65535-3) / 2 = 32766

6. CREATE TABLE `t09` (
   `name` CHAR(255)
   );
   
   CREATE TABLE `t10` (
   `name` VARCHAR(21844)
   );
   
   CREATE TABLE `t11` (
   `name` VARCHAR(32766)
   )CHARSET gbk;
   

char 与 varchar 使用细节

细节一

1. char(4) //这个4表示字符数(最大255)，不是字节数,不管是中文还是字母都是放四个按字符计算.

2. varchar(4)//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

3. 不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.

4. CREATE TABLE `t12` (
   `name` CHAR(4)
   );
   
   INSERT INTO `t12` VALUES('师徒关服')
   INSERT INTO `t12` VALUES('abcd')
   
   CREATE TABLE `t13` (
   `name` VARCHAR(4)
   );
   
   INSERT INTO `t13` VALUES('师徒关服')
   INSERT INTO `t13` VALUES('abcd')
   

细节二

1. char(4)是定长(固定的大小)，就是说，即使你插入’aa’ ,也会占用分配的4个字符的空间.
2. varchar(4)是变长(变化的大小)，就是说，如果你插入了’aa’,实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(但varchar本身还需要占用1-3个字节来记录存放内容长度) L(实际数据大小) + (1-3)字节

细节三

1. 什么时候使用char ,什么时候使用varchar
   1. 如果数据是定长，推荐使用char,比如 md5 的密码,邮编，手
      机号，身份证号码等. char(32)
   2. 如果-一个字段的长度是不确定，我们使用varchar ,比如留
      言文章
   3. 查询速度: char > varchar

细节四

1. 在存放文本时，也可以使用Text数据类型.可以将 TEXT 列视为 VARCHAR 并列，注意Text不能有默认值.大小 0-2^16 字节,如果希望存放更多字符，可以选择 MEDIUMTEXT 0-2^24 或者 LONGTEXT 0~2^32

2. text 类型与 varchar 存储相比有灵活变化，比如存数字会用到字节，varchar不会，所以text 更利于空间利用。

3. CREATE TABLE `t14` (
    `content` TEXT,
    `content1` MEDIUMTEXT,\
    `content2` LONGTEXT
   );
   

charset 与 character set 的区别

1. mysql_query(“set names charset utf8”) 设置客户端字符集为UTF-8

2. mysql_query(“set character set utf8”) 设置连接字符集

3. 主要区别设置的范围不同！

   • 前者包含：client | result | connection

   • 后者包影响的就：connection

日期类型

1. 基本用法

2. CREATE TABLE birthday6
   ( 
    t1 DATE, 
    t2 DATETIME,
    t3 TIMESTAMP
    NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP 
   ) ; timestamp 时间戳
   

   CREATE TABLE t01 (
    `birthday` DATE,
    `job_time` DATETIME,
    `login_time` TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
   );
   
   INSERT INTO t01(birthday, job_time) VALUES('2022-11-11', '2022-11-11 10:10:10')
   INSERT INTO t01(birthday, job_time) VALUES('2022-12-12', '2022-11-11 10:10:10')
   
   SELECT * FROM t01;
   

3. 自动更新只在你更新的那一条自动更新

练习

CREATE TABLE `emp` (
	id INT,
	`name` VARCHAR(32),
	`sex` char(1),
	`birthday` DATE,
	`entry_time` DATETIME,
	`job` VARCHAR(32),
	`Salary` DOUBLE,
	`resume` TEXT 
)CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;


INSERT INTO `emp` VALUES(1, '小妖怪', '男', '2022-11-11',
'2022-11-11 10:10:10',
'上山打老虎', 150000.225, '大王叫我来巡山')

SELECT * FROM `emp`;