MySQL command line data operation usage Experience (summary version)

Char 0~255

Varchar 0~65535

Text 0~65535 (only characters are saved)

Longtext 0~4294967295 (only characters are saved)

CMD login MySQL

Mysql-u root-p

Mysql-p 3306-uroot-p123456 (-p must be uppercase to indicate port)

1, database operation databases

Create a database

Create Database

Display Database

Show databases

Deleting a database

Drop databases < database name >

Connecting to a database

Use < database name >

View the database currently in use

Select Database ()

Display current Database information

Show tables

Import SQL database

Mysql-u root-p creat database name//create a database with the same name first

Mysql-u root-p Database name < database local address

Use database name//update database

Show Table//Display new Import table

Import. sql file commands (for example, D:/mysql.sql)

Use database;

SOURCE D:/mysql.sql;

Import and Export database (solve coding problems)

Mysql-uroot-p--default-character-set=utf8 Joke<c:/joke.sql

Mysqldump–uroot–p joke Table>joke.sql

Export (Backup) database

Mysqldump-u root-p database name >c:/file name. sql

2, table operation tables

Create a table

Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);

CREATE TABLE MyClass (
ID int,
Name Char (a) is not NULL,
Sex enum (' f ', ' m '));

Mysql> CREATE TABLE classify (

->title varchar (+) Primary key,content Longtext,author Longtext,board Lon

Gtext,datatype longtext);

Get table structure

Describe MyClass

Desc MyClass;

Change table name

Rename table name to new table name

Delete a table

DROP table < table name >

Clear table

Delete from table name

Insert a record in a table

Insert two records into table MyClass, these two records indicate:

Number 1, named Tom, has a score of 96.45.

A score of 2 named Joan is 82.99.

The result, named Wang, numbered 3, was 96.5.

INSERT into Myclassvalues (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

insertinto Table name VALUES ("HyQ", "M");

Updating data in a table

Update MYTABLE set sex= "F" wherename= ' hyq ';

To load data into a database table in text mode (for example, D:/mysql.txt)

Load data local infile "D:/mysql.txt" intotable mytable;

Delete data from a table

Delete from table name where expression

Delete from MyClass where id=1;

MySQL Bulk replace specified field string statement

UPDATE data table name set field name = Replace (field name, ' String to replace ', ' replace with ') WHERE set condition;

Update xxx Set column = Repalce (COLUMN,CHR (ASCII), '); Oracle

Update Jo1 set content =replace (content, char (10), "+char"); Carriage return + line break

3, Field operations

Add fields to the table:

ALTER TABLE name add field type other;

For example: Added a field passtest in table MyClass, type int (4), default value of 0

ALTER TABLE MyClass add passtest int (4) Default ' 0 '//4 can be omitted, length defaults to 11

Delete a field

ALTER TABLE table name drop column field name

Update Field Contents

Update table name set field name = new Content

Update table name set field name = Replace (field name, ' old content ', ' new content ');

Add 4 spaces to the front of the article

Update article Set Content=concat (", content);

modifying field types

ALTER TABLE forum ALTER COLUMN column_name New_data_type

4, Index

Improve the speed of search and query, create maintenance time, occupy physical space, and affect the speed record of inserting records. (FIX: Delete index and insert record first)

Build an index

Create table Index1 (index (ID));

ER table forum alter COLUMN column_name New_data_type

5, query operation

Query table

SELECT * from MyClass;

Querying the first few rows of data

SELECT * from MyClass ORDER by ID limit 0, 2;

The total number of data in the query table

Select COUNT (*) from table

Field type

1. int[(M)] Type: normal size integer type

2. double[(m,d)][zerofill] Type: normal size (double precision) floating-point number type

3. Date Type: The supported range is from 1000-01-01 to 9999-12-31. MySQL Displays the date value in YYYY-MM-DD format, but allows you to assign a value to the date column using a string or a number

4. CHAR (M) Type: fixed-length string type, when stored, always fills the right with a space to the specified length

5. The BLOB text type, with a maximum length of 65535 (2^16-1) characters.

6. VARCHAR Type: variable-length string type

View MySQL version

Select @ @Version

View MySQL default encoding

Show variables like ' character% ';

Show variables like "character%";

..

Set MySQL encoding

MySQL ( version 5.1.22) supports multiple encoding formats and can be set in different encoding formats at multiple levels.

• Server-level : You can set the default encoding format for the server when you install MySQL
• The simplest way to modify this is to modify the character set key values in the MySQL My.ini file.
  such as: Default-character-set = UTF8
  Character_set_server = UTF8
  After the modification, restart the MySQL services, service MySQL restart
• database-level : You can specify the encoding format of the database when you create the database, or, if you do not, inherit the encoding format of the server by default. Examples are as follows:

CREATEDATABASE   DEFAULTSET  UTF8;  

• table-level : In MySQL You can specify different encoding formats for tables in the same database, if you do not specify the encoding format that will inherit the database. Examples are as follows:

CREATETABLE ' Db_name '.  Tb_name '  (       ID varchar (20)  notnull    ,     name Varchar (20) )  Engine=innodb   default    Charset=UTF8;    

• column level : You can also specify a different encoding format for different columns in the same table, as shown in the following example:

CREATETABLE  ' db_name '.  ' Tb_name '( ID varchar() notNULL, name varchar(  ) CHARACTER SET  UTF8);           

One of the benefits of developing a coding method is to enhance the processing of Chinese, which can solve the problem of garbled content in some parts of the table in different tools.

1. Specific inquiries

SELECT Content from Forum

WHERE content= ' narrowing the gap between rich and poor is a tangled topic, how to shrink? The key is income distribution. The people with great power of discourse, the income distribution is obviously high, resulting in a widening income gap '

2. Search carefully

SELECT content,

DATE,

Url

Id

Objectkey,

Robotname,

Executionid,

Firstextracted,

Lastextracted,

Extractedinlastrun,

LastUpdated,

Num

From Aeolus.forum WHERE id= ' coax the flag to fly '

3. Fuzzy query

SELECT field from table

WHERE field like ' i % '

4. Blur Delete

DELETE from Aeolus.forum whereid like '% ';

crlf–

Carriage-return line-feed return carriage return (CR, ASCII, \ r) line break (LF, ASCII, \ n)

MySQL command line data operation usage Experience (summary version)