mysql 去重区分大小写_MySQL - Peterxiazhen - 博客园

一、数据库的好处

可以持久话数据到本地

结构化查询(SQL)

二、数据库的常见概念 *

DB：数据库 存储有组织的数据的容器

DBMS：数据库管理系统，又称为数据库软件或者数据库产品(eg. MySQL)，用于创建和管理数据库

SQL：结构化查询语言：与数据库通信，实现数据库操作，几乎所有的主流的数据库软件通用的语言

三、数据库存储的特点

数据存到表中，表再放到库中

一个库中可以有多张表，每张表具有唯一的表名来标识自己

表中有一个或多个列，列又称为“字段”(相当于Java类中的属性)

表中的每一行数据，相当于Java中的“对象”

四、MySQL的安装

属于c/s架构的软件，一般来讲，安装的是服务端

五、MySQL的启动、停止、登陆和退出

net start 服务名

net stop 服务名

mysql [-h 主机名 -P 端口号] -u 用户名 -p密码

exit

六、DQL(数据查询)语言

#进阶1：基础查询/*语法：select 查询列表 from 表名；(MySql 不区分大小写)

特点：

1、查询列表为：表中的字段、常量值、表达式和函数

2、查询结果是一个虚拟的表格*/#启动制定的库(安全)USEmyemployees;

#查询表中的单个字段SELECT last_name FROMemployees;

#查询表中的多个字段SELECT last_name, salary, email FROMemployees;

#查询表中的所有字段,*代表所有的字段(字段的顺序与原始表相同)SELECT * FROMemployees;

#查询常量SELECT 100;SELECT 'a';

#查询表达式select 100%98;

#查询函数selectversion();

#更该字段的名称(1、便于理解；2、使用别名能区分重名的字段)

#方法一：使用asselect 100%98 as结果;select last_name as 姓, first_name as 名 fromemployees;

#方法二：使用空格select 100%98结果;

#特例：查询salary，显示结果为out put(这里有空格)select salary as "out put" fromemployees;

#去重DISTINCT#select department_id fromemployees;select distinct department_id fromemployees;

#+号的作用/*mysql中的+号仅表示运算符

eg. select 100 + 9;

select 'john' + 90; #试图将字符(串)123转换为数值。

如果成功，则将字符型转换为数值，继续做加法运算；

如果不成功，则将字符型转换为0；

select null + 90; null 加任何值均为null*/#案例：连接姓和名，组合成姓名，使用函数 CONCAT()select concat(last_name, first_name) as姓名fromemployees;

#显示表的结构desc employees;

#进阶2：条件查询/*语法：

select

查询列表 #查询

from

表名 #第一步

where

筛选条件 #筛选

分类：

1、按条件表达式筛选

条件运算符：>,,>=,<=

2、按照逻辑表达式筛选

逻辑运算符：and or not(&& || !)

作用：用于连接条件表达式

3、模糊查询：like、between and、in、is null*/#按条件表达式筛选

#案例1、查询工资>12000的员工信息SELECT * FROM employees WHERE salary > 12000;

#案例1、查询部门编号不等于90的员工名和部门编号SELECTlast_name, department_idFROMemployeesWHERE department_id <> 90;

#按照逻辑表达式筛选

#案例1、查询工资在10000-20000的员工信息SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;SELECT * FROMemployeesWHERE NOT(department_id >= 90 AND department_id <= 110) OR salary >15000;

#进阶3：模糊查询/*like、between and、in、is null、is not null

特点：1、一般和通配符搭配使用

通配符：

% 任意多个字符(包含0个字符)

_ 任意单个字符

\ 表示转义字符; ESCAPE '*' 指定一个字符为转义字符*/#案例1、查询员工名中包含字符a的员工信息，%表示通配符SELECT * FROM employees WHERE first_name LIKE '%a%' OR last_name LIKE '%a%';

#案例2、查询员工名中第三个字符为e，第五个字符为a的员工名和工资SELECT last_name, salary FROMemployeesWHERE last_name LIKE '__n_l%';

