mysql再回首

Mysql与Oracle的区别

1.实例区别

　　Mysql是一个轻量型数据库，开源免费。Oracle是收费的而且价格非常高。

　　Mysql一个实例可以操作多个库，而Oracle一个实例只能对应一个库。

　　Mysql安装完后300M而Oracle有3G左右。

2.操作区别

　　主键：Mysql一般使用自动增长类型，而Oracle则需要使用序列对象。

　　单引号的处理：mysql里可以用双引号包起字符串，oracle里只可以用单引号包起字符串。

　　分页的sql语句：mysql用limit，而oracle使用内建视图和rownum伪列。

　　事务处理：mysql默认是自动提交，而oracle默认不自动提交，需要用户CTL语言进行事务提交。

 

操作Mysql

mysql和oracle对于数据物理隔离机制上的区别：

　　oracle装好后会有一个oracle实例还有一个库，库当中有数据文件，这数据文件在oracle中称为表空间。

　　　　所以在Oracle装好以后，我们首先要去创建一个永久表空间，再去创建用户。随后把这个永久表空间分配给这个用户。

　　　　接着再去创建一个用户，再给他分配一个表空间。通过表空间来实现物理隔离。

　　　　所以说在oracle中库有一个就够了，然后我们再给他创建表空间。

 

　　mysql是一个实例可以对应多个库，mysql当中呢没有表空间这个概念，所以说我们可以去创建不同的库，然后用户直接去操作不同的库。每个库中放着不同的数据文件。

 

1.创建与删除数据库

 

1.1.1使用命令创建数据库

create database 数据库名 default  character set字符编码；

1.1.1.1示例

创建一个test的数据库，并查看该数据库，以及该数据库的编码。

　　  create database  test default  character set utf-8;

创建数据库:

　　create  database 库名;

查看数据库

show databases;

查看数据库编码:

select  schema_name  ,default_character_set_name  from information_schema.schemata where schema_name='test';

 

1.2删除数据库

　drop database 数据库名;

1.2.1.1示例

drop database test;

 

2.选择数据库

需要在哪个库中创建表需要先选择该数据库。

use 需要选择的库名;

2.1示例一

创建一个名称为test的数据库，编码为utf-8;

　　 create database test  default character set utf8; 

2.2示例二

选择该数据库；

 use test;

3.Mysql中的数据类型

3.1数值类型

Mysql支持所有准备sql数值数据类型。

作为sql标准的扩展,Mysql也支持整数类型tinyint、mediumint和bigint。

 

Mysql数据类型                          含义

tinyint(m)　　　　　　　　　　一个字符 范围(-128- 127)

smallint(m)　　　　　　　　　2个字节 范围(-32768-  32767)

mediumint(m)　　　　　　　　3个字节 范围(-8388608-  8388687)

int(m)　　　　　　　　　　　　4个字节 范围(-2147483648-  2147483647)

bigint(m)　　　　　　　　　　　8个字节 范围(+-9.22*10的18次方)

 

数值类型中的长度m是指显示长度，并不显示存储长度，只有字段指定指定zerofill时有用

例如: int(3),如果实际值是2，如果列指定了zerofill,查询结果就是002,左边用0来填充。

3.2浮点型

float(m,d)　　　　　　 单精度浮点型  8位精度(4个字节)  m总个数，d小数位

double(m,d)　　　　　　双精度浮点型  16位精度(8位) m总个数,d个小数位

、

3.3字符串型

char(n)  固定长度,最多255个字符

varchar(n)  可变长度，最多65535个字符

tinytext  可变长度，最多255个字符

text　　可变长度，最多65535个字符

mediumtext  可变长度，最多2的24次方-1个字符

longtext　　　　可变长度，最多2的32次方-1个字符

 

 

3.3.1char和varchar

1.char(n)若存入字符数小于n,则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限制于此。

2.char类型的字符串检索要比varchar类型快。

 

3.3.2varchar和text

1.varchar可以指定n,text不能指定，内部存储varchar是存入的实际字符数+1个字节(n<=255),text是实际字符数+2个字节。

2.text类型不能有默认值。

3.varchar可直接创建索引,text创建索引要指定前多个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。

 

3.4日期类型

 　　mysql数据类型                       含义

　　date                                        日期2008-12-2

　　time　　　　　　　　　　　　时间‘12：25：36’

　　datetime　　　　　　　　　　日期时间‘2008-12-2 22：06：44’

　　timestamp　　　　　　　　　　自动存储记录修改时间

 

3.5二进制数据

 

1.BLOB和TEXT存储方式不同，text以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。

2.BLOB存储的数据只能整体读出。

3.text可以指定字符集，BLOB不同指定字符集。

 

DDL语言

4.创建表与删除表

4.1创建表

　　create table employees(employee_id int,last_name varchar(30),salary float(8,3))

4.2查看表

　　show tables;

4.3删除表

　　　drop table employees;

 

5.修改表

5.1使用ddl语句修改表名

 

  alter table 旧表名    rename 新表名

5.1.1示例一

     将employees表名修改为emp。

　　alter table employees rename emp;

5.2修改列名

  alter 表名  change  column  旧列名  新列名  类型

5.2.1示例

