MySQL 学习笔记

MySQL 学习笔记 一

一、数据库简单介绍

1、 按照数据库的发展时间顺序，主要出现了以下类型数据库系统：

Ø 网状型数据库

Ø 层次型数据库

Ø 关系型数据库

Ø 面向对象数据库

上面4中数据库系统中，关系型数据库使用最为广泛。面向对象数据库则是由面向对象语言催生的新型数据库，目前的一些数据库系统，如：SQL Server 2005、Oracle10g等都开始增加面向对象的特性。

 

二、常用基本SQL语句/语法

Ø SQL语句基础理论

SQL是操作和检索关系型数据库的标准语言，标准SQL语句可用于操作然后关系型数据库。

标准的SQL语句通常划分为以下类型：

查询语句：主要由于select关键字完成，查询语句是SQL语句中最复杂，功能最丰富的语句。

DML（Data Munipulation Language，数据操作语言）语句，这组DML语句修改后数据将保持较好的一致性；操作表的语句，如插入、修改、删除等；

DDL（Data Definition Language，数据定义语言）语句，操作数据对象的语言，有create、alter、drop。

DCL（Data Control Language，数据控制语言）语句，主要有grant、revoke语句。

事务控制语句：主要有commit、rollback和savepoint三个关键字完成

DDL语句

DDL语句是操作数据库对象的语句，包括创建create、删除drop、修改alter数据库对象。

常见数据库对象

对象名称	对应关键字	描述
表	table	表是数据库存储的逻辑单元，以行和列的形式存在；列是字段，行就是一条数据记录
数据字典		就是系统表，存储数据库相关信息的表，系统表里的数据通常有数据库系统维护。系统表结构和数据，开发人员不应该手动修改，只能查询其中的数据
视图	view	一个或多个数据表里的数据的逻辑显示，视图就是一张虚拟的表，并不真正存储数据
约束	constraint	执行数据检验规则，用于保证数据完整性的规则
索引	index	用于提高查询性能，相当于书的目录
函数	function	用于完成一个特定的计算，具有返回值和参数
存储过程	procedure	完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境
触发器	trigger	相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理

上面的对象都可以通过用create、alter、drop完成相关的创建、修改、删除操作。

常用数据类型

列类型	说明
tinyint/smallint/mediumint int(integer)/bigint	1字节、2字节、3字节、4字节、8字节整数，又可分有符号和无符号两种。这些整数类型的区别仅仅表现范围不同
float/double	单精度、双精度浮点类型
decimal(dec)	精确小数类型，相当于float和double不会产生精度丢失问题
date	日期类型，不能保存时间。当Java里的Date对象保存到该类型中，时间部分丢失
time	时间类型，不能保存日期。当Java的Date对象的保存在该类型中，日期部分丢失
datetime	日期、时间类型
timestamp	时间戳类型
year	年类型，仅保存年份
char	定长字符串类型
varchar	可变长度字符串类型
binary	定长二进制字符串类型，它以二进制形式保存字符串
varbinary	可变长度的二进制字符串类型，二进制形式保存字符串
tingblob/blob mediumblob/longblob	1字节、2字节、3字节、4字节的二进制大对象，可存存储超图片、音乐等二进制数据，分别可存储：255/64K/16M/4G的大小
tingtext/text mediumtext/longtext	1字节、2字节、3字节、4字节的文本对象，可存储超长长度的字符串，分别可存储：255/64K/16M/4G的大小的文本
enum(‘val1’, ‘val2’, …)	枚举类型，该列的值只能是enum括号中出现的值的之一
set(‘value1’, ‘value2’, …)	集合类型，该列的值可以是set中的一个或多个值

Ø 常用查询

MySQL结束符是“；”结束。

 

1、    显示所有数据库

show databases;

 

2、    删除数据库

drop database dbName;

 

3、    创建数据库

create database [if not exists] dbName;

中括号部分可选的，判断该数据不存在就创建

 

4、    切换、使用指定数据库

use dbName;

 

5、    显示当前使用数据库所有的表对象

show tables；

 

6、    显示表结构describe（desc）

desc tableName;

 

7、    创建一张表

create table user (

        --int 整型

        uId int,

        --小数

        uPrice decimal,

        --普通长度文本，default设置默认值

        uName varchar(255) default ‘zhangsan’,

        --超长文本

        uRemark text,

        --图片

        uPhoto blob,

        --日期

        uBirthday datetime

);

 

