SQL入门笔记

一、关系型数据库

二、使用终端操作数据库

登录数据库

mysql -uroot -p密码

查询数据库服务器中所有数据库

show databases;

直接操作数据库

use '数据库名';  
select * from '表名';  
// 通过字段查表里面的某行数据  
select * from admin where Admin_ID=1;

退出数据库

exit;

创建数据库

// 创建一个数据库
create database test;
use test;
// 查看数据库中的表
show tables;
// 在数据库中创建数据表
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
deatth DATE
);

查看数据表详情

describe pet;

查看表中的记录

select * from pet;

往数据表中添加记录

INSERT INTO pet
VALUES ('Puffball','Diane','hamster','f','2020-03-30',null);

mysql三大数据类型
MySQL 数据类型 | 菜鸟教程 (runoob.com)
删除某条数据

delete from pet where name='旺财';

修改某条数据

update pet set name='旺旺财' where owner='小明';

三、mysql创建表约束

主键约束

能够唯一确定一张表中的一条记录。通过给某个字段添加约束，就可以使得该字段不重复且不为空。

创建主键约束表

create table user(
    id int primary key,
    name varchar(20)
);

往表添加数据

 insert into user values(1, '张三');

往表中添加id为1的数据

 insert into user values(1, '李四');  
// id 不能重复报错  
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'  
 insert into user values(2, '李四');  
// id修改为2成功插入数据  
Query OK, 1 row affected (0.01 sec)

添加id为空的数据

insert into user values(null, '王二');  
ERROR 1048 (23000): Column 'id' cannot be null

同时给多个主键添加约束
也叫联合主键，只要这两个键值不同时重复就可以。

create table user2 (
    id int,
    name varchar(20),
    password varchar(20),
    primary key(id, name)
)

自增约束

create table user3 (
    id int primary key auto_increment,
    name varchar(20)
);  
// 只给name添加值  
insert into user3 (name) values('zhangsan');  
// 此时id值自动变为1  
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+  
 // 重复给name添加数据  
insert into user3 (name) values('zhangsan');  
// 此时id值自动变为2 
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+

添加主键约束

create table user4 (
    id int,
    name varchar(20)
); 
desc user4;  
-- key 没有约束  
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
-- 添加主键约束  
alter table user4 add primary key(id);  
-- id 有约束了  
desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+   
-- 删除主键约束  
alter table user4 drop primary key;
desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+  
-- 通过 modify 修改某个字段添加主键约束  
alter table user4 modify id int primary key;

唯一约束

约束修饰的字段的值不能重复。

create table user5(
    id int,
    name varchar(20)
);  
-- 给name添加unique约束  
alter table user5 add unique(name);  
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+  
-- 重复给name插入张三
insert into user5 values(1, '张三');
insert into user5 values(1, '张三');  
-- 错误提示  
ERROR 1062 (23000): Duplicate entry '张三' for key 'user5.name'  


--  创建表的时候就添加约束  
create table user6(
  id int,
  name varchar(20),
  unique(name)
);
-- 单独给某个键添加unique
create table user7(
  id int,
  name varchar(20) unique
);  
--  添加联合约束  只要两个字段不同时重复就可以  
create table user8(
  id int,
  name varchar(20),
  unique(id,name)
);  


-- 删除唯一约束  
alter table user7 drop index name;  
-- 使用modify添加约束  
alter table user7 modify name varchar(20) unique;

总结

创建表的时候可以添加约束
使用 alter 。。。 add 。。。添加约束
使用 alter。。。 modify。。。添加约束
使用 alter。。。drop。。。删除约束

非空约束

给字段添加not null修饰

  create table user9(
    id int,
    name varchar(20) not null
  );

  insert into user9 values(1,null);  
  -- 报错  ERROR 1048 (23000): Column 'name' cannot be null

也可以使用alter add 等添加非空约束

默认约束

当插入字段值的时候没有传值，就会使用默认值。

mysql> create table user10(
  id int,
  name varchar(20),
  age int default 10
);
-- 插入id、名字，不插入年龄  
mysql> insert into user10 (id,name) values(1, '张三');
mysql> select * from user10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
+------+------+------+
-- 插入id、名字、年龄
insert into user10 values(1, '张三', 18);
mysql> select * from user10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    1 | 张三 |   18 |
+------+------+------+

外键约束

设计到两个表的概念：主表副表
班级表-主表

create table classes(
  id int primary key,
  name varchar(20)
);

学生表-副表

create table students(
  id int primary key,
  name varchar(20),
  class_id int,
  -- 关联声明，class_id 与班级表关联  
  foreign key(class_id) references classes(id)
);

主表classes中没有的数据，在副表中是不可以使用的。
主表中的记录被副表引用，是不可以被删除的。

四、数据库的三大设计范式

1. 第一范式1NF

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

create table student2(
  id int,
  name varchar(20),
  address varchar(30)
);

-- 地址还可以拆分国家 省 市 区 门牌号 所以不满足第一范式
insert into student2 values(1, '张三', '中国广东省广州市天河区黄埔大道中100号');

满足第一范式的表，地址拆分的很详细

create table student3(
  id int,
  name varchar(20),
  country varchar(30),
  province varchar(30),
  city varchar(30),
  details varchar(30)
);
insert into student3 values(1, '张三', '中国','广东省','广州市','天河区黄埔大道中100号');

2. 第二范式

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

2.1 订单表

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)
);

拆分表之后符合第二范式。

3. 第三范式

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

订单表

create table myorder(
  order_id int primary key,
  product_id int,
  customer_id int,
  customer_phone varchar(15)
);

这张表里面的用户手机可以通过order_id 或 customer_id查到，所以不符合第三范式，需要把用户手机放到用户表里。优化如下
订单表

create table myorder(
  order_id int primary key,
  product_id int,
  customer_id int
);

用户表

create table customer(
  id int primary key,
  name varchar(20),
  phone varchar(15)
);