#案例3、查询员工名中第2个字符为下划线的员工名和工资(转义字符\)SELECT last_name, salary FROMemployees

#where last_name like '_\_%';WHERE last_name LIKE '_*_%' ESCAPE '*';/*between and

1、可以提高语句的简洁度；

2、左右都是闭区间

3、两个临界值不能颠倒*/#案例4、查询员工编号在100到200之间的员工信息SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;/*in

含义：用于判断某字段的值是否属于列表中的某一项

特点：1、使用in做筛选，能提高语句的简洁度

2、in列表中的值类型必须统一，或者兼容

3、IN等价于是否等于，列表中的每一项不支持通配符(通配符表示某一范围)*/#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号SELECT last_name, job_id FROMemployeesWHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');/*is null

1、 =或者<> NULL 这种写法是不对的

2、is null 或者 is not null 可以判断null*/#案例：查询奖金率不为null的员工名和奖金率SELECTlast_name,

commission_pctFROMemployeesWHEREcommission_pctIS NOT NULL;/*安全等于 <=>

既可以判断null值，又可以判断普通的数值

可读性较is null低*/

SELECTlast_name,

salaryFROMemployees

#WHERE commission_pct <=> NULL;WHERE salary <=> 12000;

#查询员工号为176的员工的姓名、部门号和年薪SELECTlast_name,

department_id,

salary*12*(1+IFNULL(commission_pct,0)) AS年薪FROMemployeesWHERE department_id = 100;

#案例SELECT *

FROMemployeesWHERE job_id <> 'IT' OR salary = 12000;

#查询表的结构信息DESCdepartments;

#查询部门departments中涉及到哪些位置编号SELECT DISTINCT location_id FROMdepartments;

#面试题：语句1和语句2的结果是否相同

#语句1SELECT * FROMemployees;

#语句2SELECT * FROM employees WHERE last_name LIKE '%';

#答案：不相同，因为通配符% 不能匹配null的情况

注意：比较一下 is null 和<=>

普通类型的数值 null值 可读性is nullno yes yes<=> yes yes no

七、排序查询

一、语法

select 查询列表

from 表

[where 筛选条件]

order by 排序列表 [asc|desc](默认为升序)

二、特点

asc表示升序，desc代表降序，默认为升序

order by子句支持单个字段、多个字段、表达式、函数和别名

order by子句放在查询语句的最后面，limit子句除外

#案例1：查询员工信息，从高到低排序SELECT *

FROMemployeesORDER BY salary DESC;

#案例2：查询部门编号>=90的员工信息，按入职时间的先后排序SELECT *

FROMemployeesWHERE department_id >= 90

ORDER BY hiredate ASC;

#案例3：按照年薪的高低显示员工的信息和年薪(按照表达式或者别名排序)SELECT *, salary * 12 * (1+IFNULL(commission_pct, 0)) AS年薪FROMemployees

#order by salary * 12 * (1+IFNULL(commission_pct, 0)) desc; #表达式ORDER BY 年薪 DESC; #别名

#查询4：查询员工信息，要求先按工资升序，再按员工编号降序(按多个字段排序)SELECT *

FROMemployeesORDER BY salary ASC, employee_id DESC;

#测试1：查询员工的姓名、部门号和年薪，按照年薪降序、姓名升序SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)) 年薪FROMemployeesORDER BY 年薪 DESC, last_name ASC;

#测试2：选择工资不在8000到17000的员工的姓名和工资，按工资降序SELECTlast_name, salaryFROMemployees

#where not (salary>= 8000 and salary<=17000)WHERE NOT salary BETWEEN 8000 AND 17000

ORDER BY salary DESC;

#测试3：查询邮箱中包含e的员工信息，并先按照邮箱的字节数降序，再按部门号升序

#length()函数用于求字节数SELECT *, LENGTH(email)FROMemployeesWHERE email LIKE '%e%'

