Mysql基础
mysql数据库:
1.mysql数据库?数据库的操作系统?数据库的操作语言?
数据库DB:用来持久化的存储操作数据。database数据库
mysql,oracle,sqlserver…
DBMS:数据库的管理系统。
sql语言:用来操作数据库中数据的一种语言。
4.登录数据库
本地方式:
mysql -uroot -p1234
mysql -uroot -p ---敲回车
登陆后的效果:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.56 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
5.数据库的常用命令:
5.1 显示所有的库:(不要删除修改操作information_schema和mysql和performance_schema)
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5.2.创建一个数据库
create database mydb;
Query OK, 1 row affected (0.00 sec)
5.3 删除一个数据库
drop database mydb;
5.4所有的mysql操作语法之后(默认方式)添加分号结束,
5.5.退出mysql
exit
5.6.选择数据库,进入到库中
use +库名;
结果:
mysql> use bjpowernode;
Database changed
5.7.库中存储的是表:
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
6.库中的表,用来真正的存数据的,数据库中的最基本的单位:
对于表中的一行称之为一条记录
对于表中的一列称之为一个字段
7.将sql语言划分:
DDL: 对结构操作: create,drop
DML: 对数据的操作:增加insert,删除delete,修改update
DQL: 对数据的操作: 查询select
TCL: 事务的操作
DCL: 权限的操作
8.导入准备好表:
source 表的路径
mysql> source D:\course\javaEE\1msyql\bjpowernode.sql
注意: 路径中不要出现中文
显示库中所有的表:
show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept 部门表 |
| emp 员工表 |
| salgrade 薪水等级表 |
+-----------------------+
显示表的结构:
desc emp;
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2) | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
表中的字段,每个字段的类型(整数,小数,布尔值,字符串,时间....),字段的约束
9.查询的操作:
1.单表查询:
简单查询:select 字段名称 from 表名;(见名知意)
1.1 单个字段查询
select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
1.2 多个字段查询
select ename,sal from emp;
select sal,ename from emp;
select ename,sal,deptno from emp;
1.3 查询整张表
(推荐)
select empno,ename,job,mgr,hiredate,sal,deptno from emp;
简化的方式:
select * from emp;
1.4 为字段添加一个别名 as 可以省略
方式一
select empno as '编号',ename as '姓名',job,mgr,hiredate,sal,deptno from emp;
方式二
select empno '编号',ename '姓名',job,mgr,hiredate,sal,deptno from emp;
方式三:
在mysql中可以用的,但是这个不是标准sql语句写法,如果调整了数据库可能会报错
select empno "编号",ename "姓名",job,mgr,hiredate,sal,deptno from emp;
注意:所有的select语句都不会改变表,只是显示使用
2.条件查询:对数据进行筛选,筛选出来符合条件
语法: select 字段名称 from 表名称 where 条件;
2.1 = 等于
select * from emp where ename='JONES';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
select * from emp where sal=3000;
2.2 <>或!= 不等于
select * from emp where deptno!=20;
select * from emp where deptno<>20;
2.3 < 小于
select * from emp where sal <4000;
2.4 <= 小于等于
select * from emp where sal <=1500;
2.5 > 大于
select * from emp where sal >1500;
2.6 >= 大于等于
select * from emp where sal >=1500;
2.7 between …and …. 两个值之间,等同于 >= and <=
select * from emp where sal between 1100 and 2850;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 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 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
select * from emp where sal between 2850 and 1100;
Empty set (0.00 sec) 有数据语法问题查不到
mysql> select * from emp where sal between 28500 and 51000;
Empty set (0.00 sec) 没有符合的数据
between之后是小的数据 and之后是大的数据
2.8 is null 为 null(is not null 不为空)
select * from emp where comm = null;
Empty set (0.00 sec)
select * from emp where comm is null;
+-------+--------+-----------+------+------------+---------+------+--------+
| 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 |
| 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 |
| 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 where comm is not null;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
2.9 and 并且 和的意思
select * from emp where deptno = 20 and sal >1500;
连接多个条件,只有所有的条件全部成立才是符合的数据。
2.10 or 或者
select * from emp where deptno = 20 or sal >1500;
连接多个条件,只要满足一个条件就是符合的数据。
2.11 and和or放在一起使用(符号的优先级问题),使用小括号来完成优先级的区分
需求:查询部门编号为30的部门 并且 工资大于1000或工资小于4000?
select * from emp where deptno = 30 and sal > 1000 or sal <4000; //不是需求的
select * from emp where sal > 1000 or sal <4000 and deptno = 30;//不是需求的
select * from emp where deptno = 30 and (sal > 1000 or sal <4000);
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
6 rows in set (0.00 sec)
2.12 in 包含,相当于多个 or(not in 不在这个范围中)
语法: in(条件值,多个);
select * from emp where sal = 950 or sal =1500 or sal = 2850;
+-------+--------+----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
select * from emp where sal in(950,1500,2850,1250);
2.13 not in
select * from emp where sal not in(950,1500,2850,1250);
2.13 not not 可以取非,
主要用在 is 或 in 中
2.14 like like 称为模糊查询,
支持%或下划线匹配
% 匹配0到多个字符
下划线,一个下划线只匹配一个字符
需求:
查询所有姓名以S开头的员工
select * from emp where ename like 'S%';
查询所有姓名包含S的员工
select * from emp where ename like '%S%';
查询所有姓名以S开头的员工,名称的字符个数是5个的
select * from emp where ename like 'S___';
注意:如果匹配规则_或%会和匹配数据中的_或%产生识别上冲突
需求:查询学生姓名中包含_的所有学生:
select * from t_student where name like '%_%'; //所有的学生,只要有任意一个字符
mysql> select * from t_student where name like '%\_%';
+----+-----------+------+
| no | name | cno |
+----+-----------+------+
| 5 | zhang_san | 101 |
+----+-----------+------+
1 row in set (0.00 sec)
3.排序查询:
正序,倒叙 order by
mysql中默认的排列是正序,所有asc关键字可以省略
以一个字段进行排序:
按照工资的正序排列:
select * from emp order by sal;
select * from emp order by sal asc;
按照工资的倒序排列:
select * from emp order by sal desc;
以两个字段,更多字段进行排序:(以一个字段如果数据一致了)
首先以工资进行正序的排列,如果工资相同了在以姓名的倒叙对相同工资
的员工进行倒叙排列;
select * from emp order by sal asc,ename desc;
select
字段名
from
表名
where
条件
order by
字段名;
select * from emp where deptno != 10 order by sal asc,empno desc;
4.聚合查询:
在sql语句中的位置:
出现在select关键字之后
出现在having关键字之后
max:最大值
min:最小值
sum:和
avg:平均数
count:统计数量
统计员工的工资和:
select sum(sal) from emp;
统计员工的平均工资:
select avg(sal) from emp;
统计员工的最高工资:
select max(sal) from emp;
统计员工的最低工资:
select min(sal) from emp;
统计员工的人数:(一般情况下,以主键进行统计,也可以使用*来统计效率最低)
select count(empno) from emp;
一起使用:
select sum(sal) as '和',avg(sal) as '平均数',max(sal) as '最大值', min(sal) as '最小值' ,count(empno) as '统计数量' from emp;
5.内置函数查询:
6.分组查询:
**
单行函数处理
**
**
trim()
**
**
ifnull()
**
*****注意事项**
分组函数不能用在where后面
分组函数
**
分组查询
**
**
执行顺序
**
TEST group by +sum()
**
注意事项
**
**
having
**