MySQL-数据库、表和MySQL数据类型

Neo丶

已于 2023-03-20 12:21:51 修改

阅读量385

点赞数

文章标签： mysql java 数据库

于 2022-09-27 12:35:42 首次发布

本文链接：https://blog.csdn.net/ego_ekko/article/details/127036789

版权

数据库

MySQL三层结构

1）安装MySQL数据库，就是在主机安装一个数据库管理系统DBMS（database manage system），这个管理程序可以管理多个数据库；

2）一个数据库中可以创建多个表，已保存数据（信息）；

3）数据库管理系统、数据库和表的关系：（MySQL数据库-普通表的本质仍然是文件）

客户端命令终端(Dos) SQLyog java		MySQL数据库
		数据库管理系统（DBMS）	数据库DB1	表1
	↔ 端口3306			表2
			数据库DB2	表1
				表2
			数据库DB3	...
		↑ mysqld.exe程序等	↑ data文件	↑ db01...文件

数据在数据库的存储方式

1）数据以表的方式存储在数据库中，列(column)，行(row)；

2）表的一行称之为一条记录（在java程序中，一行记录使用对象表示）。

SQL语句分类

1）DDL数据定义语句【create 表，库...】

2）DML数据操作语句【增加 insert，修改 update，删除delete】

3）DQL数据查询语句【select】

4）DCL数据控制语句【管理数据库：用户授权grant，权限撤回revoke】

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name
        [create_specification[,create_specification]...]

create_specification：
[DEFAULT]CHARACTER SET charset_name
[DEFAULT]COLLATE collation_name

1)CHARACTER SET ：指定数据库采用的字符集，如果不指定字符集，默认为utf8；

2）COLLATE：指定数据库字符集的校对规则：常用的utf8 bin（区分大小写）、utf8_general_ci(不区分大小写)，默认的是utf8_general_ci。

#演示创建数据库
#创建一个名称为pero_db01的数据库。【图形化和指令 演示】
#使用指令创建数据库
CREATE DATABASE pero_db01;
#使用指令删除数据库
DROP DATABASE pero_db01;
#创建一个使用utf8字符集的pero_db02数据库
CREATE DATABASE pero_db02 CHARACTER SET utf8;
#创建一个使用utf8字符集，并带校对规则的pero_db03数据库
CREATE DATABASE pero_db03 CHARACTER SET utf8 COLLATE utf8_bin;
#校对规则 utf8_bin 区分字母大小写 默认utf8_general_ci 不区分大小写
#在db03建表，如果表中的字符集和核对不做修改，默认与数据库的字符集和核对为准
#查询的sql语句,select 查询，*表示查询所有字段，from 表示从哪个表查，
#where表示从哪个字段查，name='tom'表示查询名字是tom的（区分大小写只能查询一条记录）
SELECT * 
		FROM t1 
		WHERE NAME = 'tom';
#查询db02中的表数据(选择pero_db02数据库），由于不区分大小写，查询结果为两条
#一条为tom，另一条为Tom

查看和删除数据库

显示数据库语句：
SHOW DATABASES

显示数据库创建语句：
SHOW CREATE DATABASE db_name

数据库删除语句：
DROP DATABASE [IF EXISTS] db_name

#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES;
#查看创建的pero_db01数据库定义信息
SHOW CREATE DATABASE pero_db01;
#在创建数据库和表的时候。为了规避关键字可以使用反引号解决例如：`CREATE`
CREATE DATABASE 	`int`;
#删除创建的pero_db01数据库【删除数据库前一定要慎重，确定是否有需要的数据在数据库中】
DROP DATABASE `int`;
DROP DATABASE `pero_db01`;

备份和恢复数据库

★备份数据库（在Dos命令中执行）

mysqldump -u 用户名 -p 密码 -B 数据库1 数据库2 ...数据库n > (路径)文件名.sql

