MySQL

#符号需要都是英文半角的哦
#连接服务器
C:\Users\ZSH>mysql -uroot -p
Enter password: ******

#展示一下数据库服务器下面有哪些数据库
mysql> show databases;
#新装的数据库会有四个自带的库，不能动
+--------------------+
| Database           |
+--------------------+
| information_schema |           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#创建数据库
mysql> create database javademo1;
Query OK, 1 row affected (0.00 sec)

#删除数据库
mysql> drop database javademo1;
Query OK, 0 rows affected (0.00 sec)

#使用库
mysql> use javademo1;
Database changed
#这选中的库中建表
#语法格式： create  table  表名 (字段1 数据类型1, 字段2  数据类型2 .....)
mysql> create table person (id int ,name varchar(32),sex boolean,age int ,info text);
Query OK, 0 rows affected (0.03 sec)
#int  默认的是11位
#varchar 是32位
#text 文本，不限制有多少个字符

#查看库下的表
mysql> show tables;
+---------------------+
| Tables_in_javademo1 |
+---------------------+
| person              |
+---------------------+
1 row in set (0.00 sec)

#查看一下表结构
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

1.alter修改表结构

#查看一下表结构
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#把表person里面的字段info删除掉 （drop）
mysql> alter table person drop info;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

#在表中添加一个字段，在最后面添加 （add）
mysql> alter table person add info text;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

#想要在name之后，age之前添加一个字段（add 新字段 after name）
mysql> alter table person add job varchar(32) after name;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

#修改name字段 的数据类型  varchar(32)  修改为 char(32)（modify）
mysql> alter table person modify name char(32);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

#修改字段和数据类型  同时修改（change）
mysql> alter table person change sex gender char(32);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查看一下表结构
mysql> desc person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | char(32)    | YES  |     | NULL    |       |
| job    | varchar(32) | YES  |     | NULL    |       |
| gender | char(32)    | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| info   | text        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

2.添加数据（insert）

#插入数据（增）
#第一种方式
mysql> insert person (id,name,job,gender,age,info) value(1,"沈腾","演员","男",30,"曾是军校校草，现在真的很喜剧");
Query OK, 1 row affected (0.01 sec)

#第二种方式
mysql> insert person value(2,"杨洋","演员","男",28,"也曾是军校校草，现在也帅的一批");
Query OK, 1 row affected (0.00 sec)

#查看表的所有数据（查）
mysql> select * from person ;
+------+--------+--------+--------+------+-----------------------------------------------+
| id   | name   | job    | gender | age  | info                                          |
+------+--------+--------+--------+------+-----------------------------------------------+
|    1 | 沈腾   | 演员   | 男     |   30 | 曾是军校校草，现在真的很喜剧                  |
|    2 | 杨洋   | 演员   | 男     |   28 | 也曾是军校校草，现在也帅的一批                |
+------+--------+--------+--------+------+-----------------------------------------------+
2 rows in set (0.00 sec)

3.删除数据（delete）

