MySQL基础部分-SQL语言第一篇

一、数据库的好处：
1、保存数据的容器：
数组、集合：内存，易失性
文件：磁盘，查询效率低
2、数据库的优势：
持久化数据到本地；实现结构化查询，方便管理
二、数据库的概念：
1、DB:数据库，保存了一系列有组织的数据
2、DBMS：数据库管理系统，DB是通过DBMS创建管理的，常见的有MySQL(成本低，性能高)，Oracle, DB2（优势是支持海量数据），Sqlserver（只支持windows系统）
DBMS分为两类：
- 基于共享文件系统的DBMS，比如ACCESS
- 基于客户机-服务器的DBMS，比如MySQL,Oracle,Sqlserver
3、SQL：结构化查询语言，专门用来与数据库通信的语言
优点：几乎所有DBMS都支持SQL；
三、数据库MySQL：
（一）windows版本安装
1、MySQL解压版安装：
https://blog.youkuaiyun.com/salted___fish/article/details/106356078
https://blog.youkuaiyun.com/salted___fish/article/details/106356078
2、my.ini：配置文件，[mysqld]后的port也可以在安装后改变端口
3、MySQL服务端的启动和停止：（借助管理员模式的命令行）

	net start mysql
	net stop mysql

4、MySQL服务端的登录和退出（前提是启动状态，借助管理员模式的命令行）

mysql  -h localhost -P3306 -u root -p  #P后面是端口号，后面会输入密码
mysql  -h localhost -P3306 -u root -p123456  #p后面可以直接写密码
mysql -u root -p  #P后面是端口号  #若连接本机的3306，可以省略 -h localhost -P3306 

5、MySQL的常见命令：每条命令用分号隔开

source  路径\文件.sql  #运行脚本.sql文件
**show** databases; #展示所有的数据库，其中information_schema , mysql, performance_schema，sys不可以改动
**use** database_name; show tables;#和前一条语句搭配使用，展示前面使用的库的所有表
show tables from database_name;#等价于上一行命令,但是后者只是展示，没有将当前位置位于database_name中
**select** database() #返回当前所在的库
select version() #返回mysql的版本
**desc** table_name; #查看表的信息，包括字段名称、类型以及其他性质等

不区分大小写，但是建议关键字大写；每条命令用分号结尾；注释 #或者–空格 /多行/
（二）linux版本安装
添加链接描述

