MySQLStudy——数据行 详细操作
数据行的操作
增
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: 闭区间 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. 通配符: 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表示 内连接: 左右两边的数据都会显示 select * from 表名 inner join 表名 on 链接条件; select * from emp inner join dep on emp.dep_id = dep.id; 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, 1;