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 [bi]> insert into test values ('谢霆锋',188,38,1); Query OK, 1 row affected (0.01 sec) MariaDB [bi]> 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 [bi]> insert into test (name,age) values ('张一山',26); Query OK, 1 row affected (0.00 sec) MariaDB [bi]> 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 [bi]> insert into test (name,age) values ('杨紫',20),('老毕',11);
4、另一种插入数据的语法
insert into tablename set 字段1名称=字段1值,字段2名称=字段值
MariaDB [bi]> 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 [bi]> delete from test where name='哈哈';
2、模糊删除(不建议使用)
MariaDB [bi]> 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 [bi]> update test set age=30; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 MariaDB [bi]> 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 [bi]> update test set high=180 where id=3;
3、修改多个字段的值
MariaDB [bi]> 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 [bi]> select * from t1; +------+-------+ | t1id | t1str | +------+-------+ | 1 | a | | 2 | b | | 3 | c | +------+-------+ 3 rows in set (0.00 sec) MariaDB [bi]> select * from t2; +------+-------+ | t2id | t2str | +------+-------+ | 2 | c | | 3 | d | +------+-------+ 2 rows in set (0.00 sec) MariaDB [bi]> 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 [bi]> 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 [bi]> 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 [bi]> 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 [bi]> 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 [bi]> 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 [bi]> 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 [bi]> 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 [bi]> 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)