What are the SQL statements commonly used in MySQL such as table building?
This article will explain in detail what MySQL commonly used SQL sentences such as table building are, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
Build a table:
DROP TABLE IF EXISTS bulletin CREATE TABLE bulletin (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, # primary key uid INT (11) NOT NULL DEFAULT 0, # creator id context VARCHAR (600) NOT NULL DEFAULT'', # announcement details (300 words) begintime DEC (20) NOT NULL DEFAULT 0, # announcement start time endtime DEC (20) NOT NULL DEFAULT 0, # announcement end time createtime DEC (20) NOT NULL DEFAULT 0 # creation time modifytime DEC (20) NOT NULL DEFAULT 0 # modification time PRIMARY KEY (`Id`),) DEFAULT CHARSET=UTF8 TYPE=INNODB
Modify the original field name and type:
ALTER TABLE bulletin CHANGE uid username VARCHAR (50) NOT NULL DEFAULT''
Add a new field:
Alter table bulletin add citycode varchar (6) not null default 0; # City Code
1. Set the encoding when you create the database
Create database test character set utf8
two。 Set encoding when creating a table
Create table test (id int primary key) DEFAULT charset=utf8
3. Modify database coding
Alter database test character set utf8
4. Modify table default coding
Alter table test character set utf8
5. Modify field coding
Alter table test modify col_name varchar (50) CHARACTER SET utf8
Add index method
1. Add PRIMARY KEY (primary key index)
Mysql > ALTER TABLE `table_ name` ADD PRIMARY KEY (`column`)
two。 Add UNIQUE (unique index)
Mysql > ALTER TABLE `table_ name` ADD UNIQUE (`column`)
3. Add INDEX (normal index)
Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column`)
4. Add FULLTEXT (full-text index)
Mysql > ALTER TABLE `table_ name` ADD FULLTEXT (`column`)
5. Add multi-column index
Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column1`, `column2`, `column3`) this is the end of the article on "what are the common SQL statements such as table building in MySQL". I hope the above content can be helpful to you so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
