数据库实践LAB大纲 01 管理

原创已于 2023-02-08 15:28:39 修改 · 663 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #oracle

于 2023-02-08 13:27:05 首次发布

DataBase 专栏收录该内容

25 篇文章

订阅专栏

文章目录

基本操作
管理表记录
MySQL运算符
增添表记录
- 修改表记录
- 删除表记录

基本操作

创建数据库

-- CREATE DATABASE 新数据库名;
create database bookstore;

-- 设置字符集
alter database bookstore character set utf8;

-- 文件存放路径
show variables like ‘datadir’;

-- 查看数据库的相关信息
show create database bookstore;

-- 选择数据库
use bookstore;

-- 删除数据库
drop database bookstore;

-- 创表
/*
CREATE TABLE [IF NOT EXISTS] 表名
( 字段名1 数据类型 [约束]]
[ ,字段名2 数据类型 [约束]]
……
[ ,字段名n 数据类型 [约束]]
[其他约束条件]
)[其他选项];

*/
create table users
(
uid int not null primary key auto_increment,
name varchar(20) not null unique,
pwd varchar(20) not null,
sex char(2)
)engine= InnoDB;

-- 常用的关键字
/*
AUTO_INCREMENT：用于设置整数类型字段的自动增量属性。AUTO_INCREMENT字段必须被索引，而且必须为NOT NULL。每个表最多只能有一个字段具有AUTO_INCREMENT属性

DEFAULT：表中添加新行时给表中某一字段指定的默认值。使用DEFAULT定义，一是可以避免NOT NULL值的数据错误；二是可以加快用户的输入速度

NOT NULL：指定NOT NULL属性的字段，不能有NULL值

UNSIGNED：表示该值不能为负数

PRIMARY KEY，UNIQUE，FOREIGN KEY（仅InnoDB）

*/

-- 查看数据库表结构
describe users;
desc users;

-- 修改表
-- ALTER TABLE 表名 ACTION [,ACTION]… 支持一条ALTER TABLE语句带多个动作，以逗号分隔
/*
添加、修改、删除表属性字段
添加、修改、删除表约束条件
删除索引
修改表名
修改表的存储引擎
*/

-- DROP TABLE [IF EXISTS] 表名1 [,表名2,…];
drop table A

管理表记录

每个字段都有数据类型 —— 数据存储格式、约束、有效范围
合适的type —— 节省空间，提升性能

数值类型（包括整数类型和小数类型）、字符串类型、日期时间类型、复合类型以及二进制类型

整数

type	length
TINYINT	1字节
SMALLINT	2字节
MEDIUMINT	3字节
INT（INTEGER）	4字节
BIGINT	8字节

默认int正负数都可以表示

若只表达非负整数，可使用unsigned修饰

age tinyint unsigned

可指示宽度

int(8) -- 若数值宽度＜ 8 在前方用空格填满宽度

数字位数不够时需要用“0”填充时，则可以使用关键字zerofill

int(8) zerofill -- 若数值宽度＜ 8 在前方用0填满宽度

大于或等于指定显示宽度，按数的本来大小展示

create的应用

CREATE DATABASE type_test;
USE type_test;

create table int_test(
	int_field1 int(6), -- 组内分割用逗号
	int_field2 int(8) UNSIGNED,
	int_field3 int(8) ZEROFILL -- 最后一个属性请不要加逗号
	);
desc int_test;

插入数据后可查询

insert into int_test values(1, 2, 3);
select * from int_test;

在这里插入图片描述

可以利用alter table .. modify 进行类型转换

ALTER TABLE int_test MODIFY int_field1 INT ( 6 ) ZEROFILL;
ALTER TABLE int_test MODIFY int_field2 INT ( 3 ) ZEROFILL;
SELECT
	* 
FROM
	int_Test;

在这里插入图片描述

大于或等于指定显示宽度

在这里插入图片描述

AUTO_INCREMENT

作用：产生 唯一标识符 or 顺序值，可用此熟悉（仅适用整数类型）

要求

一个表最多一个AUTO_INCREMENT字段
AUTO_INCREMENT字段应为NOT NULL 定义为 PRIMARY KEY 或 UNIQUE

小数

小数类型有两种：浮点数和定点数

浮点数：
FLOAT DOUBLE

定点数：
DECIMAL

字符串形式存放，更精确
表示货币等高精度数据

可在类型后加上(M, D)表示

M 代表一共显示M位数字
D 表示小数点后的位数
在 D 后的数值需要按照 D 来四舍五入
不指定(M, D)
- 浮点数：实际值存储
- 定点数：整数位默认10，小数位默认0

