MySql
========================================
安装
------------------------------
1. 用安装文件直接 setup
2. 绿色版
1) 解压缩到 c:\
2) 将 mysql 安装为系统服务
开始->运行-> cmd
-------------
c:
cd \
cd mysql-5.5.11-win32
cd bin
mysqld -nt-install mysql
-------------
Service successfully installed.
3) 启动 mysql 服务
net start mysql
---------------------
mysql 服务正在启动 .
mysql 服务已经启动成功。
mysql 客户端工具
---------------------------------------------
窗口工具:
Navicat
mysql front
命令行:
c:
cd \
cd mysql-5.5.11-win32
cd bin
mysql -uroot -p
password:空密码,直接回车
mysql>
查看数据库列表
-----------------------------------
mysql> show databases;
切换到指定的库
-----------------------------------
mysql> use test;
查看库中的数据表
-----------------------------------
mysql> show tables;
查看指定表的表结构 desc - descript
-----------------------------------
mysql> desc tbluser;
执行脚本文件
-----------------------------------
mysql> source d:\hr_mysql.sql
sql 语句
==========================================
* Structured Query Language
结构化的查询语言
* 关系型数据库的一种规范的数据操作语句
* 分类:
SELECT - 查询
DML - 操作: 增删改
DDL - 定义
SELECT
===================================
where 子句
--------------------------------
=
<>
>
>=
<
<=
between 小 and 大
in (值1,值2,值3...)
is null
like 模糊查询
% - 通配0到多个任意字符
_ - 统配单个任意字符
not 关键字
----------------
is not null
not between ... and ...
not in
not like
or
and
order by 子句
-------------------------------
* 以指定字段的值排序
* 升序: asc - ascend
* 降序: desc - descend
* order by 字段1
order by 字段1 asc,字段2 desc
先按 字段1 排序,字段1相同的数据再按 字段2 排序
distinct
-------------------------------
* 去除重复值
select distinct 字段列表 from ....
* 多个字段,组合不重复
字段别名
=====================================
* select
字段1 as 别名1,
表达式2 别名2
* as 关键字可省略
* where 不支持字段别名
* order by 支持字段别名
函数
=====================================
字符串
char_length('a中') - 字符数
length('a中') - 字节数
concat('a','b','cde','fff') - 字符串连接
concat_ws('####','abc','def','ggg') - 用分隔符连接字符串
instr('abcdefgdef','def') - 返回第一个子串的位置,从1开始,找不到返回0
locate('abc', '---abc---abc---abc-') - 返回第一个子串的位置,从1开始,找不到返回0
locate('abc', '---abc---abc---abc-',5) - 从指定位置向后找
insert('abcdefghijkl',2, 11, '---') - 用子串取代从2位置开始的11个字符
lower('AdFfLJf') - 变为小写
upper('AdFfLJf') - 变为大写
left('AdFfLJf',3) - 返回最左边的三个字符
right('AdFfLJf',3) - 返回最右边的三个字符
lpad('abc', 8, '*') - 左侧填充,指定长度比源字符串少,相当于left
rpad('abc', 8, '*') - 右侧填充,指定长度比源字符串少,相当于left
trim(' a bc ') - 去除两端空格
substring('abcdefghijklmn', 3) - 从3位置开始的所有字符
substring('abcdefghijklmn', 3, 6) - 从3位置开始的6个字符
repeat('abc', 3) - 重复三遍abc
REPLACE('Hello MySql','My','Your') - 子串替换
REVERSE('Hello') - 翻转字符串
SPACE(10) - 返回10个空格
数字
floor(3.94) - 向下取整
format(391.536, 2) - 数字格式化为字符串,###,###.###,四舍五入,第二个参数为小数位数
round(673.4974) - 四舍五入--> 673
round(673.4974, 2) - 四舍五入到小数点后两位--> 673.50
round(673.4974, -2) - 四舍五入到百--> 700
TRUNCATE(234,37, 1) - 舍去至小数点后1位-->234.3
日期
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE(时间) 提取日期或日期/时间表达式的日期部分
TIME(时间) 提取日期或日期/时间表达式的时间部分
EXTRACT(字段 From 日期) 返回日期/时间按的单独部分
字段的合法值:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DATE_ADD(日期, INTERVAL 数量 字段) 给日期添加指定的时间间隔
字段的合法值同上
DATE_SUB(日期, INTERVAL 数量 字段) 从日期减去指定的时间间隔
DATEDIFF(日期1, 日期2) 返回两个日期之间的天数
DATE_FORMAT(日期, 格式) 用不同的格式显示日期/时间
格式字符:
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(01-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位
STR_TO_DATE(字符串, 格式) - 字符串解析为日期
LAST_DAY(日期) - 返回当月最后一天
NULL 相关
IFNULL(数据1,数据2) - 数据1是null返回数据2;不是null返回数据1
coalesce(数据1,数据2,......) - 从左向右第一个不是null的数据
多行函数
MAX(字段) - 最大值
MIN(字段) - 最小值
SUM(字段) - 求和
AVG(字段) - 求平均
COUNT(字段或*) - 对字段数据计数,*计算数据行数
多行函数
=================================
* 不能直接与其他字段一起查询
* 会忽略 null 值
* count
count(字段) 指定字段有值部分计数
count(*) 行数
count(distinct 字段) 指定字段有值部分重复值只记一次
group by 子句
------------------------------------
* 与多行函数一起使用
* 按指定字段的相同值分组执行多行函数
group by 字段1
group by 字段1,字段2
having 子句
---------------------------------------
* 一定跟在 group by 之后,
* 对分组后的多行函数结果做过滤
* 普通过滤条件,放在 where 后
* having 可用多行函数结果别名
条件子查询
=====================================
* 查询结果,作为另一查询的过滤条件值
* 单值子查询
=
>
>=
<
<=
* 多值子查询
in
> all
> any
< all
< any
练习
==========================================
1. 创建数据表 stu
C:\mysql-5.5.11-win32\bin>mysql -uroot -p
password:
mysql> use test;
create table stu(
id int,
name varchar(16),
gender char(1),
birthday date
);
show tables;
desc stu;
2. 在 stu 表插入数据
insert into stu
values(4343,'zhangsan','M','1983-2-12');
insert into stu
values(42,'lisi','M','1983-9-23');
insert into stu
values(855,'wangwu','F','1985-11-20');
3. 查询 stu 表的所有数据
select * from stu;
4. 修改 stu 表的数据
update stu set
name='zhaoliu',
birthday='2014-5-14';
select * from stu;
5. 删除 stu 表的数据
delete from stu;
select * from stu;
6. 员工表
show databases;
use hr;
show tables;
desc employees;
select * from employees;
7. 查询指定的字段
select
salary,
first_name,
employee_id
from
employees;
8. 查询 50 部门的员工
select
salary,
first_name,
employee_id,
department_id
from
employees
where
department_id=50;
9. 查询 90 部门的员工
select *
from employees
where department_id=90;
10. employee_id 是 100 的员工
select *
from employees
where employee_id=100;
11. 薪水 salary 大于 10000 的员工
select *
from employees
where salary>10000;
12. 排除 50 部门,查询其他所有员工
select *
from employees
where department_id<>50;
13. first_name 是 'Steven' 的员工
select *
from employees
where first_name='Steven';
14. 50 部门中薪水低于 3000 的员工
select *
from employees
where
department_id=50
and salary<3000;
15. 90、100、10、20 部门的员工
select *
from employees
where
department_id=50
or department_id=100
or department_id=10
or department_id=20;
16. 60 部门中薪水低于5000的员工,
和 job_id 是 'AD_PRES' 的员工
select *
from employees
where
(department_id=60
and salary<5000)
or job_id='AD_PRES';
17. 薪水在 5000 到 8000 范围内的员工
select *
from employees
where
salary between 5000 and 8000;
18. 入职时间 hire_date 从 '2000-1-1' 到 '2000-12-31'
select *
from employees
where
hire_date between '2000-1-1' and '2000-12-31'
19. job_id 是 'IT_PROG'、'AD_PRES'、'AD_VP'
select *
from employees
where
job_id in('IT_PROG','AD_PRES','AD_VP');
20. 90、100、10、20 部门的员工
select *
from employees
where
department_id in(90,100,10,20);
21. department_id 是 null,即不属于任何部门的员工
select *
from employees
where
department_id is null;
22. 有销售提成 commission_pct 的员工
commission_pct 不是 null
select *
from employees
where
commission_pct is not null;
23. 工资 < 3000 和 > 13000 的员工
select *
from employees
where
salary not between 3000 and 13000;
24. 排除 30,50,80 部门,查询其他部门员工
select *
from employees
where
department_id not in(30, 50, 80);
25. first_name 或 last_name 中包含 'en' 字符
select *
from employees
where
first_name like '%en%'
or last_name like '%en%';
26. first_name 中第2、3两个字符是 'en'
select *
from employees
where
first_name like '_en%';
27. 排除所有文员 'CLERK'
select *
from employees
where
job_id NOT like '%CLERK';
28. 所有员工以 salary 字段升序排列
select *
from employees
order by salary;
29. 50部门员工,以工资降序排列
select *
from employees
where
department_id=50
order by salary desc;
30. 以部门排序,相同部门以薪水降序排序
select *
from employees
order by department_id, salary desc;
31. 以 first_name 排序,相同的再按 last_name 排序
select *
from employees
order by first_name, last_name;
31. 查询所有部门的id
select distinct department_id
from employees
where department_id is not null;
32. 每个部门中都有那些岗位
select distinct department_id, job_id
from employees
order by department_id;
33. 所有主管的 id
select distinct manager_id
from employees;
34. 每个部门中的主管id
select distinct department_id, manager_id
from employees;
35. 字符串函数测试
select char_length('a中');
select length('a中');
select concat('a','b','cde','fff','fff');
select concat_ws('####','abc','def','ggg');
select instr('abcdefgdef','def');
select locate('abc', '---abc---abc---abc-');
select locate('abc', '---abc---abc---abc-',5);
select insert('abcdefghijkl',2, 11, '---');
select lower('AdFfLJf');
select upper('AdFfLJf');
select left('AdFfLJf',3);
select right('AdFfLJf',3);
select lpad('abc', 8, '*');
select rpad('abc', 8, '*');
select trim(' a bc ');
select substring('abcdefghijklmn', 3, 6);
select repeat('abc', 3);
select replace('Hello MySql','My','Your');
select REVERSE('Hello');
select SPACE(10);
36. first_name, last_name 中第二个字符相等
select *
from employees
where
substring(first_name, 2, 1)=
substring(last_name, 2, 1);
37. first_name, last_name 长度相同
select *
from employees
where
char_length(first_name)=
char_length(last_name);
38. 人名电影字幕
select
concat(lpad(first_name,20,' '), ' ', last_name) as name
from employees;
39. 数字函数测试
select floor(3.94);
select floor(-3.94);
select format(391.536, 2);
select round(673.4974);
select round(673.4974, 2);
select round(673.4974, -2);
select TRUNCATE(234.37, 1);
40. 涨工资 11.73%,四舍五入保留到百位,
涨薪之后,工资低于3000的
select
employee_id,
first_name,
salary,
round(salary*1.1173, -2) new_salary
from employees
where round(salary*1.1173, -2)<3000
order by new_salary;
41. 日期函数测试
select NOW();
select CURDATE();
select CURTIME();
select DATE(now());
select TIME(now()) ;
select EXTRACT(YEAR from now());
select EXTRACT(MONTH from now());
select EXTRACT(DAY from now());
select DATE_ADD(now(), INTERVAL 1 year);
select DATE_ADD(now(), INTERVAL -365 day);
select DATEDIFF(now(), '1989-12-9');
select DATE_FORMAT(now(), '%d/%m/%Y %H:%i:%s');
select STR_TO_DATE('9/12/1989','%d/%m/%Y');
select last_day(now());
42. 1998年下半年入职
select *
from employees
where
extract(year from hire_date)=1998
and extract(month from hire_date)>6
--------------------
hire_date between '1998-7-1' and '1998-12-31'
43. 2000年全年入职
select *
from employees
where
extract(year from hire_date)=2000
44. 2000年1月入职
select *
from employees
where
extract(year from hire_date)=2000
and extract(month from hire_date)=1
45. 所有在 1 月入职的员工
select *
from employees
where
extract(month from hire_date)=1
46. 入职满 20 年
select *
from employees
where
hire_date<date_add(now(), interval -20 year);
47. null 相关函数测试
select ifnull(1, 2); 1
select ifnull(null, 2); 2
select coalesce(1,2,3,4,5); 1
select coalesce(null,null,3,4,5); 3
48. 计算年薪
select
employee_id,
first_name,
salary,
salary*12*(1+ifnull(commission_pct,0)) sal
from
employees;
49. 多行函数测试
select
max(salary),
min(salary),
sum(salary),
avg(salary),
count(salary)
from
employees;
50. 对 commission_pct 字段的值计数
select count(commission_pct)
from employees;
select
avg(commission_pct),
sum(commission_pct)/count(commission_pct)
from employees;
51. 对部门 department_id 字段计数
select count(distinct department_id)
from employees;
52. 50部门有多少人
select count(*)
from employees
where department_id=50;
53. 2000年入职的人数
select count(*)
from employees
where extract(year from hire_date)=2000;
54. 员工 122 以及他的手下,
包括122自己,包括122的手下,
总共人数
select count(*)
from employees
where
employee_id=122
or manager_id=122;
55. 每个部门的人数
select department_id, count(*)
from employees
where department_id is not null
group by department_id
56. 每个部门 department_id 每个岗位 job_id 的人数
select department_id,job_id, count(*)
from employees
where department_id is not null
group by department_id,job_id
57. 只有一个人的部门
select department_id, count(*) c
from employees
where department_id is not null
group by department_id
having c=1;
58. 每个岗位 job_id 的平均工资
select job_id, avg(salary) sal
from employees
group by job_id
order by sal
59. 平均工资低于5000的岗位
select job_id, avg(salary) sal
from employees
group by job_id
having sal<5000
order by sal
60. 每年入职人数
select
extract(year from hire_date) year,
count(*) c
from
employees
group by
year
order by c;
61. 入职人数少于10人的年份
select
extract(year from hire_date) year,
count(*) c
from
employees
group by
year
having c<10
order by c;
62. 拿最高工资的人
select max(salary) from employees
select *
from employees
where salary=(
select max(salary) from employees
)
63. 大于平均工资的员工
select *
from employees
where salary>(
select avg(salary) from employees
)
64. 入职时间最晚的员工
select *
from employees
where hire_date=(
select max(hire_date) from employees
)
65. 平均工资比所有人平均工资高的岗位
select job_id,avg(salary) sal
from employees
group by job_id
having sal>(
select avg(salary) from employees
)
order by sal
66. 做 65 查询出来的岗位的员工
select *
from employees
where job_id in (
select job_id
from employees
group by job_id
having avg(salary)>(
select avg(salary) from employees
)
)
67. 比所有程序员(job_id='IT_PROG')工资低的员工
select *
from employees
where salary < all (
select salary from employees
where job_id='IT_PROG'
)
68. 比任意程序员(job_id='IT_PROG')工资低的员工
select *
from employees
where salary < any (
select salary from employees
where job_id='IT_PROG'
)