mysql基础之mariadb对表中数据的增删改查

复习：

查看表：show tables; 　　创建表：create table 表名(字符类型); 　　删除表：drop table 表名;

对表的结构进行增删改查：

查看表结构：desc 表名;　　修改表-添加字段：alter table 表名;　　

修改表-修改字段:不重命名版：alter table 表名 modify 列名 类型及约束;

修改表-修改字段:重命名版：alter table 表名 change 原名 新名 类型及约束;

修改表-删除字段：alter table 表名 drop 列名;

一、增加表中的数据（insert）

insert语句的语法：insert into tablename(字段1名称，字段2名称，...) values(字段1值，字段2值，...)

1、全列插入

--insert into 表名 values(..)
--主键字段 可以用0 null default 来站位
例子：向test表中插入一条信息（数据与字段要一一对应）
MariaDB [ren]> insert into test values ('谢霆锋',188,38,1);
Query OK, 1 row affected (0.01 sec)

MariaDB [ren]> select * from test;
+-----------+------+------+------+
| name      | high | age  | id   |
+-----------+------+------+------+
| 任彦忠    | NULL | 0    | NULL |
| 哈哈      | NULL | 66   | NULL |
| 胡歌      | NULL | 38   | NULL |
| 谢霆锋    |  188 | 38   |    1 |
+-----------+------+------+------+
4 rows in set (0.00 sec)

2、部分插入（主键不能为空）

MariaDB [ren]> insert into test (name,age) values ('张一山',26);
Query OK, 1 row affected (0.00 sec)
MariaDB [ren]> select * from test;
+-----------+------+------+------+
| name      | high | age  | id   |
+-----------+------+------+------+
| 任彦忠    | NULL | 0    | NULL |
| 哈哈      | NULL | 66   | NULL |
| 张一山    | NULL | 26   | NULL |
| 胡歌      | NULL | 38   | NULL |
| 谢霆锋    |  188 | 38   |    1 |
+-----------+------+------+------+
5 rows in set (0.00 sec)

3、部分插入多条记录

MariaDB [ren]> insert into test (name,age) values ('杨紫',20),('老毕',11);

4、另一种插入数据的语法

insert into tablename set 字段1名称=字段1值，字段2名称=字段值

MariaDB [ren]> insert into test set name='小磐',age=20,id=2;

使用set的方式插入数据时，insert语句中字段的顺序可以与表中的字段顺序不同，而第一种语法中，字段顺序必须与表中的字段顺序相同。

5、aql_mode的模式：

ANSI：宽松模式，对插入数据进行校验，如果不符合定义类型 或长度，对数值截断保存，报警告信息，默认模式；

STRICT_TRANS_TABLES：只在事务型表中进行严格限制；

STRICT_ALL_TABLES：对所有表进行严格限制；

TRADITIONAL：严格模式，当插入数据时，进行数据的严格校验，错误的数据将不能被插入，报error错误。用于事务时，会进行事务的回滚，官方提醒我们说，如果我们使用的存储引擎是非事务型的存储引擎（比如myisam），当我们使用这种模式时，如果执行非法的插入或更新数据操作时，可能会出现部分完成的情况。

二、删除表中的数据（delete）

删除数据需要通过where子句给定删除的范围

1、物理删除

delete from 表名 where 条件

MariaDB [ren]> delete from test where name='哈哈';

2、模糊删除（不建议使用）

MariaDB [ren]> delete from test where name rlike '.*彦*';

like：like的内容不是正则，而是通配符。eg：like "%12__"；A like '%abc%' or A like '%cba%'

rlike：rlike的内容可以是正则。（需要转义）eg：rlike ".*12.*" ；A rlike '.*(abc|cba).*'

3、从test表中找出age>30的数据行，然后将这些行按照age进行降序排列，排列后删除第一个

delete from test where age > 30 order by age desc limit 1;

4、清空表数据

truncate [table] 表名;         #无法恢复数据，并且清空自增
delete from 表名;    #在一定条件下可以恢复数据，不会清除自增

5、逻辑删除

用一条字段来表示这条信息是否已经不能再使用了

-- 给test表添加一个is_delete字段 bit 类型
    alter table test add is_delete bit default 0;

三、修改表中的数据（update）

修改数据也需要通过where子句给定修改的范围

update 表名 set 列1=值1, 列2=值2... where 条件;

1、如果不加where，则是修改的表中所有记录的该字段的数据

