MySQL数据库CRUD

INSERT语句

INSERT INTO 表名 (column1,column2,column3,…)
VALUES (value1,value2,value3,…);

INSERT INTO website (url,country)
VALUES ('https://www.baid.com',CN)

可以省略列信息,但所有字段都需要按顺序赋值才能相加

[En]

Column information can be omitted, but all fields are required to be sequentially assigned values to add

INSERT INTO 表名
VALUES (value1,value2,value3,…);

INSERT INTO website
VALUES (1,'百度','https://www.baid.com',CN)

UPDATE语句

UPDATE 表名
SET column1=value1,column2=value2,…

WHERE some_column=some_value;
修改 WHERE 确定的某些记录的列数据,如果没有 WHERE,将修改所有记录

//修改所有记录
UPDATE employee
SET salary = 15000
//使用WHERE过滤
UPDATE employee
SET salary = salary*1.2,birthday = '2022-7-13'
WHERE name = '枫原万叶'

DELETE语句

DELETE FROM 表名
WHERE some_column=some_value;
删除 WHERE 确定的某些记录,如果没有 WHERE,将删除所有记录

DELETE FROM employee
WHERE salary = 15000

注意DELETE 语句是删除一条记录而不能删除某一列的值,可以通过 UPDATE 把值设置为 NULL‘ ‘

SELECT语句

SELECT [DISTINCT]*| {column1,column2,..,} FROM 表名

  • DISTINCT可选,表示是否去掉重复数据。
    • 表示查询所有列,如果没有 * ,需要指定要查询的列名

您可以使用表达式对查询的列数据进行操作。

[En]

You can use expressions to operate on the column data of a query.

//统计学生总分
SELECT name, (chinese+english+math) FROM student;

您可以为查询的表达式或其他字段指定别名,执行查询语句后的输出结构将使用别名而不是字段名。

[En]

You can specify an alias for the expression or other fields of the query, and the output structure after the query statement is executed will use the alias instead of the field name.

SELECT name AS '名字', (chinese + english + math + 10) AS total_score
    FROM student

在where字句中使用的比较运算符

运算符 功能 >,

-- 比较查找
SELECT * FROM student WHERE english > 90
-- 区间查找,注意是闭区间
SELECT * FROM student WHERE english BETWEEN 80 AND 90
-- IN列表查找
SELECT * FROM student WHERE math IN (89, 90, 91);
-- 模糊查询,查找姓张的人
SELECT * FROM student WHERE name LIKE '张%'
-- 日期用于比较
SELECT * FROM emp WHERE hiredate>'1992-01-01'

ORDER BY column asc|desc

  • column 指定待排序的列名,列明也可以是之前as定义的别名
  • asc是升序排列,也是默认情况,desc是降序
  • 排序可以多次使用,即前一字段中的相同数据按后一字段排序。
    [En]

    sorting can be used multiple times, that is, the same data in the previous field is sorted according to the latter field.*

--降序排列SELECT name, (chinese + english + math) AS total_score    FROM student        WHERE name LIKE '韩%' ORDER BY total_score desc;-- 两次排序SELECT * FROM emp    ORDER BY deptno ASC,sal DESC;

返回符号条件的行数,即符合条件的记录数

[En]

Returns the number of rows of symbolic conditions, that is, the number of records that meet the criteria

SELECT COUNT(*) | COUNT(列名) FROM 表名
如果指定了列名, COUNT 不会将空记录计入到总数中。

//统计表中的所有记录的条数
SELECT COUNT(*) FROM student;
//WHERE语句过滤
SELECT COUNT(*) FROM student WHERE math > 80;

求和函数将相应的列数据相加,只能在数值列中使用

[En]

Summation function, which adds the corresponding column data, can only be used in numeric columns

SELECT { SUM(列名), SUM(列名) … } FROM 表名

SELECT SUM(math),SUM(english),SUM(chinese) FROM student;

对符合条件的列数据求平均值

[En]

Average the column data that meet the criteria

SELECT {AVG(列名), AVG(列名) …} FROM 表名

//各科成绩求平均值
SELECT AVG(math + english + chinese) FROM student;

SELECT MAX(列名)/MIN(列名) FROM 表名