create table number_test(
	float_field float,
	double_field double,
	decimal_field decimal
);
insert into number_test values(1234.56789, 1234.56789, 1234.56789);
insert into number_test values(1.234, 1.234, 1.234);
select * from number_test;

在这里插入图片描述

FLOAT 和 DOUBLE的转换

float类型默认大小为24位数字，精度大约7位数字。当设置M大小大于24时，自动转换为DOUBLE类型；同时设置M和D时不进行自动转换

alter table number_test modify float_field float(5,1);
alter table number_test modify double_field double(5,1);
alter table number_test modify decimal_field decimal(5,1);

在这里插入图片描述

字符串

CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

type	length
CHAR(n)	0-255
VARCHAR(n)	0-255 或者 0-65535
TINYTEXT	0-255
TEXT	0-65535
MEDIUMTEXT	0-16777215
LONGTEXT	0-4294967295

区别

CHAR 定长
剩下的都是变长
CHAR存储时删除尾部空格
VARCHAR会保留尾部空格

create table string_test(char_field char(8),varchar_field varchar(8));
insert into string_test values('test ','test ');
select * from string_test;
update string_test set char_field=concat(char_field,'+'),varchar_field= 
concat(varchar_field,'+');

在这里插入图片描述

日期时间

日期时间类型包括：DATE、TIME、DATETIME、TIMESTAMP和YEAR。

DATE表示日期，默认格式为YYYY-MM-DD；
TIME表示时间，默认格式为HH:MM:SS；DATETIME和
TIMESTAMP表示日期和时间，默认格式为YYYY-MM-DD HH:MM:SS；
YEAR表示年份

type	min	max
DATE	1000-01-01	9999-12-31
TIME	-838:59:59	838:59:59
DATETIME	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	1970-01-01 08:00:01	2037年某时刻
YEAR	1901	2155

PS：

YEAR可以表示2位or四位，默认四位

在4位格式中，允许值的范围为1901～2155。
在2位格式中
- 取值为70～99时，表示从1970年～1999年
- 取值为01～69时，表示从2001年～2069年

DATETIME与TIMESTAMP都包括日期和时间两部分
- TIMESTAMP类型与时区相关
- 而DATETIME则与时区无关
一个表中定义了两个类型为TIMESTAMP的字段

(注意，这个特性是某些版本特有的)
First one 默认为 CURRENT_TIMESTAMP
Second one 默认 0000-00-00 00:00:00

create table year_test(year_field year);
insert into year_test values(2155);
insert into year_test values(69);
select * from year_test;

在这里插入图片描述

create table datetime_test(
datetime_field datetime,
timestamp_field1 timestamp default CURRENT_TIMESTAMP,
timestamp_field2 timestamp default "0000-00-00 00:00:00");
desc datetime_test;

insert into datetime_test  values(now(), now(), now());
select * from datetime_test;

在这里插入图片描述

复合类型

ENUM枚举类型和 SET集合类型

ENUM

只允许从集合中取得某一个值
最多可以包含65535个元素

SET

允许从集合中取得多个值
最多可以包含64个元素

create table enum_test(sex enum('男','女'));
insert into enum_test values('女');
insert into enum_test values('男');
insert into enum_test values(NULL);
insert into enum_test values(1);
select * from enum_test;

在这里插入图片描述

create table set_test(hobby set('旅游','听音乐','看电影','上网','购物'));
insert into set_test values('看电影,听音乐');
insert into set_test values('上网');
insert into set_test values(NULL);
select * from set_test;

在这里插入图片描述

二进制类型

BINARY、VARBINARY、BIT(按位为单位存储)、TINYBLOB、MEDIUMBLOB和LONGBLOB

除了BIT其他都是按字节为单位存储

type	length
BINARY(n)	0-255
VARBINARY(n)	0-65535
BIT(n)	0-63
TINYBLOB	0-255
BLOB	0-65535
MEDIUMBLOB	0-16777215
LONGBLOB	0-4294967295

MySQL运算符

算术运算符

$*\ /\ \%$

补充

DIV \，前者表示整除，后者返回实际运算

MOD %,前者返回整数，后者符号可正可负

比较运算符

数字/日期/字符串用

=
!= / <>
<=> （NULL也可以使用）
<
>
>=

NULL用

<=>
is null
is not null

范围

between a and b 可比较范围内
in 在集合内
like 通配符匹配
regexp 正则表达式匹配

example

varchar_field like '%Li’表示当varchar_field中的字符串以“Li”结尾时，则返回值为1，否则返回值为0
varchar_field regexp 'Li$'表示当varchar_field中的字符串以“Li”结尾时，则返回值为1，否则返回值为0。
varchar_field regexp '^Mr’表示当varchar_field中的字符串以“Mr”开头时，则返回值为1，否则返回值为0

