时间戳

首页 > 技术文章 > 01-mysql基础

01-mysql基础

xsyw 2021-10-12 10:20 原文

初识MySQL

数据库是所有软件体系中最核心的存在

数据库（DB，DataBase）

概念：数据仓库，作为软件安装在操作系统之上（Window，Linux，mac...）

数据库的分类：

关系型数据库：（SQL）

  • MySQL，Oracle，Sql Server，DB2，SQLlite
  • 通过表与表之间行与列之间的关系进行数据的存储

非关系型数据库：（NoSQL）Not Only

  • Redis，MongDB
  • 非关系型数据库，对象存储，通过对象的自身属性来决定

DBMS（数据库管理系统）

  • 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
  • MySQL是当今最流行的关系型数据库管理系统（RDBMS，Relational Database Management System）应用软件之一

安装Navicat Premium 可视化软件。

使用Navicat Premium

1.打开连接数据库

2.新建一个数据库school

image-20211009142137718

可通过历史日志查看到创建数据库的语句：

CREATE DATABASE `school` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

3.新建一张表student

image-20211009143141288

image-20211009143937892

4.打开表查看

image-20211009144244054

5.往表中添加数据

image-20211009145441109

命令行操作数据库

mysql -u root -p -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';	-- 修改用户密码
flush privileges;	-- 刷新权限
--------------------------------------------------------------------
-- 所有语句都使用;结尾 
show databases;	-- 查看所有的数据库

mysql> use school;	-- 使用school数据库
Database changed

mysql> show tables;	-- 查看数据库中所有的表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.01 sec)

mysql> describe student;	-- 显示数据库中所有的表的信息
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int          | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

create database databaseName;	-- 创建一个新的数据库

exit；	-- 退出连接

操作数据库

1.创建数据库

CREATE DATABASE demo;

image-20211009152615416

2.使用数据库

USE demo;

image-20211009152641030

3.查看所有的数据库

SHOW DATABASES;

image-20211009152704010

4.删除数据库

DROP DATABASE demo;

image-20211009152735841

列的数据类型

数值

  • int 标准的整数 4个字节
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数（精度问题） 8个字节
  • decimal 字符串形式的浮点数 金融计算的时候使用

字符串

  • char 字符串固定大小 0~255
  • varchar 可变字符串 0~65535
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 保存大文本

时间日期

java.util.Date

  • date YYYY-MM-DD 日期格式
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳
  • year 年份表示

NULL

  • 没有值，未知
  • 注意，不要使用NULL进行运算，结果为NULL

数据库的字段属性

Unsigned：无符号的整数，声明了该列不能声明为负数

zerofill：不足的位数，使用0来填充

自增：在上一条记录的基础上+1（默认），可自定义主键自增的起始值和步长

非空（not null）：如果不赋值，就会报错

每一个表都必须存在以下五个字段！
id		主键
version			乐观锁
is_delete		 伪删除
gmt_create	 创建时间
gmt_update	修改时间

创建数据库表

-- 使用SQL语句创建一个school数据库
-- 创建一个学生表
-- 学号int 密码varchar(20) 姓名，性别varchar(2)，出生日期(datetime)，家庭地址，email

-- 使用英文(),表的名称和字段尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串用单引号 '' 括起来
-- 所有的语句后面加 , 
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键

CREATE DATABASE school;
USE school;
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATETIME  DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] '注释',
    ......
    `字段名` 列类型 [属性] [索引] '注释'
    PRIMARY KEY (`字段名`)	-- 设置唯一主键
)[存储引擎][字符编码]

查看SQL语句

SHOW CREATE DATABASE school;	-- 查看创建数据库的语句
SHOW CREATE TABLE student;	-- 查看student数据表的定义语句
DESC student;	-- 查看表的具体结构

image-20211011092024619

数据表的类型

关于数据库引擎：

INNODB 默认使用

MYISAM 早些年使用

MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下

image-20211011093632155

本质上还是文件的存储！

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

CHAESET=utf8

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

修改删除表

-- 修改表	ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE student RENAME AS student1;

