数据库和表的操作

• 创建数据库并插入数据
  • SQL语句
• 库相关数据语句
  • 显示数据库
  • 显示数据库信息
  • 删除数据库
  • 导入数据库
  • 新建数据库
  • 连接数据库
  • 关于修改数据库
• 表相关数据语句
  • 数据表
  • 新建数据表
  • 查询所有表
  • 查询表信息
  • 修改表名
  • 删除表
  • 查询表字段
  • 数据类型

创建数据库并插入数据

SQL语句

定义

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO](https://baike.baidu.com/it）颁布了SQL正式国际标准。

分类

• DML（Data Manipulation Language）数据操纵语言

如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

• DDL（Data Definition Language）数据库定义语言

如：create table之类

• DCL（Data Control Language）数据库控制语言

如：grant、deny、revoke等，只有管理员才有相应的权限

• DQL（Data Query Language）数据库查询语言 如: select 语法
• TCL:事务控制语言

注意：

1. 以;结尾
2. SQL关键字不区分大小写
3. 可以由空格或换行,但一定要以;结尾

库相关数据语句

显示数据库

查询所有数据库

SHOW DATABASES;

显示数据库信息

SHOW CREATE DATABASE 数据库名;

删除数据库

drop database 数据库名;

导入数据库

source f:/emp.sql;

新建数据库

首先，我们创建一个数据库，给它一个名字，比如 mysql_shiyan，以后的几次实验也是对 mysql_shiyan 这个数据库进行操作。 语句格式为 CREATE DATABASE <数据库名字>;，（注意不要漏掉分号 ;），前面的 CREATE DATABASE 也可以使用小写，具体命令为：

CREATE DATABASE mysql_shiyan;
create database db1 charset=utf8;
create database db2 charset=gbk;

创建成功后输入命令 show databases; （注意不要漏掉;） 在大多数系统中，SQL 语句都是不区分大小写的，因此以下语句都是合法的：

CREATE DATABASE name1;
create database name2;
CREATE database name3;
create DAtabaSE name4;

但是出于严谨，而且便于区分保留字（保留字(reserved word)：指在高级语言中已经定义过的字，使用者不能再将这些字作为变量名或过程名使用。）和变量名，我们把保留字大写，把变量和数据小写。

连接数据库

由于一个系统中可能会有多个数据库，要确定当前是对哪一个数据库操作，使用语句 use <dbname>：

use mysql_shiyan;

输入命令 show tables; 可以查看当前数据库里有几张表，现在 mysql_shiyan 里还是空的.

关于修改数据库

目前 Mysql 没有提供修改数据库名称的方法，因为这曾导致一系列安全问题。

在老版本中 RENAME DATABASE 可以修改数据库名称，这条命令在 MySQL 5.1.7 中被加入，但官方很快就发现这条命令所带来的危险，于是在 MySQL 5.1.23 中把这条命令移除。

事实上，数据库名几乎不会遇到必须修改的情况，如果你一定要这么做，比较安全的做法是重新建一个新库，然后将旧库中的数据转移到新库中，并且暂时不要删除旧的数据库，以防数据遗失。

表相关数据语句

数据表

数据表（table）简称表，它是数据库最重要的组成部分之一。数据库只是一个框架，表才是实质内容。

而一个数据库中一般会有多张表，这些各自独立的表通过建立关系被联接起来，才成为可以交叉查阅、一目了然的数据库。如下便是一张表：

ID	name	phone
01	Tom	110110110
02	Jack	119119119
03	Rose	114114114

新建数据表

在数据库中新建一张表的语句格式为：

CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);
# 列名即字段名

我们尝试在 mysql_shiyan 中新建一张表 employee，包含姓名，ID 和电话信息，所以语句为：

CREATE TABLE employee (id int(10),name char(20),phone int(12));
create table student(
    name varchar(50),
    chinese int,
    math int,
    english int
)charset=utf8;

查询所有表

show tables;

查询表信息

show create table 表名;

修改表名

重命名一张表的语句有多种形式，以下 3 种格式效果是一样的：

rename table 原名 to 新名;
ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

删除表

• 格式: drop table 表名;

查询表字段

desc 表名;

数据类型

MySQL 的数据类型和其他编程语言大同小异，下表是一些 MySQL 常用数据类型：

数据类型	大小(字节)	用途	格式
INT	4	整数
FLOAT	4	单精度浮点数
DOUBLE	8	双精度浮点数
ENUM	--	单选,比如性别	ENUM('a','b','c')
SET	--	多选	SET('1','2','3')
DATE	3	日期	YYYY-MM-DD
TIME	3	时间点或持续时间	HH:MM:SS
YEAR	1	年份值	YYYY
CHAR	0~255	定长字符串
VARCHAR	0~255	变长字符串
TEXT	0~65535	长文本数据

整数除了 INT 外，还有 TINYINT、SMALLINT、MEDIUMINT、BIGINT。

CHAR 和 VARCHAR 的区别: CHAR 的长度是固定的，而 VARCHAR 的长度是可以变化的，比如，存储字符串 “abc"，对于 CHAR(10)，表示存储的字符将占 10 个字节(包括 7 个空字符)，而同样的 VARCHAR(12) 则只占用 4 个字节的长度，增加一个额外字节来存储字符串本身的长度，12 只是最大值，当你存储的字符小于 12 时，按实际长度存储。

ENUM 和 SET 的区别: ENUM 类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而 SET 类型的值则可以多选。

命名规则

• 字段名必须以字母开头，尽量不要使用拼音
• 长度不能超过30个字符（不同数据库，不同版本会有不同）
• 不能使用SQL的保留字，如where,order,group
• 只能使用如下字符az、AZ、0~9、$ 等
• Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
• 多个单词用下划线隔开，而非java语言的驼峰规则

字符

• char长度固定，不足使用空格填充，执行效率略高, 当保存数据的长度相对固定时使用, 最大值255，char(11)存储abc，占11位。查询速度极快但浪费空间
• varchar(m),可变长度,m=10 存abc 占3,更节省空间, 最大值65535 但推荐保存短的数据(255以内),varchar(11)存储abc，只占3位。查询稍慢，但节省空间。
• text(m),可变长度, 最大值65535,建议保存长度大于255的:

以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

UTF8字符集

• 目前utf8 和utf8mb3 一样 ,代表的是 用3个字节表示一个字符 (mb3=most byte 3)

• utf8mb4 最多不超过4个字节表示一个字符

数字

• 整数: int(m)和bigint(m) m代表显示长度, m=5 存18 查询得到00018

  create table t3(age int(5) zerofill);
  insert into t3 values(18);
  select * from t3;
  

• float,double小数类型 double(m,d) m代表总长度,d代表小数长度 , 存23.212 m=5 d=3

  create table t5(price double(5,3));
  insert into t5 values(23.32123);
  insert into t5 values(233.32123);  //报错 
  

• numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数

• decimal和numeric表示精确的整数数字

日期

• date, 只能保存年月日

• time, 只能保存时分秒

• datetime, 保存年月日时分秒, 默认值为null , 最大值 9999-12-31

• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数,保存年月日时分秒,默认值为当前系统时间,最大值 2038-1-19

create table t6(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t6 values("2022-5-15",null,null,null);
insert into t6 values(null,"14:20:25","2011-10-22 10:20:30",null);

图片

• blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。