8、    子查询建表方法

部分列名匹配模式：

create table userInfo (

name varchar(20),

sex char

)

as

select name, sex from user;

上面的列名和子查询的列名以及类型要对应

 

全部列名模式：

create table userInfo

as

select * from user;

直接将整个表的类型和数据备份到新表userInfo中

 

9、    添加表字段

添加单列

alter table user add tel varchar(11) default ‘02012345678’;

 

添加多列

alter table user

add (

photo blob,

birthday date

);

上面就同时增加了多列字段

 

10、    修改表字段

修改tel列

alter table user modify tel varchar(15) default ‘02087654321’;

修改tel列的位置，在第一列显示

alter table user modify tel varchar(15) default '02087654321' first;

修改tel列的位置，在指定列之后显示

alter table user modify tel varchar(15) default '02087654321' after age;

注意：alter modify不支持一次修改多个列，但是Oracle支持多列修改

但是MySQL可以通过多个modify的方式完成：

alter table user

modify tel varchar(15) default '02087654321' first,

modify name varchar(20) after tel;

 

11、    删除指定字段

alter table user drop photo;

 

12、    重命名表数据

表重命名

alter table user rename to users;

 

字段重命名

alter table users change name u_name varchar(10);

alter table users change sex u_sex varchar(10) after u_name;

如果需要改变列名建议使用change，如果需要改变数据类型和显示位置可以使用modify

13、 删除表

drop table users;

drop删除表会删除表结构，表对象将不存在数据中；数据也不会存在；表内的对象也不存在，如：索引、视图、约束；

 

truncate删除表

truncate都被当成DDL出来，truncate的作用就是删除该表里的全部数据，保留表结构。相当于DDL中的delete语句，

但是truncate比delete语句的速度要快得多。但是truncate不能带条件删除指定数据，只会删除所有的数据。如果删除的表有外键，

删除的速度类似于delete。但新版本的MySQL中truncate的速度比delete速度快。

Ø 约束

MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；

约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。

 

常用五类约束：

not null：非空约束，指定某列不为空

unique： 唯一约束，指定某列和几列组合的数据不能重复

primary key：主键约束，指定某列的数据不能重复、唯一

foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据

check：检查，指定一个表达式，用于检验指定数据

MySQL不支持check约束，但可以使用check约束，而没有任何效果；

 

根据约束数据列限制，约束可分为：

单列约束：每个约束只约束一列

多列约束：每个约束约束多列数据

 

MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；

1、    not null约束

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：

所有的类型的值都可以是null，包括int、float等数据类型

空字符串“”是不等于null，0也不等于null

create table temp(

        id int not null,

        name varchar(255) not null default ‘abc’,

        sex char null

)

上面的table加上了非空约束，也可以用alter来修改或增加非空约束

增加非空约束

alter table temp

modify sex varchar(2) not null;

 

取消非空约束

alter table temp modify sex varchar(2) null;

 

取消非空约束，增加默认值

alter table temp modify sex varchar(2) default ‘abc’ null;

 

2、    unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null

同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。

唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

MySQL会给唯一约束的列上默认创建一个唯一索引；

create table temp (

        id int not null,

        name varchar(25),

        password varchar(16),

        --使用表级约束语法，

        constraint uk_name_pwd unique(name, password)

);

表示用户名和密码组合不能重复

添加唯一约束

alter table temp add unique(name, password);

alter table temp modify name varchar(25) unique;

删除约束

alter table temp drop index name;

 

3、    primary key

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束，

那么这些列都不允许为空值，并且组合的值不允许重复。

每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。MySQL的主键名总是PRIMARY，

当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

列模式：

create table temp(

    /*主键约束*/

    id int primary key,

    name varchar(25)

);

 

create table temp2(

    id int not null,

    name varchar(25),

    pwd varchar(15),

    constraint pk_temp_id primary key(id)

);

 

组合模式：

create table temp2(

    id int not null,

    name varchar(25),

    pwd varchar(15),

    constraint pk_temp_id primary key(name, pwd)

);

 

alter删除主键约束

alter table temp drop primary key;

 

alter添加主键

alter table temp add primary key(name, pwd);

 

alter修改列为主键

alter table temp modify id int primary key;

 

设置主键自增

create table temp(

        id int auto_increment primary key,

        name varchar(20),

        pwd varchar(16)

);

auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。

 

