mysql查询最近一个自然周_第五周：2019-06-25笔记

/*复习*/

/*创建表的操作*/

create table emp_bak(e_id int,e_name varchar(50),e_salary double(8,2),e_deptid int);

/*查看表*/

desc emp_bk;

show keys from emp_bk;

select * from emp_bk;

/*修改表操作*/

alter table emp_bak rename emp_bk; #修改表名

alter table emp_bk change column e_deptid dept_id int; #修改列名,需要加上列名类型

alter table emp_bk modify dept_id int; #修改列的数据类型

alter table emp_bk add comm double(6,2); #添加列

alter table emp_bk drop comm; #删除列

/*增加与删除约束的操作*/

alter table emp_bk add primary key(e_id); #添加主键约束

alter table emp_bk modify e_id int auto_increment; #添加自动增长机制

alter table emp_bk modify e_id int; #删除自动增长

alter table emp_bk drop primary key; #删除主键约束

alter table emp_bk add constraint emp_pk primary key(e_id); #添加主键约束,并自定义约束名

alter table emp_bk modify e_name varchar(50) not null; #添加非空约束

alter table emp_bk modify e_name varchar(50) null; #删除非空约束

alter table emp_bk add constraint emp_uk unique(e_name); #添加唯一性约束

alter table emp_bk drop key emp_uk; #删除唯一性约束

alter table emp_bk add constraint emp_fk foreign key(dept_id) references departments(department_id); #添加外键约束

alter table emp_bk drop foreign key emp_fk; #删除外键约束

alter table emp_bk drop index emp_fk; #删除外键索引

/*添加数据操作*/

insert into emp_bk values(null,'bjsxt',3000,1); #通过null占位符进行完全列插入

insert into emp_bk values(0,'shsxt',3000,2); #通过0占位符进行完全列插入

insert into emp_bk values(default,'gzsxt',3000,3); #通过default占位符进行完全列插入

DML操作

1.添加数据

格式：insert into 表名(列名1,列名2...) values (值1,值2...)

1)可选插入

示例：insert into departments(department_name,location_id) values ("market",1);

2)完全插入

注意：如果某列是自动增长类型则对于该列的值可用default或者null或者0进行占位操作

示例一：insert into departments values(null,"development",2); //使用null占位

示例二：insert into departments values(default,"human",3); //使用default占位

示例三：insert into departments values(0,"teaching",4); //使用0占位

2.默认值处理

在MySQL中也使用default为字段设定一个默认值。如果在插入数据时并未指定该列的值则库会将

默认值添加到该列

1)创建表时指定默认值

示例：create table emp2 (e_id int default 10);

2)修改表时指定默认值

示例：alter table emp2 modify seq_num int default 10;

示例：insert into emp2 values (default); //使用default占位

3.更新数据

与Oracle不同的是MySQL不能在set和where中做更新表的查询操作，但可在update后做更新表

的查询操作

格式：update 表名 set 列名=值，列名=值 where 条件；

示例：update emp2 set address="BeiJing" where id=1; //简单修改

示例：update emp2 e,(select address from emp2 where id=1) e1 set e.address=e1.address where e.id=2; //复杂修改，方式一

示例：update emp2 set address=(select e.address from (select address from emp2 where id=1) e) where id=3; //方式二

4.删除数据

格式：delete from 表名 where 条件；

示例：delete from emp2 where id=1; //from关键词必须加

5.截断表

格式：truncate table 表名；

示例：truncate table emp2;

truncate与delete删数据的区别：

1)truncate是整体删除(效率高)，delete是逐条删除

2)truncate不写server log，delete写server log

3)truncate会重置自增值，delete删除行后自增值仍然会继续累加

MySQL中的事务处理

在MySQL中默认情况下，事务是自动提交的，且为一行一提交

1.手动提交

可在执行DML操作之前加上start transaction，其后的DML操作需要使用commit来提交，或者使

用rollback来回滚

MySQL的基本查询

1.列选择

格式：select *|投影列 from 表名

2.行选择

格式：select *|投影列 from 表名 where 条件