逻辑运算符

NOT !
AND &&
OR ||
XOR

操作数有NULL的时候，所有的返回值是NULL

例外：

1 or null 返回 1
0 and null 返回 0

¤ 位运算符

按位

移位

<< 整型数据在内存中的二进制补码向左移出指定的位数，向左移出的位数丢弃，右侧添0补位
>> 将整型数据在内存中的二进制补码向右移出指定的位数，向右移出的位数丢弃，左侧添0补位

字符集

mySQL默认latin1（西欧ISO_8859_1字符集的别名）
支持 utf8、gbk和big5等

排序规则命名方法：

开头：字符排序规则对应的字符集
中间：国家名（或general）
结尾：ci cs bin

ci ：大小写不敏感
cs ：大小写敏感
bin：按二进制编码值进行比较

show character set查看例支持的字符集、字符集的默认排序规则、字符集占用的最大字节长度等信息

show variables like 'character%' 可以查看
当前服务实例使用的字符集信息

在这里插入图片描述

修改字符集的方法

MySQL服务并生成服务实例后

character_set_server将使用my.ini配置文件中[mysqld]选项组中character_set_server参数的值
character_set_client character_set_connection character_set_results的字符值将使用my.ini配置文件中[mysqld]选项组中default_character_set参数的值

修改my.ini配置文件并重启
set命令设置
set names设置

（一次性将设置好character_set_client、character_set_connection以及character_set_results的参数值）

连接MySQL服务器时指定字符集

mysql --default-character-set=utf8 -h 127.0.0.1 -u root -p 相当于连接服务器后执行命令set names=utf8

增添表记录

INSERT

/*
INSERT [INTO] table_name [(column_name,…)]
VALUES ({expr|DEFAULT}, ...), (...), …
|SET column_name={expr|DEFAULT},
…
*/

-- table_name 表名
-- column_name 字段名
	# 省略表示给全部字段插入数据
	# 只插入部分需要指出字段名
		# 未指定字段，优先 defalut， 然后null， 两者都没有报错
		# 自增型会自动生成编号
		# timestamp 会自动填充当前日期时间
-- values 包含字段需要插入的数据清单
	# 顺序要和字段对应
	# 省略字段 所有字段都要给出对应值
	# 常量/变量/表达式/null，类型要一致
-- set 给指定字段赋值

例子

create table course(
course_id int auto_increment primary key,
course_name varchar(20),
course_hours int default 72,
teacher_id char(10) not null,
constraint course_teacher_fk foreign key(teacher_id) references teacher(teacher_id)
);
insert into course values(NULL,'C程序设计',default,'0412893402');
insert into course(course_name,teacher_id) values('Java程序设计','0412893401');
insert into course values(NULL,'MySQL数据库',60,'0412893403');

在这里插入图片描述

auto_increment字段插入数据，可以插入NULL或省略这个字段，插入值自动填充下一个自增值
向默认值约束字段插入数据时，字段值可以使用default关键字或省略该字段，此时插入的值为该字段的默认值。
constraint .. foriegn key(..) references ..(..)是外键约束关系。 course表中的teacher_id字段值要来自于表teacher中teacher_id字段

INSERT语句一次向表中插入多条记录

truncate course;
insert into course(course_name,course_hours,teacher_id) values
('C程序设计',default,'0412893402'),
('Java程序设计',default,'0412893401'),
('MySQL数据库',60,'0412893403')

注意，删除了先前的数据，再加入新数据的时候，AUTO_INCREMENT不会填被删除的数据而是继续加

在这里插入图片描述

SELECT

/*
REPLACE [INTO] table_name [(column_name,…)]
VALUES ({expr|DEFAULT}, ...), (...), …
|SET column_name={expr|DEFAULT},
*/

/*
REPLACE [INTO] table_name[(column_name,…)]
SELECT (column_name,…) from source_table_name where conditions
*/

与 INSERT的区别

使用REPLACE语句添加记录时，if 新记
录的主键值或者唯一性约束的字段值与已有记录相同时，then 删除后再添加新记录。
INSERT则直接报错 —— 插入失败

修改表记录

UPDATE

UPDATE table_name 
SET column_name={expr|DEFAULT},…
[where condition]
-- 省略where对所有记录修改
-- set子句用于指出记录中需要修改的字段及其取值

删除表记录

DELETE或TRUNCATE语句

/*
DELETE from table_name
[WHERE condition]
*/

-- TRUNCATE语句清空表记录
TRUNCATE [table] table_name
-- 类似DELETE from table_name

turncate 会 重新设置自增型字段起始值，delete不会

select auto_increment from information_schema.tables where 
table_name='course_copy';
-- 语句的作用是查询course_copy表中自增字段的起始值。