MySQL基础知识(配合Navicat使用)

数据库的基本概念

数据(Data), 数据库(DataBase，DB), 数据库管理系统(DataBase Management System,DBMS),

数据库系统(DataBase System，DBS), 数据库应用系统(DataBase Application System,DBAS)

关系型数据库：

• MySQL，Oracle，SqlServer，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储，例如：学院信息表，考勤表

非关系型数据库：

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

数据库管理系统DBMS：

• 数据的管理软件，科学有效的管理我们的数据。维护和获取数据
• MySQL，数据库管理系统

MySQL简介

• MySQL是一个**关系型数据库管理系统**

• MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统)

• MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言

连接数据库

show databases;   -- 查看所有的数据库

mysql> use school;  -- 切换数据库   use 数据库名
Database changed

show tables;     -- 查看数据库中所有的表

desc student;    -- 显示数据库中所有表的信息

exit;       -- 退出连接

--  代表单行注释
/*
多行注释
*/

数据库xxx语言：

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

操作数据库(了解)

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2. 删除数据库

DROP DATABASE [IF EXISTS] hello;

3. 使用数据库

USE school;

4. 查看所有数据库

SHOW DATABASES;

数据库的列类型

数值-

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• **int 标准的整形 4个字节 ****
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算的时候一般使用

字符串

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

时间日期

• date YYYY-MM-DD ,日期格式
• time HH-mm-ss 时间格式
• datetime YYYY-MM-DD HH-mm-ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数 也较为常用
• year 年份表示

null

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

数据库的字段属性（重点）

Unsigned：

• 无符号整数
• 声明了该列不能为负数

zerofill：

• 零填充
• 不足的位数，使用0来填充

自增：设置自增类型的字段，需要将其设置为主键，否则会报错

• 通常可以理解为自增，自动在上一条记录的基础上 +1 （默认）
• 通常用来设计唯一的主键~ index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空 null not null

• 假设设置为not nul ，如果不给它赋值，就会报错
• NULL，如果不填写，默认就是null

默认：

• 设置默认的值，需要加 ’ ’ (英文的)
• sex，默认值为男，如果不指定该列的值，则会有默认的值

创建数据库表(重点)

-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串使用  单括号''  括起来
-- 所有的语句后面加 ,(英文的) ，最后一个不用加
-- PRIMARY　KEY　主键，一般一个表只有一个唯一的主键
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] `表名`(
	'字段名' 列类型 [属性] [索引] [注释],
	'字段名' 列类型 [属性] [索引] [注释],
	.....
	'字段名' 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]

navicat查看创建数据库的语句,数据表的语句和表的结构

-- 1.
SHOW CREATE DATABASE school  --查看创建数据库的语句
-- 2.
SHOW CREATE TABLE student  --查看student数据表的定义语句
-- 3.
DESC student --显示表的结构

运行结果：

-- 1.
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
-- 2.
-- 逆向操作，先创建一个数据表，然后在查看
CREATE TABLE `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

-- 3.

数据表的类型

--关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/

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

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

MySQL引擎在物理文件上的区别

• INNODB 在数据库表中只有一个*.firm文件，以及上级目录下的 ibdata1 文件
• MYISAM对应文件
  • *.firm 表结构文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

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

CHARSET=utf8

不设置的话，会是MySQL默认的字符据编码~（不支持中文！）

MySQL的默认编码是latin1,不支持中文

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

character_set_server=utf8

修改删除表

修改

-- 修改表名ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher01

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

-- 修改表的字段名  （重命名，修改约束!）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher01 MODIFY age VARCHAR(11)  -- 修改约束

-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[]
ALTER TABLE teacher01 CHANGE age age1 INT(10)  -- 字段重命名
-- change一般用来字段重命名，modify修改约束

-- 删除表的字段名 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher01 DROP age1

删除

-- 删除表（如果表存在再删除）
DROP TABLE [IF EXISTS] teacher01

所有的创建和删除操作尽量加上判断，以免报错

注意点：

• `` 字段名使用这个包裹！
• 注释 – /* */
• sql关键字大小写不敏感，一般使用小写
• 所有的符号全部用英文

MySQL数据管理

外键（了解即可）

方式一：在创建表的时候，增加约束，（麻烦，比较复杂）

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


-- 学生表的 gradeid 字段  要去引用年级表的  gradeid
-- 定义外键key
-- 给这个外键添加约束  （执行引用） references 引用
CREATE TABLE `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 '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除有外键关系的表，必须先删除引用别人的表（从表），再删除被引用的表（主表），否则报错

