SQL知识点详细总结（附操作图解）（二）

目录

查询已有数据库和表

SELECT语句+列名称 查询列

SELECT语句+AS关键字 设定别名

SELECT语句+常数

SELECT语句+DISTINCT 删除重复行

SELECT语句+LIMIT 限制返回行数

SELECT语句+WHERE 条件选取

SELECT语句+算数运算符

SELECT语句+比较运算符

SELECT语句+逻辑运算符

注释的书写方法

---

查询已有数据库和表

1.打开CMD并登录MySQL:mysql -uroot -p

C:\Windows\system32>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.查询已有数据库：SHOW DATABASES;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| class              |
| information_schema |
| mysql              |
| performance_schema |
| product            |
| shop               |
| sys                |
+--------------------+
7 rows in set (0.02 sec)

3.查询指定数据库中已有的表:SHOW TABLES;

mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

4.查询表的结构：DESC 表名称;

mysql> desc student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | char(4)      | NO   | PRI | NULL    |       |
| student_name | varchar(100) | NO   |     | NULL    |       |
| test_scores  | int          | NO   |     | NULL    |       |
| student_sex  | varchar(100) | YES  |     | NULL    |       |
| birth_date   | date         | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

SELECT语句+列名称 查询列

1.查询表中所有的列:SELECT * FROM 列名称;

mysql> select * from student;
+------------+--------------+-------------+-------------+------------+
| student_id | student_name | test_scores | student_sex | birth_date |
+------------+--------------+-------------+-------------+------------+
| 01         | 张三         |          90 | 男          | 2002-02-23 |
| 02         | 李梅         |          95 | 女          | 2001-11-12 |
| 03         | 王二         |          89 | 男          | 2002-05-01 |
| 04         | 钱进         |          80 | 男          | 2002-11-12 |
| 05         | 韩雪         |          90 | 女          | 2001-12-25 |
+------------+--------------+-------------+-------------+------------+
5 rows in set (0.00 sec)

2.查询表中需要的列:SELECT 列名称1，列名称2，列名称3 FROM 表名称;

mysql> select student_id,student_sex,student_name from student;
+------------+-------------+--------------+
| student_id | student_sex | student_name |
+------------+-------------+--------------+
| 01         | 男          | 张三         |
| 02         | 女          | 李梅         |
| 03         | 男          | 王二         |
| 04         | 男          | 钱进         |
| 05         | 女          | 韩雪         |
+------------+-------------+--------------+
5 rows in set (0.00 sec)

SELECT语句+AS关键字 设定别名

1.SELECT 列名称1 AS 别名1,列名称2 AS 别名2 FROM 表名称;

mysql> select student_id as id,student_sex as sex,student_name as name from student;
+----+------+------+
| id | sex  | name |
+----+------+------+
| 01 | 男   | 张三 |
| 02 | 女   | 李梅 |
| 03 | 男   | 王二 |
| 04 | 男   | 钱进 |
| 05 | 女   | 韩雪 |
+----+------+------+
5 rows in set (0.00 sec)

2.设定中文别名——需要用双引号“”标识

SELECT 列名称1 AS "中文别名1",列名称2 AS "中文别名2" FROM 表名称;

mysql> select student_id as "学号",student_sex as "性别",student_name as "姓名" from student;
+------+------+------+
| 学号 | 性别 | 姓名 |
+------+------+------+
| 01   | 男   | 张三 |
| 02   | 女   | 李梅 |
| 03   | 男   | 王二 |
| 04   | 男   | 钱进 |
| 05   | 女   | 韩雪 |
+------+------+------+
5 rows in set (0.00 sec)

SELECT语句+常数

查询常数：SELECT '字符串常数' AS string,数字常数 AS number,'日期常数' AS date FROM 表名称;

mysql> select '学生' AS string,90 AS number,'2002-02-23' AS date,student_id,student_name from student;
+--------+--------+------------+------------+--------------+
| string | number | date       | student_id | student_name |
+--------+--------+------------+------------+--------------+
| 学生   |     90 | 2002-02-23 | 01         | 张三         |
| 学生   |     90 | 2002-02-23 | 02         | 李梅         |
| 学生   |     90 | 2002-02-23 | 03         | 王二         |
| 学生   |     90 | 2002-02-23 | 04         | 钱进         |
| 学生   |     90 | 2002-02-23 | 05         | 韩雪         |
+--------+--------+------------+------------+--------------+
5 rows in set (0.00 sec)