mysql> insert person value(3,"艾伦","谐星","男",28,"沈腾的搭档，主要就体现一个大聪明的特性");
Query OK, 1 row affected (0.01 sec)
mysql> insert person value(4,"xx","xxx","x",00,"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
Query OK, 1 row affected (0.01 sec)

mysql> select * from person ;
+------+--------+--------+--------+------+-----------------------------------------------------------+
| id   | name   | job    | gender | age  | info                                                      |
+------+--------+--------+--------+------+-----------------------------------------------------------+
|    1 | 沈腾   | 演员   | 男     |   30 | 曾是军校校草，现在真的很喜剧                              |
|    2 | 杨洋   | 演员   | 男     |   28 | 也曾是军校校草，现在也帅的一批                            |
|    3 | 艾伦   | 谐星   | 男     |   28 | 沈腾的搭档，主要就体现一个大聪明的特性                    |
|    4 | xx     | xxx    | x      |    0 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx                            |
+------+--------+--------+--------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

#删除id = 4的数据
mysql> delete from person where id = 4;
Query OK, 1 row affected (0.01 sec)

#查看是否删除
mysql> select * from person ;
+------+--------+--------+--------+------+-----------------------------------------------------------+
| id   | name   | job    | gender | age  | info                                                      |
+------+--------+--------+--------+------+-----------------------------------------------------------+
|    1 | 沈腾   | 演员   | 男     |   30 | 曾是军校校草，现在真的很喜剧                              |
|    2 | 杨洋   | 演员   | 男     |   28 | 也曾是军校校草，现在也帅的一批                            |
|    3 | 艾伦   | 谐星   | 男     |   28 | 沈腾的搭档，主要就体现一个大聪明的特性                    |
+------+--------+--------+--------+------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

4.修改数据（update）

#更改数据
mysql> update person set name = "薛之谦",job = "歌手",gender = "男" ,age =  26, info = "虽然是个歌手，但也是个逗比" where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#查看是否更改
mysql> select * from person ;
+------+-----------+--------+--------+------+-----------------------------------------------+
| id   | name      | job    | gender | age  | info                                          |
+------+-----------+--------+--------+------+-----------------------------------------------+
|    1 | 沈腾      | 演员   | 男     |   30 | 曾是军校校草，现在真的很喜剧                  |
|    2 | 杨洋      | 演员   | 男     |   28 | 也曾是军校校草，现在也帅的一批                |
|    3 | 薛之谦    | 歌手   | 男     |   26 | 虽然是个歌手，但也是个逗比                    |
+------+-----------+--------+--------+------+-----------------------------------------------+
3 rows in set (0.00 sec)

5.查询数据（select）

#查询表中的所有数据
mysql> select * from person;
+------+-----------+--------+--------+------+-----------------------------------------------+
| id   | name      | job    | gender | age  | info                                          |
+------+-----------+--------+--------+------+-----------------------------------------------+
|    1 | 沈腾      | 演员   | 男     |   30 | 曾是军校校草，现在真的很喜剧                  |
|    2 | 杨洋      | 演员   | 男     |   28 | 也曾是军校校草，现在也帅的一批                |
|    3 | 薛之谦    | 歌手   | 男     |   26 | 虽然是个歌手，但也是个逗比                    |
+------+-----------+--------+--------+------+-----------------------------------------------+
3 rows in set (0.00 sec)
#查询指定数据
mysql> select id,name from person;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 沈腾      |
|    2 | 杨洋      |
|    3 | 薛之谦    |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select id,name,info from person where age = 28 ;
+------+--------+-----------------------------------------------+
| id   | name   | info                                          |
+------+--------+-----------------------------------------------+
|    2 | 杨洋   | 也曾是军校校草，现在也帅的一批                |
+------+--------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,name,info from person where age >26 ;
+------+--------+-----------------------------------------------+
| id   | name   | info                                          |
+------+--------+-----------------------------------------------+
|    1 | 沈腾   | 曾是军校校草，现在真的很喜剧                  |
|    2 | 杨洋   | 也曾是军校校草，现在也帅的一批                |
+------+--------+-----------------------------------------------+
2 rows in set (0.00 sec)

6.数据约束

在创建表的时候，一定要对字段进行设置，在你插入数据的时候要按照当前字段要求进行处插入数据，不然就会报错

6.1默认值(default)

#在插入数据的时候，如果没有给当前字段进行赋值，可以在创建表的时候带一个默认的值
mysql> create table athlete
    -> (id int ,
    -> name varchar(32),
    -> nation varchar(32) default "中国",
    -> item char(32));
Query OK, 0 rows affected (0.02 sec)

mysql> insert athlete (id,name,item) value (1,"谷爱凌","女子U型滑");
Query OK, 1 row affected (0.01 sec)

mysql> insert athlete (id,name,item) value (2,"武大靖","速滑");
Query OK, 1 row affected (0.01 sec)

mysql> select * from athlete;
+------+-----------+--------+---------------+
| id   | name      | nation | item          |
+------+-----------+--------+---------------+
|    1 | 谷爱凌    | 中国   | 女子U型滑     |
|    2 | 武大靖    | 中国   | 速滑          |
+------+-----------+--------+---------------+
2 rows in set (0.00 sec)

6.2非空和唯一

not null 不能为空

unique 唯一

mysql> create table athlete1
    -> (id int unique,
    -> name varchar(32) not null,
    -> nation varchar(32) default "PRC",
    -> item char(32) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert athlete1 (id,name,item) value (1,"武大靖","速滑");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> insert athlete1 (id,name,item) value (2,"武大靖","速滑");
Query OK, 1 row affected (0.01 sec)

mysql> select * from athlete1;
+------+-----------+--------+--------+
| id   | name      | nation | item   |
+------+-----------+--------+--------+
|    1 | 武大靖    | PRC    | 速滑   |
|    2 | 武大靖    | PRC    | 速滑   |
+------+-----------+--------+--------+
2 rows in set (0.00 sec)

6.3主键

primary key 主键，是唯一和非空的组合

在设计数据库的时候，一定要有一个主键字段

mysql> create table athlete2
    -> (id int primary key,
    -> name varchar(32) unique,
    -> nation varchar(32) default "中国",
    -> item varchar(32) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert athlete2 (id,name,item) value (1,"武大靖","速滑");
Query OK, 1 row affected (0.01 sec)

mysql> insert athlete2 (id,name,item) value (1,"谷爱凌","女子U型滑");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

6.4自增长

#在插入数据的时候，有些字段是可以实现自增长的
#auto_increment
#使用自增长约束字段那么字段必须是整数类型，而且一般是主键
#插入第一个数据的时候主键值是1  不是0
#在删除最后一条数据的时候，然后再插入一条数据，主键的值在原来被删除id的值的基础之上自增1
mysql> insert athlete2 (id,name,item) value (1,"谷爱凌","女子U型滑");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> create table athlete3
    -> (id int primary key auto_increment,
    -> name varchar(32) unique,
    -> nation varchar(32) default "中国",
    -> item varchar(32) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert athlete3 (name,item) value ("武大靖","速滑");
Query OK, 1 row affected (0.00 sec)

mysql> insert athlete3 (name,item) value ("谷爱凌","女子U型滑");
Query OK, 1 row affected (0.01 sec)

mysql> select * from athlete3;
+----+-----------+--------+---------------+
| id | name      | nation | item          |
+----+-----------+--------+---------------+
|  1 | 武大靖    | 中国   | 速滑          |
|  2 | 谷爱凌    | 中国   | 女子U型滑     |
+----+-----------+--------+---------------+
2 rows in set (0.00 sec)

7.外键的约束

#现在创建一个员工表  id  empName deptName  regTime

mysql> create table employee(
    -> id int primary key auto_increment,
    -> empName varchar(32) not null,
    -> deptName varchar(32) not null,
    -> regTime timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into employee(empName, deptName) values("张三", "研发部");
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee(empName, deptName) values("李四", "研发部");
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee(empName, deptName) values("恶魔波刚", "销售部");
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee(empName, deptName) values("小龙", "后勤部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee(empName, deptName) values("黑手", "后勤部");
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+-----------+-----------+---------------------+
| id | empName   | deptName  | regTime             |
+----+-----------+-----------+---------------------+
|  1 | 张三       | 教学部    | 2022-02-18 11:31:39 |
|  2 | 李四       | 教学部    | 2022-02-18 11:31:45 |
|  3 | 恶魔波刚    | 销售部    | 2022-02-18 11:32:05 |
|  4 | 小龙       | 后勤部    | 2022-02-18 11:32:39 |
|  5 | 黑手       | 后勤部    | 2022-02-18 11:32:50 |
+----+-----------+-----------+---------------------+
5 rows in set (0.01 sec)
#感觉在建表的时候不合适，部门的数据冗余。这样不好
#拆开两张表，一个是部门表  一个是员工表
mysql> create table employee (
    -> id int primary key auto_increment,
    -> empName varchar(32) not null,
    -> deptId int not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table dept (
    -> id int primary key auto_increment,
    -> deptName varchar(32) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dept (deptName) values("研发部");
Query OK, 1 row affected (0.01 sec)

mysql> insert into dept (deptName) values("销售部");
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept (deptName) values("后勤部");
Query OK, 1 row affected (0.00 sec)

mysql> select * from deopt;
ERROR 1146 (42S02): Table 'java2115.deopt' doesnt exist
mysql> select * from dept;
+----+-----------+
| id | deptName  |
+----+-----------+
|  1 | 研发部     |
|  2 | 销售部     |
|  3 | 后勤部     |
+----+-----------+
3 rows in set (0.00 sec)

mysql> insert into employee (empName, deptId) values("张三", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee (empName, deptId) values("李四", 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee (empName, deptId) values("恶魔波刚", 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee (empName, deptId) values("小龙", 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee (empName, deptId) values("黑手", 3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+-----------+--------+
| id | empName   | deptId |
+----+-----------+--------+
|  1 | 张三      |      1 |
|  2 | 李四      |      1 |
|  3 | 恶魔波刚   |      2 |
|  4 | 小龙      |      3 |
|  5 | 黑手      |      3 |
+----+-----------+--------+
4 rows in set (0.00 sec)
#给员工插入一个数据，但是部门不存在，现在能不能插入成功？ 能
#但是不符合开发需求
mysql> insert into employee (empName, deptId) values("周杰伦", 7);
Query OK, 1 row affected (0.02 sec)

#删除了一个部门，部门下面的人也没了，但是现在有
mysql> delete from dept where id = 3;
Query OK, 1 row affected (0.02 sec)

mysql> select * from employee;
+----+-----------+--------+
| id | empName   | deptId |
+----+-----------+--------+
|  1 | 张三      |      1 |
|  2 | 李四      |      1 |
|  3 | 恶魔波刚   |      2 |
|  4 | 小龙      |      3 |
|  5 | 黑手      |      3 |
|  6 | 周杰伦     |      7 |
+----+-----------+--------+
#怎么解决以上业务不符合的场景
#给加外键的约束
mysql> drop table employee;
Query OK, 0 rows affected (0.02 sec)

mysql> create table employee (
    -> id int primary key auto_increment,
    -> empName varchar(32) not null,
    -> deptId int not null,
    -> regTime timestamp default current_timestamp,
    #constraint  约束的意思
    # fk_emp_dep  外键的名字  
    #foreign key(deptId)外键  本表里面去关联  另外一张表的一个字段
    #references  关联  本表里面的一个字段 deoptId去关联另外一张表dept的id
    -> constraint  fk_emp_dep foreign key(deptId) references dept(id)
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into employee (empName, deptId) values("谷爱凌", 1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into employee (empName, deptId) values("武大靖", 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee (empName, deptId) values("冰墩墩", 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+----------+--------+---------------------+
| id | empName  | deptId | regTime             |
+----+----------+--------+---------------------+
|  1 | 谷爱凌    |      1 | 2022-02-18 14:24:41 |
|  2 | 武大靖    |      1 | 2022-02-18 14:25:04 |
|  3 | 冰墩墩    |      2 | 2022-02-18 14:25:17 |
+----+---------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+----+-----------+
| id | deptName  |
+----+-----------+
|  1 | 竞技部     |
|  2 | 迎宾部     |
+----+-----------+
2 rows in set (0.03 sec)

#在员工表中添加数据的时候，如果添加的deptId这个值，在另外一张表（部门表）中id没有这个值，就报错
mysql> insert into employee (empName, deptId) values("恶魔波刚", 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`javademo1`.`employee`, CONSTRAINT `fk_emp_dep` FOREIGN KEY (`deptId`) REFE
RENCES `dept` (`id`))
#删除一个部门数据，会报错吗？会的
mysql> delete from  dept where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`javademo1`.`employee`, CONSTRAINT `fk_emp_dep` FOREIGN KEY (`deptId`)
REFERENCES `dept` (`id`))
mysql>
#以上的解决方案
#如果两张添加了外键约束，会有你以下的特点
#1.若是添加，先添加部门表[主表]。再添加员工表【从表】
#2.先删除员工表【从表】，再删除部门表【主表】
#3.修改从表【员工表】,在修改部门表
#增加了外键约束以后，那么你的数据变成了一坨。操作起来不太方便，怎么解决？
#级联操作，也是在设计表的时候加上的
mysql> create table employee (
    -> id int primary key auto_increment,
    -> empName varchar(32) not null,
    -> deptId int not null,
    -> regTime timestamp default current_timestamp,
    -> constraint  fk_emp_dep foreign key(deptId) references dept(id)
    -> on delete cascade
    -> on update cascade
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into employee (empName, deptId) values("谷爱凌", 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee (empName, deptId) values("武大靖", 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee (empName, deptId) values("冰墩墩", 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+---------+--------+---------------------+
| id | empName | deptId | regTime             |
+----+---------+--------+---------------------+
|  1 | 谷爱凌   |      1 | 2022-02-18 14:38:03 |
|  2 | 武大靖   |      1 | 2022-02-18 14:38:05 |
|  3 | 冰墩墩   |      2 | 2022-02-18 14:38:08 |
+----+---------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+----+-----------+
| id | deptName  |
+----+-----------+
|  1 | 竞技部    |
|  2 | 迎宾部    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> delete from dept where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+---------+--------+---------------------+
| id | empName | deptId | regTime             |
+----+---------+--------+---------------------+
|  3 | 冰墩墩   |      2 | 2022-02-18 14:38:08 |
+----+---------+--------+---------------------+
1 row in set (0.01 sec)

mysql> select * from dept;
+----+-----------+
| id | deptName  |
+----+-----------+
|  2 | 迎宾部     |
+----+-----------+
1 row in set (0.00 sec)