Jackie's Blog
MySQL
Jackie
  2023-07-23   2025-05-04  2.4k 字  9 分钟

MySQL

狂神说MySQL

小tips

为了网站和数据库的安全性，MySQL内置有root最高用户，划分等级，每个用户对应管理一个数据库，这样保证互不关联，从而不会影响到其他数据库的运行

–表示注释；/**/为多行注释

所有语句使用分号;结尾

如果表名或者字段名是一个特殊字符。就需要带`

基本的数据库操作命令

net start mysql：启动MySQL服务

net stop mysql：关闭MySQL服务

mysql -u root -p：登录MySQL（记得以管理员运行cmd）

show database;：查看所有的数据库

所有的语句都使用;结尾，切换数据库时不需要

use 数据库名：切换数据库

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

describe 表名;：显示数据库中所有的表的信息

create database 数据库名：创建一个数据库

exit：退出连接（Ctrl+C强制退出）

操作数据库

结构化查询语句分类

名称 说明 命令
DDL（数据定义语言） 定义和管理数据对象，如数据库、数据表等 CREATE、DROP、ALTER
DML（数据操作语言） 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL（数据查询语言） 用于查询数据库的数据 SELECT
DCL（数据控制语言） 用于管理数据库的语言，包括管理权限及数据更改 GRANT、commit、rollback

基本操作

  1. 创建数据库
CREATE DATABASE 数据库名
  1. 删除数据库
DROP DATABASE 数据库名
  1. 使用数据库
USE `school`
  1. 查看数据库
SHOW DATABASES

数据库类型

  1. 数值
类型 说明 取值范围 存储需求/字节
tinyint 非常小的数据 有符值：-27 ~27-1
无符号值：0~28-1		 1
smallint 较小的数据 有符值：-215 ~215-1
无符号值：0~216-1		 2
mediumint 中等大小的数据 有符值：-223 ~223-1
无符号值：0~224-1		 3
int 标准的整数 有符值：-231 ~231-1
无符号值：0~232-1		 4
bigint 较大的数据 有符值：-263 ~263-1
无符号值：0~264-1		 8
float 单精度浮点数 ±1.17e -38 4
double 双精度浮点数 ±2.22e -308 8
decimal 字符串形式的浮点数
金融计算时常用		 decimal(m,d)
参数m<65 是总个数，d<30且 d<m 是小数位		 m个字节
  1. 字符串
类型 说明 最大长度/字节
char 固定长字符串 0-255
varchar 可变字符串 0-65535
tinytext 微型文本 28-1
text 文本串 216-1
  1. 时间日期

java.util.Date

类型 说明
DATE YYYY-MM-DD
TIME Hh:mm:ss
DATETIME YYYY-MM-DD HH:mm:ss
TIMESTAMP 时间戳（1970.1.1到现在的毫秒数）
YEAR 表示年份

yyyy-MM-dd HH:mm:ss年-月-日 时:分:秒 大写是区分“ 月 ”与“ 分 ” HH为什么大写，是为了区分 12小时制 与 24小时制 。 小写的h是12小时制，大写的H是24小时制

  1. null

没有值，未知

不要使用null进行算术运算，结果仍为null

MySQL数据类型的长度和范围

image

※字段属性

  • Unsigned

无符号的

声明该数据列不允许负数

  • Zerofill

0填充的

不足位数的用0来填充 , 如int(3),5则为005

  • Auto_InCrement

自动增长 , 每添加一条数据 , 自动在上一个记录数上加 1（默认）

通常用于设置主键 , 且为整数类型

可自定义设计主键自增的起始值和步长

当前表设置步长(AUTO_INCREMENT=100)：只影响当前表

SET @@auto_increment_increment=5;影响所有使用自增的表（全局）

  • NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值

如果设置为NOT NULL , 则该列必须有值

  • Default

设置默认的值

创建数据库表

--目标 : 创建一个school数据库
--创建学生表(列,字段)
--学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
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] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';
-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

数据表类型

InnoDB：默认使用

MyISAM：早些年使用的

名称 MyISAM InnoDB
事务处理 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大，约2倍

常规使用操作

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

数据表的存储位置

位置：Mysql安装目录\data\下存放数据表。目录名对应数据库名 , 该目录下文件名对应数据表

每个库目录存在一个保存当前数据库的选项文件db.opt

注意

  • *.frm 表结构定义文件

  • *.MYD 数据文件 ( data )

  • *.MYI 索引文件 ( index )

  • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

  • MyISAM类型数据表对应三个文件：
    *.frm 表结构定义文件
    *.MYD 数据文件 ( data )
    *.MYI 索引文件 ( index )

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

我们可为数据库、数据表、数据列设定不同的字符集，设定方法：

  • 创建时通过命令来设置

    CREATE TABLE 表名()CHARSET=utf8;

  • 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定

参考链接：http://t.csdn.cn/Vy9j2

修改、删除表

  • 修改表（ALTER TABLE）

修改表名：ALTER TABLE 旧表名 RENAME AS 新表名

添加字段：ALTER TABLE 表名 ADD 字段名 列属性[属性]

修改字段

​ ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

​ ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段：ALTER TABLE 表名 DROP 字段名

  • 删除表

DROP TABLE [IF EXISTS] 表名

MySQL数据管理

外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外部作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

  1. 创建子表同时创建外键
-- 创建外键的方式一 : 创建子表同时创建外键
-- 年级表 (id\年级名称)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除具有主外键关系的表时 , 要先删子表 , 后删主表

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;
  1. 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

ALTER TABLE `表名` ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES (`哪个字段`)

不建议使用（避免数据库过多造成困扰）

增删改查

  • Insert 添加
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1'),('值2'),('值3')

例

INSERT INTO `student`(name,sex,age) values('张三',18,'男')
--另一种形式
INSERT INTO Set name='张三',age=18
  • Update 修改
UPDATE 表名 SET column_name=value [,column_name1=value1,...] [WHERE condition];

例

UPDATE student set name='张三' where id=1
  • Delete 删除
DELETE FROM 表名 [WHERE condition];

例

DELETE FOTM student WHERE id=1
  • TRUNCATE

作用：用于完全清空表数据，但表结构、索引、约束等不变

TRUNCATE [TABLE] table_name;

DELETE与TRUNCATE区别

  • 相同：都能删除数据，不删除表结构，但TRUNCATE速度更快
  • 不同：
    • TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
    • 使用TRUNCATE TABLE不会对事务有影响

DELETE删除的问题

  • InnoDB 自增列会重新从1开始（存在于内存当中，断电即失）
  • MyISAM 继续从上一个自增量开始（存在于文件中，不会丢失）
  • SELECT

死锁：指两个或两个以上的进程在执行过程中因争夺资源而造成的一种互相等待的现象

 评论
评论插件加载失败
正在加载评论插件
  1. MySQL
  2. 小tips
  3. 基本的数据库操作命令
  4. 操作数据库
    1. 结构化查询语句分类
    2. 基本操作
    3. 数据库类型
    4. ※字段属性
    5. 创建数据库表
    6. 数据表类型
    7. 修改、删除表
  5. MySQL数据管理
    1. 外键
    2. 增删改查
  1. MySQL
  2. 小tips
  3. 基本的数据库操作命令
  4. 操作数据库
    1. 结构化查询语句分类
    2. 基本操作
    3. 数据库类型
    4. ※字段属性
    5. 创建数据库表
    6. 数据表类型
    7. 修改、删除表
  5. MySQL数据管理
    1. 外键
    2. 增删改查