MySQL学习笔记

MySQL学习笔记

终端操作mysql数据库
1>mysql -uroot -proot

2>查看有哪些数据库

show databases;

±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
±-------------------+

3>进入某一个数据库

use test;

4>如何退出数据库服务器

mysql> exit;

Bye

5>如何在数据库服务器创建数据库

create database test1;

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

选中这个数据库
mysql> use test1;
Database changed

6>如何查看某个数据库中所有的数据表

mysql> show tables;

Empty set (0.00 sec)

7>如何创建一个数据表

 CREATE TABLE pet(
       name VARCHAR(20),
       owner VARCHAR(20),
       species VARCHAR(20),
       sex CHAR(1),
       birth DATE,
       death DATE
 );

mysql> CREATE TABLE pet(
-> name VARCHAR(20),
-> owner VARCHAR(20),
-> species VARCHAR(20),
-> sex CHAR(1),
-> birth DATE,
-> death DATE
-> );
Query OK, 0 rows affected (0.82 sec)
—查看数据表是否创建成功

mysql> show tables;

±----------------+
| Tables_in_test1 |
±----------------+
| pet |
±----------------+
1 row in set (0.00 sec)

–查看创建好的表格

mysql> describe pet;

±--------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±------------±-----±----±--------±------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
±--------±------------±-----±----±--------±------+
6 rows in set (0.06 sec)

—查看数据表中有哪些数据记录

mysql> select *from pet;

Empty set (0.00 sec)

—向数据表中插入数据

INSERT into pet values('xiahao','zheng','hamster','f','1999-03-30',null);

mysql> INSERT into pet values('xiahao','zheng','hamster','f','1999-03-30',null);

Query OK, 1 row affected (0.36 sec)

查看表中的数据
mysql> select * from pet;
±-------±------±--------±-----±-----------±------+
| name | owner | species | sex | birth | death |
±-------±------±--------±-----±-----------±------+
| xiahao | zheng | hamster | f | 1999-03-30 | NULL |
±-------±------±--------±-----±-----------±------+
1 row in set (0.00 sec)

mysql常用数据类型有哪些

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和**字符串(**字符)类型。

–数值
mysql> create table TestTable(number tinyint);
Query OK, 0 rows affected (0.28 sec)

------数据类型如何选择
日期 选择按照格式
数值和字符串看范围

mysql> insert into testtable values(400);
ERROR 1264 (22003): Out of range value for column ‘number’ at row 1

INSERT into pet values(‘hjahao’,‘cheng’,‘hamster’,‘f’,‘1999-03-30’,null);
INSERT into pet values(‘ddahao’,‘feng’,‘hamster’,‘m’,‘1999-03-30’,null);
INSERT into pet values(‘dfgaho’,‘fheng’,‘hamster’,‘m’,‘1999-03-30’,null);
INSERT into pet values(‘diahao’,‘fheng’,‘hamster’,‘m’,‘1999-03-30’,null);
INSERT into pet values(‘giahao’,‘dheng’,‘hamster’,‘f’,‘2099-04-30’,null);
INSERT into pet values(‘ciahao’,‘dheng’,‘hamster’,‘m’,‘2010-03-30’,null);
INSERT into pet values(‘dfahao’,‘dheng’,‘hamster’,‘f’,‘2020-06-30’,null);
INSERT into pet values(‘dvahao’,‘dheng’,‘hamster’,‘f’,‘2012-03-30’,null);

--------删除某一个记录

delete from pet where name=‘dvahao’;
mysql> delete from pet where name=‘dvahao’;
Query OK, 1 row affected (0.39 sec)

—如何修改shuju
update pet set name=‘旺旺财’ where owner=‘周星驰’;

