虽然关注我公众号的大多数(至少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', '526984', 'male'); # 对应上面Feld列的四个属性值
mysql> INSERT INTO user_tb(user_id, name, pass) # 插入行的一部分 -> VALUES(6, 'david', '6513894');
查询数据
--------------------查询所有值# 以下是我插入多次数据后结果mysql> SELECT * FROM user_tb; # 可以看到刚才插入的值,*代表查看所有,也可以写某一个列名,只查看某一列值+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 1 | fsd | 498651 | female || 2 | faie | 984135 | male || 3 | fasf | 879163 | male || 4 | tgrh | 4651342 | female || 5 | ankeh | 464552 | female || 6 | david | 6513894 | NULL || 10 | admin | 526984 | male |+---------+-------+---------+--------+--------------------查询不同的值mysql> SELECT DISTINCT user_id FROM user_tb; --------------------限制查询结果mysql> SELECT * FROM user_tb LIMIT 5;+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 1 | fsd | 498651 | female || 2 | faie | 984135 | male || 3 | fasf | 879163 | male || 4 | tgrh | 4651342 | female || 5 | ankeh | 464552 | female |+---------+-------+---------+--------+mysql> SELECT * FROM user_tb LIMIT 2, 4; # LIMIT后面第一个参数为起始行,第二个为起始行后的行数+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 3 | fasf | 879163 | male || 4 | tgrh | 4651342 | female || 5 | ankeh | 464552 | female || 6 | david | 6513894 | NULL |+---------+-------+---------+--------+
更新数据
mysql> UPDATE user_tb -> SET user_id=7, name='gala', pass='2315665',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 | 498651 | female || 2 | faie | 984135 | male || 3 | fasf | 879163 | male || 4 | tgrh | 4651342 | female || 5 | ankeh | 464552 | female || 7 | gala | 2315665 | 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 | 984135 | male || 4 | tgrh | 4651342 | female || 6 | falar | 981523 | female |+---------+-------+---------+--------+mysql> SELECT * FROM user_tb -> WHERE user_id BETWEEN 3 and 5;+---------+-------+---------+--------+| user_id | name | pass | gender |+---------+-------+---------+--------+| 3 | fasf | 879163 | male || 4 | tgrh | 4651342 | female || 5 | ankeh | 464552 | female |+---------+-------+---------+--------+mysql> SELECT * FROM user_tb -> WHERE name LIKE '%fasf%';+---------+------+--------+--------+| user_id | name | pass | gender |+---------+------+--------+--------+| 3 | fasf | 879163 | 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 | 984135 | male | 1 | lego | 18.3 | 66 | 2 || 1 | fsd | 498651 | 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 | 984135 | male | 1 | lego | 18.3 | 66 || 1 | fsd | 498651 | 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 |+-----------+-----+
欢迎长按下方二维码关注公众号
麻烦右下角点个在看支持河蟹,蟹蟹大家!