第一章基本概述

第一章基本概述

1. 数据库（数据库管理系统）

通过简单的SQL语句便可以对数据进行增删改查，但其底层都是通过IO流对数据进行增删改查；但数据库只需几条简单的SQL语句就可以了，十分便捷

1.1 登录MySQL

进入DOS，输入命令
第一种：
mysql -uroot -p123
第二种：（密码会以*号代替）
mysql -uroot -p

1.2 修改密码

  - 方法1： 用SET PASSWORD

首先登录MySQL
格式：
mysql> set password for 用户名@localhost = password(‘新密码’);
例子：
mysql> set password for root@localhost = password(‘123’);

  - 方法2：用mysqladmin

格式：
mysqladmin -u用户名 -p旧密码 password 新密码
例子：
mysqladmin -uroot -p123456 password 123

  - 方法3：用UPDATE直接编辑user表

首先登录MySQL
mysql> use mysql;
mysql> update user set password=password(‘123’) where user=’root’ and host=’localhost’;
mysql> flush privileges;

  - 方法4：在忘记root密码的时候，可以这样

以windows为例：
1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysql\bin目录。
3. 输入mysqld —skip-grant-tables 回车。—skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
5. 输入mysql回车，如果成功，将出现MySQL提示符 >
6. 连接权限数据库： use mysql;
7. 改密码：update user set password=password(“123”) where user=”root”;（别忘了最后加分号）
8. 刷新权限（必须步骤）：flush privileges;
9. 退出 quit。
10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

1.3 卸载MySQL

1、双击安装包，点击下一步，然后点击remove，卸载
2、手动删除Program Files中的MySQL目录
3、手动删除ProgramData目录（这个目录是隐藏的）中的MySQL

2. DB、DBMS、SQL的关系

DB:
DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）
DBMS:
DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer…）
SQL:
结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。

SQL属于高级语言。SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）
DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。DBMS -(执行)-> SQL -(操作)-> DB

3. 表的概念

表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强
一个表包括行和列：行：被称为数据/记录(data)；列：被称为字段(column)
每一个字段应该包括哪些属性：字段名、数据类型、相关的约束。

4. SQL语句的分类

DQL（数据查询语言）: 查询语句，凡是select语句都是DQL
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改
DDL（数据定义语言）：create drop alter，对表结构的增删改
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等

5. 导入数据

第一步：登录mysql数据库管理系统
DOS窗口下：
mysql -uroot -p333

第二步：查看有哪些数据库
show databases; (这个不是SQL语句，属于MySQL的命令)

第三步：创建属于我们自己的数据库
create database bjpowernode; (这个不是SQL语句，属于MySQL的命令)

第四步：使用bjpowernode这个数据库的数据
use bjpowernode; (这个不是SQL语句，属于MySQL的命令。)

第五步：查看当前使用的数据库中有哪些表？
show tables; (这个不是SQL语句，属于MySQL的命令。)

第六步：初始化数据
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql

6. sql脚本

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

注意： 直接使用source命令可以执行sql脚本。sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化

7. 删除数据库：

drop database bjpowernode;

8. 查看表结构：

desc dept;

9. 常用命令

select database(); 查看当前使用的是哪个数据库
select version(); 查看mysql的版本号。
\c 命令，结束一条语句。
exit 命令，退出mysql。
show create table emp;查看创建表的语句

10. 简单的查询语句（DQL）

语法格式：

select 字段名1,字段名2,字段名3,…. from 表名;

提示： 1、任何一条sql语句以“;”结尾 2、sql语句不区分大小写

字段可以参与数学

                     select ename,sal * 12 from emp;

给查询结果的列取别名

                     select ename,sal * 12 as yearsal from emp;

别名中有中文？

             select ename,sal * 12 as 年薪 from emp; // 错误<br />                      select ename,sal * 12 as '年薪' from emp;

注意： 标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用

as关键字可以省略？可以省略但不建议，会降低代码的可读性

mysql> select empno,ename,sal * 12 yearsal from emp;

查询所有字段？实际开发中不建议使用*，效率较低

select * from emp;

找出工资不等于3000的？

select ename,sal from emp where sal <> 3000;
select ename,sal from emp where sal != 3000;

找出工资在1100和3000之间的员工，包括1100和3000？

select ename,sal from emp where sal >= 1100 and sal <= 3000;
// between…and…是闭区间 [1100 ~3000]，与>= XXX and <=XXX等价
select ename,sal from emp where sal between 1100 and 3000;
// 查询不到任何数据，between and在使用的时候必须左小右大。
select ename,sal from emp where sal between 3000 and 1100;

