SQL（一）创建数据库

1.数据库相关知识

数据库

• 关系型
• NoSQL

**数据持久化：**将数据保存到能够长久保存数据的存储介质中，在掉电的情况下数据也不会丢失

数据库优点：

• 可多人同时操作
• 权限管理
• 数据体量大

Excel行一般只能达到一百万+，列数六万+

二维表：

• 数据
• 行（记录，元组）
• 列（字段，属性）
• 关系键
  • 主键：唯一的标识一条记录，行前面的序号123…
  • 外键
• 实体

SQL（结构化查询语言）：

• 领域特定语言，直观，强大，容易上手
• 声明式编程语言，不关注过程，只关注结果
  • 数据定义语言（DDL）-create创建/drop删除/alter修改/rename重命名/truncate截断
  • 数据操作语言（DML）-insert插入/delete删除/update更新/select选择
  • 数据控制语言（DCL）-grant授权/revoke召回
  • 事务控制语言（TCL）-start transaction开始事务/commit提交/rollback

mysql命令提示符：

mysql  -u root -p

重新设置密码：alter user ‘root’ @ ‘loacalhost’ identified by ‘1257049298’;（强口令密码）

mysql不分大小写

2.创建数据库

• 查看数据库：

  show databases;
  

• 创建名为school的数据库：

create database school default charset utf8mb4;
#utf8mb4：最大允许4字节的utf-8

• 删除名为school的数据库：

  drop database if exists school;（如果存在这个库就删除）
  

• 修改数据库默认的字符集

  alter database school default charset utf8mb4(或者gbk...);
  

• 切换数据库

  use school;
  

• 显示二维表

  show tables;
  

• 创建二维表

   create table tb_student` 
  (
  	stu_id int  '学号',#非空约束
  
  	stu_name varchar(50) not null comment '姓名',#注释
  
  	stu_sex bool comment '性别',
  
  	stu_birth date defalut '2000-01-01' comment '出生日期'，#默认值
  
  	primary key (stu_id)//设置主键
  
  )engine=innodb comment '学生表';
  #用前缀来区分表的类别
  

• 获取数据类型的帮助

  ? data types;
  

• 删除表

  drop table if exists tb_student
  

• 修改表—添加列

  alter table tb_student add column stu_addr varchar(500) default '' comment '家庭住址';
  

• 修改表—删除列

  alter table tb_student drop column stu_tel;
  

• 修改表—修改列

  修改类型：alter table tb_student modify column stu_sex bool default 1 commnet` '性别';
  
  修改列名：alter table tb_student chage column stu_sex stu_gender bool defalut 1 commnet '性别';
  

• 修改表名

  alter table tb_student rename to tb_test;
  

• 加删约束

  alter table tb_teacher add constraint ck_teacher_sex check (tch_sex in ('男','女'));#添加约束
  alter table tb_teacher drop constraint ck_teacher_sex ;#删除约束
  

2.1练习

#老师（工号，姓名，性别，出生日期，职称，学历）
   create table tb_teacher 
    (
        tea_id int not null comment '工号',
        tea_name varchar(20) not null comment '姓名',
        tea_sex char(1) default '男' comment '性别',
        tea_birth date comment '出生日期',
        tea_title varchar(10) not null default '助教' comment '职称',
        primary key (tea_id),
        check (tea_sex in ('男', '女'))
    )engine=innodb comment='老师表';

#学院（编号，名称，网址，联系电话）
create table tb_college
(
	clg_id int not null auto_increment comment '编号',
    clg_name varchar(50) not null comment '名称',
    clg_url varchar(1024) not null default '' comment '网址',
    clg_tel varchar(20) comment '联系电话',
    primary key (clg_id),
    unique(clg_tel)#唯一约束
)engine=innodb comment '学院表';

3.数据类型

3.1获取数据类型的帮助

? data types;
? int;
? varchar;

3.2数值型

• 整数

  int/integer --> 32bit --> 4byte --> -2^31 ~ 2^31-1
  	int unsigned --> 无符号整数 -->只能表示0和正数 --> 0 ~ 2^32-1
  	int(4) zerofill --> 1 -->0001
  	
  bigint --> 64bit --> 2byte
  	bigint unsigned --> 0 ~ 2^64-1
  
  smallint --> 16bit --> 2byte
  tinyint --> 8bit --> -128~127
  

• 小数

  float / double --> 不推荐
  decimal(M,N) --> decimal(10,0) -->推荐使用
  

• 字符串

  定长：char
  变长：varchar(M) --> 65535
  超长内容： clob --> character large object --> longtext --> 不推荐
  	------> 通过列来保存数据的URL链接即可
  

• 日期

  日期：date/year
  时间：time
  日期时间：datetime --> 推荐 
  时间戳：timestamp --> 调整时区 --> 底层是一个int类型 --> overflow风险 --> 不推荐
  

4.外键约束

-- 删除名为school的数据库
drop database if exists school;

-- 创建名为school的数据库并指定默认的字符集
create database school default charset utf8mb4 collate utf8mb4_0900_ai_ci;

-- 切换到school数据库
use school;

-- 创建学生表
create table tb_student 
(
    stu_id integer unsigned not null comment '学号', 
    stu_name varchar(20) not null comment '姓名', 
    stu_sex char(1) default '男' comment '性别', 
    stu_birth date comment '出生日期',
    primary key (stu_id),
    check (stu_sex in ('男', '女'))
) engine=innodb comment='学生表';

