msyql笔记

发表于 | 更新于 | 分类于
mysql必知必会学习笔记~

mysql 用法合集

条目很多，主要是提供模板以及部分注意点，并无具体例子，作为学习笔记。

create database databaseName; 新建库
create user userName@host identified by password; 创建用户并指定密码限制登陆ip
grant xxx on databaseName.xxx to userName@host; 赋予某个用户某个库的某些权限
Revoke xxx on dbName.xxx from user;撤销权限
show databases; 展示该用户下所有的库
select database(); 查询当前使用的库
select user(); 查询当前用户
show tables 展示该库所有的表
desc tableName/show columns from tableName; 展示表的字段结构
Rename user userName to userName2;用户重命名
Drop user userName;删除用户
Set password for userName = password(xxxx)
Set password = password(xxx)//默认修改当前用户

查询语句

基础模板:

select xxx from xxx where xxx group by xxx having xxx order by xxx limit xx;

去重查询:

distinct :select distinct xxx from xxx

where过滤查询

包含等于(=)，不等于(!=,<>)，大于小于(>,<)，大于等于(>=)，小于等于(<=)，between(包头包尾)等条件操作符,IS NULL(空值检查)，and/or/not 关键字。还有就是like与通配符，正则的使用等。

其中条件操作符就是对某个列的值做对应的比较where columnName 条件操作符 value ，注意下between包头包尾(between x and y)即可。其他的主要讲下注意点。

NULL 指的是不包含值而不是零值。TIP!在涉及到不匹配的时候需要注意。
ex:select xxx from xxx where columnName != y 这里columnName如果是null,他是不会检索返回的。我测试的时候是使用的mysql5.7.10 社区版

IN 指的是某列的值在一个集合里，类似于or的功能，但是执行效率高一点:where column in (x,y,z)

AND 与操作，OR或操作。用于多个条件的拼接。AND优先级高于OR,为了防止歧义，可以使用括号

NOT 非操作。否定后面的操作。

like 配合通配符使用:
where xxx like "%xx。%表示n个字符(n >= 0)
where xxx like "_xx。_表示1个字符
不过不推荐使用，如果使用的话推荐放在检索条件的最后。原因是效率问题

这里where后面还可以跟正则搜索regexp这里是不区分大小写的，如果需要对匹配的值注意大小写，使用binary：where columnName regexp binary 'A'常规使用可以参考正则使用，举个简单例子
select * from spider_user where username regexp '^a[a-z\\-]*[0-9]+$' 这里匹配a开头中间包含(0或者多个)(小写字母或者-符号)并且数字结尾的值

限制查询:

limit select xx from xxx limit index,nums
or
select xxx from xxx limit nums offset index//mysql5开始支持
从index 开始查询nums条记录，index是从0开始的

分组查询:

Group by这时候的聚集函数(下面有讲)就是针对分组数据生效的.除了计算聚合函数外，select后的每一个都必须出现在group by 中:
select vacation,count(*) as nums from spider_user where sex in('0','1') group by vacation having nums>=2 order by vacation

联结查询:

join(inner join),left join,right join
使用等值联结/内联结:
select a.x,b.y from a,b where a.xxx = b.xxx
or
select a.x,...,b.y,... from a inner join b on a.aaa = b.bbb
左联结:
select xxx,...yyyy from a left join b on a.username = b.username;
右联结:
select xxx,...yyyy from a right join b on a.username = b.username;

通常什么联结哪边的表数据就是全的假设称为主表，另外一个没有数据的用null代替。对于on后面的过滤条件，如果条件不符合则null填充，主表例外。

组合查询:

Union/Union all：用于结果集的组合，后者不去重复

全文本搜索等需要数据引擎的支持。

函数

concat():拼接对象:
select concat(username,'(',name,')') from user//结果以username，name的值以username(name)的形式显示

upper():转换为大写
lower():转换为小写
length():返回长度
locate(substr,str)：返回str中substring第一次出现的位置，位置是从1开始数的。
ltrim():去掉左边的空格
rtrim():去掉右边的空格
trim():去掉两边的空格
substring(target,pos,len):在target中从pos位置开始取len长度的子串。pos为负数代表从尾部倒数第几个开始。计算pos依旧是从1开始数位置
left(target,len):代表从左边开始取len长度
right(target,len):代表从右边开始取len长度

这里还有个比较好玩的:
soundex(xxx) 代表发音:ex:select * from spider_user where soundex(name) = soundex('quite')这里检索到了name为quiet的数据

部分聚集函数:
AVG():求平均值
MIN():求最小值
MAX():求最大值
SUM():求总和
COUNT():求数量。这里同样需要注意null值是不计入的

其他还有部分例如时间处理函数，数学方面的比如Mod()取余数:
Abs():绝对值
Cos():余弦值
Exp():返回一个数的指数值
Mod(x,y):返回x处于y的余数
Pi():返回圆周率
Rand():返回随机数
Sin():返回正弦值
Sqrt():返回一个数的平方根
Tan():返回正切
AddDate():增加一个日期:adddate(order_date,interval 2 day)order_date加两天返回
CurDate():返回当前日期
CurTime():返回当前时间
Date():返回一个datetime的日期部分
Time():返回一个datetime的时间部分
Day():返回一个日期的天数部分
DayOfWeek():返回一个日期对应的是周几
Month():返回一个日期的月份
Year():返回一个日期的年份
…

数据操作语句部分

insert into xxx(columnName[,columnName...])values(.....)[,(...)]:插入数据
update xxx set columnName = value where xxxxx更新数据
delete from xxxx where aaa = bbbb删除数据
这里有个关于mysql安全模式的问题，在安全模式下是不能做一些敏感操作的，就比如说上面的delete后面不接where啥的。可以通过set sql_safe_updates = 0(关闭)/set sql_safe_updates = 1(开启)设置

