Mysql SQL语句记录

1.tm_old_allowance(allownce_userid)与tm_old_usersh(sh_userid)一一关联对应，由于不知名的原因，造成部分tm_old_usersh数据缺失，现在需要找出tm_old_allowance中的哪些数据没有对应的tm_old_usersh数据，查找Sql如下：

SELECT
     A.allowance_userid
FROM
     tm_old_allowance A
WHERE
     (
	SELECT
	       count(1) AS num FROM tm_old_usersh B WHERE B.sh_userid = A.allowance_userid
     ) = 0
AND A.allowance_operate != 'd'

记录，更复杂一点的：

SELECT
    A.allowance_userid,
    A.allowance_groupid,
    a.allowance_state
FROM
    tm_old_allowance A
WHERE
    (
    SELECT
        COUNT(1) AS num
    FROM
        tm_user_allowance B
    WHERE
        B.ua_userid = A.allowance_userid AND B.ua_groupid LIKE '610102002%' AND B.ua_a_state = 2
) = 0 AND A.allowance_operate != 'd' AND A.allowance_groupid LIKE '610102002%' AND allowance_state = 2

 

2.将tm_old_user中查询的数据插入到一张新的表tm_user65

 

INSERT INTO tm_user65 (
	dataid,
	user_name,
	user_idno,
	user_sex,
	user_photo,
	user_age,
	user_address,
	user_tel,
	user_groupid,
	user_operate,
	user_odate
) SELECT
	user_id,
	user_name,
	user_idno,
	user_sex,
	user_photo,
	user_age,
	user_address,
	user_tel,
	user_groupid,
	user_operate,
	user_odate
FROM
	tm_old_user
LEFT JOIN tm_old_card ON card_userid = user_id
WHERE
	card_lable = 3

3.根据一张表的信息更改另一张表

update table_1 t1,table_2 t2 set t1.column = t2.column where t1.id = t2.pid;

4.where group by having 三者联合使用

如图：找出语文数学分数都大于等于80分的同学

 

sql如下：

SELECT name from ss where score>=80 GROUP BY name HAVING COUNT(*)>1

最终结果是小华。这是我以前面试时遇到的一道笔试题，当时是瞬间蒙圈，感觉简单，写起来却无从下手，后来偶遇一篇博客https://blog.csdn.net/bingogirl/article/details/52559302，彻底茅塞顿开。

5.修改时在要修改的字段上统一追加字符串

UPDATE
    tm_organ g,
    tm_temp_group tm
SET
    g.organ_name = CONCAT(tm.new_groupname,'工作站') 
WHERE
    g.organ_groupid = tm.new_groupid 

 

6.将户金额修改成各成员金额之和

UPDATE poor_db AS a,
 (
	SELECT
		sum(family_money) AS sum
	FROM
		poor_db
	GROUP BY
		user_idno
) AS b
SET a.user_money = b.sum;

7.自连接

【1】如下商品采购表信息，用自连接方式，我们需要找到采购价格比惠惠高的所有信息

sql如下：

SELECT
	b.*
FROM
	shopping AS a,
	shopping AS b
WHERE
	a. NAME = '惠惠'
AND a.price < b.price

结果如下：

  

【2】公司员工信息表

• 员工编号：empno
• 员工姓名：ename
• 员工上级编号：mgr,mgr其实就是empno

sql如下：

SELECT
	e.ename,
	(
		SELECT
			ename
		FROM
			emp d
		WHERE
			d.empno = e.mgr
	) AS boss
FROM
	emp e;

结果：

8.统计各区县的数据，但是数据中只包含到村社区的区划，查询区县数据需要截取数据区划中的前六位

SELECT
	COUNT(*),
	SUM(insure_money),
	LEFT (insure_groupid, 6)
FROM
	`tm_medical_insure`
WHERE
	insure_date BETWEEN '2018-01-01'
AND '2018-12-31'
GROUP BY
	LEFT (insure_groupid, 6)

9.sql练习记录

–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数
 

 

【1】查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT
	a.s_score as '课程01成绩',
	b.s_score as '课程02成绩',
        c.s_name as '学生姓名'
FROM
	score a,
	score b,
        student c
WHERE
    a.c_id = '01' 	    -- 查询出课程01的成绩信息
AND b.c_id = '02' 	    -- 查询出课程02的成绩信息
AND a.s_score > b.s_score   -- 课程01成绩大于课程02成绩
and a.s_id=b.s_id	    -- 课程01的成绩与课程02成绩的大小比较针对的是同一个学生
and a.s_id=c.s_id	    -- 通过成绩的学生编号调取学生信息

【2】查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT
	AVG (s_score),
	a.s_id,
	b.s_name
FROM
	score a,
	student b
WHERE
	a.s_id = b.s_id      -- 课程表与学生表连接调出学生信息
GROUP BY
	a.s_id               --根据学生编号分组
HAVING
	AVG (s_score) >= 60  --根据分组后的学生成绩求平均数

【3】查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
	a.s_id,
	a.s_name,
	count(b.s_score),
	sum(b.s_score)
FROM
	student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
	a.s_id,
	a.s_name

group by 的简单说明:  group by 一般和聚合函数一起使用才有意义,比如 count sum avg等

使用group by的两个要素:
   (1) 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.
   (2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having

【4】查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT
	c.s_name
FROM
	score a,
	score b,
	student c
WHERE
    a.c_id = '01'
AND b.c_id = '02'
AND a.s_id = b.s_id
AND a.s_id = c.s_id

10.mysql去重

delete from cl_a01_4 
WHERE `disabled_idno` in
    (
    SELECT `disabled_idno` FROM
                      (
                      SELECT `disabled_idno` FROM cl_a01_4 GROUP BY `disabled_idno` HAVING count(`disabled_idno`)>1
                      ) t1
    )
AND `disabled_id` NOT IN
    (
    SELECT cid FROM
                    (
                    SELECT max(`disabled_id`) cid FROM cl_a01_4 GROUP BY `disabled_idno` HAVING count(`disabled_idno`)>1
                    ) t2
    )

11.mysql去除字段中空格

update tm_xx set address = replace(replace(replace(address ,char(9),''),char(10),''),char(13),'') 

12.mysql 去除字段中的单引号

update a_die_data set idno = replace(idno,'\'','')