数据库操作， 数据类型表操作。

一、数据库操作

数据库操作 在关系数据库中，sql语句或程序中

1. 注释 - -为单行注释  /* 多行注释 */

2. 不区分大小写，但有习惯性将默认的关键字为大写

3. 每个语句结尾建议有分号

4. mysql中使用反引号为兼容性操作

1. 查看库 show SHOW DATABASES [LIKE '数据库名']; 

-- 查看所有数据库
show databases;
-- 查看数据库名称为db开头
show databases like 'db%';
/*
在数据库列表中
information_schema
mysql
有一个表名称为 user 管理员相关的信息
performance_schema
sys
都是mysql系统的数据库，一般不要操作
*/
-- 查看当前登录用户转为使用的库
select database(),user(),now(),version();
-- 查看建立数据库的语句代码
show create database d1;
-- CREATE DATABASE `d1` /*!40100 DEFAULT
CHARACTER SET utf8 */ /*!80016 DEFAULT
ENCRYPTION='N' */
-- CREATE DATABASE d1 DEFAULT CHARACTER SET utf8;
show create database mysql;
-- CREATE DATABASE `mysql` 
/*!40100 DEFAULT
CHARACTER SET utf8mb4 COLLATE
utf8mb4_0900_ai_ci */ 

/*!80016 DEFAULT
ENCRYPTION='N' */

2. 建立数据库

-- 建立数据库
create database d1;
create database d2 default character set
utf8;
create database if not exists `d3` charset
utf8;
create database db default CHARACTER SET
gbk;
show create database db;
-- 修改数据库文件的字符集
alter database db character set utf8;

3. 删除数据库 drop

-- 删除数据库
drop database d1;
drop database if exists `d2`;
drop database d3;

4.查看字符集信息 

show charset like 'gb%';
show charset like 'utf%';
show VARIABLES like 'char%';
show VARIABLES like '%dir%';

二、数据类型表操作

1. 数据类型

1.1 numeric数字类型

整数： tinyint smallint mediumint int bigint

小数：float double decimal(p,s) numeric(p,s)

DOUBLE PRECISION[(M,D)] [UNSIGNED]

[ZEROFILL], REAL[(M,D)] 

无符号 ，填充0[UNSIGNED] [ZEROFILL]

-- 建立表(字段列使用数据类型)
create table student(
id int unsigned auto_increment,
name varchar(30),
/* age bigint , 不能这样写，bigint 是占用8字节
*/
age tinyint, /* 整数只能存储整数 18*/
money decimal(10,2),/* 99999999.99 最大数字
如果没有数字则是 0.00 */
primary key(id)
);
-- 插入数据
insert into student
values(null,
'jack'
,18,200);
-- 查询数据
select * from student;

1.2 string 字符串

char(n) varchar(n) tinytext text mediumtext longtext

char(0-255)

varchar(0-21844) // 63533

定长字符串 char()

变长字符串 varchar(n) tinytext text mediumtext longtext(4GB字符串)

-- char varchar tinytext text mediumtext
longtext
-- 32767-1 21845-1 16383-1
-- gbk varchar(32766) utf8 varchar(21844)
utf8mb4 varchar(16383)
select 65535/2,65535/3,65535/4;
-- 演示
create table t2(
c1 char(5),/*定长字符串，功能是，如果设置5，
则插入数据不到被空格也要占用5字节*/
c2 varchar(5),/*变长字符串，如果设置5，最多
插5个字符，如果插入2个字，则占用2个字符的字节数*/
c3 tinytext,
c4 mediumtext,
c5 text,
c6 longtext
);
insert into t2
value('a'
,
'b'
,
'c'
,
'd'
,
'e'
,
'f');
select * from t2;
-- 建立表
create table user(
sno char(5),/*定长*/
name varchar(3)/*变长字符串 3个字符串不是3个
字节*/
);
insert into user value('20211'
,
'李四六');
insert into user value('1'
,
'jac');
select name,char_length(name),length(name)
from user;
select sno,char_length(sno),length(sno)
from user;
-- 数据类型 字符型（串）
show tables;
select
user(),database(),now(),3*3,rand(),version(
),sysdate();
select CURRENT_DATE(),curdate();
-- 删除指定几个表
drop table if exists t1,t2,t3,t4,t5,t6;
/* 多行注释 */
-- 单行注释
-- 字符串类型 的值需要添加单引号，数字类型值一般不加
单引号
-- char 定长字符串 varchar(30) '' 变长字符串
-- uuid char(36) varchar(36)
-- string char(n) n(max=255) varchar(n)
TINYTEXT text MEDIUMTEXT LONGTEXT(4GB)
create table t1(
s1 char(6), /* 6个字符 */
s2 char(200),
s3 char(255),
s4 varchar(10000),
s5 varchar(11383)
);
drop table if exists t1;
create table t2(
s1 char(6), /* 6个字符 最多是255 */
s2 char(3) /*a */
);
insert t2 value('javaab'
,
'李si');
select * from t2;
select
CHARACTER_LENGTH(s1),length(s1),CHARACTER_L
ENGTH(s2),length(s2) from t2;
-- 114688
create table t3(
c char(5)
);
insert t3 value('d2a');
select * from t3;