四、SQL语言的学习
（一）DML（增删改查）
先use database_name;定位到库；
1、查询：
（1）基础查询
select 信息（字段，*）… from 表名；
select 常量值，表达式，函数 ; # 类似于执行
- “ ”可以用来区分不是关键字；
- AS 可以定义字段或表的别名 ，格式为 字段 as 别名，也可省略用空格代替；当为表别名时，查询字段只能使用新的表别名
- distinct 可以去重，不显示重复结果，格式为 distinct 字段
- mysql中的 + 只能用于运算，如果两个操作数有字符型，会自动转换为数字运算，如果不成功，当做0处理；
- 无论什么运算，如果一方为null，则结果为null
- concat(str1,str2)可以实现字符串拼接
- IFNULL(字段名，默认值），如果字段名为空，将默认值显示
desc table_name; #查询表的结构
（2）条件查询
select 查询列表 from table_name where 筛选条件 #先from 在where 最后select
- 条件表达式： <(=) >(=) = <>
- 逻辑表达式： and or not
- 模糊查询 ：(not) like: 通配符结合：% _ ; 若需要匹配%或者下划线本身，可使用\转义字符 ；仅like支持通配符结合
(not) between A and B : [A , B]
(not) in : ( , , ) ,且列表中值类型要可兼容，
is (not) null ： 判断是否等于null 要使用is，而不是等号，
特别的有安全等号<=>，可以充当=和is;
（3）排序查询
select 查询列表 from table_name 【where 筛选条件】 order by 排序列表 【asc|desc】#默认升序
- #先from 在where 在排序 最后select
- 排序列表中的字段可以使用运算结果，别名等；
- 运算结果：length() 一般运算符
- 可以为排序列表中每个元素分别在后面指定desc还是asc
（4）分组查询：先from 再where 再分组 再select 最后排序
select 分组函数(列)，列 from table_name [where 筛选条件] [group by 分组列表] [order by 排序列表 [asc|desc]]
- 注意到此时的where为分组前的筛选
- 为了实现分组后的筛选，可以使用having，格式为 ：group by 分组列表 having 条件表达式；
- 查询列必须是出现在group by后面的字段；排序列表需要为分组函数参数或分组参数
- 分组列表以及having条件中可以使用字段，也可以为表达式或者函数
- 按多个字段分组时，只需要将这些字段罗列在group by后面，无序；本质是这些字段值都相同才在一个组内
（5）连接查询：（多表连接）
select 表1.字段1，表2.字段2 from 表1，表2 where 表1.字段=表2.字段； #当没歧义时，可省略表名；表1表2是无序的
- select 字段1，字段2 from 表1，表2；#分别从表1取字段1，表2取字段2，但是一共有|字段1|*|字段2|个，即笛卡尔集
- 按照年代分类：sql92标准；sql99标准； 前者只支持内连接，后者除了全外连接均支持
- 连接查询按照功能分类：要有特殊的关系才可以:
- sql92标准：
- 内连接：
- 等值连接：where table1.字段1 = table2.字段1 #找到这个是关键
- 非等值连接： where table1.字段1 比较运算符（除了等号） table2.字段1
- 自连接：table1和table2使用同一个，因此额外需要使用别名
- sql99标准：
select 查询列表 from 表1 别名 【连接类型】join 表2 别名 on 连接条件 where …
当多表连接时， 【连接类型】join 表2 别名 on 连接条件 多写几个即可，注意顺序
- 内连接：用于查询数据在两个表都有的情况
- inner，且inner可省略 ，不同内连接类型的连接条件与sql92是一样的
- 外连接：用于查询数据一个表有一个表没有的情况，分别称为主表和从表，显示主表
所有的记录，从表有匹配，显示匹配值；没有则显示null;可通过where条件从表字段 is null；
- 左外连接 left [outer] 左边是主表
- 右外连接 right [outer] 右边是主表 ，则左外和右外可交换表顺序来等价
- 全外连接 full [outer] mysql不支持
- 交叉连接：cross 本质就是笛卡尔集；

				内连接（A∩B） ；左外连接（A）,增加从表字段 is null条件时为A-A∩B；
				右外连接（B）,增加从表字段is null条件时为B-A∩B；
				全外连接（A∪B)

(6)子查询：出现在其他语句中的select语句或其他语句，子查询放在**（）内
以select语句为例：称为子查询或内查询；外部的查询成为主查询或外查询；
前三种子查询执行早于主查询；最后一种主查询早有子查询；
- 子查询分类：
- 按结果集的行列数不同：
- 标量子查询(单行子查询)：一行一列
- 列子查询（多行子查询） ：一列多行
- 行子查询：多行多列或一行多列（主要）
- 表子查询： 结果集就行，一般为多行多列
- 按照出现位置
- select 后面：仅支持标量子查询
- from 后面：表子查询,充当数据源，即表，必须起别名**
- where 或 having 后面 ：；放在条件右侧；
- 标量子查询：搭配单行操作符，即比较运算符；注意返回为空的情况最终结果会出错
- 列子查询：搭配多行操作符，即(not)in(等于其中的某一个值即可)、 any/some(两个意义一样，和其中的某一个值满足条件即可)、 all（所有的值都要满足条件）；
- 行子查询：where (字段1，字段2) 运算符 （select 字段1，字段2 from …）：不同字段的运算符一样时才可以这么写，局限性比较大
- （not）exists(完整的查询语句)后面：表子查询，也称为相关子查询；如果空，返回0；否则返回1；常放在where和having后面；都可以转换为(not)in的格式
(7)分页查询：当显示的数据过多，需要分页提交sql请求
语法：select 查询列表 from 表 [join类型 表 on 连接条件 where 筛选条件 group by 分组序列 having 筛选条件 order by 排序] limit offset ，size #offset表示起始偏移量（从0开始，为0可省），size为要显示的个数

总结：from ->join ->where ->group by ->having->select->order by->limit

