变量，存储过程

变量

系统变量（全局变量，会话变量），自定义变量（用户变量，局部变量）

初始化&赋值&查看

查看所有系统全局变量

show global variables;

查看所有系统会话变量

show session variables;

设置&查看系统变量

set @@session.autocommit=1
select @@session.autocommit

设置&查看自定义变量

set @myvar = 1;
select @myvar;

将mytable的记录数赋值给$count

select count(*) into $count from mytable;

声明局部变量并赋值

ps：声明必须为begin end块的第一句，必须定义类型

begin
declare a int default 1;
declare b int default 2;
declare sum int;
set sum=a+b;
select sum;
end

存储过程

基础语法

delimiter 结束标记
create procedure myname(myparams)
begin
    存储过程体
end 结束标记

ps:

参数包含三部分

参数模式，参数名，参数类型
例子：IN name varchare(20)
参数模式：
IN：该参数可以作为输入，需要调用方传入值
OUT：该参数可以作为输出，该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出

如果存储过程体只有一句话，begin end可以省略

存储过程体中的每条sql语句结尾必须加分号。
delimiter重新设置存储过程的结尾，类比双引号，单引号，delimiter默认为；，但如果有很多；想一起执行，那还是换一个结束符号来处理，语法更清晰
语法：
delimiter 结束标记

调用语法

call funcname(params);

例子：

1. 空参存储过程

命令行下运行

delimiter $
create procedure myp1()
begin
  insert into table1(name)values('a1'),('a2'),('a3'),('a4'),('a5');
end $

调用

call myp1()$

查看

select * from table1 $

2.带IN和OUT参数的存储过程

MariaDB [test]> select * from table1 where name='a5';$
+----+------+-----------+------+
| id | name | addr      | age  |
+----+------+-----------+------+
|  9 | a5   | 劳动节    |   22 |
+----+------+-----------+------+
1 row in set (0.001 sec)

MariaDB [test]> create PROCEDURE myp2(IN uname VARCHAR(20),OUT addr VARCHAR(20),OUT age int)
    -> BEGIN
    -> SELECT table1.addr,table1.age into addr,age
    -> from table1 where table1.name=uname;
    -> end $
Query OK, 0 rows affected (0.010 sec)

MariaDB [test]> call myp2("a5",@addr,@age)$
Query OK, 1 row affected (0.001 sec)

MariaDB [test]> select @addr,@age $
+-----------+------+
| @addr     | @age |
+-----------+------+
| 劳动节    |   22 |
+-----------+------+
1 row in set (0.000 sec)

3.带INOUT参数的存储过程

MariaDB [test]> create PROCEDURE myp3(INOUT a int)
    -> BEGIN
    -> set a=a*2;
    -> end $
Query OK, 0 rows affected (0.020 sec)

MariaDB [test]> set @test=10$
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> call myp3(@test)$
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> select @test;
    -> $
+-------+
| @test |
+-------+
|    20 |
+-------+
1 row in set (0.001 sec)

删除存储过程

一次只能删一个
drop procedure proname;

查看存储过程的信息

show create procedure proname;

约束分为表级约束和列级约束，列级约束包括，主键，唯一，非空，默认值；外键约束一般写在表级约束里面

create table if not exists major(
    id int primary key,
    name varchar(20)
)

create table if not exists info(
    id int primary key,
    name varchar(20) not null,
     gender char(1),
    age int default 18,
    seat int unique,
    majorid int,
    constraint fk_info_major foreign key(majorid) referencer major(id) 
);

desc info;
show index from info;

1.注意外键字段必须是关联的主键或唯一键，关联其他键会报错

2.注意创建顺序，一定是先创建表，再创建带约束的关联表

3.插入和删除关联记录时候，注意顺序；插入先major后info；删除先info，后major

主键和唯一的区别

主键特点唯一，非空，一张表中至多一个主键，允许组合
唯一：可以为空，一张表中可以有多个唯一键，允许组合

create table if not exists info(
    id int,
    name varchar(20) not null,
     gender char(1),
    age int default 18,
    seat int,
    majorid int,
    constraint fk_info_major foreign key(majorid) referencer major(id),
    primary key(id,name),
    unique(age,seat)
);

自增id

alter table info modify COLUMN id int auto_increment

级联删除

constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete cascade;

alter table info add constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete cascade ;

级联置空

constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete set null;

alter table info add constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete set null;

dml 数据管理语言

truncate和delete的区别
1.truncate table tname; 清空数据表，初始化自增长id=1
delete id从断点开始
2.d有返回值，t无
3.d可以回滚，t无
4.d可以加where，t无
5.t删除效率高于d

