mysql基础知识时间：2024/3/22作者：未知来源：盘绰网教程人气：
[摘要]or^xor~位异或>位右移<<位左移常用函数字符串函数函数作用CONCAT(s1,s2,s3…)连接s1到sn的字符串（任何字符串和null拼接都是null）insert(str...
| or
| | ^ | xor | | ~ | 位异或
| | > | 位右移
| | << | 位左移
| 常用函数 字符串函数 | 函数 | 作用 | CONCAT(s1,s2,s3…)
| 连接s1到sn的字符串（任何字符串和null拼接都是null）
| insert(str,x,y,instr)
| 将字符串str从x位置开始，y字符长的子串替换为字符串instr
| lower(str)
| 将字符串str中所有字符变为小写
| UPPER(str)
| 大写
| LEFT(str,x)
| 返回字符串str最左边x个字符
| RIGHT(str,x)
| 返回字符串str最右边的x个字符
| LPAD(str,n,pad)
| 用字符串pad对str最左边进行填充，直到长度为n个字符串长度
| PRPAD(str,n,pad)
| 用字符串pad对str最右边进行填充，直到长度为n个字符串长度
| LTRIM(str)
| 去掉字符串str左侧的空格
| RIGHT(str)
| 去掉字符串str行尾的空格
| REPEAT(str,x)
| 返回str重复x次的结果
| REPLACE(Str,a,b)
| 用字符串b替换字符串str中所有出现的字符串a
| (STRCMPs1,s2)
| 比较字符串s1和s2
| TRIM(str)
| 去掉行尾和行头的空格
| SUBSTRING(str,x,y)
| 返回字符串str x位置起y字符串长度的字串
|
数字函数 函数
| 功能
| ABS(X)
| 返回x的绝对值
| CEIL(X)
| 返回大于x的最小整数值
| FLOOR(X)
| 返回小于x的最大整数值
| MOD(x,y)
| 返回x/y的模
| RAND()
| 返回0-1内的随机值
| ROUND(x,y)
| 返回参数x的四舍五入的有y位小数的值
| | TRUNCATE(x,y) | 返回数值x截断为y位小树的结果 |
日期和时间函数函数
| 功能
| CURDATE()
| 返回当前日期
| CURTIME()
| 返回当前时间
| NOW()
| 返回当前的日期和时间
| UNIX_TIMESTAMP(date)
| 返回date的unix时间戳
| FROM_UNIXTIME
| 返回UNIX时间戳的日期值
| WEEK(date)
| 返回日期date为一年中的第几周
| YEAR(date)
| 返回日期date的年份
| HOUR(time)
| 返回time的小时值
| MINUTE(time)
| 返回time的分钟值
| MONTHNAME(date)
| 返回date的月份名
| DATE_FROMATE(date,fmt)
| 返回按字符串fmt格式化日期date值
| DATE_ADD(date,interval expr type)
| 返回一个日期或时间值加上一个时间间隔的时间值
| DATEDIFF(expr,expr2)
| 返回起始时间expr和结束时间expr2之间的天数 |
流程函数 函数
| 功能
| IF(value,t f)
| 如果value是真 返回 t；否则返回f
| IFNULL(value1,value2)
| 如果value1不为空，返回value1，负责返回value2
| CASE WHEN[value1] THEN[value2]…ELSE[default] END
| 如果value1是真，返回result1否则返回defalut
| case [expr] WHEN[value1] THEN[value2]…ELSE[default] END
| 如果expr等于value1，返回result1否则返回defalut
|
实例
其他函数
函数
| 功能
| DATABASE()
| 返回的确数据库库名
| VERSION()
| 返回当前数据库版本
| USER()
| 返回当前登录用户名
| INET_ATON(IP)
| 返回ip地址的数字表示
| INET_NTOA(num)
| 返回数字代表的ip地址
| PASSWORD(str)
| 返回字符串str加密版本
| MD5()
| 返回字符串的md5值 |
MySql引擎
MySql支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，用户可以选择不同的数据存储引擎来提高应用的效率 创建表如果不指定存储引擎，系统默认使用默认存储引擎，MySql5.5之前的默认引擎是MyISAM，5.5之后改为InnoDB。如果要修改默认的存储引擎，可以在参数文件中设置default-table-type. show ENGINES //查看的确支持的存储引擎
//通过增加engine关键字设置新建表的储存引擎z
create table ai(i bigint(20)not null auto_increment,primary key(i))engine=innodb default charset=gbk;
通过alter 来修改一个表的存储引擎
ALTER TABLE ai ENGINE =MyISAM; MyISAM
MyISAM 不支持事务、也不 不支持外键，其优点是速度快，对事务完整性没有要求。以SELECT和INSERT为主的应用基本上都就可以使用这个表 InnoDB
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。 create table autoincre_demo (i smallint not null auto_increment,name varchar(10),primary key(i))engine=innodb;
insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3')
如果插入空或者0，则实际插入的将是自动增长后的值。
可以通过以下语句强制设置自动增加列的初始值，默认从1开始，但是该强制的默认值是保留到内存中，如果数据库从起，这个强制的默认值会丢失，就需要数据库启动后重新设置
ALTER TABLE *** auto_increment =n MEMORY
memory 存储引擎使用存在于内存中的内容来创建表，每个MEMORY表实际对应一个磁盘文件，格式是.fm,MEMORY表的访问非常快，因为它的数据是放在内存中，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会 alter table t2 engine=memory;
show TABLE status like 't2'
给memory表创建索引。可以指定hash索引还是btree索引
create index mem_hash using hash on tab_memory(city_id); 存储过程和函数的相关操作. 在对储存过程和函数操作时，需要首先确认用户是否具有相应的权限。例如，创建存储过程或者函数需要CREATE ROUTINE权限，修改或者删除存储过程或者函数需要ALTER ROUT INE权限，执行过程或者函数需要EXECUTE权限 创建一个新的过程 film_in_stock,该过程用来检查 film_id和store_id对应的inventory是否满足要求，并且返回满足的inventory_id 以及满足要求的记录数
CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)
READS sql data
begin
select inventory_id
from inventory
where film_id =p_film_id
and store_id=p_store_id
and inventory_in_stock(inventory_id);
SELECT found_rows() into p_film_count;
end $$
通常在创建过程和函数之前，都会通过DELIMITE $$命令将语句的结束符从'；'修改成其他符号，这里使用‘$$’，这样在过程和函数中的
';'就不会被MySql，解释成语句的结束而错误。在存储过程或者函数创建完成 通过‘DELIMITER;'命令在将结束符改回成';'
调用过程
CALL film_in_stock(2,2,@a);
存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦逻辑改变，只需要修改存储过程，对调用者的程序没有影响
删除存储过程或者函数
一次只能删除一个存储过程或者函数，删除需要ALTER ROUTINE权限
drop procedure film_in_stock;
查看存储过程或者函数状态
show procedure status like 'film_in_stock';
查看存储过程的函数定义
show create procedure film_in_stock
变量使用
存储过程和函数中可以使用变量，在MySql 5.1版本中，变量不区分大小写
变量的定义
通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN...END中，可以用在嵌套块中
定义一个DATE类型的变量
DECLARE last_month_start date;
变量赋值 可以直接赋值，或者通过查询赋值。直接赋值使用set，可以赋常量或者赋表达式
set var_name=expr [,var_name=expr]...
set last_month_start=date_sub(current_date(),interval month);
select col_name[,...] into var_name[,...] table_expr; 定义条件和处理 delimiter $$create procedure actor_insert()begin
declare continue handler for sqlstate '23000' set @x2=1;
set @x=1;
insert into actor(actor_id,first_name,last_name) values(201,'test','201');
set @x=2;
insert into actor(actor_id,first_name,last_name) values(1,'test','1');
set @x=3;end ;$$
调用处理函数时遇到主键重的错误会按照定义的处理方式去处理，由于定义的是CONTINUE 会继续执行下面的语句
还支持EXIT表示终止 光标使用 声明光标
declare cursor_name cursor for select_statement
open光标
open cursor_name
fetch光标
fetch cursor_name into var_name[,var_name]...
close光标
close cursor_name
delimiter $$
create procedure payment_stat()
begin
declare i_staff_id int;
declare d_amount decimal(5,2);
declare cur_payment cursor for select staff_id,amount from payment;
declare exit handler for not found close cur_payment;
set @x1=0;
set @x2=0;
open cur_payment;
REPEAT
FETCH cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
end if;
until 0 end repeat;
close cur_payment;
end;
$$
变量，条件，处理程序，光标都是通过DECLARE定义的，她们之间是有先后顺序要求的。
变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明 控制语句 case
when i_staff_id =2 then
set @x1=@x1+d_amount; else
set @x2=@x2+d_amount;loop 和leave结合create procedure actor_insert()begin
set @x=0;
ins:loop
set @x=@x+1; if @x=100 then
leave ins; end if;
insert into actor(first_name,last_name) values('Test','201'); end loop ins;end;
$$
inerate 语句作用是跳过当前循环的剩下语句，直接进入下一轮循环create procedure actor_insert()begin
set @x=0;
ins:loop
set @x=@x+1; if @x=10 then
leave ins;
elseif mod(@x,2)=0 then
iterate ins; end if;
insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x); end loop ins;end;
$$repeat 语句 有条件的循环控制语句，当满足条件的时候退出循环repeat
fetch cur_payment into i_staff_id,d_amount; if i_staff_id =2 then
set @x1=@x1+d_amount; else
set @x2=@x2+d_amount; end if; until 0 end repeat;whiledelimiter $$create procedure loop_demo()begin
set @x=1,@x1=1;
repeat
set @x=@x+1； until @x>0 end repeat; while @x<1 do
set @x=@x+1; end while; end;
$$//创建事件调度器CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECONDDOINSERT INTO dept(deptno,deptname)
VALUES(3,'3');//查看本地调度器状态
show variables like '%scheduler%'; //打开调度器
set global event_scheduler=1; //查看后台进程
show processlist; //创建一个新的定时器 定时清空表，防止表变大，这类触发器非常适合去定期清空临时表或者日志表
create event trunc_test on schedule every 1 minute do truncate table test;
禁用调度器或者删除
alter event test_event_1 disable;
drop event test_event_1; SQL Mode 在MySql中，SQLMode常用来解决下面几类问题 通过设置SQL Mode，可以完成不同严格程度的数据校验，有效的保障数据准确性。 通过设置SQL Mode，为ANSI模式，来保证大多数SQL符合标准的Sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务SQL进行较大的修改 在不同数据库之间进行数据迁移之前，通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中
查看 SQL Mode命令select @@sql_mode
插入一个出国实际定义值的大小varchar(10)insert into value('123400000000000000000000000000000');//查看warning内容show warningsselect * from t 这里对插入的数据
进行截取前10位
设置SQL Mode为 严格模式set session sql_mode='STRICT_TRANS_TABLES'再次插入insert into value('123400000000000000000000000000000'); 直接给出ERROR，而不是
warning
SQL Mode常见功能
校验日期是合法性set seesion sql_mode='ANSI'insert into t values('2007-04-31')
结果是 插入值变成'0000-00-00 00:00:00' 并且系统给出warning 而在TRADITIONAL模式下，直接提示日期非法，拒绝插入，同时Mode（x,0）也会报错
qidon NO_BACKSLASH_ESCAPES模式，使反斜杠成为普通字符，在导入数据时，如果数据含有反斜杠字符,你们启动NO_BACKSLASH_ESCAPES模式，保证数据的正确性
启动PIPES_AS_CONCAT。将
关键词： mysql基础知识
|
|