关于mysql:从零开始的常用MySQL语句练习大全

先说一些废话

很多时候深刻学习诚然很重要，然而想要写下一篇给老手都能看得懂看的很香，并且老鸟能够查漏补缺的的练习博客，还是挺有难度，
所以明天尝试写一些对于MySQL的语句练习大全，供想要从零开始练习MySQL的老手们去学习。

须要留神的是写代码是一种脑力和操作并行的技术，倡议没怎么写过SQL的人肯定要先从命令行的语句练习开始，这样才可能更好的加深印象！！！

所有的语句我会写一些根本的正文，须要深刻学习的童鞋能够参考单个知识点持续理解。

当然明天列出的所有语句都有测试过，然而不确保没有手误打错的时候，如果有什么谬误的中央请在评论区指出，非常感谢！！！

提示信息：本文测试所应用的MySQL版本是5.7.26

其余废话不多说间接上代码

连贯MySQL

# 用户名和明码要紧跟-u-p不能有空格mysql -u用户名 -p明码# 如果不想明文显示明码，执行命令后会提醒输出明码，并且不会明文显示明码mysql -u 用户名 -p

数据库

# 查看所有的数据库SHOW DATABASES;# 创立一个数据库CREATE DATABASE test;# 删除一个数据库DROP DATABASE test;# 应用数据库USE test;

表

# 查看所有的表SHOW TABLES;# =======================================================# 知识点提醒！！！# 主键束缚（PRIMARY KEY）：没有明确的概念定义，是唯一性索引的一种，不能反复，一个表只能有一个主键# 主动减少（AUTO_INCREMENT）：主动减少（须要和主键PRIMARY KEY一起应用）# 非空束缚（NOT NULL）：要求被装璜的字段非空# 外键束缚（FOREIGN KEY）：用来在表与表的数据之间建设链接，它能够是一列或者多列  # 举个栗子帮忙了解：班级表，10个班级，年级表，6个年级，每个班级只能所属1个年级，# 且必须是这6个年级中的1个，这个时候班级表就须要用年级表来建设外键，确保数据统一且残缺  # 惟一束缚（UNIQUE KEY）：指所有记录中的字段的值不能反复呈现，能够联结非空UNIQUE(字段1，字段2)  # 数据类型，务必深刻理解，对数据库的优化十分重要  # AS 别名或者连贯语句的操作符# =======================================================# 创立一个表，领有主键CREATE TABLE test(id INT, name VARCHAR(10), PRIMARY KEY (id));# 创立另一个表，领有主键，并蕴含前一个表的外键束缚，以及惟一束缚  CREATE TABLE test_key(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES test(id), UNIQUE (name));# 间接将查问后果导入或复制到，一个新创建的表CREATE TABLE test_copy SELECT * FROM test;CREATE TABLE test_copy_as AS SELECT * FROM test;# 将一个已存在表的数据结构克隆到，一个新创建的表CREATE TABLE test_clone LIKE test;# 创立一个长期表，各种形式  # 长期表将在你连贯MySQL期间存在，当断开连接时，MySQL将主动删除表并开释所用的空间，也可手动删除。CREATE TEMPORARY TABLE test_tem(id INT, name VARCHAR(10));CREATE TEMPORARY TABLE test_tem_copy SELECT * FROM test;# 删除一个存在表DROP TABLE IF EXISTS test;# 更改存在表的名称ALTER TABLE test RENAME test_rename;RENAME TABLE test_rename TO test;# 查看表的构造# 以下五条语句成果雷同，举荐第一条，因为简略  DESC test;DESCRIBE test;SHOW COLUMNS IN test;SHOW COLUMNS FROM test;EXPLAIN test;# 查看表的创立语句SHOW CREATE TABLE test;

表构造

# 增加表字段ALTER TABLE test ADD age VARCHAR(2);# 删除表字段ALTER TABLE test DROP age;# 更改表字段和表字段属性ALTER TABLE test CHANGE age age_change INT;# 只更改表字段属性ALTER TABLE test MODIFY age VARCHAR(7);# 查问所有表信息SHOW TABLE STATUS;

表数据

