數據庫和數據表的示例

-- 創建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;