Mysql数据库教程(第一章 表的操作)

前言

• 命令行登录Mysql
格式: mysql -h 主机地址 -u用户名 -p 用户密码
例 1:连接到本机上的 MySQL。
ye@ubuntu:~$ mysql -uroot -p mysql;

例 2:连接到远程主机上的 MYSQL。
ye@ubuntu:~$ mysql -h 127.0.0.1 -uroot -p mysql;

• 第三方工具：Navicat
• mysql端口号：3306

库的基本操作

创建库
create database 数据库名;

删除库
drop database 数据库名;

查看现有的数据库
show databases;

一、数据类型

MySQL支持多种数据类型,大致分为三类:

• 数值
• 日期/时间
• 字符串(字符)类型

1.1 数值型

MySQL支持所有标准sql数值数据类型:

注: 整数常用int，小数常用decimal

1.2 日期和时间类型

日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

注：记住类型和格式。重点掌握 DATETIME YYYY-MM-DD HH:MM:SS

1.3 字符串类型

• 字符串类型指CHAR(固定长度)、VARCHAR(可变长度)、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

• CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。

• BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。

• BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

• 有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

注：重点掌握CHAR(字符串长度)和VARCHAR(字符串长度)。
区别：CHAR(长度) 定长，给他设置多大的长度，它就会把所有的长度空间全部占满，多的不会释放，会用空格进行填充；VARCHAR(长度) 变长，它是以实际的长度来占用空间，多的释放。

二、表的操作

2.1 创建表

注：每一次建表前，都要明确该表隶属于哪一个库。一定是在某一个库下进行建表操作。

• 表的创建语法
Create table 表名(
属性名 数据类型 [约束条件],
属性名 数据类型 [约束条件],
属性名 数据类型 [约束条件]
);

• []中括号种的内容是可选内容
• 最后一个属性后没有逗号

-- 新建教师表(教师编号，姓名，年龄，薪资，入职日期)
create table 教师表(
	教师编号 varchar(10),
	姓名 char(6),
	年龄 int,
	薪资 decimal(5,2),
	入职日期 datetime
);

注：在navicat中，“打开表”–>看表中内容，”设计表“–>看表结构

命令切换数据库
格式：USE 数据库名字

-- 在”数据库“下，新建一个学生表(学号，姓名，年龄)
-- 因为目前还是在destdb库中，所以先来进行库的切换
use 数据库;

create table 学生表(
	学号 varchar(6),
	姓名 varchar(10),
	年龄 int
);

注：navicat中ctrl+s可保存代码。保存过的代码可在左侧”查询“中找到，右击”在文件夹中显示“，默认后缀为.sql，可用记事本打开。

2.2 删除表

格式：:drop table 表名;

删除多个表
•drop table 表名1,表名2…

-- 删除学生表
drop table 学生表;

2.3 修改表

添加列(属性)
• alter table 表名 add 属性名 数据类型;

删除列(属性)
• alter table 表名 drop 属性名;

修改属性
• alter table 表名 modify 属性名 新的数据类型;

修改字段名
• alter table 表名 change 旧字段名 新字段名 数据类型;

-- 给学生表添加一个专业列 字符串 10
alter table 学生表 add 专业 varchar(10);
-- 给学生表添加一个出生日期列 datetime
alter table 学生表 add 出生日期 datetime;

-- 删除学生表中出生日期列
alter table 学生表 drop 出生日期;

-- 把教师表的薪资改成整数类型int
alter table 教师表 modify 薪资 int;

-- 把教师表的姓名改成教师姓名
alter table 教师表 change 姓名 教师姓名 varchar(6);

2.4 显示表

显示表结构
• desc 表名;

三、创数据的完整性建表

3.1 数据的完整性

• 数据的完整性指存储在数据库中的数据应该保持准确性和可靠性。如出现学号相同的数据就是不准确的。

3.2 实体完整性

• 实体完整性是对关系中的记录唯一性。
• 定义表中的所有行能唯一的标识
• 表中主属性(字段)不能为Null且不能有相同值
• 一般用主键、唯一索引、 unique关键字来实现
让某列值能达到唯一区分的效果

3.2.1 主键约束

• 主关键字(primary key)是表中的一个或多个字段,它的值用于唯一地标识表中的某一条记录
• 创建表时指定主键
唯一 不重复 不为空
一个表中有且只能有一个主键约束

添加主键约束

1)建表时直接添加

格式一：
CREATE TABLE 表名(
列名1 数据类型 PRIMARY KEY,
列名2 数据类型
);
第一种sql server/oracle/mysql通用

