MySQL基础之基本命令及数据查询语句总结

一、MySQL的背景

前身属于瑞典的一家公司，MySQL AB
08年被sun公司收购
09年sun被oracle收购

二、MySQL的优点

1、开源、免费、成本低
2、性能高、移植性也好
3、体积小，便于安装

二、MySQL与Oracle的区别

1、数据库区别
Oracle数据库是一个收费的重量型数据库。服务收费（数据恢复、数据迁移）
MySQL是一个开源的免费的轻量型数据库。无提供服务
2、存储引擎的区别
（1）Oracle，SqlServer等数据库只有一种存储引擎。
（2）MySQL提供了插件式的存储引擎架构InnoDB MyISAM、MEMORY等。
3、事务的隔离级别：
（1）Oracle支持的2种事务隔离级别:READ COMMITTED , SERIALIZABLE。
Oracle 默认的事务隔离级别为:READ COMMITTED（读已提交）
（2）Mysql支持4种事务隔离级别.。
Mysql默认的事务隔离级别为: REPEATABLE READ（可重复读）
4、全外连接：
全外连接mysql不支持，oracle支持
5、存储方式
（1）mysql数据以库形式进行存储，连接指定库读取指定库中数据
（2）oracle以全局数据库存储，只拥有一个库，通过不同表空间进行表的存储与使用，根据连接的用户使用相应用户默认表空间中的数据
6、自增
（1）mysql提供了数值字段自增约束 auto_increment，进行添加时可以自增

（2）oracle没有提供自增约束，需要定义序列在添加时使用 序列名.nextval进行赋值
7、 单引号的处理
（1）mysql里可以用双引号包起字符串也可以用单引号
（2）ORACLE里只可以用单引号包起字符串。在使用数据库操作字符串时使用单引号作为字符串标识

8、分页查询

（1）mysql提供了分页关键字limit 可以快速对结果进行指定条数的获取

（2）oracle没有提供分页关键字，而是通过子查询伪列rownum的形式进行指定条数的获取

9、权限与安全

（2）mysql通过连接ip、账号、密码的形式进行校验，安全性相对较低，可能被模拟主机进行登录

（2）oracle通过ip、账号、密码、权限、默认表空间进行连接与数据安全的校验 安全性较高

10、性能诊断
（1）MySQL的诊断调优方法较少，主要有慢查询日志。
（2）Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等

三、MySQL的版本及配置

版本

有5.5 、5.6、 5.7、 8.0版本
配置信息：
D:\MySQL\MySQL Server 5.5目录下的my.ini文件中可以更改服务器端口号（mysqld下）、等的一些配置

四、MySQL的基本操作

net start 服务名（启动服务）
net stop 服务名（停止服务）

登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码

mysql -h localhost -p3306 -uroot -proot
mysql -u root -proot

退出：
exit或ctrl+C

五、MySQL的常见命令

1.查看当前所有的数据库
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |保存原数据信息
| mysql              |保存用户信息
| performance_schema |收集性能信息
| test               |用于测试 空的
+--------------------+

select database();//查看当前所在的库

2.打开指定的库
use 库名;

3.查看当前库的所有表
show tables;

4.查看其它库的所有表
show tables from 库名;

5.创建表
create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);
6.查看表结构
desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

六、MySQL的语法规范

1. 不区分大小写,但建议关键字大写，表名、列名小写
2. 每条命令最好用分号结尾 \g也可
3. 每条命令根据需要，可以进行缩进 或换行
4. 注释
   单行注释：#注释文字
   单行注释：-- 注释文字
   多行注释：/* 注释文字 */

七、SQL的语言分类

DQL（Data Query Language）：数据查询语言
	select 
DML(Data Manipulate Language):数据操作语言
	insert 、update、delete
DDL（Data Define Languge）：数据定义语言
	create、drop、alter
TCL（Transaction Control Language）：事务控制语言
	commit、rollback

八、 常见类型

整型：

Tinyint(1字节) 、smallint(2字节) 、 mediumint(3字节) 、 Int、intger(4字节) 、 Bigint(8字节)
特点：
1、都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
2、如果插入数值超出了范围 则报out of range异常 并且插入的是临界值
3、长度可以不指定不影响，其默认的是11 11是单元格可见的长度，左边不够可以用zreofill 零填充，并且默认变为无符号整型

浮点型：

定点数：
dec|decimal（M,D） 8字节
精度比double更精确 货币运算 等使用

浮点数：
float(M,D)(4)
double(M,D)(8)

