当前位置 : 主页 > 编程语言 > 其它开发 >

MYSQL:基本SELECT语句

来源:互联网 收集:自由互联 发布时间:2022-05-30
第03章_基本的SELECT语句1.SQL概述1.1SQL背景知识 1.2SQL语言排行榜 自从SQL加入了TIOBE编程语言排行榜,就一直保持在Top 10. 1.3 SQL分类 SQL语言在功能上主要分为如下3大类: 学习技巧:大出着
第03章_基本的SELECT语句 1.SQL概述 1.1SQL背景知识

image

image

1.2SQL语言排行榜

自从SQL加入了TIOBE编程语言排行榜,就一直保持在Top 10.

image

1.3 SQL分类

SQL语言在功能上主要分为如下3大类:

image

学习技巧:大出着眼,小处着手。

2.SQL语言的规则与规范 2.1基本规则

image

2.2 SQL大小写规范(建议遵守)

image

2.3注释

image

2.4命名规则(暂时了解)

image

image

2.5导入数据

方式一:source 文件的全路径名

举例:source D:\atguigudb.sql;

mysql> source D:\atguigudb.sql;
ERROR:
Unknown command '\a'.
Query OK, 0 rows affected, 1 warning (0.04 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.30 sec)

Database changed
Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected, 2 warnings (5.29 sec)

Query OK, 25 rows affected (0.50 sec)
Records: 25  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.18 sec)

Query OK, 0 rows affected, 4 warnings (1.84 sec)

Query OK, 27 rows affected (0.20 sec)
Records: 27  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected, 6 warnings (4.07 sec)

Query OK, 107 rows affected (0.62 sec)
Records: 107  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected, 3 warnings (2.30 sec)

Query OK, 6 rows affected (0.36 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.21 sec)

Query OK, 0 rows affected, 3 warnings (1.51 sec)

Query OK, 10 rows affected (0.33 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.22 sec)

Query OK, 0 rows affected, 3 warnings (2.03 sec)

Query OK, 19 rows affected (0.29 sec)
Records: 19  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.15 sec)

Query OK, 0 rows affected, 2 warnings (2.00 sec)

Query OK, 23 rows affected (0.48 sec)
Records: 23  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.29 sec)

Query OK, 0 rows affected, 2 warnings (1.63 sec)

Query OK, 3 rows affected (0.36 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.19 sec)

Query OK, 0 rows affected, 2 warnings (3.94 sec)

Query OK, 4 rows affected (0.57 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.29 sec)

Query OK, 0 rows affected (0.23 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 6 warnings (2.69 sec)

Query OK, 0 rows affected (2.01 sec)

Query OK, 0 rows affected (0.18 sec)

Query OK, 0 rows affected (0.93 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| dbtest1            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.11 sec)

方式二:基于具体的图形化界面的工具可以导入数据库

image

image

image

3.基本的SELECT语句 3.0SELECT...
SELECT 1;  #没有任何子句
SELECT 9/2 
REOM DUAL; #dual:伪表
3.1SELECT ... FROM
  • 语法:SELECT 字段1,字段2,... FROM 表名
# *:表中的所有字段(或列)
SELECT * FROM employees;

SELECT employees_id,last_name,salary
FROM employees;

image

image

3.2列的别名

image

image

3.3去除重复行

image

image

image

3.4空值参与运算

image

3.5着重号

image
image

3.6查询常数

image

4.显示表结构

使用DESCRIBE或DESC,命令,表示表结构。

DESCRIBE employees;
或
DESC employees;

image

5.过滤数据
  • 背景

image

  • 语法
#查询90号部门的员工信息
SELECT * 
FROM employees
#过滤条件声明在from后面
WHERE department_id = 90;

image

课后习题

【题目】1.查询员工12个月的工资总和,并起别名为ANNUALSALARY

2.查询employees表中去除重复的job_id以后的数据

3.查询工资大于12000的员工姓名和工资

4.查询员工号为176的员工的姓名和部门号

5.显示表departments的结构,并查询其中的全部数据

#第03章课后练习题

1.查询员工12个月的工资总和,并起别名为ANNUALSALARY
#基本工资,无奖金

SELECT employee_id,last_name,salary * 12 "ANNUL SALARY"
FROM employees;

所有工资,包括奖金

SELECT employee_id,last_name,salary * 12*(1+IFNULL(commission_pct,0)) "ANNUL SALARY" 
FROM employees;


2.查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id
FROM employees;


3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary  >12000;

4.查询员工号为176的员工的姓名和部门号

SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;

#5.显示表departments的结构,并查询其中的全部数据

DESCRIBE departments;
网友评论