# 数据库和表的操作
# 创建数据库并插入数据
# 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:事务控制语言
注意:
- 以
;
结尾 - SQL关键字不区分大小写
- 可以由空格或换行,但一定要以
;
结尾
# 库相关数据语句
# 显示数据库
查询所有数据库
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;
2
3
创建成功后输入命令 show databases;
(注意不要漏掉;
)
在大多数系统中,SQL 语句都是不区分大小写的,因此以下语句都是合法的:
CREATE DATABASE name1;
create database name2;
CREATE database name3;
create DAtabaSE name4;
2
3
4
5
但是出于严谨,而且便于区分保留字(保留字(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 数据类型(数据长度)
);
# 列名即字段名
2
3
4
5
6
7
我们尝试在 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;
2
3
4
5
6
7
# 查询所有表
show tables;
# 查询表信息
show create table 表名;
# 修改表名
重命名一张表的语句有多种形式,以下 3 种格式效果是一样的:
rename table 原名 to 新名;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;
2
3
4
# 删除表
- 格式:
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;
1
2
3float,double小数类型
double(m,d)
m代表总长度,d代表小数长度 , 存23.212 m=5 d=3create table t5(price double(5,3)); insert into t5 values(23.32123); insert into t5 values(233.32123); //报错
1
2
3numeric(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);
2
3
# 图片
- blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
约束 →