mysql总结

MySQL

MySQL常见命令

net start msyql; #启动mysql
net stop mysql; #停止mysql
show databases;   #查看所有库
use 库名; #打开指定的库
show tables; #查看当前库内的所有表
show tables from mysql;
select database(); #查看当前库
create table 表名(列名 列类型,name varchar(20)); #创建表
desc 表名; #查看表结构
select * from 表名; #查看表内数据
insert into 表名(id,name) values(序号，'rose'); #向表中插入数据
update 表名 set name='' where id=序号(id); #修改表中数据
delete from 表名 where id=序号; #删除指定数据
select version();  mysql --version  mysql -V #查看当前mysql版本

mysql不区分大小写

去重

select distinct department_id from 表名 #去重

+号的作用

只能数值相加，如果一边为字符型，那么会试图转换为数值型.

如果一边为null 那么一定为null

concat('a','b','c'); #输出abc

条件查询

语法：
	select 
			查询列表
	from
    		表名
   	where
   			筛选条件

模糊查询

like
between and 
in
is null | is not null
#安全等于
<=>

like用法

#案例 查询员工名中第二个字符为_的员工名
select 
		last_name
from  
		employees(数据库名);
where
		last_name like '';

between and

select 
   	*
from
   	employees数据库名;
where
   	employees_id between 100 and 120;

排序查询

 select 查询列表
 from  表
【where 筛选条件】
 order by 排序列表 【asc|desc】
# asc代表升序 desc代表降序

常见函数

select 函数名（实参列表）【from表】

分类

1.单行函数

如 concat、length、ifnull等

2.分组函数

功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

字符函数

#length 获取参数值的字节个数
select length('');
#concat 拼接字符串
select concat(,'',);
from 表名;
#upper、lower
select upper('');
#substr、substring
select substr('',索引,'');
#instr 返回字串第一次出现的索引，如果找不到返回0
select instr('','');
#trim 去前后空格
select trim('');
#lpad 用指定的字符左填充指定长度
select lpad('',指定长度,'指定的字符');
#replace 替换
select replace('');

示例 将姓变大写，名变小写，然后拼接

select concat(upper(last_name),lower(first_name)) from 表名;

示例

select trim('a' from 'aaaaa张aaaa三aaaa');

数学函数

#round 四舍五入
select round(1.45);
#ceil 向上取整,返回>=该参数的最小数
select ceil(1.00);
#floor 向下取整，返回<=该参数的最大整数
select floor(9.99);
#truncate 截断
select truncate(1.69999,1);
#mod 取余
select mod(-10,-3);

日期函数

#now 返回当前系统日期＋时间
select now();
#curdate 返回当前系统日期，不包括时间
#curtime 返回当前时间，不包括日期
#str_to_date 将字符通过指定的格式转换成日期
#date_format 将日期转换成字符

图片

其他函数

# version
# database
# user

流程控制函数

# if 函数  if else效果
# 1 case 函数 switch case
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
...
else 要显示的值n或者语句n;
end
# 2
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或者语句n
end

分组函数

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order 不要 排序列表】

1.都忽略null值

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

3.可以和distinct搭配实现去重

4.一般使用count(*)用作统计行数

5.和分组函数一同查询的字段要求是group by后的字段

#sum 求和
#avg 平均值
#max 最大值
#min 最小值
#count 计算非空值的个数

分组查询

select 分组函数，列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
# 按多个字段分组

多表连接

select 字段1,字段2
from 表1,表2,...;

笛卡尔乘积

select name,boyName from beauty,boys;

SQL92语法

等值连接

①多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

select e.last_name,e.job_id,j.job_title #select 查询列表
from 数据库名 e,表名 j #from 表1 别名,表2 别名
where e.'job.id' = j.'job_id'; #where 表1.key = 表2.key;

非等值连接

自连接

#select 查询列表
#from 表 别名1,表 别名2
#where 等值连接;

