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:coursejavaEE1msyqlbjpowernode.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.分组查询:
**
单行函数处理**
**
**
**
ifnull()**
*****注意事项**
分组函数不能用在where后面
**
分组查询**
**
**
TEST group by +sum()
**
**