ddl 数据定义语言

create database if not exists db; #如果db不存在，就创建一个叫db的数据库
alter database db character set gbk; #改变db数据库的字符编码
drop database if exists db; #如果db存在，就删除叫db的数据库

创建表

create table if not exists book(
#列名 类型【（长度） 约束】
published datatime,
id int unsigned #无符号整数 0～非负整数
id2 int(7) zerofill #显示长度7，不够用0填充
)

修改表的列名，修改列的类型或约束，添加新列，删除列，修改表名

alter table book change column published pubdate datatime;
alter table book modify column published timestamp;
alter table book add column name text;
alter table book drop column name;
alter table book rename to book1;

复制表的结构

create table copy like book;
create table c4 select id from book where 0; (只复制一个id)

复制表的结构+数据(where 部分数据)

create table copy2 select * from book;

1.concat连接字段查询 ， Ifnull(字段名，0):如果字段为null，则强制转换为0

<> 就是!=
select concat(name,"-",gender) from users;

2.模糊查询like, between and, in ,is null

通配符 %代表匹配任意个字符(包含0个字符)，_代表匹配一个字符
案例1:查询员工名称中包含字符a的员工信息
select * from employees where username like ‘%a%’;

案例2:查询员工名称中第三个字符为a，第5个字符为e的员工信息
select * from employees where username like ‘__a_e%’;

案例3:查询员工名称中第二个字符为_的员工信息
select * from employees where username like ‘_\_%’;
select * from employees where username like ‘_$_%’ ESCAPE ‘$’;#指定$为转义符

案例4:查出员工编号在100到120之间的员工信息
select * from employees where id >=100 and id <=120;
select * from employees where id between 100 and 120;
between and注意：
1.包含临界值
2.临界值顺序不能调换

案例5:查出员工编号为100或者120的员工信息
select * from employees where id =100 or id =120;
select * from employees where id in(100,120);

案例6:查询员工编号为null的记录

select * from employees where id is null; #注意不能用=
查询员工编号不为null的记录
select * from employees where id is not null;

案例7:

select * from employees；
select * from employees where id like ‘%%’;
select * from employees where address like ‘%%’; 

如果address有null值，则上面三条语句查询出的记录是不一样的，否则一样

3. 常见函数

select trim(a from “aaabaaacaaa”); #output: baaac
select lpad(a,3,0);# output: 00a
select rpad(a,3,0);# output: a00;
select now();
select curdate();
select year(now());
select datediff(d1,d2);
instr:获取子串第一次出现的索引
str_to_date:字符转日期
date_format:日期转字符

4.分组查询(默认忽略null)

案例：查询哪个部门的员工个数>2,having关键字添加分组后结果的筛选条件，where是在分组前筛选

select count(*),department_id from employees group by department_id Having count(*)>2;

5.多表查询

如果给表起了别名，那原始表名就不被认识了，因为from语句是先执行的
sq192语法
内连接：等值连接，非等值连接，自连接（查交集）
n表等值连接，必须有n-1个连接条件
自连接案例：查询员工名和上级名：
select e.employee_id, e.name, m.employee_id, m.name from employees
as e, employees as m
where e.manager_id = m.employee.id

sql199语法
外连接：左外，右外，全外(sql不支持)；交叉连接）
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接：inner
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】

交叉连接： cross join等价于sql192语法的笛卡尔乘集结果，count=表1记录数*表2记录数

sql199语法的内连接和sql192语法的内连接效果一样，写法格式不一样，推荐用sql199语法 ，
连接条件和筛选条件分离，可读性更强

sql199外连接特点：
1. 外连接查询结果为主表中的所有记录=内连接结果+主表中有而从表中没有的记录
如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示null
2.左外连接，left outer join左边是主表；右外连接，right outer join右边是主表
3.左外和右外交换两表顺序，可以实现同样的效果
4.全外连接=内连接结果+表1中有但表2中没有的+表2中有但表1中没有的，mysql不支持这种操作

6 子查询(select嵌套)

定义：标量子查询（结果1行1列）
列子查询（结果1列多行）
行子查询（结果1行多列）
表子查询（结果为多行多列）
按子查询出现的位置：
select后面只支持标量子查询
from，exists后面支持表子查询
where或having后面支持除表子查询以外的三种查询，用得比较多的是标量和列子查询

特点：1.子查询放在小括号内，2.子查询一般放在条件右侧 3.标量配合<>=用 4.列子配合in,any/some(min or max),all(max or min

7 union联合查询

查询语句1
union 默认去重
查询语句2
union all（加上all以后不去重）
查询语句3
。。。。。
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
注意事项：要求查询列数一致，类型，顺序一致