3.算术表达式

支持+ - * / %

4.对空值的处理

包含空值的算术表达式或连字操作结果为空

5.列别名

格式：select 列名 (AS) 列别名 from 表名 where 条件

6.连字符

MySQL中只能使用concat函数来做连接操作符

7.去除重复

使用distinct关键字来去重

8.比较运算符

=, >, >= ,< ,<= ,<> or !=

示例一：select last_name,salary from employees where salary>=3000;

示例二：select last_name,salary from employees where salary>=5000;

9.模糊查询

使用like关键字；与Oracle不同的是%表示任意多个任意字符，_表示一个任意字符(包含了%和_)

示例：select last_name,salary from employees where last_name like "_e%";

10.逻辑运算符

and

示例：select last_name,salary from employees where salary=5000 and last_name like "%d%";

or

not

示例：select last_name,salary from employees where last_name not like "%u%";

11.范围查询

between...and... 表示在一个连续的范围内

示例：select last_name,salary from employees where salary between 3000 and 8000;

in 表示在一个非连续的范围内

示例：select last_name,salary from employees where salary in(5000,6000,8000);

12.空值判断

is null

is not null

13.排序

使用order by关键字进行排序操作

示例：select last_name,salary from employees order by salary; //升序排序

示例：select last_name,salary from employees order by salary desc; //降序排序

MySQL中常见的单行函数

1.大小写控制函数

注意：与Oracle不同的是无initcap函数

lower 转换大小写混合的字符串为小写字符串

upper 转换大小写混合的字符串为大写字符串

2.字符处理函数

concat(str1,str2...) 将str1,str2等字符串连接起来

substr(str,pos,len) 从str的第pos位开始截取，截取长度为len

length(str) 获取str的长度

instr(str,substr) 获取substr在str中的位置

lpad/rpad(str,len,padstr) 左右填充

trim(str) 从str中删除开头和结尾的空格

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

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

replace(str,from_str,to_str) 将str中的from_str替换为to_str

3.数字函数

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

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

mod(arg1,arg2) 取余操作

4.日期函数

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

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

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

dayofmonth(date) 计算日期date是本月的第几天

dayofweek(date) 计算日期date是数字形式的周几(1-7)

dayofyear(date) 返回指定年份的天数

dayname(date) 返回日期date是周几

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

5.转换函数

date_format(date,format) 将日期格式化成字符串

示例：select date_format(sysdate(),"%Y年%m月%d日");

str_to_date(str,format) 将字符串转换为日期

示例：select str_to_date("2019年06月25日","%Y年%m月%d日");

以下是常用的一些表示符号：

%a 缩写星期名

%b 缩写月名

%D 带有英文前缀的月中的天

%d 月的天，数值(00-31)

%e 月的天，数值(0-31)

%Y 4位的年

%y 2位的年

%m 数值月(00-12)

%p AM或PM

%s 秒(00-59)

%H 小时(00-23)

%h 小时(01-12)

举例：

1)向employees表中添加hire_date类型为date类型

示例：alter table employees add hire_date date;

2)向employees表中添加一条数据，名字:King，email:king@sxt.cn,部门id为1，薪水9000，入职时间2018年5月1日

示例：insert into employees values (default,"King","king@sxt.cn",1,9000,str_to_date("2018年5月1日","%Y年%m月%d日"));

3)查询employees表中雇员名字为King的雇员的入职日期，要求显示格式为 年月日

示例：select date_format(hire_date,"%Y年%m月%d日") from employees where last_name="King";

6.通用函数

ifnull(expr1,expr2) 判断expr1是否为空，为空则使用expr2代替expr1

nullif(expr1,expr2) 判断expr1是否等于expr2，相等返回null，不等返回expr1

if(expr1,expr2,expr3) 判断expr1是否为空，为空则返回expr2，不为空则返回expr3

coalesce(expr1,expr2...) 判断expr1是否为空，不为空则返回，为空则继续判断expr2以此类推

case when then else end 条件判断

MySQL中的多表查询

1.等值连接