(8)union联合查询：将多条查询语句的结果合并到一个结果
语法：查询语句1 union 查询语句2 union …
当查询不同的表的时候没有直接的连接关系，但查询信息一样，那么可以使用union合并为一个结果集返回
特点：不同查询语句中涉及的列数以及顺序保持一致；自动去掉重复行数据（可以使用union all禁止去重）

2、插入：
(1)语法：insert into table_name (列名，…) values (值，…)
注意：值的类型与列的类型一致或兼容（数字型，字符型，时间型，图片型blob, null）且要一一对应
不可以为null的列必须插入值；可以为null的 插入值 | null| | 列和值都省；
(列名,…)可以省略，但是需要按照表中列定义的顺序填入值，不可省null
(2)语法：insert into table_name set 列1=值1，列2=值2，…
(3)两种语法的比较：
- 前者可以一次插入多行，只需要在(值，…) ，(…）依次列出即可；后者不支持
- 前者支持子查询，后者不支持；insert into table_name (列名,…) select …即可 ，省略values

3、更改：
（1）修改单表的记录：
语法：update table_name set 列1=新值1，列2=新值2，… where 筛选条件
（2）修改多表的记录：指涉及到多个表
sql92语法： update 表1 别1 ，表2 别2 set 列1=值1，… where 连接条件 and 筛选条件
sql99语法：update 表1 别1 【join 类型】 表2 别2 on 连接条件 set 列1=值1，… where 筛选条件
4、删除：
（1）语法1：delete from table_name where 筛选条件 #以行为单位删除
补充：多表删除：
- sql92语法：delete 表别名 from 表1 别1 ，表2 别2 where 连接条件 and 筛选条件
- sql99语法：delete 表别名 from 表1 别1 join类型 表2 别2 on 连接条件 and 筛选条件
注意：删除哪个表，在delete后面罗列哪个表的别名

（2）语法2：truncate table 表名；#删除全部数据，不允许使用where条件
！！！对于自增长的列，delete删除后在插入数据时从断点+1开始，truncate删除后从1开始；
delete有返回值，而truncate没有返回值
delete可以回滚，而truncate不可以回滚

（二）DDL（定义）
1、库的管理
（1）创建：
create database [if not exists ]库名；#库名不可以重复,使用if not exists实现有不创没有则创建
（2）修改：
alter database 库名 character set gbk|utf8
（3）删除：
drop database 库名

2、表的管理
（1）创建:
create table 表名（列名 列类型 【长度，约束】,…列名 列类型 【长度，约束】)
（2）修改：alter table 表名 …
- 修改列名：
alter table 表名 change column 旧列名 新列名 新类型；
- 修改列的类型或约束
alter table 表名 modify column 列名 新类型
- 增加新的列
alter table 表名 add column 列名 类型
- 删除列
alter table 表名 drop column 列名
- 修改表名
alter table 表名 rename to 新表名
（3）删除：
drop table 表名
（4）复制：
create table 新表名 like 表名；#复制表的结构
create table 新表名 select * from 表名；#复制表的结构+数据；也可以使用where仅复制部分数据或者罗列具体列名仅复制部分字段
！！！表名和新表可以不在同一个库中，只需要库名.表名 表示 表名 即可；

总结：表和库的创建都可以使用 if not exists 来完成有不创建无则创建 ；表和库的删除都可以使用 if exists 来完成有则删除无则不删除；

