MySQL数据行操作

Dear 丶 2021-12-14 02:57 204阅读 0赞

增:  
insert into 表名 (列名1, 列名2,) values(值1, 值2);  
insert into 表名 (列名1, 列名2,) values(值1, 值2),(值1,值2),(值n,值n);  
  
insert into 表名 (列名1, 列名2,) select 列名1, 列名2 from 表名;  
  
删除:  
delete from 表名;  
  
delete from 表名 where id > 10  
delete from 表名 where id < 10  
delete from 表名 where id <= 10  
delete from 表名 where id >= 10  
delete from 表名 where id != 10  
delete from 表名 where id = 10 and name='xxx'; and : 并且 两个条件都必须要成立  
delete from 表名 where id = 10 or name='xxx'; or : 或者 只要满足一个条件成立  
修改:  
update 表名 set name='zekai', age=23 where id > 10;  
  
查询:  
  
基本:  
select \* from 表名;  
select name , age from 表名;  
  
高级:  
  
a. where 条件查询:  
select \* from 表名 where id=10;  
select \* from 表名 where id >10 and id<15;  
select \* from 表名 where id > 10;  
!= : 不等于

between and: between是一个闭区间  
select \* from t4 where id between 9 and 12;  
  
in: 在某一个集合中  
select \* from t4 where id in (9,10,11....);

select \* from t4 where id in (select id from t3 where id between 2 and 4)

b. 通配符:  
alex  
  
select \* from 表 where name like 'ale%' - ale开头的所有（多个字符串）  
select \* from 表 where name like 'ale\_' - ale开头的所有（一个字符）  
  
c. 限制取几条:  
  
select \* from 表名 limit 索引偏移量, 取出多少条数据;

select \* from t3 limit 0, 10; 第一页  
select \* from t3 limit 10, 10; 第二页  
  
page = input('page:')  
  
page 索引偏移量 数据量(offset)  
1 0 10  
2 10 10  
3 20 10  
4 30 10  
  
page (page-1)\*offset offset  
  
分页核心SQL:  
  
select \* from t3 limit (page-1)\*offset, offset;

d. 排序:  
  
order by  
  
降序:  
select \* from t4 order by 列名 desc; descending  
  
升序:  
select \* from t4 order by 列名 asc; ascending

多列:  
  
create table t7(  
  
id int auto\_increment primary key,  
num int not null default 0,  
age int not null default 0  
)charset=utf8;  
  
insert into t7 (num, age) values (2, 12),(3,13),(4, 12);  
  
select \* from t4 order by num desc, name asc;  
  
如果前一列的值相等的话, 会按照后一列的值进行进一步的排序.

e. 分组  
  
select age, 聚合函数(count(num)/sum(num)/max(num)/min(num)/avg(num)) from 表名 group 　　　　　　　　by 列名;

select age, avg(num) from t7 group by age;  
  
select age, count(num) from t7 group by age;  
  
select age, count(num) as cnt from t7 group by age; 显示别名 as  
  
having的二次删选:  
select age, count(num) as cnt from t7 group by age having cnt>1;  
  
where 和 having的区别:  
1). having与where类似,可筛选数据  
2). where针对表中的列发挥作用,查询数据  
3). having针对查询结果中的列发挥作用,二次筛选数据, 和group by配合使用

f. 连表操作  
select \* from userinfo, department; (笛卡尔积)  
  
select \* from userinfo, department where userinfo.depart\_id=department.id;  
  
左连接:  
  
select \* from userinfo left join department on userinfo.depart\_id=department.id;  
左边的表全部显示, 右边没有用到不显示  
  
右连接:  
  
select \* from userinfo right join department on userinfo.depart\_id=department.id;  
右边的表全部显示, 左边没关联的用null表示  
  
内连接:  
左右两边的数据都会显示  
  
ps:  
a.只需要记住左连接 left join  
  
b.可以连接多张表 通过某一个特定的条件  
  
注意查询的顺序:  
select name,sum(score) from 表 where id > 10 group by score having age> 12 order by age desc 　　　　　　limit 2, 10

转载于:https://www.cnblogs.com/duGD/p/11022681.html