Mysql基础(一)

数据库相关概念

    1.DB:  数据库，保存一组有组织的数据的容器

    2.DBMS: 数据库管理系统，又称数据库软件，用于管理DB中的数据

    3.SQL:结构化查询语言，用于DBMS通信

MYSQL服务的登录和退出

登录：mysql 【-h主机名 -p端口号】 -用户名 -密码

退出： exit ,   ctrl  c       如： mysql -u root -p123456

MYSQL的常见命令

    1.查看当前所有的数据库： show  database;

    2.打开指定的库  ：use库名

    3.查看当前库的所有类  ：show tables;

    4.查看其他库的所有类 ：show table 表名；

    5.创建表    ：  create  table  表名(

                                    列名   列类型，

                                    列名  列类型，

                                  。。。

                             );

    6.查看表结构  ：desc 表名;

    7.查看服务器的版本

           方式一：登录到mysql 服务端  select  version();

           方式二：没有登录到mysql服务端    mysql --version   或 mysql --V

   

注释：

    单行注释：#注释文字  或  -- 注释文字

    多行注释：/*   注释文字   */

基础查询

USE myemployees;

-- 查表中单个字段
SELECT last_name FROM employees;

-- 查询表中的多个字段
SELECT last_name,salary,email FROM employees;

-- 查询表中的所有字段
SELECT * FROM employees;

-- 查询常量值
SELECT  100;
SELECT 'join';

-- 查询表达式
SELECT 100*23;

-- 查询函数
SELECT VERSION();

-- 起别名  as 或者用空格
SELECT 100*123 AS 结果;

-- 去重
SELECT  DISTINCT department_id FROM employees;

-- + 号的作用:只有一个功能 运算符加法运算
SELECT 'join'+99;  -- 结果为99 转换失败，将字符型数值转换为0
SELECT '133'+99;   -- 结果为232，转换成功 继续做加法运算
SELECT NULL+99;    -- 结果为null,只要其中一方为null，则结果肯定为null

-- 拼接  CONCAT   可以嵌套 ifnull(a,0)
SELECT CONCAT('a','b','c')AS 结果; 
SELECT CONCAT (last_name,first_name)AS 姓名 FROM employees;

条件查询

#条件查询

/*语法
       select
            查询列表
       from
            表名
       where
            筛选条件;
      
  分类    
       按条件表达式筛选
             条件运算符  > <  =  <>  >=  <=
       按逻辑表达式
             逻辑运算符  &&  ||  ！     
                        and  or  not           
       模糊查询    
             like
             between and
             in
             is null 
*/

-- 1.查询工资>12000的员工信息
SELECT * 
FROM employees
WHERE salary>12000;

-- 2.查询部门编号不等有90的员工名和部门名
SELECT  last_name,department_id
FROM employees
WHERE department_id != 90;

-- 3.查询工资在10000到20000之间的员工名，工资以及奖金
SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;

-- 4.查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * 
FROM employees
WHERE department_id<90 OR department_id>110 OR salary>15000;

-- 5.查询员工名中包含字符a的员工信息
SELECT * 
FROM employees
WHERE last_name LIKE '%a%';

-- 6.查询员工名中第三个字符为a  第五个字符为a的员工的工资和员工名
SELECT salary,last_name
FROM employees
WHERE last_name LIKE '__n_l%';

-- 7.查询员工名中第二个字符为_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';


-- 8.查询员工编号在100-120的员工信息
SELECT *
FROM employees
WHERE  employee_id BETWEEN 100 AND  120; -- 包含临界值，不可调换顺序的

-- 9.询员工的工种编号是 IT_PROG,AD_PRES的一个员工名和编号
SELECT last_name,job_id    
FROM     employees
WHERE job_id IN('IT_PROG','AD_PRES');  -- in列表的值的了类型必须统一或兼容

-- 10.查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE  commission_pct IS  NULL;

-- 11.安全等于
SELECT last_name,commission_pct
FROM employees
WHERE  commission_pct <=>NULL;

-- 12.查询员工号为176的员工的姓名和部门号和年薪
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0))AS 年薪
FROM employees
WHERE employee_id = 176;

#练习
-- 13.查询没有奖金，且工资小于18000的salary,last_name
SELECT salary,last_name
FROM employees
WHERE commission_pct IS NULL AND salary<18000;

#14.试问select *from employees;和
#       selet * from empleyees 
#       where commission_pct like'%%'and last_name like '%%';
#   不一样的，判断的字段有null值就不一样了
#    换成or的话是一样的   

排序

USE myemployees;
-- 15.查询员工信息，要求工资从高到底排序
SELECT *
FROM employees
GROUP BY salary DESC; -- desc 降序  默认升序

-- 16.查询部门编号大于等于90的员工信息，按入职时间的先后顺序进行排序
SELECT * 
FROM employees
WHERE department_id>=90
GROUP BY hiredate; 

