MySQL序列以及生成主键编号记录

广告

预留-------花茶(决明子 菊花 枸杞 红豆薏米茶 五宝茶 增发茶 等等 )
联系人 我

操作MySQL常用命令

#启动mysql服务器
net start mysql
#关闭 
net stop mysql
#登录mysql
mysql -u root -p
#退出MYSQL命令
exit

生成主键编号记录

##生成主键编号
select (select CONCAT('S','012345678912',DATE_FORMAT(now(),'%Y%m%d'),LPAD(nextval('SEQ_TEST'),4,'0'))) as xxzjbh from dual;
#1: 使用CONCAT(str,str,..)函数拼接 将多个字符串连接成一个字符串
#2: 使用DATE_FORMAT(now(),'%Y%m%d') 生成八位年月日
#3: 使用LPAD()函数 lpad(str,len,padstr)  用字符串 padstr对 str进行左边填补直至它的长度达到 len个字符长度，然后返回 str。如果 str的长度长于 len'，那么它将被截除到 len个字符。
#4: 调用自己的nextval()函数,用于生成序列
CREATE DEFINER = `root` @`localhost` FUNCTION `nextval` ( str VARCHAR ( 50 ) ) RETURNS INT ( 11 ) BEGIN
DECLARE
	i INT;

SET i = ( SELECT start_value FROM mysql_seq WHERE NAME = str );
UPDATE mysql_seq 
SET start_value = i + increment_value 
WHERE
	NAME = str;
RETURN i;

END
#5: 在写函数之前要新建序列表 mysql_seq
-- 创建序列专用表   序列名 初始值 步长1
create table mysql_seq (
	name varchar(50) not null primary key,
	start_value int not null,
	increment_value int not null default 1
);
-- 创建序列
insert into mysql_seq values('SEQ_TEST',1,1);

MySQL时间类型笔记

select * from student where time <= DATE_FORMAT('2019-10-31','%Y/%m/%d 23:59:59') and time >= DATE_FORMAT('2019-10-31','%Y/%m/%d 00:00:00') order by time desc

--当前时间 年月日 时分秒
select now();
--当前日期
select current_date();
--当前时间
select current_time();
--获取时间格式
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT('2018-10-22 16:08:07','%Y年%m月%d日 %H时%i分%s秒');

--时间格式
		date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();
　　str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date();
　　%Y：代表4位的年份
　　%y：代表2为的年份
　　%m：代表月, 格式为(01……12)
　　%c：代表月, 格式为(1……12)
　　%d：代表月份中的天数,格式为(00……31)
　　%e：代表月份中的天数, 格式为(0……31)
　　%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)
　　SELECT DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s') as time
　　select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') as time

select STR_TO_DATE(now(),'%Y-%m-%d %H:%i:%s') as time


--函数
--返回字符串str的第一个字符的ASCII值(str是空串的时候返回0)
select ASCII('10')

序列的创建

##mysql 在创建函数的时候一定要先开启函数功能
##查看函数功能是否打开
show variables like '%func%';  
##设置函数功能打开or关闭      等于 1 开启 
set global log_bin_trust_function_creators=1; 


-- 创建序列专用表   序列名 初始值 步长1
create table mysql_seq (
	name varchar(50) not null primary key,
	start_value int not null,
	increment_value int not null default 1
);
 
-- 创建序列
insert into mysql_seq values('SEQ_TEST',1,1);
 
 
-- 定义获取序列函数
DELIMITER //
create  function nextval(str varchar(50)) returns integer
begin
	declare i int;
	set i=(select start_value from mysql_seq where name=str);
	update mysql_seq
		set start_value=i+increment_value
	where name=str;
return i;
end;
//
 
 
-- 使用序列
select nextval('SEQ_TEST');
select nextval('SEQ_TEST');
select nextval('SEQ_TEST');

############################# 和上一个同样 就是命名不一样

##1.创建表emp_seq，用来存放sequence值：
create table emp_seq (
name varchar(50) not null primary key,
start_value int not null,
increment_value int not null default 1
);

##2.手动插入数据：
insert into emp_seq values('empno',1,1);


##3.定义函数 nextval：
DELIMITER //
create function nextval(str varchar(50)) returns integer
begin
declare i int;
set i=(select start_value from emp_seq where name=str);
update emp_seq
set start_value=i+increment_value
where name=str;
return i;
end;
//

##4.恢复默认的语句结束符：
DELIMITER ;
 
select nextval('empno') from dual;

MySQL中文转汉语拼音(未解决多音字,生僻字等问题)

使用场景 在查询姓名的时候,只知道名字读音但是不清楚汉字,所以需要把中文转成汉语拼音,进行查询,但是无法解决多音字问题以及生僻字等问题;
个人觉得在入信息的时候,就把姓名的汉语拼音给记录上,这样方便之后查询

