MySQL数据库入门（基础常识）

牛马__
105 阅读8分钟

2、 操作数据库

2.1、数据库的字段属性

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
 ​
 -- 查看数据库的定义
 SHOW CREATE DATABASE school;
 -- 查看数据表的定义
 SHOW CREATE TABLE student;
 -- 显示表结构
 DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

2.2、创建数据库表(重点)

 -- 目标：创建一个school数据库
 -- 创建学生表（列，字段） 使用SQL创建
 -- 学号int 登陆密码varchar（20）姓名，性别varchar（2）出生日期（datatime），家庭住址，email
 ​
 -- 注意点，使用英文()，表的名称和字段尽量使用``括起来
 -- AUTO INCREMENT 自增
 -- 字符串使用 单引号括起来
 -- 所有的语句后面加，（英文的），最后一个不用加
 ​
 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;  -- 查看student数据表的定义语句
 DESC student;           -- 显示表的结构

2.3、 数据库表的类型

 -- 关于数据库引擎
 /*
 INNODB  默认使用
 MYISAM  早些年使用的
 */
MYISAM（早些年使用的）INNODB（默认使用）
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大，约为两倍

常规使用操作：

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

在物理空间存在的位置

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

本质还是文件的存储！

MYSQL引擎是在物理文件上的区别

  • innoDB在数据库中只有一个*.frm文件，以及上级目录下的 ibdata1 文件

  • MYISAM对应文件

    • *.frm -- 表结构定义文件
    • *.MYD -- 数据文件（data）
    • *.MYI -- 索引文件（index）

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

 CHARSET=UTF8

2.4、修改删除表

修改

 ​
 -- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
 ALTER TABLE teacher RENAME AS teacher1;
 -- 增加表的字段  ALTER TABLE 表名 ADD 字段名 列属性;
 ALTER TABLE teacher1 ADD age INT(11);
 ​
 -- 修改表的字段（重命名，修改约束）
 -- ALTER TABLE 表名 MODIFY 字段名 列属性[];
 ALTER TABLE teacher1 MODIFY age VARCHAR(11);    -- 修改约束
 -- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[];
 ALTER TABLE teacher1 CHANGE age ageq INT(1);    -- 字段重命名
 ​
 -- 删除表的字段   ALTER TABLE 表名 DROP 字段名;
 ALTER TABLE teacher1 DROP ageq;

删除

 ​
 -- 删除表  如果存在再删除
 DROP TABLE IF EXISTS teacher1;

注意：

  • `` 字段名，使用反引号包裹
  • 注释 -- /**/
  • sql关键字大小写不敏感，建议写小写

3、数据管理

3.1、外键（了解）

主键PK ，唯一键UK，外键FK_

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

 ​
 CREATE TABLE `grade`(
     `gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级',
     `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
     PRIMARY KEY (`gradeid`) 
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
 ​
 -- 学生表的 gradeid 字段 要去引用年纪表的 gradeid
 -- 定义外键key
 -- 给这个外键添加约束 （执行引用）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 '出生日期',
     `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(4) NOT NULL AUTO_INCREMENT COMMENT '年级',
     `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
     PRIMARY KEY (`gradeid`) 
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
 ​
 -- 学生表的 gradeid 字段 要去引用年纪表的 gradeid
 -- 定义外键key
 -- 给这个外键添加约束 （执行引用）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 '出生日期',
     `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 `student`
   ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
 ​
 -- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表 (那个字段)

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

最佳实践

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

3.2、DML 语言（重点）

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

DML语言：数据库操作语言

  • insert
  • update
  • delete

3.3、添加

INSTER

 ​
 -- 插入语句（添加）
 -- INSERT INTO 表名(字段一,字段二,字段三) VALUES('值1','值2','值3');
 INSERT INTO `grade`(`gradename`) VALUES('大四');
 ​
 -- 由于主键自增我们可可以省略（如果不写表的字段他就会一一匹配）
 INSERT INTO `grade`VALUES('大三');
 ​
 -- 一般写插入语句，我们需要数据和字段一一对应！
 ​
 -- 插入多个字段
 INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');
 ​
 INSERT INTO `student`(`name`)VALUES('张三');
 INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('张三','aaaaa','男');
 INSERT INTO `student`(`name`,`pwd`,`sex`)
     VALUES('李四','aaaaa','男'),('王五','aaaaa','男');

语法：INSERT INTO 表名(字段一,字段二,字段三) VALUES('值1','值2','值3');

注意事项：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的，但是后面的值必须一一对应，不能少
  3. 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开 VALUES(' ',' '),(' ',' '),...

3.4、修改

UPDATE

 -- 修改学员名字,带条件
 UPDATE `student` SET `name`='hwt' WHERE `id`=1;
 ​
 -- 不指定条件的情况下，会改动所有表
 UPDATE `student` SET `name`='老六';
 ​
 -- 修改多个属性，逗号隔开
 UPDATE `student` SET `name`='嘎子',`email`='gazi@163.com' WHERE id = 1;
 ​
 -- 语法
 -- UPDATE 表名 SET 列名 = '修改的值',列名 = '修改的值',.... WHERE 条件;

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

操作符含义范围结果
=等于5=6flase
<>或!=
<
<=
>=
BETWEN...AND...在某个范围内[2,5]
AND我和你&&5>x and x>2false
OR我或你5>x or x>2true
 -- 通过多个条件定位数据
 UPDATE `student` SET `name`='老八' WHERE `name`='老六'AND `sex`='男';

语法：UPDATE 表名 SET 列名 = '修改的值',列名 = '修改的值',.... WHERE 条件;

注意;

  • colnum_name 是数据库的列，尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有的列
  • value，是一个具体的值，也可以是一个变量
  • 多个设置属性之间使用英文逗号隔开
 UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='老八'AND `id`=2;

3.5、删除

DELETE

语法：DELETE FROM 表名 WHERE 条件;

 -- 删除数据（避免这样写，会全部删除）
 DELETE FROM `student`;
 ​
 -- 删除指定数据
 DELETE FROM `student` WHERE id = 1;
 ​

TRUNCATE命令

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

 -- 清空 student 表
 TRUNCATE `student`;

DELETE和TRUNCATE 区别

  • 相同点：都能删除数据

  • 不同

    • TRUNCATE 重新设置自增列 自增计数器会归零
    • TRUNCATE 不会影响事务
 -- 测试DELETE和TRUNCATE 区别
 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('1'),('2'),('3');
 ​
 DELETE FROM `test`;     -- 不会影响自增
 ​
 TRUNCATE TABLE `test`   -- 自增会归零

了解：DELETE删除问题，重启数据库之后

  • InnoDB 自增列会从1开始（存在内存中的，断电即失）
  • MyISAM 继续上一个自增量开始（存在文件中的，不会丢失）
avatar
文章
阅读
粉丝