初识MySql

mysql(Relational Database Management System)

mysql是一种处理文件操作的一款软件，是关系型的数据库，关系型可以理解为“表格的概念”，一个关系型数据库由一个或多个表格组成。

--表头(header) 每一列的名称;
　　　　--列 (row) 具有相同数据类型的数据的集合;
　　　　--行 (col) 每一行用来描述个人或物的信息;
　　　　--值 (values) 行的具体信息，每个值必须与该列的数据类型相同;
　　　　--键 (key) 表中用来识别某个特定的人或物的方法，键的值在当前列中具有唯一性；

服务端软件

　　　　　　socket服务端
　　　　　　本地文件操作
　　　　　　解析sql语句

客户端软件
　　　　　　socket客户端
　　　　　　发送指令
　　　　　　解析sql语句

　　PS：
　　　　　　DBMS数据库管理系统
　　　　　　SQL语句

　　　　其他类似软件：
　　　　　　--关系型数据库：sqllite,db2,oracle,access,sql server,mysql
　　　　　　--非关系型数据库：MongoDB,redis

Windows下的mysql安装，配置

安装

　　　　www.mysql.com 官网下载
　　　　1，安装windowns服务： mysqld --install
　　　　2，初始化data文件：mysqld -- initialize -insecure

　　　　初始化完后，会有一个默认的root用户，密码是空

　　　　　　连接mysql服务器: net start mysql
　　　　　　关闭mysql服务器：net stop mysql
　　　　　　卸载mysql服务器：sc delete mysql

　　使用 mysqladmin 方式:

　　　　修改密码： mysqladmin -u root -p password 新密码
　　　　set password for '用户名'@'IP地址' = Password('新密码')

使用mysql

mysql数据类型：
数字：unsigned加上是表示"无符号"
　　　　　

　　　　　　整形
　　　　　　　　int
　　　　　　　　　　有符号：
　　　　　　　　　　　　-2147483648 ～ 2147483647
　　　　　　　　　　无符号：
　　　　　　　　　　　　0 ～ 4294967295
　　　　　　　　tinyint

　　　　　　　　　　有符号：
　　　　　　　　　　　　-128 ~127
　　　　　　　　　　无符号：
　　　　　　　　　　　　0 ～ 255


　　　　　　bigint
　　　　　　　　　　有符号：
　　　　　　　　　　　　-9223372036854775808 ～ 9223372036854775807
　　　　　　　　　　无符号：
　　　　　　　　　　　　0 ～ 18446744073709551615

浮点型

　　　　　　　　float 精度丢失，到了一定长度精度丢失
　　　　　　　　double 精度丢失，比float大一倍左右才会丢失精度
　　　　　　　　decimal 精度最高(可指定长度跟小数点位数)
　　　　　　　　　　decimal(65,30) 最大值
　　　　　　　　　　对于精确数值计算时需要用此类型
　　　　　　　　　　decaimal能够存储精确值的原因在于其内部按照字符串存储。

字符串：

　　　　　　　　char 查找速度快
　　　　　　　　　　  用于表示固定长度的字符串，最多255字符
　　　　　　　　varchr 节省空间
　　　　　　　　　　  不固定长度的字符串，最多255字符


　　　　　　　　文本：text ,tinytext,mediumtext,longtext
　　　　　　　　　　　　上传大文件：文件存硬盘
　　　　　　　　　　　　路径存数据库
　　　　　　　　enum 枚举类型
　　　　　　　　　　　　五个列中，只能选择一个
　　　　　　　　set 集合类型
　　　　　　　　　　　　create table myset(col("a","b","c","d"))
　　　　　　　　　　　　inert into myset (col) values("a",("c")),("d","a");
　　　　　　　　　　　　可以多个组合

　　　　　　　　二进制(存图片，音乐，视频等)：tinyblob, blob,mediumblob

时间类型：

　　　　　　　　DATE
　　　　　　　　　　年月日
　　　　　　　　TIME
　　　　　　　　　　时分秒
　　　　　　　　DATETIME
　　　　　　　　　　年月日时分秒
　　　　　　　　TIMESTAMP
　　　　　　　　　　从1970-01-01 00:00:00开始

创建用户：

　　　　--登录：mysql -u 用户名 -p; (-p是密码)
　　　　mysql -D 所选的数据库名 -u 用户名 -p


　　　　--选择数据库连接：use 数据库名;
　　　　--必须是登录的root用户
　　　　--连接mysql数据库
　　　　create user "用户名"@"IP地址" identifed by "密码";

　　　　root1只能在192.168.0.1这个IP上登录
　　　　create user "root1"@"192.168.0.1" identifed by "123";

　　　　%是代表所有的ip都能登录
　　　　create user "root1"@"%" identifed by "123";

