MySQL数据库操作

MySQL数据库操作

事务：一组操作当做一个操作，要么全部成功，要么全部失败。

• 关系型数据库：MySQL、Oracle、SQLite、SQL Server
• 非关系型数据库：Redis、MongoDB、HBash、Neo4J

id	name	age	gender	hobby
1	张三	18	男	女
2	李四	20	女	男

SQL的分类

• DDL：Data Definition Language【数据定义语言】不能直接操作数据，但是可以决定数据的存储的方式
• DML：Data Manipulation Language【数据操作语言】insert插入数据、delete删除数据、update修改数据
• DQL：Data Query Language【数据查询语言】select
• DCL：Data Control Language【数据控制语言】create user、grant on to
• DTL：Data Transaction Language【数据事务语言】start transaction；commit；rollback

数据类型

int：数值型，4个字节
tinyint：数值型，1个字节
bigint：数值型，8个字节
float、double：浮点型，4字节和8字节
char（定长字符串）、varchar（不定长字符串）、text（文本类型）
date、datetime、time、sysdate()、now()、year()、month()、

mysql命令：连接数据库服务器

• -u，–user：指定用户名
• -p，–password：指定密码
• -h，–host：指定主机
• -P，–port：指定端口
• -V：查看版本

数据库中的命令分为两种

• 客户端命令：不需要以分号结尾，并且有别名
• 服务器命令：必须加分号结尾

库操作

创建库

create database 库名;

-- 创建数据库并指定编码
create database 库名 default character set = 字符编码;
-- 创建数据库并指定编码
create database 库名 charset 字符编码;

切换数据库

use 库名

查看数据库

show databases;

删除数据库

drop database 库名;

查看创建过程

show create database 库名;

表操作

查看表

show tables;

创建

create table 表名([字段名 数据类型, ...]
);

查看指定表

desc 表名;
show create table 表名;

修改表

-- 删除表中的字段
alter table 表名 drop 字段名;

-- 给表中添加一个字段
alter table 表名 add 字段名 数据类型;

-- 给表中在指定字段后添加一个字段
alter table 表名 add 字段名 数据类型 after 字段名;

-- 给表中添加一个字段（之前）
alter table 表名 add 字段名 数据类型 before 字段名;

-- 添加两个字段
alter table 表名 add 字段名 数据类型, add 字段名 数据类型;

-- 修改指定字段的类型
alter table 表名 modify 字段名 数据类型;

-- 修改指定字段的类型和字段名
alter table 表名 change 旧字段名 新字段名 数据类型;

删表

drop table 表名;

DML操作

插入数据

insert into 表名(字段名...) values (值...);

修改数据

update 表名 set 字段名 = 值 where 字段 = 值;

删除数据

delete from 表名 where 字段 = 值;

查看

-- 查询全部
select * from 表名;

-- 查询指定字段名
select 字段名... from 表名;

-- 使用别名
select 字段名 别名 from 表名;
select 字段名 as 别名 from 表名;

-- 条件查询
select 字段名... from 表名 where 字段 = 值;

-- 多条件查询
select 字段名... from 表名 where 字段 = 值 and 字段 = 值;
select 字段名... from 表名 where 字段 = 值 or 字段 = 值;

-- 比较运算
select 字段名... from 表名 where 字段 > 值;
select 字段名... from 表名 where 字段 < 值;
select 字段名... from 表名 where 字段 >= 值;
select 字段名... from 表名 where 字段 <= 值;
select 字段名... from 表名 where 字段 <> 值;
select 字段名... from 表名 where 字段 != 值;

数据约束

default：默认值

create table 表名(字段名 数据类型 default 默认值...);

not null：非空约束

create table 表名(字段名 数据类型 not null...);

unique：唯一约束，不能有重复的数据

create table 表名(字段名 数据类型 unique...);

primary key：主键约束、唯一且非空

auto_increment：自增长

foreign key：外键

on delete cascade：级联删除

on update cascade：级联修改

数据查询

• 通配符：*

• 数据运算

• 别名（as）

• 排序（order by asc、order by desc）

• 条件查询

  • 等值判断（=）

  • 逻辑判断（and、or、not）

  • 不等值判断（>、>=、<、<=、!=、<>）

  • 区间判断（between 值1 and 值2）

  • null值判断（is null、is not null）

  • 枚举（in(值1，值2，值3…)）（子查询中也能用到，当查询的结果是多行单列的时候）

  • 分支

    case 
    	when 条件1 then 结果1
    	when 条件2 then 结果2
    	when 条件3 then 结果3
    	when 条件4 then 结果4
    	when 条件5 then 结果5
    	else 结果6
    end
    

    mysql> select id , name ,age , case when age >10 then '帅哥' when age >20 then '老了' else '年轻' end from student;
    +----+-----------+------+------------------------------------------------------------------------------+
    | id | name      | age  | case when age >10 then '帅哥' when age >20 then '老了' else '年轻' end       |
    +----+-----------+------+------------------------------------------------------------------------------+
    |  1 | yyqx      |   22 | 帅哥                                                                         |
    |  2 | 哈哈      |   20 | 帅哥                                                                         |
    |  3 | 哈        |   20 | 帅哥                                                                         |
    |  4 | 哈哈哈    | NULL | 年轻                                                                         |
    |  5 | 呵呵      | NULL | 年轻                                                                         |
    |  6 | 哦呵呵    | NULL | 年轻                                                                         |
    |  7 | 哦哈哈    | NULL | 年轻                                                                         |
    |  8 | 哈哈哦    | NULL | 年轻                                                                         |
    |  9 | 呵呵哦    | NULL | 年轻                                                                         |
    | 10 | 哈呵呵    | NULL | 年轻                                                                         |
    +----+-----------+------+------------------------------------------------------------------------------+
    10 rows in set (0.00 sec)
    

  • 分组查询（group by 被分组的字段）

  • 分组过滤（having）要求只能跟在分组之后的条件

  • 限定查询（limit 起始行，行数）分页

  • 模糊查询（like）%表示任意数量的任意字符、_表示任意单个字符

  • 合并查询（union、union all）union会去重、union all不会去重

  • 联表查询

    • 交叉查询：select from 表名1,表名2
    • 内连接：inner join 表名 on 连接的条件
    • 左外连接：left join on，以左表为主表，右表中的内容可能为空
    • 右外连接：right join on，以右表为主表，左表中的内容可能为空
    • 全连接：full join on【MySQL不支持】

  • 子查询：

    • 子查询的结果是单行单列，用于等值判断后

      -- 查询年龄最大的员工的薪资
      select salary from employee where age = (select max(age) from employee);
      

    
    
    - 子查询的结果是多行单列
    
     ```sql
     -- 查询和任一男性工资相同的女性的信息
     select * from employee where gender = '女' and salary in (select salary from employee where gender = '男'); 
    

    • 子查询的结果是多行多列（派生表，伪表）

      -- 查询薪资排名前5的学生中最低薪资的学生