mysql入门|mysql最基础笔记（mysql常用语句）

MySQl入门

数据库的基本概念

数据库的基本概念

数据库的意思：在同一个数据集合中，不同的选择条件对应了不同的输出结果，数据库就是这样一种按数据结构来组织、存储和管理数据的仓库。程序或用户可以通过它来进行数据的访问与修改，它是数据存储的灵魂。

1. 数据库管理系统：是由客户端和服务器端组成，数据储存在服务器，客户端是一个我们用来操作的用户界面而已。

2. 数据库：数据库管理系统中包含了数据库。每个数据库包含有表、储存过程、视图、函数、触发器、事件。这些就是所谓的数据库对象。用来管理、组织、存储数据的仓库。

3. 数据表： 表是由一行一行的数据构成，列又有一个名字叫做字段。它的结构是二维表。他是用来描述一个一个实体的，有时候实体与实体之间有关系，这种关系我们也需要进行描述，它也是用二维表来描述的。

4. 数据库设计概念：

领域模型：一个对象用java语言来描述。比如学生类。
概念模型：用E-R图（实体关系图）来描述PoweDesinger工具，他就是来画E-R图的工具。
物理模型：把E-R图转化为数据库的表
设计数据库：很多时候不要有冗余数据
设计的步骤
- 画E。图，设计概念模型
- PoweDesinger工具可以自动把概念模型变成物理模型，自动生成表结构。然后可以导出sql语句。
E-R举例
- 员工（编号、姓名、性别、年龄、部门的编号）
- 部门（部门的编号、部门的名称）
  <img src="https://i.loli.net/2020/10/15/VTC6KLZ7pjOsUx3.png" />

MySQL的启动

首先要连接服务器端再去启动客户端
启动服务器端： 使用dos命令行来启动

1第一种方式
net start mysql 启动服务器
net stop mysql 关闭服务器

启动客户端：

通过dos命令行来连接

mysql uroot p1234
默认的服务器是root，密码是自己设置的密码

手动连接，启动客户端软件，自己进行连接。
登录成功后，以下2个数据库不要动了，
information_schema：系统数据库
mysql：系统数据库
test:数据库是提供给用户临时使用的。
MySQL的安装目录说明
bin:可执行文件。
include:放置了软件的.h头文件。
lib:放置了库文件
share:放置了字符集等信息
my-default.ini
这是配置文件：用来修改服务器的配置。一般修改完毕后要重新启动服务。

MySQL的相关命令操作

什么是SQL
结构化查询语言，是1974年提出来的。他是关系数据库的标准语言。数据库分为标准数据库和非关系数据库。非关系数据库典型的有Redis，他可以用来做缓存
SQL的分类
- DML语言：数据库操作语言（数据库操纵分类），比说插入操纵insert、删除操纵delete、修改操纵update。
- DQL语言：数据查询语言，用来对数据库表中的数据进行查询。select
- DDL语言：数据定义语言，建立数据库、删除数据库、建立表的结构、修改表的结构等这就需要使用数据定义语言。（对数据结构进行操作的语言）
- DCL语言：数据控制语言，常常需要建立用户，用户有不同的角色和权限。所以要给用户授权。
创建数据库
- 命令行：CREATE DATABASE IF NOT EXISTS test01
- 手工创建，单击创建‘
- 查询数据库列表：show databases
- 删除数据库：DELETE DATABASE test01

数据完整性

对于数据库来说，有一个很关键的问题就是要保证数据的完整性。所谓完整性，就是数据准确性和一致性，比如说学号要唯一，性别只能是男或者女，学生参加考试的课程必须是学校开设了的课程。员工必须属于某个部门等。

主键和外键

主键：假如一个学生的信息包括姓名、学号、性别、年龄。只有这个学号是唯一，那么这个学号就属于主键。
外键：A表上面有学生的学号，B表上面学号属于这张表的标识，那么这个学号就是B表的主键，A表的外键。
主键和外键主要是为了数据的完整性：
- 主键是能确定一条记录的唯一标识。
- 外键用于与另一张表的关联，是能确定另一张表记录的字段，用于保持数据的一致性。
主键和外键的定义：
- 主键：唯一标识一条记录，不能有重复，不允许为空。
- 外键：表的外键是另一个表的主键，外键可以有重复的可以为空值
一个表中只能有一个主键，可以有多个外键。

