MySQL daily operation

< <= >= > = between in without% or _ starts with an index operation

1. See in which tables a field is in:

Use INFORMATION_SCHEMA;

Select table_name form columns where column_name= ' xxx '

2. View the statement of the build table

Show CREATE TABLE TableName

3. Modifying the data type of a column

Alert Table table name modify column column name the type of the new columns

Alert Table example Modify column col1 varchar (10)

Modify only the column names, or modify the data types of both the specified and the columns

Alert table table name change column old column name new column type

Alert Table Example Change column sname stuname varchar (10)

4. This version of the MySQL doesn ' t yet support ' LIMIT & in/all/any/some subquery ' means that

This version of MySQL does not support in/all/any/some subqueries that use the limit clause, which is a limit subquery that supports non-in/all/any/some subqueries.

In other words, such statements are not executed correctly.

SELECT * FROM table where ID in (select id from table limit 10)

But, as long as you have another layer on the line. Such as:

SELECT * FROM table where ID in (select t.id from (SELECT * from table limit) as T)

5, three table join

Select xxx from ((A left join B on a.id = b.id)

Left Join C on a.id = c.id

Where b.id is not null

SELECT * FROM A inner join B on a.id = a.id inner join C on b.id = c.id

6. Add a field

Alert Table tablename Add column fieldname DataType

7. Copy the table

CREATE TABLE table2 SELECT * FROM table1

Renaming the table

Alert Table table1 Rename as Table2

Modifying the type of a column

Alert table table1 Modify ID int unsigned

Create an index

Create (unique unique index) index ide_id on table1 (ID)

Delete Index

Drop Index inx_id on table1

8, Linux into the MySQL mode:

1) Enter the MySQL installation directory under the Bin folder to execute./mysql-uroot-p

2) Export Path=/usr/local/mysql/bin: $PATH This method is closed at the terminal

Will fail when the time comes.

3) Vim/ect/profile

Added at last: Export path= "/usr/local/mysql/bin: $PATH" Save, exit

Source/ect/profile not error is successful

1. View the number of connections to the database:

SELECT Summary_id,count (*) as COUNT from Pe_project_summary_basic GROUP by summary_id have count>1;

excerpt from MySQL website Modify table/database character encodingALTER DATABASE mydb character set utf-8;ALTER TABLE Conf_dictitem convert to character set UTF8 4. Foreign Key ExampleCREATE TABLE person (ID SMALLINT UNSIGNED NOT NULL auto_increment, name CHAR is not NULL, PRIMARY KEY (id)); CREATE TABLE shirt (id SMALLINT UNSIGNED NOT NULL auto_increment, style ENUM (' t-shirt ', ' polo ', ' dress ') is not NULL, color The ENUM (' Red ', ' blue ', ' orange ', ' white ', ' black ') is not NULL, the owner SMALLINT UNSIGNED not NULL REFERENCES person (ID), PRIMARY KEY (ID)); INSERT into Person VALUES (NULL, ' Antonio Paz '); SELECT @last: = last_insert_id (); INSERT into shirt VALUES (null, ' Polo ', ' Blue ', @last), (null, ' Dress ', ' white ', @last), (NULL, ' T-shirt ', ' Blue ', @last); INSERT into Person VALUES (NULL, ' Lilliana Angelovska '); SELECT @last: = last_insert_id (); INSERT into shirt VALUES (null, ' Dress ', ' orange ', @last), (null, ' polo ', ' red ', @last), (null, ' Dress ', ' blue ', @last), ( NULL, ' t-shirt ', ' white ', @last); SELECT s.* from person p INNER JOIN shirt s on s.owner = P.id WHERE p.name like ' lilliana% ' and S.color <> ' whit E '; 3. SELECT @min_price: =min (Price), @max_price: =max-shop; SELECT * from shop WHERE [email protected]_price OR [email protected]_price; 1. Matching statementsSELECT * from pet WHERE name REGEXP ' W ';contains the W character, ^w the character beginning with W, w$ the character ending with W, ' ^.....$ ' is five characters long (in Chinese, one Chinese is 3 ..., i.e. one Chinese is three characters), ' ^. {6}$ ' the same effect as previous 2. LOAD DATA LOCAL INFILE ' event.txt ' into TABLE event will txt fileImport the event tableExecute SQL file on Linux, source Xxx.sql CREATE TABLE Shop (article INT (4) UNSIGNED zerofill default ' 0000 ' NOT NULL, dealer CHAR (default "not NULL, PRI Ce DOUBLE (16,2) DEFAULT ' 0.00 ' not NULL, PRIMARY KEY (article, dealer)); INSERT into the shop VALUES (1, ' a ', 3.45), (1, ' B ', 3.99), (2, ' a ', 10.99), (3, ' B ', 1.45), (3, ' C ', 1.69), (3, ' d ', 1.25), (4, ' d ', 19.95);

MySQL daily operation