数据定义语句部分

create可以创建表，视图，包括后面的存储过程等。

建表:

1
2
3
4
5
6
create table [if not exists ] tableName(
    columnName dataType  constraint_name,
    columnName dataType  constraint_name,
    ...，
    primary key(xxx[,xxx])
)[engine = InnoDb][default charset= utf8];

其中constraint_name可以是not null ,auto_increment等使用primary key创建主键。外键类似。
这里auto_increment是设定自增长(一张表只能一个，且必须是索引，比如给主键)。一般默认是1,可以通过SHOW VARIABLES LIKE 'AUTO_INC%';查看，SET auto_increment_increment = 3修改步长为3同样可以修改初始值，不过通过语句修改重启失效，推荐修改配置文件my.ini在服务端配置下([mysqld])写auto_increment_increment = 10设置。
可以通过select last_insert_id()获取自增值

创建视图:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

drop删除

drop table if exists tableName
视图一类的同理
drop index index_name on tableName//删除指定的索引

通过alter修改表结构

alter table tableName add columnName dataType[constraint_name]//增加列
alter table tableName modify columnName xxxx//修改列
alter table tableName drop column columnName//删除列
alter table tableName add index index_name(columnName list)orr
create index indexName on tableName(columnList)//新增索引
drop index indexName on tableNameoralter table tableName drop index indexName//删除索引
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(columnList)//新增主键或者可以通过modify的形式新增
Alter table tableName add constraint 外键名字 foreign key (columnName) references tableName(columnName) 新增外键
alter table tableName drop primary key//删除主键
alter talbe tableName drop foreign key key_name//删除外键

重命名表

rename table tableNameOld to tableNameNew

清空表

truncate table tableName

事务

Transaction 事务//set autocommit = 0(1)是否自动提交
Commit 提交事务
Rollback 回滚//无法回退create and drop
Savepoint 保留点,可以创建多个保留点，rollback to savepointName ;回滚到指定保留点

Start transaction ;开始事务一旦rollback或者commit事务自动结束

存储过程

这里举一个例子来解释下.这是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
 -- 存在就删除
drop procedure if exists ordertotalWithTax;
-- 暂时将mysql 的结束符替换为//	
delimiter //
-- 这里增加了注释
 -- name:ordertotalWith tax
-- parameters: onnumer:订单号
-- 			   taxable:是否计税
--              ototal : 返回的总值
-- 开始创建，in代表是输入的参数，out代表是输出，后面可以通过select查询到
create procedure ordertotalWithTax(
    in onnumber int,
    in taxable boolean,
    out ototal decimal(10,2)
)comment '是否计税的订单总额计算'
-- 代表内容的开始	
begin
    -- declare 关键字声明变量等
    -- 创建临时存储总量（包含tax）
    declare t_total decimal(10,2);
    -- 税制基数
    declare taxrate int default 6;        
    -- get the t_total，into关键字带便数据的输入
    select sum(item_price*quantity) from orderitems where order_num = onnumber into t_total;
            
    -- 是否计税 使用了if
    if taxable then
        select t_total+(t_total/100*taxrate) into t_total;
        end if;
            
    -- 汇总结果
    select t_total into ototal;
end//
-- 将结束符还原
delimiter ;

call ordertotalWithTax(20005,false,@total)调用的时候变量一定要带@符号。
select @total;查询出结果。

游标

游标主要用在存储过程之中。
declare ordernumbers cursor for select order_num from orders;这里就是创建了名为ordernumbers的游标，他指向后面select的结果集
open ordernumbers打开
close ordernumbers关闭
fetch ordernumbers to xx将读取的一行数据给xx,游标指向下一条数据
这里对于declare有个队形，声明顺序满足局部变量，游标，句柄(handler)否则出错

触发器

Update ,delete,insert语句触发触发器(其规定同表同一类型的触发器只有一个也就是说最多六个3x2，且没有组合触发器，insert,update就得两个，不能合在一起写）
创建触发器：唯一的触发器名，关联的表，响应的操作，何时执行

Create trigger triggerName after(就是when: after/before) insert（操作类型:insert,update,delete） on tableName(关联的表) xxxx执行

Insert 触发器
在insert 触发器中可以引用一个名为new的虚拟表，访问被插入的行在before insert触发器中,new的值可以被更新，也就是修改插入的值.对于auto_increment.new在insert执行之前包含0，在insert之后包含新的自动生成值

1
2
3
4
5
drop trigger if exists neworder;
create trigger neworder after insert on orders for each row select new.order_num into @seq;
INSERT INTO orders(order_date, cust_id)
VALUES(now(), 10001);
select @seq;--这里就是刚自动生成的值

Delete触发器可以引用一个old的虚拟表，访问被删除的行，但是是readonly

1
2
3
4
5
6
7
8
9
create table archive_orders like orders;
drop trigger if exists deleteorder;
delimiter //
create trigger deleteoreder  before delete on orders for each row 
begin
	insert into archive_orders(order_num,order_date,cust_id) values(old.order_num,old.order_date,old.cust_id);
end//
delimiter ;
delete from orders where order_num = 20010;

这里就是将删除的数据放到一个其他表里

update触发器可以引用一个old的虚拟表，访问被更新的值，new访问更新的值。old只读，new可以修改，也就是说before可以修改更新的值

1
2
3
4
5
6
7
8
9
drop trigger if exists updatevendor;
		
create trigger updatevendor before update on vendors for each row set new.vend_state = upper(new.vend_state);
		
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
		
update vendors set vend_state = 'asd' where vend_id = 1001;
select * from vendors where vend_id = 1001;

这就是在更新前保证vend_state大写

客官扫码领红包哟~