MySQL数据库常用命令

--创建数据库
mysql> create database dept;
Query OK, 1 row affected (0.05 sec)
--切换数据库
mysql> use dept;
Database changed
--创建表
mysql> create table dept(
     deptno int(4) primary key ,
     dname varchar(20),
     loc varchar(20)
     );
Query OK, 0 rows affected (0.09 sec)
--查看表结构（另一种方式查看脚本：show create table detp）
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(4)      | NO   | PRI | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
--创建表，注意行级定义和表级定义的使用
mysql> create table emp (
    empno int(4) not null primary key,
    ename varchar(20),job varchar(20),
    mgr int(4),
    hiredate datetime,
    sal double(10,2),
    comm double(10,2),
    deptno int(4),
    constraint emp_dept_fk foreign key(deptno) references dept(deptno)
    );
Query OK, 0 rows affected (0.42 sec)
--查看表结构
mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   | PRI | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| job      | varchar(20)  | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | datetime     | YES  |     | NULL    |       |
| sal      | double(10,2) | YES  |     | NULL    |       |
| comm     | double(10,2) | YES  |     | NULL    |       |
| deptno   | int(4)       | YES  | MUL | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
--插入数据
mysql> insert into dept values(10,'accounting','beijing');
Query OK, 1 row affected (0.06 sec)
--插入数据
mysql> insert into emp values(7782,'clark','manager',7839,'1988-12-17',2400,0,10);
Query OK, 1 row affected (0.06 sec)
--查看所有数据
mysql> select * from dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|     10 | accounting | beijing |
+--------+------------+---------+
1 row in set (0.02 sec)
--查询所有数据
mysql> select * from emp;
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | clark | manager | 7839 | 1988-12-17 00:00:00 | 2400.00 | 0.00 |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)


--删除所有数据
mysql> delete from emp;
Query OK, 1 row affected (0.06 sec)
--删除所有数据
mysql> delete from dept;
Query OK, 1 row affected (0.17 sec)
--导入数据
mysql> load data infile 'D:/aheadetp基地实训相关/java11期教学/mysql/dep_.txt' into table dept;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
--查询所有数据
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | nanchang |
|     30 | sales      | shanghai |
|     40 | operations | shenzhen |
+--------+------------+----------+


7 rows in set (0.00 sec)
--从外表文件导入表数据
mysql> load data infile 'D:/aheadetp基地实训相关/java11期教学/mysql/emp_.txt' into table emp;
Query OK, 14 rows affected (0.09 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    0.00 |     20 |
|  7469 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1200.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-01 00:00:00 | 2800.00 |    0.00 |     20 |
|  7654 | martin | salesman  | 7698 | 1981-08-29 00:00:00 | 1200.00 |    0.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2800.00 | 1400.00 |     30 |
|  7782 | clark  | manager   | 7839 | 1988-12-17 00:00:00 | 2400.00 |    0.00 |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    0.00 |     20 |
|  7839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1800.00 |    0.00 |     30 |
|  7876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1300.00 |    0.00 |     20 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 | 1300.00 |    0.00 |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    0.00 |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |   55.00 |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.02 sec)


mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(4)      | NO   | PRI | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)


mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | nanchang |
|     30 | sales      | shanghai |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)
--统计表的记录总数
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)
--查询某个字段数据，并取消重复行
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
3 rows in set (0.00 sec)
--条件查询，名字为'smith'
mysql> select sal,job,deptno from emp where ename='smith'
    -> ;
