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 | |
+--------+---------+------+-----+---------+-------+