数据库基本命令及数据库定义语言DDL

---

一、数据库操作的基本命令

1. 连接数据库

mysql -u用户名 -p用户密码
【注意】 -u和-p后面不能加空格,否则会被当做用户名/密码的内容,导致登录失败 !

2.注释

（1） – 单行注释
（2） /* 多行注释 */

3.其他操作的基本命令

update user set password=password('123456')where user='root';  --修改密码
flush privileges;         --刷新数据库
show databases;           --显示所有数据库
create database name;    --创建数据库
use 数据库名;             --打开某个数据库
show tables;             --显示数据库mysql中所有的表
describe 表名;           --显示表mysql数据库中某张表的列信息
exit;                    --退出Mysql

? 命令关键词;            --寻求帮助
CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果，其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。

4.MySQL数据表以文件方式存放在磁盘中

• 包括表文件 , 数据文件 , 以及数据库的选项文件
• 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 ,
  该目录下文件名对应数据表 。

二、数据库操作

2.1 基本命令

结构化查询语句分类

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

命令行操作数据库

创建数据库 : create database [if not exists] 数据库名;
删除数据库 : drop database [if exists] 数据库名;
查看数据库 : show databases;
使用数据库 : use 数据库名;
[注意]在涉及到一些数据库名/字段名/...是特殊字符时，用``括起来

2.2 数据值和列类型

1.数值类型（加粗的是常用的）

类型	说明	存储容量
tinyint	非常小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准整数	4个字节
bigint	较大的数据	8个字节
float	单精度浮点数	4个字节
double	双精度浮点数	8个字节
decimal	字符串形式的浮点数	4个字节

2.字符串类型

类型	说明	最大长度
char	固定长度的字符串	M个字符（0 <= M <= 255）
varchar	可变字符串	变长度（0 <= M <= 65535）
tinytext	微型文本串	2^8 - 1 字节
text	文本串	2^16 - 1 字节

3.日期和时间型数值类型

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

4.NULL值

• 理解为 “没有值” 或 “未知值”
• 不要用NULL进行算术运算 , 结果仍为NULL

2.3 数据字段属性

UnSigned

• 无符号的整数
• 声明该数据列不允许负数

ZEROFILL

• 不足位数的用0来填充
• 例如：int(3)，5 --> 005

Auto_InCrement

• 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
• 通常用于设置主键 , 且为整数类型
• 可定义起始值和步长
  • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
  • SET @@auto_increment_increment=5：影响所有使用自增的表(全局)

NULL 和 NOT NULL

• 默认为NULL , 即没有插入该列的数值
• 如果设置为NOT NULL , 则该列必须有值

DEFAULT

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

2.4 创建数据表

1. 创建的语句属于DDL
   【注】所有的创建和删除操作尽量都加上判读语句if not exists / if exists，避免报错

create table [if not exists] `表名`(
   `字段名1` 列类型 [属性][索引][注释],
   `字段名2` 列类型 [属性][索引][注释],
   `字段名n` 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

3. 例一

create table if not exists `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`date` DATETIME DEFAULT NULL COMMENT '日期',
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

在SQLyog的图形界面可以直接修改其约束条件


4. 例二

-- 目标 : 在数据库school中创建学生表student
-- 学号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';

【拓展】一个表中必须有的字段

• id 主键（自增）
• version 乐观锁
• is_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

2.5 修改和删除表

修改表

修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

ALTER TABLE `student` RENAME AS `stutable`;   -- 修改表名
ALTER TABLE `student` ADD `age` INT(10) NOT NULL DEFAULT '18' COMMENT '年龄';    -- 增加表的字段

修改字段

• 修改约束：ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
• 字段重命名：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

ALTER TABLE `student` MODIFY `age` VARCHAR(5);   -- 修改约束
ALTER TABLE `student` CHANGE `pwd` `password` VARCHAR(2);    -- 字段重命名

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

ALTER TABLE `student` DROP `age`;   -- 删除age字段

删除数据表

语法：DROP TABLE [IF EXISTS] 表名

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

DROP TABLE IF EXISTS `teacher`;   -- 删除teacher表

其他

1. 可用反引号 ` 将标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！
2. 每个库目录存在一个保存当前数据库的选项文件db.opt。
3. 模式通配符：

• _ 任意单个字符
• % 任意多个字符，甚至包括零字符
• 单引号需要进行转义 ’

4. SQL对大小写不敏感
5. 清除已有语句：\c