MySQL基础

本文示例基于crashcourse数据库,文中如有错误请谅解!
  


MySQL入门

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#查看数据库
show databases;
#选择一个数据库
use 数据库名;
#创建数据库
create database 数据库名;
#查看创建数据库的SQL语言
show create database 数据库名;
#查看表
show tables;
#创建表
create table student(id int(11) not null,name char(20));
#查看表结构
desc student;
#查看创建表语句
show create table student;
#插入数据
insert into student(id,name) values(1001,’zhangsan’);
#查询数据
select * from student;
#删除数据
delete from student where id=1001;
#删除表
drop table student;
#删除数据库
drop database mydb;
#查看mysql服务器状态信息
show status;
#查看用户权限
show grants;
#查看服务器错误信息
show errors;
#查看服务器警告信息
show warnings;
#修改数据库保护模式
set SQL_SAFE_UPDATES =0;

数据定义语句(DDL)

数据表

1
2
3
4
5
6
7
Create table customers
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name char(50) not null default china,
cust_email char(50) null,
primary key(cust_id)
)engine = InnoDB;#引擎为InnoDB

注:

  • auto_incrment:自动增量,每个表只允许一个auto_increment,可通过select last_insert_id()获取该值。
  • 指定默认值:default
  • 更新表:alter table
    1
    2
    3
    4
    5
    6
    7
    8
    Alter table vendors ADD vend_phone char(20);
    Alter table vendors drop column vend_phone;
    Alter table 表名modify 列名 新类型 新参数;
    Alter table 表名 change 旧列名 新列名 新类型 新参数;
    Alter table 表名drop 列名;
    alter table products ADD CONSTRAINT fk_products_vendors
    Foreign key (vend_id) references vendors(vend_id);#更新外键
    rename table 旧表名 to 新表名;#重命名

视图

1
2
3
4
5
Create view viewname AS 查询语句;
Select * from viewname;#可以查看视图
Show create view viewname;#查询创建视图语句
Drop view viewname;#删除视图
Update view set 列名=‘数据’ where条件;

存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
use crashcourse;
delimiter // #改变分隔符(除\之外都可以作为分隔符)
create procedure p5(out n5 decimal(8,2),in in_t char(1))#创建存储过程#in传入out传出
begin
if in_t = 'Y' then
select avg(prod_price)into n5 from products;
set @p=n5; #设置变量p
else
set n5 = 100;
end if;
end //
delimiter ;
call p5(@n5,'n');
select @n5;
select @p;
drop procedure p5;
show warnings;
select * from products;
delimiter //
create procedure p3(in n int,in j char(1))
begin
if j = 'h' then
select * from products where prod_price<n;
else
select * from products where prod_price>n;
end if;
end//
## drop procedure p3//
delimiter ;
call p3(5,'f');#查看存储过程结果
drop procedure p3;
delimiter //
create procedure p4(in n int)
begin
declare i int;
declare j int;
set i = 1;
set j = 0;
while i <=n do
set i = j+i;
set i = i+1;
end while;
select j;
end//
delimiter ;
call p4(100);
drop procedure p4;
show procedure status where db='crashcourse';#查看数据库的存储过程
show errors;
drop procedure p3;

游标(cursor,只能用于存储过程中,有开启就必须关闭)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Declare cursorname cursor for 查询语句;
use crashcourse;
create table p_test(sid varchar(20));
deLIMITER //
create procedure products()
begin
declare a varchar(16);
declare done INt default false;#default 设置默认值
declare cur cursor for select prod_id from products;#定义游标cur,语句为cursor for 查询语句
declare continue handler for not found set done = true;
open cur;
read_loop:loop#定义循环
fetch cur into a;#fetch 检索cur给变量a
if done then#判断语句
leave read_loop;#结束循环
end if;#结束判断语句
insert into p_test(sid) values(a);#插入a变量值到p_test表
end loop;
close cur;
end//
delimiter ;
call products();
select * from p_test;
drop procedure products;
drop table p_test;
show procedure status where db=’crashcources’;#查看存储过程
delete from p_test;#清空表
show warnings;

触发器(对表操作)

触发器响应的活动:insert、delete、update。before /after.每个表最多支持6个触发器。

1
2
3
4
Set @result = null;#定义变量,非触发器格式
Create trigger tg after insert on products for each now selete ‘added’into @result;#红色部分为触发器,for each row 每行
Show triggers;#查看触发器
Drop trigger tg;#删除触发器

