MySQL
2022/2/23 2:21:35
本文主要是介绍MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
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)
这篇关于MySQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017，VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7
