02-mysql基础

使用终端操作数据库

mysql> show databases;	-- 查看有哪些数据库

mysql> create database databaseName;	-- 在数据库服务器中创建自己的数据库mysql> use databaseName;	-- 选择数据库mysql> create TABLE pet(name VARCHAR(20),owner VARCHAR(20),specise VARCHAR(20),sex CHAR(1),brith DATE,death DATE );	-- 创建一个数据表mysql> INSERT INTO pet VALUES('kk','cc','dog','1','1998-8-2',null);	-- 插入数据mysql> select * from pet;	-- 查询表中的数据

mysql> describe pet;	-- 查看数据表的架构 Field:字段的名称 Type :字段的类型,可以有int、var、varchar  Null	  :字段是否能为空 Key  :是否是关键字 如可以定义为:primary key 或者 unique key ... Default  :默认,可以预设初始值var()与varchar()的区别在于var()是定常的,存储的字符串没有达到"()"中数字的上限,var()依然会占用空格来填充空间,而varchar()则是不定长的,没有达到"()"中的上限则会自动去掉后面的空格;

mysql> show tables;	-- 查看该数据库中有哪些表

mysql> INSERT INTO pet VALUES('kk1','cc1','dog1','1','1998-1-2',null),('kk2','cc2','dog2','2','1998-2-2',null),('kk3','cc3','dog3','1','1998-3-2','1998-12-2'),('kk4','cc4','dog4','2','1998-4-2',null);	-- 先插入数据DELETE FROM pet WHRER 条件;	-- 删除数据UPDATE pet SET 字段1=值1,字段2=值2 ... WHERE 条件;	-- 修改数据mysql> exit;	-- 退出数据库服务器

mysql建表中的约束

1、主键约束

1. 主键用于唯一地标识表中的每一条记录，可以定义一列或多列为主键
2. 是不可能（或很难）更新
3. 主键列上没有任何两行具有相同值（即重复值），不允许空（NULL）
4. 主键可作外键

mysql> create table user11(id int PRIMARY KEY,name VARCHAR(20));	-- 设置id为主键mysql> desc user11;	-- 查看表结构

可以看到id是不可以为NULL ，而且 key的值 也变为PRI(primary key)

2、复合主键

mysql> CREATE TABLE user12(id INT,name VARCHAR(20),password VARCHAR(20),PRIMARY key(id,name));mysql> desc user12;	-- 查看表结构

mysql> INTO user2 VALUES (1,'老王','123456');mysql> INTO user2 VALUES (2,'老王','123456');mysql> INTO user2 VALUES (1,'老李','123456');mysql> SELECT * FROM user2;	-- 插入三条数据后，查看表

说明，复合主键只要所有的字段都不是相同的情况下可以允许其中的字段重复。

3、唯一约束

1. 唯一性约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以放置多个唯一性约束
2. 只要唯一就可以更新
3. 表中任意两行在指定列上都不允许有相同的值，允许空（NULL）
4. 一个表可以放置多个唯一性约束

mysql> create table user13(	id int,	name varchar(20));mysql> alter table user13 add unique(name); -- 建表后添加唯一约束mysql> desc user13;	-- 查看表结构

mysql> insert into user13 values(1,'zhangsan');	-- 插入数据Query OK, 1 row affected (0.01 sec)mysql> insert into user13 values(1,'zhangsan');	-- 相同名字无法添加ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user13.name'

建表时设置唯一约束

mysql> create table user14(	id int,	name varchar(20),	unique(name));mysql> create table user15(	id int,	name varchar(20) unique);mysql> create table user16(	id int,	name varchar(20),	unique(id,name));mysql> desc user16;

-- 向user16中添加数据mysql> insert into user16 values(1,'zhangsan');mysql> insert into user16 values(2,'zhangsan');mysql> insert into user16 values(1,'lisi');mysql> select *from user16;

说明，多字段复合唯一约束只要所有的字段都不是相同的情况下可以允许其中的字段重复。

删除唯一约束

mysql> alter table user15 drop index name;

通过modify添加唯一约束

mysql> alter table user15 modify name varchar(20) unique;

4、自增约束

1. 一个表只能有一个自增约束，因为一个表只有一个维护自增值的变量

2. 自增约束的列只能是整数列

3. 自增约束的列必须是键列（主键，唯一键，外键），

   实际中一般是主键自增最多

mysql> CREATE TABLE user17(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20));mysql> desc user17;

