MySQL

我叫吴桂鑫

已于 2022-03-07 15:00:33 修改

阅读量254

点赞数 1

分类专栏： SQL 文章标签： mysql

于 2018-03-04 23:03:27 首次发布

本文链接：https://blog.csdn.net/baidu_36583119/article/details/79440832

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql–复杂sql语句解析:http://blog.csdn.net/zhuxineli/article/details/7954940
关于 MySQL LEFT JOIN 你可能需要了解的三点：http://www.oschina.net/question/89964_65912
mysql三张表关联查询：https://www.cnblogs.com/vanl/p/5472812.html
正确理解MySQL中的where和having的区别：http://blog.csdn.net/yexudengzhidao/article/details/54924471
MySQL练习题参考答案：http://www.cnblogs.com/pythonxiaohu/p/5749864.html
.MySql常用函数大全讲解:.MySql常用函数大全讲解:
https://blog.csdn.net/sinat_38899493/article/details/78710482
MySQL——合并查询结果（使用 UNION 连接查询结果）：
https://blog.csdn.net/qq_41573234/article/details/80292679
时间字符串互相转换：https://blog.csdn.net/qq_31772441/article/details/80171154

MySQL优先级

** join on(关联) > where(条件) > group by (分组) > 聚合函数(Aggregation) > having(过滤)**
记忆：和关公讲条件，分秒回合，皆是过错。

MySQL中的where和having的区别

having的对象是查询的结果集
where的对象是from对应的表，或者是join on 关联后的联合表

SQL语句中WHERE子句不能有聚合函数:

聚集函数是运行在行组上，计算和返回单个值的函数。

AVG() 返回某列的平均值 ;
COUNT() 返回某列的行数;
MAX() 返回某列的最大值;
MIN() 返回某列的最小值 ;
SUM() 返回某个列之和.

mysql不支持select top n的语法，应该用这个替换：

select * from tablename order by orderfield desc/asc limit position， counter；
position 指示从哪里开始查询，如果是0则是从头开始，counter 表示查询的个数。

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒:

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据（左为主（A LEFT），右为辅（JOIN B））
在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
一个问题：下面两个查询的结果集有什么不同么？

4. SELECT * FROM product LEFT JOIN product_details
         ON (product.id = product_details.id)  AND  product_details.id=2;
+----+--------  +------+--------+-------+
| id | amount |   id   | weight | exist |
+----+--------  +------+--------+-------+
|  1 |    100 |   NULL |   NULL |  NULL |
|  2 |    200 |      2 |     22 |     0 |
|  3 |    300 |   NULL |   NULL |  NULL |
|  4 |    400 |   NULL |   NULL |  NULL |
+----+--------  +------+--------+-------+
4 rows in set (0.00 sec)

5. SELECT * FROM product LEFT JOIN product_details
         ON (product.id = product_details.id)
         WHERE product_details.id=2;
+----+--------+  ----+--------+-------+
| id | amount |   id | weight | exist |
+----+--------+  ----+--------+-------+
|  2 |    200 |    2 |     22 |     0 |
+----+--------+  ----+--------+-------+
1 row in set (0.01 sec)

第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行；所有来自product表的匹配(product.id = product_details.id)的数据行都被检索到了，但在product_details表中只能匹配到一条记录（product_details.id=2）。

第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

某数据库中有：

学生表 s（id，学生名sn，…）、
课程表 c（id，课程名cn，…）、
学生选修课程表 sc（学生唯一标志sid，课程唯一标志cid，分数score…）

问题

###(1)“join…on…”搜索选修课程是税收基础的学生信息
解析：首先，找出问题中所涉及的表
此处涉及的表有3个，分别是：课程表、学生表；
接着，找出表与表之间的关联表
此处课程表和学生表的关联表是：学生选修课程表；
然后，找出问题中的条件
此处的条件是：选修课程是税收基础；
最后，找出所求目标
此处的所求目标是：学生信息。
答案：

SELECT  s.*  
FROM    s,sc,c 
WHERE   s.id = sc.sid  AND  c.id = sc.cid
或者
SELECT  s.*  
FROM    s  
JOIN    sc   ON  s.id = sc.sid
JOIN    c   ON  c.id = sc.cid
WHERE   c.cn = '税收基础'

(2)“group by … having”查询选修课程大于2门课的学生信息

解析：按学生表的学生ID进行分组（group by）和过滤（where/having）

SELECT   m.*
FROM (
   SELECT   s.*,count(*)  AS  scc
   FROM     s
   JOIN     sc  ON  s.id  = sc.sid
   GROUP BY s.id
) as m
WHERE    m.scc>3

或者

