Mysql学习笔记（连载中）

邓诗任

Mysql

01 使用CMD连接数据库

1. -- 在控制台连接数据库（需将位置切换到mysql所在地址）
2. mysql -u root -p
3. password:12345
5. -- 修改mysql账户密码及权限，安装配置完后慎用
6. update mysql.user set authentication_string = password('12345') where user = 'root' and Host = 'localhost';-- 修改用户密码
7. flush privileges; -- 刷新权限
9. --------------------------------------------------
10. -- 所有语句需要使用;作为结尾
11. show databases; -- 查看所有数据库
13. use test01; -- 切换数据库 use+数据库名
15. show tables; --查看数据库中所有的表
16. describe `user`; --显示某张表的信息
18. create database test01; -- 创建一个数据库
20. exit; -- 退出连接
22. -- 单行注释
23. -- 下面是多行注释
24. /*
25. hello
26. */

复制代码

• 数据库xxx语言 CRUD 增删改查
  
  
  
  • DDL  定义
    
  • DML  操作
    
  • DQL  查询
    
  • DCL  控制
    
  
  

02 操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字不区分大小写！！
2.1 操作数据库

• 创建数据库
  

1. CREATE DATABASE test01; -- 创建数据库01
2. -- 但问题是如果test01存在，系统会报错
3. -- 所以一般会加入一个判断条件
4. CREATE DATABASE IF NOT EXISTS test01;

复制代码

• 删除数据库
  

1. DROP DATABASE IF EXISTS test01;

复制代码

• 使用数据库
  

1. -- tab键上方有一个``，如果数据库的表名或者字段名是一个特殊字符，就需要添加
2. USE `test01`;

复制代码

• 查询数据库
  

1. SHOW DATABASES; -- 查看所有数据库

复制代码

2.2 数据库列数据类型

数字

• 
  

2.3 数据库字段属性（重要）

• unsigned：
  
  
  
  • 无符号的整数
    
  • 声明了该列不能填充负数
    
  
  
• zerofill：
  
  
  
  • 0填充的
    
  • 不足位数在前面用0来填充，例如int(3),5-->005
    
  
  
• 自增
  
  
  
  • 通常理解为自增，自动在上一条记录的基础上+1（默认）
    
  • 通常用来设计唯一的主键~index，必须是整数类型
    
  • 可以自定义自增步长
    
  • Navicat中Mysql自增设置在选项中
    
  
  

• 非空 NULL  not NULL
  
  
  
  • 假如设置为not  null ，如果不给他赋值就会报错！
    
  • NULL，如果不填写值，默认就是NULL！
    
  
  
• 默认：
  
  
  
  • 设置默认的值
    
  • 例如 sex，默认值为男性，如果没有特别指定该列的值，则会有默认的值！
    
  • 需要注意，在Navicat中，默认值设置，自增，无符号以及填充零都在下方
    
  
  

• 拓展
  

1. /*每一个表都必须存在以下五个字段，未来做项目都有用，表示一个记录存在的意义
2. id 主键
3. is_delete 伪删除
4. gmt_create 创建时间
5. gmt_update 更新时间
6. */

复制代码

2.4 创建数据库表（重点）

1. -- 目标：创建一个school数据库
2. -- 创建学生表（列，字段） 使用SQL语句创建
3. -- 学号int 登陆密码varchar(20) 姓名 性别varchar(2) 出生日期(datetime) 家庭住址 email
4. -- 注意点，使用英文()，表的名称和字段尽可能使用``括起来
5. -- AUTO_INCREMENT 自增
6. -- 字符串使用单引号括起来；
7. -- 所有的语句后面加,(英文的),最后一个不用加
8. -- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
10. CREATE TABLE IF NOT EXISTS `studentinformation`(
11.     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
12.                 `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
13.                 `pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
14.                 `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
15.                 `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
16.                 `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
17.                 `email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
18.                 PRIMARY KEY(`id`)
19. )ENGINE = INNODB DEFAULT CHARSET=utf8

复制代码

格式