1.3 date、time、datetime日期相关类型

date yyyy-MM-dd 3字节 time hh:mm:ss 3字节

datetime yyyy-MM-dd hh:mm:ss 8字节 用来存储日期和时间。格式为"yyyy-mm-dd hh:mm:ss"，在mysq中占8个字节 存储范围： 1000-01-01 00:00:00 -- 9999-12-31 23:59:59

timestamp 时间戳 yyyy-MM-dd hh:mm:ss 4字节 用来存储时间戳。格式为“yyyymmddhhmmss”，显 示为"yyyy-mm-dd hh:mm:ss"。在msql中占4个字节 select CHARACTER_LENGTH(s1),length(s1),CHARACTER_L ENGTH(s2),length(s2) from t2; -- 114688 create table t3( c char(5) ); insert t3 value('d2a'); select * from t3; 存储范围为: 1970-01-01 00:00:01 UTC -- 2038-01- 19 03:14:07

注意：它存储的是由格林尼治时间1970年1月1日到 当前时间的秒数，但是显示为"yyyy-mm-dd hh:mm:ss"。

year用来存储年份数据。格式为“yyyy”，在mysql中 占1个字节 存储范围为：1901 -- 2155

1.4 enum set 复合类型

enum 枚举 set集合

-- null 男 女 a
gender enum('男','女') default '男'
age enum(10,20,30,40) default 20
create table user(
id int unsigned auto_increment,
name varchar(30),
gender enum('男','女','保密') default'男'
, /*gender字段数据只能填写 男 女 保密 其中一
个*/
depart set('高等数学','英语','线性代数','离散数学'),
/* 可以不填写，填写必须是集合中的元素，可
是任意个 */
primary key(id)
)engine=innodb default charset=utf8;
-- 插入正常
insert into user value(null,'李四','女','英语,英语,线性代数');
-- 插入数据错误
insert into user value(null,'李丰','保密','英语,英语,线性代数,大学语文');
select * from user;

1.5 bit 布尔

bit 位类型，只能存储1 或 0 其实mysql底层使用的 是　tinyint unsigned

1.6 json

json是一种特殊字符串，轻量级的数据格式，不同程序 不同语言数据交换格式。 JSON：JavaScript Object Notation, JS 对象简谱)是一种 轻量级的数据交换格式。它基于ECMAScript (欧洲计算 机协会制定的js规范)的一个子集，采用完全独立于编程 语言的文本格式来存储和表示数据。简洁和清晰的层次 结构使得 JSON 成为理想的数据交换语言。 易于人阅读 和编写，同时也易于机器解析和生成，并有效地提升网 络传输效率。

mysql 从5.7开始增加了json新的数据类型，此类型非常 灵活。

tinyint int bigint double(p,s) numeric(p,s) char(64) varchar(255) text longtext date datetime enum set bit json（mysql 5.7） 二进制类型（字节类型）用于存储文件

1.7 binary 二进制

binary(255)

varbinary()

blob 65k

TinyBlob 最大 255 相当于 binary(255) b

lob 最大 65K MediumBlob 最大 16M

LongBlob 最大 4G

2.表操作

数据类型：bit tinyint int bigint float double decimal(8,1) datetime date enum set char varchar json blob