4、    foreign key 约束

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

也就是说从表的外键值必须在主表中能找到或者为空。

当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，

然后才可以删除主表的数据。还有一种就是级联删除子表数据。

注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，

那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。

创建外键约束：

主表

create table classes(

        id int auto_increment primary key,

        name varchar(20)

);

从表

create table student(

        id int auto_increment,

        name varchar(22),

        constraint pk_id primary key(id),

        classes_id int references classes(id)

);

 

通常先建主表，然后再建从表，这样从表的参照引用的表才存在。

表级别创建外键约束：

create table student(

        id int auto_increment primary key,

        name varchar(25),

        classes_id int,

        foreign key(classes_id) references classes(id)

);

上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n，

其中student是表名，n是当前约束从1开始的整数。

 

指定约束名称：

create table student(

        id int auto_increment primary key,

        name varchar(25),

        classes_id int,

        /*指定约束名称*/

        constraint fk_classes_id foreign key(classes_id) references classes(id)

);

 

多列外键组合，必须用表级别约束语法：

create table classes(

        id int,

        name varchar(20),

        number int,

        primary key(name, number)

);

create table student(

        id int auto_increment primary key,

        name varchar(20),

        classes_name varchar(20),

        classes_number int,

        /*表级别联合外键*/

        foreign key(classes_name, classes_number) references classes(name, number)

);

 

删除外键约束：

alter table student drop foreign key student_ibfk_1;

alter table student drop foreign key fk_student_id;

 

增加外键约束

alter table student add foreign key(classes_name, classes_number) references classes(name, number);

 

自引用、自关联（递归表、树状表）

create table tree(

        id int auto_increment primary key,

        name varchar(50),

        parent_id int,

        foreign key(parent_id) references tree(id)

);

 

级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on delete cascade

或on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null。

create table student(

        id int auto_increment primary key,

        name varchar(20),

        classes_name varchar(20),

        classes_number int,

        /*表级别联合外键*/

        foreign key(classes_name, classes_number) references classes(name, number) on delete cascade

);

 

5、    check约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用。

create table temp(

        id int auto_increment,

        name varchar(20),

        age int,

        primary key(id),

/*check约束*/

check(age > 20)

);

上面check约束要求age必须大于0，但没有任何作用。但是创建table的时候没有任何错误或警告。

 

 

Ø 索引

索引是存放在模式（schema）中的一个数据库对象，索引的作用就是提高对表的检索查询速度，

索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。

索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。

提示：索引保存在information_schema数据库里的STATISTICS表中。

 

创建索引方式：

自动：当表上定义主键约束、唯一、外键约束时，该表会被系统自动添加上索引。

手动：手动在相关表或列上增加索引，提高查询速度。

 

删除索引方式：

自动：当表对象被删除时，该表上的索引自动被删除

手动：手动删除指定表对象的相关列上的索引

索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。

 

创建索引：

create index idx_temp_name on temp(name);

 

组合索引：

create index idx_temp_name$pwd on temp(name, pwd);

 

删除索引：

drop index idx_temp_name on temp;

 

Ø 视图

视图就是一个表或多个表的查询结果，它是一张虚拟的表，因为它并不能存储数据。

视图的作用、优点：

限制对数据的访问

让复杂查询变得简单

提供数据的独立性

可以完成对相同数据的不同显示

   

创建、修改视图

create or replace view view_temp

as

    select name, age from temp;

通常我们并不对视图的数据做修改操作，因为视图是一张虚拟的表，它并不存储实际数据。如果想让视图不被修改，可以用with check option来完成限制。

create or replace view view_temp

as

    select * from temp

with check option;

 

修改视图：

alter view view_temp

as

    select id, name from temp;

 

删除视图：

drop view view_temp;

 

显示创建语法：

show create view v_temp;

 

Ø DML语句

DML主要针对数据库表对象的数据而言的，一般DML完成：

插入新数据

修改已添加的数据

删除不需要的数据

1、    insert into 插入语句

insert into temp values(null, ‘jack’, 25);

主键自增可以不插入，所以用null代替

 

指定列

insert into temp(name, age) values(‘jack’, 22);

在表面后面带括号，括号中写列名，values中写指定列名的值即可。当省略列名就表示插入全部数据，

注意插入值的顺序和列的顺序需要保持一致。

Set方式插入，也可以指定列

insert into temp set id = 7, name = 'jason';

 