将emp表中的last_name 修改为name

alter table   employees   change  column  last_name  name varchar(30)

 

5.3使用ddl来修改列类型

alter table 表名 modifity 列名 新类型

5.3.1示例

将emp当中的name长度指定为49；

alter table employees  MODIFY  name varchar(40)；

5.4使用ddl语句来添加列

alter table 表名 add column 新列名 类型

5.4.1示例

在emp表中添加一个新的lie为commission_pct

alter table employees add column commission_pct float(4,2)

 

5.5使用ddl来删除列

alter table 表名 drop  column  列名

 

5.5.1示例

删除emp表中的commission_pct

alter table emp drop column commsission_pct;

 

查询表的约束信息

show keys from table;

 

6.Mysql中的约束

6.1约束类型

• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key)
• 外键约束(foreign key)
• 检查约束(目前Mysql不支持、Oracle支持)

6.2创建表时添加约束

查询表中的约束信息

show keys from 表名

 

6.2.2示例二

创建employees表包含employees _id该列为主键且自动增长，last_name列不允许含有空值,email列不允许有重复不允许有空值，dept_id为外键参照departments表的主键。

create table employees(

employees_id int primary key auto_increment,

last_name varchar(30) not null,

email varhcar(40) not null unique,

dept_id int,

constraint emp_fk foreign key(dept_id)referenes departments(department_id);

)

6.3约束的添加和删除

6.3.1主键约束

 

6.3.1.1添加主键约束

alter table 表名 add primarykey(列名)

 

6.3.1.1.1示例

将emp表中的employee_id修改为主键自动增长

添加主键：alter table emp add primary key(employee_id);

添加自动增长:alter table emp modify_id auto_increment;

 

6.3.1.2删除主键约束

alter table 表名 drop  primary key

注意:删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。

 

例子：

删除employee_Id的主键约束。

去掉自动增长：alter table emp modify employee_id int;

 删除主键：alter table emp drop primary key;

 

6.3.2非空约束

6.3.2.1添加非空约束

alter  table 表名 modify 列名 类型 not null;

 

6.3.2.1.1示例

向emp表中的salary添加非空约束

alter table emp  modify salary  float(8,2) not null,

6.3.2.2删除非空约束

alter table 表名 modify 列名 类型 null

 

6.3.2.3添加唯一性约束

向emp表中的name添加唯一约束

alter table  add constraint emp_uk unique(name);

6.3.3.2删除唯一性约束

alter table 表名 drop key 表名。

alter table emp drop key emp_uk;

6.3.4外键约束

alter table 表名 add constraint  约束名 foreign key(列名)

refrences 参照的表名(操作的列名)

 

alter table  add constraint e_fk foreign key(dept_id) refrences departments(department_Id);

 

6.3.4.2删除外键约束

删除外键：

alter table 表名 drop foreign key 约束名

删除外键索引(索引名与约束名同名)

alter table表名 drop index 索引名。

 

6.3.4.2.1示例

删除dept_id的外键约束

删除外键: alter table emp drop foreign key e_fk;

删除索引: alter table emp drop  index  e_fk;

 

7mysql中DML操作

 

7.1添加数据(insert )

7.1.1插入数据

7.1.1.1选择插入

insert into 表名(列名1，列名2....)values(值1，值2，值3...);

7.1.1.2完全插入

insert into 表名 values(值1，值2，值3.....)

7.1.1.3插入多条记录

insert into 表名 (...)values

(值1，值2，值3.....),

(值1，值2，值3.....),

(值1，值2，值3.....);

 

Mysql中的自动增长类型要求

一个表中只能有一个列为自动增长。

自动增长的列的类型必须是整数类型。

自动增长只能添加到具备主键约束与唯一性约束的列上。

删除主键约束或者唯一约束，如果该列拥有自动增长能力，则需要去掉自动增长然后删除约束。

 

Create table emp2(id int primary key ,name varchar(30),seq_num int unique auto_increment);

默认值

在MySQL中可以使用default为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么MySQL会将默认值添加到该列中。

 

7.1.3.1创建表时指定列的默认值

create table emp3(emp_id int  primary key auto_increment ,name varhcar(30),address varchar(50) default 'unknown');

跟新

update 表名 set 列名=值,列名=值 where 条件

 

mysql的update的特点

跟新的表不能在set和where中用于子查询；

update后面可以做任意的查询。

 

跟新emp3中id为2的数据，将地址修改为id为1用户相同

　Oracle:　　　　　update emp3 e set e.address=(select  address from emp3 where emp_id=1)where e.emp_id=2;

　mysql:　update emp3 e,(select address from emp3 where emp_id=1)t set  e.address=t where e.emp_id=2;

方式二：

update emp3 e set e.address=(select t1.address from(select  * from emp3) t1 where t1.emp_id=1)

 

7.3删除数据(DELETE)

7.3.1使用delete子句

 delete from 表名 where 条件

7.3.1.1示例

删除emp3表中emp_id为1的雇员信息。

7.3.2使用truncate清空表

truncate  table 表名

7.3.2.1示例

删除emp3表中的所有数据

truncate table emp3;

7.3.3delete与truncate区别

