数据库mysql的基本操作命令(全)

数据库mysql的基本操作

1.1 如何进入mysql? mysql的基本操作命令
//先进入mysql文件所在的bin目录下
cd C:\mysql-5.7.15-winx64\bin


//进入mysql
mysql -uroot -p123456
//列出所有数据库名
show database;

//使用、进入一个数据库
use 数据库名;

//查看所有的表
show tables;

//退出mysql
exit;

//创建一个数据库
create database 数据库名;

//导入表 注意:路径不能有中文
source D:\mysql_ceshi\表名.sql

source C:\coursedb.sql
//查询dept表中的所有数据
select * from dept;

//查询emp表中的所有数据
select * from emp;

//查询emp表的关键信息
describe emp;
缩写:
desc emp;

//查询dept表的关键信息
describe dept;
缩写:
desc dept;
//查看当前数据库版本
mysql> select version();

//查看当前使用的是哪个数据库
mysql> select database();

//删除数据库(表)
第一步,先退出
exit;
第二步,再进入
mysql -uroot -p123456
第三步,列出所有数据库(表)
show databases;、
第四步,选择要删除的库(表)
drop database 数据库名(或表名);

1.2 简单查询
--进入mysql
 mysql -uroot -p123456

--创建新数据库
 create database ceshiku;
 
--进入新创建的数据库
 use ceshiku;

--导入新表
 source D:\mysql_ceshi\ceshibiao.sql

--列出当前数据库的所有表名
 show tables;
 显示:
 +-------------------+
| Tables_in_ceshiku |
+-------------------+
| dept              |
| emp               |
+-------------------+
 
--查询全部数据
 select * from emp;
 显示:
 +-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

--查询某个表的某个字段(单个信息)
 select 字段 from 表名;
 
--查询emp表的empno字段的信息
 mysql> select empno from emp;
 显示:
 +-------+
| empno |
+-------+
|  7369 |
|  7499 |
|  7521 |
|  7566 |
|  7654 |
|  7698 |
|  7782 |
|  7788 |
|  7839 |
|  7844 |
|  7876 |
|  7900 |
|  7902 |
|  7934 |
+-------+

--查询某个表的某些字段(多个信息)
 select 字段名,字段名,字段名,字段名 from 表名;

--查询emp表的empno,ename,job,mg字段的信息
 select empno,ename,job,mgr from emp;
 显示:
 +-------+--------+-----------+------+
| empno | ename  | job       | mgr  |
+-------+--------+-----------+------+
|  7369 | SMITH  | CLERK     | 7902 |
|  7499 | ALLEN  | SALESMAN  | 7698 |
|  7521 | WARD   | SALESMAN  | 7698 |
|  7566 | JONES  | MANAGER   | 7839 |
|  7654 | MARTIN | SALESMAN  | 7698 |
|  7698 | BLAKE  | MANAGER   | 7839 |
|  7782 | CLARK  | MANAGER   | 7839 |
|  7788 | SCOTT  | ANALYST   | 7566 |
|  7839 | KING   | PRESIDENT | NULL |
|  7844 | TURNER | SALESMAN  | 7698 |
|  7876 | ADAMS  | CLERK     | 7788 |
|  7900 | JAMES  | CLERK     | 7698 |
|  7902 | FORD   | ANALYST   | 7566 |
|  7934 | MILLER | CLERK     | 7782 |
+-------+--------+-----------+------+
1.3 起别名
--给字段起别名,并查询该字段的内容(as可省略)
 select 字段名 as 别名 from emp;
 
--给ename字段起别名为empname,并查询ename字段的内容
 select ename as empname from emp;
 
--别名中含有空格,需要用单引号扩起来
 select ename as 'emp ename' from emp;
 显示:
 +-----------+
| emp ename |
+-----------+
| SMITH     |
| ALLEN     |
| WARD      |
| JONES     |
| MARTIN    |
| BLAKE     |
| CLARK     |
| SCOTT     |
| KING      |
| TURNER    |
| ADAMS     |
| JAMES     |
| FORD      |
| MILLER    |
+-----------+

--给多个字段同时起别名,并查询这些字段的内容(使用逗号隔开即可)
 select job as 'emp job',empno as 'emp empno' from emp;
 显示:
 +-----------+-----------+
| emp job   | emp empno |
+-----------+-----------+
| CLERK     |      7369 |
| SALESMAN  |      7499 |
| SALESMAN  |      7521 |
| MANAGER   |      7566 |
| SALESMAN  |      7654 |
| MANAGER   |      7698 |
| MANAGER   |      7782 |
| ANALYST   |      7788 |
| PRESIDENT |      7839 |
| SALESMAN  |      7844 |
| CLERK     |      7876 |
| CLERK     |      7900 |
| ANALYST   |      7902 |
| CLERK     |      7934 |
+-----------+-----------+
1.4 字段计算
--对emp表的sal(月薪)字段进行计算,得到员工的年薪,并对年薪起别名为year sal
 select sal*12 as 'year sal' from emp;
 显示:
 +----------+