ORDER BY LENGTH(email) DESC, department_id ASC;

八、常见函数

1、好处：提高重用性和隐藏实现细节

2、调用：select 函数名(实参列表) [from 表]

3、单行函数：字符函数、数学函数、日期函数、流程控制函数

#length(utf8下，一个字母占一个字节，一个汉字占三个字节)：获取参数值的字节个数

#select length('john');SELECT LENGTH('张三丰');

#concat 拼接字符SELECT CONCAT(last_name, '_', first_name)FROMemployees;

#upper()|lower()SELECT UPPER('john');SELECT LOWER('JOHN');

#substr：获取子串(索引从1开始到字符串尾的所有字符)SELECT SUBSTR('Xia Zhenbin', 5) out_put;SELECT SUBSTR('Xia Zhenbin', 3, 3) out_put;

#instr：返回子串第一次出现的起始索引，如果找不到返回0SELECT INSTR('我尹柳霞是好人', '尹柳霞') ASout_put;

#trim：去掉指定的字符SELECT LENGTH(TRIM('xia')) ASout_put;SELECT TRIM('&' FROM '&&&&&&&&夏振斌&&&&&&&') ASout_put;

#lpad：用指定的字符进行左填充SELECT LPAD('Xia', 10,'*') ASout_put;

#rpadSELECT RPAD('Xia', 10, '*') ASout_put;

#替换函数SELECT REPLACE('XiazhenbinXiazhenbinXiazhenbn', 'Xia', 'Handsome') ASout_put;

#round：四舍五入SELECT ROUND(-2.46);SELECT ROUND(-2.467, 2);

#ceil：向上取整，返回>=该参数的最小整数SELECT CEIL(1.2);

#floor：向下取整SELECT FLOOR(-1.2);

#truncate：截断SELECT TRUNCATE(2.877777, 1); #2.8#mod取余 mod(a, b): a-a/b*bSELECT MOD(-10, -3);

#日期函数

#now 返回当前系统日期+时间SELECTNOW();

#curdate 返回当前系统的日期(不包含时间)SELECTCURDATE();

#curtime 返回当前系统时间SELECTCURTIME();

#获取指定的部分SELECT YEAR(NOW());SELECT MONTH(NOW());SELECT DAY(NOW());SELECTHOUR(NOW());

#str_to_date 将字符通过指定的格式转换为日期SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d') ASout_put;SELECT * FROM employees WHERE hiredate = STR_TO_DATE('1992-4-3', '%Y-%m-%d');

#date_format：将日期转换为字符/*%m: 01、02、...11、12

%c: 1、2、3..、11、12*/

SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') ASout_put;

#其他函数SELECTVERSION(); #查看版本号SELECT DATABASE(); #查看库SELECT USER(); #查看使用的用户

#五 流程控制函数/*一、if 函数

二、使用一

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1(语句时要加分号;)

when 常量2 then 要显示的值2或语句2

when 常量3 then 要显示的值3或语句3

...

else 要显示的值N或语句N

end

使用二

case

when 条件1 then 要显示的值1或语句1

when 条件2 then 要显示的值2或语句2

when 条件3 then 要显示的值3或语句3

...

else 要显示的值N或语句N

end*/#if

SELECT IF(10<2, '大', '小');

#case函数的使用(使用一)SELECTsalary 原始工资, department_id,CASEdepartment_idWHEN 30 THEN salary * 1.1

WHEN 40 THEN salary * 1.2

WHEN 50 THEN salary * 1.3

ELSEsalaryEND AS新工资 #相当于表达式FROMemployees;

#case函数的使用(使用二)SELECTsalary,CASE

WHEN salary > 20000 THEN 'A'

WHEN salary > 15000 THEN 'B'

WHEN salary > 10000 THEN 'C'

ELSE 'D'

END AS工资级别FROMemployees;

#习题1：显示系统时间(日期+时间)SELECTNOW();