• truncate是整体删除(速度较快)，delete是逐条删除(速度较慢)；
• truncate不写服务器log,delete写服务器log，也就是truncate效率比delete高的原因;
• truncate是会重置自增值，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的值。而delete删除以后,自增值仍然会继续累加。

8.MySQL中事务处理

在mysql中默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事务，并且提交了事务。

8.1关闭MSQL的事务自动提交

 

start transaction                  (此后的数据需要自己手动提交)

DML.....

commit|rollback

8.1.1示例

向emp3表中添加一条数据，要求手动提交事务。

 

六.Mysql查询数据

1.1Mysql的列选择

select *|投影列from 表名

1.1.1示例

查询所有

select * from departments;

1.2Mysql的行选择

select *|投影列 from 表名 where 选择条件。

 

select department_name,location_Id from departments where department_id=4;

 

1.3Mysql语句中的算数表达式

 

+:加法运算

-：减法运算

*：乘法运算

/：除法运算，返回商

%:求余运算，返回余数。

示例一

修改employees表添加salary。

  alter table employees add column salary float(9,2);

示例二

select  employees_id,last_name,email,12*salary from employees;

 

3.Mysql中常见的单行函数

3.1大小写控制函数

LOWER(str)    转换大小写混合的字符串为小写

UPPER(str)    转换大小写混合的字符串为大写

3.2字符处理

CONCAT(str1,str2)  将str1、str2等字符串连接起来

SUBSTR(str,pos,len)  从str的第pos位(范围：1-str.length)开始，截取长度为len的字符串

length(str)  获取str的长度

instr(str,substr)

Lpad(str,len,padstr) 获取substr在str中的位置

trim(str)  从str中删除开头和结尾的空格(不会处理字符串中间含有的空格)

Ltrim(str)  从str中删除左侧开头的空格

Rtrim(str) 从str中删除右侧结尾的空格

 

REPLACE(str,from_str,to_str) 将str中的from_str替换为to_str(会替换掉所有符合from_str的字符串)

 

3.3数字函数

ROUND(arg1,arg2):四舍五入指定小数的值

ROUND(arg1):四舍五入保留整数

TRUNC(arg1,arg2):截断指定小数的值，不做四舍五入。

MOD(arg1,arg2)：取余

 

3.4日期函数

SYSDATE()或者NOW()    返回当前系统时间，格式为YYYY-MM-DD-hh-mm-ss

CURDATE()   返回系统当前日期，不返回时间

CURTIME()   返回当前系统中的时间，不返回日期

DAYOFMONTH(date)  计算日期 d是本月的第几天。

 DAYOFWEEK(date)   日期d今天是星期几    1星期日   

dayofyear(date)

dayname(date)

LAST_DAY(date)      返回date日期当月的最后一天。

 

3.5转换函数

date_format(date,format)  将日期转换成字符串(类似oracle中的to_char())

str_to_date(str,format)   将字符串转换成日期(类似oralce中的to_date())

format的格式都列出来：

    %M 月名字(January……December) 
    %W 星期名字(Sunday……Saturday) 
    %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
    %Y 年, 数字, 4 位 
    %y 年, 数字, 2 位 
    %a 缩写的星期名字(Sun……Sat) 
    %d 月份中的天数, 数字(00……31) 
    %e 月份中的天数, 数字(0……31) 
    %m 月, 数字(01……12) 
    %c 月, 数字(1……12) 
    %b 缩写的月份名字(Jan……Dec) 
    %j 一年中的天数(001……366) 
    %H 小时(00……23) 
    %k 小时(0……23) 
    %h 小时(01……12) 
    %I 小时(01……12) 
    %l 小时(1……12) 
    %i 分钟, 数字(00……59) 
    %r 时间,12 小时(hh:mm:ss [AP]M) 
    %T 时间,24 小时(hh:mm:ss) 
    %S 秒(00……59) 
    %s 秒(00……59) 
    %p AM或PM 
    %w 一个星期中的天数(0=Sunday ……6=Saturday ） 
    %U 星期(0……52), 这里星期天是星期的第一天 
    %u 星期(0……52), 这里星期一是星期的第一天 
    %% 字符% )

https://www.jb51.net/article/135803.htm

 

 

select date_format(sysdate,'%Y年%月%d日')

select str_to_date('2019年03月23日','%Y年%m月%d日');

3.6示例一

 

insert into empoyess values(default,'King','King@sxt.cn',190000,0.6,str_to_date('2018年5月1日','%Y年%m月%d日'))

 

3.9通用函数

ifnull(expr1,expr2)

if(expr1,expr2,expr3)

coalesce(value...)判断value的值是否为null，如果不为null，则返回value；如果为空，则判断下一个value是否为空..直到出现不为空的value并返回或者返回最后一个为null的value。

 

4.多表连接查询

4.1等值连接

示例

查看雇员king所在部门名称

select  department_name from  employees e,departments d where e.dept=d.department_id and e.last_name='king'

4.2非等值连接

4.2.1示例一

创建sal_level表，包含lowest_sal,highest_sal ,level.

 

create  table sal_level(lowest_sal int ,highest_sal int  ,level VARCHAR(30));

 

插入多条数据

insert into sal_level  values(1000,2999,'A')

 

 