　　删除用户：

　　　　drop user "用户名"@"IP地址";

修改用户：

　　　　rename user "用户名"@"IP地址" to "新用户"@"IP地址";

　　授权：

　　　　给root1用户在所有的IP都能登录给root1用户 查看，插入， 更新的权限
　　　　grant select,insert,update on 数据库名 to "root1"@"%";

　　　　给root1用户在所有的IP都能登录，在db这个数据库的所有操作权限
　　　　grant all privileges on db to "root1"@"%";

数据库操作

查看：

　　　　show databases;

创建：

　　　　create database 数据库名 default charset utf8;

　　　　default charset utf8指定字符编码

删除：

　　　　drop database 数据库名;

表操作

查看：

　　　　show tables;

创建：

　　　　create table 表名(列 类型 auto_increment primary key,
　　　　　　　　　　　　　列 类型 )engine=innodb default charset utf8;


　　　　create table 表名(id int auto_increment primary key,
　　　　　　　　　　　　name char(10) not null,
　　　　　　　　　　　　age tinyint unsigned not null)engine=innodb default charset utf8;
　　　　　　解释：
　　　　　　　　auto_increment 自增，
　　　　　　　　primary key 约束(不能重复也不能为空)加速查找速度，
　　　　　　　　两者是在一起用的，给一列数据用，
　　　　　　　　engine=innodb 指定引擎 支持事物，原子性操作，不指定默认myisam
　　　　　　　　not null 指定该列的值不能为空，必须要填
　　　　　　　　null 可为空
　　　　　　　　unsigned 修饰int类型，表示int是无符号

清空表：

　　　　delete from 表名; 删除表里的内容，表还存在，
　　　　truncate table 表名; 删除表里的内容，表还存在

删除表：

　　　　drop table 表名; 删除整张表

创建后表的操作：

--添加列：

　　　　　alter table 表名 add 新列名 新列数据类型;

　　　　　alter table 表名 add 新列名 新列数据类型 alter 插入位置;

　　　　　
--修改列：

　　　　　　修改列名
　　　　　　　　alter table 表名 change 列名 新列名 新数据类型;

　　　　　　修改列的数据类型
　　　　　　　　alter table 表名 change 列名 列名 新数据类型;

--删除列：

　　　　　　alter table 表名 drop 列名;

--重命名表：

　　　　　　alter table 表名 rename 新表名;

--添加主键：

　　　　   alter table 表名 add primary key(列名);

--删除主键：

                alter table 表名 drop primary key;

　　　　　　　　　 alter table 表名 modify 列名 int,drop primary key;

表内文件操作

增：

　　　　插入部分数据
　　　　　　insert into 表名(列名1,列名2....) values (值1,值2....)

　　　　插入所有列的数据，表中有几列，就需要添加几个值
　　　　　　insert into 表名 values (值1,值2,值3....);

删：

　　　　delete from 表名 where 删除条件;

改：

　　　　update 表名 set 列名=新值;
　　　　update 表名 set 列名=新值 where 更新条件;

查：

　　　　select * from 表名; 查询表内所有信息;

　　　　select 列名1,列名2 from 表名; 两列的信息;

　　　　select * from 表名 where age>20； 从表中找出age>20的所有信息
　　　　 > = < >= <= != or and》等等

1,条件 in, not in , between and
    select * from 表名 where id>10 and name != "钢弹" and password = "123";

    select * from 表名 where id in (12,22,43);

    select * from 表名 where id not in (12,22,32);
    
    select * from 表名 where id in (select id from 表名);

    select * from 表名 where between 5 and 10; -- 第五列到10列

2,通配符 like
    
    select * from 表名 where name like "%hello%"; -- 中间有hello就行

    select * from 表名 where name like "he_" -- he后面的一个字符

    select * from 表名 where name like "he%" -- he后面的所有字符

3,分页 limit
    
    select * from 表名 limit 10; -- 前10行

    select * from 表名 limit 10,10; -- 第10行后面的10行
    
    select * from 表名 limit 10 offset 10; -- 第10行后面的10行，跟上面一样


4,排序 order by desc和asc

    select * from 表名 order by 列名 asc;  -- 从小到大

    select * from 表名 order by 列名 desc; -- 从大到小

    select * from 表名 order by 列名1 desc 列名2 asc; -- 列名1从大到小，如有重复的，在按照列名2的从小到大排序


4, 分组 group by 二次筛选用having，不能用where

    select num from 表名 group by num; -- 过滤重复的

    select num,nid  from 表名 group by num,nid;
    
    -- 先把num分组，在把num大于10的全部找出来
    select num from 表名 group by num having num>10;    

    注意：where需要在group by前面才可以 ，order by在最后    
    select num from 表名 where id=1 group by num;


    count分完组总共有几行，sum求和，max最大数，min最小数，都是函数    
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid


