必知必會的MySQL基礎知識

前言：此博客記錄了之前學習MySQL時的一些基礎知識，在此更新分享，2021年1月3日更

資料庫操作

登錄資料庫

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 建表約束條件

主鍵約束

它能夠唯一確定一張表中的一條記錄，通過給某個欄位添加約束，就可以使得該欄位不重復且不為空，

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

2. 課程表

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

3. 教師表

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

4. 成績表

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
著作權宣告：未經允許，禁止轉載
后續會繼續更新，喜歡的朋友收藏 + 關注，謝謝 ^_^