select  e.last_name  from employee e,sal_level s where e.salary between s.lowest_sal and highest_sal;

 

select emp.last_name  from employees emp ,employees man where emp.manager_id=man.employees_id;

 

SQL99标准中的查询

Mysql5.7支持SQL99标准。

6.1SQL99中的交叉连接(cross join)

 

6.1.1示例

使用交叉连接查询employees表与department表

select  * from employees cross join departments

 

 

6.2SQL99中的自然连接(natural join)

使用自然连接查询所有部门的雇员的名字以及部门名称。

select  e.last_name,d.department_name from employees natural join departments  d where e.last_name='oldlu';

若两个表有多个列相同，则都做连接条件。

6.3SQL99的内连接(inner join)

6.3.1示例

查询雇员名字为oldlu的雇员id，薪水与部门名称。

　　select  e.employees_id,e.salary,d.department_name from employees e inner join departments d on e.department_id=d.department_id where e.last_name='Oldlu';

7.聚合函数

7.1AVG(arg)函数

 

对分组数据做平均值运算

arg:参数类型只能是数字类型

 

select  avg(e.salary) from employees e;

7.2SUM(arg)函数

对分组数据求和

arg:参数类型只能是数字类型

 

select  sum(salary) from employees;

7.3MIN(arg)函数

求分组中最小数据。

arg:参数类型可以是字符、数字、日期

select imn(salary) from employees;

7.4MAX(arg)函数

 

求分组中最大的数据。

arg:参数类型可以是字符、数字、日期。

 

7.5COUNT函数

返回一个表中的行数

 

COUNT  函数有三种格式：

count(*)

count(expr)

count(distinct  expr)

 

8.数据分组(group by)

8.1.1示例

计算每个部门的平均薪水

select   avg(e.salary) from employees  e group by e.department_id;

8.2约束分组结果(having)

显示那些最高薪水大于5000的部门的部门号和最高薪水。

select e.department_id,max(e.salary) from employees e group by e.department_id having max(e.salary)>5000;

 

9.子查询

可以将子查询放在许多的sql子句中，包括：

• where子句
• having 子句
• from子句

9.1使用子查询的原则

• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中庸单行运算符，在多行子查询中用多行运算符。

9.1.1示例

谁的薪水比oldru高

  select  em.last_name ,em.salary from empoyees em where em.salary>(select e .salary from employees e where e.last_name='Oldlu');

 

9.2子查询中的单行运算符

=        等于

>       大于

>=      大于或等于

<         小于

<=     小于或者等于

<>        不等于

9.2.1示例

查询oldlu的同事，但是不包含他自己。

　　select empl.last_name from employees empl

where empl.department_id=

(select  e.department_id from employees e where e.last_name='oldru')

and empl.last_name<>'Oldlu';

9.3多行子查询

　　操作                     含义

　　in                       等于列表中的任何成员

　　any                     比较子查询返回的每个值

　　all                        比较子查询返回的全部值

 

示例：

查找各个部门收入最低的那些雇员。显示他们的名字，薪水以及部门id。

select  em.last_name ,em.salary,em.department_Id from employees em where em.salary in(select min(e.salary) from employees group by e.department_id);

10Mysql中的正则表达式

 mysql中允许使用正则表达式定义字符串搜索条件，性能高于like。

mysql中的正则表达式可以对整数类型或者字符类型检索。

使用REGEXP关键字表示正则匹配。

默认忽略大小写，如果要区分大小写，使用BINARY关键字

 

10.1正则表达式的模式及含义

 

模式	什么模式匹配
^	字符串的开始
$	字符串的结尾
.	任何单个字符
[...]	在方括号内的任何字符列表
[^...]	非列在方括号内的任何字符
p1|p2|p3	交替匹配任何模式p1,p2或者p3
*	零个或者多个前面的元素
+	前面的元素的一个或多个实例
{n}	前面的元素的n个实例
{m,n}	m到n个实例前面的元素

10.2  ^符号

^在正则表达式中表示开始

 

10.2.1语法

查询以x开头的数据(忽略大小写)

select 列名  from 表名 where 列名  REGEXP '^X';

 

10.2.2示例

查询雇员表中名字以k开头的雇员名字与薪水

• select  name ,salary from emp3 where name regexp BINARY '^k';

10.3'$'符号

10.3.1语法

查询以x结尾的数据(忽略大小写)

select 列名 from 表名 where 列名 REGEXP 'x$';

 

10.3.2示例

查询雇员表中名字以n结尾的雇员名字与薪水。

select   last_name ,salary from employees where last_name REGEXP binary 'n$';

 

10.4'.'符号

10.4.1语法

英文的点，它匹配任何一个字符，包括回车、换行等。

 

select 列名 from 表名 where 列名REGEXP 'x';

 

10.4.2示例

查询雇员表中名字含有o的 雇员的姓名与薪水。

 

select  last_name,salary  from employees where last_name REGEXP'O.';

10.5“*”符号

10.5.1语法

“*”:星号匹配0个或者多个字符，在它之前必须有内容。

10.6“+”符号

10.6.1语法

“+”：加号匹配1个或者多个字符，在它之前也必须有内容。

