MySQL

一、数据库简介

1.数据库系统

1.1数据库

DataBase【简称DB】，指的是长期保存在计算机上的一些数据，按照一定的规则组织起来

意义：可以被多个用户或者多个应用共享【存储，维护和管理数据的集合】

mysql:

sqlite

1.2数据库管理系统

DataBase Management System【简称DBMS】，用来操作和管理数据库的软件，用于建立，使用和维护数据库，为了保证数据库数据的完整性和安全性，用户通过数据库管理系统访问数据库

【面试题：数据库和数据库管理系统之间的关系】

数据库：存储，维护和管理数据的集合

数据库管理系统：数据库软件，数据库通过数据库管理系统进行维护和访问

1.3数据库的应用

增删改查

用户访问数据库，实质上是可以进行数据库中数据的删除，增加，修改和查询

2.常见数据库管理系统

1>Oracle（甲骨文）:目前比较成功的关系型数据库管理系统，运行稳定，功能齐全，性能超群，技术领先，主要应用在大型的企业数据库领域

2>DB2:IBM（国际商业机器公司）的产品，伸缩性比较强

3>SQL Server:Microsoft的产品，软件界面友好，易学易用，在操作性和交互性方面独树一帜

4>PostgreSQL:加州大学伯克利分校以教学为目地开发的数据库系统，支持关系和面向对象的数据库，属于数据库管理系统

5>MySQL:免费的数据库系统，被广泛引用于中小型应用系统，体积小，速度快，总体拥有成本低，开发源代码，2008年被SUN收购，2009年SUN被Oracle收购

二.数据库的安装

1.安装

1.验证是否安装MySQL

演示命令：
yangyang@yangyang-virtualmachine:~$ mysql -u root -p		#登录MySQL数据库
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 	'/var/run/mysqld/mysqld.sock' (2)				#报错，说明数据库没有启动
yangyang@yangyang-virtualmachine:~$ sudo service mysql start		#启动数据库
yangyang@yangyang-virtualmachine:~$ mysql -u root -p		
Enter password: 							#输入数据库密码rock1204
Welcome to the MySQL monitor.  Commands end with ; or \g.		#出现左边的信息说明已经安装
Your MySQL connection id is 3
Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, 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.

mysql> exit		#退出数据库
Bye

2.如果之前有安装 不能使用的话 可按如下步骤卸载

演示命令：
#第一步：依次执行下面的语句
sudo apt-get autoremove --purge mysql-server
sudo apt-get remove mysql-server
sudo apt-get autoremove mysql-server
sudo apt-get remove mysql-common 

#第二步：清理残留数据
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
#这个步骤尽量多执行几次，清理干净

3.重新安装

#####第一步：下载MySQL资源，安装之前请先卸载之前的，一定要卸载干净
https://dev.mysql.com/downloads/file/?id=477124，进入官网之后直接点击No thanks, just start my download，下载下来的是mysql-apt-config_0.8.10-1_all.deb安装包【注意：如果是其他的则有问题】
#####第二步：在终端里输入下面的命令安装下载的发布包
sudo dpkg -i 文件路径+文件名　
也可以cd到文件目录下，直接执行sudo dekg -i mysql-apt-config_0.8.10-1_all.deb

#####第三步：使用以下命令从MySQL APT存储库更新包信息
sudo apt-get update

#####第四步：安装MySQL
sudo apt-get install mysql-server

注意：这里要选择第二个，不然安装之后只有用sudo才能进入MySQL，而且不能使用数据库

2.启动和停止mysql服务

检查MySQL的状态：sudo service mysql status

开启MySQL服务器：sudo service mysql start

关闭MySQL服务器：sudo service mysql stop

三、SQL概述

1.简介

SQL【Structure Query Language】,结构化查询语言

一种标准

2.数据库服务器、数据库和表之间的关系

数据库服务器：在计算机上安装了一个数据库管理系统，该管理程序可以管理多各数据库，一般情况开发人员会针对每个应用创建一个数据库，针对一个数据库创建多个表

3.数据在SQL中的存储形式

举例

User表

id name age

1 lisi 18

2 zhangsan 20

说明：

​ a.表中一行数据被称为一条记录【实体】

​ b.表中的一列数据一类数据

4.SQL的分类

DDL:【Data Definition Language】,数据定义语言，用户创建，修改，删除库和表结构

​ create alter drop