特点：
1、M和D ······M:整数部位+小数部位 即总长度······· D：小数部位
2、如果超过范围，则报out of range异常，则插入临界值
3、 m和d的可以省略
但如果是decimal 默认m和d（10，0）
float double 则会根据数值的精度来决定精度
4、如果精度要求较高，则优先考虑使用定点数

字符型：

较短的文本：char、varchar

       			 	写法  			 M的意思   			 	特点  	 		 空间的耗费    效率
char 		  char(M) 			 	最大的字符数  		固定长度的字符	 	  比较耗费		高
varchar 	varchar(M)					同上	        可变长度的字符      	 比较节省        低

char(M)的M可以省略，默认为1
varchar(M)的M不可以省略

binary和varbinary类型
只包含二进制字符串

Enum枚举 只能设置它定义时所包含的字符 只能其中保存一个 若设置了别的值 则被警告 结果为空  大写存储的也是小写
例： create table tab_char{
		c1 enum('a','b','c')
		
}; 
insert into tab_set values('a');
insert into tab_set values('A');  

set类型
集合 里面可以保存0~64个成员 可以保存多个字符  大写存储的也是小写
 create table tab_char{
		s1 set('a','b','c','d')
}; 
insert into tab_set values('a');
insert into tab_set values('A','B');  
insert into tab_set values('a,b');

	较长的文本：text、blob(较长的二进制数据)

日期型

日期型：

    date 	 	    4    1000-01-01   
 ★ datetime  		8    1000-01-01 00：00：00
 ★ timestamp 		4    1000-01-01 00：00：00 比较容易受时区、语法模式、版本影响
    time			3    00:00:00
    year	  		1    1001

datetime和timestamp的区别
1、 timestamp支持的时间范围较小 datetime更大
2、 timestamp和实际时区有关 更能反应实际的日期，而datetime则只能反映出插入时的当地时间
3、 timestamp的属性受mysql版本和SQLMode的影响很大

​					字节     	 范围    	时区等的影响
datetime  	 8      1000-9999      不受
timestamp    4      1970-2038		受

九、常见约束

一种限制 用于 限制表中的数据，为了保证表中的数据的准确性和可靠性

（1）六大约束

not null：非空约束 用于保证该字段的值不能为空

default ：默认，用于保证该字段值有默认值 比如：性别

primary key : 主键，用于保证该字段的值具有唯一性，并且非空，比如学号，员工编号等

unique ：唯一，用于保证该字段的值具有唯一性，可以为空

​ 比如：座位号

check ：检查约束【mysql中不支持 不报错但无效果】

foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值 在从表添加外键约束，用于引用主表中某列的值

比如：学生表的专业编号、员工表的部门编号、员工表的工种编号

主键和唯一的对比：

保证唯一性     是否允许为空       					一个表中可以有多少个  			  是否允许组合

主键					是						否										一个						是 不推荐

唯一					是						是（但只能有一个为空） 多个			 是 不推荐

组合：两个列组合成主键时，只有当两个列内容重复时，才报错  

​ 其中一个列重复不报错

外键：

1、要求在从表设置外键关系

2、从表的外键列的类型和主表的对应列的类型要求一致或兼容，名称无要求

3、要求主表中的对应列必须是一个key(一般是 主键、唯一)

4、插入数据时，先插入主表，再插入从表

 删除数据时，先删除从表，再删除主表

（2）约束添加分类：

列级约束：

六大约束语法都支持，但外键约束没有效果 一列可以加多个约束

表级约束：

除了非空，默认，其他都支持

语法：在各个字段的最下面

【constraint 约束名】 约束类型（字段名）

​ 支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

create table   stuinfo{
#列级约束
	id int  primary  key ,  #主键
	stuName  varchar(20)   not   null,default '李四',   #非空
	gender  char(1) check(gender = '男' or gender = '女')，#检查
	seat  int  unique ,#唯一
	age   int  default  18 , #默认约束
	majorId int foreign key references major(id), #外键
#表级约束
	constraint pk primary key(id),#主键
	constraint uq unique(seat),   #唯一
	constraint ck check(gender ='男' or gender ='女'),#检查
	constraint fk foreign key(majorid) references major(id)
	
}

create table major{
	id int primary key,
	majorName varchar(20)

}

desc stuinfo;查看结构（非空 主键等）

show index from stuinfo;查看stuinfo表中所有的索引，
						包括 主键、外键、唯一

（3）添加约束的时机：

