数据库1-增删改查

用户876219106245
96 阅读6分钟

数据库

1.1 数据库简介

  1. 数据库能干什么

    • 持久的存储数据
    • 备份和恢复数据
    • 快速的存取数据
    • 权限控制

  2. 数据库的类型

    • 关系数据库

      • 特点：以表和表的关联构成的数据结构
      • 优点：能表达复杂的数据关系；强大的查询语言，能精确查找想要的数据
      • 缺点：读写性能比较差，尤其是海量数据的读写；数据结构比较死板
      • 用途：存储结构复杂的数据
      • 代表：Oracle、MySql、Sql Server

    • 非关系型数据库

      • 特点：以极其简单的结构存储数据
      • 优点：格式灵活；海量数据读写效率很高
      • 缺点：难以表示复杂的数据结构；对于复杂查询效率不好
      • 用途：存储结构简单的数据
      • 代表：MongoDB、Redis、Membase

    • 面向对象数据库

  3. 相关术语

    • DB : database 数据库
    • DBA : database administrator数据库管理员
    • DBMS : database management system数据库管理系统
    • DBS : database system 数据库系统 DBS包含DB、DBA、DBMS

1.2 数据库设计

管理库

创建库

CREATE DATABASE `abc`;

切换当前库

use sys;

删除库

drop DATABASE test;

管理表

创建表

字段 （一列）

  • 字段名

  • 字段类型

    • bit ：占1位, 0或1 ，false或true
    • int：占32位，整数
    • decimal(M, N) ：能精确计算的实数，M是总的数 字位数, N是小数位数
    • char(n) ：固定长度位n的字符
    • varchar(n)：长度可变,最大长度位n的字符
    • text ：大量的字符
    • date ：仅日期
    • datetime ：日期和时间
    • time ：仅时间

  • 自增

image.png

  • 默认值

主键和外键

主键：

  • 根据设计原则,每张表都要有主键
  • 唯一；不能更改；无业务含义

外键：

  • 用于产生表关系的列
  • 外键列会连接到另一张表(或自己)的主键

表关系

  • 一对一

    • 一个A对应一个B，一个B对应一个A

    • 例如：用户和用户信息

    • 把任意一张表的主键同时设置为外键

image.png

  • 一对多

    • 一个A对应多个B，一个B对应一个A，A和B是一 对多，B和A是多对一
    • 例如：班级和学生，用户和文章
    • 在多一端的表上设置外键，对应到另一张表的主键

  • 多对多

    • 一个A对应多个B ,一个B对应多个A
    • 例如：学生和老师
    • 需要新建一张关系表，关系表至少包含两个外键，分别对应到两张表

  • 三大设计范式

    • 要求数据库表的每一列都是不可分割的原子数据项
    • 非主键列必须依赖于主键列
    • 非主键列必须直接依赖主键列 （stu表→班级id→班级名称）

1.3 表记录的增删改

CRUD（增：CREATE；删：DELETE；改：UPDATE；查：RETRIEVE）

导出：右键→转储SQL文件→结构和数据

导入：创建→运行SQL文件→导入文件

增

    -- 增加语句

    -- 增加一条语句

    -- INSERT INTO `test`.`stu`(stuno,`name`,birthday,sex,phone,classid)
    -- VALUES('500','lanlan','2000-1-1',TRUE,'1234548755',2);

    -- 性别默认值

    INSERT INTO `test`.`stu`(stuno,`name`,birthday,sex,phone,classid)
    VALUES('500','lanlan','2000-1-1',DEFAULT,'1234548755',2);

    -- 增加多条语句
    -- 可以右键运行已选择的

    INSERT INTO `test`.`stu`(stuno,`name`,birthday,sex,phone,classid)
    VALUES('500','lanlan','2000-1-1',DEFAULT,'1234548755',2),
    ('501','小明','2001-5-1',DEFAULT,'1234545756',2);

删

    -- 删除语句 删行

    DELETE FROM stu
    WHERE `name`='bnk';

改

    -- 修改语句

    UPDATE stu SET `name`='蓝精灵' 
    WHERE id=6;

1.4 单表基本查询

查

运行顺序：

  • from
  • where
  • select
  • order by