Insert触发器:可引用new的虚拟表,访问被插入的行;
Before insert触发器中,new中的值可以被更新。
Create tigger neworder after insert on orders for each row select new.order_num into @result;
Delete触发器:可引用old表。

练习:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
use crashcourse;
set @result=null;
create trigger tg after insert on s5 for each row selete ‘added’ into @result;
select * from s5;
insert into s5 values(11);
select @result;
drop trigger tg;
show triggers;
use crashcourse;
set @result=null;
create trigger tg before insert on s5 for each row selete new.price into @result;#引用new表
select * from s5;
insert into s5 values(11);
select @result;
drop trigger tg;
show triggers;
use crashcourse;
set @result=null;
create trigger tg before delete on s5 for each row selete old.price into @result;#引用old表
select * from s5;
insert into s5 values(11);
delete from s5 where price=6;#删除数据
select @result;
drop trigger tg;
show triggers;


数据检索语句(DQL)

select检索数据

  • distinct——去除重复行,显示不重复的行。
    select distinct 列名 from 表名;
  • limit——显示制定行数。
    select 列名 from limit n; #显示前n行
    select 列名 from limit n,m; #从第n行开始的m行(从0开始的)
  • order by——结果排序。(ASCII码)
    select 列名 from 表名 order by 列名;
    select 列名 from 表名 order by 列名1,列名2;
  • ASC——升序,降序——DESC,关键字排序只用于其前面的列名排序。
    注:limit用于order by之后。

    分组查询与子查询

  • 创建分组:
    select vend_id,COUNT(*) AS num_prods from products GROUP BY vend_id with rollup;
    注:
    后面加上with rollup,可以汇总分组数据;
    group by 在where之后,order by 之前;
  • 过滤分组:
    select vend_id,count(*) as num_prods from products where prod_price>=10 group by vend_id having count(*)>=2;
    注:having 针对分组结果,where针对列值。
  • 子查询:in操作符,zi子查询常用于where子句的in操作符中,以及用来填充计算列。
    select cust_id from orders where order_num IN (select ordre_num from orderitems where prod_id =’TNT2’);
    select cust_name,cust_state,(select count(*) from orders where order.cust_id = customers.cust_id) AS orders from customers order by cust_name;

    联结表

  • 创建联结
    Select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id order by vend_nam,prod_name;
    笛卡尔积:去除联结条件,检索出的行数目为第一个表的行数乘以第二个表的行数。
  • 内联结(等值联结):INNER JOIN ON
    SELECT vend_name,prod_name,prod_price from vendors INNOR JOIN products ON vendors.vend_id = products.vend_id;
  • 多表联结(表关联的越多,性能下降越厉害)

    1
    2
    3
    4
    5
    Select prod_name,vend_name,prod_price,quantity
    From orderitems,products,vendors
    Where products.vend_id = vendors.vend_id
    AND orderitems.prod_id = products.prod_id
    AND order_num = 20005;
  • 表别名

    1
    2
    3
    4
    5
    Select cust_name,cust_contact
    From customers AS c,orders AS O,orderitems AS oi
    Where c.cust_id = o.cust_id
    AND oou.order_num = o.order_num
    AND prod_id = ‘TNT2’;
  • 自联结(自联结执行效率高于子查询)

    1
    2
    SECECT prod_id,prod_name from products where vend_id = (select vend_id from products where prod_id = ‘DTNTR’;
    Select p1.prod_id,p1.prod_name from products AS p1,products AS p2 where p1.vend_ud = p2.vend_id AND p2.prod_id = ‘DTNTR’;
  • 外联结(左联结、右联结)
    左联结:
    SELECT customers.cus_id,orders.order_num from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.order.cust_id;
    右联结:
    SELECT * from student RIGHT JOIN course ON student_id = course.student_id;

  • 联合查询:UNION /UNION ALL(显示重复)
    注:查询条件列要一样,数据类型可以兼容,order by语句针对最终结果,不是对最后一个查询语句排序,

数据操作语句(DML)

insert插入数据

  • 插入完整行
    insert into 表名(列名)values(各个列的值);#不需要按列名顺序插入
    insert into 表名 values(各个列的值);#不给列名,按列名顺序插入
  • 插入行的一部分
  • 插入多行(逗号隔开)
    insert into 表名 values(各个列的值),(各个列的值);
  • 插入查询得到的数据(插入时,是按照列进行插入,不是按照列名插入)
    insert into 表A(列1,列2) select 列1,列2 from表B;

update更新数据

update customers set cust_email = ‘elmer@fudd.com’ where cust_id = 1005;
注:更新多行数据用逗号‘,‘隔开


delete删除数据

  • delete
    delete from customers where cust_id =10006;#删除表中的行
    delete from customers;#清空表
    注:如果需要快速清空表,则采用truncate 语句,该方法比delete删除快;
    Truncate table 表名;

#条件查询和通配符 #
select 列名 from 表名 where 列满足的条件;
注:同时使用order by 和where 字句时,order by 应位于where 之后,否则报错

  • 操作符
操作符 含义
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between 介于
  • and操作符
    select 列名 from 表名 where 条件1 and 条件2;
  • or操作符(或)
    select 表名 from 表名 where 条件1 or 条件2;
    注:and优先级高于or,圆括号优先级高于and,优先执行圆括号内的条件
    同时出现and/or时,加括号语句更加严谨。
  • in操作符(指定条件范围)
    select 列 from 表 where 列 in (value1,value2,…);
  • not操作符
    select * from crashcourse.products where vend_id not in(1002,1003);
    注:mysql中支持not对in、between、exists子句取反
  • like操作符
    %通配符——不能匹配null)
    select 列 from 表 where prod_name LIKE ‘jet%’;
    select 列 from 表 where prod_name LIKE ‘%anvil$’;
    (_通配符——匹配单个字符)
    select 列 from 表 where prod_name like ‘_ ton anvil’;
  • exists操作符
    select * from tableA Where exists(Select * From tableB Where tableB.ID=tableA.ID);
    注:exists返回一个布尔值,为真则输出该语句结果,为假则不输出结果。
    in操作符时候查询内小外大的表,exists操作符适合查询内大外小的表。In确定给定的值是否与子查询或列表中的值相匹配。exists Exists指定一个子查询,检测行的存在,因此一般用*代替所有列。

