数据库和数据表的示例

-- 创建test1 测试整数类型

CREATE TABLE IF NOT EXISTS test1(

n1 TINYINT,

n2 SMALLINT,

n3 MEDIUMINT,

n4 INT,

n5 BIGINT  -- 如果是最后一个 我们后面不要出现逗号

)ENGINE=MyISAM DEFAULT CHARSET =utf8;

-- 查看表结构 

-- DESC 表名

DESC test1;

-- 如果你想要测试括号中的数字是做什么的 请写上零填充属性

-- 零填充属性

CREATE TABLE IF NOT EXISTS test3(

n1 INT(5) ZEROFILL

)ENGINE =MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test3 VALUES(100);

-- test 4 测试长度使用

CREATE TABLE IF NOT EXISTS test4(

n1 INT(12),

n2 INT(12) ZEROFILL

)ENGINE =MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test4 VALUES(1,1);

INSERT INTO test4 VALUES(123456789011,123456789011);

-- test5 测试无符号状态

-- UNSIGNED 无符号状态

CREATE TABLE IF NOT EXISTS test5(

n1 TINYINT,

n2 TINYINT UNSIGNED

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test5 VALUES(-129,0);

INSERT INTO test5 VALUES(-128,0);

INSERT INTO test5 VALUES(127,0);

INSERT INTO test5 VALUES(-127,-1);

INSERT INTO test5 VALUES(-127,128);

INSERT INTO test5 VALUES(-127,256);

INSERT INTO test5 VALUES(-127,255);

-- 设置年龄的字段

age  TINYINT UNSIGNED,

-- 整数类型还有一个属性 AUTO_INCREMENT 属性    自增

-- 自增属性需要配合索引使用

-- test6 测试自增

CREATE TABLE IF NOT EXISTS test6(

n1 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 如果出现下面报错 请在你自增属性后面加上你的索引内容

-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

-- key(index) 索引

-- 普通索引(index)

-- 唯一索引(unique)  唯一索引所有值不能有重复

-- 主键索引primary key  一个数据表中只能有一个  唯一作用 一般设置都是自增字段

INSERT INTO test6 VALUE(NULL);

-- 浮点数 小数 

-- 定点数  decimal来表示小数

CREATE TABLE IF NOT EXISTS test7(

n1 DECIMAL(5,2)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test7 VALUES(12345);

INSERT INTO test7 VALUES(123);

CREATE TABLE IF NOT EXISTS test8(

n1 DECIMAL(5,5)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 存储php的时间内容

CREATE TABLE IF NOT EXISTS test9(

time INT UNSIGNED

)ENGINE=MyISAM DEFAULT CHARSET =utf8;

INSERT INTO test9 VALUES(1510023998);

-- test10 测试字符串类型

CREATE  TABLE IF NOT EXISTS  test10(

str1 CHAR(5),

str2 VARCHAR(5)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test10 VALUES('123','123');

INSERT INTO test10 VALUES('123456','123456');

INSERT INTO test10 VALUES('胃你好帅哥','胃你好帅哥');

CREATE  TABLE IF NOT EXISTS  test11(

sex ENUM('man','woman','girl','boy','funv','oldwoman','renyao','one-in-two')

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test11 VALUES('man');

INSERT INTO test11 VALUES('newman');

INSERT INTO test11 VALUES('man','woman');

INSERT INTO test11 VALUES(null);

CREATE TABLE IF NOT EXISTS test12(

lovegood SET('man','woman','taiyang','吹牛','dongjinghot','jianfeizao','jianmuyulu','makelove','shopping','football')

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test12 VALUES('man');

INSERT INTO test12 VALUES('oldman');

INSERT INTO test12 VALUES('man,吹牛,dongjinghot,taiyang,jianfeizao');

INSERT INTO test12 VALUES('jianmuyulu,makelove');

-- NOT NULL 

CREATE  TABLE IF NOT EXISTS test13(

str VARCHAR(255) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test13 VALUES(NULL);

INSERT INTO test13 VALUES('');

CREATE  TABLE IF NOT EXISTS test14(

str VARCHAR(255) NOT NULL,

str2 VARCHAR(255) NOT NULL DEFAULT '哇擦'

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test14(str) VALUES('1');

--  根据学的内容创建一个表

CREATE TABLE IF NOT EXISTS myuser(

id INT  UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255) NOT NULL  UNIQUE,

password CHAR(32) NOT NULL DEFAULT '',

age  TINYINT UNSIGNED  NOT NULL DEFAULT 0,

sex  TINYINT NOT NULL DEFAULT 0,

reg_time INT NOT NULL DEFAULT 0,

sign  TEXT

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO myuser VALUES(NULL,'user1',md5('123456'),18,0,1510023998,'不抛弃，不放弃，勇于担当，敢于亮剑');

INSERT INTO myuser VALUES(NULL,'user2',md5('123456'),18,0,1510023998,'让学习成为一种习惯');

INSERT INTO myuser VALUES(NULL,'user3',md5('123456'),18,0,1510023998,'让我成为一个习惯');

-- 修改表名

ALTER TABLE myuser RENAME TO  user1;

ALTER TABLE user1 RENAME TO  myuser;

-- 修改字段数据类型

ALTER TABLE myuser MODIFY age INT NOT NULL DEFAULT 0;

ALTER TABLE myuser MODIFY age TINYINT UNSIGNED NOT NULL DEFAULT 0;

-- 修改字段名 

ALTER TABLE myuser CHANGE age myage TINYINT UNSIGNED NOT NULL DEFAULT 0;

ALTER TABLE myuser CHANGE myage age TINYINT UNSIGNED NOT NULL DEFAULT 0;

-- 增加字段

ALTER TABLE myuser ADD city VARCHAR(255) NOT NULL DEFAULT '地球村';

ALTER TABLE myuser ADD lovegood SET('邱亮','avi','mp4','vob','girl') NOT NULL FIRST;

ALTER TABLE myuser ADD  tel CHAR(11) NOT NULL AFTER age;

-- 删除字段

ALTER TABLE myuser DROP lovegood;

ALTER TABLE myuser DROP city;

ALTER TABLE myuser DROP tel;

ALTER TABLE myuser DROP phone;

-- 字段排列位置

ALTER TABLE myuser MODIFY reg_time INT NOT NULL AFTER age;

ALTER TABLE myuser MODIFY reg_time INT NOT NULL AFTER sex;

-- 修改表引擎

ALTER TABLE myuser ENGINE =InnoDB;

ALTER TABLE myuser ENGINE =MyISAM;

-- 数据量大的时候 不建议像上面一样去修改表引擎 而是先创建一个和myuser表一样的表 然后把空表的引擎修改为我们要修改的引擎之后将数据导入到新表中即可

-- 将自增改为现在的最大值

ALTER TABLE myuser AUTO_INCREMENT =0;