sql 语句

————查所有的列 select * from 表名； 
————查指定列 select 列1，列2 from 表名；

————增加 insert into 表名 values(... 值的顺序与表中字段的顺序对应) insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2'); ————部分增加 insert into 表名（列1，...）values（值1，...） insert into students (name,hometown,brithday)values("黄蓉","桃化岛"，"2016-3-2") ————全列多行插入 insert into 表名 values(...),(...)...; insert into classes values(0,'python1'),(0,'python2'); insert into 表名（列1，...）value(值1，...),(值1，...)...; insert into students(name) value('杨过'),（'杨坤'),('小龙女')；

————修改
update 表名 set 列1=值1，列2=值2...where 条件 update students set gender=0,hometown="北京" where id=5；

————删除 delete from 表名 where 条件 delete from students where id=5；

————通过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 表名 where 条件； where后面支持多种运算符，进行条件的处理： -比较运算符 = > >= <= < != <>(不等于) select * from students where name != '黄蓉'; -逻辑运算符 and or not select * from students where id>3 and gender = 0; -模糊查询 like %表示任意多个任意字符 _表示一个任意字符 select * from students where name like '黄%' -范围查询 in表示在一个非连接的范围内 select * from students where id in (1,3,8); between...and...表示一个连接的范围内 select * from students where id between 3 and 8; selet * 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；

优先级高到低顺序为：小括号，not，比较运算符，逻辑运算符 and 比 or 先运算，如果同时出现并希望先算or，需要结合()使用

——————*排序 order by select * from 表名 order by 列1 asc|desc [,列2 asc|desc，...] 将行数据按照列1排序，如果列1值相同，按照列2排序，以此类推。 默认从小到大 asc即升序 desc从大到小即降序 例1：查询未删除男生信息，按学号降序
select * from students where gender=1 and isdelete=0 order by id desc; 例2：查询未删除学生信息，按名称升序 select * from students where isdelete=0 order by name; 例3：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序 select * from students where order by age desc ,hight desc;

——————聚合函数 5个聚合函数 count(\列) 例1：查询学生总数 select count() from students; max(列) 例2：查询女生的编号最大值 select max(id) from students where gender=2; min(列) 例3：查询未删除的学生最小编号 select min(id) from students where isdelete=0; sum(列) 例4：查询男生的总年龄 select sum(age) from students where gender=1; --平均年龄 select sum(age)/count() from students where gender=1; avg(列) 例5：查询未删除女生的编号平均值 select avg(id) from students where isdelete=0 and gender=2;

——————*分组 group by group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组 group by可用于单个字段分组，也可用于多个字段分组

select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          |
| 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
| 13 | 周杰      |   34 | 176.00 | 女     |      5 |           |
| 14 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
+----+-----------+------+--------+--------+--------+-----------+

select gender from students group by gender;
+--------+
| gender | 
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+
根据gender字段来分组，gender字段的全部值有4个'男','女','中性','保密'，所以分为了4组 当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大

group by + groupconcat() 1、groupconcat(字段名)可以作为输出字段使用 2、表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

select gender，group_concat(name) from students group by gender；
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+

select gender，group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

group by + 集合函数 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

select gender, group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男     | 29,59,36,27,12       |
| 女     | 18,18,38,18,25,12,34 |
| 中性   | 33                   |
| 保密   | 28                   |
+--------+----------------------+

分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+

分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
+--------+----------+

group by + having 1.having条件表达式：用来分组查询后指定一些条件来输出查询结果 2.** having作业和where一样，但having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

group by + with rollup 1.with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*)from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+

select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

——————获取部分行 select * from 表名 limit start，count 从start开始，获取count条数据 例1：查询前3行男生信息 select * from students where gender=1 limit 0,3; 示例：分页 已知：每页显示m条数据，当前显示第n页 求总页数：此段逻辑后面会在python中实现 查询总条数p1 使用p1除以m得到p2 如果整除则p2为总数页 如果不整除则p2+1为总页数 求第n页的数据 select * from students where is_delete=0 limit (n-1)m,m

——————*连接查询 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回 内连接查询 右连接查询 左连接查询 语法 select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列 例1：使用内连接查询班级表与学生表 select * from students inner join classes on students.clsid = classes.id; 例2：使用左连接查询班级表与学生表 select * from students as s left join classes as c on s.cldid = c.id; 例3：使用右连接查询班级表与学生表 select * from students as s right join classes as c on s.cldid = c.id; 例4：查询学生姓名及班级名称 select s.name,c.name from students as s inner join class as c on s.cleid = c.id;

--自关联 设计省信息的表结构provinces id ptitle

设计市信息的表结构citys
id
ctitle
proid
citys表的proid表示城市所属的省，对应着provinces表的id值

答案：
定义表areas，结构如下
id
atitle
pid

说明:
因为省没有所属的省份，所以可以填写为null
城市所属的省份pid，填写省所对应的编号id
这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

创建areas表的语句如下：
create table areas(aid int primary key,atitle varchar(20),pid int);
查询一共有多少个省
select count(*) from areas where pid is null;
例1：查询省的名称为“山西省”的所有城市
select city.* from areas as city inner join areas as province on city.pid = province.aid where province.atitle="山西省"；
例2：查询市的名称为“广州市”的所有区县
select dis.* from areas as dis inner join areas as city on city.aid = dis.pid where city.atitle ="广州市"

--子查询 子查询是嵌入到主查询中 子查询是辅助主查询的,要么充当条件,要么充当数据源 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类 标量子查询: 子查询返回的结果是一个数据(一行一列) 列子查询: 返回的结果是一列(一列多行) 行子查询: 返回的结果是一行(一行多列)

-标量子查询 1,查询班级学生平均年龄 2,查询大于平均年龄的学生 select * from students where age > (select avg(age) from student); -列级子查询 查询还有学生在班的所有班级名字 1,找到学生表中所有班级的id 2，找出班级表中对应的名字 select name from classes where id in (select cls_id from students); -行级子查询 需求: 查找班级年龄最大,身高最高的学生 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素 select * from studens where (age,height) = (select max(age),max(height) from students);

**子查询中特定关键字使用 in 范围 格式: 主查询 where 条件 in (列子查询)

***总结 查询的完整格式 SELECT selectexpr [,selectexpr,...] [ 
FROM tbname [WHERE 条件判断] [GROUP BY {colname | postion} [ASC | DESC], ...] [HAVING WHERE 条件判断] [ORDER BY {colname|expr|postion} [ASC | DESC], ...] [ LIMIT {[offset,]rowcount | rowcount OFFSET offset}] ]

select distinct * from 表名 where .... group by ... having ... order by ... limit start,count

执行顺序为： from 表名 where .... group by ... select distinct * having ... order by ... limit start,count 实际使用中，只是语句中某些部分的组合，而不是全部