mysql练习语句

来源：互联网发布：淘宝卖家延迟收货时间编辑：程序博客网时间：2024/05/01 01:59

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'
)

0 0