# 减少表数据INSERT INTO test VALUES (1, 'alpha', '24'), (2, 'beta', '25'), (3, 'delta', '27');# =======================================================# 因为test表设置了主键无奈用上面这种形式减少，前两个语句先重建一张表CREATE TABLE test_insert(id INT, name VARCHAR(8));INSERT INTO test_insert VALUES (1, 'alpha'), (2, 'beta'); # =======================================================# 减少查问之后的表数据  INSERT INTO test_insert SELECT * FROM test_insert;# 删除表数据DELETE FROM test_insert WHERE id = 2;# 更改表数据UPDATE test_insert SET name = 'delta' WHERE id = 1;# =======================================================# 为了上面的查问语句可能更直观一些咱们再插入一些数据并把age_change的表字段改为ageINSERT INTO test VALUES (10, 'gamma', '39'), (6, 'zeta', '30'), (19, 'theta', '10'), (8, 'eta', '20'), (46, 'zeta', '11'),  (56, 'zeta', '9'), (66, 'zeta', '3');ALTER TABLE test CHANGE age_change age VARCHAR(20);# =======================================================# =======================================================# 知识点提醒！！！# SELECT DISTINCT * FROM '表名' WHERE '限度条件' GROUP BY '分组根据' HAVING '过滤条件' # ORDER BY LIMIT '展现条数' OFFSET '跳过的条数'# 以上关键字应用程序不能谬误，否则会产生错误信息# DISTINCT 去重，留神如果是多个表字段去重，只有每个表字段都雷同才会认为雷同# * 代表通配符，会返回所有字段数据# WHERE 语句用来蕴含查问条件，对原始记录过滤# HAVING 语句也是用来蕴含查问条件，然而HAVING是对WHERE查问分组之后的记录持续过滤# ORDER BY 排序默认正序也就是升序，DESC示意反序也就是降序# LIMIT 属性来设定返回的记录数，个别用于列表的分页# OFFSET 属性来设定跳过的返回的记录数，个别配合LIMIT# =======================================================# 查找表数据SELECT * FROM test;# 查问去除反复数据之后的表数据SELECT DISTINCT name FROM test;# 依据表字段查找表数据SELECT * FROM test WHERE name = 'zeta';# 查找name为zeta并且id大于30的表数据SELECT * FROM test WHERE name = 'zeta' HAVING id > 30;# 依据id分组查问age小于20的表数据SELECT * FROM test GROUP BY id HAVING age < 20;# 查找依据name排序之后的表数据SELECT * FROM test ORDER BY name;# 查找依据name反序排序之后的表数据SELECT * FROM test ORDER BY name DESC; # =======================================================# 留神上面这个排序，如果排序的第一个字段所有值都不同，那么第二列排序就没有意义了，# 所以咱们之前退出了一些name雷同的值，所以能够咱们能够看下zeta字段的age排序# =======================================================# 查找先依据name排序，再依据age排序之后的表数据SELECT * FROM test ORDER BY name, age;# 查找先依据age反序，再依据name排序之后，年龄最大的前三位的表数据SELECT * FROM test ORDER BY age DESC, name LIMIT 3;# 查找先依据age反序，再依据name排序之后，年龄最大的前三位，并跳过第一条的表数据SELECT * FROM test ORDER BY age DESC, name LIMIT 3 OFFSET 1;SELECT * FROM test ORDER BY age DESC, name LIMIT 1,3;# 应用正则查问name表字段中蕴含字母g的表数据SELECT * FROM test WHERE name regexp '.*[g]+.*';

表数据 —— 连贯查问

# =======================================================# 为了不便大家更为直观的看到连贯查问的成果，我再从新建一个用于连贯的表CREATE TABLE test_join(id INT, join_name VARCHAR(20), join_desc VARCHAR(100));INSERT INTO test_join VALUES (10, 'join_a', 'desc_a'), (11, 'join_b', 'desc_b'), (10, 'gamma', 'join_gamma');# =======================================================# =======================================================# 解释一下上面的内连贯查问为什么会有三种# 第一种为不设置别名的写法# 第二种为简写AS设置别名的写法# 第三种为有AS关键字设置别名的写法# 后续均应用第二种写法# =======================================================# 内连贯查问# 留神JOIN连贯默认应用内连贯SELECT * FROM test INNER JOIN test_join ON test.id = test_join.id;SELECT * FROM test m INNER JOIN test_join ON m.id = n.id;SELECT * FROM test AS m INNER JOIN test_inert AS n ON m.id = n.id;# 左外连贯查问SELECT * FROM test m LEFT JOIN test_join n ON m.id = n.id;# 左外连贯查问，左表独有数据SELECT * FROM test m LEDT JOIN test_join n ON m.id = n.id WHERE n.id IS NULL;# 右外连贯查问SELECT * FROM test m RIHGT JOIN test_join n ON m.id = n.id;# 右外连贯查问，右表独有数据SELECT * FROM test m RIGHT JOIN test_join n ON m.id = n.id WHERE m.id IS NULL;# 穿插连贯查问，又叫笛卡尔连贯SELECT * FROM test CROSS JOIN test_join;SELECT * FROM test,test_join;# 全连贯查问  # UNION默认不返回反复的数据，UNION ALL则会返回反复的数据SELECT id,name FROM test UNION SELECT id,join_name FROM test_join;SELECT id,name FROM test UNION ALL SELECT id,join_name FROM test_join;