-- 创建汉字拼音对照临时表
CREATE TABLE IF NOT EXISTS `t_base_pinyin` (
  `pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL,
  `code_` int(11) NOT NULL,
  PRIMARY KEY (`code_`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 插入数据
INSERT INTO t_base_pinyin (pin_yin_,code_)  VALUES ("a", 20319),("ai", 20317),("an", 20304),("ang", 20295),("ao", 20292),("ba", 20283),("bai", 20265),("ban", 20257),("bang", 20242),("bao", 20230),("bei", 20051),("ben", 20036),("beng", 20032),("bi", 20026),("bian", 20002),("biao", 19990),("bie", 19986),("bin", 19982),("bing", 19976),("bo", 19805),("bu", 19784),("ca", 19775),("cai", 19774),("can", 19763),("cang", 19756),("cao", 19751),("ce", 19746),("ceng", 19741),("cha", 19739),("chai", 19728),("chan", 19725),("chang", 19715),("chao", 19540),("che", 19531),("chen", 19525),("cheng", 19515),("chi", 19500),("chong", 19484),("chou", 19479),("chu", 19467),("chuai", 19289),("chuan", 19288),("chuang", 19281),("chui", 19275),("chun", 19270),("chuo", 19263),("ci", 19261),("cong", 19249),("cou", 19243),("cu", 19242),("cuan", 19238),("cui", 19235),("cun", 19227),("cuo", 19224),("da", 19218),("dai", 19212),("dan", 19038),("dang", 19023),("dao", 19018),("de", 19006),("deng", 19003),("di", 18996),("dian", 18977),("diao", 18961),("die", 18952),("ding", 18783),("diu", 18774),("dong", 18773),("dou", 18763),("du", 18756),("duan", 18741),("dui", 18735),("dun", 18731),("duo", 18722),("e", 18710),("en", 18697),("er", 18696),("fa", 18526),("fan", 18518),("fang", 18501),("fei", 18490),("fen", 18478),("feng", 18463),("fo", 18448),("fou", 18447),("fu", 18446),("ga", 18239),("gai", 18237),("gan", 18231),("gang", 18220),("gao", 18211),("ge", 18201),("gei", 18184),("gen", 18183),("geng", 18181),("gong", 18012),("gou", 17997),("gu", 17988),("gua", 17970),("guai", 17964),("guan", 17961),("guang", 17950),("gui", 17947),("gun", 17931),("guo", 17928),("ha", 17922),("hai", 17759),("han", 17752),("hang", 17733),("hao", 17730),("he", 17721),("hei", 17703),("hen", 17701),("heng", 17697),("hong", 17692),("hou", 17683),("hu", 17676),("hua", 17496),("huai", 17487),("huan", 17482),("huang", 17468),("hui", 17454),("hun", 17433),("huo", 17427),("ji", 17417),("jia", 17202),("jian", 17185),("jiang", 16983),("jiao", 16970),("jie", 16942),("jin", 16915),("jing", 16733),("jiong", 16708),("jiu", 16706),("ju", 16689),("juan", 16664),("jue", 16657),("jun", 16647),("ka", 16474),("kai", 16470),("kan", 16465),("kang", 16459),("kao", 16452),("ke", 16448),("ken", 16433),("keng", 16429),("kong", 16427),("kou", 16423),("ku", 16419),("kua", 16412),("kuai", 16407),("kuan", 16403),("kuang", 16401),("kui", 16393),("kun", 16220),("kuo", 16216),("la", 16212),("lai", 16205),("lan", 16202),("lang", 16187),("lao", 16180),("le", 16171),("lei", 16169),("leng", 16158),("li", 16155),("lia", 15959),("lian", 15958),("liang", 15944),("liao", 15933),("lie", 15920),("lin", 15915),("ling", 15903),("liu", 15889),("long", 15878),("lou", 15707),("lu", 15701),("lv", 15681),("luan", 15667),("lue", 15661),("lun", 15659),("luo", 15652),("ma", 15640),("mai", 15631),("man", 15625),("mang", 15454),("mao", 15448),("me", 15436),("mei", 15435),("men", 15419),("meng", 15416),("mi", 15408),("mian", 15394),("miao", 15385),("mie", 15377),("min", 15375),("ming", 15369),("miu", 15363),("mo", 15362),("mou", 15183),("mu", 15180),("na", 15165),("nai", 15158),("nan", 15153),("nang", 15150),("nao", 15149),("ne", 15144),("nei", 15143),("nen", 15141),("neng", 15140),("ni", 15139),("nian", 15128),("niang", 15121),("niao", 15119),("nie", 15117),("nin", 15110),("ning", 15109),("niu", 14941),("nong", 14937),("nu", 14933),("nv", 14930),("nuan", 14929),("nue", 14928),("nuo", 14926),("o", 14922),("ou", 14921),("pa", 14914),("pai", 14908),("pan", 14902),("pang", 14894),("pao", 14889),("pei", 14882),("pen", 14873),("peng", 14871),("pi", 14857),("pian", 14678),("piao", 14674),("pie", 14670),("pin", 14668),("ping", 14663),("po", 14654),("pu", 14645),("qi", 14630),("qia", 14594),("qian", 14429),("qiang", 14407),("qiao", 14399),("qie", 14384),("qin", 14379),("qing", 14368),("qiong", 14355),("qiu", 14353),("qu", 14345),("quan", 14170),("que", 14159),("qun", 14151),("ran", 14149),("rang", 14145),("rao", 14140),("re", 14137),("ren", 14135),("reng", 14125),("ri", 14123),("rong", 14122),("rou", 14112),("ru", 14109),("ruan", 14099),("rui", 14097),("run", 14094),("ruo", 14092),("sa", 14090),("sai", 14087),("san", 14083),("sang", 13917),("sao", 13914),("se", 13910),("sen", 13907),("seng", 13906),("sha", 13905),("shai", 13896),("shan", 13894),("shang", 13878),("shao", 13870),("she", 13859),("shen", 13847),("sheng", 13831),("shi", 13658),("shou", 13611),("shu", 13601),("shua", 13406),("shuai", 13404),("shuan", 13400),("shuang", 13398),("shui", 13395),("shun", 13391),("shuo", 13387),("si", 13383),("song", 13367),("sou", 13359),("su", 13356),("suan", 13343),("sui", 13340),("sun", 13329),("suo", 13326),("ta", 13318),("tai", 13147),("tan", 13138),("tang", 13120),("tao", 13107),("te", 13096),("teng", 13095),("ti", 13091),("tian", 13076),("tiao", 13068),("tie", 13063),("ting", 13060),("tong", 12888),("tou", 12875),("tu", 12871),("tuan", 12860) ,("tui", 12858),("tun", 12852),("tuo", 12849),("wa", 12838),("wai", 12831),("wan", 12829),("wang", 12812),("wei", 12802),("wen", 12607),("weng", 12597),("wo", 12594),("wu", 12585),("xi", 12556),("xia", 12359),("xian", 12346),("xiang", 12320),("xiao", 12300),("xie", 12120),("xin", 12099),("xing", 12089),("xiong", 12074),("xiu", 12067),("xu", 12058),("xuan", 12039),("xue", 11867),("xun", 11861),("ya", 11847),("yan", 11831),("yang", 11798),("yao", 11781),("ye", 11604),("yi", 11589),("yin", 11536),("ying", 11358),("yo", 11340),("yong", 11339),("you", 11324),("yu", 11303),("yuan", 11097),("yue", 11077),("yun", 11067),("za", 11055),("zai", 11052),("zan", 11045),("zang", 11041),("zao", 11038),("ze", 11024),("zei", 11020),("zen", 11019),("zeng", 11018),("zha", 11014),("zhai", 10838),("zhan", 10832),("zhang", 10815),("zhao", 10800),("zhe", 10790),("zhen", 10780),("zheng", 10764),("zhi", 10587),("zhong", 10544),("zhou", 10533),("zhu", 10519),("zhua", 10331),("zhuai", 10329),("zhuan", 10328),("zhuang", 10322),("zhui", 10315),("zhun", 10309),("zhuo", 10307),("zi", 10296),("zong", 10281),("zou", 10274),("zu", 10270),("zuan", 10262),("zui", 10260),("zun", 10256),("zuo", 10254);


-- 建立汉字转换拼音函数
DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)
RETURNS VARCHAR(255) CHARSET gbk
BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;
    DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
    DECLARE rcode INT;
    DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
    DECLARE lp INT;
    SET mycode = 0;
    SET lp = 1;
    SET NAME = HEX(NAME);
    WHILE lp < LENGTH(NAME) DO
        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED);
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED);
        IF lcode > 128 THEN
            SET mycode =65536 - lcode * 256 - rcode ;
            SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
            SET lp = lp + 4;
        ELSE
            SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;
    RETURN LOWER(mypy);
END;
$
DELIMITER ;

--使用
select to_pinyin('王佳');

case when else end 语句

需求: 对一个code表中的数据进行要求排序,要求 橘子 排在第一个,第二个是 香蕉 其他的随便排

select * from code_fruit

查询数据 :

code	name
34	苹果
25	葡萄
66	橘子
26	香蕉
22	猕猴桃
45	火龙果

要求排序,使用 case when else end 语句, 根据code 字段值构建一个字段sort进行排序,当state = 66的时候,让sort的值为 1, 当code = 26 的时候,让sort的值为2,最后对sort进行排序

select *,(case when code = '66' then '1' when code = '26' then '2' else '3' end) as sort from code_fruit order by sort asc

查询数据:

code	name	sort
66	橘子	1
26	香蕉	2
34	苹果	3
25	葡萄	3
22	猕猴桃	3
45	火龙果	3