必知必会的MySQL基础知识

港控/mmm° 2023-03-13 09:23 13阅读 0赞

> 前言：此博客记录了之前学习MySQL时的一些基础知识，在此更新分享，2021年1月3日更

### 目录 ###

*   *   *  数据库操作
         *   *  登录数据库
             *  退出数据库
             *  显示当前数据库列表
             *  创建数据库
             *  选中某个数据库
         *  数据表操作
         *   *  显示当前数据库中的表
             *  创建表
             *  删除表
             *  插入数据
             *  删除数据
             *  修改数据
             *  查询数据
             *  查看数据表的结构
         *  MySQL常用数据类型
         *  MySQL 建表约束条件
         *   *  主键约束
             *  自增约束
             *  忘记创建主键约束
             *  唯一约束
             *  非空约束
             *  默认约束
             *  外键约束
         *  数据表设计
         *   *  第一范式
             *  第二范式
             *  第三范式
         *  MySQL练习
         *   *  创建表
             *  插入数据
             *  查询练习

### 数据库操作 ###

#### 登录数据库 ####

> mysql -u root -p

#### 退出数据库 ####

> exit;

#### 显示当前数据库列表 ####

> show databases;

#### 创建数据库 ####

> create database Mydatabase;

#### 选中某个数据库 ####

> use mydatabase;

--------------------

### 数据表操作 ###

#### 显示当前数据库中的表 ####

> show tables;

#### 创建表 ####

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

#### 删除表 ####

> DROP TABLE table\_name;

#### 插入数据 ####

> INSERT INTO pet VALUES(‘Puffball’, ‘Diane’, ‘hamster’, ‘f’, ‘1999-03-30’, NULL);

#### 删除数据 ####

> delete from pet where name=‘Fluffy’;

#### 修改数据 ####

> update pet set name=‘WanCai’ where name=‘Puffball’;

#### 查询数据 ####

> select \* from admin;  
> select \* from admin where Admin\_ID=1;

#### 查看数据表的结构 ####

> describe pet;

--------------------

### MySQL常用数据类型 ###

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。  
[MySQL数据类型–菜鸟教程][MySQL]

--------------------

### MySQL 建表约束条件 ###

#### 主键约束 ####

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

> create table user(  
> id int primary key,  
> name VARCHAR(20)  
> );

联合主键：由多个主键组合起来的键，只要联合的主键值加起来不重复就可以！

> create table user2(  
> id int,  
> name VARCHAR(20),  
> password VARCHAR(20),  
> primary key(id, name)  
> );

#### 自增约束 ####

auto\_increment 使得每次插入数据时，设置的主键自动加 1

> create table user3(  
> id int primary key auto\_increment,  
> name VARCHAR(20),  
> );

#### 忘记创建主键约束 ####

> create table user4(  
> id int,  
> name VARCHAR(20),  
> );  
> desc user4;  
> 修改表结构，添加主键  
> alter table user4 add primary key(id);

删除主键

> alter table user4 drop primary key;

使用 modify 修改字段，添加约束

> alter table user4 modify id int primary key;

#### 唯一约束 ####

约束修饰的字段的值不可以重复

> create table user5(  
> id int,  
> name VARCHAR(20)  
> );

给一个字段添加一个唯一约束

> alter table user5 add unique(name);

添加一个唯一约束

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

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

unique(id, name) 表示两个键在一起不重复就 OK

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

删除唯一约束

> alter table user6 drop index name;

modify 添加唯一约束

> alter table user6 modify name varchar(20) unique;

小结：  
1、建表的时候添加约束；  
2、可以使用 alter … add …；  
3、alter … modify …；  
4、删除使用 alter … drop …；

#### 非空约束 ####

修饰的字段不能为 NULL

> create table user7(  
> id int,  
> name VARCHAR(20) not NULL  
> );

#### 默认约束 ####

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

> create table user8(  
> id int,  
> name VARCHAR(20),  
> age int default 10  
> );

#### 外键约束 ####

涉及到两个表：父表和子表  
主表，副表

*  班级表 主表

> create table class(  
> id int primary key,  
> name varchar(20)  
> );

*  学生表 父表

> create table student(  
> id int primary key,  
> name varchar(20),  
> class\_id int,  
> foreign key(class\_id) references class(id)  
> );

> insert into class values(1, ‘一班’);  
> insert into class values(1, ‘二班’);  
> insert into class values(1, ‘三班’);

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

--------------------

### 数据表设计 ###

#### 第一范式 ####

数据表中的所有字段都是不可分割的原子值

> 不满足第一范式  
> create table student2(  
> id int primary key,  
> name varchar(20),  
> adderss varchar(30)  
> );

> insert into student2 values(1, ‘zhangsan’, ‘华为基地E’);  
> insert into student2 values(2, ‘zhangsan’, ‘华为基地E’);  
> insert into student2 values(3, ‘zhangsan’, ‘华为基地E’);