1. 建立表 create table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]
tbl_name
(create_definition,...)
[table_options]
[partition_options]
use d3;
select
version(),database(),user(),@@datadir,@@bas
edir;
-- 查看当前数据库有多少表
show tables;
show tables from d3;
show tables from mysql;
-- 建立表
create table 表名(
属性名（列名，字段名) 类型 约束
default
not null
unsigned zerofill
auto_increment
unique,
...
primary key(id)
)engine=innodb default charset=utf8
auto_increment=202001;
use db;
-- 列出查看当前数据库的所有表和视图
show tables;
show tables from mysql;
-- 建立表
create table if not exists t(t int);
create table stu(
sno int unsigned auto_increment comment
'学生的学号'
,
sname varchar(30) not null comment '姓
名'
,
score smallint unsigned default 0
comment '成绩'
,
saddress varchar(255) comment '学生家庭地
址'
,
primary key(sno) comment 'sno为PK'
)engine = innodb auto_increment=20220101
default charset utf8 comment '学生信息表';
-- 查看建立表的语句
show create table stu;
-- 查看服务器所有存储引擎
show engines;
-- 字符集查看
show charset like 'gb%';
show charset like 'utf%';
show variables like 'char%';

2. 删除表 drop table

DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
-- 删除表
use d1;
show tables;
create table t1(t int);
create table t2(t int);
create table t3(t int);
drop table t1;
-- 判断删除，如果存在就删除，不存在，报警告信息
drop table if exists `t2`; -- Unknown
table 'd1.t2'
show warnings;
-- 当前在d1数据库上，删除d3数据库中的表t
drop table if exists d3.t;
-- 删除一堆表
drop table if exists t1,t2,t3,t4,t5;

3. 修改表 alter table