★恢复数据库(进入mySQL命令行后执行）

source (全路径)文件名.sql

★备份库中的表

mysqldump -u 用户名 -p 数据库表1 表2 ...表n > (路径)文件名.sql

# database03.sql 备份pero_db02 和pero_db03库中的数据，并恢复

#备份	需要在Dos下执行mysqldump指令，
#该指令程序可以在mysql安装目录\bin下找到
#备份文件就是对应的sql语句
mysqldump -u root -p -B pero_db02 pero_db03 > d:\\back.sql

#删除数据库
DROP DATABASE pero_db02;
DROP DATABASE pero_db03;

#恢复数据库	要进入到MySQL命令行才能执行
source d:\\back.sql
#恢复方法二：直接将备份文件中的内容拷贝到查询编辑器中执行，也可恢复数据

#备份库中的表
mysqldump -u root -p pero_db02 t1 > d:\\tableback.sql

表

创建表基本语法

CREATE TABLE table_name(

    field1 datatype,
    field2 datatype,
    field3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎
field:指定列名 datatype：指定列类型（字段类型）
character set：如果不指定则为所在数据库字符集
collate：如果不指定则为所在数据库校对规则
engine：引擎

#指令创建表
CREATE TABLE `user` (
		id INT,
		`name` VARCHAR(255),
		`password` VARCHAR(255),
		`birthday` DATE)
		CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

MySQL常用数据类型（列类型）

★在能够满足需求的情况下，尽量选择占用空间小的类型。

分类	数据类型	说明
数值类型	BIT(M) TINYINT [UNSIGNED] 占1个字节 SMALLINT [UNSIGNED] 2个字节 MEDIUMINT [UNSIGNED] 3个字节 INT [UNSIGNED] 4个字节 BIGINT [UNSIGNED] 8个字节 FLOAT [UNSIGNED] DOUBLE [UNSIGNED] DECIMAL(M,D) [UNSIGNED]	位类型。M指定位数，默认值为1，范围1~64 带符号的范围是-128~127。无符号0~255。默认为有符号带符号 -2^15~(2^15)-1，无符号 0~(2^16)-1 带符号 -2^23~(2^23)-1，无符号 0~(2^24)-1 带符号 -2^31~(2^31)-1，无符号 0~(2^32)-1 带符号 -2^63~(2^63)-1，无符号 0~(2^64)-1 占用四个字节空间表示比float精度更大的小数，占用空间8个字节定点数M指定长度，D表示小数点的位数
文本、二进制类型	CHAR(size) VARCHAR(size) BLOB LONGBLOB TEXT LONGTEXT	固定长度字符串最大255 可变长度字符串 0~65535【(2^16)-1】二进制数据BLOB 0~(2^16)-1 LONGBLOB 0~(2^32)-1 文本Text 0~2^16 LONGTEXT 0~2^32
时间日期	DATE/DATETIME/TimeStamp	日期类型(YYYY-MM-DD)(YYYY-MM-DD HH:MM:SS)， TimeStamp表示时间戳，它可以用自动记录inset、update操作的时间

1）整型

#整形的使用(TINYINT)
#如果没有指定unsinged,则TINYINT是有符号的（-128~127）
CREATE TABLE `t3` (
		`id` TINYINT);

#insert into t3 values(-129);超出范围
INSERT INTO `t3` VALUES(-125);
#insert into t3 values(128);超出范围
INSERT INTO `t3` VALUES(125);

DELETE FROM `pero_db02`.`t3` WHERE `id`=125;

INSERT INTO `t3` VALUES(123);
INSERT INTO `t3` VALUES(111);
DELETE FROM `pero_db02`.`t3` WHERE `id`=123;
DELETE FROM `pero_db02`.`t3` WHERE `id`=111;

INSERT INTO `t3` VALUES(26);
DELETE FROM `pero_db02`.`t3` WHERE `id`=26

DELETE FROM `pero_db02`.`t3` WHERE `id`=-125;
DELETE FROM `pero_db02`.`t3` WHERE `id`=125;
DELETE FROM `pero_db02`.`t3` WHERE `id`=123;
DELETE FROM `pero_db02`.`t3` WHERE `id`=111;
DELETE FROM `pero_db02`.`t3` WHERE `id`=26;

CREATE TABLE t4(
		`id` TINYINT UNSIGNED, 
		`name` VARCHAR(255),
		`age` SMALLINT UNSIGNED,
		`birthday` DATETIME
		);


INSERT INTO `pero_db02`.`t4` (
		`id`,`name`,`age`,`birthday`) 
				VALUES('2','jake','16','1995-12-01 23:30:00');
SELECT * FROM t4;

BIT类型

#BIT类型的使用(1~64)
#bit(m) m在1~64z之间
#添加数据范围
#显示按照bit
CREATE TABLE t05 ( num BIT(8));
SELECT * FROM t05;
INSERT INTO `pero_db02`.`t05`VALUES (1);
#按位显示
SELECT * FROM t05;
INSERT INTO `pero_db02`.`t05` VALUES(3);
SELECT * FROM t05;
INSERT INTO `pero_db02`.`t05` VALUES(255);
SELECT * FROM t05;
#insert into `pero_db02`.`t05` values(256);(超过设定的位数，m=8表示1个字节0~255)
#查询时仍然可以按照数值来查询
SELECT * FROM t05 WHERE num = 3;

数值型（小数）的基本使用

#使用float/double[unsigned]
#decimal[m,d][unsigned] ，m是小数位数（精度）的总数，d是小数点（标度）后面的位数
#如果d是0，则值没有小数点或分数部分。M最大为65，D最大为30.
#如果D被省略则默认为是0，如果m被省略则默认为10。
#如果要求精度较高，建议使用Decimal
CREATE TABLE t06(num DECIMAL(12,10) UNSIGNED);
SELECT * FROM t06;
INSERT INTO t06 VALUES(12.1256515652);
SELECT * FROM t06 WHERE num=12.1256515652;
DELETE FROM t06 WHERE num='12.1256515652';
SELECT * FROM t06;

CREATE TABLE t07 (
		num1 FLOAT,
		num2 DOUBLE,
		num3 DECIMAL(15,12) UNSIGNED);
SELECT * FROM t07;
INSERT INTO t07 (num1,num2,num3) 
		VALUES(88.1235,22222.354465465,325.165165166515);
SELECT * FROM t07;

字符串的基本使用

#CHAR(size) 固定长度字符串最大255字符
#VARCHAR（size）可变长度字符串最大65535-3=65532(1-3个字节用于记录字段大小)
#【utf8编码最大65532/3=21844字符 】
#注释快捷键 ctrl + shift +c，注释取消ctrl + shift +r
-- 如果表的编码是utf8 varchar(size) size = (65535-3)/3 = 21844
-- 如果表的编码是gbk varchar(size) size = (65535-3)/2 = 32766
CREATE TABLE t09(
		`name` CHAR(255));
SELECT * FROM t09;

CREATE TABLE t10(
		`name` VARCHAR(21844) CHARSET utf8);
SELECT * FROM t10;
DROP TABLE t10;

CREATE TABLE t11(
		`name` VARCHAR(32766) CHARSET gbk);
SELECT * FROM t11;

DROP TABLE t11;

#演示字符串使用细节
-- 1.细节一
-- char(4)，这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算
-- varchar(4)，这个4表示字符数，不管是字母还是中文都是定义好的表的编码来存放数据
CREATE TABLE t12(
		`name` VARCHAR(4) CHARSET utf8);
INSERT INTO t12 ( `name` ) VALUES ('司马仲达' );
SELECT * FROM t12;
DROP TABLE t12;

CREATE TABLE t13(
		`name` CHAR(4) CHARSET gbk);
INSERT INTO t13(`name`) VALUES ('诸葛孔明');
SELECT * FROM t13;
DROP TABLE t13;

-- 细节二
-- char(4)是定长（存储空间固定大小），即使插入'aa'，也会占用分配的四个字符；
-- varchar(4)是变长，如果插入了'aa'，实际占用空间大小不是4个字符，
-- 而是按照实际占用空间来分配（varchar本身还需要占用1~3个字节来记录存放内容长度）；

-- 细节三
-- 如果数据定长，推荐使用char，比如md5的密码、邮编、手机号、身份证号等；
-- 如果一个字段的长度是不确定的，需要使用varchar，比如留言、文章；

-- 细节四
#如果varchar不够用，可以考虑使用mediumtext或者longtext,
#如果想简单点，可以直接使用text
-- 可以将text列视为varchar列，注意Text不能有默认值，大小0~2^16字节
-- mediumtext 0~2^24字节 或者 longtext 0~2^32字节
CREATE TABLE t14 (
		content1 TEXT, 
		content2 MEDIUMTEXT, 
		content3 LONGTEXT);
INSERT INTO t14 VALUES ('pero','jake','tom');
SELECT * FROM t14;

日期类型的基本使用

CREATE TABLE birthday(
        t1 DATE, 
        t2 DATETIME, 
        t3 TIMESTAMP 
        NOT_NULL_DEFAULT 
        CURRENT_TIMESTAMP ON UPDATE
        CURRENT_TIMESTAMP);
#timestamp时间戳
#mysql>INSERT INTO birthday (t1,t2)
#VALUES('2022-11-11','2022-11-11 10:10:10');

#日期类型的细节说明
TimeStamp在Insert和update时，自动更新

#创建一张表包含内容date ,datetime ,timestamp
CREATE TABLE t15(
		`birthday` DATE , -- 年月日
		`job_time` DATETIME, -- 年月日 时分秒
		`login_time` TIMESTAMP
			NOT NULL DEFAULT CURRENT_TIMESTAMP 
			ON UPDATE CURRENT_TIMESTAMP); -- 登陆时间,如果要自动更新需要配置
INSERT INTO t15 (
		`birthday` ,`job_time`) 
		VALUES ('2022-11-11','2022-11-11 10:10:10');
-- 在更新t15表的某条记录后，login_time列会自动的以当前时间进行更新
INSERT INTO t15 (
		`birthday` ,`job_time`) 
		VALUES ('2022-11-12','2022-11-12 10:10:10');
SELECT * FROM t15;

创建表

-- 创建表的课堂练习
-- 字段  			属性
-- `id` 			整型
-- `name` 		字符型
-- `sex`			字符型
-- `birthday`		日期型（date）
-- `entry_date`		日期型（date）
-- `job`			字符型
-- `Salary`		小数型
-- `resume`		文本类型
CREATE TABLE t4 (
		`id` INT UNSIGNED,
		`name` VARCHAR(32),
		`sex` CHAR(2),
		`birthday` DATE,
		`entry_date` DATE,
		`job` VARCHAR(255),
		`salary` DECIMAL(14,4),
		`resume` TEXT);
INSERT INTO t4 VALUES(
		'1101','tom','男性','1996-01-20','2022-11-26',
		' 前端开发工程师','200000.1234','负责前端开发');
SELECT * FROM t4;

表的修改

#添加列
ALTER TABLE tablename
ADD    (column datatype [DEFAULT expr]
        [,column datatype]...);

#修改列
ALTER TABLE tablename
MODIFY    (column datatype [DEFAULT expr]
          [,column datatype]...);

#删除列
ALTER TABLE tablename
DROP    (column);   

-- 查看表的结构： desc 表名;   -- 查看表中所有的列
-- 修改表名：Rename table 表名 to 新表名
-- 修改表字符集：alter table 表名 character set 字符集;

-- 应用实例
-- 员工表emp的上增加一个image列，varchar类型（要求在resume后面）；
ALTER TABLE `emp` 
		ADD	 `image` VARCHAR(32)	-- 增加一列
		NOT NULL 	-- 不允许为null
		DEFAULT ' ' 	-- 默认值为空
		AFTER `resume`; 	-- 位置在resume之后
DESC `emp`;	-- 显示表结构

-- 修改job列，是其长度为60；
ALTER TABLE `emp`
		MODIFY`job` VARCHAR(60) NOT NULL DEFAULT ' ';
DESC `emp`;

-- 删除sex列
ALTER TABLE `emp`
		DROP `sex`;
DESC `emp`;

-- 表明改为employee；
RENAME TABLE `emp` 
		TO `employee`;
DESC `employee`;

-- 修改表的字符集为utf8；
ALTER TABLE `employee` 
		CHARACTER SET utf8;
DESC `employee`;

-- 列名name修改为user_name；
ALTER TABLE employee 
		CHANGE `name` `user_name` VARCHAR(32) 
		NOT NULL 
		DEFAULT ' ';
DESC `employee`;

Neo丶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-数据库、表和MySQL数据类型

1）安装MySQL数据库，就是在主机安装一个数据库管理系统DBMS（database manage system），这个管理程序可以管理多个数据库；2）一个数据库中可以创建多个表，已保存数据（信息）；3）数据库管理系统、数据库和表的关系：（MySQL数据库-普通表的本质仍然是文件）客户端命令终端(Dos)SQLyogjava数据库管理系统（DBMS）
复制链接

扫一扫