2019-04-23-day038-数据库的语句

时间：2021-10-02 19:27:01

昨日回顾

补充的知识点

  • server端肯定是确定下来的

mysql的客户端

  • mysql.exe 直接在命令行就可以运行的 (学习阶段用)
  • navicat等可视化的客户端,是第三方开发的客户端 (开发辅助)
  • python代码的客户端 (主要用)

内容回顾

  • 数据库管理系统 DBMS
    • 数据库 DB
    • 表 table
    • 数据 data
  • 数据库管理员 DBA

数据库的类型:

  • 关系型数据库 : mysql\oracle\sql server
  • 非关系型数据库 : redis

SQL语句

  • DDL : 定义 表\库
    • 创建库
    • 定义表
    • 修改表结构
    • 删除表\库
  • DML : 数据的增删改查 - 重中之重
    • insert 增 次多
    • delete 删除 绝对少
    • update 修改 相对少
    • select 查 最多
  • DCL : 和用户\权限相关的
    • select user(); 查看当前的用户
    • 创建用户
    • create user '用户名'@'ip地址' identified by '密码'
    • 给用户授权(包括了创建用户)
    • grant 权限 on 库.表名 to '用户名'@'ip地址' identified by '密码'
      ##今日内容
  • mysql的存储引擎(innodb,myisam)
  • mysql支持的数据类型
  • 约束
  • 表的创建\删除\修改\查看表结构
  • 表与表之间的关系

存储引擎

第一种方式: Myisam 是5.5之前默认的存储引擎

  • 数据存在硬盘上,存三个文件,表结构,数据,和搜索目录
  • 既不支持事务、也不支持外键、不支持行级锁
  • 只支持表锁
  • 对于只读操作比较多的情况 查询速度相对快
    ###第二种方式: Innodb 是5.6之后的默认存储引擎
  • 数据存在硬盘上,存两个文件,表结构,(数据和搜索目录)
  • 支持事务
  • 支持行级锁
  • 支持外键
    ###第三种方式: Memory
  • 数据存在内存中,存一个文件,表结构(在硬盘上)
  • 数据容易丢失,但读写速度都快
    ###几个需要讲解的关键词
  • 事务 transaction
    • 在修改数据时保证了数据的安全性
  • 行级锁和表级锁
  • 外建约束

创建表结构

create table staff_info(
   id int,
   name char(12),
   age tinyint unsigned,
   sex char(6),
   phone char(11),
   job char(20)
);

mysql中的基础数据类型

  • 数值类型
    • int
    • float
  • 字符串类型
    • char
    • varchar
  • 时间类型
    • datetime
  • set和enum类型
    • enum
    • set

i系列

create table i1(id1 int,id2 tinyint,id3 int unsigned);
create table i2(id1 int(2),id2 int(11));  #对int类型的长度进行的约束无效

浮点数系列 f系列

create table f1(f float(5,2),d double(5,2),d2 decimal(5,2));
create table f2(f float,d double,d2 decimal);
create table f3(d double,d2 decimal(65,30));
  • float精确到小数点后5位
  • double能多精确一些位数,但仍然存在不精确的情况
  • decimal默认是整数,但是通过设置,最多可以表示到小数点后30位
    时间
  • year
  • date now(),20191010 '2019-01-01'
  • time now(),121212 '12:12:12'
  • datetime now(),20191010121212,'2019-01-01 12:12:12'
  • timestamp
  • create table time1(y year,d date,t time);
  • create table time2(dt datetime,ts timestamp);
    • datetime 能表示的时间范围大 可以为空,没有默认值
    • timestamp 能表示的时间范围小 不能为空,默认值是当前时间
  • create table time2(dt datetime default current_timestamp,ts timestamp);
    • 人为设置datetime类型的默认值是当前时间
      字符串
  • char 能表示的长度小,浪费存储空间,读写效率快
  • 定长字符串
    • char(5) 'abc' 'abc ' 'abcde'
    • 在显示的时候会去掉所有空格显示,对用户的视觉造成欺骗
  • varchar 能表示的长度大,节省存储空间,读写效率慢
  • 变长字符串
    • varchar(5) 'ab'-->'ab2' 'abc'-->'abc3' 'abcde'-->'abcde5'
  • 身份证号 : char(18)
  • 手机号码 : char(11)
  • 用户名 : char(12) 频繁的读取的列 并且长度的变化不大
  • 评论 : varchar(255)
  • create table s1(c char(10),v varchar(10));
    ###enum和set
  • 枚举,单选,且自动剔除不存在的选项
    • enum('male','female')
  • 集合,多选,自动剔除不存在的选项,自动去重
    • set('洗脚','洗头','抽烟','喝酒','烫头')
  • create table es(name char(10),sex enum('male','female'),hobby set('洗脚','洗头','抽烟','喝酒','烫头'));
  • insert into es values('太白','male','烫头,抽烟,洗脚,按摩');
  • insert into es values('alex','人妖','烫头');
  • insert into es values('宝元','male','抽烟,喝酒,喝酒,喝酒')
    ###查看表结构
  • desc 表名; 看的更简洁
    • == describe 表名;
  • show create table 表名; 看的更详细
    ###删除表
  • drop table 表名