5,连表 ，left join，right join， inner join

    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid

6,组合 ，union 
    
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

其他查的操作

视图：

视图是一个虚拟表(非真实存在的),其本质用SQL语句获取动态的数据集，用户使用时，只需用名称，就能获取到结果集，并可以将它当做临时表来用。

SELECT
    *
FROM
    (
        SELECT
            username,
            PASSWORD
        FROM
            userinfo
        WHERE
            id > 5
    ) AS A
WHERE
    A.username > "j"

临时表搜索

1,创建视图：

 -- 格式：create view 视图名 as SQL语句

create view v2 as select * from userinfo;

View Code

2,删除视图：

-- 格式：drop view 视图名

drop view v2;

View Code

3,修改视图：

-- 格式：alter view 视图名 as SQL语句


alter view v2 as select * from userinfo;

View Code

4,使用视图：

使用视图时，当做临时表操作就行，由于视图时虚拟的，所以不能对它进行增加，修改，删除等操作，只能查询。

-- 格式：select * from 视图名

select * from v1;

View Code

触发器

对于某个表进行【增删改】操作的前后，如果想触发某个特定的行为时，可以使用触发器，触发器是用于用户对表的【增删改】操作的前后的行为。

创建触发器的基本语法：

-- 插入前

delimiter //

create trigger c1 before insert on userinfo for each row 

BEGIN
-- 插入完执行的SQL语句

end //

delimiter ;



-- 插入后

delimiter //

create trigger c1 after insert on userinfo for each row 

BEGIN
-- 插入完执行的SQL语句

end //

delimiter ;



-- 删除前

delimiter //

create trigger c1 before delete on userinfo for each row 

BEGIN

-- 删除完完执行的SQL语句

end //

delimiter ;


-- 删除后

delimiter //

create trigger c1 after delete on userinfo for each row 

BEGIN

-- 删除完完执行的SQL语句

end //

delimiter ;


-- 更新前

delimiter //

create trigger c1 before update on userinfo for each row 

BEGIN

-- 更新完执行的SQL语句

end //

delimiter ;


-- 更新后

delimiter //

create trigger c1 after update on userinfo for each row 

BEGIN

-- 更新完执行的SQL语句

end //

delimiter ;

View Code

delimiter //

create trigger t1 before insert on userinfo for each row 
BEGIN

INSERT INTO student(gender,class_id,sname) values("女",4,"铁锤");

end //

delimiter;


-- 向userinfo 插入之前 触发 向student插入数据

插入前触发器

delimiter //
CREATE TRIGGER t1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('666'),
            ('666') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('555'),
            ('555') ;
    END IF;
END//
delimiter ;

插入后触发器

注意：NEW表示即将插入的数据行，OLD表示即将删除的数据行

1，删除触发器：

-- 格式： drop trigger 触发器名;

drop trigger t1;

View Code

2,使用触发器：

触发器无法由用户直接调用，而是由对表的【增删改】操作的时候被动的触发的。

-- 运行时会自动引发触发器

insert into userinfo(username,password) values("铁锤",123432);

View Code

存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行，还可以在内部添加函数，而视图时不能再内部添加函数的。主要用于代替程序员写SQL语句、是存在mysql服务端的

存储过程的三种调用方式：

　　1，MYSQL----》存储过程

　　　　程序：调用存储过程

 

　　2，MYSQL----》存储过程

　　　　程序：SQL语句

 

　　3，MYSQL-----》存储过程

　　　　程序：类和对象（SQL语句）

1，创建存储过程：

-- 格式：

delmiter //   -- 把SQL语句的结束符修改成//,为了避免与SQL语句冲突

create procedure 存储过程名字()

BEGIN  -- 语句开始

    SQL语句;

END//  -- 语句结束

delimiter ;




-- 创建存储过程

delimiter //

create procedure p1()

BEGIN

    select * from t1;

END//

delimiter ;




-- 执行存储过程

-- 格式：call 存储过程名字()

call p1()

无参数的存储过程

对于存储过程，可以接收参数，其参数有三类：

in          仅用于传入参数用
out        仅用于返回值用,存储过程中是没有返回值的，只是把out伪装成返回值
inout     既可以传入又可以当作返回值

-- in

delimiter //
create PROCEDURE p5(
        in n1 int,
        in n2 int
)
BEGIN
    SELECT * from student WHERE sid > n1;
end //
delimiter;

-- 调用

call p5(12,2)

-- in 返回的是结果集

有参存储过程--in

delimiter //

create procedure p3(
    in n1 int,
    inout n2 int
)
BEGIN
    set n2 = 123123;
    select * from student where sid > n1;