格式二：
CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
CONSTRAINT 主键约束的名字 PRIMARY KEY(列名1)
) ;

格式三：
CREATE TABLE 表名(
列名1 数据类型,
列名2 数据类型,
PRIMARY KEY(列名1)
);

-- 创建一个student表 sno 主键,sname,sage,ssex
create table student(
	sno varchar(6) primary key,
	sname varchar(10),
	sage int,
	ssex char(2)	
);

--创建一个商品表(商品编号 主键，名称，价格)
create table 商品表(
	商品编号 varchar(10),
	名称 varchar(10),
	价格 int,
	constraint PK_商品编号 primary key(商品编号)
);

注：在插入中文数据后保存显示1366 - Incorrect string value: ‘\xE5\xA8\x84\xE7\xA7\xA6’ for column ‘sname’ at row 1
解决：改变表字段编码需要: alter table <表名> change <字段名> <字段名> <类型> character set utf8; 或者在设计表里选择这个字段然后改变字符集后保存。

2)通过ALTER语句

格式一：
ALTER TABLE 表名 ADD CONSTRAINT 主键约束的名字 PRIMARY KEY(列名);
通用

格式二：
ALTER TABLE 表名 ADD PRIMARY KEY(列名);

格式三：
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

-- 给教师表中教师编号设置主键约束
alter table 教师表 add constraint PK_教师编号 primary key(教师编号);

-- 给学生表中学号添加一个主键约束
alter table 学生表 modify 学号 varchar(6) primary key;

删除主键约束

格式：ALTER TABLE 表名 DROP PRIMARY KEY;

-- 删除学生表中的学号的主键约束
alter table 学生表 drop primary key; 

主键的另外一个说法：
联合主键：将表中两个列看成一个整体，让该整体遵循主键约束的特征。

1)建表时直接添加联合主键约束

格式一：
CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
CONSTRAINT 主键约束的名字 PRIMARY KEY(列名1,列名2)
) ;

格式二：
CREATE TABLE 表名(
列名1 数据类型,
列名2 数据类型,
PRIMARY KEY(列名1，列名2)
);

--创建一个选课表(学号，姓名，课程号，成绩) 学号，课程号 联合主键
create table 选课表(
	学号 varchar(10),
	姓名 varchar(8),
	课程号 varchar(6),
	成绩 int,
	constraint PK_x primary key(学号,课程号)
);

2)通过ALTER语句
• ALTER TABLE 表名 ADD CONSTRAINT 主键约束的名字 PRIMARY KEY(列名1,列名2);
通用

• ALTER TABLE 表名 ADD PRIMARY KEY(列名1,列名2);

3.2.2 唯一约束

• 唯一约束是指定table的列或列组合不能重复,保证数据的唯一性;
• 唯一约束不允许出现重复的列值,但是可以为多个null;
• 同一个表可以有多个唯一约束,多个列组合的约束;
• 如果不给唯一约束名称,就默认和列名相同;
• MySQL会给唯一约束的列上默认创建一个唯一索引;

1)建表时直接添加唯一约束

格式一：
create table 表名(
列名1 数据类型 unique,
列名2 数据类型 unique,
列名3 数据类型
);
通用

格式二：
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型
constraint 唯一约束的名字1 unique(列名1),
constraint 唯一约束的名字2 unique(列名2)
);
注:约束的名字不可重复。

-- 创建一个会员表(会员编号 主键约束，姓名，手机号 唯一约束)
create table 会员表(
	会员编号 varchar(10) primary key,
	姓名 varchar(6),
	手机号 varchar(15) unique
);

-- 创建一个成绩表(学号 唯一约束，成绩)
create table 成绩表(
	学号 varchar(10),
	成绩 int,
	constraint UN_学号 unique(学号)
);

2)通过ALTER语句

格式：alter table 表名 add unique(列名);

-- 把学生表学号,姓名设置成唯一约束
alter table 学生表 add unique(学号);
alter table 学生表 add unique(姓名);

删除唯一约束

格式：alter table 表名 drop index 唯一约束名;
注: 若没有起唯一约束名，该项默认和列名保持一致。

-- 删除学生表中姓名的唯一约束
alter table 学生表 drop index 姓名;

-- 删除成绩表中学号的唯一约束
alter table 成绩表 drop index UN_学号;

3.3 域完整性

• 域完整性是对数据表中字段属性的约束
• 它是由确定表结构时所定义的字段的属性决定的
• 限制数据类型,缺省值,规则,约束,是否可以为空
• 域完整性可以确保不会输入无效的值