+--------+-------+--------+
| sal    | job   | deptno |
+--------+-------+--------+
| 800.00 | clerk |     20 |
+--------+-------+--------+
1 row in set (0.00 sec)
--查询年薪（注意null值）
mysql> select ename,sal*12+comm from emp;
+--------+-------------+
| ename  | sal*12+comm |
+--------+-------------+
| smith  |     9600.00 |
| allen  |    19500.00 |
| ward   |    14900.00 |
| jones  |    33600.00 |
| martin |    14400.00 |
| blake  |    35000.00 |
| clark  |    28800.00 |
| scott  |    36000.00 |
| king   |        NULL |
| turner |    21600.00 |
| adams  |    15600.00 |
| james  |    15600.00 |
| ford   |    36000.00 |
| miller |    15655.00 |
+--------+-------------+
14 rows in set (0.00 sec)
--查询年薪（注意null值）
mysql> select ename,sal*12+ifnull(comm,0) from emp;
+--------+-----------------------+
| ename  | sal*12+ifnull(comm,0) |
+--------+-----------------------+
| smith  |               9600.00 |
| allen  |              19500.00 |
| ward   |              14900.00 |
| jones  |              33600.00 |
| martin |              14400.00 |
| blake  |              35000.00 |
| clark  |              28800.00 |
| scott  |              36000.00 |
| king   |              60000.00 |
| turner |              21600.00 |
| adams  |              15600.00 |
| james  |              15600.00 |
| ford   |              36000.00 |
| miller |              15655.00 |
+--------+-----------------------+
14 rows in set (0.00 sec)
--使用字段别名“年薪”
mysql> select ename,sal*12+ifnull(comm,0) 年工资 from emp;
+--------+----------+
| ename  | 年工资   |
+--------+----------+
| smith  |  9600.00 |
| allen  | 19500.00 |
| ward   | 14900.00 |
| jones  | 33600.00 |
| martin | 14400.00 |
| blake  | 35000.00 |
| clark  | 28800.00 |
| scott  | 36000.00 |
| king   | 60000.00 |
| turner | 21600.00 |
| adams  | 15600.00 |
| james  | 15600.00 |
| ford   | 36000.00 |
| miller | 15655.00 |
+--------+----------+
14 rows in set (0.02 sec)
--查询emp表中工资高于3000员工信息
mysql> select * from emp where sal>3000;
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7839 | king  | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
--查询入职晚于'1982-01-01'的员工信息
mysql> select * from emp where hiredate>'1982-01-01';
+-------+--------+---------+------+---------------------+---------+-------+--------+
| empno | ename  | job     | mgr  | hiredate            | sal     | comm  | deptno |
+-------+--------+---------+------+---------------------+---------+-------+--------+
|  7782 | clark  | manager | 7839 | 1988-12-17 00:00:00 | 2400.00 |  0.00 |     10 |
|  7788 | scott  | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 |  0.00 |     20 |
|  7876 | adams  | clerk   | 7788 | 1987-05-23 00:00:00 | 1300.00 |  0.00 |     20 |
|  7934 | miller | clerk   | 7782 | 1982-01-23 00:00:00 | 1300.00 | 55.00 |     10 |
+-------+--------+---------+------+---------------------+---------+-------+--------+
4 rows in set (0.00 sec)
--查询入职早于'1982-01-01'的员工信息
mysql> select * from emp where hiredate<'1982-01-01';
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    0.00 |     20 |
|  7469 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1200.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-01 00:00:00 | 2800.00 |    0.00 |     20 |
|  7654 | martin | salesman  | 7698 | 1981-08-29 00:00:00 | 1200.00 |    0.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2800.00 | 1400.00 |     30 |
|  7839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1800.00 |    0.00 |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 | 1300.00 |    0.00 |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    0.00 |     20 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
10 rows in set (0.00 sec)
--查询薪水在2000—2500之间的员工信息（使用between... and ...）
mysql> select * from emp where sal between 2000 and 2500;
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | clark | manager | 7839 | 1988-12-17 00:00:00 | 2400.00 | 0.00 |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
--查询名字里面含义's'的，员工的名字和工资
mysql> select ename,sal from emp where ename like '%s%';
+-------+---------+
| ename | sal     |
+-------+---------+
| smith |  800.00 |
| scott | 3000.00 |
+-------+---------+
2 rows in set (0.00 sec)
--查询员工名字中第三个字母是'o'的名字，工资
mysql> select ename ,sal from emp where ename like '__o%';
+-------+---------+
| ename | sal     |
+-------+---------+
| scott | 3000.00 |
+-------+---------+
1 row in set (0.00 sec)
--查询员工编码存在于(123,7782,234,7900)中的信息
mysql> select * from emp where empno in (123,7782,234,7900)
    -> ;