select  列名 from  表名  where 列名 REGEXP 'x+';  匹配大于1个的任意字符。

 

10.7“？”符号

“？”：问号匹配0次或者1次

select 列名 from 表名 where 列名 REGEXP 'x?';     匹配0个或者1个字符

 

10.8“|”符号

"|":表示或者含义

select  列名 from 表名 where 列名 REGEXP ‘abc|bcd’ ;     匹配包含abc或者bcd

 

10.8.2示例

查询雇员表中名字含有ke或者lu的雇员的名字与薪水。

select last_name,salary form employees where last_name REGEXP'ke|lu';

 

10.9"[a-z]"

 

10.9.1语法

“[a-z]”:字符范围

“^[...]”:以什么字符开头的

"[^...]":匹配不包括在[]的字符

select 列名 from 表名 where 列名 REGEXP '[a-z]';  匹配内容包含a-z范围的数据。

 

10.9.2示例一

查询雇员表中名字包含x、y、z字符的雇员的名字和薪水。

　　select last_name ,salary from employees where last_name regexp '[x-z]';

　　select last_name ,salary from employees where last_name regexp 'x|y|z';

10.9.3示例二

查询雇员名字是t、f开头的雇员名字与薪水。

select last_name ,salary from employees where last_name regexp '^[t|f]';

10.9.3示例三

查询雇员的名字与薪水,不包括oldlu.

 

select last_name ,salary from employees where last_name regexp '[^oldlu]';

 

10.10"{n}"

10.10.1语法

“{n}”:固定次数

select * from student where name REGEXP's{2}';----匹配以s连续出现2次的所有的数据

 

10.10.2示例一

　　　　查询雇员名字含有连续两个e的雇员的姓名与薪水

　　　　select  last_name,salary from employees where last_name REGEXP'e{2}';

10.10.3示例二

　　　　查询名字含有两个o的雇员的名字与薪水。

　　　　select  last_name,salary from employees where last_name REGEXP'o.{2}';

 

10.11"{n,m}"

10.11.1语法

“{n,m}":范围次数

select * from student where name REGEXP '^s{2,5}';---匹配以s开头且重复2到5次的所有数据

10.11.2示例

查询雇员名字中包含1个或者两个o的雇员姓名与薪水。

select  last_name ,salary from employees where last_name REGEXP 'o.{1,2}';

 

普通索引

是最基本的索引，它没有任何限制。

在创建索引时，可以指定索引长度。length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是BLOB和TEXT类型，必须指定length。

创建索引时需要注意：

　　如果指定单列索引长度，length必须小于这个字段所允许的最大字符个数。

 

查询索引： show index from  table_name;

 

1.2.1直接创建索引

 create index  index_name on table(column(length))

1.2.1.1示例

为emp3表中的name创建一个索引，索引名为emp3_name_index;

   create index emp3_index  on emp3(name);

 

 

1.2.2修改表添加索引

alter table  table_name  add index index_name (column(length))

 

1.2.2.1示例

修改emp3表，为address列添加索引，索引名为emp3_address_index

alter table emp3 add index emp3_address_index(address)

 

1.2.3创建表时指定索引列

　　create table 'table'(

　　　　column type,

　　　　primary key(id);

　　　　index index_name(column(length))

)

 

1.2.3.1示例

创建emp4表，包含emp_id,name,address列，同时为name列创建索引。索引名为emp4_name_index

 

　　create table emp4(

　　　　emp_id int primary key auto_increment,

　　　　name  varchar(30),

　　　　address varchar(50),

　　　　index  emp4_name(name)

)

 

1.2.4删除索引

drop index inde_name  on table

 

 1.2.4.1示例

删除mep3表中索引名为emp3_address_index的索引。

  drop index  emp3_address_index on table;

 

 

1.3唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但是允许有空值。它有以下几种创建方式。

 

1.3.1创建唯一索引

create unique index  indexname  on table(column(length))

1.3.1.1示例

为emp表中的name创建一个唯一索引，索引名为emp_name_index

create unique index emp_name_index on emp(name);

 

1.3.2修改表添加唯一索引

alter table table_name add unique indexName(column(length))

1.3.2.1示例

修改emp表,为address列添加唯一索引，索引名为emp_address_index

alter table emp add unique emp_salary(salary);

1.3.3创建表时指定唯一索引

 create table table(

　　column type,

　　primary key(id),

　　unique index_name(column(length))

)

1.4主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

 

1.4.1修改表添加主键索引

alter table 表名 add  primary key(列名)

 

 

1.4.1.1示例

修改emp表为employee_id添加主键索引

alter table emp add primary key(employee_id)

1.4.2创建表时指定主键索引

 

 

 

1.5组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)

 

1.5.1最左前缀原则

就是最左优先。

如:我们使用表中的name,address,salary创建组合索引，那么想要组合索引生效，我们只能使用如下组合：

name/address/salary

name/address

name/

如果使用address/salary或者是salary则索引不会生效。

1.5.2修改添加组合索引

alter  table table_name add  index index_name(column(length),column(length))

 

1.5.2.1示例

修改emp6表，为name，address列创建组合索引

 

alter table emp6 add index  emp6_index_n_a (name,address);

