Examples of databases and data tables

--Create test1 test integer type

CREATE TABLE IF NOT EXISTS test1(

n1 TINYINT,

n2 SMALLINT,

n3 MEDIUMINT,

n4 INT,

n5 BIGINT --if it's the last one, we don't want commas after it.

)ENGINE=MyISAM DEFAULT CHARSET =utf8;

--View table structure

-- DESC table name

DESC test1;

--If you want to test what the numbers in parentheses do, write the zero-padding attribute.

--Zero fill attribute

CREATE TABLE IF NOT EXISTS test3(

n1 INT(5) ZEROFILL

)ENGINE =MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test3 VALUES(100);

-- test 4 Test length usage

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 Test unsigned state

-- UNSIGNED unsigned state

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);

--Set the age field

age TINYINT UNSIGNED,

--Integer type also has an attribute AUTO_INCREMENT attribute autoincrement

--Self-increasing attributes need to be used with indexes

-- test6 test self-increasing

CREATE TABLE IF NOT EXISTS test6(

n1 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

--If the following error occurs, please add your index content after your self-increasing attribute

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

-- key(index) index

--general index

--Unique index (unique) Unique index All values cannot be duplicated

--primary key index primary key A data table can only have one unique role. Generally, the settings are self-increasing fields.

INSERT INTO test6 VALUE(NULL);

--Floating-point decimal

--fixed-point number decimal to represent decimals

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;

--Storing the time content of PHP

CREATE TABLE IF NOT EXISTS test9(

time INT UNSIGNED

)ENGINE=MyISAM DEFAULT CHARSET =utf8;

INSERT INTO test9 VALUES(1510023998);

-- test10 Test string type

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 ('stomach good-looking ',' stomach good-looking ');

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',' chuiniu','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, bragging,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 a table based on what you learned

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,'user 1', md5 ('123456'),18,0,1510023998,'don't abandon, don't give up, dare to take responsibility, dare to shine sword');

INSERT INTO myuser VALUES(NULL,'user 2', md5 ('123456'),18,0,1510023998,'Make learning a habit');

INSERT INTO myuser VALUES(NULL,'user 3', md5 ('123456'),18,0,1510023998,'Make me a habit');

--Modify table name

ALTER TABLE myuser RENAME TO user1;

ALTER TABLE user1 RENAME TO myuser;

--Modify field data type

ALTER TABLE myuser MODIFY age INT NOT NULL DEFAULT 0;

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

--Modify field name

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

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

--Add fields

ALTER TABLE myuser ADD city VARCHAR(255) NOT NULL DEFAULT 'global village';

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

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

--Delete fields

ALTER TABLE myuser DROP lovegood;

ALTER TABLE myuser DROP city;

ALTER TABLE myuser DROP tel;

ALTER TABLE myuser DROP phone;

--Field arrangement position

ALTER TABLE myuser MODIFY reg_time INT NOT NULL AFTER age;

ALTER TABLE myuser MODIFY reg_time INT NOT NULL AFTER sex;

--Modify Table Engine

ALTER TABLE myuser ENGINE =InnoDB;

ALTER TABLE myuser ENGINE =MyISAM;

--When the amount of data is large, it is not recommended to modify the table engine as above. Instead, create a table like myuser table, and then modify the engine of the empty table to the engine we want to modify. After that, import the data into the new table.

--Change from self increment to current maximum

ALTER TABLE myuser AUTO_INCREMENT =0;