mysql> update pet set name=‘旺旺财’ where owner=‘周星驰’;
Query OK, 1 row affected (0.38 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-----------总结：数据记录常见操作
–增加
INSERT
–删除
DELETE
–修改
UPDATE
–查询
SELECT

mysql建表约束

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

create table user(

id int primary key,
name varchar(20) comment ‘姓名’,

mysql> create table user(
->
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.58 sec)

mysql> show tables;
±----------------+
| Tables_in_test1 |
±----------------+
| pet |
| testtable |
| user |
±----------------+
3 rows in set (0.00 sec)d

insert into user values(‘1’,‘张三’);
–重复添加
mysql> insert into user values(‘1’,‘张三’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

----联合主键
–只要 联合的主键值加起来不重复就可以
create table user2(

id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);

insert into user2 values(‘1’,‘张三’,‘123456’);
insert into user2 values(‘2’,‘张三’,‘123456’);
insert into user2 values(‘2’,‘李四’,‘123456’);

mysql> select * from user2;
±—±-------±---------+
| id | name | password |
±—±-------±---------+
| 1 | 张三 | 123456 |
| 2 | 张三 | 123456 |
| 2 | 李四 | 123456 |
±—±-------±---------+
3 rows in set (0.00 sec)

–自增约束
create table user3(

id int primary key auto_increment,
name varchar(20)
);

insert into user3(name) values(‘我是好人’);
mysql> insert into user3(name) values(‘你是华人’);
Query OK, 1 row affected (0.37 sec)

mysql> select * from user3;
±—±-------------+
| id | name |
±—±-------------+
| 1 | 我是好人 |
| 2 | 你是华人 |
±—±-------------+
2 rows in set (0.00 sec)

–如果我们创建表的时候，忘记创建表的约束条件
create table user4(
id int,
name varchar(20)
);

mysql> describe user4;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

--修改表结构 添加主键约束
alter table user4 add primary key(id);

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe user4;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

—如何删除
alter table user4 drop primary key;

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.92 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe user4;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

–使用modify修改字段
alter table user4 modify id int primary key;

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.71 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe user4;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

–外键约束

–涉及到两个表，父表，字表
–主表和副标
–班级
create table class(
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 class(id)
);

insert into students values(101,‘张三’,1);
insert into students values(102,‘张三’,2);
insert into students values(103,‘张三’,3);
insert into students values(104,‘张三’,4);

insert into students values(104,‘张三’,5);

mysql> insert into students values(104,‘张三’,5);
ERROR 1062 (23000): Duplicate entry ‘104’ for key ‘PRIMARY’

mysql> desc class;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.14 sec)

mysql> desc students;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
±---------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

insert into class values(1,‘1班’);

insert into class values(2,‘2班’);

insert into class values(3,‘3班’);

insert into class values(4,‘4班’);

mysql> select * from class;
±—±-----+
| id | name |
±—±-----+
| 1 | 1班 |
| 2 | 2班 |
| 3 | 3班 |
| 4 | 4班 |
±—±-----+
4 rows in set (0.00 sec)

----结论
–1、在主表class中没有的数据值，在附表中，是不可以使用的
–2、主表中的记录被副表引用，是不可以被删除的

–唯一约束
–约束修饰的字段的值不能重复
create table user5(
id int ,
name varchar(20)
);

alter table user5 add unique(name);

mysql> describe user5;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

insert into user5 values(1,“zhang”);

mysql> insert into user5 values(1,“zhang”);
Query OK, 1 row affected (0.36 sec)

mysql>
mysql> insert into user5 values(1,“zhang”);
ERROR 1062 (23000): Duplicate entry ‘zhang’ for key ‘name’
mysql>

create table user6(
id int ,
name varchar(20),unique(name)
);

mysql> desc user6;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

create table user7(
id int ,
name varchar(20),
unique(id,name)
);

mysql> create table user7(
-> id int ,
-> name varchar(20),
-> unique(id,name)
-> );
Query OK, 0 rows affected (0.36 sec)

mysql> desc user7;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)
insert into user7 values(1,‘张三’);
insert into user7 values(2,‘张三’);
insert into user7 values(1,‘李四’);

mysql> insert into user7 values(1,‘张三’);
Query OK, 1 row affected (0.36 sec)

mysql> insert into user7 values(2,‘张三’);
Query OK, 1 row affected (0.05 sec)

mysql> insert into user7 values(1,‘李四’);
Query OK, 1 row affected (0.05 sec)

mysql> select * from user7;
±-----±-------+
| id | name |
±-----±-------+
| 1 | 张三 |
| 1 | 李四 |
| 2 | 张三 |
±-----±-------+
3 rows in set (0.00 sec)

mysql> insert into user7 values(1,“张三”);
ERROR 1062 (23000): Duplicate entry ‘1-张三’ for key ‘id’

–如何删除唯一约束
alter table user6 drop index name;

mysql> alter table user6 drop index name;
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user6;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

–添加
alter table user6 modify name varchar(20) unique;

mysql> alter table user6 modify name varchar(20) unique;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user6;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

–总结
–1、建表的时候就添加约束
–2、可以使用alter add
–3、alter modify
–4、删除 alter drop

–非空约束
–修饰的字段不能为空
create table user9(
id int,
name varchar(20) not null
);

mysql> desc user9;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.14 sec)

