MySQL学习

作者: 小Q逛逛 | 来源:发表于2017-01-12 17:55 被阅读78次

    .数据库

    数据库的发展: 文件系统(使用磁盘文件来存储数据)=>第一代数据库(出现了网状模型,层次模型的数据库)=>第二代数据库(关系型数据库和结构化查询语言)=>新一代数据库("关系-对象"型数据库);

    层次模型是一种导航结构,
    优点:分类管理,如果查询同一类的数据是很方便的。
    缺点:如果查询很多不是同一类的数据,效率就很低了
    层次结构可以造成数据无效,比如张经理管理了一个员工叫张三,李经理也管理了一个叫张三,我们无法区分张三是一个人还是两个人。

    网状模型解决了层次模型数据无效的问题,但是没有解决导航问题,深层率查询,效率很低.

    关系模型中,每个表都是独立的,通过公共字段来建立关系。优点:表是独立的,需要什么数据就到那个表中查询。
    缺点:多表查询的时候效率低下。

    关系:两个表的公共字段叫关系

    • SQL语句

    Structured Query Language 结构化查询语言,是用来操作关系型数据库的.常用的关系型数据有:

    Access,MySQL,Sql Server,Oracls.

    标准的SQL是所有关系型数据库都支持的操作语句,标准的SQL也叫作SQL-92.但是每个数据库在标准的基础上又扩展了自己的东西.所有,一个数据库的拓展语句在在其他数据库不能运行.

    • 连接数据库

    需要的参数: Host (-h),username (-u), password (-p),port(默认3306) (-P)

    mysql -u root -h localhost -p
    
    • 退出数据库:exit,quit,\q

    • 数据库操作:数据库的本质是一个文件.操作数据库的软件叫做数据库管理系统.

    /**
    *   创建数据库
    *   如果创建的数据库已经存在,会报错.所以一般要进行判   断.
    *  同时,如果数据库名是关键字或者纯数字,需要加上反引    号,最好的办法是任何时候都加反引号
    */
    Create database db_name [charset=字符编码]
    Create database if not exists db_name [charset=字符编码]
    
    
    /** 
    */
    2.  查询数据库
    show database;
    
    3.显示数据库的创建语句
    show create database create db_name;
    
    4. 更改数据库
    alter database db_name [option];
    
    //eg:alter database haha charset=gbk;
    
    5. 删除数据库:如果数据库不存在会报错
    drop database if exists db_name;
    
    6. 选择数据库
        use db_name;
    
    • 数据库表的操作

    几个概念:

    行row:也叫记录,一行就是一条记录

    列(column) : 一列就是一个字段,字段也叫属性,一个表中包含多个字段

    1.创建表:(primary key)主键不能重复,不能为空,一个表只能有一个主键,主键可以由多个字段组成.
    
    create table table_name(
        field1 data_type [null | not null][default][auto_increment][primary key],
        field2 data_type,
        ...,
    );
    
    2. 查看所有表
    show tables;
    
    3.显示创建表的SQL语句
    show create table table_name [\G];
    
    4.显示表结构
    describe [desc] table_name;
    
    5.删除表
    drop tabel table_name1,table_name2,...;
    
    

    数据类型:

    int,decimal(总位数,小数位数) 存小数 decimal(10,3), char(1):定长, varchar(10):可变长度, text:大段文字

    • 数据操作
    1.插入数据:插入字段可以和数据库中字段顺序不一致,但是值和插入的字段顺序必须一致. 如果插入字段省略掉,插入的值和数据库表的字段的顺序和个数都要一致.
    
    insert into table_name (field1,field2) values (value1,value2);
    
    //自动增长的插入
    insert into student values (null,"name","female","shenzhen",22);
    
    //默认值的插入
    insert into student values (null,"name","female",default,33);
    
    
    2.数据的修改
    update table_name set field1=value1,field2=value2 [where 条件];
    
    //eg:
    update student set gender="male" where name="xiaoming";
    
    update student set gender = "female";
    
    3.删除数据
    delete from table_name [whre 条件];
    
    delete from student where name = "haha";
    
    delete from student;    //所有的数据都删除了
    
    4 .查询数据
    select 列名 from 表 [where 条件] [order by 排序字段 asc|desc] [limit [起始位置默认0],或者的记录数量];
    升序:asc
    降序:desc
    默认升序
    
    select name,gender from student;
    select * from;  //获取所有
    select * from student order by score desc;
    select * from student limit 3;
    select * from student limit 2,10;
    select * from student order by score desc limit 3;
    
    //取出第一位
    select * from stu order by score desc limit 0,1
    
    • 运算符

    比较运算符: > , >=, <, <=, = , <>(不等于)
    逻辑运算符: and, or, not

    聚合运算:

    Sum(), Avg(), Min(), Count(), Max().

    select max(score) from student;
    select min(score) from student;
    select sum(score) from student;
    select avg(score) from student;
    select count(*) from student;
    select count(*) from student where gender="male";
    
    

    4.PHP与MySQL

    • php开启MySQL拓展,PHP本身是一个框架,它的功能是由PHP拓展而来的,要通过PHP连接数据库,必须开启PHP连接MySQL的功能,就是PHP的MySQL拓展. 在php.ini中,把extension=php_mysql.dll 开启,重启服务器.
    1.php连接数据库
    $connect = mysql_connect("localhost",'root','password') or die("数据库连接失败");
    /**
        通过@符号屏蔽信息
        $connect = @mysql_connect("localhost",'root','password') or die("数据库连接失败");
    */
    
    2. 终止执行:exit(),和die();
    die()停止执行,把生成的代码发送到客户端.
    
    3. 选择数据库
    方法1: 执行 use db_name
    mysql_query("use database_name") or die("数据选择失败");
    
    方法二:
    mysql_select_db("database_name") or die ("数据库选择失败");
    
    4. 设置字符编码
    mysql_query("set names utf8");
    
    5.查询数据库 : 返回一个资源类型的数据
    
    $results = mysql_query("select * from products");       //resource type
    
    mysql_fetch_row: 取出结果集中数据
    开始匹配,指针指向第一个记录.取出资源中的当前记录,匹配成索引数组,指针指向下一条记录.
    
    5.1.
    while($rows = mysql_fetch_row($results)){
        echo $rows[0];
        echo "<br>";
        echo $rows[1];
        echo "<br>";
        echo $rows[2];
        echo "<br>";
        echo $rows[3];
        echo "<br>";
    }
    
    这种方法读取数据缺点:数据库字段发生变化,会影响数组的索引编码.
    
    
    5.2: mysql_fetch_assoc : 形成关联数组
    数组的键和数据表的字段名相关联
    while($rows = mysql_fetch_assoc($results)){
        echo $rows["name"];
        echo "<br>";
        echo $rows["gender"];
        echo "<br>";
        echo $rows["age"];
        echo "<br>";
        echo $rows["id"];
        echo "<br>";    
    }
    
    5.3: mysql_fetch_object: 匹配成对象,通过->访问
    
    while($obj=mysql_fetch_object($results)){
        echo $rows->name;
        echo "<br>";
        echo $rows->gender;
        echo "<br>";
        echo $rows->age;
        echo "<br>";
        echo $rows->id;
        echo "<br>";
    }
    
    
    6. 释放资源 mysql_free_result($results);
    7. 关闭连接 mysql_close($connect);
    
    页面执行完毕后,所有变量全部销毁,可以不用手动释放资源.
    
    

    数据的导入与导出,用phpMyAdmin工具


    1月 5日

    MySQL 学习

    • 简介
    1. 数据库系统: DataBase System = 数据库管理系统(DBMS,DataBase Management System) + 数据库 (DataBase) + 管理员(Manager)
      DBS = DBMS + DB;
      对大量信息进行管理的高效解决方案,按照数据结构来组织,存储和管理数据的库.

    关系型数据库系统:建立在关系模型上的数据库系统

    关系模型: 日常生活的实体和实体的属性保存到数据库时,处理实体结构的方式:
    数据结构可以规定,同类数据,结构一致.数据之间的关系可以设置实体之间的联系.

    Nosql: Not only sql,非关系型数据库
    mongoD,MemBase

    对象型数据库.

    关系型数据库的几个重要概念:

    数据库database:数据的仓库

    表table:数据是保存在表内,保存在一个表内的数据,应该具有相同的数据格式.

    行和列:行用于记录数据,列用于规定数据的格式

    MySQL是基于C/S架构的.

    • MySQL常用的操作
    (1)数据库操作
    
    //创建数据库
    create database `db_name` [数据库选项];
    
    //查询当前存在的数据库
    show databases;
    
    //查询数据库的创建语句
    show create database db_name;
    
    //删除数据库
    drop database db_name;
    
    //修改数据库信息
    alter database db_name [修改指令];
    alter database db_class character set utf8;
    
    //修改数据库的名字
    1.直接修改文件夹的目录名
    2.将数据全部导出,新建一个数据库,再导入数据,删除旧数据库
    3.新建数据库,把就数据库的表移到新数据库,删除就数据库
    
    (2)表操作
    
    可以通过.(点)语法,指明数据表所属的数据库
    create table `db_name`.`table_name`(
        field1 type,
        field2 type
    );
    
    use db_name 只是设定了默认数据库,不影响操作其它数据库.选择了默认数据库,只会影响默认行为.可以操作任意的数据库
    
    //创建表
    create table info_student(
        `name` varchar(20),
        `stu_no` varchar(20)
    );
    
    //查看表
    show tables [like 'pattern'];like pattern部分表示获得规定规则的表名. % 为通配符,表示任意字符的任意组合.同样适用于数据库查询:
    show databses like 'pattern';
    
    //查看表的结构
    describe(desc) table_name;
    
    //删除表
    drop table [if exists] table_name;
    drop database [if exists] database_name;
    
    //修改表名
    rename table old_table_name to new_table_name;
    
    //同时修改多个
    rename table tb1 to new_tb1,tb2 to new_tb2;
    
    //支持跨数据库重命名
    //可以利用这个操作为数据库重命名: 创建一个新的数据库,旧数据内的表,都rename到新的数据库.删除旧的数据库.
    
    rename table exam_user to `db_name`.table_name;
    
    //修改列的定义
    alter table tb_name [add | drop | change | modify]
    
    //增加一个新列
    alter table table_name add filed_name type;
    
    //删除一个列
    alter table tb_name drop field;
    
    //修改一个列的定义
    alter table tb_name modify field varchar(90);
    
    //重命名一个列
    alter table table_name change old_field new_field type;
    
    //修改表选项
    alter table table_name character set utf8;
    
    (3)数据操作
    //创建数据(插入数据)
    insert into table_name (fields) values (value1,...);
    
    insert into student (name,id) values (`name`,`09332`);
    
    //获取数据(查询数据)
    select fields(*) from table_name condition;
    
    select * from students;
    select name,id from students where 1;
    select * from students where score>=80;
    
    //删除数据
    delete from table_name condition;
    
    delete from students where score<=20;
    
    //修改数据
    update table_name set field1 = value1,... condition;
    
    update students set score = 90 where id = 0922;
    
    
    • SQL

    Structured Query Language:结构化查询语言;

    针对操作对象不同,分成不同语言:

    1.数据操作(管理)语言.DML;
    查询,获得数据.DQL;
    管理,增加,删除,修改数据.DML

    2.数据定义语言(对保存的数据的格式进行定义) DDL;

    3.数据库控制语言 (针对数据库软件服务进行操作) DCL;

    • 字符集

    字符的集合;构成分两部分:1.字符的集合,展示用,2.字符的编码,保存和处理.

    MySQL支持常见的字符集,查看: show character set;

    字符集决定的是字段的数据以何种形式保存.
    如果通过客户端操作服务器,那么客户端和服务器端之间进行数据通信,要保证编码一致.可以将互相发送的数据,转换成目标可以接收的编码数据.

    通过MySQL配置:

    character_ser_client 客户端发送数据编码

    character_ser_results 客户端接收数据的编码

    通过指令 show variables like 'character_set_%';查看当前的字符编码集

    设置变量: set 变量名=值;

    set character_set_client = utf8;告知服务器,客户端发送的数据是utf8编码;

    如果需要从服务器返回数据,还需要设置服务器发送给客户端的编码:set character_set_results = utf8;服务器在发送数据时,才能转成客户端认识的编码;
    对于普通的简单项目统一的操作可以使用 set names utf8;完成设置

    连接层编码: set character_set_connection = utf8;

    set names 操作可以同时设置客户端接收的数据格式,客户端发送的数据格式,连接层编码三个.

    数据编码转换过程: client->connection->[服务器内部编码]->results

    • MySQL数据类型

    1.整型

    类型 字节 最小值(有符号/无符号) 最大值(有符号/无符号)
    tinyint 1 -128 / 0 127/255
    smallint 2 -32768 / 0 32767/65535
    mediumint 3 -838808 / 0 83888607/1777215
    int/integer 4 -2147483648 / 0 2147483647/4294967295
    bigint 8 -922337203685477580/ 0 922337203685477580/8446744073709551615

    在定义字段时,可以使用unsigned标示无符号,默认为有符号.

    create table tbl_int(
        a tinyint unsigned,
        b tinyint
    );
    
    

    定义显示宽度:通过规定数据的显示宽度,达到统一显示的目的. 类型(M) ,M表示显示的最小宽度是多少. 如果需要用前导零值填充,使用 zerofill;不会影响数的范围,宽度大的不影响,不会截取;

    alter table tbl_int add c tinyint(2) zerofill;
    insert into tbl_int values (0,123,2);
    
    

    2.浮点数

    float 单精度,默认精度位数为6位;

    double 双精度,默认16位

    控制数值的范围 Type(M,D),D表示所有的数值的位数(不包括小数点和符号);
    D表示小数位数;

    支持科学计数法: 1.322*10^3;

    3.定点数

    decimal(M,D);M总位数,D小数位数,M默认10,D默认0

    小数也支持zerofill和unsigned

    
    create table number1(
        send_money decimal(10,2) zerofill;
    );
    insert into number1 values (123.43);
    insert into number1 values (122332.564);
    
    
    1. 日期和时间

    DateTime 年月日时分秒

    Timestamp 时间戳,存储时整型,表示时,日期时间

    Date 年月日

    支持0值:表示当前没有规定 2015-04-0 表示4月整个月.

    insert into dt_1 values (0,0);
    

    Time 类型: 1,表示一天中的时间 2,时间间隔,在表示时间间隔时,可以用天表示 格式: DHH:MM:SS

    create table tb_1(
        ago time
    );
    
    insert into tb_1 values ('4 21:22:11');
    insert into tb_1 values ('23:22:11');
    insert into tb_1 values ('232211');
    
    

    Year:此时表示日期的范围,不是1000-9999
    而是 1901-2155 一个字节(256间隔)

    在实际项目中,通常保存的都是Unix时间戳,直接用整型保存在数据库,不是MySQL提供的timestamp类型;

    • 字符串类型

    char(M): 固定长度

    varchar(M): 可变长度

    M在varchar中表示允许的最大长度,char内表示严格限定的长度

    char(5) varchar(5)
    '' 5个字符 一个字节 varchar需要一个字节保存字符串总长度
    'abc' 5 4
    'abcdf' 5 6
    'abcdefg'

    M表示的是字节,而不是字节数,但是总的长度的使用时按照字节计算的

    create table tb_2(
        a varchar(65535)        //会报错,最大长度是21845, 三个字节21845 * 3 = 65535
    );
    
    create table tb_3(
        a varchar(65535)
    ) character set gbk;  //报错,最大长度是32767,2个字节 2 * 32767 = 65535
    
    

    字段的最大长度除了类型本身之外,记录的总长度也是有限制的.

    真实的varchar长度:

    记录存在总长度65535限制

    特点:当类型数据超过255个字符时,采用2个字节表示长度 65535-2 = 65533

    整条记录,需要一个额外的字节,用于保存当前字段的null值.除非所有的字段都不是null,这个字节才可以忽略.一个记录,不论有多少个字段存在null,都是使用统一的一个字节表示.而不是每个字段一个字节

    Text类型: 2 ^ 16,文本类型,tinytext,longtext;

    枚举 enum

    值是否能为空: null || notnull,如果为not null,在插入时,缺少值,会插入失败;

    默认值属性: default value来声明. 在没有为该字段设置值时启用,而且默认值的设置,需要使用固定值;

    常见的是一个字段不能为空,而且存在默认值;

    create table tb_3(
        a int not null default 10,
        b int not null default 2
    );
    
    insert into tb_3 (a) values (1);
    insert into tb_3 (b) values (22);
    
    

    主键pk,primary key:

    可以唯一标示,某条记录的字段或者是字段的集合,就是主键.主键可以是真实实体的属性.常用的是使用一个与实体信息不相关的属性作为唯一标识.主键与业务逻辑不发生关系,只是用来标识记录.

    1.字段上设置:主键字段在插入时不能为空,或者冲突
    
    create table reacher(
        t_id int primary key,
        t_name varchar(5);
        class_name varchar (6);
        days tinyint unsigned
    );
    
    2.在定义完字段后,可以定义多列主键(组合主键):一个主键内包含多个字段,而不是多个字段的主键.只需要一个唯一标识即可,MySQL规定只能存在一个主键;
    常见的主键设计,每个表都应该存在一个可以唯一标识的主键字段,最好与实体没有关系,不是实体属性字段.
    
    create table teacher(
        t_name varchar(5),
        class_name varchar(6),
        days tinyint unsigned,
        primary key(t_name,class_name);
    );
    
    
    

    字段增长:auto_increment,自动增长的默认值是1,可以设置
    如果插入的值小于已经存在的主键的值,是可以的,如果是主键,不能重复;

    实体之间的关系:

    1vs1:
    两个实体表内,存在相同的主键字段.如果记录的主键值等于另一个关系表内的主键值,则两条记录一一对应;

    1 vs n
    :一个实体对应多个其它实体;例如,一个班级对应多个学生

    在多的那端,增加一个字段,用于指向改实体所属的另外实体的标识

    M:N 多对多

    典型的,利用一个中间表,标识实体之间的对应关系.中间表的每个记录,标识一个关系

    一个M:N,
    1:M,1:N来实现

    讲师主键 名字 班级
    1 joe
    2 jack
    班级主键 班级名 讲师主键
    29 0234
    30 0907
    讲师主键 班级主键
    1 29
    3 29
    • 外键 foreign key

    :如果一个实体(student)的某个字段(class_id)指向(引用)另外一个实体(class)的主键(class_id),就称student实体的class_id是外键.被指向的实体,称之为主实体(主表,父实体),对应的另外一个实体称之为从实体(从表,子实体,子表)

    外键的作用: 保证数据的完整性,用于约束处于关系的内的实体.增加子表记录的时候,是否有与之对应的父表记录.在删除或者更新的主表记录时,从表应该如何处理相关的记录.

    //定义一个外键
    在从表上在,增加一个外键字段,指向主表的主键.使用关键字foreign key;
    foreign key (外键字段) references 主表名 (关联字段)[主表记录删除时的动作][主表记录更新时的动作];
    
    drop table if eixsts class;
    create table class (
        class_id int primary key auto_increment,
        class_name varchar(10) not null default `php` comment `班级名称`
    )character set utf8;
    
    drop table if exists student;
    create table student(
        stu_id int primary key auto_increment,
        stu_name varchar(10) not null default ``,
        class_id int,
        foreign key (class_id) references class (class_id)
    ) character set utf8;
    
    

    设置级联操作:
    在主表数据发生改变时,与之关联的从表数据应该如何处理:
    主表更新:on update,主表删除 on delete

    允许的联级操作,cascade:关联操作,如果主表被更新或者删除,那么从表也会执行相应的操作.set null:设置为null,表示从表不指向任何主表记录,restrict:拒绝主表的相关操作

    修改外键:先删除,再新建.通过修改表完成
    alter table_name drop foreign key field;
    删除外键需要通过指定外键名称达到目的,可以在创建外键时,指定名称或者使用MySQL默认生成的名称;

    alter table student drop foreign key class_id;
    
    //增加外键
    alter table student add foreign key (class_id) references class (class_id) on delete set null; //删除时,将从表外键,设置为null;
    
    alter table student add foreign key (class_id) references class (class_id) on delete cascade; //删除时,将从表外键删除; 
    
    alter table student add foreign key (class_id) references class (class_id) on delete cascade on update restrict; //删除时,将从表外键上删除,更新时拒绝更新;
    
    
    • 数据库存储引擎

    默认的服务器表类型,通过mu.ini可以配置:default-storeage-engine=INNODB

    在创建表时,或者编辑表时,可以指定表的存储引擎

    alter table class engine myisam;
    
    create table room(
        room_id int primary key auto_increment,
        room_no char(3)
    )engine myisam character set utf8;
    
    

    innodb和myisam区别

    保存的文件的方式不同:
    myisam,一个表,三个文件
    Tbl_name.frm 结构 (frame)
    Tbl_name.myd 数据 (data)
    Tbl_name.myi 索引 (index)

    innodb:
    一个表一个文件:
    Tbl_name.frm 结构

    所有的innodb表,都使用相同的innodb存储空间在保存数据和索引;

    选择存储引擎的依据: 1 功能, 2 性能

    特点 Myisam InnoDB BDB Memory Archive
    事务安全 支持 支持
    全文索引 支持 5.5版本支持
    锁机制 表锁 行锁 页锁 表锁 行锁
    存储限制 没有 64TB 没有 没有
    B树索引 支持 支持 支持 支持
    哈希索引 支持 支持
    集群索引 支持
    数据缓存 支持 支持
    索引缓存 支持 支持 支持
    数据可压缩 支持 支持
    空间使用 N/A 非常低
    内存使用 中等
    支持外键 支持

    innoDB&Myisam:

    数据和索引的保存的文件不同: Myisam是分开保存的,innoDB是保存到表空间

    Myisam支持索引压缩,而innoDB索引和数据是绑定保存不压缩,体积大;

    innoDB很多时候是行级锁,而myisam是表级锁,innodb的并发高;

    innodb不支持fulltext类型的索引(新的版本的innodb支持);

    innodb支持事务,外键,数据的完整性约束要强,二myisam不支持.

    innodb中不保存表的具体行数,所以在执行 select count(*) from table_name 时,innodb要扫描一遍整个表来计算有多少行,但myisam只需要简单的读取保存好的行数即可.注意的是,当count(*)语句包含where时,两种表的操作是一样的;
    对于auto_increment类型的字段,innodb中必须包含只有该字段的索引,但是在myisam中,可以和其它字段一起建立联合.

    • 范式 (Normal Form)

    用于规范表的设计的一套原则体系.

    规范表的结构:

    体系:1NF,2NF,3NF,4NF...

    1NF:

    要求: 表,所有的列(属性),不能再分,具有原子性.通常认为,不符合第一范式的表,不具有关系模型的定义;

    2NF:

    满足第一个范式的基础上.
    要求:每一行(记录)必须能够唯一标识.同时要求:不存在非主键字段,不能对主键有部分函数依赖.(在组合键前提下,表中非主键字段,有依赖于组合主键内的个别字段依赖的情况). 比如:非主键关键字(教室号码),依赖主键内的部分字段(班级id),非主键关键字(教师性别),依赖于主关键字内部分字段(教师)

    解决方案:常用方案是增加一个单子段主键即可,可以保证唯一标识,而且也不能形成部分函数依赖(一个独立的字段)

    3NF:

    不错出现传递依赖:不能出现 A->B->C
    这样的结构,如果出现,就说C传递依赖A

    实体上表示,一个关系(二维表),只能保持一个实体的信息,不能保存多个信息

    通用原则:
    1,每个实体一个表(可以采取常用信息和详细信息分开保存的思路)
    2,为一个关系(二维表)增加,一个逻辑主键(不属于真实实体属性的字段),用于做标识。
    3,出现二维表对应的关系,采用1:1, 1:N, M:n的形式
    将关联关系设计。
    注意:
    实际的开发中,常常会为了操作方便,编码容易,做一些逆规范化的事情。
    例如,每次得到授课信息时,都需要得到教室号。如果不做逆规范话,每次都
    需要2个表内获得数据。可以考虑,将教室号,放入到 代课信息内。查询容易,编码简单。

    • 校对规则

    每一套编码字符集都有与之相关的校对规则.即,在当前编码规则下,字符间的比较规则,顺序;

    每个字符集都支持不定数量的校对规则,可以通过指令:show collation 查看字符集地区名比较规则(ci,cs,bin)(不区分大小写,区分大小写,字节比较)

    show collation like `gbk%`;
    

    在设置字符集的时候,可以设置当前字符集所采用的校对规则,如果不设置校对规则,那么每个字符集都有一个默认的校对规则;

    以gbk为例子,比较chinese_ci bin 之间的区别,一个chinese_ci,一个chinese_bin,插入相同的数据:

    当使用order by 进行排序时,数据会按照某个字段进行排序.由于排序规则不同,排序会不一致

    character set gbk collate gbk_chinese_ci;
    character set gbk collate gbk_bin;
    
    select * from tb_3 order by name;
    select * from tb_4 order by name;
    
    
    • 查询语句 select

    select语法: select [查询选项][查询表达式(字段)] [from 子句][where 子句][having 子句][order by 子句][limit 子句];

    要求子句可以不出现.如果出现,要求必须按照顺序书写;

    表达式:字段,值,返回值的语句,函数的返回值

    列子句

    select id+10,concat(t_name,'-',gender),c_name from teacher_class;
    //concat字符串拼接函数,字段可以作为参数,也可以进行加法运算
    
    

    别名: 通常一个表达式,形式不够好,不容易读取,起一个可以容易读取的别名即可:

    使用关键字 as: 标识符 [as] 别名;as可以省略,建议保留

    from子句:表示查询的目标数据源,通常情况下是表名,表名也支持别名

    select * from teacher_class,num_2;
    

    表名也是一个列表:如果没有任何条件的两个表名,会得到表1乘表2的所有数据.交叉连接,笛卡尔积;

    from可以省略,但是有些数据库不支持,MySQL支持;使用dual作为虚拟表存在

    select now() from dual;
    
    

    where 子句:

    用于通过条件过滤数据,得到真实的结果;

    MySQL支持的操作符:逻辑运算符和关系运算符
    =,>,<,>=,<=.!= (<>)

    字符串匹配运算符:like 可以利用通配符,完成模糊匹配

    通配符: _ 匹配一个字符,%匹配多个任意字符,%匹配%,_匹配_

    判断某个值是否为null: is null, is not null;
    isnull()结构可以判断null值;

    <=>功能与 = 一致,特别的功能在于可以比较null值

    select null is not null,null is null;
    select null<=>null, 10 <=> null;
    

    between and:

    between valueA and valueB:范围取值

    范围比较,闭区间: valuesA<=expr<=valueB;

    select * from teacher_class where id between 3 and 5; //3,4,5三个id的值
    
    select * from teacher_class where id in (3,5)  //两个值,3,5
    

    in| not in:集合之内(外);
    in| not in(集合元素);

    .interval:

    获得一个集合元素的集合:interval(值A,元素1,元素2....);
    一次判断值与元素之间的大小,如果值A小于元素1,则返回0;如果值A小于元素2,则返回1,依次类推;

    select t_name ,days,interval(days,10,20,30) from teacher_class where interval(days,10,20,30) = 1;
    
    

    逻辑运算符: And && ,Or ||, Not ! ;

    null值的特殊性: not null,! null 为null

    异或:xor ,有null就是null

    . group by:分组聚合查询语句:通过列内,不同的值,完成数据统计;

    分组的目录,通常是组内统计:统计是通过MySQL的统计函数完成的;

    sum()计算所有表达式的和

    select t_name,sum(days) from teacher_class where 1 group by t_name; //所有老师的上课天数
    
    select c_name, sum(days) as sum_days from teacher_class where 1 group by c_name; //所有班级的上课天数
    
    

    如果合计函数的使用,没有与group by配合,统计所有的数据,将所有的数据当作一组.

    select sum(days) from teacher_class where 1;
    

    排序group by:会通过排序字段,为数据进行重新排序,默认升序(asc)

    select t_name,sum(days) from teacher_class where 1 group by t_name [asc | desc];
    

    group by 多字段分组:

    select t_name,c_name,sum(days) from teacher_class where 1 group by t_name,c_name; 类似多字段主键,使用组合的字段进行标识;

    . rollup:在使用多字段分组时,通常需要做上级统计:

    select t_name,c_name,sum(days) from teacher_class where 1 group by t_name,c_name with rollup;

    使用 with rollup ,可以相当于利用组合条进行统计后,
    再使用上一条件再次统计一次。
    注意,会统计到 没有分组的情况,整个都是一组的情况

    是否可以得到 大于某些代课天数的讲师信息?
    select t_name, sum(days) from teacher_class where sum(days)>50 group by t_name;

    分析发现:
    where先执行,group by 后执行。
    Sum()在计算的时候,没有分组的呢

    无法在在where内使用合计函数:
    需要一个,可以在结果内,再次过滤的功能:
    having

    slect t_name,sum(days) from teacher_class where days > 10 group by t_name; //where会影响group的统计,说明where执行在前;
    

    . having :负责在结果(where查询到的)中进行再次过滤,可以像使用where一样,having进行处理;

    select t_name,sum(days) from teacher_class where 1 group by t_name having sum(days) > 50;
    
    • 聚合函数

    Sum(),Avg(),Max(),Min(),Count()

    Count():统计总的记录数,统计的是非null的记录数,通常用count(*)来统计.

    group_concat(): 组内字符串连接,做了分组,只能显示组内信息.如果需要对其它列进行显示,可以把结果连起来;

    • order by

    校对规则,决定排序关系;按照字段值进行排序,order by 字段 asc | desc;
    默认升序asc;

    允许多字段排序:先按照第一个字段排序,如果说,不能区分,才使用第二个字段,依次类推

    select * from teacher_class order by days;
    select * from teacher_class order by days ddesc;
    select * from teacher_class order by days desc,begin_date desc,edn_date asc;
    

    如果是分组,应该用对应字段group by,进行排序的group by语法.

    . limit

    限制获得的记录数量;
    limit offset,row_count;
    ;offset偏移量,默认从零开始,0可省略,row_count要取的记录数,不足,全部取了;

    select * from teacher_class limit 3,5;
    select * from teacher_class limit 5;
    
    

    . distinct: 去除重复记录

    重复的记录指的是字段值,都相同的记录,而不是部分字段相同的记录

    相对的是all,表示所有.默认就是all行为.

    select days from teacher_class;
    select distinct days from teacher_class;
    selct days,begin_date from teacher_class;
    select distinct days,begin_date from teacher_class;
    
    

    联合查询:

    将多条select语句的结果,合并到以前,称为联合操作.

    使用的场景: 获得数据的条件,出现逻辑冲突,或者很难在一个逻辑内表示,可以拆分多个逻辑,分别实现,最后吧结果合并到一起.

    //获取两个不同班级上课天数最多的老师
    (select t_name,days,from teacher_class where c_name='1234' order by days desc limit 1) union (select t_name,days,from teacher_class where c_name='2221' order by days desc limit 1)
    
    

    union all:环境

    如果union的结果存在重复的记录,会自动消除重复.如果不想消除重复,使用union all达到目的.

    (select t_name,days,from teacher_class where c_name='1234' order by days limit 10) union all
    (select t_name,days,from teacher_class where c_name='2221' order by days desc limit 10)
    
    

    排序:子语句结果的排序,1 . 将子句包裹在括号内, 2. 子语句的order by 只有在order by配合limit时才生效.union在做子语句时,会对没有limit的子语句优化(忽略)

    (select t_name,days,from teacher_class where c_name='1234' order by days) union all   //没有limit,结果无序的
    (select t_name,days,from teacher_class where c_name='2221' order by days desc limit 10)
    
    

    如果要对所有结果进行排序,只需要在最后一个select语句后进行排序.

    (select t_name,days,from teacher_class where c_name='1234') union all
    (select t_name,days,from teacher_class where c_name='2221') order by days desc;  //括号不是必须的,但提高了可读性
    

    多个select语句的检索到的字段数,必须一致.更加严格的是,数据类型上也要一致.MySQL内部会做类型转换,前提是能够转换成功. 检索结果中列的名称是根据第一条select语句决定的.

    • 子查询:语句内部的查询语句,就是子语句查询

    如果一个表内,有多个相同的结果时取数据的思路: 比如并列第一的情况

    select t_name,gender from teacher_class order by days limit 1;
    
    //方案:先获取条件数,作为判断依据变量,这是SQL支持的
    var1 = select max(days) from teacher_class;
    select t_name,gender from teacher_class where days=var1;
    
    
    //select t_name,gender from teacher_class where days=(select max(days) from teacher_class);
    

    子查询分类: 分类的依据: 出现的位置和返回值的形式

    返回值分类:
    单一值,一列,多列,表(多行,多列)

    出现的位置:
    where 型,where 后

    from型:from 后

    exists 型

    集合运算符:in,not in, any, all , !=all(not in)

    返回一行:

    在参与比较时,使用括号可以构建一行:

    (filed1, field2) 
    select t_name, gender, c_name from teacher_class where (gender, c_name) = (select distinct gender, c_name from teacher_class where t_name='李白' and c_name='0115' limit 1); 
    

    返回一个表:

    如果用于在from子句内,from子句内,要求使用一个表,是一个结果.
    应该给这个结果起个名称,别名.

    select * from (select t_name,c_name,days from teacher_class where days > 15) as temp_name where t_name like '李%';
    
    

    exists(subquery):如果子查询的可以返回数据,则认为exists表达式返回真. 否则返回假;

    //这两个语句完成的是同样的语句
    select * from teacher_class where exists(select * from teacher where teacher_class.id=t_id);
    
    select * from teacher_class where id in (select t_id from teacher);
    
    

    //两个语句的思路

    exists:先获得每一条teacher_class的数据,然后获得ID字段,去teacher 表内查询对应值,找到.

    in:先找到所有的ID 的可能性.再在检索teacher_class数据时,判断当前的id是否在id集合内.

    • join

    每一个实体,每一个表,一个业务逻辑,使用多个实体的数据,多张表应该在一起使用,将多个表的记录连接起来.

    总体的思路:将所有的数据,按照某种条件,连接起来,在进行筛选处理.

    连接的分类: 内连接,外连接,自然连接

    //内连接:数据内部的连接,要求连接的多个数据必须存在才能进行连接
    select join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days from join_teacher inner join join_teacher_class on join_teacher.id=join_teacher_class.t_id;
    
    
    //外连接:如果负责连接的一个或者多个数据不真实存在,则称之为外链接.
    select join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days from join_teacher left outer join join_teacher_class on join_teacher.id=join_teacher_class.t_id;
    
    

    内连接的处理:
    内连接,在连接时,是可以省略连接条件的.意味着所有的左表的数据都要与右表的记录做一个连接,共存M*N个连接.这种连接称为交叉连接或者笛卡尔积;

    select join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days from join_teacher inner join join_teacher_class;
    
    join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days from join_teacher cross join join_teacher_class;
    
    join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days from join_teacher join join_teacher_class;
    
    

    此时,可以用cross join 代替inner join.可以通过多表查询,不使用where做到笛卡尔积.

    在MySQL中cross join和inner join相同,但在数据库的定义上,交叉连接就是笛卡尔积.是没有条件的inner join.MySQL inner join是默认的连接方案,可以省略join

    . 有条件的内连接: 会在连接时过滤掉非法的连接.

    where的写法:在数据过滤上,数据交叉连接完成后,再做数据过滤.

    on的写法:在连接时,就对数据进行判断;

    using的写法:using要求,负责连接的两个实体之间的字段名称一致.

    建议在有同名字段时用using,而在通用的条件时,用on.在数据过滤时(不是指连接过滤)使用where.

    查询条件,与外链接通用(外链接,不能使用where作为连接条件)

    //后面再补充


    • select

    将检索到的数据,保存到服务器的文件内.可以自动创建文件,但是不能重写已经存在的文件,否则报错;

    生成的文件格式:
    默认的,采用行来区分记录,而采用制表符,来区分字

    为了满足某种特别的需求,会采用不同的分割方式。
    支持,在导出数据时,设置记录,与字段的分割符。

    通过如下的选项:

    fields:设置字段选项
    Lines: 设置行选项(记录选项)

    先看默认值:

    字段:fields terminated by '\t' enclosed by '' escaped by '\‘

    记录:lines terminated by '\n' starting by ''

    可以自己设定

    select * into outfile 'e:/amp/three'

    fields terminated by ','

    lines terminated by '\n' starting by 'start:'
    from teacher_class where t_name = '韩信';

    select * into outfile 'e:/amp/four'
    fields terminated by '\t' enclosed by 'x'
    lines terminated by '\n' starting by 'start:'
    from teacher_class where t_name = 'jjj';

    注意:
    常规的,所有的记录,应该通过行来显示
    例外是保存二进制数据:

    //
    select * into outfile '/usr/application/www' from table_name where name = "jjj";
    
    

    Blob binary

    使用 into dumpfile

    select * into dumpfile 'e:/amp/six' from teacher_class where t_name = 'jjj' limit 1;
    
    • 新增数据Insert&replace&loaddata

    Insert into tbl_name (字段列表)values (值列表)

    可以不将所有的字段都插入数据。
    如果说需要完成部分字段的插入,需要必须存在 字段列表

    没有插入的字段,使用默认值:
    insert into teacher_class (t_name) values ('张三丰');

    如果是插入部分字段, 可以使用下面的set语句:
    insert into teacher_class set t_name='张无忌';
    insert into teacher_class set t_name='赵敏', c_name='武术';

    值列表的语法,可以一次性插入多条数据:
    每条数据采用记录构造符 括号完成即可:
    insert into teacher_class (t_name, c_name) values
    ('张君宝', '太极'),
    ('杨过', '黯然销魂掌');

    插入数据时,如果主键冲突会如何?

    默认有 主键约束,不会插入成功
    但是可以在insert语法内,控制
    在主键冲突时,改成执行更新操作。

    Update 后 不跟 set。

    insert into teacher_class (id, t_name, c_name) values
    (13, '杨露婵', '太极')
    on duplicate key update
    t_name='杨露婵', c_name='太极'
    ;

    插入的数据源:

    除了使用自定义数据外,
    还可以是使用 select 语句
    查询到数据,作为插入的数据源。

    insert into teacher_class (t_name, c_name) select t_name, c_name from teacher_class;
    

    数据可以来源于其他数据表,要求,字段数量和类型一致即可:

    insert into teacher_class (t_name, c_name) select t_name, class_name from teacher;
    

    通过强制使用 default关键字,或者default()函数,使用默认值;
    insert into teacher values
    (13, 'xxx', 'yyy', default),
    (14, 'xxx', 'yyy', default(days))
    ;

    Replace

    主键或唯一索引冲突,则替换
    否则插入。
    replace into teacher values
    (1, '韩非', '法家', 30);

    replace into teacher values
    (15, '韩非', '法家', 30);

    Load data infile "file" into table tbl_name;

    导入 select * into outfile 'file' 命令
    导出的内容;
    上面两个命令 互补。

    注意:
    导入时,涉及到数据增加,需要考虑,
    是否冲突的情况。

    通常,可以在导出时,将主键导出成null。
    利用自动增长的特性。可以形成新的主键:

    同样:
    在导入数据时,需要同样指定数据的分割,
    起止符号等。
    保证 导出数据的格式与导入数据需要的格式
    是一致的即可

    删除数据:Delete

    允许使用条件(删除符合条件的数据)

    允许使用 limit :
    限制删除的记录数。
    Limit N;

    常见的是
    Limit 配合 order by来使用:
    先将结果排序,再删除固定数量的记录:
    delete from teacher order by days limit 10;
    只有 order by 是没有意义的
    ;

    允许连接删除:允许使用类似join的语法,同时删除多个表内的记录;需要先提供表名,再提供连接条件;可以拆分成 delete one,deletetwo...

    delete from one,two using one join two on one.public_field=two.public_field where one_id=2;
    
    

    清空表:truncate

    类似delete from table;

    truncate teacher;

    truncate:不会返回删除的记录数. 会重建自动增长的主键

    delete逐行删除
    ,truncate删除表,新建表

    • 更新数据

    replace,insert onduplicate key update

    update where order by limit;

    多表更新:

    update one join two on one.public_field=two.public_field set one_data='x',two_data='y' where one_id = 3;

    • 备份还原

    方案1:
    适用于 mysiam表:

    直接将 tbl_name.frm
    Tbl_name.myd
    Tbl_name.myi
    三个文件,保存,备份即可。

    需要的时候,直接解压到移动
    到相应的数据库目录内即可

    注意,如果是同样的方法,处理的
    innodb表结构的文件。
    则使用showtables时,也可以看到
    但是不能使用的;

    方案2:
    通用的方案:
    思路是:将建表结构,与插入数据的sql语句生成并保存,下次如果需要该结构和数据
    直接将数据语句 执行即可。

    利用 mysql提供的工具完成的:
    不是sql语言的一部分。

    不需要在 mysql命令行客户端执行,
    直接运行即可。

    将备份的数据库还原:
    就是将刚刚生成的sql语句,执行即可;

    将备份的数据库还原:
    

    就是将刚刚生成的sql语句,执行即可。

    在mysql客户端,直接执行即可:
    如何执行 保存在文件内的sql语句:
    使用 source 指令,可以指定需要执行sql语句的源代码文件:

    常用的备份操作:
    1,备份整个数据内的表:
    Mysqldump -uroot -p db_name > bak.sql

    2,备份数据库内的某张表:
    mysqldump -uroot -p php_one teacher_class > e:/php_one_teacher_class.sql
    mysqldump -uroot -p php_one teacher_class tbl_name1 tbl_name2 tbl_name3 > e:/php_one_teacher_class.sql

    • 视图

    创建视图:

    Create view view_name
    AS select_statement;

    create view v_teacher as select id,t_name from info_teacher;
    

    视图就是一个存在与数据库中的虚拟表了;视图,本身没有数据,只是通过执行相应的select语句完成获得相应的数据。

    视图管理

    删除视图:
    Drop view [if exists] view_name;

    修改视图:
    Alter view view_name

    修改视图内,所使用的字段的名称:
    视图名称后,使用 (字段列表即可)

    缩减业务逻辑:
    通过视图还可以,使复杂的业务逻辑,简单的完成,先使用视图完成一定的逻辑,在在视图的基础上,完成另外的逻辑。
    通常,视图完成的逻辑,都是相对来说比较基础的逻辑。

    create view join_info as select tc.id as tc_id, t_name, c_name, days from join_teacher_class as tc left join join_teacher as t on tc.t_id=t.id left join join_class as c on tc.c_id=c.id;
     
    select * from join_info;
    

    视图的执行过程:
    视图的执行算法:

    存在两种执行算法:
    1,merge
    2,temptable

    指的是一个视图是在什么
    时候执行,依据哪些方式执行:

    merge:合并的执行方式,每当执行的时候,现将我们视图的sql语句
    与外部查询视图的sql语句,混合在一起。最终执行:

    Temptable:临时表,模式,每当查询的时候,将视图所使用select语句
    生成一个结果的临时表。再在当前的临时表内进行查询。

    当用户创建视图时,mysql默认使用一种 undefine的处理算法:就是会自动在
    合并和临时表内进行选择。

    • 事务

    一组sql语句操作单元。
    组内所有sql语句完成一个业务。
    如果整组成功:意味着全部sql都实现
    如果其中任何一个失败。意味着整个操作都失败。
    失败,意味着整个过程都是没有意义的。应该
    是数据库回到 操作前的初始状态。

    上面的特性,就是事务。

    如何处理?

    1,失败后,可以回到开始位置
    2,没都成功之前,别的用户(进程,会话)是不能看到
    操作内的数据修改的。

    思路:就是在 一组操作之间,设计一个记号,备份点。

    实现:
    利用 innodb存储引擎的,事务日志功能:

    SQL执行分成2个阶段:1,执行阶段 2,将执行结果,提交的数据库的阶段;

    其中,我们的事务日志,就是保存执行阶段的结果.如果用于选择提交,则才将执行的结果提交到数据库;默认的执行方式叫自动提交,执行完毕,自动完成提交,因此.
    需要关闭自动提交功能.

    自动提交
    存在一个系统的变量,
    Autocommit 可以对自动提交进行配置

    show variables like autocommit;

    关闭后,再次执行相应的更新语句:
    发现,在其他连接中,查看数据,
    没有发生变化,因为结果没有提交.

    提交或回滚。
    

    在此基础上,执行完所有的sql语句。

    判断是否都成功(出现错误,包括语法错误,和逻辑错误
    服务器错误)。

    成:将结果提交
    利用 commit

    败:回到开始位置。
    Rollback

    常见的事务的指令:
    开启事务
    Start transaction; 可以使用begin。
    关闭自动提交。如果事务结束了,
    成或败,都会将自动提交机制,回到start时的状态。

    成功:commit;
    失败:rollback;

    限定:
    在innodb下生效(DBD)

    事务的特点:
    1,原子性。
    2,一致性。
    3,隔离性。
    4,持久性。
    ACID。

    • 触发器

    监听数据进行操作:在当前的表上,设置一个对每行数据的一个监听器,监听相关事件,每当事件发生时.会执行一段有SQL完成的一段功能代码;

    触发器的元素:事件,执行代码

    创建触发器: create trigger 名字 事件 执行代码;

    事件:insert,delete,update

    事件的时机:执行之前和执行之后,after/before

    由时机和事件在一起形成了6种事件:before insert,before delete...

    事件规定在哪个表上的什么时机的什么动作上;

    触发程序:特定事件发生,即触发:

    update addMoney set stu_money = stu_money + 20 where id = 1;

    触发器不能同名,目前MySQL只支持一类事件设置一个触发器

    create trigger my_trigger after update on addMoney for each row update class set cz_money=cz_money+20;
    
    

    管理触发器:

    删除:
    Drop trigger trigger_name;
    查看:
    Show create trigger trigger_name;

    在触发器内,获得触发该触发程序时的数据:
    Old new

    利用 触发程序内的 new 和 old来完成;

    create trigger my_trigger after update on addMoney for each row update class set cz_money=cz_money+(old.stu_money-new.stu_money);
    

    Old:
    监听事件所在表上的数据,在事件发生之前时的数据。旧的数据。
    New:
    监听表上,事件发生之后,新处理完毕的数据。

    数据,就是触发该事件的记录。

    事件是insert呢? 不能使用old
    事件是 delete呢?不能使用new

    如果一个触发程序,由多条SQL语句组成;

    应该:1.语句组成语句块(begin end)来标示语句块.
    2.语句块的语句需要独立的语句结束符,分号;

    命令行:由于触发器程序内使用分号作为语句结束符,那么当命令行客户端碰到分号时,就应该理解成触发程序内子语句结束,而不是整个触发器的语句结束.

    应该通过修改命令行的语句结束符达到目的.delimiter语句可以完成设置语句结束符;

    drop trigger ruxue;
    delimiter $$
    create trigger ruxue after insert on czbk_student for eache row 
    begin
    update class set student_count = stu_count  +1;
    update class set cz_money=cz_money+20;
    end
    $$
    
    delimiter;
    
    SQL编程

    SQL:结构化查询语言。
    是一门编程语言。是由于管理数据的编程语言。

    元素:
    数据,
    数据类型,
    变量,
    函数,
    控制流程,
    运算符 ,
    注释;

    注释:
    行:

    --[空格]
    块:
    /* */

    结束符:
    命令行:\g \G

    可以使用 delimiter 来修改语句结束符
    Delimiter $$

    变量:
    字段名就是变量。
    php有许多系统默认变量例如:
    show variables like 'char%';

    用户自定义变量:

    定义一个变量:set
    Set 变量名 = 变量值。

    注意,为了区分 系统变量和字段与用户自定义变量,需要在用户变量前,增加@标识符。
    set @who = "haha";

    通过 select 语句可以获得当前的变量的值:
    select @who;

    Set是专门的为变量赋值的形式,甚至可以子查询

    set @total = (select count(*) from join_teacher);
    

    定义一个变量 select info:

    Select 字段列表 表达式 。。。 Into 变量列表。

    select 10,14,20 into @a,@b,@c;
    select c_name from join_class where id = 2 into @c_name;
    

    注意,select into @var 要求,只能返回一行。如果返回多行,会语法错误,或者只将最后一行的数据,注入到变量内。

    //会报错
    select c_name from join_class where 1 into @c_name;
    

    利用 select语句的部分表达式达到为变量赋值的目的:

    select @who = 'xiaoming'; //=是关系判断
    

    使用 := 的形式;

    select @who := 'xiaoming';
    

    注意,=应该赋值,但是在 select语句内,就成了 关系等于。使用专门的赋值运算符 :=。
    同样适用于 set。

    set @i := '111';
    select * from teacher where @who:='xiaoming';
    
    

    使用变量是在表达式,或者使用select查询到即可。

    1,作用域。用户定义的函数,是全局的(函数内可用)。存在局部作用域变量,函数内定义的变量。
    2,有效期。会话结束(连接结束)。

    运算符:

    • 函数

    内置函数
    数值:
    Rand()得到1-0之间的随机数

    select rand();
    

    如何得到5到10?
    5+(0-5)
    5+rand()*5;
    取整:

    select floor(rand()*5+5);
    

    格式化:
    format

    select format(2132432.12323,2); //2位小数显示
    

    时间日期:
    Now();
    Unix_timestamp();

    select unix_timestamp();
    select from_unixtime(123456);
    select from_unixtime(unix_timestamp());
    

    字符串:

    Concat()字符串连接
    Substring(原字符串,开始位置,截取长度);
    开始位置 从1开始

    Char_length();
    Length();

    Lpad(需要补足的字符串,补足后的长度,补字符串);
    左边补足:

    其他:
    Md5()

    select substring("hahahaha",2,2);
    select length('你好');  //4
    select char_length("你好"); //2
    
    select lpad("1",3,"0"); //001
    
    select md5("2");
    select password("2");
    select sha1("332");
    
    
    数值函数
    Abs(X),绝对值 abs(-10.9) = 10
    Format(X,D),格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
    Ceil(X),向上取整 ceil(10.1) = 11
    
    Floor(X),向下取整 floor (10.1) = 10
    Round(X),四舍五入去整
    Mod(M,N) M%N M MOD N 求余 10%3=1
    Pi(),获得圆周率
    Pow(M,N) M^N
    Sqrt(X),算术平方根
    Rand(),随机数
    TRUNCATE(X,D) 截取D位小数
    时间日期函数
    Now(),current_timestamp(); 当前日期时间
    Current_date();当前日期
    current_time();当前时间
    Date(‘yyyy-mm-dd HH;ii:ss’);获取日期部分
    Time(‘yyyy-mm-dd HH;ii:ss’);获取时间部分
    Date_format(‘yyyy-mm-dd HH;ii:ss’,’ %D %y %a %d %m %b %j');
    Unix_timestamp();获得unix时间戳
    From_unixtime();//从时间戳获得时间
    字符串函数
    LENGTH (string )   //string长度,字节
    CHAR_LENGTH(string)    //string的字符个数
    SUBSTRING (str , position [,length ])   //从str的position开始,取length个字符
    REPLACE (str ,search_str ,replace_str )   //在str中用replace_str替换search_str
    
    
    
    INSTR (string ,substring )   //返回substring首次在string中出现的位置
    CONCAT (string [,... ])   //连接字串
    CHARSET(str)  //返回字串字符集
    LCASE (string )  //转换成小写
    LEFT (string ,length )   //从string2中的左边起取length个字符
    LOAD_FILE (file_name )   //从文件读取内容
    LOCATE (substring , string [,start_position ] )   //同INSTR,但可指定开始位置
    LPAD (string ,length ,pad )   //重复用pad加在string开头,直到字串长度为length
    LTRIM (string )  //去除前端空格
    REPEAT (string ,count )   //重复count次
    RPAD (string ,length ,pad)   //在str后用pad补充,直到长度为length
    RTRIM (string )   //去除后端空格
    STRCMP (string1 ,string2 )   //逐字符比较两字串大小
    
    
    
    流程函数:
    
    CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END   多分支
    IF(expr1,expr2,expr3)  双分支。
    聚合函数
    Count()
    Sum();
    Max();
    Min();
    Avg();
    Group_concat()
    其他常用函数
    Md5();
    Default();
    
    
    • 自定义函数

    要素:
    函数名,
    参数列表,
    函数体,
    返回值;

    语法:
    定义:
    Create function 函数名 (参数列表) 返回值类型
    函数体

    delimiter $$
    create function sayHi() returns varchar(20);
    begin
    return 'Hi';
    end
    $$
    delimiter;
    
    //调用
    select sayHi();
    
    注意:函数是与当前的数据库绑定的,可以使用库名.函数名的形式调用;
    
    select one.sayHi();
    
    

    sql中的流程控制:

    
    分支
    If 条件1 then
    条件1满足执行的语句
    Elseif 条件2 then
    条件2满足执行的语句
    ….
    Else 
    上面的条件全都不满足,执行的语句
    End if;
     
    Elseif 和 else 都是可以省略的。
    
    循环
    While 条件 do
    循环体
    End while
    
    
    循环的提前终止
    Leave       break 终止循环
    Iterate     continue 终止当前循环
     
    注意,不是根据leave和iterate所在的位置来决定终止哪个循环,而是由循环的标签来决定的。
     
    循环的标签,给循环起名字。
    标签 : while
    End while 标签;
    
    
    函数内使用的变量
    @var的形式,相当于 全局变量,函数内和函数外通用。
     
    函数的参数:
    参数,同样需要确定类型。
    参数名 类型
    
    一个函数,可以有多个参数,使用 逗号分割。
     
     
    函数声明的局部变量:
    使用 declare声明局部变量。 需要指定类型,可以指定默认值 default。
    
    delimiter $$
    create function func1() returns varchar(20)
    begin
    
    if hour(now(()) > 18 then
        return 'night';
    else
        return 'day';
    end if;
    
    end
    $$
    delimiter;
    
    
    delimiter $$
    create function func2() returns int
    begin
    -- 1-10的和
    set @i = 1;
    set @sum = 0;
    
    while @i<=10 do
        set @sum = @sum + @i;
        set @i = @i + 1;
    end while;
    return @sum;
    end
    $$
    delimiter
    
    
    delimiter $$
    create function func2() returns int
    begin
    -- iterate
    set @i = 1;
    set @sum = 0;
    
    w:while @i<10 do
        set @i = @i + 1;
        if @i = 5 then
            -- iterate w;
            iterate w;
        end if;
        set @sum = @sum + @i;
        
    end while w;
    return @sum;
    end
    $$
    delimiter
    
    
    delimiter $$
    drop function if exists sayHi;
    create function sayHi(user_name varchar(10)) returns varchar(20)
    begin
    return concat("Hi ",user_name);
    end
    $$
    delimiter;
    
    delimiter $$
    drop function if exists func2;
    create function func2() returns int
    begin
    -- 1-10的和
    declare i int default 0;
    declare total int default 0 ;
    while i<=10 do
        set total = total + i;
        set i = i+ 1;
    end while;
    return total;
    end
    $$
    delimiter;
    
    

    相关文章

      网友评论

        本文标题:MySQL学习

        本文链接:https://www.haomeiwen.com/subject/feqjbttx.html