image-20211011100129142

-- 增加表的字段	ALTER TABLE 表名 ADD 字段名 列属性;
ALTER TABLE student1 ADD age INT(11);

image-20211011100545237

-- 修改表的字段（重命名，修改约束）
ALTER TABLE student1 MODIFY age VARCHAR(11);
ALTER TABLE student1 CHANGE age age1 VARCHAR(11);

image-20211011101506936

注意：change 可以重命名列名，也可能修改列的数据类型，而modify只能修改列的数据类型。如果要用change修改字段类型和约束,要加上新字段名，新字段名可以和原来一样。

-- 删除表的字段
ALTER TABLE student1 DROP age1;
-- 删除表
DROP TABLE IF EXISTS student1;

MySQL数据管理

外键(了解即可)

CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 引用

CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATETIME  DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		`gradeid` INT(10) NOT NULL COMMENT '年级',
		PRIMARY KEY (`id`),
-- 建表时添加外键约束
		KEY `FK_gradeid` (`gradeid`),
		CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB CHARSET=utf8

-- 建表后添加外键约束
ALTER TABLE `student`;
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

image-20211011124011051

删除有外键关系的表，必须要先删除引用别人的表（从表），再删除被引用的表。以上操作都是物理外键，不建议使用，避免数据库过多造成困扰。

DML语言

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

数据库意义：数据存储，数据管理

DML语言：数据操作语言

  • Insert
  • update
  • delete

添加

-- 插入语句（添加）
-- INSERT INTO 表名([字段1,字段2,字段3...]) VALUES(('数据1'),('数据2'),('数据3')...);
-- 由于主键自增，可省略不写，自动匹配
INSERT INTO `grade`(`gradename`) VALUES('大一');
INSERT INTO `grade`(`gradename`) VALUES('大一');
INSERT INTO `grade`(`gradename`) VALUES('大二');
INSERT INTO `grade`(`gradename`) VALUES('大二');

INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`) VALUES('张三','aaaaa','男','1');
INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`) VALUES('李四','bbbbb','女','2'),('王五','ccccc','男','3');

-- 省略字段，但是后面的数据必须一一对应
INSERT INTO `student` VALUES(4,'赵六','ddddd','女','2000-01-02','西安','121434@163',4);

image-20211011134016077

image-20211011155234683

修改

-- 修改学生名字
UPDATE `student` SET `name`='法外狂徒' WHERE id=1;

-- 不指定筛选条件的情况下，会改动所有的值
UPDATE `student` SET `name`='法外狂徒';

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='小李',email='55555@163' where gradeid=2;

image-20211012082033974

-- 语法：
-- UPDATE 表名 SET colnum_name = value,[colnum_name = value...] where [条件]
-- 通过多个条件定位数据
UPDATE `student` SET `name`='小张' WHERE id=1 and sex='男';

image-20211012083220891

条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改...

操作符会返回布尔值

操作符 含义 范围 结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
>
<
<=
>=
BETWEEN...AND... 在某个范围内 [2,5]
AND 和&& 5>1and1>2 false
OR 或|| 5>1or1>2 true

删除

delete 命令

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

-- 删除数据
DELETE FROM `student` WHERE id=1;

TRUNCATE命令

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

语法：TRUNCATE 表名

测试detele和TRUNCATE的区别

-- 新建表test01
CREATE TABLE `test01`(
		`id` INT(4) NOT NULL AUTO_INCREMENT,
		`coll` VARCHAR(20) NOT NULL,
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `test01`(`coll`) VALUES('1'),('2'),('3');

1

-- 使用delete清空
DELETE FROM `test01`;
-- 再插入数据
INSERT INTO `test01`(`coll`) VALUES('1'),('2'),('3');

2

delete的清空不会影响自增。

TRUNCATE TABLE `test01`;

使用TRUNCATE清空表，自增会归零。

DELETE删除问题，重启数据库，现象

  • InnDB 自增列会从1开始（存在内存当中的，断电即失）
  • MySAM 继续从上一个自增量开始（存在文件中的，不会丢失）

推荐阅读