insert into user9 values(1);

mysql> insert into user9 values(1);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

insert into user9 values(1,‘张三’);

–默认约束
—就是当我们插入字段值的时候，如果没有传值，就会默认使用

mysql事务

mysql中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

a-> -100

upadate user set money =money-100 where name=‘a’;

b-> +100

upadate user set money =money+100 where name=‘b’;

–实际的程序中，如果只有一条语句执行成功，而另外另一条没有执行成功
–出现数据前后不一致。

–多条SQL语句，可能会有同时成功的要求，要么就同时失败。

–mysql中如何控制事物

1mysql默认是开启事务的（自动提交）。

mysql> select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 1 |
±-------------+
1 row in set (0.00 sec)

–默认事务开启的作用是什么？
–当我们去执行一个SQL语句的时候，效果会立刻体现出来，且不能回滚；

create database bank;

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

);

insert into user values(1,‘a’,1000);

insert into user values(2,‘b’,1000);
–事务回滚：撤销SQL语句执行效果
rollback；

mysql> rollback;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
1 row in set (0.00 sec)

–设置mysql自动提交为false

set automatic =0；

update user set money =money-100 where name=‘a’;
update user set money =money+100 where name=‘b’;

begin;

#或者start transaction;

都可以帮助我们手动开启一个事务。

–事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

–没有被撤销
mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 700 |
| 2 | b | 1300 |
±—±-----±------+
2 rows in set (0.00 sec)

begin;
update user set money =money-100 where name=‘a’;
update user set money =money+100 where name=‘b’;

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 600 |
| 2 | b | 1400 |
±—±-----±------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.14 sec)

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 700 |
| 2 | b | 1300 |
±—±-----±------+
2 rows in set (0.00 sec)

start transaction;
update user set money =money-100 where name=‘a’;
update user set money =money+100 where name=‘b’;

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 600 |
| 2 | b | 1400 |
±—±-----±------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 700 |
| 2 | b | 1300 |
±—±-----±------+
2 rows in set (0.00 sec)

–事务开启之后一旦commit 提交就不可以回滚（也就是当前的这个事务在提交的时候就结束了）

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 700 |
| 2 | b | 1300 |
±—±-----±------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 700 |
| 2 | b | 1300 |
±—±-----±------+
2 rows in set (0.00 sec)

mysql>

事物的四大特征：

A：原子性：事务是最小的单位，不可以在分割
C 一致性：事务要求，同一事务中的SQL语句，必须保证同时成功或者同时失败。
I 隔离性：事务2和事务2直接是具有隔离性的。
D 持久性：事务一旦结束，就不可以返回。

事务开启：
1、修改默认提交 set autocommit=0；
2、begin；
3、start transaction；

事务手动提交：
commit；
事务手动回滚：
rollback；

—事务的隔离性：
1、read uncommitted；读未提交的
2、read committed； 读已经提交的
3、repeatable read; 可以重复读
4、serializable； 串行化