示例：select e.last_name,d.department_name from employees e,departments d where e.dept_id=d.department_id and e.last_name="King";

2.非等值连接

示例：select e.last_name,e.salary,s.level from employees e,sal_level s where e.salary between s.lowest and s.highest_sal;

3.自连接

举例：

1)修改employees表，添加manager_id列

示例：alter table employees add manager_id int;

2)修改数据King为所有人的经理

示例：update employees set manager_id=13 where last_name<>"king";

3)查询每个雇员的经理的名字以及雇员的名字

示例：select e1.last_name,e2.last_name from employees e1,employees e2 where e1.manager_id=e2.employee_id;

4.左外连接

示例：select e.last_name,d.department_name from employees e left join departments d on e.dept_id=d.department_id;

5.右外连接

示例：select e.last_name,d.department_name from employees e right join departments d on e.dept_id=d.department_id;

6.全外连接

在MySQL中不支持使用full join来实现全外连接，而使用union关键字；

该关键字可将两个查询结果集合并，返回行都是唯一的(在返回行之前做了去除重复项的操作)

格式：(左外连接的查询)union(右外连接的条件)

示例：select e.last_name,d.department_name from employees e right join departments d on e.dept_id=d.department_id union select e.last_name,d.department_name from employees e left join departments d on e.dept_id=d.department_id;

7.交叉连接

示例：select * from employee,department;

8.自然连接

示例：select e.last_name,d.department_name from employees e natural join departments d；

9.内连接

示例：select e.last_name,e.salary,d.department_name from employees e inner join departments d using(department_id) where e.last_name='King';

MySQL中的聚合函数

1.avg函数

2.sum函数

3.min函数

4.max函数

5.count函数

MySQL中的数据分组

1.使用group by关键字进行分组

示例：select avg(salary) from employees group by department_id;

2.使用having关键字对分组之后的数据进行条件的筛选

示例：select department_id,max(salary) from employees group by department_id having max(salary)>10000;

MySQL中的子查询

子查询可以出现在where,from,having语句后面

示例：select last_name,salary from employees where salary>(select salary from employees where last_name="Crazy");

示例：select last_name,department_id from employees where department_id=(select department_id from employees where last_name="Crazy") and last_name<>"Crazy";

MySQL中的正则表达式

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

2)MySQL中的正则表达式可以对整数类型或者字符类型进行检索

3)使用regexp关键字表示正则匹配

4)默认忽略大小写，如果要区分大小写，则使用binary关键字

正则表达式的语法：select 列名 from 表名 where 列名 regexp "正则表达式"

正则表达式的含义

^表示字符串的开始

举例：查询雇员表中名字以c开头的雇员信息

示例：select * from employees where last_name regexp "^c";

$字符串的结尾

举例：查询雇员表中名字以e结尾的雇员信息

示例：select * from employees where last_name regexp "e$";

.任何单个字符

举例：查询雇员表中名字包含i的雇员信息

示例：select * from employees where last_name regexp "i.";

[a-z]字符范围

举例：查询雇员表中名字包含x,y,z字符的雇员信息

示例：select * from employees where last_name regexp "[x-z]";

^[...]以什么字符开头

举例：查询雇员表中名字是k或d开头的雇员信息

示例：select * from employees where last_name regexp "^[k|d]";

[^...]匹配不包含在[]的字符

举例：查询雇员表中名字不包含King的其他人的雇员信息

示例：select * from employees where last_name regexp "[^King]";

p1|p2|p3交替匹配任何模式p1,p2或p3

举例：查询雇员表中名字含有k或者d的雇员信息

示例：select * from employees where last_name regexp "d|k";

*零个或多个前面的元素

+前面的元素的一个或多个实例

?匹配0次或1次

{n}匹配n个实例前面的元素

举例：查询雇员表中名字含有连续两个e的雇员信息

示例：select * from employees where last_name regexp "e{2}";

举例：查询雇员表中名字含有两个e的雇员信息(e可不连续)

示例：select * from employees where last_name regexp "e.{2}";

{m,n}m到n个实例前面的元素