为了保证数据的完整性，在数据管理中有一种机制，就是通过约束来保证~~~~

实体完整性约束：就是保证表中的记录不重复，它肯定是我们说的外键来保证。
域完整性：域就是字段，一般是通过在字段上添加非空约束、默认约束和检查约束来保证的。
参照完整性：就是采用外键来约束。比如员工表中的部门编号的值必须是部门表中的编号的其中一个值。
mysql常用的约束
- nut null ；非空约束
- primary key ；主键约束
- froeagn key；外键约束
- default ；默认约束
- check；检查约束
- unique；唯一约束
- auto - increment；自动增长（个人认为这不算约束）

MySQL的数据类型

数字类型
- tinyint 非常小的数据
- smallint 较小的数据
- mediumint 中等大小的数据
- int 标准整数
- bigint 较大的整数
- float 单精度浮点数
- double 双精度浮点数
字符串长度
- char[(M)] 固定字符串
- varchar[(M)] 可变字符串
- tinytest 微型字符串
- text 文本串
日期和时间类型
- DATE YYYY-MM-DD日期格式
- TIME HH:mm:ss 日期格式
- DATETIME YY-MM-DD HH-mm-ss 日期格式
- TIMESTAMP YYYYMMDDhhmmss格式表示的时间戳
- YEAR YYYY日期格式

数据库表结构（对数据的结构进行增删改查）

建立表sql语法1：

create table if not exists 表明(
    字段一  数据类型  约束[primary key];
    字段二  数据类型  约束；
    字段n   数据类型  约束；
)    ENGINE = InnoDB DEFAULT CHARSET = utf8  #编码格式#

建立表sql语法2：

create table if not exists 表名(
   字段一 数据类型 约束；
   字段二 数据类型 约束；
   字段n  数据类型 约束；
   parimary key(字段名1,字段名2)
)

总结：对表进行主键添加的两种方式和添加外键：

主键：
语法： 数据类型 primary key
       parimary key(字段名)
外键：references 参考表(参考表的字段)

删除表格

drop table 表名（如果该表格与另一个表格具有外键的约束关系，那么删除表格的时候先删除外键表再删除主键表）

对表的字段进行增、删、改、查
- 将表格的名字修改
  alter table 原表格名 rename 新表格名。
- 在表格中添加新的字段
  alter table 表格名 add 字段名数据类型
- 将表格中的某一个字段名修改
  alter table 表格名 change 原字段名新字段名数据类型；（假如后面的字段类型与原来的字段类型不符合，那么会改为修改过后的数据类型）
- 修改表格中字段的字段类型
  alter table 表格名 modify 字段名数据类型
- 删除字段
  alter table 表格名 drop column 字段名；
```
#修改表的结构
#修改 表名
alter table booktable rename booktable1;

#表结构中添加字段
alter table booktable1 add abc int ;

#修改字段的名称
alter table booktable1 change abc abc int;

#修改字段的类型
alter table booktable1 modify abc varchar(20);

#删除字段
alter table booktable1 drop COLUMN abc;
```

参照表：booktable；

数据的备份

数据的备份：就是把数据库的表结构和表中的数据以.sql的文件的形式导出。

恢复数据：表损害或者数据丢失，我们把表和数据还原的过程。就是把备份的sql导入到数据库的过程。

dos命令行备份

数据的备份操作：mysqldump -u用户名 -p密码 --databases exam>文件路径
数据的恢复操作
mysql>source 备份的路径

数据的插入操作

语法： insert into 表的名字（字段一，字段二，......字段n） values （值1，值2，值3）
规则：
- 若果遇到列是auto_increment(自动增长)，可以把对应的值写null，也可以不写auto_increment对应的列,那么值也可以不写。
```
insert into booktable1(typeid,typename) VALUES (null , '国文');
```
- 有几个字段，就必须有几个值对应；个数要相等，顺序也要对应，数据类型要对应。
```
insert into booktable1 values (null,'少儿');
```
- 字段的是可以省略，要保证值得个数和表的字段的个数相同，要有对应关系。
- 默认的字段可以不写，如果写了在值列表中使用default对应。
```
insert into book(bookname,author,price,pubname,pubtime,typeid) values ('平凡的的世界','路遥',15.0,DEFAULT,NOW(),1);
```
- 对应外键字段如果定义的时候没有写not null，那么插入值得时候，可以为null。
- 要先建立主键表，插入值，再到外键表中插入值，外键的值必须在主键中存在。(否则就会报错外键约束失败)
```
insert into book(bookname,author,price,pubname,pubtime,typeid) values ('追风筝的的人','胡塞尼',15.0,DEFAULT,NOW(),15);

报错：
Cannot add or update a child row: a foreign key constraint fails 

翻译：
无法添加或更新子行:外键约束失败
```