1. CREATE TABLE [IF NOT EXISTS] `表名`(
2.     `字段名` 列类型 [属性] [索引][注释],
3.     `字段名` 列类型 [属性] [索引][注释],
4.     ·······
5.     `字段名` 列类型 [属性] [索引][注释]
6. )[表类型][字符集设置][注释]

复制代码

2.5 数据表的类型

1. -- 关于数据库引擎
2. /*
3. INNODB 默认使用~
4. MYISAM 早些年使用
5. */

复制代码

MYISAMINNODB事务支持不支持支持数据行锁定（同步）不支持支持外键约束不支持支持全文索引支持不支持表空间大小较小较大，约为前者2倍常规使用操作：

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

在物理空间的位置

所有的数据库文件都存在data目录下
所以数据库的本质还是文件的存储
Mysql引擎在物理文件上的区别

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  
• MYISAM对应文件
  
  
  
  • *.frm  表结构的定义文件
    
  • *.MYD  数据文件（data）
    
  • *.MYI  索引文件（index）
    
  
  
• 这里有必要提到的是，MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。
  如何可以查看表结构信息，官方提供了一个工具叫做ibd2sdi，在安装目录下可以找到，可以离线的将ibd文件中的冗余存储的sdi信息提取出来，并以json的格式输出到终端。可参考一篇CSDN博客。https://blog.csdn.net/philipslu/article/details/102499234
  

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

1. CHARSET = utf8

复制代码

也可以采取在数据库安装目录下新建一个ini文件的方式进行默认设置。不如不单独设置，系统会默认为一个latin字符集，我的ini文件格式如下：

1. #设置3306端口
2. port=3306
3. #设置mysql的安装目录   ----------是你的文件路径-------------
4. basedir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64
5. #设置mysql数据库的数据的存放目录  ---------是你的文件路径data文件夹自行创建
6. #datadir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64\data
7. #允许最大连接数
8. max_connections=200
9. #允许连接失败的次数。
10. max_connect_errors=10
11. #服务端使用的字符集默认为utf8mb4
12. character-set-server=utf8mb4
13. #创建新表时将使用的默认存储引擎
14. default-storage-engine=INNODB
15. #默认使用“mysql_native_password”插件认证
16. #mysql_native_password
17. default_authentication_plugin=mysql_native_password
18. [mysql]
19. #设置mysql客户端默认字符集
20. default-character-set=utf8mb4
21. [client]
22. #设置mysql客户端连接服务端时默认使用的端口
23. port=3306
24. default-character-set=utf8mb4

复制代码

• 前面有关于文件路径以及data保存路径的设置需要根据实际情况进行调整。后面的端口设置为系统默认的3306。其他相关内容可参考文章：https://blog.csdn.net/weixin_42820850/article/details/129108669
  

2.6 修改删除表

修改

1. -- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
2. ALTER TABLE test01 Rename AS test02
4. -- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
5. ALTER TABLE test02 ADD age INT(10)
7. -- 修改表的字段（重命名，修改约束）
8. -- ALTER TABLE 表名 MODIFY 字段名 列属性[]
9. ALTER TABLE test02 MODIFY age VARCHAR(10)
10. -- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
11. ALTER TABLE test01 CHANGE age age1 INT(1) -- 字段重命名
13. -- 删除表的字段
14. ALTER TABLE test01 DROP age1

复制代码

• 可以观察到，所有对于表的修改都是以ALTER开头的
  

删除

1. -- 删除表(加入判断条件，如果存在再删除)
2. DROP TABLE IF EXISTS test01

复制代码

• 需要注意，所有的创建和删除尽量加上判断，以免出现报错
  

03 MySQL数据管理

3.1 数据库级别外键（了解）

方式一：在创建表的时候，增加外键语句和关联语句等约束（比较复杂）

1. CREATE TABLE IF NOT EXISTS `studentinformation`(
2.     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
3.                 `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
4.                 `pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
5.                 `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
6.                 `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
7.                 `gradeid` INT(3) NOT NULL COMMENT'年级id',
8.                 `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
9.                 `email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
10.                 PRIMARY KEY(`id`),
11.                 KEY `FK_gradeid`(`gradeid`), -- 外键名称设置
12.                 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 关联约束设置
13. )ENGINE = INNODB DEFAULT CHARSET=utf8
16. CREATE TABLE IF NOT EXISTS `grade`(
17.     `gradeid` INT(3) NOT NULL COMMENT'年级id',
18.                 `gradename` VARCHAR(10) NOT NULL COMMENT'年级名称',
19.                 PRIMARY KEY(`gradeid`)
20. )ENGINE = INNODB DEFAULT CHARSET=utf8

