SHOW CREATE TABLE sw_goods;
CREATE TABLE `sw_goods` (
`goods_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`goods_name` VARCHAR(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '商品名称',
`goods_weight` INT(11) NOT NULL DEFAULT '0' COMMENT '重量',
`goods_price` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '价格',
`goods_number` INT(11) NOT NULL DEFAULT '100' COMMENT '数量',
`goods_category_id` INT(11) NOT NULL DEFAULT '0' COMMENT '分类',
`goods_brand_id` INT(11) NOT NULL DEFAULT '0' COMMENT '品牌',
`goods_introduce` TEXT COLLATE utf8_unicode_ci COMMENT '详细介绍',
`goods_big_img` VARCHAR(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '商品图片',
`goods_small_img` VARCHAR(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '商品小图',
`goods_create_time` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间',
PRIMARY KEY (`goods_id`)
) ENGINE=INNODB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='商品表'
SELECT * FROM sw_goods;
-- 建立myweb数据库
CREATE DATABASE bok CHARACTER SET utf8;
USE blog;
-- 注册表
CREATE TABLE bl_register(
uid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(38) NOT NULL,
usex ENUM('man','woman'),
uphone VARCHAR(11) NOT NULL,
uemail VARCHAR(30) NOT NULL,
uintroduce TEXT NOT NULL
)ENGINE=MYISAM CHARSET=utf8;
ALTER TABLE bl_register ADD upwd CHAR(32) NOT NULL AFTER uname;
ALTER TABLE bl_register CHANGE usex usex ENUM('男','女','保密');
SHOW TABLES;
TRUNCATE TABLE bl_register;
SELECT * FROM bl_register;
DESC my_register;
INSERT INTO my_register VALUES(NULL,'caolizhi','man','dsfdsfsd','sdfdsfsdf','sdfdsfsdf')
DESC bl_register;
USE blog;
CREATE TABLE bl_blogcontent(
cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ctitle VARCHAR(100) NOT NULL,
cauther VARCHAR(30) NOT NULL,
ctime INT NOT NULL,
ccontent TEXT NOT NULL
)ENGINE=MYISAM CHARSET=utf8;
SHOW TABLES;
DESC bl_blogcontent;
SELECT * FROM bl_blogcontent;
CREATE TABLE bl_tag(
tid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tname VARCHAR(100) NOT NULL,
PRIMARY KEY(tid)
)ENGINE=MYISAM CHARSET=utf8;
DROP TABLE tag;
SHOW TABLES;
SELECT * FROM bl_tag;
CREATE TABLE bl_tagblog(
lid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
PRIMARY KEY(lid)
)ENGINE=MYISAM CHARSET=utf8;
SELECT * FROM bl_blogcontent;
SELECT * FROM bl_tagblog;
SELECT * FROM bl_tag;
TRUNCATE TABLE bl_tagblog;
TRUNCATE TABLE bl_register;
CREATE TABLE bl_comment(
sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
uid INT NOT NULL,
cid INT NOT NULL,
stime INT NOT NULL,
scontent TEXT NOT NULL,
PRIMARY KEY(sid)
)ENGINE=MYISAM CHARSET=utf8;
ALTER TABLE bl_comment CHANGE uid uname VARCHAR(100) NOT NULL;
DESC bl_register;
DESC bl_comment;
SHOW TABLES;
DESC bl_register;
SELECT * FROM bl_comment;
TRUNCATE TABLE bl_comment;
TRUNCATE TABLE bl_tag;
SELECT * FROM bl_register;
TRUNCATE TABLE bl_blogcontent;
USE blog;
SELECT * FROM bl_tag;
SELECT * FROM bl_tag;
USE blog;
-- myblog数据库
DROP DATABASE myblog;
DROP DATABASE bok;
CREATE DATABASE bok CHARACTER SET utf8;
USE myblog;
-- 用户表
CREATE TABLE blog_user(
uid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(38) NOT NULL,
usex ENUM('girl','boy','保密'),
uphone VARCHAR(11) NOT NULL,
uemail VARCHAR(30) NOT NULL,
uintroduce TEXT NOT NULL,
)ENGINE=MYISAM CHARSET=utf8;
ALTER TABLE blog_user ADD upwd CHAR(32) NOT NULL AFTER uname;
ALTER TABLE blog_user ADD uimg VARCHAR(40) DEFAULT 'uimg.png';
SELECT * FROM blog_user;
-- 博客表
CREATE TABLE blog_blogcon(
cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ctitle VARCHAR(100) NOT NULL,
uid INT NOT NULL,
ctime INT NOT NULL,
ccontent TEXT NOT NULL
)ENGINE=MYISAM CHARSET=utf8;
SELECT * FROM blog_blogcon;
ALTER TABLE blog_blogcon ADD cremark TINYTEXT NOT NULL;
SELECT * FROM blog_blogcon;
ALTER TABLE blog_blogcon ADD class VARCHAR(40) NOT NULL;
ALTER TABLE blog_blogcon MODIFY bthrough INT DEFAULT 0;
ALTER TABLE blog_blogcon ADD auther VARCHAR(40) NOT NULL;
-- tag表
CREATE TABLE blog_tag(
tid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tname VARCHAR(100) NOT NULL,
PRIMARY KEY(tid)
)ENGINE=MYISAM CHARSET=utf8;
SELECT * FROM blog_tag;
-- blog tag关联表
CREATE TABLE blog_tagblog(
lid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
PRIMARY KEY(lid)
)ENGINE=MYISAM CHARSET=utf8;
-- 评论表
CREATE TABLE blog_comment(
sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
uid INT NOT NULL,
cid INT NOT NULL,
stime INT NOT NULL,
scontent TEXT NOT NULL,
PRIMARY KEY(sid)
)ENGINE=MYISAM CHARSET=utf8;
SELECT * FROM blog_blogcon;
USE myblog;
SELECT * FROM blog_blogcon;
SELECT * FROM blog_tag;
SELECT * FROM blog_tagblog;
USE myblog;
SELECT * FROM blog_comment;
ALTER TABLE blog_comment ADD sthrough TINYINT DEFAULT 0;
SELECT * FROM blog_blogcon;
SELECT * FROM blog_tagblog;
SELECT * FROM blog_user;
--
CREATE TABLE blog_upload(
gid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
gurl VARCHAR(30) NOT NULL
)ENGINE MYISAM CHARSET=utf8;
SELECT * FROM blog_upload;
TRUNCATE TABLE blog_upload;
USE myblog;
SELECT * FROM blog_blogcon;
USE myblog;
SELECT * FROM blog_blogcon;
SHOW TABLES;
DESC blog_upload;
SHOW CREATE TABLE blog_upload;
CREATE TABLE blog_upload (
gid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-- 图片名称
gname VARCHAR(30) DEFAULT NULL,
-- 相册
galbum VARCHAR(40) NOT NULL,
-- 图片标题
gtitle VARCHAR(40) NOT NULL,
PRIMARY KEY (gid)
) ENGINE=MYISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
ALTER TABLE blog_upload CHANGE gthumb gth_name VARCHAR(20);
ALTER TABLE blog_upload ADD gup_name VARCHAR(40);
ALTER TABLE blog_upload ADD uid INT NOT NULL;
ALTER TABLE blog_upload MODIFY gth_name VARCHAR(50);
ALTER TABLE blog_upload DROP gthumb;
DROP TABLE blog_upload;
DESC blog_upload;
DESC blog_upload;
SELECT * FROM blog_upload;
TRUNCATE TABLE blog_upload;
USE myblog;
ALTER TABLE blog_upload ADD gvcode TINYINT NOT NULL;
TRUNCATE TABLE blog_blogcon;
TRUNCATE TABLE blog_tag;
SELECT * FROM blog_blogcon;
USE DATABASE iot;
SHOW TABLES;
SHOW CREATE TABLE iot_news;
-- 冰果密码表
USE bok;
CREATE TABLE bok_salt (
sid INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Salt ID',
uid INT(11) NOT NULL COMMENT 'User ID',
salt INT(11) NOT NULL DEFAULT '0' COMMENT 'Salt Number',
HASH VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'Salt Hash',
PRIMARY KEY (sid)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
SHOW TABLES;
SELECT * FROM bok_salt;
SELECT * FROM bok_user;
DROP TABLE bok_user;
CREATE TABLE bok_user (
uid INT(11) NOT NULL AUTO_INCREMENT COMMENT 'UID',
u_username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',
PRIMARY KEY (uid)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
-- 修改列名
ALTER TABLE bok_user CHANGE uname u_username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名';
SELECT * FROM bok_user;
SHOW TABLES;
DROP TABLE bok_news;
CREATE TABLE bok_news (
nid INT(11) NOT NULL AUTO_INCREMENT COMMENT 'News ID',
n_title VARCHAR(255) NOT NULL COMMENT '标题',
n_author VARCHAR(255) NOT NULL COMMENT '新闻作者',
n_content TEXT NOT NULL COMMENT '新闻内容',
n_click INT(10) DEFAULT '0' COMMENT '新闻点击量',
n_created DATETIME DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
n_updated DATETIME DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (nid)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
SHOW TABLES;
SELECT * FROM bok_news;
SELECT MD5('123456ibelieve');
-- 实验数据
9eaef1b238461fd47f39488425193ec1
INSERT INTO bok_user VALUES (NULL,'admin');
SELECT * FROM bok_user;
INSERT INTO bok_salt VALUES(NULL,1,123456,'9eaef1b238461fd47f39488425193ec1');
SELECT * FROM bok_salt;
USER bok;
SELECT * FROM bok_news;
SELECT UNIX_TIMESTAMP(NOW());
SELECT CURRENT_TIMESTAMP;
SELECT UNIX_TIMESTAMP();
SELECT * FROM bok_news ORDER BY n_click DESC;
-- 链接表
SHOW CREATE TABLE iot_slide_show;
CREATE TABLE bok_link (
lid INT(11) NOT NULL AUTO_INCREMENT COMMENT 'lid',
l_title VARCHAR(128) NOT NULL DEFAULT '' COMMENT '标题',
l_url VARCHAR(512) NOT NULL DEFAULT '' COMMENT '链接',
l_created DATETIME DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
l_updated DATETIME DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (lid)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
-- 幻灯片广告
DROP TABLE bok_slideshow;
CREATE TABLE bok_slideshow (
wid INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Slide Show ID',
wtitle VARCHAR(255) NOT NULL DEFAULT '' COMMENT '幻灯片标题',
wcontent TEXT COMMENT '幻灯片内容',
wurl VARCHAR(255) NOT NULL DEFAULT '' COMMENT '幻灯片地址',
turl VARCHAR(250) NOT NULL DEFAULT '' COMMENT '点击跳转地址',
PRIMARY KEY (wid)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
ALTER TABLE bok_slideshow ADD thu_img VARCHAR(250) NOT NULL DEFAULT '' COMMENT '缩略图后台显示';
ALTER TABLE bok_slideshow ADD turl VARCHAR(250) NOT NULL DEFAULT '' COMMENT '点击跳转地址',
SHOW TABLES;
SELECT * FROM iot_slide_show;
DESC bok_slideshow;
SELECT * FROM bok_slideshow;
CREATE TABLE bok_register(
uid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(38) NOT NULL,
usex ENUM('man','woman'),
uphone VARCHAR(11) NOT NULL,
uemail VARCHAR(30) NOT NULL,
uintroduce TEXT NOT NULL
)ENGINE=MYISAM CHARSET=utf8;
转载于:https://blog.51cto.com/dreamingo/1329513