MySQL Common operations (i)

Recent internship to do intelligent question and answer system, need to collect some questions and answers to, then wrote the crawler crawl data, will crawl down into the MySQL database, the middle encountered some problems, here to summarize, to facilitate future review.

The simple operation of the database is nothing more than adding and removing changes, this article summarizes the work encountered in this order.

1, increase

1.1 Creating a data table

CREATE TABLE IF  not EXISTS' qa_law66_content ' (' ID ')INTUNSIGNED auto_increment, ' title 'VARCHAR( -) not NULL, ' question 'VARCHAR( -) not NULL, ' answer 'VARCHAR(15000) not NULL, ' Cate 'VARCHAR( -) not NULL, ' source 'VARCHAR( +) not NULL,   PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

qa_law66_linksis the table name, and the single quotation marks are the fields in the table.UNSIGNED auto_incrementRepresents an auto-increment property that, when written to a data table, does not write the contents of the field, it is automatically added in numerical order.Field  names followed by field types, such as VARCHAR (15000) Represents a character type, the length of the parenthesis is the length of the set that the field can store, and an error message is raised if the stored content is longer than the set field length.
All fields if all areVARCHARTypes, the sum of all their lengths cannot exceed 65,533 characters.
not NULL Indicates that the field is not empty.
The PRIMARY key (' ID ') indicates that the ' ID ' field is set as the primary key and cannot be duplicated.
Engine=innodb represents the storage engine for creating tables, which is described in detail in this blog post.
 The default CHARSET =utf8, which is typically set to the UTF8 format, represents a data table in the format.

1.2 Adding a table field

Alter Table Add int;

Added the ' Aid ' field to the table qa_law66_content and set it to type int.

2, change

2.1 Modifying the length of a table field

Alter Table column varchar (a); Alter Table column varchar (a); Alter Table column varchar (3500);

2.2 Modifying the type of a field

ALTER TABLE INT;

2.3 Modify the primary key, modify the order of the fields

Alter Table Drop Primary Key ; Alter Table int (ten) unsigned auto_increment first,addprimarykey (aid);

To modify a primary key, first delete the primary key property, and then set the other key as the main key.

First indicates that the key is set to column one.

2.4 Modifying the Order of fields

ALTER TABLE table name
Change field name new field FirstName type default after field name (after which field is skipped)

Alter Table  int(1 after aid;

3. By deleting

3.1 Deleting an entire table

DROP TABLE lawfact;

3.2 Delete all data from table

from Lawfact;

3.3 Delete table data conditionally

DELETE  from WHERE Title="None";

4. Check

4.1 What is the data in the query table?

Select Count (* from Lawfact;

4.2 Querying all data in the table

Select *  from Lawfact;

4.3 Query by condition

Select *  from where = ' None ';

4.5 Querying a table for values that are not duplicates of a field

SELECT DISTINCT  from Lawfact;

MySQL Common operations (i)