MySQL中外键的table的外键引用列可以插入数据可以为null，不参照主表的数据。

 

使用子查询插入数据

insert into temp(name) select name from classes;

 

多行插入

insert into temp values(null, ‘jack’, 22), (null, ‘jackson’ 23);

 

2、    update 修改语句

update主要完成对数据的修改操作，可以修改一条或多条数据。修改多条或指定条件的数据，需要用where条件来完成。

修改所有数据

update temp set name = ‘jack2’;

所有的数据的name会被修改，如果修改多列用“,”分开

update temp set name = ‘jack’, age = 22;

修改指定条件的记录需要用where

update temp set name = ‘jack’ where age > 22;

 

3、    delete 删除语句

删除table中的数据，可以删除所有，带条件可以删除指定的记录。

删除所有数据

delete from temp;

删除指定条件数据

delete from temp where age > 20;

 

Ø select 查询、function 函数

select查询语句用得最广泛、功能也最丰富。可以完成单条记录、多条记录、单表、多表、子查询等。

1、    查询某张表所有数据

select * from temp;

*代表所有列，temp代表表名，不带条件就查询所有数据

 

2、    查询指定列和条件的数据

select name, age from temp where age = 22;

查询name和age这两列，age 等于22的数据。

 

3、    对查询的数据进行运算操作

select age + 2, age / 2, age – 2, age * 2 from temp where age – 2 > 22;

 

4、    concat函数，字符串连接

select concat(name, ‘-eco’) from temp;

concat和null进行连接，会导致连接后的数据成为null

 

5、    as 对列重命名

select name as ‘名称’ from temp;

as也可以省略不写，效果一样

如果重命名的列名出现特殊字符，如“‘”单引号，那就需要用双引号引在外面

select name as “名’称” from temp;

 

6、    也可以给table去别名

select t.name Name from temp as t;

 

7、    查询常量

类似于SQL Server

select 5 + 2;

select concat('a', 'bbb');

 

8、    distinct 去掉重复数据

select distinct id from temp;

多列将是组合的重复数据

select distinct id, age from temp;

 

9、    where 条件查询

大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<>

都可以出现在where语句中

select * from t where a > 2 or a >= 3 or a < 5 or a <= 6 or a = 7 or a <> 0;

 

10、    and 并且

select * from temp where age > 20 and name = ‘jack’;

查询名称等于jack并且年龄大于20的

 

11、    or 或者

满足一个即可

select * from tmep where name = ‘jack’ or name = ‘jackson’;

 

12、    between v and v2

大于等于v且小于等于v2

select * form temp where age between 20 and 25;

 

13、    in 查询

可以多个条件 类似于or

select * from temp where id in (1, 2, 3);

查询id在括号中出现的数据

 

14、    like 模糊查询

查询name以j开头的

select * from temp where name like ‘j%’;

 

查询name包含k的

select * from temp where name like ‘%k%’;

 

escape转义

select * from temp where name like ‘\_%’ escape ‘\’;

指定\为转义字符，上面的就可以查询name中包含“_”的数据

 

15、    is null、is not null

查询为null的数据

select * from temp where name is null;

查询不为null的数据

select * from temp where name is not null;

 

16、    not

select * from temp where not (age > 20);

取小于等于20的数据

select * from temp where id not in(1, 2);

 

17、    order by

排序，有desc、asc升序、降序

select * from temp order by id;

默认desc排序

select * from temp order by id asc;

多列组合

select * from temp order by id, age;

MySQL 学习笔记二

上一篇：http://www.cnblogs.com/hoojo/archive/2011/06/20/2085390.html

Ø function 函数

函数的作用比较大，一般多用在select查询语句和where条件语句之后。按照函数返回的结果，

可以分为：多行函数和单行函数；

所谓的单行函数就是将每条数据进行独立的计算，然后每条数据得到一条结果。

如：字符串函数；而多行函数，就是多条记录同时计算，得到最终只有一条结果记录。如：sum、avg等

多行函数也称为聚集函数、分组函数，主要用于完成一些统计功能。MySQL的单行函数有如下特征：

    单行函数的参数可以是变量、常量或数据列。单行函数可以接受多个参数，但返回一个值。

    单行函数就是它会对每一行单独起作用，每一行（可能包含多个参数）返回一个结果。

    单行函数可以改变参数的数据类型。单行函数支持嵌套使用：内层函数的返回值是外层函数的参数。

 