1、创建表时

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）

)

注意：
支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

​ 通用写法 外键可以写在表级约束

create  table  if not  exists  stuinfo{
	id int  primary  key,  #主键
	stuName  varchar(20)   not null,   #非空
	gender  char(1) ,
	seat  int  unique ,#唯一
	age   int  default  18 , #默认约束
	constraint fk_primary_major foreign key(majorid ) references major(id) 

}

2、修改表时

1、添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束；

2、添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型（字段名）；

1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;

2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;

3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;

5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

例：
alter  table stuinfo modify  column stuname varchar(20) not null;
alter  table stuinfo modify  column age int default 18;
alter  table stuinfo modify  column id  int primary key;

四、自增长列

特点：

1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
​ auto_increment_increment

如果要更改起始值：手动插入值
如果要更改步长：更改系统变量

set auto_increment_increment=值;

2、一个表最多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key

（1）创建表时设置自增长列

创建

create table 表(
	字段名 字段类型 约束 auto_increment ，
​	字段名2 字段类型 约束
)

插入数值：

insert  into  表 （字段名，字段名2）  values(null,'join');

insert  into  表 (字段名2)   values ('join');

（2）修改表时设置自增长列

alter table 表 modify column 字段名 字段类型 约束 auto_increment

（3）删除自增长列

alter table 表 modify column 字段名 字段类型 约束 

十、DQL语言的学习

基础查询

查询常量
select 常量值;
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

查询表达式
select 100/1234;

去重
select distinct 字段名 from 表名;

条件查询：
根据条件过滤原始表的数据，查询到想要的数据

一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>不等于
					
二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：  

	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true
	
三、模糊查询
1、like:一般和通配符搭配使用，可以判断字符型或数值型
2、通配符： %任意多个字符   _任意单个字符
			当用到通配符时，使用转义字符 \ 
   			例  \_    \%

select last_name from empolyees 
where last_name like "%a%";

3、between 临界值 and 临界值
	①可以提高语句的简洁度
	②包含临界值
	③两个临界值不能交换顺序 

4、in 
	select last_name,job_id
	from employees
	where job_id IN ("IT","AD","AD_pre");

5、is null /is not null：用于判断null值
=不能判断null值
isnull或is not null可以判断null

6、<=> 安全等于  判断是否等于 
例子
salary <=> 12000;

is null PK <=>
			普通类型的数值	null值		可读性
is null			×			√			√
<=>				√			√			×

排序查询

order by 排序的字段|表达式|函数|别名 【asc|desc】
asc ：升序 默认升序
desc：降序


2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后（除limit语句之外）
 
 select * 
 from employees
 where email like '%e%'
 order by salary desc,part_id asc;
 

常见函数

一、单行函数
1、字符函数
length()获取字节个数
concat(varchar ...)拼接
substr(字符串)截取子串
索引位置从1 开始
	截取指定索引处后面的所有字符
	select substr("abcdef",3) out;//cdef
 	截取从指定索引处的指定的长度的字符
	select substr("abcdef",3,2) out;//cd

 instr(主串，子串)返回子串第一次出现的索引
upper()转换成大写
lower()转换成小写
trim()去前后指定的空格和字符
ltrim()去左边空格
rtrim()去右边空格
replace(主串，需替换的子串，新子串)替换
lpad（'qwe',10,'a'）左填充  用指定的字符左填充指定长度
rpad('qwe',10,'a')右填充
rand()随机数 返回0-1之间的小数  
password(”字符“) 返回该字符的密码形式
md5(”字符“)   

2、数学函数	
round(数值) 四舍五入	
rand   随机数	
floor向下取整	
ceil向上取整	
mod(a,b)取余	a-a/b*b
truncate截断  小数点后保留几位
 
3、日期函数	
now：返回当前日期+时间   select now();
year:返回年
month：返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
select str-to_date('1992.10,10','%y,%c,%d');
curtime：返回当前时间
hour:小时
minute:分钟
second：秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月

4、流程控制函数	