between and（左闭右开）除了可以使用在数字方面之外，还可以使用在字符串方面。

select ename from emp where ename between ‘A’ and ‘C’;

在数据库当中NULL不是一个值，代表什么也没有，为空；空不是一个值，不能用等号衡量，必须使用 is null或者is not null
in等同于or：select ename,job from emp where sal in(800, 5000); // in后面的值不是区间，是具体的值等价于：sal=800 or sal=5000
not in: 不在这几个值当中

10.1 模糊查询like ?

在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是；%代表任意多个字符，代表任意1个字符。
找出名字中有下划线的？通过转义字符

select name from tuser where name like ‘%\%’;

10.2 排序（升序、降序）

注意：默认是升序。怎么指定升序或者降序呢？asc表示升序，desc表示降序。

按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

select ename,sal from emp order by sal desc , ename asc;

注意： 越靠前的字段越能起到主导作用；只有当前面的字段无法完成排序的时候，才会启用后面的字段

11. 分组函数

count ：计数
sum ：求和
avg ：平均值
max ：最大值
min ：最小值

记住：所有的分组函数都是对“某一组”数据进行操作的
分组函数一共5个，分组函数还有另一个名字：多行处理函数；多行处理函数的特点：输入多行，最终输出的结果是1行。分组函数自动忽略NULL
count(*)和count(具体的某个字段)，他们有什么区别？

count(*):不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）；count(comm): 表示统计comm字段中不为NULL的数据总数量。

select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function 思考以上的错误信息：无效的使用了分组函数？原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中怎么解释？因为group by是在where执行之后才会执行的

11.1 单行处理函数

什么是单行处理函数

输入一行，输出一行

重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL
使用ifnull函数：

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

ifnull() 空处理函数？

ifnull(可能为NULL的数据,被当做什么处理) ：属于单行处理函数
select ename,ifnull(comm,0) as comm from emp;

多行处理函数其对多行进行处理，但输出只有一行，单行处理函数输入一行输出一行

11.2 group by 和 having

group by ：按照某个字段或者某些字段进行分组
having : having是对分组之后的数据进行再次过滤

注意：
分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。当一条sql语句没有group by的话，整张表的数据会自成一组。
select ename,max(sal),job from emp group by job;
以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错，语法错误；Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：
当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段

建议能够使用where过滤的尽量使用where而不要使用having，效率较高

12. DQL语句顺序：

select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..

13. 关于查询结果集的去重

mysql> select distinct job from emp; // distinct关键字去除重复记录。
mysql> select ename,distinct job from emp;

以上的sql语句是错误的
记住：distinct只能出现在所有字段的最前面

14. 连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果；在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

stuno stuname classno classname
—————————————————————————————————————————-
1 zs 1 北京大兴区亦庄经济技术开发区第二中学高三1班
2 ls 1 北京大兴区亦庄经济技术开发区第二中学高三1班
…

学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

14.1 连接查询的分类

根据语法出现的年代来划分的话，包括：SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）SQL99（比较新的语法）

根据表的连接方式来划分，包括：

内连接：
等值连接
非等值连接
自连接

外连接：
左外连接（左连接）
右外连接（右连接）

全连接（很少用）

14.2 笛卡尔积现象

在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）

当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积

关于表的别名：

select e.ename,d.dname from emp e,dept d;
表的别名有什么好处？
第一：执行效率高
第二：可读性好

怎么避免笛卡尔积现象？当然是加条件进行过滤

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？不会，次数还是56次。只不过显示的是有效记录

14.3 内连接之等值连接：

最大特点是：条件是等量关系

案例：查询每个员工的部门名称，要求显示员工名和部门名。
SQL92:（太老，不用了）
select 
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno;
SQL99：（常用的）
select 
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
// inner可以省略的，带着inner目的是可读性好一些。
select 
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
语法：
...
A
join
B
on
连接条件
where
...
SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了

14.4 内连接之非等值连接

最大的特点是：连接条件中的关系是非等量关系。

select 
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
// inner可以省略
select 
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal;

14.5 自连接：

最大的特点是：一张表看做两张表，自己连接自己

员工的领导编号 = 领导的员工编号
select 
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join
emp b
on
    a.mgr = b.empno;

14.6 外连接

什么是外连接，和内连接有什么区别？

内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。 AB两张表没有主副之分，两张表是平等的。
外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类？

左外连接（左连接）：表示左边的这张表是主表
右外连接（右连接）：表示右边的这张表是主表
左连接有右连接的写法，右连接也会有对应的左连接的写法