键

# =======================================================# 这里是指定主键束缚名的增加和删除形式，然而主键只能有一个，所以感觉主键指定名称貌似有点多余？ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);ALTER TABLE test DROP PRIMARY KEY `pk_test`;# =======================================================# 增加主键ALTER TABLE test ADD PAIMARY KEY (id);# 删除主键ALTER TABLE test DROP PRIMARY KEY;# =======================================================# 知识点提醒！！！# 不指定外键束缚名，会主动生成一个，然而不晓得怎么查出来，# 有人说用SHOW CREATE TABLE 表名 能够查问进去然而我测试了一下不行# 这个问题先放着，前期我来填坑# =======================================================# =======================================================# 外键增删改查，都要通过外键的束缚名，创立时尽量留神写外键的束缚名，# 所以不倡议上面这种不指定外键束缚名的形式ALTER TABLE test ADD FOREIGN KEY (id) REFERENCES test_join(id);# =======================================================# =======================================================# 建两个表用于测试CREATE TABLE test_key(id INT, key_name VARCHAR(20), PRIMARY KEY(id));CREATE TABLE test_foreign_key(id INT, foreign_key_name VARCHAR(20), PRIMARY KEY(id));# =======================================================# 增加外键ALTER TABLE test_key ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES test_foreign_key(id);# 删除外键ALTER TABLE test_key DROP FOREIGN KEY `fk_id`;# 批改外键ALTER TABLE test_key DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id_new FOREIGN KEY (id) REFERENCES test_foreign_key(id);# 增加惟一键# 这种不指定惟一键束缚名的形式，能够用SHOW CREATE TABLE 查看束缚名ALTER TABLE test_key ADD UNIQUE(key_name);# 增加惟一键，指定键名ALTER TABLE test_key ADD UNIQUE un_name (name);ALTER TABLE test_key ADD UNIQUE INDEX un_name (name);ALTER TABLE test_key ADD CONSTRAINT un_name UNIQUE (name);CREATE UNIQUE INDEX un_name ON test_key(name);# 删除惟一键DROP INDEX un_name ON test_key;# 增加索引ALTER TABLE test_key ADD INDEX (key_name);# 增加索引，指定索引名ALTER TABLE test_key ADD INDEX in_name (key_name);CREATE INDEX in_name ON test_key(key_name);# 删除索引DROP INDEX in_name ON test_key;

函数