SELECT    s.*  , count(*) 
FROM      s
JOIN      sc  on  sc.sid = s.id
GROUP BY  s.id
HAVING    count(*)>3

(3)“id NOT IN(子查询id)”查询没有选择数学课的学生信息

SELECT *
FROM s
WHERE id NOT IN(
    SELECT s.id
    FROM   s,sc,c
    WHERE  s.id = sc.sid and c.id = sc.cid and c.cn='数学'
)

(4)查询每门课程被选修的学生数

SELECT  c.cn,count(1)
FROM  c
JOIN  sc ON s.id=sc.sid
JOIN  c  ON c.id=sc.cid 
GROUP BY c.id

(5)查询每个学员选修了几门课程

SELECT s.id,count(1)
FROM   s
JOIN   sc ON s.id=sc.sid
JOIN   c  ON c.id=sc.cid
GROUP BY s.id

(6)查询选修课程不及格的学生信息及课程信息

SELECT s.*,c.*
FROM   s
JOIN   sc ON s.id=sc.sid
JOIN   c  ON c.id=sc.cid
WHERE  sc.score < 60

(7)查询各门课的平均成绩，输出课程名及平均成绩，最高，最低

SELECT cn,AVG(score),MAX(score),MIN(score)
FROM   s
JOIN   sc ON s.id=sc.sid
JOIN   c  ON c.id=sc.cid
GROUP BY c.id

(8)查询税收基础成绩不低于平均成绩的学生信息及其成绩

SELECT s.*,score
FROM   s
JOIN   sc ON s.id=sc.sid
JOIN   c  ON c.id=sc.cid
where  c.cn = '税收基础' AND score > (SELECT AVG(score) 
                                    FROM sc,c 
                                    WHERE sc.cid=c.id AND c.cn = '税收基础')

(9)查询年龄是21岁的平均成绩最高的学生信息

SELECT *
FROM   s
WHERE  s.id = (SELECT m.sid FROM (SELECT s.id as sid,AVG(score) as aaa FROM s JOIN sc ON s.id = sc.id WHERE s.avg = 21 GROUP BY s.id ) AS m ORDER BY aaa DESC LIMIT 0,1)

MySQL基础

ORM思想

关系数据库	面向对象	表示
表	类	定义存储结构
列	字段	定义存储属性
行	对象	表示一条数据/一个对象

cmd开启MySQL命令行

mysql -u root -p 123456

###查看所有数据库

mysql>show  databases;

查看某数据库中所有表

mysql>show tables;

创建和删除数据库

mysql>create database demo;
mysql>drop database demo;

mysql有各种存储引擎

MyISAM：拥有较高的查询速度，但不支持事务，不支持外键。
InnoDB：支持事务，支持外键，支持行级锁定，性能较低。
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM，处理效率差，且会占用更多的磁盘空间以保留数据和索引。

整型数据

mysql列类型	java数据类型
int	int/Integer
bigint	long/Long

小数类型

（1）float(s,p)和double(s,p)
可存放实型和整型，精度（p）和范围（s）
例如 money double(5,2)表示整数和小数一共占5位，其中小数占2位，都不够精确。

（2）decimal
高精度类型，金额货币优先选择。

mysql列类型	java数据类型
float	float/Float
double	double/Double
decimal	BigDecimal

字符类型

日期类型

二进制类型

计算机单位换算

1KB = 1024B
1MB = 1024KB
1GB = 1024MB

创建表

先进入某一个数据库
输入建表的命令

