mysql 基础命令

进阶1：基础查询
/*
语法：
select 查询列表 from 表名;

类似于：System.out.println(打印东西);

特点：

1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/

USE myemployees;

#1.查询表中的单个字段

SELECT last_name FROM employees;

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

#3.查询表中的所有字段

#方式一：
SELECT 
    `employee_id`,
    `first_name`,
    `last_name`,
    `phone_number`,
    `last_name`,
    `job_id`,
    `phone_number`,
    `job_id`,
    `salary`,
    `commission_pct`,
    `manager_id`,
    `department_id`,
    `hiredate` 
FROM
    employees ;
#方式二：  
 SELECT * FROM employees;
 
 #4.查询常量值
 SELECT 100;
 SELECT 'john';
 
 #5.查询表达式
 SELECT 100%98;
 
 #6.查询函数
 
 SELECT VERSION();
 
 
 #7.起别名
 /*
 ①便于理解
 ②如果要查询的字段有重名的情况，使用别名可以区分开来
 
 */
 #方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;

#案例：查询salary，显示结果为 out put
SELECT salary AS "out put" FROM employees;

#8.去重

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

#9.+号的作用

/*

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

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

select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
            如果转换成功，则继续做加法运算
select 'john'+90;    如果转换失败，则将字符型数值转换成0

select null+10; 只要其中一方为null，则结果肯定为null

*/

#案例：查询员工名和姓连接成一个字段，并显示为 姓名

SELECT CONCAT('a','b','c') AS 结果;

SELECT 
    CONCAT(last_name,first_name) AS 姓名
FROM
    employees;
    
    
 

 

 

 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
 

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> create table stuinfo(
    -> id int,
    -> name varchar(20));
Query OK, 0 rows affected (0.01 sec)

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

mysql> desc stuinfo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from stuinfo;
Empty set (0.00 sec)

mysql> insert into stuinfo (id,name) values(1,'john');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stuinfo (id,name) values(2,'rose');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuinfo;
+------+------+
| id   | name |
+------+------+
|    1 | john |
|    2 | rose |
+------+------+
2 rows in set (0.00 sec)

mysql> update stuinfo set name='lilei' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stuinfo;
+------+-------+
| id   | name  |
+------+-------+
|    1 | lilei |
|    2 | rose  |
+------+-------+
2 rows in set (0.00 sec)

mysql> delete from stuinfo where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuinfo;
+------+------+
| id   | name |
+------+------+
|    2 | rose |
+------+------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.15    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Windows\system32>mysql --version
mysql  Ver 14.14 Distrib 5.5.15, for Win32 (x86)

C:\Windows\system32>mysql -V
mysql  Ver 14.14 Distrib 5.5.15, for Win32 (x86)

转义字符：escape

select last_name from employees where last_name like '_$_%' escape '$';

 

between N and N1

name_id  betwenn 100 and 120; 包含100 和120；

安全等于：    <=>

不仅可以判断null 也可以判断数值

select × from employees where status <=> null;

 

order by 支持 单个字段 多个字段 函数、别名；

upper，lower、concat、group_concat

substr（'abcd',1,2） 字符串，起始位置，长度

substr('abcd',2) 从第二字符开始到末尾

instr（'abcde','a'）返回字符串第一次出现的位置（索引）

返回：1

TRIM （) 去空格

LPAD，RPAD 左填充、右填充

 

数学函数：

round 四舍五入

select round（1.345）；

ceil（） 向上取整

select(1.00)

floor() 向下取整

日期函数：

select now();
select curdate()

select curtime()
select year('1922-01-02')
select month('1922-01-02')
select day('1922-01-02')

str_to_date('9-13-1999','%m-%d-%Y')

select str_to_date('1922-09-12','%Y-%m-%d')

select data_format(now(),'%Y年%m月%d日')

 

流程控制函数：

1、if 函数： if else 的函数

 

select if(10 <5,'大','xiao')
select if(10>5,'da','xiao')

2、

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
.......
else
要显示的值n 或者语句
end
 

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
.......
else
要显示的值n 或者语句
end

第一个案例（等值判断）

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
.......
else
要显示的值n 或者语句
end

案例1：
部门号=30 显示的工资为1.1倍
部门号=40 显示的工作为1.2
部门号=50 显示的工资为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

第二种情况：（区间判断）

case
when 条件1 then 要显示的值1 或语句1
when 条件2 then 要显示的值2 或语句2
......
else 要显示的值n 或语句n
end

案例：查询员工的工作情况
如果工资>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

select job_id as job,
case job_id
when  'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_PRE' then 'D'
end as groade
from employees

 

 

统计函数：

sum() null 与任何数字都为null

count() 统计的是非空字段，null 不会被统计

avg() 不计算为null的字段

min() 不区分数值和字符串

max() 不区分数值和字符串类型

distinct 去重

 

group by 

select max(salary),job_id from employees GROUP BY employee_id;

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

分组后的刷选：

select count(employee_id) from  employees GROUP BY department_id HAVING count(employee_id) > 5;

 

select manager_id,min(salary) from employees
where manager_id > 102
group by manager_id
HAVING min(salary) > 5000
 

连接查询：

EXPLAIN
select department_name,d.manager_id, min(salary)
from departments d, employees e
where d.department_id=e.department_id
and commission_pct is not null
group by department_name,d.manager_id

 

sql92 标准：

夺标等值连接

n个表连接需要n-1 个连接条件

 

自连接：

查询员工名和上级领导的名（同一个表进行两次查询）

select e.employee_id, e.last_name,m.employees_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id