| year sal |
+----------+
|  9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+

--可以把‘year sal’改为中文的“年薪”
 select sal*12 as 年薪 from emp;
1.5 条件查询大于小于
--列出某个表的字段1,字段2,字段3;条件是 字段3>99999
 select 字段1,字段2,字段3 from 某表表名 where 字段3>99999;

--查询月薪大于等于1000的员工,并列出员工号,员工姓名,月薪
 select empno,ename,sal from emp where sal>=1000;
 显示:
 +-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+

--查询月薪在800到1000的员工,并列出员工号,员工姓名,月薪
方法一(字段3>=800 and 字段3<=800):
 select empno,ename,sal from emp where sal>=800 and sal<=1000;
 
方法二(字段3 between 800 and 1000):
 mysql> select empno,ename,sal from emp where sal between 800 and 1000;
显示:
 +-------+-------+--------+
| empno | ename | sal    |
+-------+-------+--------+
|  7369 | SMITH | 800.00 |
|  7900 | JAMES | 950.00 |
+-------+-------+--------+
1.6 is null (注意:null在数据库中与在Java语言中是不同的,不能直接用=)
--查询所有没有津贴的员工(也就是comm值为null),并列出员工号,员工姓名,月薪
 select empno,ename,sal from emp where comm is null;
 
--查询所有有津贴的员工(也就是comm值不为null),并列出员工号,员工姓名,月薪
 select empno,ename,sal from emp where comm is not null;
 
--错误范例
 select empno,ename,sal from emp where comm=null;
 原因:null在数据库中与在Java语言中是不同的,不能直接用等号判断!
1.7 or (注意:and前后要是两个不同字段才能使用,若是同字段要使用or)
--查询中包含多个条件(where……and……)

--查询职位是manager,月薪大于1000的员工,并列出员工号,员工姓名,月薪
  select empno,ename,sal from emp where job='manager' and sal>1000;

--查询职位是manager和clerk的员工,并列出员工号,员工姓名,月薪
 select empno,ename,sal from emp where job='manager' or job='clerk';

--查询职位是manager和clerk,月薪大于1000的员工,并列出员工号,员工姓名,月薪,职位
 select empno,ename,sal,job from emp where job='manager' or job='clerk' and sal>1000;
 显示:
 +-------+--------+---------+---------+
| empno | ename  | sal     | job     |
+-------+--------+---------+---------+
|  7566 | JONES  | 2975.00 | MANAGER |
|  7698 | BLAKE  | 2850.00 | MANAGER |
|  7782 | CLARK  | 2450.00 | MANAGER |
|  7876 | ADAMS  | 1100.00 | CLERK   |
|  7934 | MILLER | 1300.00 | CLERK   |
+-------+--------+---------+---------+

--错误范例
 select empno,ename,sal from emp where job='manager' and job='clerk';
 原因:and前后要是两个不同字段才能使用,若是同字段要使用or!
1.8 and优先级高于or(系统会先执行用and连接的条件,再执行or后的条件)
--查询出部门10与部门20的经理,并列出两位经理的所有信息(deptno是部门编码的意思)
 select * from emp where (deptno=10 or deptno=20) and job='manager';
 显示:
 +-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+------------+---------+------+--------+

--错误范例一:
 select * from emp where job='manager' and deptno=10 or deptno=20;
 显示:
 +-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
原因:and优先级高于or,系统会先执行完job='manager' and deptno=10,再执行or deptno=20!
其含义是:查询出部门10的经理信息,和查询出部门20所有员工的信息!
	
--错误范例二:
 select * from emp where deptno=10 or deptno=20 and job='manager';
 其含义是:查询出部门20的经理信息,和查询出部门10所有员工的信息!
 
--错误范例三:
 select * from emp where deptno=10 and deptno=20 or job='manager';
 显示:
 +-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+------------+---------+------+--------+
 原因:and前后必须是两个不同字段,系统会直接忽视deptno=10 and deptno=20,只执行job='manager'
 其含义是:查询出所有的经理信息!
1.9 in
--查询某表中,某字段值为9999的所有有关信息
 select * from 某表表名 where 某字段名 in(9999);
 
--查询出部门10所有员工的信息
 select * from emp where deptno in(10);
 等效于:
 select * from emp where deptno=10;
 显示:
 +-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+

--查询某表中,某字段值为10或20或30的所有有关信息
 select * from 某表表名 where 某字段名 in(10,20,30);
 
--查询出部门10与部门20的所有员工信息
 select * from emp where deptno in(10,20);
 显示:
 +-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
1.10 not in
--查询除了部门20与部门10的其他员工信息
 select * from emp where deptno not in(20,3
  • 6
    点赞
  • 42
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值