MySQL学习重点知识记录之阶段一

最新推荐文章于 2023-06-06 10:27:30 发布

fengzhexiabi

最新推荐文章于 2023-06-06 10:27:30 发布

阅读量686

点赞数

分类专栏：数据库 MySQL

本文链接：https://blog.csdn.net/fengzhexiabi/article/details/80727128

版权

数据库同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

前言：在工作中先后使用过Oracle、MySQL数据库，但是对他们的了解也只限于会使用，没有认真系统的学习过。最近在学习MySQL，现就学习过程中所总结的在工作中可能未注意到的知识点总结如下。

1、SQL是什么？

SQL——Structured Query Language,即结构化查询语言。

2、SHOW语句

2.1、SHOW DATABASES

SHOW DATABASES：返回可用数据库列表。
eg:

mysql> show databases;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| hibernate_test           |
| mysql                    |
| mytest                   |
| performance_schema       |
| peter_test_compositeid   |
| peter_test_db            |
| peter_test_extend1       |
| peter_test_extend2       |
| peter_test_extend3       |
| peter_test_many2many     |
| peter_test_many2many2    |
| peter_test_many2many_1   |
| peter_test_many2many_1_1 |
| peter_test_many2many_2_1 |
| peter_test_one2many1     |
| peter_test_one2many2     |
| peter_test_one2onefk2    |
| peter_test_one2onepk2    |
| petercopy                |
| sys                      |
+--------------------------+
21 rows in set (0.01 sec)

知道数据库后，我们可以通过use dataBaseName来切换数据库。
eg:

mysql> use mytest;
Database changed

2.2、SHOW TABLES

SHOW TABLES：返回一个数据库内表的列表。
eg：

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| class            |
| student          |
| t_class          |
| t_student        |
| t_teacher        |
| tab_test         |
| teacher          |
| testuuid         |
| users            |
+------------------+
9 rows in set (0.00 sec)

2.3、SHOW COLUMNS

SHOW COLUMNS：返回一个表的列字段信息列表。注意该命令需要配合FROM使用即必须给定表名。该命令和DESCRIBE作用相同。
eg:

mysql> show columns from class;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| c_id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name     | varchar(20) | YES  |     | NULL    |                |
| teacher_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> describe class;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| c_id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name     | varchar(20) | YES  |     | NULL    |                |
| teacher_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

2.4、SHOW CREATE DATABASE

SHOW CREATE DATABASE：显示创建数据库的SQL语句，注意该命令需要指定数据库名。
eg:

mysql> show create database mytest;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| mytest   | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

2.5、SHOW CREATE TABLE

SHOW CREATE TABLE：显示创建表的SQL语句，注意该命令需要指定表名。
eg:

mysql> show create table class;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `fk_teacher_id` (`teacher_id`),
  CONSTRAINT `fk_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、DISTINCT不能部分使用

DISTINCT关键字应用于所有列而不仅是前置它的列。
eg:

mysql> select * from class;
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    1 | bj_a   |          1 |
|    2 | bj_b   |          2 |
|    3 | bj_b   |          2 |
|    4 |        |          1 |
|    6 |        |          2 |
|    7 | NULL   |          2 |
+------+--------+------------+
6 rows in set (0.00 sec)

mysql> select distinct c_name,teacher_id from class;
+--------+------------+
| c_name | teacher_id |
+--------+------------+
| bj_a   |          1 |
| bj_b   |          2 |
|        |          1 |
|        |          2 |
| NULL   |          2 |
+--------+------------+
5 rows in set (0.00 sec)

注意上面distinct用在c_name前，但是它对teacher_id也有效了。

4、多列降序排列

如果想在多个列上进行降序排序，必须对每个列上指定DESC关键字。
eg:

mysql> select * from class;
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    1 | bj_a   |          3 |
|    2 | bj_b   |          2 |
|    3 | bj_b   |          2 |
|    4 |        |          1 |
|    6 |        |          2 |
|    7 | NULL   |          2 |
+------+--------+------------+
6 rows in set (0.00 sec)

mysql> select * from class order by c_name desc;
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    7 | NULL   |          2 |
|    2 | bj_b   |          2 |
|    3 | bj_b   |          2 |
|    1 | bj_a   |          3 |
|    4 |        |          1 |
|    6 |        |          2 |
+------+--------+------------+
6 rows in set (0.00 sec)

mysql> select * from class order by c_name desc, teacher_id desc;
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    7 | NULL   |          2 |
|    2 | bj_b   |          2 |
|    3 | bj_b   |          2 |
|    1 | bj_a   |          3 |
|    6 |        |          2 |
|    4 |        |          1 |
+------+--------+------------+
6 rows in set (0.00 sec)

从上面可以看出只有在c_name、teacher_id后面同时使用desc时，class结果集才是按照这个两个字段依次降序排列的。

5、FROM、ORDER BY、LIMIT的顺序

这个三个关键字同时出现在一条SQL时，其顺序必须严格按照：FROM … ORDER BY … LIMIT …，否则会报错。

6、LIKE和REGEXP的区别

LIKE是整列匹配，而REGEXP是列值内匹配。通过下面的命令可以查出它们之间的差距。
eg:

mysql> select * from class;
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    1 | bj_a   |          3 |
|    2 | bj_b   |          2 |
|    3 | bj_  b |          2 |
|    4 |        |          1 |
|    6 |        |          2 |
|    7 | NULL   |          2 |
+------+--------+------------+
6 rows in set (0.00 sec)

mysql> select * from class where c_name like 'bj_';
Empty set (0.00 sec)

mysql> select * from class where c_name regexp 'bj_';
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    1 | bj_a   |          3 |
|    2 | bj_b   |          2 |
|    3 | bj_  b |          2 |
+------+--------+------------+
3 rows in set (0.01 sec)

拓展1：REGEXP是不分大小写，如果想区分大小写，可以使用关键字BINARY。
eg:

mysql> select * from class where c_name regexp 'Bj_';
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    1 | bj_a   |          3 |
|    2 | bj_b   |          2 |
|    3 | bj_  b |          2 |
+------+--------+------------+
3 rows in set (0.00 sec)

mysql> select * from class where c_name regexp binary 'Bj_';
Empty set (0.00 sec)

拓展2：用select … regexp …可以来验证正则表达。结果为1表示匹配上，结果为0表示没有匹配上。
eg:

mysql> select 111 regexp '[0-9]';
+--------------------+
| 111 regexp '[0-9]' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select 'abc1' regexp '^[0-9]';
+------------------------+
| 'abc1' regexp '^[0-9]' |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> select '1abc1' regexp '^[0-9]';
+-------------------------+
| '1abc1' regexp '^[0-9]' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)