方式二：创建表成功后，添加外键约束

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


-- 学生表的 gradeid 字段  要去引用年级表的  gradeid
-- 定义外键key
-- 给这个外键添加约束  （执行引用） references 引用
CREATE TABLE `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 '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 创建表的时候没有外键关系
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列)REFERENCES 哪个表(哪个字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

以上的操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多造成困扰，了解即可！！！！）

最佳实践：

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 相使用多张表的数据，使用外键（程序区实现）

DML语言（全部记住）

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

DML语言：数据操作语言

• insert，update，delete

添加

insert

-- 插入语句
-- insert into 表名([字段1,字段2,....]) values('值1'),('值2'),('值3')...
INSERT INTO `grade` (`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略(如果不写表的字段名，他就会一一匹配)
INSERT INTO `grade` VALUES('大三')  -- 语句报错,没有一一匹配
-- 一般写入插入语句，我们一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade` (`gradename`)
VALUES('大二'),('大一')

INSERT INTO `student`(`name`) VALUES('张三')

-- 可以同时插入多条语句，VALUSES后面的值，隔开即可VALUES(),(),...
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES('李四','aaaaaa','男'),('王五','aaabbbba','男')

-- 字段可以省略，但后面的值必须要一一对应，否则报错
INSERT INTO `student` 
VALUES(4,'李四','aaaabb','男','2000-01-01','西安','email')

语法：insert into 表名([字段1,字段2,…]) values(‘值1’),(‘值2’),(‘值3’)…

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段可以省略，但后面的值必须要一一对应，否则报错
3. 可以同时插入多条语句，VALUSES后面的值，隔开即可VALUES(),(),…

修改

update

-- 语法：UPDATE 表名 SET column_name=value WHERE [条件]

-- 修改学员的名字，带了条件
UPDATE `student` SET `name`='狂神' WHERE id=1;

-- 不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name`='sgl';

-- 修改多个属性
UPDATE `student` SET `name`='coco',`email`='2259567490@qq.comm' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='长江7号' WHERE `name`='two' AND `sex`='男' AND `address`='西安';

操作符会返回 布尔值

操作符	含义	范围	结果
=	等于
<> 或 !=	不等于
>
<
>=
<=
BETWEEN … AND …	在某个范围	闭区间 [x,y]
AND	我和你 &&
OR	我或你 ||

语法：UPDATE 表名 SET column_name=value,[column_name=value,…] WHERE [条件]

注意：

• column_name 是数据库的列，尽量带上 ``
• 条件，筛选的条件，如果没有指定将会修改所有的列
• value，是一个具体的值，也可以是一个变量

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='长江7号' AND `address`='西安';
-- CURRENT_TIME WHERE 返回当前时间，是一个变量

删除

delete

-- 删除数据 （避免这样写,会全部删除）
DELETE FROM `student`

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

TRUNCATE 命令

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

-- 清空 student 表
TRUNCATE `student`

TRUNCATE 和 delete 的区别

• 相同点：都能删除数据库，都不会删除表结构
• 不同点：
  • TRUNCATE：重新设置自增列 计数器回归零，重1开始
  • TRUNCATE 不会影响事务

-- 测试TURUNCATE和delete的区别
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT ,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`id`)
	) ENGINE=INNODB DEFAULT CHARSET=utf8
	
	INSERT INTO `test`(`coll`) VALUES('a'),('b'),('c')
	
	DELETE FROM `test`   -- 不会影响自增
	
	TRUNCATE TABLE `test`  -- 自增会归零，重1开始

了解即可： DElETE删除的问题，重启数据库，现象：

• INNODB：自增量会从1开始（存在内存档中，断电即失）
• MYISAM：继续从上一个自增量开始（存在文件中，不会丢失）

DQL查询数据（非常重要）

（Data Query LANGUAGE：数据查询语言）

select完整语句：

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{
  *|talbe.*|