MySQL-DAY01

1 数据库概述及数据准备

1.1 SQL、DB、DBMS

• SQL（Structured Query Language，结构化查询语句），一般发音为sequel，SQL用来与数据库打交道，完成和数据库的通信，SQL是一套标准。

  • 标准的SQL适用于所有的数据库产品

  • SQL属于高级语言

  • SQL语句在执行时，实际上内部会先进行编译，然后再执行SQL。（SQL语句的编译由DBMS完成）

  • 但是每一个数据库都有自己的特性别的数据库没有，当使用这个数据库特性相关的功能，这时SQL语句可能就不是标准了。(90%以上的SQL都是通用的)

• DB（DataBase，数据库），通常是一个或一组文件，保存了一些符合特定规格的数据。

  • 数据库实际上在硬盘上以文件的形式存在。

• DBMS（DataBase Management System，数据库管理系统），数据库软件，如：Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql。

1.2 表

表（table）：是一种结构化的文件，可以用来存储特定类型的数据。是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。

一个表包括：列、行、主键。

• 列：被称为字段（column）

  • 每个字段包括：字段名称/字段数据类型/字段约束/字段长度

• 行：被称为数据/记录（data）

学生信息表

1.3 SQL的分类

DQL（Data Query Language，数据查询语言）：查询语句，凡是select语句都是DQL。

DML（Data Manipulation Language，数据操作语言）：insert、delete、update，对表中的数据进行增删改。

DDL（Data Definition Language，数据定义语言）：create、drop、alter，对表结构的增删改。

TCL（Transactional Control Language，事务控制语言）：commit提交事务，rollback回滚事务。

DCL（Data Control Language，数据控制语言）：grant授权，revoke撤销授权。

1.4 导入数据

1. 连接MySQL

   mysql -uroot -p******

2. 查看有哪些数据库

   show databases;

3. 创建my数据库

   create database mytest;

4. 选择数据库，使用my数据

   use mytest;

5. 查看当前使用的数据库中有哪些表

   show tables;

6. 初始化数据

   source D:\ME\development\MySQL\myemployees.sql

7. 删除数据库

   drop database mytest;

• 当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。

• 注意：直接使用source命令可以执行sql脚本。

• sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

2 常用命令

2.1 查看msyql版本

• MySQL程序选项具有以下两种通用形式：

  • 长选项，由单词之前加两个减号组成

  • 短选项，由单个大写字母之前加一个减号组成

2.2 创建数据库

1. create database 数据库名称;

create database mytest;

2. use 数据库名称

use mytest;

在数据库中建立表，因此创建表的时候必须要先选择数据库。

2.3 查询当前使用的数据库

select database();

查询数据库版本也可以使用

select version();

2.4 终止一条语句

如果想要终止一条正在编写的语句，可键入\c。

2.5 退出mysql

可使用\q、QUIT或EXIT：

3 查看“演示数据”的表结构

3.1 查看和指定现有的数据库

3.2 指定当前缺省数据库

3.3 查看当前使用的库

3.4 查看当前库中的表

3.5 查看其他库中的表

show tables from <databasename>;

如查看test库中的表

3.6 查看表的结构

desc <tablename>;

如：

3.7 查看表的创建语句

show create table <tablename>;

如：

4 简单的查询

4.1 查询一个字段

• 查询员工姓名

select ename from emp;

Select语句后面跟的是字段名称，select是关键字，select和字段名称之间采用空格隔开，from表示将要查询的表，它和字段之间采用空格隔开。

4.2 查询多个字段

• 查询员工的编号和姓名

select empno,ename from emp;

查询多个字段，select中的字段采用逗号间隔即可，最后一个字段，也就是在from前面的字段不能使用逗号了。

4.3 查询全部字段

可以将所有的字段放到select语句的后面查询，但这种方案不方便，但是比较清楚。也可以采用如下便捷方式查询全部字段。

select * from emp;

采用select * from emp，虽然简单，但是 * 号不是很明确，建议查询全部字段将相关字段写到select语句的后面，在以后java连接数据库的时候，是需要在java程序中编写SQL语句的，这个时候编写的SQL语句不建议使用select * 这种形式，建议写明字段，这样可读性强。

4.4 计算员工的年薪

• 列出员工的编号，姓名和年薪

select empno,ename,sal*12 from emp;

在select语句中可以使用运算符，以上存在一些问题，年薪的字段名称不太明确。

4.5 将查询出来的字段显示为中文

select empno as ‘员工编号’,ename as ‘员工姓名’,sal*12 as ‘年薪’ from emp;

注意：字符串必须添加单引号|双引号