3、表的创建中涉及的数据类型：
（1）数值型：
- 整数：Tinyint smallint mediumint int(integer) bigint #默认是有符号，无符号在后面使用unsigned；int(n) zerofill 不足n位的0左填充
- 小数：定点数 （dec(m,d)或decimal(m,d)）和浮点数（double(m,d) float(m,d)）#整数保留m-n-1位，小数保留位数d位；两个都可省略；dec默认为10和0，float和double会自动匹配；
（2）字符型：
- 较短：char(M) varchar(M) #M表示最多字符数；varchar为可变的，但是效率低一些；
- 较长：text blob(二进制)
- 枚举类型： enum(’’,’’,’’,…) #限定了值的范围，不在范围内的为null;
- set类型：set(’’,’’ ,…) #和enum类似，但是可以赋值的不只是单个，可以是子集
（3）日期型：
- date:只有日期
- time:只有时间
- datetime：日期+时间
- timestamp:与datetime类似，和时区有关，更准确；但范围较小
4、表的创建涉及的常见约束：
（1）not null ：
（2）default 值：默认值；
（3）primary key:主键，非空唯一；有固定的名字，pk
（4）unique ：唯一，可以为空；
（5）check( )： mysql 不支持，检查约束；
（6）foreign key: 外键，用于限制两个表的关系，保证从表中该字段的值必须来自于主表的关联列的值；关联列一般为主表的主键(更一般)或者唯一键
注意：
约束的添加只能在创建和修改表时，而且一定在数据填入前才有效；
！创建表时的约束分类：
- 列级约束: 字段类型后追加即可；多个约束只需罗列，且无序
check()和外键不支持；
虽然外键不可以直接使用，调整如下：foreign key references 主表名(字段名)；
- 表级约束：字段类型定义的最后 ，[constraint 约束名] 约束类型(字段名) ，省略时默认为字段名
constraint pk primary key(字段)，#当这里罗列多个字段，表示为联合主键
constraint uq unique(字段)，
constranit ck check( 字段约束 ),
constraint fk_从表_主表 foreign key(字段) references 主表名(字段名)
！修改表时增加约束：
- 列级：
alter table 表名 modify column 列名 新类型 新约束
- 表级：
alter table 表名 modify column 列名 新类型 新约束
alter table 表名 add 【constraint 约束名字】 约束类型(列名)
！修改表时删除约束：
- 列级：
alter table 表名 modify column 列名 新类型 ；#不写即可
- 表级：
alter table 表名 modify column 列名 新类型 ；
alter table 表名 drop 约束类型 约束名；#特别的唯一：index

5、标识列：（自增长列）不需要手动插入值，系统提供默认值
(1)创建表时设置标识列：
在定义字段时，语法：字段 类型 约束 auto_increment
在插入值时，为了保持字段和值个数相同，可以赋值的时候用null；也可以赋值，下次插入从此处的值自增；
set auto_increment_increment= ;#设置步长，mysql是支持的
set auto_increment_offset= ;#设置起始偏移量，但是mysql是不支持的；
标识列必须是一个key，即主键，外键，唯一键；
(2)修改表时设置标识列：
alter table 表名 modify column 列名 新类型 新约束 auto_increment;
(3)修改表时删除标识列：
alter table 表名 modify column 列名 新类型 新约束; #不写即可

补充：常用函数：:用于处理字段返回处理后的信息
1、单行函数：
字符函数：concat(str1,str2,str3,…); length(str)[一个汉字：utf8:3个字节；gbk:2个字节];
upper(str),lower(str);
substr(str,start_position[,length])(截取指定长度的子串，注意索引从1开始 )
instr(str1,str2)(返回str2在str1中的索引值，如果没有返回0
trim(str)（删除两侧的空格） 或者 trim(‘str1’ from str2)删除str2两侧的str1
lpad(str1,length,’*’):用指定的字符填充str1长度为length，如length小于str1长度，截断；rpad（）类似
replace(str,str1,str2):将str中的所有str1用str2来替换
数学函数：round(num[,length]):四舍五入；ceil(num)：向上取整；floor(num):向下取整
truncate(num,length):截断； mod(int1,int2); rand()
日期函数：now():当前系统日期时间；curdate():当前系统日期；curtime():当前系统时间
year(date类型)，month()， monthname():月份英文等等
datediff( , ):计算差的天数
str_to_date(str,’%y-%m-%d): Y(y) m© d H(h) i s
date_format(date,’%y-%m-%d’):将日期转化为字符串
其他函数：version() database() user() md5(str)

2、分组函数：用作统计使用；都忽略null值不处理；可以和distinct搭配去重，格式为sum(distinct 字段名)
sum() ; avg(); #参数只可以为数字类型，遇到字符当做0处理
max(); min(); #参数可以为数字类型，字符类型，日期类型（2016年大于2009年）；
count():统计的为非空的个数,可以为任何类型;
特别的有：count(*) 统计同一行数据中不是全null的行数；
count(n) 表示为每行增加一列值为n，然后统计n的个数，即行数
注意：和分组函数一同查询的字段要求只能是group by后的字段