#习题2：查询员工号、姓名、工资以及工资提高百分之20后的结果(new salary)SELECT employee_id, last_name, salary, salary*1.2 AS 'new salary'

FROMemployees;

#习题3：将员工的姓名按照首字母排序，并写出姓名的长度SELECT last_name, LENGTH(last_name), SUBSTR(last_name, 1, 1) AS首字母FROMemployeesORDER BY 首字母 DESC;

九、分组函数

#分组函数/*功能：用作统计使用，又称为聚合函数或统计函数

分类：sum、avg、max、min、count

特点：

1、sum、avg一般用于处理数值型

max、min、count可以处理任何类型

2、以上分组函数都会忽略null

3、可以和distinct搭配使用

4、一般使用COUNT(*)用来统计行数

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

SELECT SUM(salary) FROMemployees;SELECT AVG(salary) FROMemployees;SELECT MAX(salary) FROMemployees;SELECT MIN(salary) FROMemployees;SELECT COUNT(salary) FROMemployees;SELECT COUNT(salary) FROMemployees;SELECT SUM(salary) 和, ROUND(AVG(salary), 3) 平均值, MAX(salary) 最大值, MIN(salary) 最小值, COUNT(salary) 共计FROMemployees;SELECT SUM(DISTINCT salary), SUM(salary) FROMemployees;SELECT COUNT(DISTINCT salary), COUNT(salary) FROMemployees;

#count函数的详细介绍SELECT COUNT(salary) FROMemployees;SELECT COUNT(*) FROMemployees; #统计行数(同一行的不同列 只要有一个非null 就能统计上)SELECT COUNT(1) FROMemployees; #统计行数

#效率

#MYISAM存储引擎下，COUNT(*)的效率高

#INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，但比COUNT(字段)要高

#查询员工表中的最大入职时间和最小入职时间的相差天数(DATEDIFF)SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) 差值FROMemployees;

#查询部门编号为90的员工个数SELECT COUNT(*)FROMemployeesWHERE department_id = 90;

十、分组查询

#进阶5：分组查询/*group by

功能：将表中的数据分成若干组

语法：

select 分组函数(max min)，列(要求出现在group by的后面)

from 表

[where 筛选条件]

group by 分组的列表

[order by 子句]

注意：

查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：

1、分组查询中的筛选条件分为两类

原始源 位置 关键字

分组前筛选 原始表 group by子句的前面 where

分组后筛选 分组后的结果 group by子句的后面 having

1、分组函数作条件肯定是放在having子句中

2、能用分组前筛选的，就优先考虑使用分组前筛选

2、group by子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开没有顺序要求)、表达式或函数

3、排序 放到最后*/#简单的分组查询

#案例1：查询每个工种的最高工资SELECT MAX(salary), job_idFROMemployeesGROUP BYjob_id;

#案例2：查询每个位置上的部门个数`myemployees`SELECT COUNT(*), location_idFROMdepartmentsGROUP BYlocation_id;

#添加筛选条件select AVG(salary), department_idfromemployeeswhere email like '%a%' and department_id is not null

group bydepartment_id;

#案例2：查询有奖金的每个领导手下员工的最高工资select max(salary), manager_idfromemployeeswhere commission_pct is not null

group bymanager_id;

#添加分组后的筛选

#案例1：查询哪个部门的员工个数>2(分组后进行筛选)select count(*) as部门员工个数, department_idfromemployeesGroup bydepartment_idHaving count(*) > 20;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资select max(salary), job_idfromemployeeswhere commission_pct is not null

group byjob_idhaving max(salary) > 12000;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资select min(salary), manager_idfromemployeeswhere manager_id > 102

group bymanager_idhaving min(salary) > 5000;

#测试：查询所有部门的编号，员工数量和工资平均值，并按平均工资降序select avg(salary), count(*), department_idfromemployeesgroup bydepartment_idorder by avg(salary) desc;

#选择各个job_id的员工人数select count(*) 数量, job_idfromemployeesgroup by job_id;

十一、连接查询