1.5.3创建表时创建组合索引

　　create table table(

　　　　column type,

　　　　index index_name(column(length),column(length))

)

 

1.5.3.1示例

创建emp7表，包含emp_id,name,address列，同时为name,address列创建组合索引。

 

create table emp7(emp_id int  primary key auto_increment ,name varchar(20),address varchar(30),index emp_index7_n_a(name,address))

 

1.6全文索引

　　全文索引(FULLTEXT  INDEX)主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其他索引不大相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against 操作使用，而不是一般的where语句加like。

　　全文索引可以从char、varchar或者text列中作为create table语句的一部分被创建，或是随后使用alter table 添加。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

　　1.6.1修改添加全文索引

　　alter table table_name add fulltext index_content(content)

　　1.6.1.1示例一

　　修改emp7表添加content列类型为text

　　alter table emp7 add column contemt text;

　　1.6.1.2示例二

　　修改emp7，为content列创建全文索引

　　alter table emp7 add fulltext emp_content_fullindex(content)

　　1.6.2创建表时创建全文索引。

　　　　create table(

　　　　 column type,

　　　　fulltext index_name(column)

　　)

1.6.2.1示例

　　创建emp8包含emp_id列，content列该列类型为text，并为该列添加名为emp8_content_fulltext的全文索引。

　　　　create table emp8(emp_Id int primary key  auto_increment,

　　　　　　　　　　content text ,

　　　　　　　　　　fulltext emp8_content_fullindex(content))

　　1.6.3删除全文索引

　　 drop index index_name  on table

　　 

　　alter table table_name drop index index_name;

 

1.6.3示例

删除emp8表中名为emp8_content_full的索引

drop index   emp8_cotent_fullindex on emp8

 

1.7使用全文索引

全文索引的使用与其他索引不同。在查询语句中需要使用match(column)against('content')来检索数据。

 

1.7.1全文解析器

全文索引中基本单位是“词”。分词，全文索引是以词为基础的，mysql默认的分词是所有非字母和数字的特殊符号都是分词符。在检索数据我们给定的检索条件也是词。

 mysql中默认的全文解析器不支持中文分词。如果数据含有中文需要更换全文解析器NGRAM。

 

1.7.2使用全文索引

select 投影列 from表名 where match(全文列名) against('搜索内容')

 

示例二

向emp8表中插入一条数据content的值为"hello,bjsxt";

insert into emp8 values(default,"hello bjsxt");

示例三

查询emp8表中内容包含bjsxt的数据

select * from emp8 where match(content)AGAINST("bjsxt");

 

1.7.3更换全文解析器

在创建全文索引时可以指定ngram解析器

alter table table_name add fulltext index_content(content) with parser ngram 

 

1.7.3.1示例一

删除emp8表中的emp8_content_full全文索引

drop index emp8_content_Full on emp8

 

1.7.3.2示例二

修改emp8表，为content列添加名称emp8_content_full的全文索引,并指定ngram全文解析器。

　　alter table emp8 add fulltext emp8_content_full(content) with parser ngram

1.7.3.3示例三

向emp8表中添加一条数据content 值为“ 你好,诗圣杜甫”

　　insert into emp8  values(default,'你好，诗圣杜甫');

1.7.3.4示例四

查询emp8表中内容包含“诗圣杜甫”

select  * from emp8  where match(content) against('诗圣杜甫');

 

mysql分页查询

mysql分页查询原则

• 在mysql数据库中使用limit子句进行分页查询。
• mysql分页中开始位置为0.
• 分页子句查询语句的最后侧。

1.limit子句

1.1语法格式

select 投影列from  表名 where  条件 order by limit  开始位置，查询数量。

1.1.1示例

查询雇员表中所有数据按id排序，实现分页查询，每次返回两条结果。

 select * from employees order by employees_id limit 0,2;

2.limit offset子句

 2.1语法格式

select  投影列 from  表名 where 条件 ordfer by limit  查询数量 offset 开始位置。

2.1.1示例

查询雇员

select * from employees order by employee_id limit 2 offset  0；

 

mysql中的执行计划

1.mysql执行计划

在mysql中可以通过explain关键字模拟优化器执行sql语句，从而知道mysql是如何处理sql语句的。

explain select * from employees;

2.mysql整个查询执行过程

• 客户端向mysql服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器进行sql解析、预处理、再由优化器生成对应的执行计划。
• mysql根据执行计划，在调用存储引擎的api来执行查询。
• 将结果返回给客户端，同时缓存查询结果。

3.启动执行计划

 

https://www.cnblogs.com/clphp/p/6256207.html

mysql操作查询结果case when then else end用法举例

 

Case具有两种格式。简单Case函数和Case搜索函数。 
--简单Case函数 
CASE sex 
         WHEN '1' THEN '男' 
         WHEN '2' THEN '女' 
ELSE '其他' END 
--Case搜索函数 
CASE WHEN sex = '1' THEN '男' 
         WHEN sex = '2' THEN '女' 
ELSE '其他' END 

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。 
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 

--比如说，下面这段SQL，你永远无法得到“第二类”这个结果 
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类' 
         WHEN col_1 IN ('a')       THEN '第二类' 