SELECT MAX(math), MIN(math) FROM student

GROUP可以对数据按列进行分组,即列相同的放在同一组

-- 把员工按照部门进行分组,然后统计每个部门的平均薪资和最高工资
SELECT AVG(sal), MAX(sal) , deptno
FROM emp GROUP BY deptno

说明:GROUP BY分组后直接查询获得的是第一个该组成员,比如这里部门号为10,如果有多个部门号为10的数据,只会输出按照顺序排第一的数据

HAVING 对之前用 GROUP BY分组得到的结果进行过滤

//对之前的分组过过滤掉平均薪资只有两千的部门
SELECT AVG(sal), deptno
    FROM emp GROUP BY deptno
        HAVING AVG(sal) < 2000

使用字符串函数,可以将查询得到的字符串数据经过处理后输出。

[En]

Using the string function, you can output the string data obtained by the query after some processing.

函数 功能 CONCAT(string2,..) 连接列数据和指定的字符串 UCASE(string2) 字符串转大写 LCASE(string2) 字符串转小写 LENGHT(string) string长度(字节) REPLACE (str ,search_str ,replace_str ) 在 str 中用 replace_str 替换 search_str

函数 功能 ABS (num) 求绝对值 CEILING (number2) 向上取整 FLOOR (number2) 向下取整 FORMAT (number,decimal_places ) 保留小数位数(四舍五入) RAND(seed) 返回随机数 其范围为 0 ≤ v ≤ 1.0

  • 如果使用 rand(), 每次返回不同的随机数 ,在 0 ≤ v ≤ 1.0
  • 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变,该随机数也不不会变

MD5信息摘要算法(英语:MD5 Message-Digest Algorithm),一种被广泛使用的密码散列函数,可以产生出一个128位(16字节)的散列值(hash value), 相同的数据MD5加密后是一样的,不同的数据加密后理论上存在相同的可能。

MySQL提供函数 MD5(str),计算一个给定字符串的 MD5 摘要,并将结果作为一个 32 位的由十六进制字符 组成的字符串返回。一个十六进制数据可以由4个二进制数据表示,正好128位数据。
通常我们将用户密码写入数据库时,保存的不是密码的原文,而是对密码进行加密后再保存到数据库中,这样可以防止当数据库泄漏后,对方拿到的不是密码的原文,账号就不会被盗走。当验证登录时,用户输入密码,对其进行MD5加密后与数据库中保存的MD5值进行比较,相同则登录成功。

-- 插入数据,对密码'yjh123456'进行加密
INSERT INTO user
    VALUES(100,'yjh',MD5('yjh123456'))

它主要由两种类型组成,用于不同数据的不同处理。

[En]

It is mainly composed of two kinds, which are used to deal with different data differently.

语法: IF(exp1,exp2,exp3)
说明:exp1为True时返回exp2,否则返回exp3

-- 从emp表中查找数据comm为空时显示0.0,否则显示本身
SELECT ename,IF(comm IS NULL,0.0,comm)
    FROM emp

注意:这里判断是否为空,要使用 IS NULL,不为空使用 IS NOT NULL

相当于条件分支语句,您可以处理更多可能的情况

[En]

Equivalent to conditional branching statements, you can deal with more possible situations

语法:
SELECT CASE
WHEN exp1 THEN exp2
WHEN exp2 THEN exp2
END;

SELECT ename,(SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job='MANAGER' THEN '经理'
WHEN job='SALESMAN'THEN'销售人员'
ELSE job
END) AS'job'--对整列重命名
FROM emp;

当我们的查询返回的记录太多时,为了防止数据爆炸,我们可以使用分页查询,即查询符合条件的某些记录。

[En]

When our query returns too many records, in order to prevent data explosion, we can use a paged query, that is, to query certain records that meet the conditions.

基本语法:
SELECT …
LIMIT START ROWS;

作用,显示从START + 1 开始的 ROWS 条记录

当查询的要求很复杂时,需要使用多个词,这些词的顺序应该是:

[En]

When the requirements of a query are complex, multiple words need to be used, and the order of these words should be:

SELECT column.. FROM table
WHERE exp
GROUP BY column
HAVING condition
ORDER BU colunm
LIMIT start, rows