数据控制语言(DCL)

事务操作

原子性、一致性、隔离性、持久性,只有InnoDB引擎支持事务操作。

  • rollback(回退)

    1
    2
    3
    start transaction;
    insert、update、delete语句;
    rollback;
  • commit(提交)

    1
    2
    set autocommit = 0;#禁止自动提交
    set autocommit = 1;#启动自动提交
  • savepoint(保留点)

    1
    2
    savepoint delete1;#使用保留点
    rollback delete1;#回退到保留点

练习:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
use crashcourse;
select * from s5;
start transaction;
delete from s5;
select * from s5;
rollback;
select * from s5;
create table s6(sid varchar(10),sanme varchar(20));
set autocommit =1;
insert into s6 values(‘1001’,’zhangsan’);
select * from s6;
rollback;
start transaction;
insert into s6 values(‘1002’,’lisam’);
savepoint in1;
insert into s6 values(‘1003’,’wangwu’);
rollback to in1;
commit;
delete from s6;
select @@autocommit;#查看autocommit值

用户管理

  • 管理用户

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Use mysql;
    Select user from user;
    Create user jack@localhost identified by ‘jack123’;
    Rename user jack@.localhost to peter@localhost;
    Update user set password = password(‘jacknewpassword’) where user = ‘jack’;#修改密码,password(‘jacknewpassword’)加密密码
    Set password for jack@localhost = Password(‘jacknewpassword’);#同上
    Drop user jack@localhost;
    Show grants for username;#查看权限
    Grant select on crashcourse.* to username;#授予crashcourse数据库select查询权限
    Revoke select,create on crashcourse.* from username;#grant与revoke反义
  • 权限

    • 整个服务器:grant all 和revoke all;
    • 整个数据库:使用on 数据库名.*;
    • 特定的表,使用on 数据库名.表名;
    • 特定的列:
      grant select(cust_id) on *.* to jack@localhost;
    • 特地的存储过程与函数:
      grant execute on procedure/fuction mydb.student to jack@localhost;

练习:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use musql;
select user from user;
create user jack@localhost identified by ‘123’;
select user from user;
select user,password from user;
grant select on crashcourse.* to jack;
select *from student;
update student set sname = ‘limin’ where sid = ‘3108001’;#error,无权限
grant select(sname) on crashcourse.student to jack@localhost;
select * from student;#error
select sname from student;
grant update(sanme) on crashcourse.student to jack@localhost;
grant update on crashcourse.* to jack@localhost;
update student set sname = ‘limin’ where sid = ‘3108001’;#可以更新
update student set sid= ‘3105486’ where sname = ‘limin’;#error,无权限