#进阶6：连接查询/*含义：又称为多表查询，当要查询的字段来自于多个表

笛卡尔乘积：表1：m行；表2：n行； 结果 = m*n行

产生原因：没有有效的连接条件

如何避免；添加有效的连接条件

分类；

按年代分类：

sql92标准：仅仅支持内连接

sql99标准 [推荐]：支持内连接+外连接(左外和右外)+交叉连接

按功能分类：

内连接：

等值连接：多表等值连接结果为多表的交集；多表的顺序没有要求；为了避免命名冲突，一般会起别名

非等值连接

自连接

外连接：

左外连接

右外连接

全外连接

交叉连接*/#sql92标准

#内连接-等值连接

#案例1SELECT NAME, boyName FROMboys, beautyWHERE beauty.boyfriend_id =boys.id;

#案例2：查询员工名和对应的部门名SELECTlast_name, department_nameFROMemployees, departmentsWHERE employees.department_id =departments.department_id;

#案例3：查询员工名、工种号、工种名SELECTlast_name, employees.job_id, job_titleFROMemployees, jobsWHERE jobs.job_id =employees.job_id;

#加筛选

#案例1：查询有奖金的员工名、部门名(筛选)SELECTlast_name, department_name, commission_pctFROMemployees e, departments dWHERE e.`department_id` =d.`department_id`AND e.`commission_pct` IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名SELECTdepartment_name, cityFROMdepartments d, locations lWHERE d.`location_id` =l.`location_id`AND city LIKE '_o%';

#加分组

#案例1：查询每个城市的部门个数SELECT COUNT(*) 部门数, cityFROMdepartments d, locations lWHERE d.`location_id` =l.`location_id`GROUP BYcity;

#查询每个工种的工种名和员工的个数，并且按员工个数降序SELECT job_title, COUNT(*) 个数FROMemployees e, jobs jWHERE e.`job_id` =j.`job_id`GROUP BYe.`job_id`ORDER BY 个数 DESC;

#多表连接

#案例：查询员工名、部门名和所在的城市SELECTlast_name, department_name, cityFROMemployees e, departments d, locations lWHERE e.`department_id` =d.`department_id`AND d.`location_id` =l.`location_id`;

#非等值连接

#先创建job_grades表CREATE TABLEjob_grades

(grade_levelVARCHAR(3),

lowest_salINT,

highest_salINT);INSERT INTOjob_gradesVALUES ('A',1000,2999);INSERT INTOjob_gradesVALUES ('B',3000,5999);INSERT INTOjob_gradesVALUES ('C',6000,9999);INSERT INTOjob_gradesVALUES ('D',10000,14999);INSERT INTOjob_gradesVALUES ('E',15000,24999);INSERT INTOjob_gradesVALUES ('F',25000,40000);SELECT * FROMjob_grades;

#案例1：查询员工的工资和工资级别(非等值连接)SELECTsalary, grade_levelFROMemployees e, job_grades j

#where e.salary between j.`lowest_sal` andj.`highest_sal`;WHERE e.salary >= j.`lowest_sal` AND e.salary <=j.`highest_sal`ORDER BY e.`salary` DESC;

#自连接(自己连接自己)

#案例：查询员工名和上级的名称SELECTe1.last_name 员工, e1.employee_id, e2.last_name 老板, e2.employee_idFROMemployees e1, employees e2WHERE e1.`manager_id` =e2.`employee_id`;

#测试：查询员工表中的job_id中包含a和e的,并且a在e的前面SELECTjob_idFROMemployeesWHERE job_id LIKE '%a%e%';

#测试：显示当前日期，以及去除前后空格，截取子字符串的函数SELECTDATE(NOW());SELECT REPLACE('Xia Zhen Bin', ' ','');SELECT TRIM(' ' FROM 'Xia Zhen Bin'); #去除首尾的空格SELECT LTRIM(RTRIM('Xia Zhen Bin'));SELECT TRIM('8 Xia Zhen Bin');