数据的修改操作

语法：update 表格名 set 要修改的字段1=值1,字段2=值2，...，字段n=值n [where = 条件表达语句]

# 修改表数据
#把表book中的id=1的记录的pubname的值修改为湖北少儿出版社#
update book set pubname = '河南人民出版社' where id = 1;
#把表book中的id=1的记录中的出版时间改成2015-09-18#
update book set pubtime = '2015-9-18' where id =1;
#把表中的书籍的价格增加10元，同时把现有书籍的出版社全部改成中国少年出版社#
update book set price = price +10,pubname ='中国少年出版社';
#把book中的price<60的增加5块#
update book set price =price+5 where price<60;

数据的删除操作

语法：

delete from 表名 [where 执行条件语句]（如果后面的where 执行语句不写，那么就会删除表中所有的数据）
truncate table 表名。

规则：

要删除数据的时候要先删除子表中和外键关联的数据，然后才能删除主键表中的数据
删除的时候一般要写where条件，否则就会删除全部数据。

代码：

#删除表数据
#如果主表与字表有外键关系，那么就需要先删除字表与主表的外键关系，再对表数据进行删除
delete from book;
delete from booktable;
##删除子表中的typeid=1的记录，然后再删除主表typeid=1的记录，此时主外键约束存在##
delete from book where id =1;
delete from booktable where typeid = 1;
insert into book(bookname,author,price,pubname,pubtime,typeid) values ('平凡的的世界','路遥',15.0,DEFAULT,NOW(),1);
#TRUNCATE和delete的区别
truncate table book;
insert into book(bookname,author,price,pubname,pubtime,typeid) values ('平凡的的世界','路遥',15.0,DEFAULT,NOW(),1);
#使用dalete语句删除的表数据，自动增长的值会继承原来数据自动增长的值+1，而使用truncate删除的表数据，新添加的数据中自动增长的值会重新开始。

数据的查询操作

