day35 数据操作补充和单表操作

爱被打了一巴掌 2023-06-11 13:22 109阅读 0赞

# day35 数据操作补充和单表操作 #

### 文章目录 ###

*  day35 数据操作补充和单表操作
 *   *  今日内容概要
     *  昨日内容回顾
     *  今日内容详细
     *   *  数据操作补充
         *   *  增 insert
             *  删 delete（谨慎操作，不可逆）
             *  改 update
         *  单表查询
         *   *  \`select...where...\`
             *  \`group by...having...\`
             *  \`order by...limit...\`

## 今日内容概要 ##

1.  数据操作补充
    
    1.  增
    2.  删
    3.  改
2.  单表查询

## 昨日内容回顾 ##

1.  基础数据类型：
    
    1.  数字类型：tinyint，int，float(7,2)，decimal
    2.  时间类型：datetime，date，time
    3.  字符串类型：char定长浪费空间存取快 varchar变长节省空间存取慢
    4.  枚举和集合：enum()单选 set()多选和去重
2.  约束
    
    1.  unsigned
    2.  not null
    3.  default
    4.  unique/联合
    5.  primary key/联合
    6.  auto\_increment（依赖unique条件）
    7.  foreign key（依赖关联字段的unique条件）
        
         *  on update cascade
         *  on delete cascade
3.  表与表之间的关系
    
    1.  一对多/多对一：foreign key
    2.  一对一：foreign key + unique
    3.  多对多：创建第三张表
4.  创建表
    
        create table 表名(
        字段名 类型(长度) 约束 约束2)
5.  删除表
    
        drop table 表名;
6.  修改表
    
        alter table 表名 add/drop/modify/change/rename ... xxx;
7.  查看表结构
    
        desc 表名;
        describe 表名;
        show create table 表名;

## 今日内容详细 ##

### 数据操作补充 ###

#### 增 insert ####

insert into 表名(字段们) value (一行数据);
    insert into 表名(字段们) values (第一行的值),(第二行的值);
    insert into 表1(字段们) (select 字段们 from 表2);

通过第三条，我们可以实现将一个表格中的数据增加到另一张表中：

mysql> create table t1(id int,name char(20));    # 创建表t1
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> create table t2(id int,name char(20),age int);    # 创建表t2
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> insert into t1 values    # 插入数据
        -> (1,'alex'),
        -> (2,'wusir'),
        -> (3,'baoyuan');
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> insert into t2 values
        -> (1,'吴彦祖',18),
        -> (2,'蔡徐坤',21),
        -> (3,'彭于晏',16);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> insert into t2(id,name)(select * from t1);    # 将t1中的数据插入t2中
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from t2;    # 查看t2表格，成功插入
    +------+-----------+------+
    | id   | name      | age  |
    +------+-----------+------+
    |    1 | 吴彦祖    |   18 |
    |    2 | 蔡徐坤    |   21 |
    |    3 | 彭于晏    |   16 |
    |    1 | alex      | NULL |
    |    2 | wusir     | NULL |
    |    3 | baoyuan   | NULL |
    +------+-----------+------+
    6 rows in set (0.00 sec)

#### 删 delete（谨慎操作，不可逆） ####

delete from 表名; # 清空表，自增字段位置
    delete from 表名 where 条件; # 清楚符合条件的数据
    truncate table 表名; # 清空并重置并重置自增字段

#### 改 update ####

update 表名 set 字段名=新值 where 条件;
    update 表名 set 字段1=新值1,字段2=新值2 where 条件;

### 单表查询 ###

创建表：

create table employee(
    id int not null unique auto_increment,
    emp_name varchar(20) not null,
    sex enum('male','female') not null default 'male', #大部分是男的
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int, #一个部门一个屋子
    depart_id int
    );

插入数据：

insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
    ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
    ('alex','male',78,'20150302','teacher',1000000.31,401,1),
    ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
    ('yuanhao','male',73,'20140701','teacher',3500,401,1),
    ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
    ('jingliyang','female',18,'20110211','teacher',9000,401,1),
    ('jinxin','male',18,'19000301','teacher',30000,401,1),
    ('成龙','male',48,'20101111','teacher',10000,401,1),
    
    ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
    ('丫丫','female',38,'20101101','sale',2000.35,402,2),
    ('丁丁','female',18,'20110312','sale',1000.37,402,2),
    ('星星','female',18,'20160513','sale',3000.29,402,2),
    ('格格','female',28,'20170127','sale',4000.33,402,2),
    
    ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
    ('程咬金','male',18,'19970312','operation',20000,403,3),
    ('程咬银','female',18,'20130311','operation',19000,403,3),
    ('程咬铜','male',18,'20150411','operation',18000,403,3),
    ('程咬铁','female',18,'20140512','operation',17000,403,3)
    ;

单表查询的基本结构为：

select xxx from 表名 where 条件 group by 分组 having 聚合的过滤 order by 排序 limit m,n;

#### `select...where...` ####

select语句，用来筛选列。

select * from 表;
    # 可以指定查看某几列
    select 字段名1,字段名2 from 表;
    # 去重
    select distinct 字段名 from 表;
    # 数据可以进行四则运算，可以用as取别名
    select 字段名*12 as 新名字 from 表;
    # 取别名的as可以省略
    select 字段名*12 新名字 from 表;

备注：

对于单独的一列，使用distinct就是起到去重的作用：

但是对于多列来说，distinct的作用是限制两个列组合不重复：

mysql> select distinct sex,age from employee;
    +--------+-----+
    | sex    | age |
    +--------+-----+
    | male   |  18 |
    | male   |  78 |
    | male   |  81 |
    | male   |  73 |
    | male   |  28 |
    | female |  18 |
    | male   |  48 |
    | female |  48 |
    | female |  38 |
    | female |  28 |
    +--------+-----+
    10 rows in set (0.00 sec)

介绍两个重要的函数：

concat(...)    # 拼接
    concat_ws('符号',...)

其使用方法如下：

mysql> select concat(id,'号'),concat_ws(':',emp_name,age,sex) from employee;
    +------------------+---------------------------------+
    | concat(id,'号')  | concat_ws(':',emp_name,age,sex) |
    +------------------+---------------------------------+
    | 1号              | egon:18:male                    |
    | 2号              | alex:78:male                    |
    | 3号              | wupeiqi:81:male                 |
    | 4号              | yuanhao:73:male                 |
    | 5号              | liwenzhou:28:male               |
    | 6号              | jingliyang:18:female            |
    | 7号              | jinxin:18:male                  |
    | 8号              | 成龙:48:male                    |
    | 9号              | 歪歪:48:female                  |
    | 10号             | 丫丫:38:female                  |
    | 11号             | 丁丁:18:female                  |
    | 12号             | 星星:18:female                  |
    | 13号             | 格格:28:female                  |
    | 14号             | 张野:28:male                    |
    | 15号             | 程咬金:18:male                  |
    | 16号             | 程咬银:18:female                |
    | 17号             | 程咬铜:18:male                  |
    | 18号             | 程咬铁:18:female                |
    +------------------+---------------------------------+
    18 rows in set (0.00 sec)

concat和concat\_ws函数同样可以使用as取别名：

mysql> select concat(id,'号') as eid,concat_ws(':',emp_name,age,sex) as info from employee;
    +-------+----------------------+
    | eid   | info                 |
    +-------+----------------------+
    | 1号   | egon:18:male         |
    | 2号   | alex:78:male         |
    | 3号   | wupeiqi:81:male      |
    | 4号   | yuanhao:73:male      |
    | 5号   | liwenzhou:28:male    |
    | 6号   | jingliyang:18:female |
    | 7号   | jinxin:18:male       |
    | 8号   | 成龙:48:male         |
    | 9号   | 歪歪:48:female       |
    | 10号  | 丫丫:38:female       |
    | 11号  | 丁丁:18:female       |
    | 12号  | 星星:18:female       |
    | 13号  | 格格:28:female       |
    | 14号  | 张野:28:male         |
    | 15号  | 程咬金:18:male       |
    | 16号  | 程咬银:18:female     |
    | 17号  | 程咬铜:18:male       |
    | 18号  | 程咬铁:18:female     |
    +-------+----------------------+
    18 rows in set (0.00 sec)

case…when…end 条件语句，用来给符合指定条件的数据做一些特定的操作，其用法为：

mysql> select(
        -> case
        -> when emp_name='alex'
        -> then concat(emp_name,'_bigsb')
        -> when emp_name='jingliyang'
        -> then emp_name
        -> else concat(emp_name,'_sb')
        -> end
        -> )
        -> from employee;
    +---------------------------------------------------------------------------------------------------------------------------------------+
    | (
    case
    when emp_name='alex'
    then concat(emp_name,'_bigsb')
    when emp_name='jingliyang'
    then emp_name
    else concat(emp_name,'_sb')
    end
    ) |
    +---------------------------------------------------------------------------------------------------------------------------------------+
    | egon_sb                                                                                                                               |
    | alex_bigsb                                                                                                                            |
    | wupeiqi_sb                                                                                                                            |
    | yuanhao_sb                                                                                                                            |
    | liwenzhou_sb                                                                                                                          |
    | jingliyang                                                                                                                            |
    | jinxin_sb                                                                                                                             |
    | 成龙_sb                                                                                                                               |
    | 歪歪_sb                                                                                                                               |
    | 丫丫_sb                                                                                                                               |
    | 丁丁_sb                                                                                                                               |
    | 星星_sb                                                                                                                               |
    | 格格_sb                                                                                                                               |
    | 张野_sb                                                                                                                               |
    | 程咬金_sb                                                                                                                             |
    | 程咬银_sb                                                                                                                             |
    | 程咬铜_sb                                                                                                                             |
    | 程咬铁_sb                                                                                                                             |
    +---------------------------------------------------------------------------------------------------------------------------------------+
    18 rows in set (0.00 sec)

表头很长，我们同样可以给它去一个别名：

where条件用来筛选行，其常用的条件有：

1.  比较运算：> < = >= <= !=/<>
2.  范围：in between…and…
3.  模糊查询：
    
    1.  like
        
            name like 'a%'    # 任意多个字符内容
            name like 'a_'    # 任意一个字符内容
    2.  regexp
        
            name regexp '^a'    # 正则匹配
4.  逻辑运算 与或非：
    
     *  and
     *  or
     *  not：not in
5.  判断是否为空：
    
        select * from employee where post_comment is not null;
        select * from employee where post_comment is null;

#### `group by...having...` ####

group by字段，表示根据这个字段进行分组。

聚合函数：

*  count
 *  sum
 *  min
 *  max
 *  avg

聚合函数使用示例：

having队组进行过滤：

#### `order by...limit...` ####

基本格式为：

select * from 表名 order by 字段;    # 从小到大排
    select * from 表名 order by 字段 asc;    # 从小到大排
    select * from 表名 order by 字段 asc;    # 从大到小排
    # limit
    select * from 表 limit m,n; 表示从m+1开始取n条
    select * from 表 limit n; 表示从1开始取n条;就是取前n条