MySQL基本用法二

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);---查询年龄身高最高年龄最大的学生