mysql入门之基础

SHOW DATABASES;
SHOW TABLES FROM mysql;
SHOW DATABASES;
#使用myemployees库，或者说切换到myemployees库
USE myemployees;

DESC employees;
DESCRIBE employees;
SHOW COLUMNS FROM employees;
SHOW COLUMNS FROM test.person;
SELECT
  `employee_id`,
  `first_name`,
  `last_name`,
  `phone_number`,
  `job_id`,
  `job_id`,
  `manager_id`,
  `department_id`
FROM
  employees ;
 
SELECT
last_name, `email`, "令狐冲", 'hellojack',
  2 > 5,
  2 < 5,
  100 % 30,
  "hello",
  'okok',
  666,
  888.99,
  'h',
  TRUE,
  FALSE,
  'true',
  NOW()
FROM
  employees ;
 
 
  SELECT NULL;
  SELECT NULL FROM employees;

  SELECT
  NOW(),
  CURRENT_TIMESTAMP(),
  VERSION(),
  LOCALTIME(),
  LOCALTIMESTAMP()
FROM
  `employees` ;

SELECT NULL;
SELECT NULL, NULL+30, NULL+NULL, 'abc', 666, 888.99, TRUE, FALSE, "hello", 'tru', "false";

 SELECT 888;
 SELECT 'jack';
 SELECT "令狐冲";
 SELECT "tom";
 
  SELECT 100 % 30, 6 + 7, 20-3, 3*5;
  SELECT last_name, email, salary FROM employees;
 
  #查询常量值
  SELECT 100, 'jack', TRUE, FALSE, 'true', 'false', 88.66, 'h', "hello", "w";
 
   #查询表达式
  SELECT 3 > 5, 6>2, 100*5, 100%30, 20 + 6, 3 * 8,TRUE, FALSE, 'TRUE', 'false';
 
  #查询函数
  SELECT NOW(), VERSION(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP();
 
 
#取别名
#方式一，使用as关键字
SELECT last_name AS 姓 FROM employees;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
SELECT last_name AS name1, first_name AS name2 FROM employees;
 
#方式二，省略as关键字，打一个空格
SELECT email 邮箱, salary 薪水 FROM employees;

#情况3，如果你要取的别名是个mysql的关键字，或者取的别名中包含特殊符号，比如像空格
#案例，比如你的别名中包含#井号，而我们都知道#井号在mysql中是注释符号
#(解决办法是，我们把别名用引号引起来，引号可以是双引号，也可以是单引号，建议大家使用双引号)

SELECT salary "薪#水", last_name AS 'name', email "OUT put", phone_number 'num ber' FROM employees;

#去重,使用关键字DISTINCT
#查询员工表中涉及到的所有的部门编号
SELECT emp.`department_id` FROM employees emp;
SELECT DISTINCT `department_id` FROM employees;
SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT emp.`department_id` FROM employees emp;
SELECT DISTINCT emp.department_id FROM employees emp;

#+号
SELECT 20+10 AS "结果";
SELECT '30'+10 AS 结果;
SELECT 'abcd'+5 AS result;
SELECT 'null'+6  result;
#下面这样取别名失效了
SELECT 60+'null' 'result';

#下面这种写法也不报错，有点疑惑
SELECT 60+'null' "result";
#

#可以使用as取别名，这样就不会失效了
SELECT 60+'null' AS 'result';
SELECT 60+'null' AS "result";

#下面这种写法也不报错，有点疑惑
SELECT 80+'haha'"ss";
SELECT 'haha'"ss";
SELECT "'haha'ss";
SELECT '\'haha\'ss';
SELECT "\'haha\'ss";
SELECT "\"haha\"ss";
SELECT '"haha"ss';
#如下这种写法我有疑惑？
SELECT 'null''result';
SELECT 'null' 'result';
#

SELECT NULL+8 result;
SELECT NULL+NULL "result";
SELECT 'abcd'+'hello' AS 'myResult';

#+号的作用
/*

java中的+号：
1运算符，两个操作数都为数值型
2连接符，只要有一个操作数为字符串

mysql中的+号：
仅仅只有一个功能：运算符
*/

#两个操作数都为数值型，则做加法运算
SELECT 100+20;
/*只要其中一方为字符型，试图将字符型数值转换成数值型,如果转换成
功，则继续做加法运算,如果转换失败，则将字符型数值转换成0
*/
#运行结果为140
SELECT '50'+90;
#运行结果为90
SELECT 'tom'+90;
#运行结果为0
SELECT 'tom'+'jack';

#只要其中一方为null，则结果肯定为null
#运行结果为NULL
SELECT NULL+10;
#运行结果为NULL
SELECT NULL+NULL;

#CONCAT()函数
#运行结果为MySQL
SELECT CONCAT('My', 'S', 'QL') AS 'result';
#运行结果为江西省赣州市于都县
SELECT CONCAT('江西省', '赣州市', '于都县') AS 家乡;
#运行结果为NULL
SELECT CONCAT('hello', NULL, 'world') AS "结果";
#运行结果为14.3
SELECT CONCAT(14.3) AS result;
#运行结果为14.326.7
SELECT CONCAT(14.3, 26.7) result;
#运行结果为14.3hello26.7
SELECT CONCAT(14.3, "hello", 26.7);
#
SELECT
    CONCAT(last_name,first_name) AS 姓名
FROM
    employees;

#案例：查询员工名和姓连接成一个字段，并显示为 姓名
#使用CONCAT()函数
SELECT  CONCAT(last_name, `first_name`) AS 姓名 FROM employees;
SELECT  CONCAT(`last_name`, `first_name`) FROM employees;
SELECT  CONCAT(last_name, first_name) FROM employees;
SELECT  CONCAT(last_name, first_name, '`s phone_number is ', phone_number) FROM employees;

#显示departments表结构
#使用DESC关键字,或者使用DESCRIBE关键字，DESC是DESCRIBE的简写
DESC departments;
DESCRIBE departments;
#也可以使用下面的语句查看表结构
SHOW COLUMNS FROM departments;
#查询departments表中的所有数据
SELECT * FROM departments;
#用`反引号把字段名、表名、数据库名包着也可以
SELECT * FROM `departments`;
SELECT manager_id, department_name, location_id FROM departments;
SELECT `manager_id`, `department_name`, `location_id` FROM `departments`;

#显示出表employees中的全部job_id（不能重复）
SELECT DISTINCT `job_id` FROM `employees`;
SELECT DISTINCT job_id FROM employees;

#IFNULL()函数
SELECT IFNULL(`commission_pct`, 0) AS 奖金率, `commission_pct` FROM `employees`;
SELECT IFNULL(commission_pct, 0) AS 奖金率, commission_pct FROM employees;

#显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT CONCAT(`first_name`, ',', `last_name`, ',', `email`, ',', IFNULL(`commission_pct`, 0)) AS "OUT_PUT" FROM `employees`;
SELECT CONCAT(first_name, ',', last_name, ',', email, ',', IFNULL(commission_pct, 0)) OUT_PUT FROM employees;