MySQL实用教程 第三章笔记

第三章 MySQL数据库和表

3.1 MySQL数据库

1.查看数据库:

mysql>show databases;

2.创建数据库:

mysql>create database (if not exists) xxxx;
# (返回1007: 数据库存在) , 如果加了括号内的内容不汇报错

3.修改数据库:

mysql>alter database xxxx
    ->default character set GB2312 # 默认字符集
    ->default collate GB2312_CHINESE_CI; # 校对规则

4.删除数据库:

mysql>drop datbase (if not exists) xxxx;
# (返回1008: 无法删除)

3.2 MySQL表

3.3.1 创建表

1.创建全新的表

mysql> use xscj;
Database changed
mysql> create table xs
    -> (
    -> 学号 char(6) not null primary key,
    -> 姓名 char(8) not null,
    -> 专业名 char(10) null,
    -> 性别 tinyint(1) not null default 1,
    -> 出生日期 date not null,
    -> 总学分 tinyint(1) null,
    -> 照片 blob null,
    -> 备注 text null
    -> )engine=innodb;

mysql> show tables;
+----------------+
| Tables_in_xscj |
+----------------+
| xs             |
+----------------+

mysql> describe xs;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| 学号         | char(6)    | NO   | PRI | NULL    |       |
| 姓名         | char(8)    | NO   |     | NULL    |       |
| 专业名       | char(10)   | YES  |     | NULL    |       |
| 性别         | tinyint(1) | NO   |     | 1       |       |
| 出生日期     | date       | NO   |     | NULL    |       |
| 总学分       | tinyint(1) | YES  |     | NULL    |       |
| 照片         | blob       | YES  |     | NULL    |       |
| 备注         | text       | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+

2.复制现成的表:

mysql> create table xs1 like xs; # 只拷贝结构
mysql> create table xs2 as (select*from xs); # 同时拷贝数据

3.2.2 修改表

例3.5: 在xcscj数据库的xs表中, 增加"奖学金等级"一列, 并将表中的"姓名"列删除

mysql> use xscj;
Database changed
mysql> alter table xs
    -> add 奖学金等级 tinyint null,
    -> drop column 姓名;
    
mysql> describe xs;
+-----------------+------------+------+-----+---------+-------+
| Field           | Type       | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| 学号            | char(6)    | NO   | PRI | NULL    |       |
| 专业名          | char(10)   | YES  |     | NULL    |       |
| 性别            | tinyint(1) | NO   |     | 1       |       |
| 出生日期        | date       | NO   |     | NULL    |       |
| 总学分          | tinyint(1) | YES  |     | NULL    |       |
| 照片            | blob       | YES  |     | NULL    |       |
| 备注            | text       | YES  |     | NULL    |       |
| 奖学金等级      | tinyint    | YES  |     | NULL    |       |
+-----------------+------------+------+-----+---------+-------+

1.修改表结构

mysql> alter table xs
    -> add 姓名 char(8) not null after 学号,
    -> drop column 奖学金等级;

mysql> desc xs;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| 学号         | char(6)    | NO   | PRI | NULL    |       |
| 姓名         | char(8)    | NO   |     | NULL    |       |
| 专业名       | char(10)   | YES  |     | NULL    |       |
| 性别         | tinyint(1) | NO   |     | 1       |       |
| 出生日期     | date       | NO   |     | NULL    |       |
| 总学分       | tinyint(1) | YES  |     | NULL    |       |
| 照片         | blob       | YES  |     | NULL    |       |
| 备注         | text       | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+

2.更改表名

mysql> use xscj;
Database changed
mysql> rename table xs1 to myxs1, xs2 to myxs2;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+----------------+
| Tables_in_xscj |
+----------------+
| myxs1          |
| myxs2          |
| xs             |
+----------------+

3.2.3 删除表

mysql> use xscj;
	-> drop table if exists myxs2;
	-> show tables;

3.3 数据类型

3.3.1 数据类型分类

1.整形(无符号加unsigned)

数据类型	字节数
tinyint	1
smallint	2
mediumint	3
int/integer	4
bigint	8

2.定点型

数据类型(定点型)	备注
decimal(m, d)	1≤m≤65, 0≤d≤30, d≤m, m默认为10, d默认为0
numeric(m, d)	同上

3.浮点型

数据类型(浮点型)	字节长度	精度
float/real	4	6位
double	8	16位

4.位型

数据类型(位型)	备注
bit(m)	1≤m≤64 默认为1

5.字符串类型

数据类型(字符个数)	占用空间	最多存放字符个数(长度字节)
char(n)	n	255(1)
varchar(n)	实际字符个数	65535(2)
tinytext	实际字符个数	255(1)
text	实际字符个数	65535(2)
mediumtext	实际字符个数	16777215(3)
longtext	实际字符个数	4294967295(4)

