虽然关注我公众号的大多数(至少70%)是因为我分享的各种科研软件资源来的,看编程技术类文章的很少,但是推荐大家有空可以学一下这个,比编程语言简单多了,当你面对大量数据存储及查询的时候,它就有大用了!
所以花了点时间给大家整理了MySQL数据库基本语法操作,如果你有了解过MySQL一些基本概念,但是不知道如何操作的,这篇文章将带你入门;无基本概念的纯小白,本文无法让你入门,建议找本书或者视频课程来学习,会讲解的特别细致!篇幅很长,这里就不再赘述相关概念。(需要学习的书籍或视频的可以后台给我留言发给你)
MySQL官网:https://www.mysql.com/
以下为了方便理解,多数结合实操来展示,不仅仅就告诉你语法!
注意:
- MySQL语句不区分大小写的,但是为了与自定义命名区分,方便浏览,语句命令通常用大写。
- 每一个语句结束的标志是分号 ‘ ; ’ ,不是换行就结束了,所以长的语句可以分写成多行。
数据控制语言(DCL)
数据控制语言是一种可以对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。
- GRANT 和 REVOKE 可在几个层次上控制访问权限:
- 整个服务器,使用GRANT ALL 和 REVOKE ALL;
- 整个数据库,使用ON database.* ;
- 特定的表,使用ON database.table;
- 特定的列;
- 特定的存储过程;
- 新创建的账户没有任何权限。
- 账户用username@host形式定义。
- MySQL账户信息保存在mysql这个数据库中。
创建账户
mysql>CREATE USER newuser IDENTIFIED BY 'mypassword';
讯享网
修改账户名
讯享网mysql>UPDATE USER SET user='alluser' WHERE user='newuser';
查看权限
mysql>SHOW GRANTS FOR alluser;
授予权限
讯享网mysql>GRANT SELECT, INSERT ON *.* TO alluser;
删除权限
mysql>REVOKE SELECT, INSERT ON *.* FROM alluser;
更改密码
讯享网mysql>SET PASSWORD FOR alluser='wahahaha';
删除账户
mysql>DROP USER alluser;
数据定义语言(DDL)
主要功能是定义数据库对象(数据库、数据表、索引等)和数据库结构。
首先win+R输入cmd打开终端(Linux直接操作),如下操作打开数据库。
讯享网C:\Users\18768>start mysql # 启动MySQLC:\Users\18768>mysql -u root -p # -u 后面是你用户身份 # -p 回车会让你输入数据库密码,安装软件时设置的,别忘了Enter password: *Welcome to the MySQL monitor. Commands end with ; or \g....
显示数据库
mysql> SHOW DATABASES;# 会以如下形式出现结果+--------------------+| Database |+--------------------+| data1 || information_schema || mysql || performance_schema || python || sys |+--------------------+6 rows in set (0.05 sec)
创建数据库
讯享网# 这步就相当于你创建一个Excel文件,并给它命名了mysql> CREATE DATABASE crab; # 最后一个是你要创建的数据库名字
使用数据库
# 这步相当于你打开了这个Excel文件mysql> USE crab; # 必须有这个操作选中你要操作的数据库,才能有后续操作
创建数据表
讯享网# 这相当于你在Excel中创建了一个新表单mysql> CREATE TABLE crab1 # 最后这个就是表单名 -> (id int(12) unsigned NOT NULL, # 相当于Excel中命名第一列名为id,int代表这一列只能是整数 -> crabname varchar(255) NOT NULL, # 继续创建第二列,varchar指内容必须为字符串,not NULL指不允许为空值 -> price float, # 第三列,价格可能有小数点,是浮点数,因此用float -> number int);
展示数据表
mysql> DESC crab1;
讯享网# 然后结果是刚才创建的数据表的属性+----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| id | int unsigned | NO | | NULL | || crabname | varchar(255) | NO | | default | || price | float | YES | | NULL | || number | int | YES | | NULL | |+----------+--------------+------+-----+---------+-------+
修改数据表
数据表中添加列
mysql> ALTER TABLE crab1 # 说明要改变的数据表 -> ADD color varchar(255); # 添加新的一列color 设为字符串类型
讯享网mysql> DESC crab1; # 看看修改后变成啥样+----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| id | int unsigned | NO | | NULL | || crabname | varchar(255) | NO | | default | || price | float | YES | | NULL | || number | int | YES | | NULL | || color | varchar(255) | YES | | NULL | |+----------+--------------+------+-----+---------+-------+
数据表中删除列
mysql> ALTER TABLE crab1 -> DROP COLUMN crabname; # 删除crabname列
数据表中修改列
讯享网mysql> ALTER TABLE crab1 -> MODIFY COLUMN price double; # 修改价格列的内容为双精度浮点型double
数据表中添加主键
mysql> ALTER TABLE crab1 -> ADD PRIMARY KEY (id);
讯享网mysql> DESC crab1; # 可以看到以上操作将数据表变成了如下所示+--------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id | int unsigned | NO | PRI | NULL | || price | double | YES | | NULL | || number | int | YES | | NULL | || color | varchar(255) | YES | | NULL | |+--------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)
视图(VIEW)
- 基于SQL语句的结果集的可视化表。
- 虚拟的表,本身不包含数据,不能对其进行索引操作,别的操作和普通表一样。
- 作用是简化SQL操作,只给用户访问视图的权限,保证数据安全。
mysql>CREATE VIEW user_view AS # 创建视图 ->SELECT id, price FROM carb1; mysql>DROP VIEW user_view; # 删除视图
索引(INDEX)
- 作用:可以更加快速高效的查询数据,用户看不到。
- 注意:更新一个包含索引的表比无索引的表花费更多时间,这是由于索引本身也需要更新。
讯享网mysql> CREATE INDEX user_index ON crab1 (id); # 创建索引mysql> CREATE UNIQUE INDEX user_index ON crab1(id); # 创建唯一索引mysql> ALTER TABLE crab1 -> DROP INDEX user_index; # 删除索引
事务控制语言(TCL)
用于管理数据库中事务,用于管理由DML语句所做的更改,也允许将语句分组为逻辑事务。
注意:
- 不能回退SELECT 语句,回退SELECT语句也没意义;也不能回退CREATE和DROP语句。
- MySQL默认是隐性提交,每执行一条语句就把此语句当做一个事务提交。
- 出现START TRANSACTION 语句时,会关闭隐性提交,当COMMIT或ROLLBACK 语句执行后,事务会自动关闭,重新恢复隐性提交。
mysql> START TRANSACTION; # 开始事务mysql> INSERT INTO crab1 -> VALUES (1, 66.8, 24, 'black'); # 比如在表单crab1中添加值 mysql> SAVEPOINT process1; # 创建保留点,之后就可以回滚到这点的状态mysql> INSERT INTO crab1 -> VALUES (2, 48.8, 36, 'red'); # 添加第2个值mysql> ROLLBACK TO process1; # 回滚到保留点mysql> COMMIT; # 回滚之后提交事务,crab1中添加的第2个值就无效了Query OK, 0 rows affected (0.01 sec)mysql> SELECT * FROM crab1; # 可以查看这个表,只有第一个值添加进去了+----+-------+--------+-------+| id | price | number | color |+----+-------+--------+-------+| 1 | 66.8 | 24 | black |+----+-------+--------+-------+
数据操控语言(DML)
用于数据库操作,对数据库其中的对象和数据进行访问工作的编程语句,核心指令为INSERT、DELETE、UPDATE、SELECT,即增删改查,合称CRUD。
讯享网mysql> USE python; # 在这里我随便选用一个已经创建的数据库python做示范mysql> SHOW TABLES; # 显示此数库里面所有的数据表+------------------+| Tables_in_python |+------------------+| order_tb || user_tb |+------------------+mysql> DESC user_tb; # 查看一下此数据表属性+---------+---------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+---------------+------+-----+---------+----------------+| user_id | int | NO | PRI | NULL | auto_increment || name | varchar(6816) | YES | | NULL | || pass | varchar(5718) | YES | | NULL | || gender | varchar(255) | YES | | NULL | |+---------+---------------+------+-----+---------+----------------+
插入数据
mysql> INSERT INTO user_tb # 插入完整的行 -> VALUES(10, 'admin', '', 'male'); # 对应上面Feld列的四个属性值
讯享网mysql> INSERT INTO user_tb(user_id, name, pass) # 插入行的一部分 -> VALUES(6, 'david', '');
查询数据
--------------------查询所有值# 以下是我插入多次数据后结果mysql> SELECT * FROM user_tb; # 可以看到刚才插入的值,*代表查看所有,也可以写某一个列名,只查看某一列值+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 1 | fsd | | female || 2 | faie | | male || 3 | fasf | | male || 4 | tgrh | | female || 5 | ankeh | | female || 6 | david | | NULL || 10 | admin | | male |+---------+-------+---------+--------+--------------------查询不同的值mysql> SELECT DISTINCT user_id FROM user_tb; --------------------限制查询结果mysql> SELECT * FROM user_tb LIMIT 5;+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 1 | fsd | | female || 2 | faie | | male || 3 | fasf | | male || 4 | tgrh | | female || 5 | ankeh | | female |+---------+-------+---------+--------+mysql> SELECT * FROM user_tb LIMIT 2, 4; # LIMIT后面第一个参数为起始行,第二个为起始行后的行数+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 3 | fasf | | male || 4 | tgrh | | female || 5 | ankeh | | female || 6 | david | | NULL |+---------+-------+---------+--------+
更新数据
讯享网mysql> UPDATE user_tb -> SET user_id=7, name='gala', pass='',gender='male' # 设置要改成什么样 -> WHERE user_id=6; # 更改id为6的那一行的数据值
删除数据
mysql> DELETE FROM user_tb -> WHERE user_id=10;
经过以上操作,查看一下表的变化:
讯享网mysql> SELECT * FROM user_tb;+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 1 | fsd | | female || 2 | faie | | male || 3 | fasf | | male || 4 | tgrh | | female || 5 | ankeh | | female || 7 | gala | | male |+---------+-------+---------+--------+
子查询
- 子查询可以嵌套在SELECT、INSERT、UPDATE、DELETE语句或者另一个子查询中。
- 可以使用比较运算符或多行运算符IN、ANY、ALL。
- 子查询必须被圆括号()括起来。
- 子查询在其父查询之前执行,以便将结果传给父查询。
由于要用到另外一个表来示范,这里面的user_id属性与前面那个表有连接关系。mysql> DESC order_tb;+-------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+----------------+| order_id | int | NO | PRI | NULL | auto_increment || item_name | varchar(366) | YES | | NULL | || item_price | double | YES | | NULL | || item_number | double | YES | | NULL | || user_id | int | YES | MUL | NULL | |+-------------+--------------+------+-----+---------+----------------+mysql> select * from order_tb;+----------+-----------+------------+-------------+---------+| order_id | item_name | item_price | item_number | user_id |+----------+-----------+------------+-------------+---------+| 1 | lego | 18.3 | 66 | 2 || 2 | lgalo | 26.3 | 36 | 1 |+----------+-----------+------------+-------------+---------+
讯享网mysql> SELECT user_id FROM user_tb # 这句是父查询 -> WHERE user_id IN ->(SELECT user_id FROM order_tb WHERE order_id=2); # 这句就是子查询+---------+| user_id |+---------+| 1 |+---------+
WHERE
- WHERE 子句用于过滤记录,即缩小访问数据的范围。