3.3.1 默认值约束

• 创建表时设定默认值(Navicat中)

添加默认约束

1)在创建表的时候添加
• CREATE TABLE 表名(列名 数据类型 DEFAULT 3);

-- 创建一个图书表(书本编号，名称，价格 默认10)
create table 图书表(
	书本编号 varchar(10),
	名称 varchar(10),
	价格 int default 10
);

2)通过ALTER语句
• ALTER TABLE 表名 MODIFY 列名 数据类型 DEFAULT 2;

-- 给学生表的年龄添加一个默认约束
alter table 学生表 modify 年龄 int default 18;

-- 给学生表的专业添加一个默认约束  -->软测
alter table 学生表 modify 专业 varchar(10) default "软测";

注：给学生表的专业添加一个默认约束 出现错误 > 1067 - Invalid default value for ‘专业’
这是由于cpu或者是MySql版本导致的（具体是什么原因我还没清楚）
解决办法如下：(效果仍待测试)

## 创建数据库时设置校对规则
create database 数据库名 default character set gb2312 collate gb2312_chinese_ci;
## 如果已经创建数据库，操作如下：
alter database 数据库名 default character set gb2312 collate gb2312_chinese_ci;
使用该数据库
use 数据库名;

删除默认约束

格式：ALTER TABLE 表名 MODIFY 列名 数据类型;

注：有点特殊，仍用modify

--去掉学生表中年龄的默认约束
alter table 学生表 modify 年龄 int;

3.3.2 非空约束

• 创建表时设定非空

添加非空约束

1)建表时直接添加
CREATE TABLE 表名(列名 数据类型 NOT NULL);

-- 创建一个课程表(课程号，课程名，学时)
create table 课程表(
	课程号 varchar(10) not null,
	课程名 varchar(10) not null,
	学时 int not NULL
);

2)通过ALTER 语句
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

-- 把学生表的姓名不能为空
alter table 学生表 modify 姓名 varchar(10) not null;

注：出现ERROR 1366 (HY000): Incorrect string value: ‘\xE5\xB0\x8F\xE6\x98\x8E‘ for column ‘name‘ at row 1
解决： 参照.

删除非空约束

ALTER TABLE 表名 MODIFY 列名 数据类型;

--把学生表中的姓名的非空约束删除
alter table 学生表 modify 姓名 varchar(10);

3.4 参照完整性

• 指表与表之间的数据参照引用
• 使用外键约束实现

区分主表和从表：
A 参照引用B ： B是主表，A是从表

3.4.1 外键约束

• 外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系;
• 表的外键值必须在主表中能找到或者为空;
• 当主表的记录被从表参照时,主表的记录将不允许删除;
• 如果要删除数据,需要先删除从表中依赖该记录的数据。

添加外键约束

1)建表时直接添加

create table 主表(
列名1 数据类型 primary key,
列名2 数据类型
);

create table 从表(
列名1 数据类型,
列名2 数据类型
constraint 外键约束名 foreign key(从表列名1) references 主表(主表列名1)
);

练习：

--主表
CREATE TABLE class(
clas_id INT(6) PRIMARY KEY,
cla_name VARCHAR(30) NOT NULL UNIQUE
);

--从表
CREATE TABLE students(
stu_id INT(10) PRIMARY KEY,
stu_name VARCHAR(30) NOT NULL,
stu_score FLOAT(5,2) DEFAULT 0.0,
cla_id INT(10),
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(clas_id)
);


-- 建立一个sc表(sno,cno,grade) sno 参照引用student表中的学号
create table sc(
	sno varchar(10),
	cno varchar(10),
	grade int,
	constraint FK_SNO foreign key(sno) references student(sno)
);

注：参考引用的列：当从表参照引用主表的列时，要求主表的列必须要有主键约束或者唯一约束；参照引用的列，列名可以不一致，但是数据类型和内容必须一致！！！

2)通过ALTER 语句

ALTER TABLE 从表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY(从表列名) REFERENCES 主表(主表列名);

-- 给成绩表中的学号建立一个外键约束，参照引用学生表的学号
alter table 成绩表 add constraint FK_学号 foreign key(学号) references 学生表(学号);

注：此时出现1215 - cannot add foreign key constraint错误，查看两个表的引擎，没有问题；后来发现是两个表的字符集不同，将成绩表的字符集改为utf8解决。

删除外键约束

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

-- 删除成绩表的外键
alter table 成绩表 drop foreign key FK_学号;

注：一个从表可以参考多个主表