单行函数可以分为：

    类型转换函数；

    位函数；

    流程控制语句；

    加密解密函数；

    信息函数

单行函数

 

1、    char_length字符长度

select char_length(tel) from user;

 

2、    sin函数

select sin(age) from user;

select sin(1.57);

 

3、    添加日期函数

select date_add('2010-06-21', interval 2 month);

interval是一个关键字，2 month是2个月的意思，2是数值，month是单位

select addDate('2011-05-28', 2);

在前面的日期上加上后面的天数

 

4、    获取当前系统时间、日期

select curdate();

select curtime();

 

5、    加密函数

select md5('zhangsan');

 

6、    Null 处理函数

select ifnull(birthday, 'is null birthday') from user;

如果birthday为null，就返回后面的字符串

 

select nullif(age, 245) from user;

如果age等于245就返回null，不等就返回age

 

select isnull(birthday) from user;

判断birthday是否为null

 

select if(isnull(birthday), 'birthday is null', 'birthday not is null') from user;

如果birthday为null或是0就返回birthday is null，否则就返回birthday not is null；类似于三目运算符

 

7、    case 流程函数

case函数是一个流程控制函数，可以接受多个参数，但最终只会返回一个结果。

select name,

age,

(case sex

    when 1 then '男'

    when 0 then '女'

    else '火星人'

    end

) sex

from user;

 

组函数

组函数就是多行函数，组函数是完成一行或多行结果集的运算，最后返回一个结果，而不是每条记录返回一个结果。

1、    avg平均值运算

select avg(age) from user;

select avg(distinct age) from user;

 

2、    count 记录条数统计

select count(*), count(age), count(distinct age) from user;

 

3、    max 最大值

select max(age), max(distinct age) from user;

 

4、    min 最小值

select min(age), min(distinct age) from user;

 

5、    sum 求和、聚和

select sum(age), sum(distinct age) from user;

select sum(ifnull(age, 0)) from user;

 

6、    group by 分组

select count(*), sex from user group by sex;

select count(*) from user group by age;

select * from user group by sex, age;

 

7、    having进行条件过滤

不能在where子句中过滤组，where子句仅用于过滤行。过滤group by需要having

不能在where子句中用组函数，having中才能用组函数

select count(*) from user group by sex having sex <> 2;

 

Ø 多表查询和子查询

数据库的查询功能最为丰富，很多时候需要用到查询完成一些事物，而且不是单纯的对一个表进行操作。而是对多个表进行联合查询，

MySQL中多表连接查询有两种规范，较早的SQL92规范支持，如下几种表连接查询：

    等值连接

    非等值连接

    外连接

    广义笛卡尔积

SQL99规则提供了可读性更好的多表连接语法，并提供了更多类型的连接查询，SQL99支持如下几种多表连接查询：

    交叉连接

    自然连接

    使用using子句的连接

    使用on子句连接

    全部连接或者左右外连接

 

SQL92的连接查询

SQL92的连接查询语法比较简单，多将多个table放置在from关键字之后，多个table用“，”隔开；

连接的条件放在where条件之后，与查询条件直接用and逻辑运算符进行连接。如果条件中使用的是相等，

则称为等值连接，相反则称为非等值，如果没有任何条件则称为广义笛卡尔积。

广义笛卡尔积：select s.*, c.* from student s, classes c;

等值：select s.*, c.* from student s, classes c where s.cid = c.id;

非等值：select s.*, c.* from student s, classes c where s.cid <> c.id;

select s.*, c.name classes from classes c, student s where c.id = s.classes_id and s.name is not null;

 

SQL99连接查询

1、交叉连接cross join，类似于SQL92的笛卡尔积查询，无需条件。如：

select s.*, c.name from student s cross join classes c;

 

2、自然连接 natural join查询，无需条件，默认条件是将2个table中的相同字段作为连接条件，如果没有相同字段，查询的结果就是空。

select s.*, c.name from student s natural join classes c;

 

3、using子句连接查询：using的子句可以是一列或多列，显示的指定两个表中同名列作为连接条件。

如果用natural join的连接查询，会把所有的相同字段作为连接查询。而using可以指定相同列及个数。

select s.*, c.name from student s join classes c using(id);

 

4、    join … on连接查询，查询条件在on中完成，每个on语句只能指定一个条件。

select s.*, c.name from student s join classes c on s.classes_id = c.id;

 