内连接：
select 
a.ename '员工', b.ename '领导'
from
emp a
join
emp b
on
a.mgr = b.empno;
外连接：（左外连接/左连接）
select 
a.ename '员工', b.ename '领导'
from
emp a
left join
emp b
on
a.mgr = b.empno;
// outer是可以省略的。
select 
a.ename '员工', b.ename '领导'
from
emp a
left outer join
emp b
on
a.mgr = b.empno;
外连接：（右外连接/右连接）
select 
a.ename '员工', b.ename '领导'
from
emp b
right join
emp a
on
a.mgr = b.empno;
// outer可以省略。
select 
a.ename '员工', b.ename '领导'
from
emp b
right outer join
emp a
on
    a.mgr = b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来。

14.7 三张表连接查询

….
A
join
B
join
C
on
…

表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接

select 
e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
        e.mgr = e1.empno;

15. 子查询

什么是子查询？子查询都可以出现在哪里？

select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select … (select) from … (select) where … (select)

15.1 where子句中使用子查询

select * from emp where sal > (select avg(sal) from emp);

15.2 from后面嵌套子查询

案例：找出每个部门平均薪水的等级。
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select 
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
    t.avgsal between s.losal and s.hisal;

15.3 在select后面嵌套子查询

select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;

16. union （可以将查询结果集相加）

两张不相干的表中的数据拼接在一起显示？

select ename from emp
union
select dname from dept;

mysql> select ename,sal from emp -> union -> select dname from dept; ERROR 1222 (21000): The used SELECT statements have a different number of columns

17. limit (重点中的重点，用于分页查询)

limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
limit取结果集中的部分数据，这是它的作用

语法机制：
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据，length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前5个：
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;

limit是sql语句最后执行的一个环节：

select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…;

18. 通用的标准分页sql

每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3

每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize
pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页

19. 创建表：

建表语句的语法格式：

create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
….
);
关于MySQL当中字段的数据类型？以下只说常见的

int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型（对应Java中的java.sql.Date类型）
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串） Character Large OBject（对应java中的Object）
……

char和varchar怎么选择？

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char；当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar

表名在数据库当中一般建议以：t或者tbl开始。
创建学生表： ```sql 学生信息包括：学号、姓名、性别、班级编号、生日学号：bigint 姓名：varchar 性别：char 班级编号：int 生日：char

create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) );


<a name="3Angq"></a>
## 20.  insert语句插入数据
   - 语法格式：
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
要求：<br />字段的数量和值的数量相同，并且数据类型要对应相同。
// 除name字段之外，剩下的所有字段自动插入NULL。<br />insert into t_student(name) values('wangwu'); 
```sql
//建表的时候设置默认值
create table t_student(
  no bigint,
  name varchar(255),
  sex char(1) default 1,
  classno varchar(255),
  birth char(10)
);
insert into t_student(name) values('zhangsan');
mysql> select * from t_student;
+------+----------+------+---------+-------+
| no   | name     | sex  | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1    | NULL    | NULL  |
+------+----------+------+---------+-------+

insert into t_student
(no,name,sex,classno,birth)  
values
(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1    | NULL       | NULL       |
|    1 | jack     | 0    | gaosan2ban | 1986-10-23 |
|    3 | rose     | 1    | gaosi2ban  | 1952-12-14 |
|    4 | laotie   | 1    | gaosi2ban  | 1955-12-14 |
+------+----------+------+------------+------------+

需要注意的地方：当一条insert语句执行成功之后，表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。字段可以省略不写，但是后面的value对数量和顺序都有要求。

21. 删除表

drop table if exists t_student; // 当这个表存在的话删除

22. 表的复制

语法：

create table 表名 as select语句；将查询结果当做表创建出来

将查询结果插入到一张表中？

mysql> insert into dept1 select * from dept;
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

23. 修改数据：update

语法格式：

update 表名 set 字段名1=值1,字段名2=值2… where 条件;

注意：没有条件整张表数据全部更新

更新所有记录

update dept1 set loc = ‘x’, dname = ‘y’;

24. 删除数据

语法格式：

delete from 表名 where 条件;

注意：没有条件全部删除

删除所有记录？

delete from dept1;

怎么删除大表中的数据？（重点）

truncate table 表名; // 表被截断，不可回滚，永久丢失

删除表

drop table 表名; // 这个通用。
drop table if exists 表名; // oracle不支持这种写法

对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作）

增删改查有一个术语：CRUD操作

Create（增） Retrieve（检索） Update（修改） Delete（删除）

MySQL数据库 day1

第一章 基本概述