ELSE'其他' END 
下面我们来看一下，使用Case函数都能做些什么事情。 

一，已知数据按照另外一种方式进行分组，分析。 

有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key) 
国家（country） 人口（population） 
中国 600 
美国 100 
加拿大 100 
英国 200 
法国 300 
日本 250 
德国 200 
墨西哥 50 
印度 250 

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。 
洲 人口 
亚洲 1100 
北美洲 250 
其他 700 

想要解决这个问题，你会怎么做？生成一个带有洲Code的View，是一个解决方法，但是这样很难动态的改变统计的方式。 
如果使用Case函数，SQL代码如下: 
SELECT  SUM(population), 
        CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
        ELSE '其他' END 
FROM    Table_A 
GROUP BY CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
        ELSE '其他' END; 

同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下； 

SELECT 
        CASE WHEN salary <= 500 THEN '1' 
             WHEN salary > 500 AND salary <= 600  THEN '2' 
             WHEN salary > 600 AND salary <= 800  THEN '3' 
             WHEN salary > 800 AND salary <= 1000 THEN '4' 
        ELSE NULL END salary_class, 
        COUNT(*) 
FROM    Table_A 
GROUP BY 
        CASE WHEN salary <= 500 THEN '1' 
             WHEN salary > 500 AND salary <= 600  THEN '2' 
             WHEN salary > 600 AND salary <= 800  THEN '3' 
             WHEN salary > 800 AND salary <= 1000 THEN '4' 
        ELSE NULL END; 

二，用一个SQL语句完成不同条件的分组。 

有如下数据 
国家（country） 性别（sex） 人口（population） 
中国 1 340 
中国 2 260 
美国 1 45 
美国 2 55 
加拿大 1 51 
加拿大 2 49 
英国 1 40 
英国 2 60 

按照国家和性别进行分组，得出结果如下 
国家 男 女 
中国 340 260 
美国 45 55 
加拿大 51 49 
英国 40 60 

普通情况下，用UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个Select部分)，而且SQL语句会比较长。 
下面是一个是用Case函数来完成这个功能的例子 

SELECT country, 
       SUM( CASE WHEN sex = '1' THEN 
                      population ELSE 0 END),  --男性人口 
       SUM( CASE WHEN sex = '2' THEN 
                      population ELSE 0 END)   --女性人口 
FROM  Table_A 
GROUP BY country; 

这样我们使用Select，完成对二维表的输出形式，充分显示了Case函数的强大。 

三，在Check中使用Case函数。 

在Check中使用Case函数在很多情况下都是非常不错的解决方法。可能有很多人根本就不用Check，那么我建议你在看过下面的例子之后也尝试一下在SQL中使用Check。 
下面我们来举个例子 
公司A，这个公司有个规定，女职员的工资必须高于1000块。如果用Check和Case来表现的话，如下所示 
CONSTRAINT check_salary CHECK 
           ( CASE WHEN sex = '2' 
                  THEN CASE WHEN salary > 1000 
                        THEN 1 ELSE 0 END 
                  ELSE 1 END = 1 ) 

如果单纯使用Check，如下所示 

CONSTRAINT check_salary CHECK 
           ( sex = '2' AND salary > 1000 ) 

女职员的条件倒是符合了，男职员就无法输入了

 

四，根据条件有选择的UPDATE。 

例，有如下更新条件 
工资5000以上的职员，工资减少10% 
工资在2000到4600之间的职员，工资增加15% 
很容易考虑的是选择执行两次UPDATE语句，如下所示 

--条件1 
UPDATE Personnel 
SET salary = salary * 0.9 
WHERE salary >= 5000; 
--条件2 
UPDATE Personnel 
SET salary = salary * 1.15 
WHERE salary >= 2000 AND salary < 4600; 

但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。如果要是反过来执行，那么工资4600的人相反会变成减少工资。暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下: 

UPDATE Personnel 
SET salary = CASE WHEN salary >= 5000 
　            THEN salary * 0.9 
WHEN salary >= 2000 AND salary < 4600 
THEN salary * 1.15 
ELSE salary END; 

这里要注意一点，最后一行的ELSE salary是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。 
这种方法还可以在很多地方使用，比如说变更主键这种累活。 
一般情况下，要想把两条数据的Primary key,a和b交换，需要经过临时存储，拷贝，读回数据的三个过程，要是使用Case函数的话，一切都变得简单多了。 
p_key col_1 col_2 
a 1 张三 
b 2 李四 
c 3 王五 


假设有如上数据，需要把主键a和b相互交换。用Case函数来实现的话，代码如下 

UPDATE SomeTable 
SET p_key = CASE WHEN p_key = 'a' 
THEN 'b' 
WHEN p_key = 'b' 
THEN 'a' 
ELSE p_key END 
WHERE p_key IN ('a', 'b'); 

同样的也可以交换两个Unique key。需要注意的是，如果有需要交换主键的情况发生，多半是当初对这个表的设计进行得不够到位，建议检查表的设计是否妥当。 

五，两个表数据是否一致的检查。 