6.二进制串类型

数据类型	备注
binary	类似于char, 但存储的非字符串而是二进制串
varbinary	类似于varchar, 同上
tinyblob	2^8-1字节
blob	二进制对象, 存储图片视频等 2^16-1字节
mediumblob	2^24-1字节
longblob	2^32-1字节

7.日期和时间类型

数据类型	格式
date	‘yyyy-mm-dd’
datetime	‘yyyy-mm-dd hh:mm:ss’
timestamp	‘yyyy-mm-dd’ utc
time	‘hh:mm:ss’
year	‘2018’; 2018; now()

8.枚举类型

# 枚举类型 (最多65535成员)
enum(成员1, 成员2, ...)
# 集合类型 (最多64成员)
set(成员1, 成员2, ...)

3.3.2 数据类型属性

数据类型属性	语法	备注
auto_increment	id smallint NOT NULL auto_increment primary key //可通过alter table t1 auto_increment = n强制初始值	自动增长列
binary	name char(25) binary NOT NULL	只用于char和varchar, 表示区分大小写方式排序
default	sex enum(‘0’, ‘1’) NOT NULL default ‘0’	为列赋默认值
index	create index name employees(name(7)) //搜索name的前七个字符	索引一个列
not null	-	非 null
null	-	无/未知/不可用/待添加
primary key	-	非空不重复
unique	email varchar(45) unique	除 null 外不可重复
zerofill	orderid int unsigned zerofill not null	用0填充剩余字段空间 如前面int值为4时表示0000000004
IDENTITY	id int NOT NULL IDENTITY(起始值默认为1, 增量值默认为1)	唯一标识表中的一列

3.4 表记录的操作

3.4.1 插入记录

1.插入新记录

use xscj;
# 第一种方式
mysql> insert into xs
    -> values('081101', '王林', '计算机', 1, '1994-02-10', 50, null, null);
    -> # 注: 性别1可以写default, 默认为1
# 第二种方式
mysql> insert into xs(学号, 姓名, 专业名, 出生日期, 总学分)
    -> values( '081101', '王林', '计算机', '1994-02-10', 50);
# 第三种方式
mysql> insert into xs
	-> set 学号='081101', 姓名='王林', 专业名='计算机', 性别=default, 出生日期='1994-02-10', 总学分=50;

2.插入带有图片的记录

# 第一种方式: 存放路径
insert into xs
values('081102', '程明', '计算机', 1, '1995-02-01', 50, '图片路径', null);

# 第二种方式: 直接存放二进制文件
insert into xs
values('081102', '程明', '计算机', 1, '1995-02-01', 50, load_file('图片路径'), null);

3.用已有表记录插入当前表记录

# 向 xs1 表中插入 xs 表中的所有记录
use xscj;
drop table if exists xs1;
	create table xs1 like xs;
insert into xs1
	select * from xs;
select * from xs1;

# 另一种写法
use xscj;
drop table if exists xs1;
	create table xs1 as(select * from xs);
select * from xs1;

4.替换旧记录

replace into xs
	values('081211', '刘华', '通信工程', 1, '1995-03-08', 48, null, null);

3.4.2 修改记录

1.修改单个表

update xs
	set 总学分 = 总学分 + 1;
update xs
	set 学号 = '081250', 备注 = '辅修计算机专业'
	where 姓名 = '刘华';
select 修好, 姓名, 总学分, 备注 from xs;

2.修改多个表

# 将 xs 表和 xs1 表中所有学生的总学分都加4
update
	set xs.总学分 = xs.总学分 + 4, xs1.总学分 = xs1.总学分 + 4
	where xs.学号 = xs1.学号;
select * from xs;
select * from xs1;

3.4.3 删除记录

1.从单个表中删除行

# 删除 xs1 表中刘华的记录
use xscj;
delete from xs1
	where 姓名 = '刘华';

2.从多个表中删除行

# 删除 xscj 数据库中 xs 表和 xs1 表相同人员的记录
use xscj;
delete xs, xs1
	from xs, xs1
	where xs.学号 = xs1.学号;
select * from xs;

# 另一种做法
delete from xs, xs1
	using xs, xs1
	where xs.学号 = xs1.学号;
select * from xs;

3.清除表数据

# 此清除无法恢复
truncate table 表名;

3.5 MySQL 数据库信息显示

1.SHOW 语句

2.DESCRIBE 语句

mysql> use xscj;
Database changed
mysql> desc xs 学号;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| 学号   | char(6) | NO   | PRI | NULL    |       |
+--------+---------+------+-----+---------+-------+