复制代码

• 运行结果如下
  

• 可以看到此时由于外键关联关系的存在，原表在删除以及对某些字段内容进行修改时都收到了约束
  
• 删除时需要先删除引用的表，然后再回来删除被引用的表
  

方法二：正如上面所示，可以在外键部分手动添加，这里不做展开，与上面运行结果类似即可

方法三：

1. CREATE TABLE IF NOT EXISTS `grade`(
2.     `gradeid` INT(3) NOT NULL COMMENT'年级id',
3.                 `gradename` VARCHAR(10) NOT NULL COMMENT'年级名称',
4.                 PRIMARY KEY(`gradeid`)
5. )ENGINE = INNODB DEFAULT CHARSET=utf8
8. -- 外部创建外键约束
9. CREATE TABLE IF NOT EXISTS `studentinformation`(
10.     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
11.                 `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
12.                 `pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
13.                 `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
14.                 `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
15.                 `gradeid` INT(3) NOT NULL COMMENT'年级id',
16.                 `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
17.                 `email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
18.                 PRIMARY KEY(`id`)
19. )ENGINE = INNODB DEFAULT CHARSET=utf8
21. -- 创建表的时候没有外键关系
22. -- 外部添加外键约束
23. ALTER TABLE `studentinformation`
24. ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
26. -- 结构
27. ALTER TABLE 表
28. ADD CONSTRAINT 约束名 FOREIGN KEY(`作为外键的列`)

复制代码

• 但需要注意的是，以上的操作均为物理外键，是在数据库级别进行操作的外键，一般不推荐使用。（避免数据库过多造成困扰）
  

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
  
• 如果想使用多张表的关联操作，即使用外键（在程序中实现）
  

参考文章：https://www.cnblogs.com/rjzheng/p/9907304.html
3.2 DML语言（需要全部记住）

数据库意义：数据存储、数据管理
DML语言：数据操作语言

• INSERT
  
• UPDATE
  
• DELETE
  

3.3 添加INSERT

格式

1. -- 插入语句（添加）
2. -- INSERT INTO 表名 (`字段名1`,`字段名2`,`字段名3`) VALUES('第一条数据字段1数据','第一条数据字段2数据','第一条数据字段3数据'),('第二条数据字段1数据','第二条数据字段2数据','第二条数据字段3数据')····
4. -- 例：
5. INSERT INTO `grade`(`gradename`)VALUES('大一')
6. -- 需注意的是，跟在表名后面的括号内容可以选择表中的部分字段，但是需要观察表结构，那些非空且没有默认值的或者没设置自增的字段必须包括在内，否则会报错。还需注意，选定了括号内容后，VALUES后面的括号内的结构需与前面保持一致。如果想一次性添加多条数据，需要在括号中间用“,”隔开。
8. -- 错误示例：
9. INSERT INTO `grade` VALUES('大二')
10. -- 此处在表名`grade`后面没有括号，因此Mysql就会默认格式为全部字段。依照上面所述，数据库会对VALUES后面数据进行匹配。因此会报错。
11. -- 正确示例：
12. INSERT INTO `grade` VALUES(2,'大二')
14. -- 插入多条数据
15. INSERT INTO `grade`(`gradename`)
16. VALUES ('大三'),('大四')

复制代码

• 再次强调，必须注意标点符号的中英文格式！
  

注意事项：

• 字段和字段之间用英文逗号隔开
  
• 字段是可以省略的，但是后面括号内的值需要一一对应，不能少
  
• 可以同时插入多条数据，VALUES后面的值，需要使用隔开即可
  

3.4 修改UPDATE

3.5 删除DELET


来源:https://www.cnblogs.com/wutong666/p/17291722.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！