MySQL学习笔记（二）------数据库操作

MySQL学习笔记（二）------操作数据库

• 操作数据库>操作数据库中的表>操作数据库中表的数据
• MySQL关键字不区分大小写

操作数据库（了解）

创建数据库

CREATE DATABASE IF NOT EXISTS westos;

• IF NOT EXISTS：可选加或不加，意为如果不存在

删除数据库

DROP DATABASE IF EXISTS westos;

• IF EXISTS：可选加或不加，意为如果存在

使用数据库

USE school;

• 如果你的表名或者字段名是一个特殊的字符，就需要带``

查看所有数据库

SHOW DATABASES;

学习思路

• 对照SQLyog的历史记录查看sql语句
• 固定的语法或关键字必须要强行记住

数据库的数据类型（数据库的列类型）

• 按从小到大顺序

数值

整数

• tinyint：十分小的数据----1个字节
• smallint：较小的数据----2个字节
• mediumint：中等大小的数据----3个字节
• int：标准整数----4个字节（常用）
• bigint：较大的数据----8个字节

浮点数

• float：浮点数----4个字节
• double：浮点数----8个字节
• decimal：字符串形式的浮点数（精度问题，金融计算使用）

字符串

• char：固定大小的字符串----0~255
• varchar：可变字符串----0~65535（常用）
• 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填充的

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

Auto_increment（自增）

• 理解为自动增长的 , 每添加一条数据 , 自动在上一个记录数上加+1(默认)

• 通常用于设置唯一的主键 , 必须是整数类型

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

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

• SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

非空（NULL 和 not null）

• NULL，如果不填写值，默认就是null

• 如果设置为NOT NULL , 则该列必须有值，不赋值，会报错

默认（DEFAULT）

• 用于设置默认值

• 例如,sex,默认为"男"，否则为 “女”，若无指定该列的值，则默认值为"男"的值

拓展：常用字段

每一个表都必须存在以下五个字段

• id 通常为主键
• version 乐观锁中使用
• id_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

目标 : 创建一个school数据库

• 创建学生表(列,字段)，使用SQL语句创建
• 学号int，登录密码varchar(20)，姓名，性别varchar(2)，出生日期(datatime)，家庭住址，email
• 创建表之前 , 一定要先选择数据库
• 注意：使用英文括号，表的名称和字段尽量使用``括起来
• 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

常用命令

查看创建数据库的语句

SHOW CREATE DATABASE school;

查看student数据表的定义语句

SHOW CREATE TABLE student;

查看表的具体结构

DESC student;

关于数据库的引擎

• InnoDB ：默认使用
• Myisam：早些年使用

	Myisam	InnoDB
事务支持	不支持	支持
数据行锁定	不支持 （表锁）	支持 （行锁）
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为Myisam的两倍

常规使用操作

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

在物理空间存在的位置

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

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

• InnoDB：在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• Myisam对应文件：
  • * . frm ----- 表结构的定义文件
  • * . MYD ----- 数据文件 ( data )
  • * . MYI ----- 索引文件 ( index )

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

• CHARSET=utf8：不设置的话，会是MySQL默认的字符集编码（不支持中文）

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

• 可以在my.ini中配置默认的编码（character-set-server=utf8）

修改和删除表

修改表

修改字段 :

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

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

• 修改表名

alter table teacher rename as teacher1
alter table [旧表名] rename as [新表名]

• 增加表的字段

alter table teacher add age int(2)
alter table [表名] add [字段名] [列属性]

• 修改字段（修改约束）

alter table teacher modify age varchar(11)
alter table [表名] modify [字段名] [列类型]

• 修改字段（重命名）

alter table teacher change age age1 int(4)
alter table [表名] change [旧字段名] [新字段名] [列属性]

注：
change ：用来字段重命名，不能修改字段类型和约束

​modify：不用来字段重命名，只能修改字段类型和约束

删除表

• 删除表的字段

alter table teacher drop age
alter table [表名] drop [字段名]

• 删除表

drop table [if exists] teacher
drop table [if exists] [表名]

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

注意点

• CMD命令行内的语句结束符可以为 “;” , “\G” , “\g”，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。

• SQL对大小写不敏感 （关键字）

• 清除已有语句：\c