MySQL 用法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE
    ALGORITHM = UNDEFINED
    DEFINER = `root`@`localhost`
    SQL SECURITY DEFINER
VIEW `boc_vproduct_list` AS
    SELECT
        `boc_product`.*`,
        `boc_product_attr`.*
    FROM
        (`boc_product`
        LEFT JOIN `boc_product_attr` ON ((`boc_product`.`id` = `boc_product_attr`.`pid`)))

1
2
3
4

CREATE PROCEDURE `test_count_product`(OUT param1 serial)
BEGIN
	SELECT count(*) INTO param1 FROM boc_dis_product;
END

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 非补 0 版本
SELECT FLOOR(100000 + RAND() * 899999) AS random_number
FROM target_table
WHERE "unique_field" NOT IN (SELECT unique_field FROM target_table)
LIMIT 1

# 补 0 版本
SELECT LPAD(FLOOR(RAND() * 999999), 6, 0) AS random_number
FROM target_table
WHERE "unique_field" NOT IN (SELECT unique_field FROM target_table)
LIMIT 1

 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

CREATE TABLE `website0001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteBigType` int(11) DEFAULT NULL,
  `siteSmallType` int(11) DEFAULT NULL,
  `siteWorth` varchar(15) COLLATE utf8_unicode_ci DEFAULT '0' COMMENT '网站价值',
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `website0002` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteBigType` int(11) DEFAULT NULL,
  `siteSmallType` int(11) DEFAULT NULL,
  `siteWorth` varchar(15) COLLATE utf8_unicode_ci DEFAULT '0' COMMENT '网站价值',
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `websiteall` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteBigType` int(11) DEFAULT NULL,
  `siteSmallType` int(11) DEFAULT NULL,
  `siteWorth` varchar(15) COLLATE utf8_unicode_ci DEFAULT '0' COMMENT '网站价值',
  `date` date DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci INSERT_METHOD=LAST UNION=(`website0001`,`website0002`);