1、终端操作MySQL数据库
1.1如何登陆数据库服务器：
C:Usersusername>mysql -uroot -proot
1.2如何查询数据库服务器中的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.03 sec)
复制代码1.3如何选中某一个数据库进行操作
mysql> use test1
Database changed
复制代码1.4SQL语句中的查问
《2020最新Java根底精讲视频教程和学习路线！》
mysql> select * from person;
+-----------+------+
| name | sex |
+-----------+------+
| 张三 | 男 |
| 李四 | 女 |
| 王麻子 | 男 |
+-----------+------+
3 rows in set (0.00 sec)
mysql> select * from person where name="张三";
+--------+------+
| name | sex |
+--------+------+
| 张三 | 男 |
+--------+------+
1 row in set (0.00 sec)
复制代码1.5如何退出数据库服务器
mysql> exit;
Bye
复制代码2、如何创立数据库数据表
2.1如何在数据库服务器中创立咱们的数据库
mysql> create database test2;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test2;
Database changed
复制代码2.2如何查看某个数据库中的所有数据表
mysql> show tables;
Empty set (0.00 sec)
复制代码2.3如何创立一个数据表
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.09 sec)
复制代码创立了一个宠物数据表
查看数据表是否创立胜利
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| pet |
+-----------------+
1 row in set (0.00 sec)
复制代码2.4查看创立好的数据表的构造
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.00 sec)
复制代码3、insert减少数据记录
3.1查看数据表中的记录
mysql> select * from pet;
Empty set (0.00 sec)
复制代码3.2如何往数据表中增加数据记录
mysql> insert into pet
-> values('Tom','LiMing','hamster','f','2020-11-11',null);
Query OK, 1 row affected (0.01 sec)
复制代码再一次查问
mysql> select * from pet;
+------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+------+--------+---------+------+------------+-------+
| Tom | LiMing | hamster | f | 2020-11-11 | NULL |
+------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
复制代码mysql> insert into pet
-> values('旺财','周星星','狗','公','2020-11-11',null);
Query OK, 1 row affected (0.01 sec)
复制代码查看
mysql> select * from pet;
+--------+-----------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+-----------+---------+------+------------+-------+
| Tom | LiMing | hamster | f | 2020-11-11 | NULL |
| 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL |
+--------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)
复制代码3.3MySQL罕用数据类型
MySQL反对多种类型，大抵能够分为三类：数值、日期/工夫和字符串(字符)类型。
- 数值类型
如果数值超出数值类型的范畴就会报错：
mysql> create table testType(
-> number tinyint);
Query OK, 0 rows affected (0.10 sec)
mysql> describe testType;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | tinyint | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into testType
-> values(128);
ERROR 1264 (22003): Out of range value for column 'number' at row 1
复制代码- 日期和工夫类型
- 字符串类型
数据类型如何抉择
日期：依照日期格局
数值和字符串：依照大小
4、数据记录增删查改操作
4.1插入：
mysql> insert into pet values('Buffy','Job','cat','m','2020-11-10',null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into pet values('Slim','邓紫棋','dog','f','2019-11-11',null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into pet
-> values('Fang','成龙','cat','m','2018-11-11','2020-10-10');
Query OK, 1 row affected (0.01 sec)
mysql> select * from pet;
+--------+-----------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-----------+---------+------+------------+------------+
| Tom | LiMing | hamster | f | 2020-11-11 | NULL |
| 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL |
| Buffy | Job | cat | m | 2020-11-10 | NULL |
| Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL |
| Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |
+--------+-----------+---------+------+------------+------------+
5 rows in set (0.00 sec)
复制代码4.2如何删除数据
mysql> delete from pet where name='Tom';
Query OK, 1 row affected (0.01 sec)
复制代码再次查看
mysql> select * from pet;
+--------+-----------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-----------+---------+------+------------+------------+
| 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL |
| Buffy | Job | cat | m | 2020-11-10 | NULL |
| Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL |
| Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |
+--------+-----------+---------+------+------------+------------+
4 rows in set (0.00 sec)
复制代码4.3如何批改数据
mysql> update pet set name='旺财财' where owner='周星星';
Query OK, 1 row affected (0.01 sec)
复制代码再次查看：
mysql> select * from pet;
+-----------+-----------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+-----------+-----------+---------+------+------------+------------+
| 旺财财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL |
| Buffy | Job | cat | m | 2020-11-10 | NULL |
| Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL |
| Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |
+-----------+-----------+---------+------+------------+------------+
4 rows in set (0.00 sec)
复制代码4.4总结：数据记录常见操作
减少：insert
删除：delete
批改：update
查问：select
5、MySQL建表束缚
5.1主键束缚 primary key
它可能惟一确定一张表中的一条记录，也就是咱们通过给某个字段增加束缚，就能够使得该字段不反复且不为空。
mysql> create table user(
-> id int primary key,
-> name varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码再次插入id=1的记录
mysql> insert into user values(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
复制代码id为空的时候
mysql> insert into user values(null,'李四');
ERROR 1048 (23000): Column 'id' cannot be null
复制代码5.2联结主键
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id,name)
-> );
Query OK, 0 rows affected (0.06 sec)
复制代码插入记录
mysql> insert into user2 values(1,'zhangsan','123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user2 values(1,'zhangsan','234');
ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user2.PRIMARY'
mysql> insert into user2 values(2,'zhangsan','234');
Query OK, 1 row affected (0.01 sec)
复制代码联结主键只有主键字段之间加起来不反复就能够，但任何一个主键字段都不能够为空。
5.3自增束缚auto_increment
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
复制代码插入数据
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
查看
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
复制代码自增束缚和主键束缚搭配在一起应用时，它会主动给咱们管去控值，例如：主动减少id。
5.4如果创立表的时候遗记创立主键束缚怎么办？
mysql> create table user4(
-> id int,
-> name varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码批改表构造，减少主键束缚 alter … add
mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码如何删除呢？alter …drop
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码应用modify，批改字段，增加束缚
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码5.5惟一束缚
束缚润饰的字段的值不能够反复。
mysql> create table user5(
-> id int,
-> name varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.03 sec)
mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'
复制代码name=lisi不能反复
mysql> insert into user5 values(1,'lisi');
Query OK, 1 row affected (0.01 sec)
mysql> create table user6(
-> id int,
-> name varchar(20),
-> unique(name));
Query OK, 0 rows affected (0.07 sec)
复制代码能够对id和name都增加惟一束缚，这时候只有id+name不反复就能够增加记录。
mysql> create table user7(
-> id int,
-> name varchar(20),
-> unique(id,name));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into user7 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user7 values(2,'zhangsan');
Query OK, 1 row affected (0.04 sec)
mysql> select * from user7;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
复制代码如何删除惟一束缚
mysql> alter table user6 drop index name;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码通过modify增加惟一束缚
mysql> alter table user6 modify name varchar(20) unique;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码总结：
- 建表的时候增加束缚
- 能够用alter… add增加
- 能够用alter… modify增加束缚
- 删除用alter…drop
5.6非空束缚
润饰的字段不能为空null
mysql> create table user8(
-> id int,
-> name varchar(20) not null);
Query OK, 0 rows affected (0.05 sec)
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码测试：如果name为空
mysql> insert into user8 (id)values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
复制代码正确增加不为空时
mysql> insert into user8 values(1,'lisi');
Query OK, 1 row affected (0.01 sec)
mysql> select *from user8;
+------+------+
| id | name |
+------+------+
| 1 | lisi |
+------+------+
1 row in set (0.00 sec)
复制代码如果只传name是能够的
mysql> insert into user8 (name)values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select *from user8;
+------+----------+
| id | name |
+------+----------+
| 1 | lisi |
| NULL | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
复制代码同样能够用alter ... add，alter ... modify来增加惟一束缚。
5.7默认束缚default
就是当咱们插入字段值的时候，如果没有传值，就会应用默认值。
mysql> create table user9(
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected (0.06 sec)
复制代码查看表构造
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制代码插入值时直插入id和name那么age应用默认值10，如果age也插入值那么就应用插入的值不应用默认值。
mysql> insert into user9(id,name) values (1,'lisi');
Query OK, 1 row affected (0.01 sec)
mysql> select *from user9;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | lisi | 10 |
+------+------+------+
1 row in set (0.00 sec)
复制代码5.8 外键束缚
波及到两个表：父表，子表。
班级表：
mysql> create table classes(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码插入值
mysql> insert into classes values(1,'yiban');
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values(2,'erban');
Query OK, 1 row affected (0.03 sec)
mysql> insert into classes values(3,'sanban');
Query OK, 1 row affected (0.01 sec)
mysql> select *from classes;
+----+--------+
| id | name |
+----+--------+
| 1 | yiban |
| 2 | erban |
| 3 | sanban |
+----+--------+
3 rows in set (0.00 sec)
复制代码学生表：
mysql> create table students(
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references classes(id));
Query OK, 0 rows affected (0.06 sec)
复制代码mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制代码插入值
mysql> insert into students values(1001,'zhangsan',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1002,'zhangsan',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1003,'zhangsan',3);
Query OK, 1 row affected (0.04 sec)
mysql> select *from students;
+------+----------+----------+
| id | name | class_id |
+------+----------+----------+
| 1001 | zhangsan | 1 |
| 1002 | zhangsan | 2 |
| 1003 | zhangsan | 3 |
+------+----------+----------+
3 rows in set (0.00 sec)
复制代码父表classes中没有的数据值，是不能够在子表students中应用的。
父表中的记录被子表援用，是不能够被删除的。
mysql> delete from classes where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (`test2`.`students`,
CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
复制代码数据库三大范式
- 第一范式 1NF
-
- *
数据库表中的所有字段都是不可分割的原子值。
mysql> create table student(
-> id int primary key,
-> name varchar(20),
-> address varchar(30)
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> insert into student values(1,'zhangsan','甘肃会宁');
Query OK, 1 row affected (0.02 sec)
mysql> insert into student values(2,'lisi','甘肃会宁');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(3,'laowang','甘肃会宁');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+----------+--------------+
| id | name | address |
+----+----------+--------------+
| 1 | zhangsan | 甘肃会宁 |
| 2 | lisi | 甘肃会宁 |
| 3 | laowang | 甘肃会宁 |
+----+----------+--------------+
3 rows in set (0.00 sec)
复制代码address字段值还能够持续拆分的就不满足第一范式。
create table student2(
id int primary key,
name varchar(20),
country varchar(30),
privence varchar(30),
city varchar(30),
details varchar(30)
);
复制代码复制代码insert into student2 values(1,’张三’,’中国’,’甘肃’,’兰州’,’安宁区安定街道30号’); insert into student2 values(2,’李四’,’中国’,’甘肃’,’兰州’,’安宁区安定街道34号’); insert into student2 values(3,’王五’,’中国’,’甘肃’,’兰州’,’安宁区安定街道80号’);
mysql> select * from student2;
+----+--------+---------+----------+--------+----------------------------+
| id | name | country | privence | city | details |
+----+--------+---------+----------+--------+----------------------------+
| 1 | 张三 | 中国 | 甘肃 | 兰州 | 安宁区安定街道30号 |
| 2 | 李四 | 中国 | 甘肃 | 兰州 | 安宁区安定街道34号 |
| 3 | 王五 | 中国 | 甘肃 | 兰州 | 安宁区安定街道80号 |
+----+--------+---------+----------+--------+----------------------------+
3 rows in set (0.00 sec)
复制代码范式：设计的越具体，对于某些实际操作可能更好，但不肯定都有益处。个别要结合实际状况设计。
- 第二范式 2NF
-
- *
第二范式必须是满足第一范式的前提下，要求，除主键外的每一列都必须齐全依赖于主键。 如果呈现不齐全依赖，只可能产生在联结主键的状况下。
create table myolder(
product_id int,
customer_id int,
product_name varchar(30),
customer_name varchar(30),
primary key(product_id,customer_id)
);
复制代码问题： 除主键外的其余列，只依赖于主键的局部字段。 不满足第二范式 解决办法是拆表。
create table myolder(
older_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 myolder(
older_id int primary key,
product_id int,
customer_id int,
customer_phone varchar(15)
);
复制代码在这个表中，咱们能够看到customer_phone和customer_id之间存在依赖关系，这样就不满足第三范式的要求；解决办法是：咱们把customer_phone放到customer表中。如下：
create table customer(
id int primary key,
name varchar(20),
phone varchar(15)
);
复制代码sql的四种连贯查问
内连贯
inner join 或者join
外连贯
左连贯 left join 或者left outer join
右连贯 right join 或者right outer join
齐全外连贯
full join 或者full outer join
复制代码创立两个表：
person表
id，name，cardID
1 张三 1
2 李四 3
3 王五 6
card表
id,name
1 饭卡
2 建行卡
3 农行卡
4 工商卡
5 邮政卡
并没有创立外键
--inner join查问（内连贯）
SELECT * from person INNER JOIN card ON person.cardID=card.id;
id name cardid id name
1 张三 1 1 饭卡
2 李四 3 3 农行卡
--内连查问，其实就是两张表中的数据，通过某个字段相等查问出相干记录数据。
--left join左外连贯
SELECT * FROM person LEFT JOIN card ON person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
---左外连贯，会把右边表里所有的数据取出来，而左边表里的数据，如果有相等的，就显示进去，如果没有就补null。
----right join右外连贯
SELECT * FROM person RIGHT JOIN card ON person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)
---右外连贯，会把左边表里所有的数据取出来，而右边表里的数据，如果有相等的，就显示进去，如果没有就补null。
--------------------------------------------------------------------
---------------------------------------------------------------------
----full join全外连贯
---MySQL不反对全外连贯
---只能通过union来实现
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 |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
6 rows in set (0.00 sec)
复制代码事务
MySQL事务
mysql 中，事务其实是一个最小的不可分割的工作单元。事务可能保障一个业务的完整性。
比方咱们的银行转账:
a -> -100
update user set money=money-100 where name='a' ;
b -> +100
update user set money=money+100 where name='b ';
复制代码理论的程序中，如果只有一条语句执行胜利了，而另外一条没有执行胜利，就会呈现数据前后不统一。
多条sql语句，可能会有同时胜利的要求，要么就同时失败。
MySQL中如何管制事务
- MySQL是默认开启事务的（主动开启）
SELECT @@autocommit;
-------------
@@autocommit
1
-------------
复制代码默认事务开启的作用是什么?
当咱们去执行一个sql语句的时候，成果会立刻体现进去，且不能回滚。
先创立一个表：
create database bank;
create table user(
id int primary key,
name varchar(20),
money int
);
insert into user values( 1, 'a' ,1000);
复制代码事务回滚：撤销sql语句执行成果。rollback;
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select *from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
复制代码设置mysql主动提交为false。
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
复制代码下面的操作，敞开了mysql的主动提交(commit)。
咱们再插入一条记录：
mysql> insert into user values(2,'b',1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
复制代码插入第二条数据其实没有插入到理论表中；
要想插入胜利，则进行如下操作：
再次插入
mysql> insert into user values(2,'b',1000);
Query OK, 1 row affected (0.00 sec)
手动提交数据
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
撤销不能够（持久性）
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
复制代码总结：
主动提交：@@autocommit
手动提交：commit
事务回滚：rollback
例如转账操作：
mysql> update user set money=money-100 where name='a' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name='b ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
复制代码事务给咱们提供了一个返回的机会。
手动开启事务
两种形式
begin;
start transaction;
复制代码mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set money=money-100 where name='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name='b ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 800 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
复制代码事务开启之后，一旦commit提交，就不能回滚了（也就是以后的这个事务在提交的时候就完结了）。
事务的一些基本概念
事务的四大特色ACID
A 原子性：事务是最小的单位，不能够在宰割。
C 一致性：事务要求，同一事务中的sql语句，必须保障同时胜利或者同时失败。
I 隔离性：事务1和事务2之间是具备隔离性的。
D 持久性：事务一旦完结（commit，rollback），就不能够返回。
事务开启
1。批改默认提交set autocommit=0;
begin;start transaction;
事务手动提交
commit;
事务手动回滚
rollback;
事务的隔离性
1、read uncommitted;
读未提交的
2、read committed;
读曾经提交的
3、repeatable read;
能够反复读
4、serializable;
串行化
mysql 默认隔离级别REPEATABLE-READ
解释：
如果有事务a,和事务b,
a事务对数据进行操作，在操作的过程中，事务没有被提交，然而b 能够看见a操作的后果。
eg：bank数据库的user表：
insert into user values( 3,'小明',1000);
insert into user values(4,'淘宝店' ,1000) ;
如何查看数据库的隔离级别：
零碎级别：select @@global.transaction_isolation;
会话级别：select @transaction_isolation;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
1 row in set (0.00 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> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED |
+--------------------------------+
1 row in set (0.00 sec)
复制代码转账:小明在淘宝店买鞋子:800块钱,
小明-》成都ATM
淘宝店-》广州ATM
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 | 900 |
| 2 | b | 1000 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)
复制代码如果两个不同的中央，都在进行操作，事务a 开启之后，他的数据能够被其余事务读取到。这样就会呈现(脏读)。
脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读。
理论开发是不容许脏读呈现的。
不可反复读 尽管我只能读到另外一个事务提交的数据，但还是会呈现问题，就是读取同一个表的数据，发现前后不统一。不可反复读景象。
幻读事务a和事务b 同时操作一张表，事务a提交的数据，也不能被事务b读到，就能够造成幻读。
串行化 问题是，性能特差!!!READ—UNCOMMITTED > READ-COMMITTED > REPEATABLE-READ > SERIALIZABLE;
隔离级别越高，性能越差。
原文链接：https://juejin.cn/post/689819…
发表回复