-- alter table 修改表结构（表名，列名，列的数据类
型，相关约束）
use d3;
show tables;
-- 查看表结构
desc tuser;
describe tuser;
show columns from tuser;
describe table tuser;
-- 修改表名
alter table tuser rename userinfo;
rename table userinfo to tuser;
-- 移动表表tt2从db2库移动当前数据库为tt3表
rename table db2.tt2 to tt3;
/*
RENAME TABLE
tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_*/
drop table if exists
t,t1,t3,s,stu,user,tuser,tteacher,dept,empl
oyees,student;
create table t1(t int);
create table t2(t int);
create table t3(t int);
-- 修改多个表的名称
rename table
t1 to tt1,
t2 to tt2,
t3 to tt3;
-- 查看表结构
desc tt1;
-- 查看建立数据库的语句
show create database mysql;
-- 查看建立表的语句
show create table tt1;
rename table tt1 to student;
-- 增加字段类型
alter table student add column id int
unsigned auto_increment primary key;
alter table student add sname varchar(15)
not null;
alter table student add age tinyint
unsigned default 18;
alter table student add gender
enum('男'
,
'女') default '男' after sname;
alter table student add address
varchar(255) first;
-- 修改列的类型 及 位置
alter table student modify address
varchar(255) after age;
-- 修改列名
alter table student rename column address
to saddr;
-- 修改列名 类型 及 位置
alter table student change saddr address
varchar(100) after age;
-- 复制表结构或建立一个空表
create table stu like student;
-- 根据查询的内容建立一个表，此表没有相关的约束，不
推荐使用
create table t1 as select 20,
'jack';
-- 删除字段
alter table student drop column t;
-- 修改表名 student cf_student cf_user
cf_admin cf_car
rename table w_student to cf_student;
alter table cf_student rename st;
-- 查看表结构
describe st;
desc st;
-- 查看建立表语句
show create table st;
CREATE TABLE `st` (
`id` int unsigned NOT NULL
AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`gender` enum('男'
,
'女') DEFAULT NULL,
`course` set('计算机英语'
,
'高数'
,
'离散数
学'
,
'线性代数') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT
CHARSET=utf8mb3
-- 增加一列（字段）
-- 删除一列（字段）
-- 修改列名
-- 修改列的数据类型 int bigint varchar int
varchar(30) varchar(5)
select * from st;
-- 增加一列
alter table st add address varchar(255) not
null default '郑州市';
alter table st add age tinyint unsigned not
null default 18 first;
alter table st add age tinyint unsigned
default 0 after name;
-- 删除一列
alter table st drop address;
alter table st drop column age;
-- 修改列名 name StudentName sname
alter table st rename column name to sname;
alter table st change sname name
varchar(30) first;
alter table st change name name varchar(30)
after id;
-- 修改列的数据类型及精度
alter table st modify name varchar(100)
after address;
alter table st modify name varchar(50)
after id;
-- 复制表的结构
-- create table t6(t int);
create table st2 like st;
show create table st2;
select * from st2;
-- 插入数据
insert into st2 select * from st;
show tables;
-- 根据查询的结构，建立一个没有相关约束的表,可以直接
插入数据
create table st3 as select id,name from st;
create table st4 as select id,name,address
from st where name like '李%';
select * from st3;
show create table st3;
drop table st3;
select * from st4;

3.表约束

1.PK（主键约束）

在关系数据库，一个表中，只能有一个主键(Primary Key)，有些数据库没有pk，系统报出错误。在myql数据 库，建立表时，可以有主键，也可以没有主键（推荐建 立表时必须有主键）。 主键列，在插入数据时，必须填写，不能为null，不能 重复。

drop table if exists
t1,tt2,tt3,student,stu;
-- 建立表 使用主键
create table tt1(
t int primary key
);
create table tt2(
t int,
primary key(t)
);
create table tt3(
t int unsigned auto_increment primary
key
);
create table tt4(
t char(5)
);
insert tt4 values(1),(2),(2),(3),(2),(5);
select * from tt4;
-- 有重复数据，将不法增加主键约束
alter table tt4 add primary key(t);
-- 删除主键
alter table tt4 drop primary key;
-- 建立复合主键，此表还是只有一个主键，在系统数据库
mysql.user表中的复合主键。
create table tt5(
id int unsigned,
name varchar(30) not null,
age tinyint unsigned default 18,
primary key(id,name)
);
-- 表约束
-- 主键PK约束（索引）唯一约束 非空 默认值
show tables;
create table wx_student(
sno char(6) primary key,/*主键 PK 不能
空，不能重复，一个表只有有一个主键*/
sname varchar(30) not null,
sphone char(11) unique ,/*唯一约束，不能重
复*/
sage tinyint unsigned default 18,
saddr varchar(255),
key(sname)
);
-- 定义uuid自动主键
create table ws_goods(
id char(36) default (uuid()) primary
key,
name varchar(50) not null,
price decimal(10,1)
);
insert into ws_goods(name,price)
values(uuid(),
'笔记本'
,3000),('台式机'
,5500);
insert into ws_goods(id,name,price)
values(uuid(),
'服装'
,260),(uuid(),
'台式机
2'
,3500);
select * from ws_goods;
select uuid_short(),uuid();
create table t2(t bigint unsigned);
drop table t2;
-- 99657422712340480
insert into t2 value(99657422712340480);
select
uuid_short(),uuid_short(),uuid_short(),uuid
_short();

从mysql v8.0.13开始，可以使用表达式作为字段的默认值：DEFAULT子句中指定的默认值可以是文字常量或表达式。除一个例外，将表达式默认值括在括号内，以将其与文字常量默认值区分开。

唯一的例外是，对于 TIMESTAMP和 DATETIME列， 您可以将CURRENT_TIMESTAMP函数指定为默认函 数，而不用括号括起来。

2.FK（外键约束）

FK foreign key 外键约束，一般涉及多个表，关联约束 形式，在mysql，表的存储引擎为innodb时才可以使用 外键。

3.unique 唯一约束

create table stu(
id int unsigned auto_increment,
name varchar(15) unique, /* 唯一约束 ，
可以不填写，如果填写，不能为重复*/
age tinyint unsigned default 18, /* 默认
值 */
gender enum('男'
,
'女') default '男'
,
address varchar(255) not null, /* 必须填
写*/
primary key(id)
);

4.not null

create table stu(
id int unsigned auto_increment,
name varchar(15) unique, /* 唯一约束 ，
可以不填写，如果填写，不能为重复*/
age tinyint unsigned default 18, /* 默认
值 */
gender enum('男'
,
'女') default '男'
,
address varchar(255) not null, /* 必须填
写*/
primary key(id)
);

5.default

create table stu(
id int unsigned auto_increment,
name varchar(15) unique, /* 唯一约束 ，
可以不填写，如果填写，不能为重复*/
age tinyint unsigned default 18, /* 默认
值 */
gender enum('男'
,
'女') default '男'
,
address varchar(255) not null, /* 必须填
写*/
primary key(id)
);