可以采用as关键字重命名表字段，其实as也可以省略，如：

select empno "员工编号",ename "员工姓名",sal*12 "年薪" from emp;

5 条件查询

条件查询需要用到where语句，where必须放到from语句表的后面。

语法格式： select 字段,字段... from 表名 where 条件;

执行顺序：先from，然后where，最后select

支持如下运算符

Mysql默认情况下大小写是不敏感的。

注意：

MySQL在windows下是不区分大小写的，将script文件导入MySQL后表名也会自动转化为小写，结果再想要将数据库导出放到linux服务器中使用时就出错了。因为在linux下表名区分大小写而找不到表，查了很多都是说在linux下更改MySQL的设置使其也不区分大小写，但是有没有办法反过来让windows下大小写敏感呢。其实方法是一样的，相应的更改windows中MySQL的设置就行了。

具体操作：

在MySQL的配置文件my.ini中增加一行：

lower_case_table_names=0

其中0：区分大小写，1：不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

　　1、数据库名与表名是严格区分大小写的；

　　2、表的别名是严格区分大小写的；

　　3、列名与列的别名在所有的情况下均是忽略大小写的；

　　4、变量名也是严格区分大小写的；MySQL在Windows下都不区分大小写

6 排序数据

6.1 单一字段排序

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序。如果存在where子句，那么order by必须放到where语句的后面。

• 按照薪水由小到大排序(系统默认由小到大)

select * from emp order by sal;

• 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)

select * from emp where job=‘MANAGER‘  order by sal;  

如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面。

• 按照多个字段排序，如：首先按照job排序，再按照sal排序

select * from emp order by job,sal;  

6.2 手动指定排序顺序

• 手动指定按照薪水由小到大排序

select * from emp order by sal asc;  

• 手动指定按照薪水由大到小排序

select * from emp order by sal desc;  

6.3 多个字段排序

• 按照job和薪水倒序

select * from emp order by job desc, sal desc;  

如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

6.4 使用字段的位置来排序

• 按照薪水升序

select * from emp order by 6;  

不建议使用此种方式，采用数字含义不明确，程序不健壮。

7 分组函数/聚合函数/多行处理函数

• 所有的分组函数都是对“某一组”数据进行操作的。

• 分组函数还有另一个名字：多行处理函数。

  • 多行处理函数的特点：输入多行，最终输出的结果是1行。

• 注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。

  • select count(*) from emp where xxx; 符合条件的所有记录总数。

  • select count(comm) from emp; comm这个字段中不为空的元素总数。

• 注意：分组函数不能直接使用在where关键字后面。

  • mysql> select ename,sal from emp where sal > avg(sal);

  • ERROR 1111 (HY000): Invalid use of group function

• comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

  • select sum(sal+IFNULL(comm, 0)) from emp;

  • ifnull(可能为NULL的数据,被当做什么处理) ：属于单行处理函数（输入一行，输出一行）。

• 取得最晚入职得员工

  • select max(str_to_date (hiredate, ‘%Y-%m-%d‘)) from emp;

• 取得最早入职得员工（可以不使用str_to_date转换）

  • select min(str_to_date(hiredate, ‘%Y-%m-%d‘)) from emp;

• 组合聚合函数，可以将这些聚合函数都放到select中一起使用

  • select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

8 分组查询

分组查询主要涉及到两个子句，分别是：group by和having

• group by：按照某个字段或者某些字段进行分组。

• having：对分组之后的数据进行再次过滤。

8.1 group by

• 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

select job, sum(sal) from emp group by job; 

如果使用了order by，order by必须放到group by后面。

• 注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。

  并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。

  当一条sql语句没有group by的话，整张表的数据会自成一组。

• 按照工作岗位和部门编码分组，取得的工资合计

select job,deptno,sum(sal) from emp group by job,deptno; 

在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。

8.2 having

如果想对分组数据再进行过滤需要使用having子句

• 取得每个岗位的平均工资大于2000

select job, avg(sal) from emp group by job having avg(sal) >2000; 

分组函数的执行顺序：

1. 根据条件查询数据

2. 分组

3. 采用having过滤，取得正确的数据

8.3 select语句总结

一个完整的select语句格式如下

select 字段  
from 表名  
where …….  
group by ……..  
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)  
order by ……..  

以上语句的执行顺序

1. 首先执行from找到表

2. 执行where语句过滤原始数据

3. 执行group by进行分组

4. 执行having对分组数据进行操作

5. 执行select选出数据

6. 执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

MySQL-DAY01

原文：https://www.cnblogs.com/winterriver/p/12484562.html