-- 创建老师表
create table tb_teacher 
(
    tea_id int not null comment '工号',
    tea_name varchar(20) not null comment '姓名',
    tea_sex char(1) default '男' comment '性别',
    tea_birth date comment '出生日期',
    tea_title varchar(10) not null default '助教' comment '职称',
    primary key (tea_id)
) engine=innodb comment='老师表';

-- 修改老师表添加检查约束
alter table tb_teacher add constraint ck_teacher_sex check (tea_sex in ('男', '女'));

-- 创建学院表
create table tb_college 
(
    col_id int not null auto_increment comment '编号',
    col_name varchar(50) not null comment '名称',
    col_tel varchar(20) not null default '' comment '联系电话',
    primary key (col_id)
) engine=innodb auto_increment=101 comment='学院表';

-- 修改表添加唯一约束
alter table tb_college add constraint uk_college_tel unique (col_tel);

-- 修改学生表添加学院编号列
alter table tb_student add column cid int not null comment '学院编号';

-- 修改学生表添加外键约束
alter table tb_student add constraint fk_student_cid 
foreign key (cid) references tb_college (col_id);#cid对应col_id

-- 修改老师表添加学院编号列
alter table tb_teacher add column cid int not null comment '学院编号';

-- 修改老师表添加外键约束
alter table tb_teacher add constraint fk_teacher_cid 
foreign key (cid) references tb_college (col_id);

-- 创建课程表
create table tb_course
(
    cou_id int unsigned not null comment '编号',
    cou_name varchar(100) not null comment '名称',
    cou_credit int not null comment '学分',
    tid int not null comment '授课老师',
    constraint pk_course_id primary key (cou_id),#主键
    constraint fk_course_tid foreign key (tid) references tb_teacher (tea_id)#外键
) engine=innodb comment='课程表';

-- 通过创建中间表将多对多关系变成两个对多一关系
create table tb_record
(
    rec_id bigint unsigned auto_increment自动增长 not null comment '流水号',
    sid int unsigned not null comment '学号',
    cid int unsigned not null comment '课程号',
    sel_date datetime not null comment '选课日期',
    score decimal(4,1) comment '考试成绩',
    primary key (rec_id),
    foreign key (sid) references tb_student (stu_id),
    foreign key (cid) references tb_course (cou_id),
    unique (sid, cid)
) engine=innodb comment='选课记录表';

5.插入数据

5.1学院数据

insert into table_name(column_1,column_2,...,column_n)
values (value_1,value_2,...,value_n);

#原表
-- 创建学院表
create table tb_college 
(
    col_id int not null auto_increment自动增长 comment '编号',
    col_name varchar(50) not null comment '名称',
    col_tel varchar(20) not null default '' comment '联系电话',
    primary key (col_id)
) engine=innodb auto_increment=101 comment='学院表';

-- 修改表添加唯一约束
alter table tb_college add constraint uk_college_tel unique (col_tel);

#要先进入表里才能操作
use school;

#要注意原表中的各个列是否有默认值，是否不为空。
#空字符串和空值（null）是不一样的。
#空字符串在唯一约束条件下也不能重复
insert into tb_college(col_id,col_name,col_tel)
values (default,'计算机学院','028-44556677');

insert into tb_college(col_name,col_tel)
values ('外国语学院','028-55667788');

insert into tb_college(col_name)
values ('经济管理学院');


#批处理
insert into tb_college(col_name,col_tel)
values
	('计算机学院','028-44556677'),
	('外国语学院','028-55667788'),
	('经济管理学院',default);

5.2学生数据

#原表

-- 创建学生表
create table tb_student 
(
    stu_id integer unsigned not null comment '学号', 
    stu_name varchar(20) not null comment '姓名', 
    stu_sex char(1) default '男' comment '性别', 
    stu_birth date comment '出生日期',
    primary key (stu_id),
    check (stu_sex in ('男', '女'))
) engine=innodb comment='学生表';

-- 修改学生表添加学院编号列
alter table tb_student add column cid int not null comment '学院编号';

-- 修改学生表添加外键约束
alter table tb_student add constraint fk_student_cid 
foreign key (cid) references tb_college (col_id); #cid对应col_id

# 注意检查等约束，不要违反约束条件
insert into tb_student(stu_id,stu_name,stu_brith,stu_sex,cid)
values (1001,'慕珏','1995-5-5','男',101);

#批处理
insert into tb_student (stu_id,stu_name,stu_birth,stu_sex,cid)
values 
	(1002,'南无思','1998-9-120','女',102),
	(1003,'南与画','1998-9-12','女',101);

6.删除数据

delete from table_name where conditions;

#删除一般在主键中删除，主键是唯一的
delete from tb_student where stu_id=1003;

#批量处理
delete from tb_student where stu_id=1001 or stu_id=1002;

delete from tb_student where stu_id in (1001,1002,555,666,888);

#注意不要删除全表，可以开启保护功能，删除全表时报错
#数据在子表中有引用时，删除不了
#比如，某学院下面有学生，就不能删除该学院，但如果没有学生，就可以删除

#默认/级联删除，连着子表学生一起删除/
on delete（删除） restrict/cascade/set null  on updata(更新) restrict;

7.更新

update table_name set column_1=value_1,column_2=value_2... where conditions;

update tb_college 
set col_name='计算机学院', col_tel = '028-12345678'
where col_id=101;