select..from…（别名、*、case、distinct去重）

    -- 查找语句 
    SELECT id,loginid,'abc' FROM `user`;

    -- 定别名
    SELECT ismale as 性别 FROM `employee`;

    -- 查找所有
    SELECT * FROM `employee`;


    -- 查找id，姓名，性别，工资，并把性别1改为男，0改为女；工资高于10000的改为高，高于5000为中
    -- select...from...
    SELECT id,`name`,
    CASE ismale
    WHEN 1 THEN '男'
    ELSE '女'
    end sex,
    CASE
    WHEN salary>=10000 THEN '高'
    WHEN salary>=5000 THEN '中'
    ELSE '低'
    end `level`,
    salary
    FROM employee;

    -- distinct 一般查一列
    SELECT DISTINCT location FROM employee;

image.png

where（=、is、is not、><≥≤、between、like、and、or）

-- =
SELECT *
FROM employee
WHERE ismale=1;

-- IN
SELECT *
FROM department
WHERE companyId in (1,2);

-- IS NOT
SELECT *
FROM employee
WHERE location IS NOT NULL;

-- BETWEEN
SELECT *
FROM employee
WHERE salary BETWEEN 10000 and 12000;

-- LIKE
-- 姓袁：袁%
-- 袁在最后：%袁
-- 袁x：袁_
SELECT *
FROM employee
WHERE `name` LIKE '%袁%';

-- AND OR
SELECT *
FROM employee
WHERE `name` LIKE '张%' AND ismale=0 AND salary>=12000
OR
birthday>='1996-1-1';

order by

    -- ASC升序 DESC降序
    SELECT *
    FROM employee
    ORDER BY salary ASC,salary DESC;

limit

    -- 跳过2条选3条
    SELECT *
    FROM employee
    limit 2,3
  • 练习
        -- 查询user表，得到账号为admin,密码为123456的用户
        -- 登录
        SELECT * FROM `user`
        WHERE loginId='admin' and loginPwd = '123123'

        -- 查询员工表，按照员工的入职时间降序排序，并且使用分页查询
        -- 查询第2页，每页10条数据
        -- limit (page-1)*pagesize 当前页面-1 乘 页总数
        SELECT * FROM employee
        ORDER BY employee.joinDate DESC
        LIMIT 10,10

        -- 查询工资最高的女员工
        SELECT * FROM employee
        WHERE ismale=0
        ORDER BY salary DESC
        LIMIT 0,1;

1.5 联表查询

  • 全连接,笛卡尔积（两张表数量相乘）

  • 左连接,左外连接, left join（以左表为基准，左表中的数据至少出现一次）

        SELECT *
        from department as d left join employee as e
        on d.id = e.deptId;

image.png

  • 右连接,右外连接, right join（以右表为基准，右表中的数据至少出现一次）
        SELECT *
        from employee as e right join department as d
        on d.id = e.deptId;

image.png

  • 内连接, inner join（必须满足）
        SELECT e.`name` as empname, d.`name` as dptname, c.`name` as companyname
        from employee as e
        inner join department as d on d.id = e.deptId
        inner join company c on d.companyId = c.id;

image.png

  • 练习
        -- 1.创建一张team表，记录足球队
        -- 查询出对阵表
        SELECT t1.name 主场, t2.name 客场
        FROM team as t1,
        team as t2
        WHERE t1.id != t2.id;

        -- 2.显示出所有员工的姓名、售剧(使用男或女显示)、入职时间、薪水、所属部门(显示部门名称)、所属公司(显示公司名称)
        SELECT e. `name` 员工姓名,
        case ismale when 1 then '男' else '女' end 性别,
        e.joinDate 入职时间,
        e.salary 薪水,
        d.`name` 部门名称
        FROM employee e
        inner join department d on e.deptId = d.id
        inner join company c on d.companyId = c.id

        -- 3.查询腾讯和蚂蚁金服的所有员工姓名、性别、入职时间、部门名、公司名
        WHERE c.`name` in ('腾讯科技'，'蚂蚁金服')
        -- 4.查询渡一教学部的所有员工姓名、性别、入职时间、部门名、公司名
        WHERE C.`name` like ' %渡一%' AND d.`name` ='教学部';

image.png

avatar
文章
阅读
粉丝