有时候我们需要查两个或两个以上的表的数据,只需要在 FROM 后面多添加一个表名即可,在不作任何 WHRER字句过滤时,得到的是一个 笛卡尔集,它将一个表的每一条记录与其它表任何一条记录组合,因此得到的 总记录数是每个表的记录的乘积。

--使用where字句过滤多表查询的结果
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno--出现相同字段时,用表名.字段

自联接是指将同一个表查询为两个表。当我们需要查询一个字段两次,但查询条件不同时,可以使用自连接来解决问题。使用自连接时需要注意几点:

[En]

Self-join is to query the same table as two tables. When we need to query a field twice but the query conditions are different, we can use self-join to solve the problem. There are several points to pay attention to when using self-connection:

  • 因为它们是相同的表,所以我们需要为这两个表指定别名,后跟表名。
    [En]

    because they are the same tables, we need to specify aliases for these two tables, followed by the table name.*

  • 最后为列名也指定别名
--查询员工的名字并且根据它的上级编号再查出上级的名字SELECT worker.ename AS'职员名',boss.ename AS'上级名'FROM emp worker,emp bossWHERE worker.mgr = boss.empno;

如果我们想要在多表查询中保留其中一个表的所有内容,我们可以使用外部联接。如果保留的表在右侧,则它是右连接,而在左侧是左连接。

[En]

If we want to keep all the contents of one of the tables in a multi-table query, we can use an outer join. If the reserved table is on the right, it is a right join, and on the left is a left join.

比如,在学生分数表和学生信息表中,我们想显示每个人的成绩,根据对应的学号找到学生的相关信息,但有些人不参加考试,没有它的分数。但是我们也想输出它的信息,我们可以使用右边的链接来显示所有的学生信息表,在这个字段的分数中,那些没有显示的都是空的。

[En]

For example, in the student score sheet and the student information table, we want to display everyone’s scores and find the relevant information about the students according to the corresponding student numbers, but some people do not take the exam and do not have its scores. But we also want to output its information, we can use the right link to display all the student information tables, in the scores of this field, those that are not shown are empty.

语法:SELECT … FROM 表1 LEFT JOIN 表二 ON [条件…]

SELECT name, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;

子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询,子查询分为单行子查询,多行子查询,多列子查询,同时子查询结果还可以作为一张表使用。

单行子查询返回一行查询数据

[En]

A single-row subquery returns a row of query data

-- 单行子查询:显示与 SMITH 同一部门的所有员工
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH' )

多行子查询使用 IN 关键字

-- 如何查询和部门 10 的工作相同的雇员的
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) AND deptno <> 10

你可以将子查询的结果作为一个表,然后进行多表查询,这种查询方法可以解决许多复杂的查询问题,当前展示一个你在那里看到的案例,然后在未来用更多的理解来丰富它。

[En]

You can take the results of the subquery as a table, and then carry out a multi-table query, this query method can solve many complex query problems, currently show a case that you see there, and then enrich it with more understanding in the future.

--查询 ecshop 中各个类别中,价格最高的商品
--先得到 各个类别中,价格最高的商品 max + group by cat_id, 当做临时表
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
FROM (SELECT cat_id , MAX(shop_price) AS max_price
        FROM ecs_goods
            GROUP BY cat_id) temp , ecs_goods
WhERE temp.cat_id = ecs_goods.cat_id
AND temp.max_price = ecs_goods.shop_price

注释:关于这个查询需求,一开始我以为直接用 MAX+分组就好了,但是输出结果与目标不一致,这是因为这里还要输出商品号,商品号和这个最高价格不匹配,这是GROUP BY导致的,这里的商品号其实是每一组第一个成员的商品号

如果要求查询结果具有多个字段的要求,则可以使用多列子查询。

[En]

If you require query results to have requirements for multiple fields, you can use multi-column subqueries.

-- 查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN' ) AND ename != 'ALLEN

有时候为了多个查询结构需要合并,可以使用集合操作符 UNION(去重), UNION ALL(不去重)

Original: https://www.cnblogs.com/yjh1024/p/16530505.html
Author: Nights_Watch
Title: MySQL数据库CRUD

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505129/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球