CREATE TABLE 表名(
     列名1  类型  [约束]
     列名2  类型  [约束]
     列名n  类型  [约束]
)
//最后一行没有逗号
//避免使用数据库的关键字，比如order
//例子：
CREATE TABLE client (
  id     bigint(20)     NOT NULL   AUTO_INCREMENT   PRIMARY KEY ,
  name   varchar(20)    NOT NULL   UNIQUE,
  age    int(11)        DEFAULT NULL,
  sex    bit            DEFAULT 0,
  email  varchar(40)    NOT NULL,
  phone  varchar(40)    NOT NULL
  password  varchar(40)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



create table story(
   id   bigint   not null auto_increment   primary key,
   title   varchar(40)   not null,
   photo_dir varchar(100) not null,
   content tinytext not null,
   date  datetime DEFAULT NULL,
   cid   bigint(20) not null,
   CONSTRAINT `s_fk_cid` FOREIGN KEY (`cid`) REFERENCES `client` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看表的结构

mysql>desc client;

表的约束

MySQL编码修改

mysql>charset gbk;

MySQL编码修改

修改表

UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,...WHERE [条件]

注意：如果没有where条件，则修改全表。

逻辑运算符

AND或&&
OR 或 ||
NOT 或 !

优先级

范围查询

范围查询
范围查询2
范围查询3
范围查询4

模糊查询

LIKE的通配符
_：意思是必须占一个位置，任意字符
%：意思是可以占据0到N个位置，任意字符

WHERE name LIKE '张_三'

SQL子句的执行顺序

先执行FROM子句确定从哪张表开始做查询
接着执行WHERE子句，立马过滤掉不合法的数据
再执行SELECT子句，选择最终需要显示哪些列
最后执行ORDER子句，对查询的结构进行排序

排序查询

降序 Descending
升序 ascending
ORDER BY子句出现在SELECT子句的最后。

SELECT ...ORDER BY 列名1 [ASC/DESC] , 列名2 [ASC/DESC],...;

按别名排序

按别名排序
注意：如果列的别名使用了引号，则按照该别名的排序是无效的。

分页查询的规律

先排序再分页

按照零售价升序排列，设置每页显示5条数据。

SELECT id,pro_name,salePrice FROM product ORDER BY salePrice ASC LIMIT 0,5

什么是聚集函数

聚集函数作用于一组数据，并对一组数据返回一个值。

COUNT：统计结果记录数，一共多少条数据，在java中查询结果总数得使用Long类型来接受。
MAX
MIN
SUM
AVG

使用别名可以简化查询

SELECT c.name,s.title 
FROM client c,story s
WHERE c.id = s.cid

自连接查询

数据恢复和备份

数据恢复和备份1
数据恢复和备份2
数据恢复和备份3
数据恢复和备份4

连接查询

统计上一个月的数据

    <!--统计 上个月 每个用户的 实际支付金额-->
	<select id="statisticsLastMonthGroupByUserId" resultType="map">
		SELECT user_id AS userId, sum(fact_price) AS payY
		FROM user_order
		WHERE pay_status = 1 AND PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_date, '%Y%m' ) ) =1
		GROUP BY user_id
	</select>

统计当天的数据

    <!--统计今日帖子数-->
	<select id="countToday" resultType="int">
		SELECT count(id)
		FROM bbs_post
		<where>
			status = 1
			AND del_flag = 1
			AND create_time BETWEEN  UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d')) AND  UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY))
		</where>
	</select>

时间比较

select * from msg where create_date >= '2020-05-19'

text 类型可以存多大数据

longtext：可以存储4G的数据。
mediumtext：可以存储16M的数据。
text：64K的数据。

linux上操作mysql

创建新用户
CREATE USER wuguixin IDENTIFIED BY ‘123456’;
修改登录方式
ALTER USER ‘wuguixin’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
创建新数据库
create database project01;
授权
grant all privileges on project01.* to ‘wuguixin’@’%’;
退出当前用户
exit;
进入新用户
mysql -u wuguixin -p
进入新数据库
use project01;

使用索引可以提高一倍的搜索速度（前提是index函数内的索引名称都要被用到，否则无效）

select * from product  FORCE INDEX(IDX_VOLUME,IDX_PRICE) where 1.0 < ((volume*2.5 + price*1.6)/2 < 2.0 limit 2,9

随机排序

UPDATE  product  SET  sort = (rand()*200000) 	WHERE  is_delete = 0   AND  `status` = 0

数据库多个字段合一，为唯一值

UNIQUE KEY `product_code_and_year` (`sn_code`,`year`) USING BTREE

注意：如果使用了 join 关联，则查询条件必须放在 where子句中（on语句只筛选对应的表的数据，无法筛选其他表的数据，只放在on语句中是会查询出其他表的全部数据的），最好也放在 on 语句中（join关联执行优先于where），例如下面的 pc.id = ‘12’。

SELECT
		p.id, p.name, pc.class_name 
		FROM product AS p
		LEFT JOIN product_class AS pc on pc.id = p.class_id  ON pc.id = '12'
		WHERE p.name LIKE '奥特曼%'  
		AND  pc.id = '12'

随机获取几条记录

 SELECT
	id
FROM user
ORDER BY rand()
LIMIT 0, #{winnerNum}

排序分页时，数据丢失或重复：

order by后面得指定具有唯一性的字段来排序才行。如：

lqw.last("ORDER BY LENGTH(parent_id_path) ASC, dept_id ASC");

我叫吴桂鑫

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL

mysql–复杂sql语句解析:http://blog.csdn.net/zhuxineli/article/details/7954940关于 MySQL LEFT JOIN 你可能需要了解的三点：http://www.oschina.net/question/89964_65912mysql三张表关联查询：https://www.cnblogs.com/vanl/p/5472812.html
复制链接

扫一扫