SELECT语句+DISTINCT 删除重复行

1.单列数据合并：SELECT DISTINCT 列名称 FROM 表名称;

注：使用DISTINCT时，NULL也被视为一类数据，也会被合并。

mysql> select distinct test_scores from student;
+-------------+
| test_scores |
+-------------+
|          90 |
|          95 |
|          89 |
|          80 |
+-------------+
4 rows in set (0.01 sec)

2.多列数据合并：SELECT DISTINCT 列名称1,列名称2,列名称3 FROM 表名称;

SELECT语句+LIMIT 限制返回行数

1.选取前N行：SELECT 列名称 FROM 表名称 LIMIT （行数）;

2.选取中间行（m-n）：

SELECT 列名称 FROM 表名称 LIMIT n-m+1（行数） OFFSET m-1（第一行偏移量）;

或者SELECT 列名称 FROM 表名称 LIMIT m-1(第一行偏移量）,n-m+1（返回的最大行数）;

注：Limit M,N——跳过M条记录向后选取N条数据。

3.统计表格共有几行数据：SELECT COUNT(*) FROM 表名称;

select student_name from student limit 2;
+--------------+
| student_name |
+--------------+
| 张三         |
| 李梅         |
+--------------+

mysql>  select student_id from student limit 1,4;
+------------+
| student_id |
+------------+
| 02         |
| 03         |
| 04         |
| 05         |
+------------+
4 rows in set (0.00 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

SELECT语句+WHERE 条件选取

1.SELECT 列名称 FROM 表名称 WHERE 条件表达式;

mysql> select student_name,test_scores from student where test_scores=90;
+--------------+-------------+
| student_name | test_scores |
+--------------+-------------+
| 张三         |          90 |
| 韩雪         |          90 |
+--------------+-------------+
2 rows in set (0.00 sec)

mysql> select student_name from student where test_scores=90;
+--------------+
| student_name |
+--------------+
| 张三         |
| 韩雪         |
+--------------+
2 rows in set (0.00 sec)

2.SELECT+WHERE+BETWEEN+AND：

SELECT 列名称 FROM 表名称 WHERE 列名称 BETWEEN 条件1 AND 条件2;

mysql> select student_name,test_scores from student where test_scores between 80 and 90;
+--------------+-------------+
| student_name | test_scores |
+--------------+-------------+
| 张三         |          90 |
| 王二         |          89 |
| 钱进         |          80 |
| 韩雪         |          90 |
+--------------+-------------+
4 rows in set (0.00 sec)

SELECT语句+算数运算符

1.算数运算符：+、-、*、/（加、减、乘、除）

2.（）——括号内的运算表达式优先运算

3.所有包含NULL的计算，结果肯定是NULL

mysql> select student_name,test_scores,test_scores/2 FROM student;
+--------------+-------------+---------------+
| student_name | test_scores | test_scores/2 |
+--------------+-------------+---------------+
| 张三         |          90 |       45.0000 |
| 李梅         |          95 |       47.5000 |
| 王二         |          89 |       44.5000 |
| 钱进         |          80 |       40.0000 |
| 韩雪         |          90 |       45.0000 |
+--------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select student_name,test_scores,(test_scores+10)*2 from student;
+--------------+-------------+--------------------+
| student_name | test_scores | (test_scores+10)*2 |
+--------------+-------------+--------------------+
| 张三         |          90 |                200 |
| 李梅         |          95 |                210 |
| 王二         |          89 |                198 |
| 钱进         |          80 |                180 |
| 韩雪         |          90 |                200 |
+--------------+-------------+--------------------+
5 rows in set (0.00 sec)

mysql> select student_name,test_scores,test_scores+NULL from student;
+--------------+-------------+------------------+
| student_name | test_scores | test_scores+NULL |
+--------------+-------------+------------------+
| 张三         |          90 |             NULL |
| 李梅         |          95 |             NULL |
| 王二         |          89 |             NULL |
| 钱进         |          80 |             NULL |
| 韩雪         |          90 |             NULL |
+--------------+-------------+------------------+
5 rows in set (0.00 sec)

mysql> select (1000+2000)*2 as caculation;
+------------+
| caculation |
+------------+
|       6000 |
+------------+
1 row in set (0.00 sec)

SELECT语句+比较运算符

比较运算符

等于	=
不等于	<>
大于等于	>=
大于	>
小于等于	<=
小于	<

  注：1.比较运算符位置不能颠倒。

          2.字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

          3.不能对NULL使用比较运算符，选取NULL或者不是NULL记录条件表达式使用IS NULL或者IS NOT NULL。

mysql> select student_name,test_scores
    -> from student
    -> where test_scores<>90;
+--------------+-------------+
| student_name | test_scores |
+--------------+-------------+
| 李梅         |          95 |
| 王二         |          89 |
| 钱进         |          80 |
+--------------+-------------+
3 rows in set (0.00 sec)

mysql> select student_name,student_sex,birth_date from student
    -> where birth_date>='2002-01-01';
+--------------+-------------+------------+
| student_name | student_sex | birth_date |
+--------------+-------------+------------+
| 张三         | 男          | 2002-02-23 |
| 王二         | 男          | 2002-05-01 |
| 钱进         | 男          | 2002-11-12 |
+--------------+-------------+------------+
3 rows in set (0.00 sec)

mysql> select student_name,student_id,test_scores from student
    -> where test_scores-60>=30;
+--------------+------------+-------------+
| student_name | student_id | test_scores |
+--------------+------------+-------------+
| 张三         | 01         |          90 |
| 李梅         | 02         |          95 |
| 韩雪         | 05         |          90 |
+--------------+------------+-------------+
3 rows in set (0.00 sec)

mysql> -- 创建表
mysql> CREATE TABLE string(number CHAR(4) NOT NULL,PRIMARY KEY(number));
Query OK, 0 rows affected (0.05 sec)

mysql> -- 插入数据
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into string(number) values('1'),('2'),('3'),('4'),('11'),('12'),('21'),('33'),('123');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> -- 查询所有列
mysql> select * from string;
+--------+
| number |
+--------+
| 1      |
| 11     |
| 12     |
| 123    |
| 2      |
| 21     |
| 3      |
| 33     |
| 4      |
+--------+
9 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> -- 选出大于'21'的数据
mysql> select number from string where number>'21';
+--------+
| number |
+--------+
| 3      |
| 33     |
| 4      |
+--------+
3 rows in set (0.04 sec)

SELECT语句+逻辑运算符

1.逻辑运算符：NOT、AND、OR

2.真值——值为真（TRUE）或者假（FALSE）其中之一的值。

3.逻辑运算符把比较运算符返回的真值等进行操作：

    AND运算符两侧真值均为真则反馈真，否则为假；

    OR运算符两侧真值均为假则返回假，否则为真；

    NOT转换真为假，假为真；

4.只有SQL中的逻辑运算被称为三值逻辑，第三种值为不确定（UNKNOWN），需要用到IS NULL或者IS NOT NULL。

5.AND运算符的优先级高于OR运算符，要优先执行OR运算符需要使用括号。

NOT运算符的运用：SELECT 列名称 FROM 表名称 WHERE NOT 条件表达式;

mysql> select student_name,student_sex,test_scores from student where NOT test_scores<90;
+--------------+-------------+-------------+
| student_name | student_sex | test_scores |
+--------------+-------------+-------------+
| 张三         | 男          |          90 |
| 李梅         | 女          |          95 |
| 韩雪         | 女          |          90 |
+--------------+-------------+-------------+
3 rows in set (0.00 sec)

AND运算符的运用：SELECT 列名称 FROM 表名称 WHERE 条件1 AND 条件2;

mysql> select student_name,student_sex,test_scores from student where test_scores<=90 and student_sex='男';
+--------------+-------------+-------------+
| student_name | student_sex | test_scores |
+--------------+-------------+-------------+
| 张三         | 男          |          90 |
| 王二         | 男          |          89 |
| 钱进         | 男          |          80 |
+--------------+-------------+-------------+
3 rows in set (0.00 sec)

OR运算符的运用：SELECT 列名称 FROM 表名称 WHERE 条件1 OR 条件2;

mysql> select student_name,student_sex,test_scores from student where test_scores<85 OR student_sex='女';
+--------------+-------------+-------------+
| student_name | student_sex | test_scores |
+--------------+-------------+-------------+
| 李梅         | 女          |          95 |
| 钱进         | 男          |          80 |
| 韩雪         | 女          |          90 |
+--------------+-------------+-------------+
3 rows in set (0.00 sec)

注释的书写方法

1.注释——SQL语句中用来标识说明或者注意事项，对SQL的执行没有任何影响。

2.单行注释——写在同一行，写在-- 之后（需要在--后面加半角空格）

3.多行注释——可以跨行，写在/*和*/之间

未完待续。。。。。。