完整性约束

  1. 设置整形无符号 int unsigned
  2. 设置默认值 default
  3. 是否可以为空 not null
  4. 是否唯一 unique
  5. 自增 auto_increment
  6. 主键 primary key
  7. 外建 foreign key

not null

表结构 : id,name,phone,sex

create table stu1(
id int,
name char(12) not null,
phone char(11),
sex enum('male','female')
);

not null + default
create table stu2(
id int,
name char(12) not null,
phone char(11),
sex enum('male','female') not null default 'male'
)

唯一 unique

unique只是约束在char数据类型内不能重复,但是不能约束null

id name ident
create table stu3(
    id int,
    name char(12),
    ident char(18) unique
)

联合唯一 unique

一台机器上跑着多少个服务

把每一个正在运行的应用程序的信息都统计下来

ip + port
192.168.16.13  mysql 3306
192.168.16.13  kugou 8080
192.168.16.13  flask 5000
192.168.16.15  mysql 3306
192.168.16.16  mysql 3306

create table service(
  id int,
  ip char(15),
  name char(15),
  port int(5),
  unique(ip,port)
)

auto_increment 自增的条件(这一列必须是数字,这一列必须是uniuqe)

userinfo
1,alex,'alex3714'
create table userinfo(
  id int unique auto_increment,
  name char(12),
  password char(32)
)

user = input()
pwd  = input()
sql = "insert into userinfo (name,password) values('%s','%s')"%(user,pwd)

not null 非空 + unique 唯一  == primary key
登录时候的用户名 一定是唯一的
create table userinfo3(
  id int unique,
  username char(18) not null unique,
  password char(32),
  ident char(18) not null unique
)

create table pri1(
  id1 int unique not null,
  id3 int unique not null
)

一张表中只能有一个主键 : 主键从约束的角度上来说 就是非空且唯一的

只不过,非空+唯一可以设置多个字段,但是主键只能给一个表中的一个字段设置

auto_increment = not null
create table userinfo2(
  id int unique auto_increment,
  username char(18) not null unique,
  password char(32),
  ident char(18) not null unique
)

主键 primary key :在约束中就是非空 + 唯一

  • 一般情况下,我们给id字段设置为主键,不允许一张表不设置主键
create table pri2(
  id1 int primary key,
  id3 int unique not null
)

create table pri3(
  id1 int primary key,
  id3 int primary key
)   * 报错  一张表只能有一个主键

联合主键 : 约束多个字段各自不能为空,并且联合唯一

create table pri4(
    id1 int,
    num int,
    primary key(id1,num)
);

外键

创建两张表

  • 表2 班级表 cid class_name
create table clas(
    cid int primary key,
    class_name char(20)
)
表1 学生表 id name class_id
create table stu(
    id int primary key ,
    name char(18),
    class_id int,
    foreign Key(class_id) references clas(cid)
)

有外键之后所有的新增和删除都会受到外表的约束；比如

  • 如果新增了一个学生所在的班级不存在,那么不能写入学生
  • 如果删除一个还有学生指向的班级,也不能删除,也不能修改外键指向的键

级联更新 级联删除

create table stu4(
    id int primary key ,
    name char(18),
    class_id int,
    foreign Key(class_id) references clas(cid)
    on update cascade on delete cascade
)

表与表之间的结构

  1. 一对多 foreign key
    • 一个班级有多个学生
    • 外键
      • 学生的班级id是外键,关联班级表中的id字段
  2. 多对多 第三张表 + foreign key1 + foreign key2
    • 一个学生可以选多门课程
    • 一门课程可以被多个学生选择
  3. 一对一 foreign key + unique
    • 客户和学生
    • 客户表 什么途径 什么时候联系的你 招生老师
    • 学生表 学号 课程id 入学时间 班级id 客户id

相关文章