DML：【Data Manipulation Language】,数据操作语言，用于对数据表进行增删改记录

​ insert update delete

DQL:【Data Query Language】,数据查询语言，用于查询表记录

​ select …from

​ where :如果…

​ group …by :分组

​ having，有…

​ order…by：排序

​ limit：限制

DCL:【Data Control Language】,数据控制语言，用户的访问权限和安全级别

四、数据库操作

1.DDL

1.1create创建

语法：

#创建数据库
CREATE DATABASE database_name;

#创建表
CREATE TABLE 表名(
		字段1 字段类型[列级别约束条件]([默认值])，
		字段2 字段类型[列级别约束条件]([默认值])，
		….
		字段n 字段类型[列级别约束条件]([默认值])
		[表级别约束条件]
)

#注意：mysql命令不区分大小写，一般采用小写

#第一部分：创建数据库
#使用数据库的流程
#1.安装数据库管理系统【mysql】
#2.登录数据管理系统
#3.在当前数据库管理系统下创建数据库：create database xxx;
#4.查看当前数据库管理系统中有哪些数据库：show databases;
#5.查看当前正在使用的数据库:select  database();
#6.切换到需要工作的数据库：use  xxx; 
#7.修改数据库编码【只修改一次】
	安装完mysql后，默认的编码格式为latin1,数据库中存储中文，则会出现乱码的错误，将数据库中的编码格式修改为能够识别中文的编码格式，比如：utf-8,gbk等
	
	#a.重启一个新的终端，cd /etc/mysql
	#b.打开文件vim my.cnf
	#c.在my.cnf内部引用另外两个目录
		/etc/mysql/conf.d/
		/etc/mysql/mysql.conf.d/
	#d.进入mysql.conf.d,查看内容，ls
	#e.打开mysqld.cnf,注意：最好使用超级管理员的权限打开
		在[mysql]下面添加一行内容
		character-set-server=utf8
	#f.保存退出，重启mysql服务：service mysql restart,输入开机密码授权
	#g.回到原来数据库的终端，exit退出数据库，
	#h.重新登录数据库，输入\s查看是否修改成功
	
	#注意：在当前的数据库下，如果插入中文，发现乱码，则删除该数据库，重新创建一个新的数据库

#8.退出数据库：quit或者exit

#9.在当前数据库下面创建数据表

演示命令

演示命令：
 select version();     #查看当前数据库的版本
+-------------------------+
| version()               |
+-------------------------+
| 5.7.24-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select now();		#查看数据库中的时间
+---------------------+
| now()               |
+---------------------+
| 2018-12-24 09:28:08 |
+---------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

#第二部分：创建表
c.需求：创建一个员工表

字段   			属性

id				整型

name			字符型

gender			字符型

birthday		日期型

entry_date		日期型

job				字符型

salary			整型

#1.切换到指定的数据库下
	use db
#2.创建数据表
	create table 表名()
#3.查看当前数据库下所有的表
	show tables;
#4.查看一个表具体的字段信息
	desc 表名；

1.2alter操作

a.语法：

#1.修改表名
语法规则：ALTER TABLE old_table_name RENAME [TO] new_table_name

#2.修改字段的数据类型
语法规则：ALTER TABLE table_name MODIFY 字段名 数据类型
修改完成之后可以查看DESC table_name检验结果

#3.修改字段名
语法规则：ALTER TABLE table_name CHANGE 旧字段名 新字段名 数据类型

#4.添加字段
语法规则：ALTER TABLE table_name ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER 已经存在的字段名]

#5.删除字段
语法规则：ALTER TABLE table_name DROP 字段名

#6.修改字段的排列位置
语法规则：ALTER TABLE table_name MODIFY 字段1 数据类型 FIRST|AFTER 字段2
	first: 设置成第一个
	after 字段2： 在指定字段2的后面
	
#7.删除表的外键约束
语法规则：ALTER TABLE table_name DROP FOREIGN KEY 外键约束名

#8.删除数据表
#删除没有被关联的表
语法规则：DROP TABLE [IF EXISTS] 表1，表2...
#删除被其他表关联的的表
直接删除会出现错误的，操作： 先解除关联 再进行删除
#删除主键约束:alter table 表名 drop constraint 主键名
#删除其他键约束：alter table 表名 drop foreign key 约束名
#删除约束：alter table 表名 drop constraint 主键名

b.常用数据类型

