数据库实践LAB大纲 01 管理

基本操作

创建数据库

-- 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 顺序值， 可用此熟悉 （仅适用整数类型）

要求

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

小数

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

浮点数：
FLOAT DOUBLE

定点数：
DECIMAL

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

可在类型后加上(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

区别

1. CHAR 定长
2. 剩下的都是 变长
3. CHAR存储时删除尾部空格
4. 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：

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

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

2. DATETIME与TIMESTAMP都包括日期和时间两部分

   • TIMESTAMP类型与时区相关
   • 而DATETIME则与时区无关

3. 一个表中定义了两个类型为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运算符

算术运算符

$$+-\ast /\%$$

补充

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

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

比较运算符

数字/日期/字符串用

1. =
2. != / <>
3. <=> （NULL也可以使用）
4. <
5. >
6. >=

NULL用

1. <=>
2. is null
3. is not null

范围

1. between a and b 可比较范围内
2. in 在集合内
3. like 通配符匹配
4. 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

逻辑运算符

1. NOT !
2. AND &&
3. OR ||
4. XOR

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

例外：

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

¤ 位运算符

按位

1. &
2. |
3. ~
4. ^

移位

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

字符集

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

排序规则命名方法：

1. 开头：字符排序规则对应的字符集
2. 中间：国家名（或general）
3. 结尾：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参数的值

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

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

4. 连接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表中自增字段的起始值。