举例：查询查询雇员表中名字含有一个或两个e的雇员信息

实例：select * from employees where last_name regexp "e.{1,2}";

查询索引

show index from 表名

MySQL中的索引类型

1.普通索引

是最基本的索引，它没有任何限制，可对表中的任意列创建普通索引；

在创建索引时，可以指定索引长度。length为可选参数，表示索引的长度，只有字符串类型的字段

才能指定索引长度，如果是BLOB和TEXT类型，必须指定length

创建索引时需要注意：如果指定单列索引长度，length必须小于这个字段所允许的最大字符个数

1)创建普通索引的三种方式：

---->直接创建索引

格式：create index 索引名 on 表名 (column(length))

示例：create index employees_lastnameindex on employees(last_name(200));

---->修改表添加索引

格式：alter table 表名 add index 索引名 (column(length));

示例：alter table employees add index employees_lastnameindex (last_name(200));

---->创建表时指定索引列

格式：create table 表名 (列名 类型，index 索引名 (column(length)))

示例：create table sal_level01(level varchar(30),index level_index (level(25)));

2)删除索引

格式一：alter table 表名 drop index 索引名；

示例：alter table employees drop index employees_lastnameindex;

格式二：drop index 索引名 on 表名；

示例：drop index employees_lastnameindex on employees；

2.唯一索引(对具有唯一约束的列的索引)

唯一索引与普通索引类似，不同之处在于索引列的值必须唯一，但允许有空值

1)创建唯一索引的方式

---->直接创建索引

格式：create unique index 索引名 on 表名 (column(length))

示例：create unique index email_index on employees (email(100));

---->修改表添加索引

格式：alter table 表名 add unique 索引名 (column(length));

示例：alter table employees add unique email_index (email(100));

---->创建表时指定索引列

格式：create table 表名 (列名 类型,unique 索引名 (column(length)))

示例：create table emp5 (emp_id int,name varchar(30),address varchar(30),unique name_index (name(25)));

注意：当在表中指定唯一索引后该列会自动具有唯一约束

2)删除索引

格式一：alter table 表名 drop index 索引名；

示例：alter table employees drop index email_index;

格式二：drop index 索引名 on 表名；

示例：drop index email_index on employees；

3.主键索引

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

创建主键索引

4.组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引

才会被使用(最左前缀原则)

注意：注意复合索引在where条件中的前后顺序，如果最左列的顺序在其他列的后面则不走索引

---->直接创建索引

格式：create index 索引名 on 表名(column1(length),column2(length)...)

示例：create index name_address_index on emp5(name,address);

---->修改表时添加索引

格式：alter table 表名 add index 索引名 (cloumn1(length),cloumn2(length)...)

示例：alter table emp5 add index name_address_index (name,address);

---->创建表时指定索引列

格式：create table 表名 (列名 类型,index 索引名 (cloumn1(length),cloumn2(length)...))

示例：create table emp5 (emp_id int,name varchar(30),address varchar(30),index name_address_index (name,address));

2)删除索引

格式一：alter table 表名 drop index 索引名；

示例：alter table employees drop index name_address_index;

格式二：drop index 索引名 on 表名；

示例：drop index name_address_index on employees；

5.全文索引(对大文本的不完全值匹配，优点类似like搜索)

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引与其他索引大

不相同，它更像是一个搜索引擎，而非简单的where语句的参数匹配，全文索引配合match against

操作使用，而不是一般的where语句加like

全文索引可以从char,varchar或text列中作为create table语句的一部分被创建，或是随后使用alter

table添加。不过对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的方式

6.全文索引的使用

全文索引的使用与其他索引不同，在查询语句中需要使用match(cloumn) against("内容")来检索

数据；内容使用了分词技术，也就是说一个单词为一个词，必须匹配到一个单词或者多个单词才能

匹配到内容

1)全文索引的使用

格式：select 列名 from 表名 where match(列名) against("内容")

2)更换全文解析器

格式：alter table 表名 add fulltext 索引名 (列名) with parser ngram; //换成中文全文解析器