mysql基本语法和高级语法

一，插入

插入（单条）：

insert into tabel (变量1，变量2…) values(‘123’,‘456’)

插入（多条）：

insert into tabel (变量1，变量2…) values(‘123’,‘456’),
(‘abd’,‘bcd’),
(‘abd’,‘bcd’),…

二，模糊查询：

select * from tabel where xxx like '888%'
select * from tabel wherexxx like '%888%'
select * from tabel where xxx like '%888’

三，范围查询：

select * from tabel where xxx between 18 and 20
select * from tabel where xxx in(18,20)
select * from tabel where xxx not in(18,20)

四，聚合函数：
mix()最小， max()最大值， avg()平均值， sum()总值， count()次数

select count(*) from tabel

case…when…then语句使用
case…when…then语句，相当于编程语言中if判断

根据IsUser字段查询学生是否在线：

select a.StudentID,
(case a.IsUse
when ‘0’ then ‘未在线’
when '1’then ‘在线’ else ‘未上传’ end) as 在线情况
from StudentBindPaperTypeEntity as a

top取数

例1.取出表中第几行数据（如第一行）
select top 1 * from StudentBindPaperTypeEntity

例2.取出表中百分之多少数据
select top 50 percent * from StudentBindPaperTypeEntity

五，排序去重
排序：desc倒序，asc正序

select * from tabel order by id desc

去重：distinct

select distinct(age) from tabel

限制：limit

select * from tabel order by id desc limit 10 只查看10条
select * fromtabel order by id desc limit 2,5 从第二个后开始查看5条（不包含第2个）–一般用于分页
参数2 指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。
参数5 指定要返回的最大行数。
另一种写法：
select * fromtabel order by id desc limit 5 offset 2

六，分组查询：
–一般是将数据进行分类汇总（可对多个字段分组）
使用group by的sql语句，select后的字段，只能是group by后面的字段，如果想要展示其他列，需要给该列使用聚合函数，否则默认展示分组里的第一行数据

select region,sum(price) from tabel group by region

七，分组过滤：
having对分组之后的数据进行过滤，搭配group by使用

select region,sum(price) as total_price from tabel group by region having total_price>1000

八，sql执行顺序：
from …where…group by…having…select…order by…

九，连接查询：
join：主要用于链接两个或多个表
inner join
以某字段为条件，取左表和右表中同时存在相同数据的行，相当于两个表的交集

select * from user u inner join order o on u.id=o.user_id
查询出两个表id一致的数据

full join
只要其中一个表中存在匹配，就会生成一行，另一个表的列值为空。
结果示例：

十，左连接和右连接查询：
（1）左连接 left join，以某字段为连接条件，取左表的全部数据+右表与该字段对应的数据，可能会有某些数据在左表存在但是右表不存在的情况（展示空）

select * from user u left join order o on u.id=o.user_id

查询出u表中有id的数据，o表中如果没有user_id展示空

（2）右连接 right join，以某字段为连接条件，取右表的全部数据+左表与该字段对应的数据，可能会有某些数据在右表存在但是左表不存在的情况（展示空）

select * from user u right join order o on u.id=o.user_id
查询出o表中有user_id的数据，u表中如果没有id展示空

十一，嵌套查询：
多个select语句进行嵌套，嵌套的位置可以在select后，from后，where后
（1）select后：
注意：select后面查出来的值只能是一行，一列

select (select region from order where id=‘2’),phone from user
从订单表查了一个区域，再从user表查了一个phone来临时组建成一个表

（2）from后：
放在from后面需要给这个嵌套重新命名，相当于from后面的表是一个临时通过select语句来组建的一个新表

select od.region from (select region,price from order) od

（3）where后：
将查询出来的值作为下一个查询的条件

select phone from user where id in (select user_id from order
where regin=‘北京’)

十二，union语句：
union：用于合并两个或多个 SELECT 语句的结果集。

注：union和union all语句中，内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
（1）union
可以将多个select语句的结果合并为一个，合并后会删除重复的数据

select phone from user union select tel from order --没有重复数据

（2）union all
可以将多个select语句的结果合并为一个，合并后不会删除重复的数据

select phone from user union select tel from order --可以有重复数据

十三，数据库运算符：
（1）算术运算符
+加，-减，*乘，/除，%取余
（2）比较运算符
‘=’等于，!=不等于，>=大于等于，<=小于等于，in null为空，is not null不为空
（3）逻辑运算符
not或!–逻辑非，and–逻辑与，or–逻辑或

十四，数据库索引：（与数据库性能息息相关）
注：主键本身就是一种唯一索引
表字段添加所有可以大大提高mysql的检索速度，数据库比喻一本字典，那索引就是字典的目录，一般会针对where后面的字段添加索引
添加普通索引
alter tabel 表名add index 索引名称(表字段）

例子：alter tabel order add index idx_user_id(user_id)

添加唯一索引

alter tabel order add unique idx_user_id(user_id)

删除索引
drop index 索引名称 on 表名

例子：drop index idx_user_id on user

十五，表复制：
命令有两个步骤：复制表，复制数据
（1）复制表–此时还是空数据
create table 复制后的表名 like 被复制的表名

例子：create table user_copy like user

（2）复制数据
insert into 复制后的表名select * from 被复制的表名

例子：insert into user_copy select * from user

十六，字符串函数
（1）concat()函数，将两个或者多个字符串组合成一个字符串

select concat(user_name,phone) from user

（2）length()函数，获取字符串的长度

select length(phone) from user

（3）replace(字段名,被替换字符,替换字符)函数，搜索并替换字符串中的子字符串

select length(email,‘mtx’,‘text’) from user --将email字段中的mtx替换成test

（4）substring()函数，从具有特定长度的位置开始提取一个子字符串

select substring(phone,8,4) from user --获取手机号的后四位

十七，时间函数
（1）curdate()函数，返回当前日期

select curdate()

（2）now()函数，返回当前日期和时间

select now()

（3）year()函数，返回日期中的年份

select  year('2019-10-18')   或者  select  year(create_time) from user

（4）month()函数，返回日期中的月份
（5）day()函数，返回日期中的天数

十八，数据处理函数
（1）rand()函数，获取0-1之间的随机数（可以使用加法以及乘法来实现各个范围的随机数）

select rand()*100 取0-100之间的随机数

（2）floor()函数，小数取整

select floor(10+rand()*90 ) 取10-100之间的整数

十九，主键和外键
主键是唯一标识表中每行的列或一组列，主键必须是唯一值，主键列不包含null值，一个表中只能有一个主键，且主键字段的类型必须是整数类型
外键表示一个表1中的一个字段A被另外一个表2中的一个字段B引用，外键可以在数据库层面上保证数据的完整性，当删除表1或者表2中的数据的时候，数据库都会检查一下是否可以被删除

二十，存储过程语法
是在大型数据库中，一组为了完成特定功能的sql语句，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程中的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库的一个重要对象。类似于写java python的一些脚本函数一样
一般在测试中可以用来造数据
总结：存储过程=逻辑+sql
逻辑：
（1）while循环语法

while 条件 do
语句;
end while;

（2）if判断语法

if 条件 then
语句;
else 语句;
end if;

需求：构造10000个测试账号
需要创建一个存储过程，在navicat中点击创建一个过程，然后写入存储过程
存储过程：

begin 
    --定义所需要的变量，使用declare
    declare i  int(6) default 6;
    declare u_name varchar(10);
    declare u_age int(3);
    declare u_gender int(2);
    declare u_phone verchar(11);
    declare u_email verchar(20);
    --while循环来批量构造数据
    while i<10000 do
       --判断奇数偶数来定义性别数据
       if i%2=0 then
         set u_gender=0;
       else
         set u_gender=1;
       end if;
    --set方法来实现赋值
    set u_phone=concat('188130',i+10000);
    set u_name=concat('testname_',i);
    set u_age=floor(rand()*100);
    set u_email=concat(u_name,'@qq.com');
    --使用sql语句插入数据
    insert into user(user_name,password,age,gender,phone,email,create_time)
      values (u_name,'111111',u_age,u_gender,u_phone,u_emai,now());
    --i递增
    set i=i+1;
    end while;
end

二十一，数据库表的导入导出
（1）通过navicat导出导入
（2）通过命令导出导入
使用mysql提供的mysqldump工具，在mysql的bin目录下执行
a. 导出命令：
mysqldump -u username（用户名） -p dbanme（数据库名）>dbname.sql（存储路径）
例子：将bxm库导出来

mysqldump -u root -p bxm>d:\bxm.sql

导出数据表user

mysqldump -u root -p bxm user>user.sql

b. 导入命令：
需要在mysql数据库内部，执行导入命令
（1）将需要导入的sql文件拷贝到mysql的bin目录下，然后在bin目录下登录mysql进入mysql里
进入到mysq内命令：

mysql -u root -p

（2）选择需要导入的库：

use mtx;

（3）执行导入命令：

source mtx.sql;

二十二，窗口函数

（1）排名函数
rank()、dense_rank()、row_number()
（2）分区函数