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