MariaDB [ren]> update test set age=30;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [ren]> select * from test;
+-----------+------+------+------+
| name      | high | age  | id   |
+-----------+------+------+------+
| 张柏芝    |  177 | 30   |    3 |
| 王菲      |  177 | 30   |    2 |
| 谢霆锋    |  188 | 30   |    1 |
+-----------+------+------+------+
3 rows in set (0.00 sec)

2、修改一个字段的值

MariaDB [ren]> update test set high=180 where id=3;

3、修改多个字段的值

MariaDB [ren]> update test set high=180,name='李晓丽' where id=3;

四、查询数据（select）(条件，排序，聚合函数，分组，分页)

 创建表并写入数据：

--创建学生表
create table students (
    id int unsigned not null auto_increment primary key,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    high decimal(5,2),
    gender enum('男', '女', '中性', '保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

--创建班级表
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(20) not null
);

--往students表里插入数据
insert into students values
(0,'小明',18,180.00,1,1,0),
(0,'小月月',19,180.00,1,2,0),
(0,'彭于晏',28,185.00,1,1,0),
(0,'刘德华',58,175.00,1,2,0),
(0,'黄蓉',108,160.00,2,1,0),
(0,'凤姐',44,150.00,4,2,1),
(0,'王祖贤',52,170.00,2,1,1),
(0,'周杰伦儿',34,null,1,1,0),
(0,'程坤',44,181.00,1,2,0),
(0,'和珅',55,166.00,1,2,0),
(0,'刘亦菲',29,162.00,2,3,0),
(0,'金星',45,180.00,3,4,0),
(0,'静香',18,170.00,2,4,0),
(0,'郭靖',22,167.00,1,5,0),
(0,'周杰',33,178.00,1,1,0),
(0,'钱小豪',56,178.00,1,1,0),
(0,'谢霆锋',38,175.00,1,1,0),
(0,'陈冠希',38,175.00,1,1,0);

--向classes表里插入数据
insert into classes values (0, '云唯_01期'),(0, '云唯_02期');

（一）简单查询

1、查询所有列

--select * from 表名;
select * from students;

2、查询制定列

select id,name from students;

3、使用as给字段起别名

select id,name as '姓名', age, high, gender from students;

4、通过表名字段查询

select students.name from students;

5、给表起别名

select s.id,s.name,s.age from students as s;

6、消除重复行

-- distinct（/dɪ'stɪŋkt/）有区别的，独特的，明显的
select distinct age from students;

（二）条件查询

1、一定条件查询

select * from where id=5;

select * from where age !=38;

2、比较运算符

-- 查询年纪大于18岁的信息
select * from students where age > 18;

--18岁到28岁之间(and)
select * from students where age >= 18 and age =< 28;
select * from students where age between 18 and 28

--不在18岁到28岁之间(and)
select * from students where age < 18 or age > 28;
select * from students where age not between 18 and 28;

--在18岁以上或者身高180以上的人(or)
select * from students where age > 18 or high > 180;

（三）模糊查询

-- like(rlike)
-- % 替代1个或者多个甚至是没有
--_表示一个字符
-- 查询姓名中有‘小’的所有名字
select * from students where name like '%小%';
-- 查询两个字人的名字
select * from students where name like '__';
-- 查询至少有2个字的名字
select * from students where name like '%__%';
--查询name字段以t开头的所有数据
select * from students where name rlike '^t.*';

（四）范围查询

-- in (1,3,8)表示在一个非连续的范围内
-- 查询 年纪为18和34的人
select * from students where age in (18, 34);
--查询 年龄在17岁到34岁之间的信息
select * from students where age between 17 and 34;
--查询 年纪不在18到34岁的信息
select * from students where age not between 17 and 34;

（五）空判断

-- 判断is null
-- 查询身高为空的信息
select * from students where high is null;
-- 判断非空is not null
select * from students where high is not null;

（六）排序

-- order by 字段
-- asc从小到大排列,即升序（ascend，上升，升入，追溯）
-- desc从大到小排序，即降序（descend，下降，下去，下来）
-- 查询年纪在18到34岁之间的男性，按照年纪从小到大
select * from students where gender=1 and age between 18 and 34 order by age;
-- 查询年纪在18到34岁之间的女性，身高从高到矮
select * from students where gender=2 and age between 18 and 34 order by high desc;
-- order by 多字段
-- 查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
select * from students where age between 18 and 34 and gender=2 order by high desc;
-- 查询年纪在18到34岁的男性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；
select * from students where age between 18 and 34 and gender=1 order by high desc, age, id asc;

（七）聚合函数

-- 总数
-- count 
-- 查询男性有多少人
select count(*) from students where gender=1;
-- 最大值
-- max
-- 查询最大的年纪
select max(age) from students;
-- 查询女性的最高 身高
select max(high) from students where gender=2;
-- 最小值
-- min
select min(high) from students;
-- 求和
-- sum
-- 计算所有人的年龄总和
select sum(age) from students;
-- 平均值
-- avg（average）
-- 计算平均年纪
-- 计算平均年纪 sum(age)/count(*)
select sum(age)/count(*) from students;
select avg(age) from students;
-- 保留2位小数round(圆的，整数的)
select round(avg(age),2) from students;

（八）分组

-- group by
-- 按照性别分组，查询所有的性别
select gender from students group by gender;
-- 计算每组性别的人数
select gender, count(*) from students group by gender;
-- 查询男性组中的姓名 group_concat(合并多个数组；合并多个字符串)
select gender,group_concat(name) from students where gender=1 group by gender; 
-- having
-- 查询每个性别平均年纪超过30岁的性别，以及姓名 having avg(age) > 30
select gender, group_concat(name) from students group by gender having avg(age) > 30;
-- 查询每种性别中的人数多于4个的组的信息
select gender,group_concat(name) from students group by gender having count(*)>4;

（九）分页

-- limit：限制，限定
-- 显示5页
select * from students limit 5;
-- 分页显示，每页显示2条数据（0表示从第一行开始显示，2表示显示两行）
select * from students limit 0,2;
-- 按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据
select * from students where gender=2 order by high desc limit 0,2;

（十）关联查询（连接查询）（多表查询）

 1、交叉连接（cross join）（不常用）

MariaDB [ren]> select * from t1;
+------+-------+
| t1id | t1str |
+------+-------+
|    1 | a     |
|    2 | b     |
|    3 | c     |
+------+-------+
3 rows in set (0.00 sec)

MariaDB [ren]> select * from t2;
+------+-------+
| t2id | t2str |
+------+-------+
|    2 | c     |
|    3 | d     |
+------+-------+
2 rows in set (0.00 sec)

MariaDB [ren]> select * from t1,t2;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    1 | a     |    2 | c     |
|    1 | a     |    3 | d     |
|    2 | b     |    2 | c     |
|    2 | b     |    3 | d     |
|    3 | c     |    2 | c     |
|    3 | c     |    3 | d     |
+------+-------+------+-------+
6 rows in set (0.00 sec)

MariaDB [ren]> select * from t1 cross join t2;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    1 | a     |    2 | c     |
|    1 | a     |    3 | d     |
|    2 | b     |    2 | c     |
|    2 | b     |    3 | d     |
|    3 | c     |    2 | c     |
|    3 | c     |    3 | d     |
+------+-------+------+-------+
6 rows in set (0.00 sec)

-- 多张表连接起来
select * from t1 cross join t2 cross t3;

2、内连接（内关联）（inner join （on））

可以理解为交集（拥有相同元素）

MariaDB [ren]> select * from t1,t2 where t1.t1id=t2.t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    2 | b     |    2 | c     |
|    3 | c     |    3 | d     |
+------+-------+------+-------+
2 rows in set (0.00 sec)

MariaDB [ren]> select * from t1 inner join t2 on t1.t1id=t2.t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    2 | b     |    2 | c     |
|    3 | c     |    3 | d     |
+------+-------+------+-------+
2 rows in set (0.00 sec)

-- inner join ... on
-- 两个表连接查询
select * from students inner join classes
-- 查询能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
-- 按照要求显示姓名，班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
-- 给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
-- 在以上查询中将班级姓名显示在第一列
select classes.name,students.* from students inner join classes on students.cls_id=classes.id;
--关联查询
-- 查询有能够对应班级的学生以及班级信息，按照班级进行排序
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id;
-- 当是同一个班级的时候，按照学生的id从小到大
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id, students.id;

3、自关联（自连接）

自连接就是把同一张表当做两张表连接起来

创捷一个新表并往表内写数据：

create table areas(
aid int primary key auto_increment,
name varchar(20),
pid int
);

--省份
insert into areas(aid,name,pid) values(1,'北京市',null);  
insert into areas(aid,name,pid) values(0,'天津市',null);  
insert into areas(aid,name,pid) values(0,'河北省',null);  
insert into areas(aid,name,pid) values(0,'山西省',null);  
--地级市
insert into areas(aid,name,pid) values(0,'海淀区',1);  
insert into areas(aid,name,pid) values(0,'滨海区',2);  
insert into areas(aid,name,pid) values(0,'沧州市',3);  
insert into areas(aid,name,pid) values(0,'大同市',4);
insert into areas(aid,name,pid) values(0,'朝阳区',1);  
insert into areas(aid,name,pid) values(0,'武清区',2);  
insert into areas(aid,name,pid) values(0,'石家庄',3);  
insert into areas(aid,name,pid) values(0,'太原市',4);
--县级市
insert into areas(aid,name,pid) values(0,'西二旗',5);  
insert into areas(aid,name,pid) values(0,'大港',6);  
insert into areas(aid,name,pid) values(0,'任丘市',7);  
insert into areas(aid,name,pid) values(0,'清徐',8);    
insert into areas(aid,name,pid) values(0,'中关村',5);  
insert into areas(aid,name,pid) values(0,'汉沽',6);  
insert into areas(aid,name,pid) values(0,'河间市',7);  
insert into areas(aid,name,pid) values(0,'阳曲',8);

-- 查询出河北省所有市
select * from areas as province inner join areas as city on province.aid=city.pid having name='河北省';
select province.name,city.name from areas as province inner join areas as city on province.aid=city.pid having province.name='河北省';

-- 子查询
-- 标量子查询
-- 查询出北京市所有区的信息
select * from areas where pid=(select aid from areas where name='北京市');
select * from areas where pid in (select aid from areas where name='北京市');

4、外连接（left join，right join）

“外连接”分为两种，“左连接”(left outer join)和“右连接”(right outer join)：

左连接：

左侧的表中不符合连接条件的记录也会被展示出来，由于右侧表中并没有可与之连接的记录，所以右侧表中使用“空记录”与左侧表中不符合连接条件的记录进行连接。

右连接同理

--左关联，以左边表为基准，条件对应不上的显示null
select * from classes left join students on students.cls_id=classes.id;
--右关联，以后边为基准，条件对应不上的显示null
select * from classes right join students on students.cls_id=classes.id;

5、联合查询：union与union all

语法格式：select columu_name(s) from table_name1 UNION select columu_name(s) from table_name2

当使用union连接两个查询语句时，两个语句查询出的字段数量必须相同，否则无法使用union进行联合查询。

MariaDB [ren]> select * from t1 union select * from t2;
+------+-------+
| t1id | t1str |
+------+-------+
|    1 | a     |
|    2 | b     |
|    3 | c     |
|    2 | c     |
|    3 | d     |
+------+-------+
5 rows in set (0.00 sec)

两张表的记录完全相同的话，使用union查询出的重复结果会被合并为一条

使用union all 进行联合查询时，可以将所有内容显示出来，且不会合并重复行

6、全连接（full join）

我们使用"left join"、"union"、"right join"的组合可以实现所谓的“全连接”。

MariaDB [ren]> select * from t1 left join t2 on t1id=t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    2 | b     |    2 | c     |
|    3 | c     |    3 | d     |
|    1 | a     | NULL | NULL  |
+------+-------+------+-------+
3 rows in set (0.00 sec)

MariaDB [ren]> select * from t1 right join t2 on t1id=t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    2 | b     |    2 | c     |
|    3 | c     |    3 | d     |
| NULL | NULL  |    4 | e     |
+------+-------+------+-------+
3 rows in set (0.00 sec)

MariaDB [ren]> select * from t1 left join t2 on t1id=t2id
    -> union
    -> select * from t1 right join t2 on t1id=t2id;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    2 | b     |    2 | c     |
|    3 | c     |    3 | d     |
|    1 | a     | NULL | NULL  |
| NULL | NULL  |    4 | e     |
+------+-------+------+-------+
4 rows in set (0.00 sec)
MariaDB [ren]> select * from t1 left join t2 on t1id=t2id where t2id is null
    -> union
    -> select * from t1 right join t2 on t1id=t2id where t1id is null;
+------+-------+------+-------+
| t1id | t1str | t2id | t2str |
+------+-------+------+-------+
|    1 | a     | NULL | NULL  |
| NULL | NULL  |    4 | e     |
+------+-------+------+-------+
2 rows in set (0.01 sec)