mysql学习之路

1-- 更新

UPDATE help_topic a

SET`name` = 'WANG'

WHERE

    a.help_category_id = 28;

1-- 查询

SELECT
      *
FROM
      help_topic A
WHERE
      A. NAME = 'DAVID';

1--删除

DELETE
FROM
    help_topic
WHERE
    `name` = 'WANG';

1-- 添加

INSERTINTO help_topic (
    `name`,
    `help_category_id`,
    `description`,
    `example`,
    `url`
)
VALUES
    (
        'DAVID',
        28,
        'DAD',
        'DADA',
        'SB'
    );

1—GROUP BY剔除重复,适用于两张表剔重，而UNION是找重（chong）；

SELECT
    help_category_id
FROM
    help_topic
UNION
    SELECT
        help_category_id
    FROM
        help_category
    ORDER BY
        help_category_id;

1-- ORDER BY排序，ASC升序，DESC降序；

SELECT
    help_category_id
FROM
    help_topic
WHERE
    help_category_id LIKE '%2%'
ORDERBY
    help_category_id ASC;

1-- GROUP BY 分组查询，加上COUNT(*)对出现次数求和

SELECT
`name`,COUNT(*)
FROM
help_topic
GROUPBY
`name`;

1-- 分组查询加求和

--COALESCE对最后一项求和进行命名

SELECT COALESCE(help_category_id,'name'),SUM(help_category_id) -- SUM(expr)对expr的值进行求和

AS help_category_id_sum,COUNT(*)  -- 使用 help_category_id_sum 为列名

FROM

help_topic

GROUPBY

help_category_id-- 根据help_category_id查重分组；

WITHROLLUP;-- 根据分组help_category_id的值，进行求和

1--

SELECT

a.`name`

FROM

help_topica  -- 左连接左表；

INNERJOIN -- LEFT JOIN ,,,&  RIGHT JOIN

 help_category b  -- 右连接右表；

WHERE-- WHERE等价于ON

 a.help_category_id=b.help_category_id

ORDERBY `name` ASC

1-- 表复杂查询； 在一张表中查出工资大于部门平均最大工资的员工name

SELECT
MAX(avg_wages),`name`
FROM
(
SELECT
AVG(wages)AS avg_wages,department
FROM
test
GROUPBY
department
) a
LEFTJOIN
`test`b
ON b.department=a.department
WHEREwages>avg_wages

1-- case when then应用

-- 依据条件更改显示方式

SELECT

(CASEWHEN shuxeu>=80 THEN '优秀'

WHENshuxeu>=60 THEN '优秀'

ELSE '不及格' END )AS 数学,

(CASEWHEN yuwen>=80 THEN '优秀'

WHENyuwen>=60 THEN '优秀'

ELSE '不及格' END) AS 语文,

(CASEWHEN yingyu>=80 THEN '优秀'

WHENyingyu>=60 THEN '优秀'

ELSE '不及格' END) AS 英语

FROM

`testdemo`;

1-- 模糊查询

select

*

FROM

orders

WHERE

orderNumber

LIKE'%1%';