# =======================================================# 聚合函数# =======================================================# 求总数SELECT count(id) AS total FROM test;# 求和SELECT sum(age) AS total_age FROM test;# 求平均值SELECT avg(age) AS avg_age FROM test;# 求最大值SELECT max(age) AS max_age FROM test;# 求最小值SELECT min(age) AS min_age FROM test;# =======================================================# 数学函数# =======================================================# 绝对值：8SELECT abs(-8);# 二进制：1010，八进制：12，十六进制：ASELECT bin(10), oct(10), hex(10);# 圆周率：3.141593SELECT pi();# 大于x的最小整数值：6SELECT ceil(5.5);# 小于x的最大整数值：5SELECT floor(5.5);# 返回汇合中最大的值：86SELECT greatest(13, 21, 34, 41, 55, 69, 72, 86);# 返回汇合中最小的值：13SELECT least(13, 21, 34, 41, 55, 69, 72, 86);# 余数：3SELECT mod(1023, 10);# 返回0-1内的随机值，每次不一样SELECT rand();# 提供一个参数生成一个指定值SELECT rand(9);  # 0.406868412538309SELECT rand('test123');  # 0.15522042769493574# 四舍五入：1023SELECT round(1023.1023);# 四舍五入，保留三位数：1023.102SELECT round(1023.1023, 3);# 四舍五入整数位：SELECT round(1023.1023, -1);  # 1020SELECT round(1025.1025, -1);  # 1030，留神和truncate的区别# 截短为3位小数：1023.102SELECT truncate(1023.1023, 3);# 截短为-1位小数：1020SELECT truncate(1023.1023, -1);  # 1020SELECT truncate(1025.1025, -1);  # 1020，留神和round的区别# 符号的值的符号(正数，零或正)对应-1，0或1SELECT sign(-6);  # -1SELECT sign(0);  # 0SELECT sign(6);  # 1# 平方根：10SELECT sqrt(10);# =======================================================# 字符串函数# =======================================================# 连贯字符串 'fx67ll'SELECT concat('f', 'x', '67', 'll');# 用分隔符连贯字符串 'fx67ll'，留神如果分隔符为NULL，则后果为NULLSELECT concat_ws('-', 'fx', '6', '7', 'l', 'l');  # fx-6-7-l-lSELECT concat_ws(NULL, 'fx', '6', '7', 'l', 'l');  # NULL# 将字符串 'fx67ll' 从3地位开始的2个字符替换为 '78'SELECT insert('fx67ll', 3, 2, '78');  # fx78ll# 返回字符串 'fx67ll' 右边的3个字符：fx6SELECT left('fx67ll', 3);# 返回字符串 'fx67ll' 左边的4个字符: 67llSELECT right('fx67ll', 4);# 返回字符串 'fx67ll' 第3个字符之后的子字符串：67llSELECT substring('fx67ll', 3);# 返回字符串 'fx67ll' 倒数第3个字符之后的子字符串：7llSELECT substring('fx67ll', -3);# 返回字符串 'fx67ll' 第3个字符之后的2个字符：67SELECT substring('fx67ll', 3, 2);# 切割字符串 ' fx67ll ' 两边的空字符，留神字符串左右有空格：'fx67ll'SELECT trim(' fx67ll ');# 切割字符串 ' fx67ll ' 右边的空字符：'fx67ll 'SELECT ltrim(' fx67ll ');# 切割字符串 ' fx67ll ' 左边的字符串：' fx67ll'SELECT rtrim(' fx67ll ');# 反复字符 'fx67ll' 三次：fx67llfx67llfx67llSELECT repeat('fx67ll', 3);# 对字符串 'fx67ll' 进行反向排序：ll76xfSELECT reverse('fx67ll');# 返回字符串的长度：6SELECT length('fx67ll');# 对字符串进行大小写解决，大小写各两种形式SELECT upper('FX67ll');  # FX67LLSELECT lower('fx67LL');  # fx67llSELECT ucase('fX67Ll');  # FX67LLSELECT lcase('Fx67lL');  # fx67ll# 返回 'f' 在 'fx67ll' 中的第一个地位：1SELECT position('f' IN 'fx67ll');# 返回 '1' 在 'fx67ll' 中的第一个地位，不存在返回0：0SELECT position('1' IN 'fx67ll');# 比拟字符串，第一个参数小于第二个返回正数，否则返回负数，相等返回0SELECT strcmp('abc', 'abd');  # -1SELECT strcmp('abc', 'abb');  # 1SELECT strcmp('abc', 'abc');  # 0# =======================================================# 工夫函数# =======================================================# 返回以后日期，工夫，日期工夫SELECT current_date, current_time, now();# 返回以后工夫的时，分，秒SELECT hour(current_time), minute(current_time), second(current_time);# 返回以后日期的年，月，日SELECT year(current_date), month(current_date), day(current_date);# 返回以后日期的季度SELECT quarter(current_date);# 返回以后月份的名称，以后星期的名称SELECT monthname(current_date), dayname(current_date);# 返回以后日在星期的天数，以后日在月的天数，以后日在年的天数SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);# =======================================================# 控制流函数# =======================================================    # IF判断：1SELECT IF(2>1, '1', '0')  # 1# IFNULL判断# 判断第一个表达式是否为NULL，如果为NULL则返回第二个参数的值，否则返回第一个参数的值SELECT IFNULL(NULL, 1);  # 1SELECT IFNULL('fx67ll', 0);  # fx67ll# ISNULL判断# 承受1个参数，并测试该参数是否为NULL，如果参数为NULL，则返回1，否则返回0SELECT ISNULL(1);  # 0SELECT ISNULL(1/0);  # 1# NULLIF判断# 承受2个参数，如果第1个参数等于第2个参数，则返回NULL，否则返回第1个参数SELECT NULLIF('fx67ll', 'fx67ll');  # NULLSELECT NULLIF('fx67ll', 'll76xf');  # fx67ll# NULLIF相似于上面的CASE表达式CASE WHEN expression_1 = expression_2   THEN NULLELSE   expression_1END;# CASE判断：secondSELECT CASE 2    WHEN 1 THEN 'first'    WHEN 2 THEN 'second'    WHEN 3 THEN 'third'    ELSE 'other'    END;# =======================================================# 零碎信息函数# =======================================================# 显示以后数据库名SELECT database();# 显示以后用户idSELECT connection_id();# 显示以后用户SELECT user();# 显示以后mysql版本SELECT version();# 返回上次查问的检索行数SELECT found_rows();

