常用sql语句汇总

最新推荐文章于 2025-02-10 23:24:54 发布

SanfordLuo

最新推荐文章于 2025-02-10 23:24:54 发布

阅读量409

点赞数 1

分类专栏：数据库文章标签： sql mysql 面试

本文链接：https://blog.csdn.net/SanfordLuo/article/details/90812780

版权

数据库专栏收录该内容

2 篇文章

订阅专栏

查询出不同的name：

SELECT DISTINCT
    name
FROM
    test_user;

把id为5的oldName修改为newName ：

UPDATE test_user 
SET 
    name = jay
WHERE
    id = 5;

查询重复出现的name：

SELECT 
    name
FROM
    pick_record
GROUP BY name
HAVING COUNT(name) > 1;

表Employee第二高Salary 展示为 SecondHighestSalary

SELECT 
    MAX(Salary) AS SecondHighestSalary
FROM
    Employee
WHERE
    Salary < (SELECT 
            MAX(Salary)
        FROM
            Employee
        WHERE
            Salary);

表:people, num0, num1, num0升序, num1降序

SELECT 
    *
FROM
    people
ORDER BY num1 DESC;

SELECT 
    *
FROM
    people
ORDER BY num0 , num1 DESC;

分页：

SELECT 
    *
FROM
    test_user
LIMIT 0 , 20;

分组：

SELECT 
    name, COUNT(name)
FROM
    test_user
GROUP BY name;

提取倒数10条数据：

SELECT 
    *
FROM
    test_user
ORDER BY id DESC
LIMIT 10;

删除邮箱重复的记录：

DELETE p1 FROM t_user p1,
    t_user p2 
WHERE
    (p1.email = p2.email)
    AND (p1.id > p2.id);

左连接，右连接，全连接，内连接

select * from biao_a left join biao_b on biao_a.ID=biao_b.ID;

select * from biao_a right join biao_b on biao_a.ID=biao_b.ID;

select * from biao_a full join biao_b on biao_a.ID=biao_b.ID;

select * from biao_a join biao_b on biao_a.ID=biao_b.ID;

字段增删改

ALTER TABLE biao_a ADD COLUMN `add_column` VARCHAR(24) NOT NULL COMMENT '增加字段测试' AFTER `status`;

ALTER TABLE biao_a DROP COLUMN `add_column`;

ALTER TABLE biao_a CHANGE `old_column` `new_column` VARCHAR(24) NOT NULL COMMENT '修改字段测试' AFTER `status`;