- WHERE 后跟一个返回 true 或 false 的条件。
- WHERE 可以与 SELECT,UPDATE 和 DELETE 一起使用。
- 可以在 WHERE 子句中使用的操作符
| 运算符 | 描述 |
|---|---|
| = | 等于 |
| <> | 不等于。注释:在 SQL 的一些版本中,该操作符可被写成 != |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索某种模式 |
| IN | 指定针对某个列的多个可能值 |
IN 和 BETWEEN
- IN 操作符在 WHERE 子句中使用,作用是在指定的几个特定值中任选一个值。
- BETWEEN 操作符在 WHERE 子句中使用,作用是选取介于某个范围内的值。
AND、OR、NOT
- AND、OR、NOT 是用于对过滤条件的逻辑处理指令。
- AND 优先级高于 OR,为了明确处理顺序,可以使用 ()。
- AND 操作符表示左右条件都要满足。
- OR 操作符表示左右条件满足任意一个即可。
- NOT 操作符用于否定一个条件。
LIKE
- LIKE 操作符在 WHERE 子句中使用,作用是确定字符串是否匹配模式。
- 只有字段是文本值时才使用 LIKE。
- LIKE 支持两个通配符匹配选项:% 和 _。
- 不要滥用通配符,通配符位于开头处匹配会非常慢。
- % 表示任何字符出现任意次数。
- _ 表示任何字符出现一次。
mysql> SELECT * FROM user_tb -> WHERE user_id IN (2, 4, 6);+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 2 | faie | | male || 4 | tgrh | | female || 6 | falar | | female |+---------+-------+---------+--------+mysql> SELECT * FROM user_tb -> WHERE user_id BETWEEN 3 and 5;+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 3 | fasf | | male || 4 | tgrh | | female || 5 | ankeh | | female |+---------+-------+---------+--------+mysql> SELECT * FROM user_tb -> WHERE name LIKE '%fasf%';+---------+------+--------+--------+| user_id | name | pass | gender |+---------+------+--------+--------+| 3 | fasf | | male |+---------+------+--------+--------+
连接(JOIN)
- 如果一个 JOIN 至少有一个公共字段并且它们之间存在关系,则该 JOIN 可以在两个或多个表上工作。
- 连接用于连接多个表,使用 JOIN 关键字,并且条件语句使用 ON 而不是 WHERE。
- JOIN 保持基表(结构和数据)不变。
- JOIN 有两种连接类型:内连接和外连接。
- 内连接又称等值连接,使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。
- 自连接可以看成内连接的一种,只是连接的表是自身而已。
- 自然连接是把同名列通过 = 测试连接起来的,同名列可以有多个。
- 内连接 vs 自然连接
- 内连接提供连接的列,而自然连接自动连接所有同名列。
- 外连接返回一个表中的所有行,并且仅返回来自次表中满足连接条件的那些行,即两个表中的列是相等的。外连接分为左外连接、右外连接。
- 左外连接就是保留左表没有关联的行。
- 右外连接就是保留右表没有关联的行。
- 连接 vs 子查询
- 连接可以替换子查询,并且比子查询的效率一般会更快。
讯享网----------------内连接mysql> SELECT * FROM user_tb INNER JOIN order_tb -> ON user_tb.user_id = order_tb.user_id;+---------+------+--------+--------+----------+-----------+------------+-------------+---------+| user_id | name | pass | gender | order_id | item_name | item_price | item_number | user_id |+---------+------+--------+--------+----------+-----------+------------+-------------+---------+| 2 | faie | | male | 1 | lego | 18.3 | 66 | 2 || 1 | fsd | | female | 2 | lgalo | 26.3 | 36 | 1 |+---------+------+--------+--------+----------+-----------+------------+-------------+---------+-----------------自然连接mysql> SELECT * FROM user_tb NATURAL JOIN order_tb;+---------+------+--------+--------+----------+-----------+------------+-------------+| user_id | name | pass | gender | order_id | item_name | item_price | item_number |+---------+------+--------+--------+----------+-----------+------------+-------------+| 2 | faie | | male | 1 | lego | 18.3 | 66 || 1 | fsd | | female | 2 | lgalo | 26.3 | 36 |+---------+------+--------+--------+----------+-----------+------------+-------------+------------------左外连接想象把两个有连接的表并排放,以左边表为基准来查询mysql> SELECT user_tb.user_id, order_tb.order_id -> FROM user_tb LEFT JOIN order_tb -> ON user_tb.user_id = order_tb.user_id;+---------+----------+| user_id | order_id |+---------+----------+| 1 | 2 || 2 | 1 || 3 | NULL || 4 | NULL || 5 | NULL || 6 | NULL || 7 | NULL |+---------+----------+------------------右外连接mysql> SELECT user_tb.user_id, order_tb.order_id -> FROM user_tb RIGHT JOIN order_tb -> ON user_tb.user_id = order_tb.user_id;+---------+----------+| user_id | order_id |+---------+----------+| 1 | 2 || 2 | 1 |+---------+----------+
组合(UNION)
- UNION 运算符将两个或更多查询的结果组合起来,并生成一个结果集,其中包含来自 UNION 中参与查询的提取行。
- UNION 基本规则
- 所有查询的列数和列顺序必须相同。
- 每个查询中涉及表的列的数据类型必须相同或兼容。
- 通常返回的列名取自第一个查询。
- 默认会去除相同行,如果需要保留相同行,使用 UNION ALL。
- 只能包含一个 ORDER BY 子句,并且必须位于语句的最后。
- 应用场景
- 在一个查询中从不同的表返回结构数据。
- 对一个表执行多个查询,按一个查询返回数据。
mysql>SELECT name -> FROM user_tb -> WHERE gender IN ('male') -> UNION -> SELECT name -> FROM user_tb -> WHERE name = 'falar';
函数
文本处理
| 函数 | 说明 |
|---|---|
LEFT()、RIGHT() |
左边或者右边的字符 |
LOWER()、UPPER() |
转换为小写或者大写 |
LTRIM()、RTIM() |
去除左边或者右边的空格 |
LENGTH() |
长度 |
SOUNDEX() |
转换为语音值 |
时间和日期
| 函 数 | 说 明 |
|---|---|
AddDate() |
增加一个日期(天、周等) |
AddTime() |
增加一个时间(时、分等) |
CurDate() |
返回当前日期 |
CurTime() |
返回当前时间 |
Date() |
返回日期时间的日期部分 |
DateDiff() |
计算两个日期之差 |
Date_Add() |
高度灵活的日期运算函数 |
Date_Format() |
返回一个格式化的日期或时间串 |
Day() |
返回一个日期的天数部分 |
DayOfWeek() |
对于一个日期,返回对应的星期几 |
Hour() |
返回一个时间的小时部分 |
Minute() |
返回一个时间的分钟部分 |
Month() |
返回一个日期的月份部分 |
Now() |
返回当前日期和时间 |
Second() |
返回一个时间的秒部分 |
Time() |
返回一个日期时间的时间部分 |
Year() |
返回一个日期的年份部分 |
讯享网mysql> SELECT now();+---------------------+| now() |+---------------------+| 2020-09-02 20:14:22 |+---------------------+mysql> SELECT curtime();+-----------+| curtime() |+-----------+| 20:15:35 |+-----------+mysql> SELECT second(now());+---------------+| second(now()) |+---------------+| 41 |+---------------+
数值处理函数
| 函数 | 说明 |
|---|---|
| SIN() | 正弦 |
| COS() | 余弦 |
| TAN() | 正切 |
| ABS() | 绝对值 |
| SQRT() | 平方根 |
| MOD() | 余数 |
| EXP() | 指数 |
| PI() | 圆周率 |
| RAND() | 随机数 |
| 函 数 | 说 明 |
|---|---|
AVG() |
返回某列的平均值 |
COUNT() |
返回某列的行数 |
MAX() |
返回某列的最大值 |
MIN() |
返回某列的最小值 |
SUM() |
返回某列值之和 |
mysql> SELECT count(item_number) FROM order_tb;+--------------------+| count(item_number) |+--------------------+| 3 |+--------------------+
排序和分组
ORDER BY
- ORDER BY 用于对结果集进行排序。
- ASC :升序(默认)
- DESC :降序
- 可以按多个列进行排序,并且为每个列指定不同的排序方式
指定多个列的排序方向
GROUP BY
- GROUP BY 子句将记录分组到汇总行中。
- GROUP BY 为每个组返回一个记录。
- GROUP BY 通常还涉及聚合:COUNT,MAX,SUM,AVG 等。
- GROUP BY 可以按一列或多列进行分组。
- GROUP BY 按分组字段进行排序后,ORDER BY 可以以汇总字段来进行排序。
分组
分组后排序
HAVING
- HAVING 用于对汇总的 GROUP BY 结果进行过滤。
- HAVING 要求存在一个 GROUP BY 子句。
- WHERE 和 HAVING 可以在相同的查询中。
- HAVING vs WHERE
- WHERE 和 HAVING 都是用于过滤。
- HAVING 适用于汇总的组记录;而 WHERE 适用于单个记录。
讯享网mysql> SELECT * FROM order_tb;+----------+-----------+------------+-------------+---------+| order_id | item_name | item_price | item_number | user_id |+----------+-----------+------------+-------------+---------+| 1 | lego | 18.3 | 66 | 2 || 2 | lgalo | 26.3 | 36 | 1 || 3 | apple | 12.6 | 111 | 4 |+----------+-----------+------------+-------------+---------+mysql> SELECT * FROM order_tb -> ORDER BY item_price DESC, user_id ASC;+----------+-----------+------------+-------------+---------+| order_id | item_name | item_price | item_number | user_id |+----------+-----------+------------+-------------+---------+| 2 | lgalo | 26.3 | 36 | 1 || 1 | lego | 18.3 | 66 | 2 || 3 | apple | 12.6 | 111 | 4 |+----------+-----------+------------+-------------+---------+mysql> SELECT item_name, count(item_number) AS sum_number -> FROM order_tb GROUP BY item_name;+-----------+------------+| item_name | sum_number |+-----------+------------+| lego | 1 || lgalo | 1 || apple | 1 |+-----------+------------+mysql> SELECT * FROM order_tb;+----------+-----------+------------+-------------+---------+| order_id | item_name | item_price | item_number | user_id |+----------+-----------+------------+-------------+---------+| 1 | lego | 18.3 | 66 | 2 || 2 | lgalo | 26.3 | 36 | 1 || 3 | apple | 12.6 | 111 | 4 || 4 | banana | 8.8 | NULL | 6 |+----------+-----------+------------+-------------+---------+mysql> SELECT item_name, count(*) AS num -> FROM order_tb -> WHERE item_number IS NOT NULL -> GROUP BY item_name -> HAVING count(*) >= 1;+-----------+-----+| item_name | num |+-----------+-----+| lego | 1 || lgalo | 1 || apple | 1 |+-----------+-----+
欢迎长按下方二维码关注公众号

麻烦右下角点个在看支持河蟹,蟹蟹大家!


版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/112513.html