mysql之知识点与细节_MySQL
程序员文章站
2022-03-25 11:21:53
...
bitsCN.com
mysql之知识点与细节 这些天又把mysql系统的看了一遍，温故而知新……
1. char/varchar 类型区别char定长字符串，长度固定为创建表时声明的长度（0-255），长度不足时在它们的右边填充空格以达到声明长度。当检索到CHAR值时，尾部的空格被删除掉varchar变长字符串(0-65535),VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节),varchar值保存时不进行填充。当值保存和检索时尾部的空格仍保留。 它们检索的方式不同,char速度相对快些注意它们的长度表示为字符数，无论中文还是英文均是那么长
text搜索速度稍慢，因此如果不是特别大的内容，用char/varchar,另外text不能加默认值2. windows中mysql自带的客户端中查询的内容乱码，那是因为系统的编码为gbk,使用前发送一条"set names utf8"语句即可3. 小数型：float(M,D)，double(M,D),decimal(M,D) M代表总位数，不包括小数点，D代表小数位，如(5,2) -999.99——999.99 M是小数总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。4. group by与聚合函数结合使用才有意义（sum,avg,max,min,count），group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面，如果没有出现，只取每类中第一行的结果
如果想查询每个栏目下面最贵的商品：id,价格，商品名,种类。用select goods_id,cat_id,goods_name,max(shop_price) from goods group by cat_id;查询的结果商品名，id是和最贵商品不匹配的，如果再加上order by哪？也是错误的，因为:Select语句执行有顺序（语法前后也有）： where子句基于指定的条件对记录行进行筛选； group by子句将数据划分为多个分组； 使用聚集函数进行计算； 使用having子句筛选分组； 计算所有的表达式； 使用order by对结果集进行排序。
可以用下面查询语句： select * from (select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc) as tmp group by cat_id;5. having having子句在查询过程中慢于聚合语句(sum,min,max,avg,count).而where子句在查询过程中则快于聚合语句(sum,min,max,avg,count)。 简单说来： where子句： select sum(num) as rmb from order where id>10 //只有先查询出id大于10的记录才能进行聚合语句
having子句: select reportsto as manager, count(*) as reports from employees group by reportsto having count(*) > 4以下这条语句是错误的: select goods_id,cat_id,market_price-shop_price as sheng where cat_id=3 where sheng>200; 应改为: select goods_id,cat_id,market_price-shop_price as sheng where cat_id=3 having sheng>200; where针对表中的列发挥作用,查询数据，having针对查询结果中的列发挥作用,筛选数据 看下面的一道面试题：有下面一张表
查询：有两门及两门以上不及格成绩同学的平均分 起初用的以下语句： select name,count(score1;这条语句是不行的首先弄清以下2点：a，count(exp) 参数无论是什么，查询的都是行数，不受参数结果影响如
b，可以用如下语句,将count换成sum:
或者：select name,avg(score) from stu group by name having sum(score1;写法6. 子查询 a. where 子查询：把内层查询的结果作为外层查询的比较条件。eg：查询最新的商品select max(goods_id),goods_name from goods;报错：Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause可以用这样的查询：select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods); 查询每个栏目下的最新商品: select goods_id,cat_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
b.from 型子查询：把内层查询结果当成临时表，供外层sql再次查询（临时表必须加一个别名）查询每个栏目下最新商品 select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as t group by cat_id;5中 查询挂科两门及以上同学的平均分 select sname from (select name as sname from stu) as tmp; c. exists子查询：把外层查询的结果变量，拿到内层，看内层的查询是否成立查询有商品的栏目：select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);
由于没有条件，将会查出所有栏目: select cat_id,cat_name from category where exists (select * from goods); 用in也可实现7. in(v1,v2-----) between v1 and v2(包括v1,v2) like(%,_) order by column1(asc/desc),column2(asc/desc)先按第一个排序，然后在此基础上按第二个排序8. union 把两次或多次查询结果合并起来两次查询的列数一致 ，对应列的类型一致列名不一致时，取第一个sql的列名如果不同的语句中取出的行的值相同，那么相同的行将会合并（去重复），如果不去重用union all来指定 如果子句中有order by,limit 子句必须加（），select * from ta union all select * from tb;
取第四栏目商品，价格降序排列，还想取第五栏目商品，价格也按降序排列 (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=5 order by shop_price desc); 推荐放到所有子句之后，即：对最终合并的结果来排序 ( select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=5 order by shop_price desc);
9. 连接查询 左连接： select column1,column2,columnN from ta left join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看] where group,having....照常写 右连接： select column1,column2,columnN from ta right join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看] where group,having....照常写 内连接： select column1,column2,columnN from ta inner join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看] where group,having....照常写 左连接以左表为准，去右表找匹配数据，没有匹配的列用null补齐，有多个的均列出如有下两表：
select boy.*,girl.* from boy left join girl on boy.flower=girl.flower;结果：
左右连接可以相互转化，推荐用左连接，数据库移植方便 内连接：查询左右表都有的数据（左右连接的交集） 选取都有配对的组合
左或右连接查询实际上是指定以哪个表的数据为准，而默认（不指定左右连接）是以两个表中都存在的列数据为准，也就是inner join mysql不支持外连接 outer join 即左右连接的并集当多个表中都有的字段要指明哪个表中的字段 三个表连接查询 brand,goods,categoryselect g.goods_id,cat_name,g.brand_id,brand_name,goods_name from goods g left join brand b on b.brand_id=g.brand_id left join category c on g.cat_id=c.cat_id; 作者 ljfbest bitsCN.com
mysql之知识点与细节 这些天又把mysql系统的看了一遍，温故而知新……
1. char/varchar 类型区别char定长字符串，长度固定为创建表时声明的长度（0-255），长度不足时在它们的右边填充空格以达到声明长度。当检索到CHAR值时，尾部的空格被删除掉varchar变长字符串(0-65535),VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节),varchar值保存时不进行填充。当值保存和检索时尾部的空格仍保留。 它们检索的方式不同,char速度相对快些注意它们的长度表示为字符数，无论中文还是英文均是那么长
text搜索速度稍慢，因此如果不是特别大的内容，用char/varchar,另外text不能加默认值2. windows中mysql自带的客户端中查询的内容乱码，那是因为系统的编码为gbk,使用前发送一条"set names utf8"语句即可3. 小数型：float(M,D)，double(M,D),decimal(M,D) M代表总位数，不包括小数点，D代表小数位，如(5,2) -999.99——999.99 M是小数总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。4. group by与聚合函数结合使用才有意义（sum,avg,max,min,count），group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面，如果没有出现，只取每类中第一行的结果
如果想查询每个栏目下面最贵的商品：id,价格，商品名,种类。用select goods_id,cat_id,goods_name,max(shop_price) from goods group by cat_id;查询的结果商品名，id是和最贵商品不匹配的，如果再加上order by哪？也是错误的，因为:Select语句执行有顺序（语法前后也有）： where子句基于指定的条件对记录行进行筛选； group by子句将数据划分为多个分组； 使用聚集函数进行计算； 使用having子句筛选分组； 计算所有的表达式； 使用order by对结果集进行排序。
可以用下面查询语句： select * from (select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc) as tmp group by cat_id;5. having having子句在查询过程中慢于聚合语句(sum,min,max,avg,count).而where子句在查询过程中则快于聚合语句(sum,min,max,avg,count)。 简单说来： where子句： select sum(num) as rmb from order where id>10 //只有先查询出id大于10的记录才能进行聚合语句
having子句: select reportsto as manager, count(*) as reports from employees group by reportsto having count(*) > 4以下这条语句是错误的: select goods_id,cat_id,market_price-shop_price as sheng where cat_id=3 where sheng>200; 应改为: select goods_id,cat_id,market_price-shop_price as sheng where cat_id=3 having sheng>200; where针对表中的列发挥作用,查询数据，having针对查询结果中的列发挥作用,筛选数据 看下面的一道面试题：有下面一张表
查询：有两门及两门以上不及格成绩同学的平均分 起初用的以下语句： select name,count(score1;这条语句是不行的首先弄清以下2点：a，count(exp) 参数无论是什么，查询的都是行数，不受参数结果影响如
b，可以用如下语句,将count换成sum:
或者：select name,avg(score) from stu group by name having sum(score1;写法6. 子查询 a. where 子查询：把内层查询的结果作为外层查询的比较条件。eg：查询最新的商品select max(goods_id),goods_name from goods;报错：Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause可以用这样的查询：select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods); 查询每个栏目下的最新商品: select goods_id,cat_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
b.from 型子查询：把内层查询结果当成临时表，供外层sql再次查询（临时表必须加一个别名）查询每个栏目下最新商品 select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as t group by cat_id;5中 查询挂科两门及以上同学的平均分 select sname from (select name as sname from stu) as tmp; c. exists子查询：把外层查询的结果变量，拿到内层，看内层的查询是否成立查询有商品的栏目：select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);
由于没有条件，将会查出所有栏目: select cat_id,cat_name from category where exists (select * from goods); 用in也可实现7. in(v1,v2-----) between v1 and v2(包括v1,v2) like(%,_) order by column1(asc/desc),column2(asc/desc)先按第一个排序，然后在此基础上按第二个排序8. union 把两次或多次查询结果合并起来两次查询的列数一致 ，对应列的类型一致列名不一致时，取第一个sql的列名如果不同的语句中取出的行的值相同，那么相同的行将会合并（去重复），如果不去重用union all来指定 如果子句中有order by,limit 子句必须加（），select * from ta union all select * from tb;
取第四栏目商品，价格降序排列，还想取第五栏目商品，价格也按降序排列 (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=5 order by shop_price desc); 推荐放到所有子句之后，即：对最终合并的结果来排序 ( select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=5 order by shop_price desc);
9. 连接查询 左连接： select column1,column2,columnN from ta left join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看] where group,having....照常写 右连接： select column1,column2,columnN from ta right join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看] where group,having....照常写 内连接： select column1,column2,columnN from ta inner join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看] where group,having....照常写 左连接以左表为准，去右表找匹配数据，没有匹配的列用null补齐，有多个的均列出如有下两表：
select boy.*,girl.* from boy left join girl on boy.flower=girl.flower;结果：
左右连接可以相互转化，推荐用左连接，数据库移植方便 内连接：查询左右表都有的数据（左右连接的交集） 选取都有配对的组合
左或右连接查询实际上是指定以哪个表的数据为准，而默认（不指定左右连接）是以两个表中都存在的列数据为准，也就是inner join mysql不支持外连接 outer join 即左右连接的并集当多个表中都有的字段要指明哪个表中的字段 三个表连接查询 brand,goods,categoryselect g.goods_id,cat_name,g.brand_id,brand_name,goods_name from goods g left join brand b on b.brand_id=g.brand_id left join category c on g.cat_id=c.cat_id; 作者 ljfbest bitsCN.com