目录

一、操作数据库

1、操作数据库

1、创建 数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

2、删除 数据库

DROP DATABASE [if EXISTS] 数据库名;

3、使用 数据库

–如果表名或者字段名是特殊字符，则需要带``

use 数据库名;

4、查看数据库

SHOW DATABASES;

2、数据库的字段属性

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

  • 默认的
  • 用于设置默认值
  • 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

3、创建数据库表

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

注意点：

  • 表名和字段尽量使用``括起来

  • AUTO_INCREMENT 代表自增

  • 所有的语句后面加逗号，最后一个不加

  • 字符串使用单引号括起来

  • 主键的声明一般放在最后，便于查看

  • 不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

格式：

CREATE TABLE IF NOT EXISTS `student`(
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]

常用命令：

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

4、数据库存储引擎

INNODB

  • 默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

  • 早些年使用，节约空间，速度较快
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大，约为2倍

5、修改数据库 （alter table）

修改

修改表名 :

ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 :

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

修改字段 :

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段 :

 ALTER TABLE 表名 DROP 字段名

举例

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

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

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名

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

6、删除数据库 （drop table）

语法：

DROP TABLE [IF EXISTS] 表名
  • IF EXISTS为可选 , 判断是否存在该数据表
  • 如删除不存在的数据表会抛出错误

– 删除表(如果存在再删除)

DROP TABLE IF EXISTS teachers;

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

二、外键

1.外键概念

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

以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

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

2.外键作用

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

目标：学生表（student）的gradeid字段 要去引用年级表（grade）的 gradeid字段

创建外键

方式一：在创建表的时候增加约束

/*
    1. 定义外键key
    2. 给外键添加约束（执行引用）references 引用
*/
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 DEFAULT CHARSET=utf8

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

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

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

/*
    1. 定义外键key
    2. 给外键添加约束（执行引用）references 引用
*/
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`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

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

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

最佳实践

  • 数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）
  • 我们想使用多张表的数据，使用外键，用程序去实现