SQL99语法

语法

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
where 筛选条件

分类

内连接： inner

外连接： left【outer】

​			左外： left【outer】

​			右外： right【outer】

​			全外： full【outer】

交叉连接：cross

内连接

外连接

全外

交叉连接

select 查询列表
from 表1 别名
cross join 表2 别名;

子查询

​

分页查询

union联合查询

语法

查询语句1
union
查询语句2
union
...

DML语言

插入语句

#①
insert into 表名(列名,...)
values(值1,....);
#②
insert into 表名
set 列名=值，列名=值....

修改语句

update 表名
set 列=新值,列=新值...
where 筛选条件

删除语句

#① 单表删除
delete from 表名 where 筛选条件
#② 
truncate table 表名;

Sql192语法

# 多表删除
delete 表1的别名，表2的别名
from 表1 别名。表2 别名
where 连接条件
and 筛选条件

Sql199语法

# 多表删除
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

DDL

创建：create
修改：alter
删除：drop

库的管理

create database [if not exists] 库名;

#更改库的字符集
alter database books character set 编码;

库的删除

drop database [if exists] books;

表的创建

create table 表名{
		别名 列的类型[(长度) 约束],
		别名 列的类型[(长度) 约束],
		别名 列的类型[(长度) 约束],
		...
		别名 列的类型[(长度) 约束],
}

表的修改

修改列名

alter table book change column 旧列名 新列名 datetime;

修改列的类型或约束

alter table book modify column 列名 timestamp;

添加新列

alter table author add column 列名 double;

删除列

alter table author drop column 列名;

修改表名

alter table author rename to 表名;

表的删除

drop table [if exists]表名;

表的复制

#仅仅只复制表的结构
create table copy like 表名;
#复制表的结构+数据
create table copy2
select * from 表名;
#只复制部分数据
create table copy3
select id,name 
from 表名
where nation = 中国;
# 仅仅复制某些字段
create table 表名
select id,au_name
from author
where 1=2;

常见的数据类型

数值型:
		整数
		小数:
				定点数
				浮点数
字符型:
		较短的文本:char、varchar
		较长的文本:text、blob(较长的二进制数据)
日期型

整型

浮点型

字符型

日期型

常见约束

create table 表名(
	字段名 字段类型 约束
)

创建表时添加约束

直接在字段名和类型后面追加 约束类型即可

create table stuinfo(
		id INT PRIMARY KEY,
    	stuName VARCHAR() NOT NULL,
    	gender CHAR(1) CHECK(gender='男' OR gender='女'),检查
    	seat INT UNIQUE,#唯一
    	age INT DEFAULT 18,#默认约束
)

添加表级约束

#表级约束加在最后
create table stuinfo(
		id INT ,
    	stuName VARCHAR(),
    	gender CHAR(1),
    	seat INT ,
    	age INT,
        CONSTRAINT A PRIMARY KEY(id), #id约束
);

主键和唯一的对比

外键的特点

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3. 主表的关联列必须时一个key(一般是主键或者唯一)
4. 插入数据时，先插入主表，再插入从表，删数据时，先删从表，再删主表

修改表时添加约束

#添加非空约束
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;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

修改表时删除约束

#删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;

标识列

创建表时设置标识列

CREATE TABLE 表名(
		id INT PRIMARY KEY AUTO_INCREMENT,#添加表示列
		NAME VARCHAR(20)
);

修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT ;

TCL语言(Transaction 事务控制语言)

事务的创建

隐式事务

事务没有明显的开始和结束标记

比如：insert update delete语句

delete from 表 where id = 1;

显示事务

事务具有明显的开启和结束标记

**前提：**必须先设置自动提交功能为禁用 set autocommit=0;

步骤1：开启事务

set autocommit=0;
start transaction; #可选的
# 步骤2：编写事务中的SQL语句(select insert update delete)
# 步骤3：结束事务
commit; #提交事务
rollback; #回滚事务