5、    左右外连接：3种外连接，left [outer] join、right [outer] join，连接条件都是通过用on子句来指定，条件可以等值、非等值。

select s.*, c.name from student s left join classes c on s.classes_id = c.id;

select s.*, c.name from student s right join classes c on s.classes_id = c.id;

 

    子查询

    子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。子查询可以出现在2个位置：

    from关键字之后，被当做一个表来进行查询，这种用法被称为行内视图，因为该子查询的实质就是一个临时视图

    出现在where条件之后作为过滤条件的值

 

子查询注意点：

    子查询用括号括起来，特别情况下需要起一个临时名称

    子查询当做临时表时（在from之后的子查询），可以为该子查询起别名，尤其是要作为前缀来限定数据列名时

    子查询用作过滤条件时，将子查询放在比较运算符的右边，提供可读性

    子查询作为过滤条件时，单行子查询使用单行运算符，多行子查询用多行运算符

 

将from后面的子查询当做一个table来用：

select * from (select id, name from classes) s where s.id in (1, 2);

当做条件来用：

select * from student s where s.classes_id in (select id from classes);

select * from student s where s.classes_id = any (select id from classes);

select * from student s where s.classes_id > any (select id from classes);

Ø 操作符和函数

1、    boolean只判断

select 1 is true, 0 is false, null is unknown;

select 1 is not unknown, 0 is not unknown, null is not unknown;

 

2、    coalesce函数，返回第一个非null的值

select coalesce(null, 1);

select coalesce(1, 1);

select coalesce(null, 1);

select coalesce(null, null);

 

3、    当有2个或多个参数时，返回最大的那个参数值

select greatest(2, 3);

select greatest(2, 3, 1, 9, 55, 23);

select greatest('D', 'A', 'B');

 

4、    Least函数，返回最小值，如果有null就返回null值

select least(2, 0);

select least(2, 0, null);

select least(2, 10, 22.2, 35.1, 1.1);

 

5、    控制流函数

select case 1 when 1 then 'is 1' when 2 then 'is 2' else 'none' end;

select case when 1 > 2 then 'yes' else 'no' end;

 

6、    ascii字符串函数

select ascii('A');

select ascii('1');

 

7、    二进制函数

select bin(22);

 

8、    返回二进制字符串长度

select bit_length(11);

 

9、    char将值转换成字符，小数取整四舍五入

select char(65);

select char(65.4);

select char(65.5);

select char(65.6);

select char(65, 66, 67.4, 68.5, 69.6, '55.5', '97.3');

 

10、    using改变字符集

select charset(char(0*65)), charset(char(0*65 using utf8));

 

11、    得到字符长度char_length,character_length

select char_length('abc');

select character_length('eft');

 

12、    compress压缩字符串、uncompress解压缩

select compress('abcedf');

select uncompress(compress('abcedf'));

 

13、    concat_ws分隔字符串

select concat_ws('#', 'first', 'second', 'last');

select concat_ws('#', 'first', 'second', null, 'last');

Ø 事务处理

动作

    开始事务：start transaction

    提交事务：commit

    回滚事务：rollback

    设置自动提交：set autocommit 1 | 0

    atuoCommit系统默认是1立即提交模式；如果要手动控制事务，需要设置set autoCommit 0;

    这样我们就可以用commit、rollback来控制事务了。

 

在一段语句块中禁用autocommit 而不是set autocommit

start transaction;

select @result := avg(age) from temp;

update temp set age = @result where id = 2;

select * from temp where id = 2;//值被改变

rollback;//回滚

select * from temp where id = 2;//变回来了

在此期间只有遇到commit、rollback，start Transaction的禁用autocommit才会结束。然后就恢复到原来的autocommit模式；

 

不能回滚的语句

    有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，

    和创建、取消或更改表或存储的子程序的语句。

    您在设计事务时，不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句，

    则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能 回滚事务的全部效果。

 

一些操作也会隐式的提交事务

如alter、create、drop、rename table、lock table、set autocommit、start transaction、truncate table 等等,

在事务中出现这些语句也会提交事务的

    事务不能嵌套事务

    事务的保存点

Savepoint pointName/Rollback to savepoint pointName

一个事务可以设置多个保存点，rollback可以回滚到指定的保存点，恢复保存点后面的操作。

如果有后面的保存点和前面的同名，则删除前面的保存点。