END //
    delimiter ;
            

-- 调用            
set @_p3_0 = 12
ser @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1

有参存储过程--out和input

delimiter //

create procedure p2(
       in n1 int,
       inout n3 int,
        out n2 int,
 )

begin
       declare temp1 int ;
       declare temp2 int default 0;

       select * from v1;
       set n2 = n1 + 100;
       set n3 = n3 + n1 + 100;
end //
delimiter ;

结果集和out返回值

delimiter //
create procedure p3()
begin 
        declare ssid int; -- 自定义变量1  
        declare ssname varchar(50); -- 自定义变量2  
        DECLARE done INT DEFAULT FALSE;


        DECLARE my_cursor CURSOR FOR select sid,sname from student;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        
        open my_cursor;
                xxoo: LOOP
                        fetch my_cursor into ssid,ssname;
                        if done then 
                                leave xxoo;
                        END IF;
                        insert into teacher(tname) values(ssname);
                end loop xxoo;
        close my_cursor;
end  //
delimter ;

游标

delimiter \
CREATE PROCEDURE p4 (
       in nid int
)
BEGIN
          PREPARE prod FROM 'select * from student where sid > ?';
          EXECUTE prod USING @nid;

END\
delimiter ;


-- 调用 

call p4(12)

动态执行SQL

-- 事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。



delimiter \
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN 
    DECLARE exit handler for sqlexception -- 声明如果出现异常执行下面的代码块
    BEGIN 
        -- ERROR 
        set p_return_code = 1; -- 返回1 代表出异常了
        rollback; -- 回滚 ，回滚到原始状态
     END; 
             
    START TRANSACTION;  -- 没出现异常，开始事务
          DELETE from userinfo;
          insert into userinfo(username)values('seven');
     COMMIT;  -- 提交事务
             
    -- SUCCESS 
     set p_return_code = 2; -- 返回2 ，代表执行成功 
             
END\
delimiter ;

事务

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='db1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

pymysql执行存储过程

2,删除存储过程：

-- 格式：drop procedure 存储过程名;


drop procedure p5;

View Code

3,执行存储过程：

-- 无参数

call p5()


-- 有参数 in

call p5(12,12)


-- 有参数 in out inout

set @t1=12;
set @t2=12;

call p5(12,12,@t1,@t2)

View Code

函数

MySql中提供了许多的内置函数

CHAR_LENGTH(str)
    返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

CONCAT(str1,str2,...)
    字符串拼接
    如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
    字符串拼接（自定义连接符）
    CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONV(N,from_base,to_base)
    进制转换
    例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
    例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
    特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
    返回字符串 str 中子字符串的第一个出现位置。

LEFT(str,len)
    返回字符串str 从开始的len位置的子序列字符。

LOWER(str)
    变小写

UPPER(str)
    变大写

LTRIM(str)
    返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
    返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
    获取字符串子序列

LOCATE(substr,str,pos)
    获取子序列索引位置

REPEAT(str,count)
    返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
    若 count <= 0,则返回一个空字符串。
    若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
    返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
    返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
    从字符串str 开始，返回从后边开始len个字符组成的子序列

SPACE(N)
    返回一个由N空格组成的字符串。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

    mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
    返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

    mysql> SELECT TRIM('  bar   ');
                    -> 'bar'

    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                    -> 'barxxx'

    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                    -> 'bar'

    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                    -> 'barx'

部分内置函数

1，自定义函数

-- 创建自定义函数

delimiter \
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \
delimiter ;

View Code

2，执行函数

--  获取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;


-- 在查询中使用
select f1(11,nid) ,name from userinfo;

View Code

3,删除函数

-- 格式：drop function 函数名;

drop function f1;

View Code

索引

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

MySql常见的索引有：

普通索引
唯一索引
主键索引
组合索引

1，普通索引：

普通索引只有一个功能：加速查询

create table user(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)

创建表+普通索引

-- 格式：create index 索引名 on 表名(列名);

-- 索引名不能是关键字

create index in on user(name); -- 错误，索引名跟SQL关键字in冲突


-- 正确

create index in_name on user(name);

创建普通索引

-- 格式：drop index 索引名 on 表名;


drop index in_name on user;

删除普通索引

-- 格式：show index from 表名;

show index from user;

查看索引

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index in on user(extra(32));

View Code

2,唯一索引：

唯一索引有两个功能：加速查找，唯一约束

create table user(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique in (name)
)

创建表+唯一索引

-- 格式：create unique index 索引名 on 表名(列名);

create unique index in_name on user(name);

创建唯一索引

-- 格式：drop index 索引名 on 表名;

drop index in_name on user;

删除唯一索引