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):数据操作语言
	insertupdatedelete
DDL(Data Define Languge):数据定义语言
	createdropalter
TCL(Transaction Control Language):事务控制语言
	commitrollback

八、 常见类型

整型:

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');

	较长的文本:textblob(较长的二进制数据)
日期型

日期型:

    date 	 	    4    1000-01-01datetime  		8    1000-01-01 000000timestamp 		4    1000-01-01 000000 比较容易受时区、语法模式、版本影响
    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 表名 addconstraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

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

5、外键
添加外键
alter table 表名 addconstraint 约束名】 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  表 (字段名,字段名2values(null,'join');

insert  into(字段名2)   values ('join');
(2)修改表时设置自增长列
alter tablemodify column 字段名 字段类型 约束 auto_increment
(3)删除自增长列
alter tablemodify 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
	
三、模糊查询
1like:一般和通配符搭配使用,可以判断字符型或数值型
2、通配符: %任意多个字符   _任意单个字符
			当用到通配符时,使用转义字符 \ 
   			例  \_    \%

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

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

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

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

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

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


排序查询


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


2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3order 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、流程控制函数	

1if 处理双分支	
select if(10>5,"大",”小");
          
2case情况一
case 要判断的字段或表达式或变量	
when 常量1then 要显示的值1或语句1;
when 常量2then 要显示的值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、可以按多个字段分组,字段之间用逗号隔开

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


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

多表连接查询

内连接

等值连接:

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

92语法:
select 查询列表
from1  别名  , 表2  别名
where1.key  =2 . keyand  筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

非等值连接

 select 查询列表
from1  别名  , 表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  		等于列表中的任意一个            等于  {102030 }  中的任意一个
any|some 	和子查询返回的某一个值比较      比较{102030 }  中的任意一个
All   				和子查询返回的所有值比较          比较{102030 }  中所有元素



查询  location_id是14001700的部门中的所有员工姓名

1 查询location_id 是14001700的部门编号

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 join2group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引位置   可不写】条目数;


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

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

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

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

使用联合查询
select *    from  emloyees	where department_id>90
union
select *    from  emloyees	where email like "%a%";
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值