Release savepoint会删除一个保存点，如果在一段事务中执行commit或rollback，则事务结束，所以保存点删除。

 

 Set Transaction设计数据库隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

本语句用于设置事务隔离等级，用于下一个事务，或者用于当前会话。

在默认情况下，SET TRANSACTION会为下一个事务（还未开始）设置隔离等级。

如果您使用GLOBAL关键词，则语句会设置全局性的默认事务等级，

用于从该点以后创建的所有新连接。原有的连接不受影响。使用SESSION关键测可以设置默认事务等级，

用于对当前连接执行的所有将来事务。

默认的等级是REPEATABLE READ全局隔离等级。

 

Ø 注释

select 1+1;     # 单行注释

select 1+1;     -- 单行注释

select 1 /* 多行注释 */ + 1;

Ø 基本数据类型操作

字符串

    select 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';

    select "hello", "'hello'", "''hello''", "hel""lo", "\"hello";

 

\n换行

    select 'This\nIs\nFour\nLines';

 

\转义

    select 'hello \ world!';

    select 'hello \world!';

    select 'hello \\ world!';

    select 'hello \' world!';

Ø 设置数据库mode模式

SET sql_mode='ANSI_QUOTES';

create table t(a int);

create table "tt"(a int);

create table "t""t"(a int);

craate talbe tab("a""b" int);

Ø 用户变量

set @num1 = 0, @num2 = 2, @result = 0;

select @result := (@num1 := 5) + @num2 := 3, @num1, @num2, @result;

Ø 存储过程

创建存储过程：

delimiter //

create procedure get(out result int)

begin

 select max(age) into result from temp;

end//

调用存储过程：

call get(@temp);

查询结果：

select @temp;

 

删除存储过程：

drop procedure get;

 

查看存储过程创建语句：

show create procedure get;

 

select…into 可以完成单行记录的赋值：

create procedure getRecord(sid int)

begin

    declare v_name varchar(20) default 'jason';

    declare v_age int;

    declare v_sex bit;

    select name, age, sex into v_name, v_age, v_sex from temp where id = sid;

    select v_name, v_age, v_sex;

end;

call getRecord(1);

Ø 函数

函数类似于存储过程，只是调用方式不同

例如：select max(age) from temp;

 

创建函数：

create function addAge(age int) returns int

     return age + 5;

 

使用函数：

select addAge(age) from temp;

 

删除函数：

drop function if exists addAge;

drop function addAge;

 

显示创建语法：

show create function addAge;

Ø 游标

声明游标：declare cur_Name cursor for select name from temp;

打开游标：open cur_Name;

Fetch游标：fetch cur_Name into @temp;

关闭游标：close cur_Name;

 

示例：

CREATE PROCEDURE cur_show()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE v_id, v_age INT;

  DECLARE v_name varchar(20);

  DECLARE cur_temp CURSOR FOR SELECT id, name, age FROM temp;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

 

  OPEN cur_temp;

 

  REPEAT

    FETCH cur_temp INTO v_id, v_name, v_age;

    IF NOT done THEN

       IF isnull(v_name) THEN

          update temp set name = concat('test-json', v_id) where id = v_id;

       ELSEIF isnull(v_age) THEN

          update temp set age = 22 where id = v_id;

       END IF;

    END IF;

  UNTIL done END REPEAT;

 

  CLOSE cur_temp;

END

Ø 触发器

触发器分为insert、update、delete三种触发器事件类型

还有after、before触发时间

创建触发器：

create trigger trg_temp_ins

before insert

on temp for each row

begin

insert into temp_log values(NEW.id, NEW.name);

end//

 

删除触发器：

drop trigger trg_temp_ins

MySql like 查询变向写法（不用like 完成like查询）

?

select * from account where userName like 'ad%';

select * from account where userName >= 'ad' and userName < 'ae'

这两种查询的结果是一样的，效率好像也差不多，没有做具体的效率测试，有兴趣可以测试下效率。

like查询中的ad%是查询ad开头userName的数据，

而userName >= 'ad'就是查询ad开头的数据并且还包含 ae、af、ag……，也就是说是查询“ad”中包含d且大于“ad”中d的数据

所以，and userName < 'ad'就保证查询的区间在“ad”中的，而ae、af、ag……这些数据就不会出现在结果集中。

当然你可以试试：

select * from account where userName >= 'ad' and userName < 'az';

结果集是不是包含：ae、af、ag……ak、al……ay、az等开头的数据。