视图

# 创立视图CREATE VIEW v AS SELECT id,name FROM test;CREATE VIEW sv(sid,sname) AS SELECT id,name FROM test;# =======================================================# 知识点提醒！！！# MySQL视图规定FROM关键字前面不能蕴含子查问# 批改了视图对基表数据会有影响，反之，批改了基表数据对视图也会有影响# =======================================================# 查看创立视图语句SHOW CREATE VIEW v;# 查看视图构造DESC v;DESCRIBE v;SHOW COLUMNS IN v;SHOW COLUMNS FROM v;EXPLAIN v;# 查看视图数据SELECT * FROM v;# 批改视图CREATE OR REPLACE VIEW v AS SELECT name,age FROM test;ALTER VIEW v AS SELECT age FROM test;# 删除视图DROP v IF EXISTS v;# =======================================================# 知识点提醒！！！# 应用视图的益处：# 1. 平安，只查问须要的数据  # 2. 性能，防止过多应用JOIN查问  # 3. 灵便，放弃应用一些过旧的表  # =======================================================

存储过程

# 因为存储过程比拟灵便，这里不写具体示例，次要写一些定义语法  # 申明语句结束符DELIMITER $$# =======================================================# 知识点提醒！！！# 下面的($$)结束符能够本人定义，MySQL中默认是;   # =======================================================# 申明存储过程CREATE PROCEDURE 存储过程名( [IN | OUT | INOUT] 参数 数据类型)# =======================================================# 知识点提醒！！！# IN 输出参数  OUT 输入参数  INOUT 输入输出参数     # =======================================================# 申明存储函数CREATE FUNCTION 存储函数名(参数 数据类型)# 申明存储过程开始和完结BEGIN ...... END# 申明变量DECLARE 变量名 变量类型 默认值# 变量赋值SET @变量名 = 值# 调用存储过程CALL 存储过程名(传参)# 正文-- 正文体# 查看存储过程的具体SHOW CREATE PROCEDURE 数据库.存储过程名  # 批改存储过程ALTER PROCEDURE# 删除存储过程DROP PROCEDURE# =======================================================# 知识点提醒！！！# 把过多的业务逻辑写在存储过程汇中不利于保护治理，# 除了个别对业务性能要求较高的业务，其余的必要性不是很大    # =======================================================

备份还原

# 备份命令# 选项参考下方列表内容mysqldump [选项] 数据库名 [表名] > 脚本名mysqldump [选项] --数据库名 [选项 表名] > 脚本名# 备份所有数据库命令mysqldump [选项] --all-databases [选项]  > 脚本名# =======================================================# 知识点提醒！！！# 下方在导入备份数据库前，db_name如果没有，是须要创立的，# 而且与db_name.db中数据库名是一样的才能够导入# =======================================================# 零碎行还原命令mysqladmin -uroot -p create db_name mysql -uroot -p  db_name < /backup/mysqldump/db_name.db# source还原命令mysql > use db_namemysql > source /backup/mysqldump/db_name.db# =======================================================# 知识点提醒！！！# 不同的业务场景与规模有不同的备份策略  # 双机热备：通过日志文件来传输服务器上的数据变动，主服务器上数据被更新时触发，# 传输相应的日志文件到从服务器，并通过日志文件，作出相应的操作  # =======================================================

备份命令选项阐明

用户

用户权限是十分敏感的一类操作，须要十分小心的应用，所以这里只介绍批改明码的罕用操作，其余操作请在残缺理解过MySQL权限零碎之后再作尝试
您能够参考这篇文章 -- Mysql用户与权限操作系统学习MySQL用户与权限

# 批改用户明码UPDATE USER SET authentication_string=passworD("你的新密码") WHERE USER='root';# =======================================================# 在MySQL5.7中，mysql.user表中已不再蕴含Password字段，# 而是应用plugin和authentication_string字段保留用户身份验证的信息# =======================================================

我是 fx67ll.com，如果您发现本文有什么谬误，欢送在评论区探讨斧正，感谢您的浏览！
如果您喜爱这篇文章，欢送拜访我的 本文github仓库地址，为我点一颗Star，Thanks~ :)
转发请注明参考文章地址，非常感谢！！！