mysql video表内判断 VID字段 1-100 如果server字段为空

qq_35581496 2016-08-12 04:43:03

mysql video表内判断 VID字段 1-100 如果server字段为空填写http://www.baidu.com

可能说的话不够简单

MYSQL数据库内video表中有VID 和 server字段如何判断 VID 1-100的数据中server是不是空如果是空填写http://www.baidu.com

...全文

133 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

ACMAIN_CHM 2016-08-12

打赏
举报


mysql> select VID,server from video;
+-----+--------+
| VID | server |
+-----+--------+
|  28 |        |
|  37 |        |
+-----+--------+
2 rows in set (0.00 sec)

mysql> update video set server='http://www.baidu.com'
    -> where VID between 1 and 100
    -> and server is null or server='';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select VID,server from video;
+-----+----------------------+
| VID | server               |
+-----+----------------------+
|  28 | http://www.baidu.com |
|  37 | http://www.baidu.com |
+-----+----------------------+
2 rows in set (0.00 sec)

mysql>

zjcxc 2016-08-12

打赏
举报

server` varchar(255) NOT NULL DEFAULT '' ---------------------------------------- 那意味着你说的空是 0 长度字符串，不是 null update video set server='http://www.baidu.com' where VID>=1 and VID<=100 and server =''

qq_35581496 2016-08-12

打赏
举报

引用 1 楼 autfish 的回复:

update video set server='http://www.baidu.com' where VID>=1 and VID<=100 and server is null ?

-- phpMyAdmin SQL Dump

-- version phpStudy 2014

-- http://www.phpmyadmin.net

--

-- 主机: localhost

-- 生成日期: 2016 年 08 月 12 日 17:02

-- 服务器版本: 5.5.47

-- PHP 版本: 5.3.29



SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";





/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;



--

-- 数据库: `qqcfjsqlhxhlwysy`

--



-- --------------------------------------------------------



--

-- 表的结构 `video`

--



CREATE TABLE IF NOT EXISTS `video` (

  `VID` bigint(20) NOT NULL AUTO_INCREMENT,

  `UID` bigint(20) NOT NULL DEFAULT '0',

  `title` varchar(120) NOT NULL DEFAULT '',

  `description` text NOT NULL,

  `featuredesc` text NOT NULL,

  `keyword` text NOT NULL,

  `channel` varchar(255) NOT NULL DEFAULT '0|',

  `vdoname` varchar(40) NOT NULL DEFAULT '',

  `flvdoname` varchar(40) DEFAULT NULL,

  `duration` float NOT NULL DEFAULT '0',

  `space` bigint(20) NOT NULL DEFAULT '0',

  `type` varchar(7) NOT NULL DEFAULT '',

  `addtime` varchar(20) DEFAULT NULL,

  `adddate` date NOT NULL DEFAULT '0000-00-00',

  `record_date` date NOT NULL DEFAULT '0000-00-00',

  `location` text NOT NULL,

  `country` varchar(120) NOT NULL DEFAULT '',

  `vkey` varchar(20) NOT NULL DEFAULT '',

  `viewnumber` bigint(10) NOT NULL DEFAULT '0',

  `viewtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `com_num` int(8) NOT NULL DEFAULT '0',

  `fav_num` int(8) NOT NULL DEFAULT '0',

  `download_num` bigint(20) NOT NULL DEFAULT '0',

  `featured` varchar(3) NOT NULL DEFAULT 'no',

  `ratedby` bigint(10) NOT NULL DEFAULT '0',

  `rate` float NOT NULL DEFAULT '0',

  `filehome` varchar(120) NOT NULL DEFAULT '',

  `be_comment` varchar(3) NOT NULL DEFAULT 'yes',

  `be_rated` varchar(3) NOT NULL DEFAULT 'yes',

  `embed` varchar(8) NOT NULL DEFAULT 'enabled',

  `embed_code` text NOT NULL,

  `thumb` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `thumbs` tinyint(2) unsigned NOT NULL DEFAULT '20',

  `voter_id` varchar(200) NOT NULL DEFAULT '',

  `server` varchar(255) NOT NULL DEFAULT '',

  `active` char(1) NOT NULL DEFAULT '',

  `hd_filename` varchar(20) NOT NULL DEFAULT '',

  `ipod_filename` varchar(20) NOT NULL DEFAULT '',

  `aspect_hd` varchar(10) NOT NULL DEFAULT '0',

  `width_hd` int(4) NOT NULL DEFAULT '0',

  `height_hd` int(4) NOT NULL DEFAULT '0',

  `aspect_sd` varchar(10) NOT NULL DEFAULT '0',

  `width_sd` int(4) NOT NULL DEFAULT '0',

  `height_sd` int(4) NOT NULL DEFAULT '0',

  `iphone` int(1) NOT NULL DEFAULT '0',

  `hd` int(1) NOT NULL DEFAULT '0',

  `likes` bigint(20) NOT NULL DEFAULT '0',

  `dislikes` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`VID`),

  UNIQUE KEY `vkey` (`vkey`),

  KEY `viewnumber` (`viewnumber`),

  KEY `rate` (`rate`),

  KEY `fav_num` (`fav_num`),

  KEY `active` (`active`),

  KEY `channel` (`channel`),

  KEY `server` (`server`),

  KEY `hd` (`hd`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=38 ;



--

-- 转存表中的数据 `video`

--



INSERT INTO `video` (`VID`, `UID`, `title`, `description`, `featuredesc`, `keyword`, `channel`, `vdoname`, `flvdoname`, `duration`, `space`, `type`, `addtime`, `adddate`, `record_date`, `location`, `country`, `vkey`, `viewnumber`, `viewtime`, `com_num`, `fav_num`, `download_num`, `featured`, `ratedby`, `rate`, `filehome`, `be_comment`, `be_rated`, `embed`, `embed_code`, `thumb`, `thumbs`, `voter_id`, `server`, `active`, `hd_filename`, `ipod_filename`, `aspect_hd`, `width_hd`, `height_hd`, `aspect_sd`, `width_sd`, `height_sd`, `iphone`, `hd`, `likes`, `dislikes`) VALUES

(28, 1, '长征', '', '', '长征', '11', '28.mp4', '', 1784, 27240105, 'public', '1415991583', '2014-06-12', '0000-00-00', '', '', 'a4eb1c875bab14fff8ae', 483533, '2015-11-12 02:22:59', 0, 121, 543, 'no', 9, 90, '', 'yes', 'yes', 'enabled', '', 1, 20, '', '', '1', '', '28.mp4', '0', 0, 0, '4:3', 320, 240, 1, 0, 226, 82),

(37, 1, '长征1', '', '', '长征', '11', '37.mp4', '', 1784, 27240105, 'public', '1416994237', '2014-06-12', '0000-00-00', '', '', 'c4319008307d66547ce6', 237338, '2015-11-12 02:34:17', 0, 673, 543, 'no', 9, 96, '', 'yes', 'yes', 'enabled', '', 1, 20, '', '', '1', '', '37.mp4', '0', 0, 0, '4:3', 320, 240, 1, 0, 861, 32);



/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;