Python 测开28期 - 小锅巴 - 数据库与SQL- 学习笔记

学习笔记
,
1

数据库与SQL

DDL数据库操作

  • 创建数据库
    • IF NOT EXISTS:可选，在创建之前先判断是否存在，不存在即创建
    • CHARACTER SET = 字符集：可选项，指定数据库的字符集
    • 语法：create database [IF NOT EXISTS] 库名 [CHARACTER SET = 字符集]
      • 示例：create database test；create database test_1 CHARACTER SET utf8；
  • 查看数据库
    • 查看所有数据库：SHOW DATABASES;
    • 使用数据库：USE 数据库名；
    • 查看数据库创建的语法：SHOW CREATE DATABASE 数据库名；
  • 修改数据库
    • 必写关键字：DATABASE
    • 可选字段：数据库名，不写的话默认修改的是当前所选的数据库
    • 可选：CHARACTER SET = 字符集：可选项，指定数据库的字符集
    • 语法：ALTER [DATABASE] [数据库名] [CHARACTER SET utf8]；
    • 示例：ALTER DATABASE test CHARACTER SET utf8；
  • 删除数据库
    • 必选关键字：DATABASE
    • 可选关键：IF EXISTS 删除先先判断库是否存在，可避免删除不存在的库导致异常发生
    • 语法：DROP DATABASE [IF EXISTS] 数据库名；
    • 示例：DROP DATABASE IF EXISTS test;

DDL数据库表操作

数据类型
  • 数字类型：
    • TINTINT最小整数
    • SMALLINT-小型整数
    • MEDIUMINT-中型整数
    • INT-标准整数
    • BIGINT-大整数
    • FLOAT-单精度浮点数
    • DOUBLE-双精度浮点数
    • BOOLEAN-布尔
  • 字符串类型：
    • CHAR-固定长度字符串(1-255字符)
    • VARCHAR-可变字符串(最长不超过255个字符)
    • TEXT-最大长度64K文本
    • TINYTEXT-与TEXT相同，最大长度255个字节
    • MEDIUMTEXT-与TEXT相同，最大长度不超过16K
    • LONGTEXT-与TEXT相同，最大长度不超过4GB
  • 日期和时间类型：
    • DATE-日期，格式YYYY-MM-DD
    • TIME-时间，格式HH:MM:SS
    • DATETIME-日期和时间，格式YYYY-MM-DD HH:MM:SS
    • TIMESTAMP-时间标签，与DATATIME相同但是范围小
    • YEAR-年份，可指定2位数或者4位数
数据库表
  • 创建数据库表
    • 列是否允许为空值：NOT NULL | NULL
    • 默认值：DEFAULT
    • 是否为自动递增编号：AUTO_INCREMENT
    • 是否为主键：PRIMARY KEY
    • 语法：CREATE TABLE 表名 (列1，列2…)
    • 示例：CREATE TABLE test (id AUTO_INCREMENT, name VARCHAR(20) PRIMARY KEY , age INT )
  • 修改数据库表
    • 表添加字段列属性：ALTER TABLE 表名 ADD 列名 属性
      • ALTER TABLE student ADD email varchar(50) NOT NULL;
    • 修改列字段的定义类型：ALTER TABLE 表名 MODIFY 列名 属性
    • 修改列字段名：ALTER TABLE 表名 CHANGE 旧列名 新列名 属性
    • 删除列：ALTER TABLE 表名 DROP 列名
    • 修改表名：
      • 方式1：ALTER TABLE 旧表名 RENAME AS 新表名；
      • 方式2：RENAME TABLE 旧表名 TO 新表名；
  • 删除数据库表
    • IF EXISTS:可选字段，删除表之前先判断是否存在
    • 语法：ALTER TABLE [IF EXISTS] 表名；
  • 查看数据库表
    • 查看有哪些表语法：SHOW DATABASES;
    • 查看表结构：DESCRIBE 表名 列名；

数据库表-表数据操作-DML

  • 插入数据：INSERT INTO 表名 (列1，列2…) VALUES (var1，var2…)
  • 修改数据：UPDATE FROM 表名 SET 列名=值 … WHERE 要修改的列名 = 值；
  • 删除数据：DELETE FROM 表名 WHERE 条件表达式；
    • 删除表中全部数据：TRUNCATE TABLE 表名；

数据库表-表数据查询-DQL

  • 单表查询
    • SELECT * FROM 表名；
    • SELECT 列名 AS 别名 FROM 表名；
    • 去重：SELECT DISTINCT 列名 FROM 表名；
  • 单表条件查询
    • SELECT * FROM 表名 WHERE 条件表达式；
    • 条件
      • 比较运算符：>、<、>=、<=、!=、=、BETWEEN…AND、IN、LIKE、IS NULL
      • 逻辑运算符：AND、OR、NOT
      • 通配符：%-匹配对个、_:匹配一个
  • 其他关键字：
    • 排序：ORDER BY
    • 聚合函数：COUNT()、MAX()、MIN()、SUM()、AVG()
    • 分组：GROUP BY HAVING 条件表达式
    • 截取多少数据：LIMIT 数值

SQL约束

  • 主键约束：设置字段属性为 PRIMARY KEY，就变成了主键
  • 非空约束：设置字段属性为 NOT NULL ，此字段不能为空
  • 唯一约束：设置字段属性为 UNIQUE，此字段就变成了唯一，但是对NULL不做唯一判断
  • 默认值：设置字段属性为DEFAULT 值, 这个字段就会默认写入 值 的数据
  • 外键约束：从表中与主表中对应的字段。建立主表与从表关联关系，为两个表建立数据联系。
    • CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES 主表名称(主键字段)
    • CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
);
  • 删除外键约束：ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名称；
    • ALTER TABLE emp_part DROP FOREIGN KEY emp_dept;

多表操作

  • 笛卡尔积查询

    • 语法：select * from 表1，表2

  • 内连接查询：表中的数据匹配上显示，匹配不上不显示

    • 关键字：inner join
    • 隐式内连接：语法：select [字段名称] from 表1，表2 where [条件]
    • 显示内连接：语法：select [字段名称] from [表1] inner join [表2] on [2表相匹配的条件] 这种方式

  • 外链接查询：

    • 左外链接：语法：SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]
      含义：以左表为基准匹配右表的数据，右表中没有的项，显示为空
    • 右外连接：语法：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]
      含义：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]

  • 子表查询：子查询作为过滤条件的时候需要用()包裹

    • 带 FROM关键字的子查询：将子查询的结果当做父查询的表来使用
      • 示例：select dept_name,count(emp_id) from (select dept_name, emp_id,ename,gender from dept inner join emp_part where id=dept_id and gender='男')b group by dept_name;
    • 带 IN/NOT IN 关键字的子查询：子查询的结果是单列多行，作为where的过滤条件
      • 示例：SELECT * FROM emp_part WHERE dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')
    • 带比较运算符的子查询：查询结果作为过滤条件出现在比较运算符的一端
      • 示例：SELECT emp_id,ename,salary FROM emp_part WHERE salary > (SELECT AVG(salary) FROM emp_part);
    • with…as :如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示出来
      • 示例： with dept_avg as (select dept_id,avg(salary) avg_salary from emp_part group by dept_id)select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg on id=dept_id and avg_salary > (select avg(avg_salary) from dept_avg);