Empty set (0.00 sec)
--查询员工编码存在于(123,345,7782)中的信息
mysql> select * from emp where empno in (123,345,7782);
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | clark | manager | 7839 | 1988-12-17 00:00:00 | 2400.00 | 0.00 |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
--查询员工编码存在于(123,7900,7782)中的信息
mysql> select * from emp where empno in (123,7900,7782);
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | clark | manager | 7839 | 1988-12-17 00:00:00 | 2400.00 | 0.00 |     10 |
|  7900 | james | clerk   | 7698 | 1981-12-03 00:00:00 | 1300.00 | 0.00 |     30 |
+-------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
--查询没有上级的员工（错误用法）
mysql> select * from emp where mgr=null;
Empty set (0.00 sec)
--查询没有上级的员工（正确用法）
mysql> select * from emp where mgr is null;
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7839 | king  | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
--查询工资高于500或者职位是manager，且名字以j开头的员工信息（歧义用法）
mysql> select * from emp where sal>500 or job='manager' and ename like 'j%';
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    0.00 |     20 |
|  7469 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1200.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-01 00:00:00 | 2800.00 |    0.00 |     20 |
|  7654 | martin | salesman  | 7698 | 1981-08-29 00:00:00 | 1200.00 |    0.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2800.00 | 1400.00 |     30 |
|  7782 | clark  | manager   | 7839 | 1988-12-17 00:00:00 | 2400.00 |    0.00 |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    0.00 |     20 |
|  7839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1800.00 |    0.00 |     30 |
|  7876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1300.00 |    0.00 |     20 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 | 1300.00 |    0.00 |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    0.00 |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |   55.00 |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
--查询工资高于500或者职位是manager，且名字以j开头的员工信息（正确用法）
mysql> select * from emp where (sal>500 or job='manager') and ename like 'j%';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7566 | jones | manager | 7839 | 1981-04-01 00:00:00 | 2800.00 | 0.00 |     20 |
|  7900 | james | clerk   | 7698 | 1981-12-03 00:00:00 | 1300.00 | 0.00 |     30 |
+-------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
--查询所有员工信息，按工资降序排列
mysql> select * from emp order by sal desc;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    0.00 |     20 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    0.00 |     20 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2800.00 | 1400.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-01 00:00:00 | 2800.00 |    0.00 |     20 |
|  7782 | clark  | manager   | 7839 | 1988-12-17 00:00:00 | 2400.00 |    0.00 |     10 |
|  7844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1800.00 |    0.00 |     30 |
|  7469 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 | 1300.00 |    0.00 |     30 |
|  7876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1300.00 |    0.00 |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |   55.00 |     10 |
|  7654 | martin | salesman  | 7698 | 1981-08-29 00:00:00 | 1200.00 |    0.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1200.00 |  500.00 |     30 |
|  7369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    0.00 |     20 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
--查询员工信息，部门号按升序排列，工资按降序排
mysql> select ename ,deptno,sal from emp order by deptno asc,sal desc;
+--------+--------+---------+
| ename  | deptno | sal     |
+--------+--------+---------+
| king   |     10 | 5000.00 |
| clark  |     10 | 2400.00 |
| miller |     10 | 1300.00 |
| ford   |     20 | 3000.00 |
| scott  |     20 | 3000.00 |
| jones  |     20 | 2800.00 |
| adams  |     20 | 1300.00 |
| smith  |     20 |  800.00 |
| blake  |     30 | 2800.00 |
| turner |     30 | 1800.00 |
| allen  |     30 | 1600.00 |
| james  |     30 | 1300.00 |
| martin |     30 | 1200.00 |
| ward   |     30 | 1200.00 |
+--------+--------+---------+
14 rows in set (0.00 sec)


--查询员工的年薪，按升序排列
mysql> select ename,sal*12+ifnull(comm,0) as 年薪 from emp order by 年薪;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| smith  |  9600.00 |
| martin | 14400.00 |
| ward   | 14900.00 |
| james  | 15600.00 |
| adams  | 15600.00 |
| miller | 15655.00 |
| allen  | 19500.00 |
| turner | 21600.00 |
| clark  | 28800.00 |
| jones  | 33600.00 |
| blake  | 35000.00 |
| ford   | 36000.00 |
| scott  | 36000.00 |
| king   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)