1、read uncommitted；读未提交的
如果有事务a和事务b
a事务对数据进行操作，在操作的过程中，事务没有被提交，但是吧
可以看见a的操作

bank数据库 user 表

insert into user values(3,‘小明’,1000);

insert into user values(4,‘淘宝店’,1000);

mysql> select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±----------±------+
4 rows in set (0.00 sec)

-----如何查看数据库的隔离级别

select @@global.transaction_isolation;

mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| REPEATABLE-READ |===默认隔离级别
±-------------------------------+
1 row in set (0.13 sec)

–如何修改隔离级别

set global transaction isolation level read uncommitted;

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| READ-UNCOMMITTED |
±-------------------------------+
1 row in set (0.00 sec)

start transaction;
update user set money=money-800 where name=‘小明’;
update user set money=money+800 where name=‘淘宝店’;
mysql> select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
±—±----------±------+
4 rows in set (0.00 sec)
–给淘宝店打电话，说你去查一下，是不是到账

mysql> select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
±—±----------±------+
4 rows in set (0.00 sec)

–发货
–请吃饭
–1800

–小明
mysql> select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±----------±------+
4 rows in set (0.00 sec)

–结账的时候发现钱不够

select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±----------±------+
4 rows in set (0.00 sec)

–如果两个不同的地方，都在进行操作，如果事务a开启之后，它的数据
可以被其他事务读取到，这样会出现（脏读）
==脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读

–实际开发是不允许出现。

set global transaction isolation level repeatable read;

–查看隔离级别
select @@global.transaction_isolation;

–修改隔离级别

set global transaction isolation level read committed;

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| READ-COMMITTED |
±-------------------------------+
1 row in set (0.00 sec)

bank数据库 user表

小张：银行的会计
start transaction;
select * from user;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±----------±------+
4 rows in set (0.00 sec)
小张去厕所了 抽烟

小王

start transaction;
insert into user values(5,‘c’,100);
commit;

mysql> select * from user;
±—±----------±------+
| id | name | money |
±—±----------±------+
| 1 | a | 700 |
| 2 | b | 1300 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±----------±------+
5 rows in set (0.00 sec)

–小张上完厕所，抽完烟

select avg(money) from user;

mysql> select avg(money) from user;
±-----------+
| avg(money) |
±-----------+
| 820.0000 |
±-----------+
1 row in set (0.10 sec)

–money的平均值不是1000，变少了
–虽然我只能读到另外一个提交的数据，但还是会出现问题，就是读取同一个表的数据，发现前后不一致
–不可重复读现象，read committed

–3、repeatable read; 可以重复读

set global transaction isolation level repeatable read;

–查看隔离级别
select @@global.transaction_isolation;

mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| REPEATABLE-READ |
±-------------------------------+
1 row in set (0.00 sec)

–在REPEATABLE-READ 隔离级别下又会出现什么问题

–张全蛋–成都
start transaction;
insert into user values(6,‘d’,‘1000’);

–王尼玛–北京

start transaction;

一方可以查询数据，未查到已经存在的数据。不能插入数据
插入数据显示已经存在。
===幻读
—事务a和事务b同时操作一张表，事务a提交的数据
而不能被事务b读到，就可以造成幻读。

4、serializable； 串行化

set global transaction isolation level serializable;

select @@global.transaction_isolation;

mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| SERIALIZABLE |
±-------------------------------+
1 row in set (0.00 sec)

–当user表被另外一个事务操作的时候，其他事务里边的写操作，是不可以

–串行化问题是 性能特差

隔离级别越高，性能越差
READ-UNCOMMITTED>READ-COMMITTED>REPEATABLE-READ>SERIALIZABLE;

mysql默认隔离级别是

set global transaction isolation level repeatable read;

sql的四种连接查询

内连接
inner join 或者join

外连接

1、左连接 left join 或者left outer join

2、右连接 right join或right outer join

3、完全外连接 full join 或者full outer join

–创建表
person 表
id，
name，
cardId

create table person(
id int,
name varchar(20),
cardId int
);

–card表
id，
name