mysql> INSERT INTO user17(name) VALUES('张三');mysql> INSERT INTO user17(name) VALUES('李四');mysql> select * from user17;

没有自定义id值，但是自动生成了id

mysql> INSERT INTO user17(id,name) VALUES(555,'王五');

自增列如果有指定值，就按照指定的来，如果没有指定值或指定的值为0或NULL，就自增。

取消自增

mysql> alter table user17 modify id int;

建表后增加自增

mysql> alter table user17 modify id int auto_increment;

5、非空约束

mysql> create table user18(	id int,	name varchar(20) not null);mysql> desc user18;

mysql> insert into user18 (id) values(1);ERROR 1364 (HY000): Field 'name' doesn't have a default value

NOT NULL约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

6、默认约束

在添加数据时，如果没有传值，就会使用默认值。

mysql> create table user19(	id int,	name varchar(20),	age int default 10);mysql> desc user19;

mysql> insert into user19 values(1,'zhangsan',20);mysql> insert into user19(id,name) values(1,'zhangsan');mysql> select * from user19;

建表后删除指定字段的默认值

mysql> alter table user19 alter column age drop default;mysql> insert into user19(id,name) values(1,'zhangsan');1364 - Field 'age' doesn't have a default valuemysql> desc user19;

建表后添加指定字段的默认值

mysql> alter table user19 alter column age set default 5;mysql> insert into user19(id,name) values(1,'zhangsan');mysql> select * from user19;

7、外键约束

涉及到两个表：主表，副表

-- 班级表	主表create table classes(	id int primary key,	name varchar(20));-- 学生表	副表create table students(	id int primary key,	name varchar(20),	class_id int,	foreign key(class_id) references classes(id));	-- 将 class_id 设置为外键

添加数据：

mysql> insert into classes values(1,'一班');mysql> insert into classes values(2,'二班');mysql> insert into classes values(3,'三班');mysql> insert into classes values(4,'四班');mysql> insert into students values(1001,'张三',1);mysql> insert into students values(1002,'张三',2);mysql> insert into students values(1003,'张三',3);mysql> insert into students values(1004,'张三',4);

1. 主表classes中没有的数据值，在副表中是不可以使用的

   mysql> insert into students values(1005,'张三',5);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

2. 主表中的记录被副表引用，是不可以被删除的

   mysql> delete from classes where id=4;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

数据库设计三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

第一范式1NF

第一范式：数据库表中的所有字段值都是不可分割的原子值

第一范式是最基本的范式。

例：

mysql> create table student2(	id int primary key,	name varchar(20),	address varchar(30));mysql> insert into student2 values(1,'张三','重庆市江津区白沙镇');mysql> insert into student2 values(2,'李四','重庆市江津区李市镇');mysql> insert into student2 values(3,'王五','重庆市江津区永兴镇');mysql> select * from student2;

其中，address字段值还可以继续拆分，就不满足第一范式！

可改为如下样式：

mysql> create table student3(	id int primary key,	name varchar(20),	cuntry varchar(30),	privence varchar(30),	city varchar(30),	details varchar(30));mysql> insert into student3 values(1,'张三','中国','重庆市','江津区','白沙镇');mysql> insert into student3 values(2,'李四','中国','重庆市','江津区','李市镇');mysql> insert into student3 values(3,'王五','中国','重庆市','江津区','永兴镇');mysql> select * from student3;

第一范式的合理遵循需要根据系统的实际需求来定，范式设计得越详细，对于某些实际操作可能更好，但不一定都是好处。

第二范式2NF

第二范式：(确保表中的每列都和主键相关)在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

必须是满足第一范式的前提下，第二范式要求除主键外的每一列都必须完全依赖于主键，如果要出现不完全依赖，只可能在联合主键的情况下。

-- 订单表create table myorder(	product_id int,	customer_id int,	product_name varchar(20),	customer_name varchar(20),	primary key(product_id,customer_id));

问题，除主键外的其他列，只依赖于主键的部分字段。

-- 拆表create table myorder(	order_id int primary key,	product_id int,	customer_id int,);create table product(	id int primary key,	name varchar(20));create table customer(	id int primary key,	name varchar(20));

分成三个表后，就满足了第二范式的设计。

第三范式3NF

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系

create table myorder(	order_id int primary key,	product_id int,	customer_id int,);create table product(	id int primary key,	name varchar(20));create table customer(	id int primary key,	name varchar(20),	customer_phone varchar(15));

一般，一个数据库设计符合 3N F或 BCNF 就可以了。