1.数字数据类型
#- INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达11位的宽度。
- TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。
- SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。
- MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。
- BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。
#- FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。小数精度可以到24个浮点。
- DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。
- DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。numeric  decimal

2.日期和时间类型
#- DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。
- DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
- TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。 timestrap
- TIME - 存储时间在HH:MM:SS格式。
- YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。

3.字符串类型
虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。 下面列出了在MySQL中常见的字符串数据类型。
#- CHAR(M) - 【固定长度】的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。    char
#- VARCHAR(M) - 【可变长度】的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。   varity:可变化的
- BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
- TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
- MEDIUMBLOB or MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
- LONGBLOB 或 LONGTEXT -  BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。
- ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。

注意：主要了解 char 和 varchar 的区别

char(M)是固定长度的字符串， 在定义时指定字符串列长。当保存数据时如果长度不够在右侧填充空格以达到指定的长度。M 表示列的长度，M 的取值范围是0-255个字符

varchar(M)是长度可变的字符串，M 表示最大的列长度。M 的取值范围是0-65535。varchar的最大实际长度是由最长的行的大小和使用的字符集确定的，而实际占用的空间为字符串的实际长度+1

#1.添加新的字段image【添加了一列】
	alter table 表名 add  字段  字段类型；
	#blob可以二进制数据
#2.将job列的长度修改为40
	alter table 表名 midify  字段  字段类型；
#3.删除image列
	alter table 表名  drop  字段；
#4.重命名表名
	方式一：rename table 表名 to  新的表名；
	方式二：alter table 表名 rename to 新的表名；
#5.重命名字段名【列名】
	alter table  表名  change  字段  新的字段  字段类型；
	
#6.查看表的创建细节
	show create table 表名；
	
#7.修改指定表的字符集
	alter table 表名 character  set  字符集类型；
	
#注意事项：alter操作的侧重点:表头，对表中的内容并没有做任何操作

1.3drop删除

语法：

DROP DATABASE database_name

#1.删除数据库
	drop database  数据库名；
#2.删除数据表
	drop table 表名；	

2.DML

DML对表中的数据进行增删改的操作，不要与DDL区分开

​ insert：增

​ update：改

​ delete：删

2.1insert插入

语法：

#单行插入
INSERT INTO table_name (field1, field2,...fieldN) VALUES(value1, value2,...valueN);
                       
#多行插入
INSERT INTO table_name (field1, field2,...fieldN)
                       VALUES
                       (value1, value2,...valueN),
                       (value12, value22,...valueNN)...;
                       
注意:
	a.列名与列值的类型，个数以及长度保持一致
	b.可以将列名当做Python中的形参，将列值当做实参
	c.值不能超过定义的长度
	d.如果插入空值，使用null表示
	e.插入日期和字符串写法是一样的，使用引号括起来
	
#1.插入数据
	#插入单条数据
	insert into 表名(字段名，使用逗号隔开) values(值，使用逗号隔开)；
	#批量插入
	insert into 表名(字段名，使用逗号隔开) values(值，使用逗号隔开)，(值，使用逗号隔开)，(值，使用逗号隔开)。。。；
#2.查询整个表中的数据【DQL】
	select * from 表名；

2.2update更新

语法：

UPDATE table_name SET field1=new-value1, field2=new-value2  [WHERE Clause]

注意：
	a.可以更新一个字段或者多个字段
	b.可以指定条件，使用where子句
	c.可以一次性更新一个表中的所有值
	d.如果没有使用where子句，则表示全部更新

1>where子句

语法：

注意：where子句其实就是一个操作符，类似于Python中的if语句，可以做数据的筛选

操作符	说明
=	相等
<> / !=	不相等
<	小于
<=	小于等于
>	大于
between and	位于两值之间
>=	大于等于
IN(A，B)	A 和 B 之间
AND	连接多个表达式 并且的关系

#练习
#1.将所有员工的薪水修改为5000

#2.将tom的薪水修改为8000

#3.将jerry的薪水修改为3500，并将性别修改为b

#4.将张三的薪水在原来的基础上增加1000

#5.将id为4的员工的姓名修改为"perfect"

#6.将月薪大于8000的员工，月薪在原来的基础上增加2000

2.3delete删除

语法：

DELETE FROM table_name [WHERE Clause]

注意：
	a.如果没有添加where子句，则表中的所有数据都会被删除
	b.根据where条件删除指定的内容
	c.可以单条删除，也可以一次性删除
	