MySQL正则表达式

  • 正则表达式简介
      正则表达式是用来匹配文本的特殊的字符集合,如果想从一个文本文件中提取电话号码,可以使用正则表达式来完成。
  • 正则表达式作用
      正则表达式的作用是匹配文本,讲一个正则表达式与一个文本串进行比较Mysql用where子句对正则表达式提供支持,允许指定正则表达式过滤select 查找出的数据
  • 正则表达式格式
      在where子句中使用REGEXP关键字,后面接正则表达式字符
  • 正则表达式与like的区别
      - 在where中可以使用like(not like )和REGEXP(NOT REGEXP)来匹配特定的内容
  1. like匹配整列数据
  2. REGEXP可以匹配列中任意位置的文本
      - REGEXP更加灵活强大
      - Mysql中正则表达式不区分大小写,如果需要区分大小写,可在regexp后加binary。如:where prod_name regexp binary “JetPack .000”.
  • 用法简介
      - 使用‘|’,来匹配2个字符串的一个where prod_name regexp ‘1001|1002’;
      - 使用[],匹配几个字符中的一个 where prod_name REGEXP ‘[123]’ ton’;
      - 使用[^ ],不匹配几个字符中的任意一个
    • 匹配一个范围,[1-9]1-9数字/[a-z]a-z字符
      • 任意字符的匹配——‘\’如:\. 表示.的转义字符
  • 正则表达式字符集



    实例:
    Where prod_name regexp ‘[[:digit:]]{4};——连续四个数字

函数

  • concat(链接字段)
    select concat(vend_name,’(‘,vend_country,’)’) from vendors ORDER BY vend_name;
    trim——去掉字符串左右两边空格
    ltrim——去掉左边空格
    rtrim——去掉右边空格
  • 列别名
    select concat(vend_name,’(‘,vend_country,’)’)AS evend_title from vendors ORDER BY vend_name;
  • 算数计算(+ - * /)
    1
    2
    3
    select prod_id,quantity,item_price,quantity*item_price AS expanded_price
    from orderitems
    where order_num =20005;

文本处理函数

Left(字符串,N) Right(字符串,N) ——返回串左(右)边的字符
Select left(cust_address,3) from customers;
Length() ——返回串的长度
Select vend_name,length(vend_name) as vend_name_length from crashcrouse,vendors;
Locate() ——找出串的一个子串
Select vend_city,locate(‘o’,vend_city) from vendors;
Trim() ltrim() rtrim() ——去掉空格
Select trim(cust_address) from customers;
Lower() upper() ——字符串大小写
Select vend_name,upper(vend_name) as vend_name_upcase from croushcourse.vendors;
Soundex() ——返回串的soundex值
Select cust_name,cust_contact from customers where soudex(cust_contact) = soundex(‘Y lie);
Substring() ——返回子串的字符

日期和时间处理函数

Abs()——绝对值
Cos()——余弦
Exp()——指数值
Mod()——余数
Pi()——圆周率
Rand()——随机数
Sin()——正弦
Sqrt()——平方根
Tan() ——正切
例如:不需要from表。
Select pi();
Select rand();

聚集函数

Avg()——平均值
Count()——行数
—count(*)表示对表的行数进行统计,count(列名)表示对该列中具体值进行统计。
例如:

1
2
select count(*) as num_cust from customers; #返回5
select count(ust_email) as num_cust from customers; #返回3

注:null值被count(列名)忽略,而不被count(*)忽略。

Max()——最大值
Min()——最小值
Sum()——列值求和
DISTINCT参数:剔除重复
Select avg(distinct prod_price) as avg_price from products where vend_id =1003;
注:distinct适用于以上5种函数,但是不适用于count(*),必须使用别名,不能用户计算或表达式。


数据库维护

  • 备份和恢复数据
    mysqldump –u root –p crashcourse>/tmp/back.sql #备份数据库
    mysqldump –u root –p crashcourse Vendors>table.sql#备份数据表
    mysql-uroot –p crashcourse</tmp/backup.sql#备份数据导入数据库
  • analyze table orders;
  • optimize table orders;
  • 配置文件:/etc/my.cnf
  • 错误日志:/var/log/mysqld.log
  • flush tables;
  • flush logs;

注:
Linux默认安装有mysql;
Service mysql status #linux下查看mysql服务器开启情况
Service mysql start #linux下启动mysql

-------------本文结束感谢您的阅读-------------