create table card(
id int,
name varchar(20)
);

insert into card values(1,‘饭卡’);
insert into card values(2,‘建行卡’);
insert into card values(3,‘农行卡’);
insert into card values(4,‘邮政卡’);
insert into card values(5,‘工商卡’);

insert into person values(1,‘张三’,‘1’);
insert into person values(2,‘李四’,‘3’);
insert into person values(3,‘王五’,‘6’);

select * from person;
mysql> select * from person;
±-----±-------±-------+
| id | name | cardId |
±-----±-------±-------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
±-----±-------±-------+
3 rows in set (0.00 sec)

select * from card;

mysql> select * from card;
±-----±----------+
| id | name |
±-----±----------+
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 邮政卡 |
| 5 | 工商卡 |
| 1 | 饭卡 |
±-----±----------+
5 rows in set (0.00 sec)

–没有创建外键约束
–inner join查询
–内联查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据。

select * from person inner join card on person.cardId=card.id;

mysql> select * from person inner join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 2 | 李四 | 3 | 3 | 农行卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
±-----±-------±-------±-----±----------+
2 rows in set (0.00 sec)

select * from person join card on person.cardId=card.id;

–2、left join(左外连接）

select * from person left join card on person.cardId=card.id;

mysql> select * from person left join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 2 | 李四 | 3 | 3 | 农行卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
| 3 | 王五 | 6 | NULL | NULL |
±-----±-------±-------±-----±----------+
3 rows in set (0.00 sec)

–左外连接，会把左边表里的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来
–如果没有，就会补null

–3、右外连接

select * from person right join card on person.cardId=card.id;

mysql> select * from person right join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 邮政卡 |
| NULL | NULL | NULL | 5 | 工商卡 |
±-----±-------±-------±-----±----------+
5 rows in set (0.00 sec)

–右外连接，会把右边表里的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
–如果没有，就会补null

select * from person right outer join card on person.cardId=card.id;

mysql> select * from person right outer join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 邮政卡 |
| NULL | NULL | NULL | 5 | 工商卡 |
±-----±-------±-------±-----±----------+
5 rows in set (0.00 sec)

–4、full join （全外连接）
select * from person full join card on person.cardId=card.id;

select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;

mysql> select * from person left join card on person.cardId=card.id
-> union
-> select * from person right join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 2 | 李四 | 3 | 3 | 农行卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 邮政卡 |
| NULL | NULL | NULL | 5 | 工商卡 |
±-----±-------±-------±-----±----------+
6 rows in set (0.01 sec)

数据库的三大设计范式，sql

—1.第一范式
– 1NF
–数据表中的所有字段都是不可分割的原子值
create table student(
id int primary key,
name varchar(20),
address varchar(20)
);

insert into student values(1,‘张三’,‘中国湖南长沙开福区1’);
insert into student values(2,‘张三’,‘中国湖南长沙开福区2’);
insert into student values(3,‘张三’,‘中国湖南长沙开福区3’);

mysql> select * from student;
±—±-------±-----------------------------+
| id | name | address |
±—±-------±-----------------------------+
| 1 | 张三 | 中国湖南长沙开福区1 |
| 2 | 张三 | 中国湖南长沙开福区2 |
| 3 | 张三 | 中国湖南长沙开福区3 |
±—±-------±-----------------------------+
3 rows in set (0.00 sec)

–字段值可以继续拆分的，就不满足第一范式
create table student1(
id int primary key,
name varchar(20),
country varchar(20),
privence varchar(20),
city varchar(20),
details varchar(20)
);

insert into student1 values(1,‘张三’,‘中国’,‘湖南’,‘长沙’,‘开福区1’);
insert into student1 values(2,‘王五’,‘中国’,‘湖南’,‘长沙’,‘开福区2’);
insert into student1 values(3,‘李四’,‘中国’,‘湖南’,‘长沙’,‘开福区3’);

–范式，设计的越详细，对于某些实际操作可能更好，但是不一定都是好处

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

–订单表
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、第三范式
–3NF
–必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

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

);