-- 17.按年薪的高低显示员工的信息和年薪
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
GROUP BY  年薪;

-- 18.按姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) AS 名字长度,last_name,salary 
FROM employees
ORDER BY 名字长度;

-- 19.查询员工信息，先按工资排序再按员工编号排序
SELECT *
FROM employees
ORDER BY salary ASC ,department_id DESC;

特点：order by 字句中可以支持单个字段，多个字段，表达式，函数，别名

           order by 字句一般是放在查询语句的最后面，limit语句除外

函数

#字符函数
--  length()获取字节个数 utf-8每个汉字三个字节15
SELECT LENGTH('丁芳芳linlin');
SELECT LENGTH('join'); -- 4
SHOW  VARIABLES LIKE '%char%';

-- concat()拼接字符串 
SELECT  CONCAT(last_name,'_',first_name) 姓名
FROM employees;

-- upper() 转为大写， lower()转为小写

-- substr ，substring 索引从1 开始
-- 两个参数的截取：从指定索引处开始截取指定长度的字符
SELECT SUBSTR('我想你了，琳琳',5) out_put; --  ，琳琳

-- instr 返回子串第一次出现的索引，如果找不到返回0

-- trim去除字符串两边的空格

-- lpad 左填充,用指定的字符实现左填充指定的长度，
-- rpad 右填充同理
SELECT LPAD('何苗苗',6,'AA') OUT_PUT;

-- replace 替换
SELECT REPLACE('AABBCC','AA','DD')  SOUT; -- DDBBCC

#数学函数
-- round四舍五入 负数先算绝对值再加符号
SELECT ROUND(1.56);
SELECT ROUND(1.238,2);-- 小数点后保留两位 1.24

-- ceil 向上取整,返回大于等于该参数的最小整数
SELECT CEIL(1.00);  --  1
SELECT CEIL(-2.35);--  -2

-- floor 向下取整，返回小于等于该参数的最大整数
SELECT FLOOR(9.83);  --   9
SELECT FLOOR(-23.34); --  24

-- truncate  截断
SELECT TRUNCATE(23.599,2);  -- 23.59

-- MOD()取余
/*有意思
mod(a,b):a-a/b*b
mod(-10,-3):-10-(-10)/(-3)*(-3) = -1
*/
SELECT MOD(10,-3); -- 1 符号与10的符号相同

#日期函数
-- now() 返回当前系统日期 + 时间
SELECT NOW();-- 2020-04-07 16:26:42

-- curdate() 返回当前系统日期，不包含时间
SELECT CURDATE();-- 2020-04-07

-- currime()
SELECT CURTIME();-- 16:29:37

-- 获取年
SELECT YEAR(NOW()) 年；-- 2020

-- STR_TO_DATE查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

-- date-formate() 将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')AS sout;

#流程控制函数
-- if函数：if else的结果
SELECT  IF( 10>3,'大于','小于');

-- CASE函数的使用 switch-case
-- 查询员工的工资，要求
/*
部门号 = 30，显示的工资为1.1倍
部门号 = 40，显示的工资为1.2倍
部门号 = 40，显示的工资为1.3倍
*/
SELECT salary department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

-- 查询员工的工资情况
/*
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则，显示D级别
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'

END AS 工资级别
FROM employees;

#分组函数
-- sum avg  min  max  count
SELECT SUM(salary) ,AVG(salary) ,MIN(salary), MAX(salary),COUNT(salary)
FROM employees;

 -- distinct与分组函数搭配
 SELECT SUM(DISTINCT salary)
 FROM employees;
 
 
-- count() 函数,统计行数
SELECT COUNT(*) FROM employees;

和分组函数一同查询的字段要求是group by后的字段

分组查询

#分组函数
-- 查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;    

-- 查询邮箱中包含a字符的，每个部门的平均工资
SELECT  AVG(salary), department_id
FROM employees
WHERE email LIKE  '%a%'
GROUP BY department_id;

-- 查询有奖金的每个领导手下员工的最高工资
SELECT  MAX(salary),manager_id
FROM employees
WHERE  commission_pct IS NOT NULL
GROUP BY manager_id;


-- 查询那个部门的员工个数大于2
SELECT  COUNT(*) c ,department_id
FROM employees
GROUP BY department_id
HAVING c>2;

-- 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>12000 ; 

-- 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低
SELECT manager_id,MIN(salary) m
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING m>5000;

-- 查询按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*) c, LENGTH(last_name) m
FROM employees
GROUP BY m
HAVING    c>5;

-- 查询每个部门每个工种的员工的平均工资，平均工资大于10000的，按平均工资的高低显示
SELECT AVG(salary) a,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id, job_id    
HAVING a>10000
ORDER BY a DESC;

-- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary) m,manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING m>=6000;