CRUD基本用法
基本查询
查询所有列
select * from 表名;
例:
select * from classes;
查询指定列
可以使用as为列或表指定别名
select 列1,列2,... from 表名;
例:
select id,name from classes;
增加
格式:INSERT [INTO] tb_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
说明:主键列是自动增长,但是在全列插入时需要占位,通常使用0或者 default 或者 null 来占位,插入成功后以实际数据为准
全列插入:值的顺序与表中字段的顺序对应
insert into 表名 values(...)
例:
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');
部分列插入:值的顺序与给出的列顺序对应
insert into 表名(列1,...) values(值1,...)
例:
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');
上面的语句一次可以向表中插入一行数据,还可以一次性插入多行数据,这样可以减少与数据库的通信
全列多行插入:值的顺序与给出的列顺序对应
insert into 表名 values(...),(...)...;
例:
insert into classes values(0,'python1'),(0,'python2');
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例:
insert into students(name) values('杨康'),('杨过'),('小龙女');
修改
格式: UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]…[where 条件判断],修改表数据用update,修改表结构包括字段名和属性用alter
update 表名 set 列1=值1,列2=值2... where 条件
例:
update students set gender=0,hometown='北京' where id=5;
删除
DELETE FROM tbname [where 条件判断]
delete from 表名 where 条件
例:
delete from students where id=5;
逻辑删除,本质就是修改操作
update students set isdelete=1 where id=1;
使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
where后面支持多种运算符,进行条件的处理
比较运算符
逻辑运算符
模糊查询
范围查询
空判断
比较运算符
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
select * from students where id > 3;
select * from students where name != '黄蓉';
select * from students where is_delete=0;
逻辑运算符
and
or
not
select * from students where id > 3 and gender=0;
select * from students where id < 4 or is_delete=0;
模糊查询
like
%表示任意多个任意字符
_表示一个任意字符
select * from students where name like '黄%';
select * from students where name like '黄_';
select * from students where name like '黄%' or name like '%靖';
范围查询
in表示在一个非连续的范围内
select * from students where id in(1,3,8); ---返回的是一个结果集,id只可能=1或3或8
between ... and ...表示在一个连续的范围内
select * from students where id between 3 and 8;--id在[3,8],包含两端
select * from students where (id between 3 and 8) and gender=1;
空判断
**注意:null与''是不同的**
判空is null
select * from students where height is null;
判非空is not null
select * from students where height is not null;
select * from students where height is not null and gender=1;
优先级
优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
语句执行顺序:select–>from—>join–>where–>group by–>having—>order by–>limit
备份与恢复
这里说的数据备份与恢复指数据库的导入和导出
备份
运行mysqldump命令
mysqldump –u root –p 数据库名 > python.sql;
按提示输入mysql的密码
恢复
连接mysql,创建新的数据库
退出连接,执行如下命令
mysql -u root –p 新数据库名 < python.sql
根据提示输入mysql密码
MySQL条件查询
select 列1,列2,… from 表名;
例:
select name from students;
使用 as 给字段起别名
select id as 序号, name as 名字, gender as 性别 from students;
可以通过 as 给表起别名,现在的数据库支持起别名时as的省略
– 表名.字段名
select students.id,students.name,students.gender from students;
– 可以通过 as 给表起别名
select s.id,s.name,s.gender from students as s;
消除重复行
在select后面列前使用distinct可以消除重复的行
select distinct 列1,… from 表名;
例:
select distinct gender from students;
模糊查询
%表示任意多个任意字符
_表示一个任意字符
select * from students where name like '黄%';
select * from students where name like '黄%' or name like '%靖';
范围查询
select * from students where id in(1,3,8); ---查询编号是1或3或8的学生
select * from students where id between 3 and 8;---查询编号在[3,8]的学生
聚合函数
select count(*) from students;
select max(id) from students where gender=2;
select min(id) from students where is_delete=0;
select sum(age) from students where gender=1;
select avg(id) from students where is_delete=0 and gender=2;
分组查询
分组查询主要讲group by,having, group_concat()的用法
group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
group_concat(字段名)可以作为一个输出字段来使用
having 条件表达式:用来分组查询后指定一些条件来输出查询结果,having只能作用于group by之后
with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和
select gender,group_concat(age) from students group by gender having count(*)>10 with rollup;
分页查询
select * from 表名 limit start,count
关联查询
这一部分比较重要,一般会关联两张及两张以上的表来进行查询操作
自关联 join,寻找两张表的交际,比如:选出性别为男且选修了课程的学生:select s.name from students s join subject sc on s.subject_id==sc.id
左关联 left join,左边会全部显示出来,如果左边特有右边没有的则显示null,如查询所有的学生和他们的班级,如果班级不存在则显示空:
select * from students as s left join classes as c on s.cls_id = c.id;
同理也有有关联,但有关联在实际运用中并不多,只是把left换成right即可。
特别用法:自关联
自关联最常见的就是,两张表在设计的时候有大量相同的列重复,那么可以将两张表合在一起,在查询的时候关联本身取别名即可,碧如哪些有层级划分的数据,最典型的应用就是市和省份。
select city.* from areas as city
inner join areas as province on city.pid=province.aid
where province.atitle='上海市';
子查询
这也是很常用的查询方式,简单讲就是select套select,将select的查询结果作为条件继续select,子查询又分为如下三类:
标量子查询: 子查询返回的结果是一个数据(一行一列)
列子查询: 返回的结果是一列(一列多行)
行子查询: 返回的结果是一行(一行多列)
select * from students where age > (select avg(age) from students);---查询年龄>平均年龄的学生
select name from classes where id in (select cls_id from students);----查询被学生选修的课程的名字
select * from students where (height,age) = (select max(height),max(age) from students);---查询年龄身高最高年龄最大的学生