导入csv文件

使用如下命令：
mysql> load data infile "your csv file path" into table [tablename] fields terminated by ','
注意上面导入文件时，都需要提前建立好与文件内各个段对应好的数据表。并且文件的路径需要使用引号括起来，双引号和单引号都可以。

方法一：

在使用mysql时，有时需要查询出某个字段不重复的记录，虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是 distinct只能返回它的目标字段，而无法返回其它字段

下面先来看看例子：

table
id name
1 a
2 b
3 c
4 c
5 b

库结构大概这样，这只是一个简单的例子，实际情况会复杂得多。

比如我想用一条语句查询得到name不重复的所有数据，那就必须使用distinct去掉多余的重复记录。

select distinct name from table
得到的结果是:

name
a
b
c

好像达到效果了，可是，我想要得到的是id值呢？改一下查询语句吧:

select distinct name, id from table

结果会是:

id name
1 a
2 b
3 c
4 c
5 b

distinct怎么没起作用？作用是起了的，不过他同时作用了两个字段，也就是必须得id与name都相同的才会被排除。。。。。。。

我们再改改查询语句:

select id, distinct name from table

很遗憾，除了错误信息你什么也得不到，distinct必须放在开头。难到不能把distinct放到where条件里？能，照样报错。。。。。。。

最终好用的语句如下：

select *, count(distinct name) from table group by name

结果:

id name count(distinct name)
1 a 1
2 b 1
3 c 1

最后一项是多余的，不用管就行了，目的达到。。。。。

哦，对，再顺便说一句，group by 必须放在 order by 和 limit之前，不然会报错。。。。。。。。！OK了

总结语句：select *, count(distinct name) from (select * from table……等嵌套语句) group by name
参考出处：https://blog.csdn.net/djun100/article/details/84473838?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param

流程控制一般三种：顺序，分支，循环

分支

1.if函数

语法：if(exp1,exp2,exp3)
执行顺序：
如果表达式1即exp1成立，则if函数返回表达式2即exp2的值，否则返回表达式3即exp3的值

2.case结构

语法：case [变量|表达式|字段]#区间判断的时候省略
when 要判断的值或要判断的条件1 then 返回值1或语句1；
when 要判断的值 then 返回值2或语句2；
when 要判断的值 then 返回值3或语句3；
…
else 要返回的值n
END CASE;
与其他语言相比，没有break，但是;默认和break功能一样

#case案例
create procedure test_case(IN score INT)
begin
    case
    when score>=90 and score<=100 then select 'a';
    when score>=80 then select 'b';
    when score>=60 then select 'c';
    else select 'd';
    end case;
end $

3.if结构

语法：
if 条件1 then 语句；
elseif 条件2 then 语句2；
[else 语句n；]
end if
只能在begin end里用

#if结构案例
create function test_if(score INT) returns char
begin
    if score>=90 and score<=100 then return 'a';
    elseif score>=80 then return 'b';
    elseif score>=60 then return 'c';
    else return 'd';
    end if;
end $

4.循环

分类：while,loop,repeat
控制：iterate类似continue：结束本次循环，进入下一次，
leave类似break：结束当前所在循环

1.while语法

[标记:] while 条件 do 循环体;
end while [标记];

2.loop语法

[标记:] loop
循环体;
end loop [标记];
用来模拟简单的死循环

3.repeat语法

[标记:] repeat
循环体;
until 结束循环的条件
end repeat [标记];

#while案例：根据传入次数n，向table1插入n条记录
##没有返回值，所以用procedure而不用function
create procedure pro_while1(IN insertCount INT)
begin
    declare i int default 1;
        while i<=insertCount do
        insert into table1(name)values(concat("a1",i));
        set i=i+1;
    end while;
end $

call pro_while1(3)$

#leave案例：根据传入次数n，向table1插入n条记录,如果次数>=20，则停止
##没有返回值，所以用procedure而不用function
truncate table table1$
drop procedure pro_while1$
create procedure pro_while1(IN insertCount INT)
begin
    declare i int default 1;
        label1:while i<=insertCount do
        insert into table1(name)values(concat("a1",i));
        IF i>=20 then leave label1;
        end IF;
        set i=i+1;
    end while label1;
end $

call pro_while1(3)$

#iterate案例:偶数次插入
create procedure pro_it(IN count INT)
begin
    declare i int default 0;
    a:while i<count do
        set i=i+1;
        if mod(i,2)!=0 then
                iterate a;
        end if;
        insert into table1(name)values(concat("a1",i));
    end while a;    
end$

mysql的函数和存储过程

共同点：

1. 都是一组sql语句集合
2. 提高代码复用性
3. 简化操作
4. 减少编译次数，并减少了和数据库服务器的连接次数

区别：

存储过程可以有0个返回或者多个返回，适合批量插入和更新
函数：有且仅有一个返回，适合做完数据处理后返回一个结果

函数语法说明

create function funcname(params) returns return_type
begin
    func_detail;
end

ps：
1. params包含两个部分：参数名 参数类型
2. 函数体必须包含return 语句
3. delimiter语句设置结束标记

函数调用说明

select funcname(params)

案例

返回table1的记录数

create function myf1() returns int
begin
    declare c int default 0;
    select count(*) into c
    from table1;
    return c;
end $

select myf1() $

根据name返回age

create function myf2(uname varchar(20)) returns int
begin
    set @age=0;
    select age into @age
    from table1 where name=uname;
    return @age;
end $

select myf2("a2") $

查看函数

show create function myf2;

删除函数

drop function myf2;

变量，存储过程

变量

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

初始化&赋值&查看

查看所有系统全局变量

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
。。。。。
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
注意事项：要求查询列数一致，类型，顺序一致

查询Mysql数据库中以逗号分隔id存储的字段信息,id对应的详细信息在另一个表中

有两张表，

一张为资产表b表

一张为用户表 u表

u表

id	名称	资产Id
1	小红	1,2,3
2	小明	2,5

b表

id	名称
1	房子
2	车子
3	股票
4	基金
5	债券

正常查询u表

select * from u

现在需求是：

id	名称	资产
1	小红	房子，车子，股票
2	小明	车子，债券

修改sql为

select u.id,u.名称，group_concat(b.名称 orderby b.id separator "----") from u left join b on find_in_set(b.id,u.资产Id) group by u.id;     

注意：有时候mysql会报错：

mysql 错误Row xx was cut by GROUP_CONCAT()

解决办法:

1.在MySQL配置文件中加上

group_concat_max_len = 102400 #你要的最大长度

2.可以简单一点，执行语句：

mysql> SET GLOBAL group_concat_max_len=102400;
Query OK, 0 rows affected (0.01 sec)

原理:

GROUP_CONCAT有个最大长度的限制，超过最大长度就会被截断掉，你可以通过下面的语句获得：

mysql> SELECT @@global.group_concat_max_len;

@@global.group_concat_max_len
1024

1024这就是一般MySQL系统默认的最大长度，如果你的bid串起来大于这个就会出问题,所以解决方法就是改掉这个默认值了