Case函数不同于DECODE函数。在Case函数中，可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS，可以进行子查询，从而 实现更多的功能。 
下面具个例子来说明，有两个表，tbl_A,tbl_B，两个表中都有keyCol列。现在我们对两个表进行比较，tbl_A中的keyCol列的数据如果在tbl_B的keyCol列的数据中可以找到， 返回结果'Matched',如果没有找到，返回结果'Unmatched'。 
要实现下面这个功能，可以使用下面两条语句 

--使用IN的时候 
SELECT keyCol, 
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) 
THEN 'Matched' 
ELSE 'Unmatched' END Label 
FROM tbl_A; 
--使用EXISTS的时候 
SELECT keyCol, 
CASE WHEN EXISTS ( SELECT * FROM tbl_B 
WHERE tbl_A.keyCol = tbl_B.keyCol ) 
THEN 'Matched' 
ELSE 'Unmatched' END Label 
FROM tbl_A; 

使用IN和EXISTS的结果是相同的。也可以使用NOT IN和NOT EXISTS，但是这个时候要注意NULL的情况。 

六，在Case函数中使用合计函数 

假设有下面一个表 
学号(std_id) 课程ID(class_id) 课程名(class_name) 主修flag（main_class_flg) 
100 1 经济学 Y 
100 2 历史学 N 
200 2 历史学 N 
200 3 考古学 Y 
200 4 计算机 N 
300 4 计算机 N 
400 5 化学 N 
500 6 数学 N 

有的学生选择了同时修几门课程(100,200)也有的学生只选择了一门课程(300,400,500)。选修多门课程的学生，要选择一门课程作为主修，主修flag里面写入 Y。只选择一门课程的学生，主修flag为N(实际上要是写入Y的话，就没有下面的麻烦事了，为了举例子，还请多多包含)。 
现在我们要按照下面两个条件对这个表进行查询 
只选修一门课程的人，返回那门课程的ID 
选修多门课程的人，返回所选的主课程ID 

简单的想法就是，执行两条不同的SQL语句进行查询。 
条件1 

--条件1：只选择了一门课程的学生 
SELECT std_id, MAX(class_id) AS main_class 
FROM Studentclass 
GROUP BY std_id 
HAVING COUNT(*) = 1; 

执行结果1 

STD_ID   MAIN_class 
------   ---------- 
300      4 
400      5 
500      6 

条件2 

--条件2：选择多门课程的学生 
SELECT std_id, class_id AS main_class 
FROM Studentclass 
WHERE main_class_flg = 'Y' ; 

执行结果2 

STD_ID  MAIN_class 
------  ---------- 
100     1 
200     3 

如果使用Case函数，我们只要一条SQL语句就可以解决问题，具体如下所示 

SELECT  std_id, 
CASE WHEN COUNT(*) = 1  --只选择一门课程的学生的情况 
THEN MAX(class_id) 
ELSE MAX(CASE WHEN main_class_flg = 'Y' 
THEN class_id 
ELSE NULL END 
) 
END AS main_class 
FROM Studentclass 
GROUP BY std_id; 

运行结果 

STD_ID   MAIN_class 
------   ---------- 
100      1 
200      3 
300      4 
400      5 
500      6 

通过在Case函数中嵌套Case函数，在合计函数中使用Case函数等方法，我们可以轻松的解决这个问题。使用Case函数给我们带来了更大的自由度。 
最后提醒一下使用Case函数的新手注意不要犯下面的错误 

CASE col_1 
WHEN 1    　   THEN 'Right' 
WHEN NULL  THEN 'Wrong' 
END 

在这个语句中When Null这一行总是返回unknown，所以永远不会出现Wrong的情况。因为这句可以替换成WHEN col_1 = NULL，这是一个错误的用法，这个时候我们应该选择用WHEN col_1 IS NULL。

 

 

 

 

 

-----------------

举例1：

使用该查询，得出iFavoriteID,iFavUserType ,cUser,iArticleID,dFavoriteTime五个字段的值:

SELECT iFavoriteID,
CASE WHEN iFavUserType = 0 THEN '新闻管理员'
WHEN iFavUserType = 1 THEN '商家'
WHEN iFavUserType = 2 THEN '会员'
WHEN iFavUserType = 3 THEN '未注册'
WHEN iFavUserType = 4 then '匿名'
END AS iFavUserType, cUser, iArticleID,
CONVERT(nvarchar(100), dFavoriteTime, 111) AS dFavoriteTime FROM dig_favorite

举例2：

SELECT CASE WHEN `MEMBERTYPE` =1
THEN '参赛队员'
ELSE '指导老师'
END FROM `tab_sign_member`
WHERE 1

 

 

--------------------------------------------

下面为您举例说明了三种mysql中case when语句的使用方法，供您参考学习，如果您对mysql中case when语句使用方面感兴趣的话，不妨一看。

1。

select name,
 case
        when birthday<'1981' then 'old'
        when birthday>'1988' then 'yong'
        else 'ok' END YORN
from lee;

2。

select NAME,
 case name
     when 'sam' then 'yong'
        when 'lee' then 'handsome'
        else 'good' end
from lee;

当然了case when语句还可以复合

3。

select name,birthday,
 case
     when birthday>'1983' then 'yong'
        when name='lee' then 'handsome'
        else 'just so so ' end
from lee;