字段值还可以继续拆分的，就不满足第一范式

> create table student2(  
> id int primary key,  
> name varchar(20),  
> country varchar(30),  
> city varchar(30),  
> details varchar(20)  
> );

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

#### 第二范式 ####

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

订单表

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

#### 第三范式 ####

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系

> create table myorder(  
> order\_id int primary key,  
> product\_id int,  
> customer\_id int,  
> customer\_phone varchar(30)  
> );

customer\_phone 依赖与除开主键以外的其他键 customer\_id，不满足第三范式  
应该作如下设计，phone 放入 customer 表中

> create table customer(  
> id int primary key,  
> name varchar(20)，  
> phone varchar(30)  
> );

--------------------

### MySQL练习 ###

#### 创建表 ####

1.  学生表

> create table student(  
> number varchar(20) primary key,  
> name varchar(20) not null,  
> sex varchar(3) not null,  
> birthday datetime,  
> class varchar(20)  
> );

1.  课程表

> create table course(  
> course\_number varchar(20) primary key,  
> course\_name varchar(20) not null,  
> teacher\_number varchar(20) not null,  
> foreign key(teacher\_number) references teacher(teacher\_number)  
> );

1.  教师表

> create table teacher(  
> teacher\_number varchar(20) primary key,  
> name varchar(20) not null,  
> sex varchar(3) not null,  
> birthday datetime,  
> prof varchar(20) not null,  
> depart varchar(20) not null  
> );

1.  成绩表

> create table score(  
> student\_number varchar(20) not null,  
> course\_number varchar(20) not null,  
> degree decimal,  
> foreign key(student\_number) references student(number),  
> foreign key(course\_number) references course(course\_number),  
> primary key(student\_number, course\_number)  
> );

#### 插入数据 ####

> insert into student values(‘101’, ‘小明’, ‘男’, ‘1995-01-17’, ‘三年二班’);

> insert into teacher values(‘1001’, ‘马老师’, ‘男’, ‘1995-01-17’, ‘副教授’, ‘计算机系’);

> insert into course values(‘3-105’, ‘操作系统’, ‘1001’);

> insert into score values(‘101’, ‘3-105’, ‘99’);

#### 查询练习 ####

1.  查询student表中的所有记录的 name、sex、class 列
    
    > select name, sex, class from student;
2.  查询教师所有的单位即不重复的 depart 列
    
    > select distinct depart from teacher;
    
    distinct 排除重复行
3.  查询score 表中成绩在 60-80 之间的所有记录
    
    > select \* from score where degree between 60 and 80;
    
    > select \* from score where degree > 60 and degree < 80;
4.  score 表中成绩为85，86，或88的记录
    
    > select \* from score where degree in(85, 86, 88);
5.  查询 student 表中 “95031” 班或性别为 “女” 的学生记录
    
    > select \* from student where class=‘95031’ or sex=‘女’
6.  以 class 降序查询 student 表中的所有记录（默认为升序 asc）
    
    > select \* from student order by class desc;
7.  以 course\_number 升序、degree 降序查询 score 表的所有记录
    
    > select \* from score order by course\_number asc, degree desc;
8.  查询 “95031” 班的学生人数（统计 count）
    
    > select count(\*) from student where class=‘95031’
9.  查询 score 表中的最高分的学生学号和课程号。（子查询或者排序）
    
    > select student\_number, course\_number from score where degree=(select max(degree) from score)
    
    1）找到最高分  
    2）找最高分的 student\_number, course\_number  
    3）排序的做法：
    
    > select student\_number, course\_number from score order by degree desc limit 0,1;
    
    limit 0，1 第一个数字表示从多少开始，第二个数字表示多少条
10. 查询每门课的平均成绩（avg 平均）
    
    > select course\_number, avg(degree) from score group by course\_number;
    
    group by 分组
11. 查询 score 表中至少有 2 名学生选修的并以 3 开头的课程的平均分数
    
    > select course\_number, avg(degree) from score group by course\_number having count(course\_number)>=2 and course\_number like ‘3%’;
12. 查询分数大于70，小于90的 course\_number 列
    
    > select course\_number, degree from score where degree > 70 and degree < 90;
13. 查询所有学生的 name、course\_number 和 degree 列
    
    > select name, course\_number, degree from student, score where student.number=score.student\_number;
14. 查询所有学生的 number、course\_name 和 degree 列
    
    > select number, course\_name, degree from courre, score where course.course\_number = score.course\_number;

--------------------

> 作者： Caso\_卡索  
> 博客: https://kasuo.blog.csdn.net  
> 版权声明：未经允许，禁止转载  
> 后续会继续更新，喜欢的朋友收藏 + 关注，谢谢 ^\_^

[MySQL]: https://www.runoob.com/mysql/mysql-data-types.html