1.选择数据库
语法:
USE 数据库名;
例如:
USE myemployees;
2.查询字段
#查询表employees中的first_name字段
SELECT first_name FROM employees;
#查询表employees中的first_name字段、last_name字段,salary字段
SELECT first_name,last_name,salary FROM employees;
#查询表employees中的全部字段
SELECT * FROM employees;
3.查询常量
SELECT 100;
SELECT 'john';
4.查询表达式
SELECT 100*11;
5.查询函数
SELECT VERSION();
如下所示:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.62 |
+-----------+
1 row in set (0.01 sec)
6.给变量起别名
#使用关键词AS
SELECT last_name AS 姓氏 FROM employees;
#省略AS 只用空格也行
SELECT last_name 姓氏,first_name 名字 FROM employees;
SELECT salary "out put" FROM employees;
7.去重,且 distinct 之后每次只能加一个字段
SELECT DISTINCT department_id FROM employees;
8.+
号的作用:运算符
①两边均为数值型,做加法运算
SELECT 100+19;
②其中一方为字符型,则尝试将其转换为数值型,如果转换成功,则做加法运算
SELECT '100'+19;
③如果转换失败,则将字符型转换成0
SELECT 'mike'+19;
④如果其中一方为null,则结果为null
SELECT NULL+19;
9.拼接函数concat(str1,str2...)
,注意null和任何数据拼接结果都是null
SELECT
CONCAT(last_name,'_',first_name) AS 姓名
FROM
employees;
10.上述知识的练习
①显示表departments的结构,并查询其中的全部数据
DESC departments;
SELECT * FROM departments;
②显示employees中所有的job_id(不能重复)
SELECT DISTINCT job_id FROM employees;
③显示表employees的全部列,各个列之间用逗号连接,列头显示为OUT_PUT
SELECT
IFNULL(commission_pct, 0) AS 结果,
commission_pct
FROM
employees;
SELECT
CONCAT(
first_name,
',',
last_name,
',',
email,
',',
phone_number,
',',
job_id,
',',
salary,
',',
IFNULL(commission_pct,0),
',',
manager_id,
',',
department_id,
',',
hiredate
) AS 'OUT_PUT'
FROM
employees;