1、if 处理双分支	
select if(10>5,"大"，”小");
          
2、case情况一
case 要判断的字段或表达式或变量	
when 常量1：then 要显示的值1或语句1;
when 常量2：then 要显示的值2或语句2;       
...
else 要显示的值n或者语句n;
end;
  
  case情况二
case 
when 条件一：then 要显示的值1或语句1
when 条件二：then 要显示的值2或语句2    
...
else 要显示的值n或者语句n
end
  
          
5、其他函数	
version()版本	
database()当前库	
user()当前连接用户

二、分组函数
sum 求和	
max 最大值	
min 最小值	
avg 平均值	
count 计数	
特点：	
1、以上五个分组函数都忽略null值，除了count(*)

2、sum和avg一般用于处理数值型		
max、min、count可以处理任何数据类型    

3、都可以搭配distinct使  用，用于统计去重后的结果	

4、count的参数可以支持：	
字段、*、常量值，一般放1：统计所有行	

MyISAM存储引擎，count（*）最高
Inn0DB存储引擎：count(*)和count(1)效率>count(字段)

建议使用 count(*)
5、和分组函数一同查询的字段要求是group by后的字段

分组查询

特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
         				针对的表	    位置			关键字
	分组前筛选：			原始表		group by的前面    where
 分组后筛选：			分组后的结果集   group by的后面    having

4、可以按多个字段分组，字段之间用逗号隔开

 、可以支持排序 （排序放在整个分组查询的最后）
 
6、having后可以支持别名


例 
按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数》5的有哪些 降序排列
select count(*),length(last_name)
	from employees
	Group by length(last_name) desc
	having count(*)>5 ;

多表连接查询

内连接

等值连接：

① 为表起别名
② 多表连接至少需要n-1个连接条件
③ 多表的顺序可以调换
④等值连接的结果是多表的交集部分

92语法：
select 查询列表
from  表1  别名  ， 表2  别名
where  表1.key  =  表2 . key
【and  筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

非等值连接

 select 查询列表
from  表1  别名  ， 表2  别名
where  非等值的连接条件
【and  筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】   

自连接

 select 查询列表
from  表  别名1  ， 表 别名2
where  等值的连接条件
【and  筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】   

内连接所有99语法

99语法：
select  查询列表
from 表一   别名   【连接类型】
join  表二     别名     on    连接条件 
【where  筛选条件】
【group by  分组】
【having  筛选条件】
【order by  排序列表】 

外连接

用于查询一个表中有，另一个表中没有的记录
特点：

1、外连接的查询结果为主表中的所有记录

​ 如果从表中有和它匹配的，则显示匹配的值

​ 如果从表中没有和他匹配的，则显示null

​ 外连接查询结果=内连接结果+主表中有而从表中没有的记录

2、左外连接：left左边的是主表

​ 右外连接：right右边的是主表

左外和右外交换两个表的顺序，可以实现同样的效果

分类：

​ 左外：left 【outer】

​ 右外：right【outer】

​ 全外：full 【outer】

子查询
一 、含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询

二、分类：

1 、按出现的位置：

子查询可以放在

• select后面 仅仅支持标量子查询
• from后面 支持表子查询
• where后面、having后面
• 标量子查询（单行）、列子查询（多行）、行子查询
• exists后面 结果为1或0 （相关子查询），但一般放在条件的右侧 标量子查询、列子查询、行子查询、表子查询

2、按结果集的行列 ：

标量子查询（单行子查询） 结果集只有一行一列
列子查询（多行子查询） 一列多行
行子查询 多行多列
表子查询 多行多列 （任意）

1、标量子查询  

 查询最低工资的员工姓名和工资

select name,salary from  employees
where salary = (
	select  min(salary)  from   employees;
);

2、多行子查询：

使用多行比较操作符
in/not in  		等于列表中的任意一个            等于  {10，20，30 }  中的任意一个
any|some 	和子查询返回的某一个值比较      比较{10，20，30 }  中的任意一个
All   				和子查询返回的所有值比较          比较{10，20，30 }  中所有元素



查询  location_id是1400或1700的部门中的所有员工姓名

1 查询location_id 是1400或1700的部门编号

select last_name
from employees 
where department_id in(
​	select distinct  department_id
   from  departments
​	where location_id in(1400,1700)
)

分页查询

select 字段|表达式,...
from 表
【where 条件】  join type join 表2
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引位置   可不写】条目数;


查询前五条员工信息
select  *   from employees 
Limit 0,5;

1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式：select * from  表 limit 
（page-1）*sizePerPage,sizePerPage
假如:每页显示条目数sizePerPage要显示的页数 page

联合查询
引入：
union 联合、合并
特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

select 字段|常量|表达式|函数 【from 表】 【where 条件 】 
union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 
 union  【all】
.....select 字段|常量|表达式|函数 【from 表】 【where 条件】

使用联合查询
select *    from  emloyees	where department_id>90
union
select *    from  emloyees	where email like "%a%";