访问远程服务器上的数据库

  • mysql -u用户名 -p密码[-h数据库服务器IP地址 -P端口号]

SQL:一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

通用语法

  • SQL语句可以单行或多行书写，以分号结尾。

    1
    show databases;

  • SOL语句可以使用空格/缩进来增强语句的可读性。

    1
    show  databases;

  • MySQL数据库的SQL语句不区分大小写。

    1
    SHOW DATABASES;

  • 注释

    1. 单行注释:–注释内容 或#注释内容(MySQL特有)

      1
      -- show databases;

    2. 多行注释: /* 注释内容 */

      1
      /* show databases; */

DDL

数据定义语言，用来定义数据库对象(数据库，表，字段)

1
2
3
4
5
6
//创建数据库
creat database '数据库名'
//展示数据库
show database
//删除数据库
drop database '数据库名'

  1. 查询

    • 查询当前数据库所有表: show tables;
    • 查询表结构: desc 表名;
    • 查询建表语句: show create table 表名

  2. 修改

    • 添加字段: alter table 表名 add 字段名 类型(长度)[comment 注释] [约束];
    • 修改字段类型: alter table 表名 modify 字段名 新数据类型(长度);
    • 修改字段名和字段类型: alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束
    • 删除字段: alter table 表名 drop column 字段名;
    • 修改表名: rename table 表名 to 新表名

  3. 删除

    • 删除表: drop table [if exists]表名

DML

数据操作语言，用来对数据库表中的数据进行增删改

  1. 插入数据

    • 指定字段添加数据: insert into 表名(字段名1,字段名2) values (值1,值2);
    • 全部字段添加数据: insert into 表名 values (值1,值2,…);
    • 批量添加数据(指定字段): insert into 表名(字段名1,字段名2) values (值1,值2),(值1,值2);
    • 批量添加数据(全部字段) : insert into 表名 values (值1,值2,…),(值1, 值2,…);

  2. 更新数据

    • 修改数据: update 表名 set 字段名1= 值1,字段名2 = 值2,…[ where 条件];

  3. 删除数据

    • 删除数据: delete from 表名 [where 条件];

DQL

数据查询语言，用来查询数据库中表的记录

1

  1. 基本查询：

    • 查询多个字段: select 字段1,字段2, 字段3 from 表名
    • 查询所有字段(通配符): select * from 表名;
    • 设置别名: select 字段1[as 别名1],字段2[as 别名2] from 表名 as关键词可省略
    • 去除重复记录: select distinct 字段列表 from 表名;

  2. 条件查询:

    • select 字段列表 from 表名 where 条件列表;
    • Where条件运算符
      2

  3. 聚合函数：
    3

    • null值不参与所有聚合函数运算。
    • 统计数量可以使用:count( *) count(字段) count(常量)，推荐使用count( * )。
    • //流程控制函数：if(条件表达式，true取值，false取值)，
    • //case 表达式 when 值1 then 结果1 when 值2 then 结果2 …else …end

  4. 分组查询:

    • (where之后不能使用聚合函数)
    • select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];
    • 执行顺序：where>聚合函数>having，where不能对聚合函数进行判断，having可以

  5. 排序查询：

    • select 字段列表 from 表名 [where 条件列表] [group by 分组字段]order by 字段1 排序方式1,字段2 排序方式2…
    • ASC: 升序(默认值) DESC:降序
    • 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

  6. 分页查询

    • select 字段列表 from 表名 limit 起始索引,查询记录数
    • 起始索引 = (页码 - 1) * 每页展示记录数

  7. 注意：

    • 起始索引从0开始，起始索引= (查询页码-)*每页显示记录数
    • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10.

多表设计

对表进行外键约束，保证数据的完整性，一致性，有效性。
4

5

实体之间的关系：

  1. 一对一：人和身份证
  2. 一对多：部门和员工
  3. 多对多：学生选课，一个学生可以选多门课程，一门课程也可以被多个学生选择

多表查询

多表查询:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

  1. 连接查询

    • 内连接查询:查询A和B交集部分数据
      • 隐式内连接:使用where条件消除无用数据
      • 显示内连接:使用inner join …on 消除无用数据
    • 外连接查询:查询A和B所有数据，如果数据不存在，则为null
      • 左外连接:查询A表所有数据，以及A和B交集部分数据
      • 右外连接:查询B表所有数据，以及A和B交集部分数据
    • 自连接查询:当前表与自身连接查询，自连接必须使用表别名

  2. 子查询

    • 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
    • 子查询一般作为其他语句的条件使用，子查询可以放在where后面，也可以放在having后面，还可以放在select后面。子查询语句可以放在小括号中。
    • 根据子查询出现的位置分类
      • select后面：标量子查询
      • from后面：表子查询
      • where后面：标量子查询、列子查询、行子查询
      • having后面：标量子查询、列子查询、行子查询
    • 根据子查询的结果分类
      • 标量子查询(结果集只有一行一列,操作符: > < >= <= = <>)
      • 列子查询(结果集只有一列多行，操作符: in、not in、any/some、all)
      • 行子查询(结果集有一行多列，操作符: =、<>)
      • 表子查询(结果集一般为多行多列，操作符: in)

事务

概念：一组操作的集合，这组操作要么全部成功，要么全部失败

开启事务: start transaction; / begin ;
提交事务: commit;
回滚事务: rollback;

四大特性：

  1. 原子性：事务是一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成。
  2. 一致性：事务开始和完成时，数据必须保持一致状态。
  3. 隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  4. 持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

索引

索引 (index)是帮助数据 高效获取数据的 数据结构（二叉树）

优点：

  • 提高数据查询的效率，降低数据库的IO成本。
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

  • 索引会占用存储空间。
  • 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率

6

结构：B+Tree
特点：

  • 每一个节点，可以存储多个key (有n个key，就有n个指针)。
  • 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。
  • 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。

语法：

创建索引：create [unique] index 索引名 on 表名(字段名,…)；
查看索引：show index from 表名；
删除索引：drop index 索引名 on 表名；

注意：
主键字段，在建表时，会自动创建主键索引。（性能最高）
添加唯一约束时，数据库实际上会添加唯一索引。