简单查询
- 语法：select 列名 as 别名 from 表名 where 条件语句(说明：我们平时在写条件的时候，可以用如下的写法.<, >= ,<=, <>, is NULL ,is NOT NULL, and or no
模糊查询
- 关键字 like ；‘_’字符长度； %任意字符长度
- 排序：order by 排序的字段 asc/desc（升序 / 降序）

建立表结构

CREATE TABLE tab_student( 
s_number CHAR(6), 
s_name VARCHAR(20), 
s_age INT, 
s_gender VARCHAR(10) 
) CHARACTER SET utf8 COLLATE utf8_general_ci;
#频繁地插入表数据 
INSERT INTO tab_student VALUES('S_1001', 'liuYi', 35, 'male'); 
INSERT INTO tab_student VALUES('S_1002', 'chenEr', 15, 'female'); 
INSERT INTO tab_student VALUES('S_1003', 'zhangSan', 95, 'male'); 
INSERT INTO tab_student VALUES('S_1004', 'liSi', 65, 'female'); 
INSERT INTO tab_student VALUES('S_1005', 'wangWu', 55, 'male'); 
INSERT INTO tab_student VALUES('S_1006', 'zhaoLiu', 75, 'female'); 
INSERT INTO tab_student VALUES('S_1007', 'sunQi', 25, 'male'); 
INSERT INTO tab_student VALUES('S_1008', 'zhouBa', 45, 'female'); 
INSERT INTO tab_student VALUES('S_1009', 'wuJiu', 85, 'male'); 
INSERT INTO tab_student VALUES('S_1010', 'zhengShi', 5, 'female'); 
INSERT INTO tab_student VALUES('S_1011', 'xxx', NULL, NULL);

运行结果：

简单查询

#查询学号为S_1001，或者姓名为liSi且性别为female的记录：#
select *  from tab_student where s_number = 'S_1001' or (s_name = 'lidi' and s_gender = 'female');

运行结果:

模糊查询

#查询姓名中包含“a”字母的学生记录： 
select * from tab_student where s_name like '%a%';

运行结果：

复杂查询
1. 分组查询
  - 语法： select 字段名 from 表名 where 执行语句 group up 分组字段 having 组内筛选字段（执行语句）order by 倒序|升序
  - 在分组查询的时候聚合函数的判断必须放在having的后面
    代码演示
```
#查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序
select deptno as 部门编号, COUNT(empno) as 部门人数 , AVG(sal) as 平均工资
from emp
GROUP BY deptno
HAVING AVG(sal)>2000 and  COUNT(empno)>2
ORDER BY COUNT(empno) DESC;
```
    运行结果：
2. 连表查询
  1. 内连接查询
    - 语法：select 字段 from tabname1 inner join tabname on tabname1.外键 = tabname2.主键....
      代码演示：
```
# 查询所有的员工信息以及部门名称
SELECT e.*,d.dname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno;
```
      运行结果:
  2. 左外链接查询
    - 语法:select 字段 from tabname1 left join tabname on tabname1.外键 = tabname2.主键....
      代码演示：
```
#左链接查询
select e.*,d.*
from emp e LEFT JOIN dept d
on e.deptno = d.deptno;
```
      运行结果：
      特点：以from为参照物，from左边的为左表，from右边的为右表，左外链接查询就是两个表链接，执行条件对的上的，左边的表正常显示，右边的匹配不上的就使用null来表达。举个栗子就是在上面的代码中在加一个执行条件 ‘where deptno is null’就会只显示员工姓名（ename）为JACK的一行。
  3. 右外连接查询
    - 语法：语法:select 字段 from tabname1 right join tabname on tabname1.外键 = tabname2.主键....
      代码演示：
```
#右链接查询
select e.*,d.*
from emp e RIGHT JOIN  dept d
on e.deptno = d.deptno;
```
      运行结果：
      特点与上面的用处相反，左边的匹配不上就是用null填充。
      左外链接与右外连接可以互相改变，只需要改变表的位置即可
  4. 自连接查询
    - 语法与内连接查询一样，不同的是表自己链接自己，需要注重的是逻辑条件，举个栗子，
      代码演示：
```
#查询所有工作做在NEW YORK 和 CHICAGO 的 员工姓名，员工编号，以及他们的经理姓名，经理编号。
SELECT e1.ename as 员工姓名 , e1.empno as 员工编号 , e2.ename as 经理姓名 , e2.empno as 经理编号
FROM emp e1
INNER JOIN emp e2
ON e1.mgr =e2.empno
INNER JOIN dept d
ON e2.deptno = d.deptno
where d.loc in('NEW YORK','CHICAGO');
```
      运行结果：
      解析：其中这上边的经理编号、经理姓名是不能直接查找的，根据表的结构和数据分析来进行对表的筛选。
  5. 子查询
    - 子查询也叫嵌套查询，在一个查询中嵌套了其他的查询，这就是子查询。
    - 一般是在from或者是where 条件中放了另外一个查询。另外一个查询叫子查询，本查询叫主查询。执行的时候先执行子查询，再执行主查询。因为主查询要依赖子查询的结果。
      代码演示：
```
#案例一：查询部门名称是SALES的员工的编号和姓名
SELECT e.empno as 员工编号 , e.ename as 员工姓名
FROM emp e
where deptno = (SELECT deptno FROM dept where dname = 'SALES');
```
      运行结果：
  6. 分页查询
    - 分页查询关键字‘limit’ , 分页查询就是按照查询的条数，每几条叫做一页。
      代码演示：
```
#案例：员工的工资按照从低到高排序，查询排序结果的前3条记录。
SELECT emp.*
FROM emp
ORDER BY sal DESC
LIMIT 0,3;
```
      结果演示：
      分析：这里可以理解为每三条记录分为一页，假设页取别名叫做pageNo，记录取别名叫做pageSize，那么他们分页的公式就是 limit （pageNo -1 ）* pageSize ，pageSize。