#1.删除lisi的所有信息


#2.删除表中的所有数据[清空表]


#3.删除表


【面试题：drop，delete,truncate之间的区别】
	#1.占用空间
		drop：将表所占用的空间全部释放掉
		delete：不会减少表或者索引所占用的空间
		truncate：表或者索引所占用的空间会恢复到初始大小
		drop>truncate>delete
	#2.应用范围
		delete主要操作表和view
		truncate只能操作表
		drop操作表和数据库
	#3.删除数据
		delete和truncate只删除数据
		drop删除表
	#4.所属分类
		truncate和drop属于DDL
		delete属于DML	

3.DQL

select

数据查询语言，数据库执行DQL不会对数据库内部的内容发生改变，只是将符合条件的结果返回到客户端

语法：

SELECT 列名 FROM 表名【WHERE --> GROUP BY -->HAVING--> ORDER BY】

3.1基础查询

#1.查询所有列
	select * from 表名；	
#2.查询指定列
	select field1,field2  from 表名；

3.2条件查询：where

主要结合where使用

between…and:介于…和…之间

and：逻辑与

or：逻辑或

in：在…中，类似于Python中的成员运算符

is：是…,类似于Python中的身份运算符

is not:不是…

#1.查询性别为nv，并且年龄为20的数据

#2.查询学号为3或者姓名为xiaoming的数据

#3.查询学号为1,2,3的数据

#4.查询学号不为2的数据

#5.查询年龄在15~20之间的数据

#6.查询性别不为nan的数据
	select * from 表名 where 字段！=xxx
	select * from 表名 where 字段<>xxx

#7.查询姓名不为null的数据
	#数据库有很多不同的数据类型，null被当做特殊的数据类型，不同的数据类型之间无法比较
	name【varchar】 = null
	name != null
	没有可比性
	name!="null"

3.3模糊查询：like

where：精准匹配

like：模糊匹配

注意：where子句结合like子句使用

通配符：

​ _:匹配任意一个字符

​ %:任意0~n个字符

#1.查询名字由4个字符组成的数据
	
#2.查询姓名由4个字符组成，并且最后一个字母为i的数据

#3.查询姓名以j开头的数据

#4.查询姓名第二位为i的数据

#5.查询姓名包含i的数据

3.4字段控制查询

as :起别名，

用法：select 字段 as 别名；

ifnull(xx):如果xx未空

distinct：去除重复记录

#1.去除重复记录

#2.给列名起别名
	#注意：as是可以省略的
#3.ifnull()，将null转换
#查看学生学号和年龄和
#ifnull(字段，希望被转换成为的值)，如果某个字段的值为null，则可以通过该函数将其转换为具体的值

3.5排序；order by

asc:升序【Ascending】
desc：降序【Descending】
结合where或者like的使用

格式：select xxx   from 表名  order  by 字段 asc/desc;
#1.查询所有的学生记录，按照年龄升序排序
	
#2.查询所有的学生记录，按照年龄升序排序，如果年龄相等，按照学号降序排序
	select xxx   from 表名  order  by 字段1 asc/desc，字段 2asc/desc;
	#说明：首先根据字段1排序，如果字段1的值相等，则根据字段2的值排序

3.6聚合函数

聚合函数：用来做纵向运算的函数

count()：记录行数

#1.查询年龄大于15的人数

#2.查询年龄和学号和大于20的人数

#3.查询有年龄，有性别的人数

sum():计算和

#1.计算所有学生的年龄和
#2.计算所有学生年龄和编号和

max():求最大值

min();求最小值

#1.求年龄的最大值和最小值

avg():average,求平均值

格式：select 聚合函数 from 。。。

3.7分组查询：group by

#1.分别查询女生和男生的人数

having和where比对

​ a.二者都表示限定条件

​ b.having是分组后对数据进行过滤【对分组后的数据添加约束】

​ where是在分组前对数据进行过滤

​ c.having后面可以使用聚合函数

​ where后面不能使用聚合函数

#1.查询各部门的人数

#2.查询每个部门的工资和

#3.统计每个部门中工资大于1000的人数

3.8分页查询：limit

格式：select * from 表名 limit start,end

注意：包含start，不包含end

总结：

​ 查询语句书写顺序：select---->from ---->where ---->group by —>having---->order by ---->limit

​ 查询语句执行顺序：from ---->where---->group by ----->having---->select---->order by ---->limit