Msql学习-例子
2019-01-27 本文已影响263人
Jayss_987
所需要用到的创建表格的代码如下：
create table dept
(
deptno numeric(2),
dname varchar(14),
loc varchar(13),
constraint pk_dept primary key(deptno)
);
create table emp
(
empno numeric(4),
ename varchar(10),
job varchar(9),
mgr numeric(4),
hiredate date,
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2),
constraint pk_emp primary key(empno),
constraint fk_deptno foreign key(deptno) references dept(deptno)
);
insert into dept values(10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-02-02',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
insert into emp
values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
表视图：
emp表结构
emp表视图
dept表结构
dept表视图
Mysql简单select句子
列出管理MySQL数据库服务的常用命令：
Systemctl start/stop/status/restart mysql
#用root用户连接数据库，查询当前有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#创建db数据库，执行scott.sql脚本文件，创建测试表emp和dept
mysql> create database db;
Query OK, 1 row affected (0.07 sec)
mysql> use db
Database changed
mysql> source scott.sql
#连接db数据库，查询db数据库中有哪些表
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| dept |
| emp |
+--------------+
2 rows in set (0.00 sec)
#查看emp和dept表的结构。
mysql> desc dept;（查询表dept结构）
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| deptno | decimal(2,0) | NO | PRI | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> desc emp;（查询表emp结构）
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | decimal(4,0) | NO | PRI | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | decimal(4,0) | YES | | NULL | |
| hiredate | datetime | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | decimal(2,0) | YES | MUL | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
#连接db数据库，查询emp表中的所有人的姓名。
mysql> select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| KING |
| TURNER |
| JAMES |
| FORD |
| MILLER |
+--------+
12 rows in set (0.00 sec)
#用别名把上面查询结果中的列名ename改为汉字：员工名称。
mysql> select ename as 员工名称；
-> from emp;
+-----------------+
| 员工名称； |
+-----------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| KING |
| TURNER |
| JAMES |
| FORD |
| MILLER |
+-----------------+
12 rows in set (0.00 sec)
#查询emp表中的不重复的部门号。
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
3 rows in set (0.00 sec)
#查询emp表中，工资额大于2000的员工的姓名及其工资额。
mysql> select ename,sal from emp where sal > 2000;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
#查询emp表中，工资额界于2000与3000之间的员工姓名及其工资额。
方法一：
mysql> select ename,sal from emp where sal >= 2000 and sal <= 3000;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| FORD | 3000.00 |
+-------+---------+
4 rows in set (0.00 sec)
方法二：
mysql> select ename,sal from emp where sal between 2000 and 3000;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| FORD | 3000.00 |
+-------+---------+
4 rows in set (0.00 sec)
#查询emp表中，ename列以字母A开头的员工的姓名。
mysql> select ename,sal from emp where ename like 'A%';
+-------+---------+
| ename | sal |
+-------+---------+
| ALLEN | 1600.00 |
+-------+---------+
1 row in set (0.00 sec)
#查询emp表中，ename列含有字母A的员工的姓名。
mysql> select ename,sal from emp where ename like '%A%';
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| JAMES | 950.00 |
+--------+---------+
6 rows in set (0.00 sec)
#查询emp表中，ename列第三个字母为A、第五个字母为R的员工的姓名。
mysql> select ename,sal from emp where ename like '__A_R%';
Empty set (0.00 sec)
#查询emp表中，姓名及工资额，要求工资按照降序排序。
mysql> select ename,sal(从低到高)
-> from emp
-> order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
12 rows in set (0.00 sec)
mysql> select ename,sal from emp order by sal desc;（丛高到低）
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
12 rows in set (0.00 sec)
#查询emp表中，comm列为NULL的员工的姓名。
mysql> select ename from emp where comm is null;
+--------+
| ename |
+--------+
| SMITH |
| JONES |
| BLAKE |
| CLARK |
| KING |
| JAMES |
| FORD |
| MILLER |
+--------+
8 rows in set (0.00 sec)
mysql> select ename from emp where comm is not null;
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| TURNER |
+--------+
4 rows in set (0.00 sec)
汇总函数
#查询每个部门的最高工资，要求列出部门编号及其最高工资额。
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
3 rows in set (0.00 sec)
#查询各个工种的人数。
mysql> select job 工种,count(empno) 员工人数 from emp group by job;
+-----------+--------------+
| 工种 | 员工人数 |
+-----------+--------------+
| CLERK | 3 |
| SALESMAN | 4 |
| MANAGER | 3 |
| PRESIDENT | 1 |
| ANALYST | 1 |
+-----------+--------------+
5 rows in set (0.00 sec)
#查询sal列上有无重复值，给出判断语句及判断结果。
方法一：
mysql> select sal,count(sal) from emp group by sal having count(sal);
+---------+------------+
| sal | count(sal) |
+---------+------------+
| 800.00 | 1 |
| 1600.00 | 1 |
| 1250.00 | 2 |
| 2975.00 | 1 |
| 2850.00 | 1 |
| 2450.00 | 1 |
| 5000.00 | 1 |
| 1500.00 | 1 |
| 950.00 | 1 |
| 3000.00 | 1 |
| 1300.00 | 1 |
+---------+------------+
11 rows in set (0.00 sec)
方法二：
mysql> select sal,count(*) from emp group by sal having count(sal)>1;
+---------+----------+
| sal | count(*) |
+---------+----------+
| 1250.00 | 2 |
+---------+----------+
1 row in set (0.00 sec)
#查询每个员工工资与emp表的平均工资之间的差距。
mysql> select ename,sal - (select avg(sal) from emp) from emp;
+--------+----------------------------------+
| ename | sal - (select avg(sal) from emp) |
+--------+----------------------------------+
| SMITH | -1277.083333 |
| ALLEN | -477.083333 |
| WARD | -827.083333 |
| JONES | 897.916667 |
| MARTIN | -827.083333 |
| BLAKE | 772.916667 |
| CLARK | 372.916667 |
| KING | 2922.916667 |
| TURNER | -577.083333 |
| JAMES | -1127.083333 |
| FORD | 922.916667 |
| MILLER | -777.083333 |
+--------+----------------------------------+
12 rows in set (0.00 sec)
mysql> select sal,count(*) from emp group by sal having count(sal)>1;
+---------+----------+
| sal | count(*) |
+---------+----------+
| 1250.00 | 2 |
+---------+----------+
1 row in set (0.00 sec)
#查询emp表最高工资与平均工资的差距。
mysql> select max(sal) - (select avg(sal) from emp) from emp;
+---------------------------------------+
| max(sal) - (select avg(sal) from emp) |
+---------------------------------------+
| 2922.916667 |
+---------------------------------------+
1 row in set (0.00 sec)
#查询每个员工的名称及其工种的名称。
mysql> select ename,job from emp;
+--------+-----------+
| ename | job |
+--------+-----------+
| SMITH | CLERK |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| KING | PRESIDENT |
| TURNER | SALESMAN |
| JAMES | CLERK |
| FORD | ANALYST |
| MILLER | CLERK |
+--------+-----------+
12 rows in set (0.00 sec)
#查询哪个工种的人数最多，要求列出这种工种的完整名称及其人数。
方法一：
mysql> select job,count(1) from emp group by job;
+-----------+----------+
| job | count(1) |
+-----------+----------+
| CLERK | 3 |
| SALESMAN | 4 |
| MANAGER | 3 |
| PRESIDENT | 1 |
| ANALYST | 1 |
+-----------+----------+
5 rows in set (0.00 sec)
方法二：
mysql> select job,count(*) from emp group by job order by count(*) desc limit 1;
+----------+----------+
| job | count(*) |
+----------+----------+
| SALESMAN | 4 |
+----------+----------+
1 row in set (0.00 sec)
#查询哪个部门的员工个数最多，要求列出此部门编号及其员工个数。
mysql> select deptno,count(*) from emp group by deptno order by count(*) desc limit 1;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 30 | 6 |
+--------+----------+
1 row in set (0.01 sec)
#查询每年的入职人数，要求列出相应年份及入职人数，查询结果以年份排序。
mysql> select year(hiredate),count(*) from emp group by year(hiredate) order
by year(hiredate) asc;
+----------------+----------+
| year(hiredate) | count(*) |
+----------------+----------+
| 1980 | 1 |
| 1981 | 10 |
| 1982 | 1 |
+----------------+----------+
3 rows in set (0.00 sec)
#查询哪个部门的平均工资最高，要求列出部门名称及其平均工资额。
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2258.333333 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec
#查询哪些工种的平均工资高于2000，要求列出工种名称及其平均工资额。
mysql> select avg(sal),job from emp group by job having avg(sal)>2000;
+-------------+-----------+
| avg(sal) | job |
+-------------+-----------+
| 2758.333333 | MANAGER |
| 5000.000000 | PRESIDENT |
| 3000.000000 | ANALYST |
+-------------+-----------+
3 rows in set (0.00 sec)
#利用find命令(或其他命令)，查询mysql和mysqld在哪个目录下。
Mysql：
[root@law ~]# find / -iname mysql
/etc/logrotate.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/mysql
Mysqld：
[root@law ~]# find / -iname mysqld
/run/mysqld
/usr/sbin/mysqld
查询
# 查询每个部门分别是哪个员工获得了其所在部门的最高工资，要求列出其名称、部门名称及其工资额。
select e.ename,dept.dname,a.max_sal
from
emp e,
dept,
(select deptno,max(sal) as max_sal from emp group by deptno) a
where
e.deptno = dept.deptno
and
a.deptno = e.deptno
and
a.max_sal = e.sal
结果：
+-------+------------+---------+
| ename | dname | max_sal |
+-------+------------+---------+
| KING | ACCOUNTING | 5000.00 |
| FORD | RESEARCH | 3000.00 |
| BLAKE | SALES | 2850.00 |
+-------+------------+---------+
3 rows in set (0.00 sec)
#查询工资超过平均工资的员工个数
mysql> select count(sal) from emp where sal>(select avg(sal) from emp); +------------+
| count(sal) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
#查询每个部门中，超过部门平均工资的员工姓名。
select deptno,ename from emp
where
sal>(select avg(sal) from emp where emp.deptno = deptno)
结果：
+--------+-------+
| deptno | ename |
+--------+-------+
| 20 | JONES |
| 30 | BLAKE |
| 10 | CLARK |
| 10 | KING |
| 20 | FORD |
+--------+-------+
5 rows in set (0.00 sec)
索引
#在emp表的ename列创建索引idx_ename，确认索引已创建。
mysql> create index idx_ename on emp(ename);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from emp\G;
*************************** 1. row ***************************
Table: emp
Non_unique: 1
Key_name: idx_ename
Seq_in_index: 1
Column_name: ename
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
1 row in set (0.00 sec)
ERROR:
No query specified
#执行下面命令对emp表添加一行记录：
mysql> insert into emp(empno, ename, sal, deptno)
-> values(9999, 'MIKE', 3000.00, 10);
重新执行show index from emp，查看索引的Cardinality是否变化，说明了什么问题。执行下面命令对emp表重新计算统计信息，再次查询其Cardinality是否变化。
mysql> analyze table emp;
重新分析
#查看下面查询的执行计划，确认其是否使用了idx_ename索引。
#select * from emp where ename = 'SMITH'
mysql> explain select * from emp where ename = 'SMITH'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
possible_keys: idx_ename
key: idx_ename
key_len: 43
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
#select * from emp where ename like 'S%'
mysql> explain select * from emp where ename like 'S%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: range
possible_keys: idx_ename
key: idx_ename
key_len: 43
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.04 sec)
ERROR:
No query specified
#select * from emp where ename like '%S'
mysql> explain select * from emp where ename like '%S'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 13
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
#删除idx_ename索引。
mysql> drop index idx_ename on emp;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
#执行下面命令在ename和sal列上创建复合索引idx_ename_sal，确认索引已创建。
mysql> create index idx_ename_sal on emp(ename, sal);
#下面哪个查询会使用以上idx_ename_sal。
select * from emp where ename = 'SMITH'
select * from emp where sal = 1000
select * from emp where ename = 'SMITH' and sal = 1000
select * from emp where sal = 1000 and ename = 'SMITH'
#下面命令，查看表连接的执行计划。
mysql> explain
-> select e.ename, d.dname
-> from emp e, dept d
-> where e.deptno = d.deptno
-> \G
#创建big_table存储过程后，执行call big_table(100000)，创建big_table表，并对其添加10万行记录，在id列上创建索引idx_id，下面查询是否会用到此索引：
mysql>select big_table from t where id = 1000
#执行下面update命令：
mysql>update big_table set id = 1000 where id >= 1001;
#执行下面命令重新计算big_table的统计信息：
mysql> analyze table big_table;
#再次查看下面命令是否使用了idx_id索引：
mysql>select * from big_table where id = 1000
#执行下面命令统计id列的直方图后，以上查询是否会使用索引
mysql> analyze table big_table update histogram on length with 1000 buckets;
逻辑备份
#执行select into outfile导出dept表，清空dept表后由逻辑备份执行load data导入数据。
mysql> select * from dept into outfile '/var/lib/mysql-files/dept.ckp'
-> ;
Query OK, 4 rows affected (0.00 sec)
mysql> truncate table dept;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from dept;
Empty set (0.00 sec)
mysql> edit
-> \p
--------------
load data infile '/var/lib/mysql-files/dept.ckp'
into table dept
--------------
-> ;
Query OK, 4 rows affected (0.10 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
[root@law mysql-files]# ls
dept_bak.CSV dept.bkp dept.ckp dept.sql dept.txt
[root@law mysql-files]# cat dept.ckp
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
欢迎技术交流
WeChat......