例子：

#开启事务的语句：
update 表 set zs的余额=500 where name='zs'
#结束事务的语句：
update 表 set gx的余额=1500 where name='gx'

事务的隔离级别

事务的隔离级别

read uncommitted; # 出现脏读、幻读、不可重复读
read committed; # 避免出现脏读、出现幻读和不可重复读
repeatable read; # 避免脏读、幻读  但会出现幻读
serializable; # 避免出现脏读、幻读、不可重复读

savepoint 节点名; 设置保存点

用法:

set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a; #设置保存点
delete from account where id=28;
rollback a;

MySQL中默认 从第三个隔离级别repeatable read

视图

创建视图

create view 视图名
as
# 查询语句;

视图的修改

#方法一
create or replace view 视图名
as
查询语句;
#方法二
alter view 视图名
as
查询语句;

删除视图

drop view 视图名，视图名，...;

查看视图

desc 视图名;
show create view 视图名;

视图的更新

create or replace view 视图名;
as
select last_name,email,salary*12*(1+ifnull(commission_pct,0)) "annual salary"
from employees;

变量

系统变量

#查看所有的系统变量
show global| [session] variables;
#查看满足条件的部分系统变量
show global| [session] variables like '%char%';
#查看指定的某个系统变量的值
select @@global| [session].系统变量名;
#为某个系统变量赋值
1. set global| [session] 系统变量名=值;
2. set @@global| [session].系统变量名=值;

会话变量

#查看所有的会话变量
show session variables;
#查看满足条件的部分会话变量
show [session] variables like '%char%';
#查看指定的某个会话变量的值
select @@session.会话变量名;
select @@会话变量名
#为某个会话变量赋值
1. set session 会话变量名=值;
2. set @@session.会话变量名=值;

自定义变量

用户变量

赋值的操作符：=或:=

# 声明并初始化
set @用户变量名=值;或
set @用户变量名:=值;或
set @用户变量名:=值;
# 赋值
1.通过set或select
set @用户变量名=值;或
set @用户变量名:=值;或
select @用户变量名:=值;

2.通过select into
select 字段 into 变量名
from 表;

局部变量

# 声明
declare 变量名 类型;
declare 变量名 类型 default 值;
# 赋值
1.通过set或select
set 用户变量名=值;或
set 用户变量名:=值;或
select @用户变量名:=值;

2.通过select into
select 字段 into 变量名
from 表;
# 使用
select 局部变量名;

储存过程语法

空参列表存储过程

结束标记 $

# 创建语法
create procedure 储存过程名(参数列表)
begin
		存储过程体(一组合法的SQL语句)
end $
# 调用语法
call 存储过程名(实参列表);
# 查看
show create procedure 存储过程名;

参数模式：in out inout

删除存储过程

# 语法：
drop procedure 存储过程名;

函数

创建语法

create function 函数名(参数列表) returns 返回类型
begin
		函数体
end

使用delimiter语句设置结束标记 delimiter $;

调用语法

select 函数名(参数列表)

查看函数

show create function 函数名;

删除函数

drop function 函数名;

流程控制结构

分支结构

if函数 功能：实现简单的双分支

# 语法
if(表达式1，表达式2，表达式3)

case结构

case 变量|表达式|字段
when 要判断的值 then 返回的值1
when 要判断的值 then 返回的值2
...
else 要返回的值n
end

if结构 功能：实现多重分支

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句n;]
end if;
# 应用场合：在begin end中

循环结构

分类

while loop repeat 

循环控制

iterate类似于 continue 继续，结束本次循环，继续下一次
leave类似于 break 跳出，结束当前所在的循环

# while
while 循环条件 do
		循环体;
end while [标签];
#loop
[标签] loop
		循环体;
end loop [标签];
#repeat
[标签]repeat
		循环体;
until 结束循环的条件
end repeat [标签];