Installation and use of MySQL

Install MySQL

from MSSQL Guide data to MySQL:

sqlyog with Visual Tools

refer to: http://www.cnblogs.com/gaizai/p/3237907.html

MySQL and MSSQL Syntax differences:

Difference	MSSQL	Mysql
Comments	Single-line Comment:-- Paragraph notes:/*abc*/	Single-line comment: // or # or -- (one more space) Multi-line comments : /*abc*/
Stored Procedures	CREATE PROC test1 (@s BIGINT) BEGIN ... END	CREATE PROCEDURE test1 (ins BIGINT) BEGIN ... END ;
modifying columns	Alter table alter column columns Type	ALTER TABLE Table Change Old column name New column name type ;
Delete primary key	alter table table   drop primary Key name	ALTER TABLE Table drop primary key;

using Navicat for MySQL

Press F6(Tools - Command column interface) to enter the command line

650) this.width=650; "style=" width:500px;height:340px; "src=" http://s3.51cto.com/wyfs02/M01/71/EA/ Wkiol1xa1hoatqmyaakjsu8ehbo013.jpg "title=" image 1.png "height=" 340 "hspace=" 0 "border=" 0 "vspace=" 0 "width=" alt= " Wkiol1xa1hoatqmyaakjsu8ehbo013.jpg "/>

or open the Query window

650) this.width=650; "style=" width:500px;height:340px; "src=" http://s3.51cto.com/wyfs02/M01/71/ED/ Wkiom1xa0nsbnl-kaaldzknngpk199.jpg "title=" image 2.png "height=" 340 "hspace=" 0 "border=" 0 "vspace=" 0 "width=" alt= " Wkiom1xa0nsbnl-kaaldzknngpk199.jpg "/>

table Operation:

To create a table:

mysql> CREATE TABLE t1 (id bigint,name nvarchar (200));

Query OK, 0 rows affected

To modify a table:

Mysql> ALTER TABLE t1 add addtime datetime;

mysql> ALTER TABLE T1 drop column addtime;

mysql> ALTER TABLE t1 change name score int;

To view the table structure:

mysql> desc T1;

Mysql> show columns from T1;

Mysql> Show CREATE table T1;

To delete a table:

mysql> drop table T1;

Index Operations:

New index:mysql> CREATE index XID on T1 (ID);

Mysql> ALTER TABLE T1 ADD index XID (ID);

Delete indexes:mysql> DROP index xID on T1;

mysql> ALTER TABLE T1 DROP INDEX XID;

View index:mysql> Show index from T1;

Rebuild index:mysql> repair table T1 quick;

Index statistics: Right - Object Information

PRIMARY KEY operation:

Add primary key:mysql> ALTER TABLE T1 add primary key (ID);

Delete primary key:mysql> ALTER TABLE T1 drop PRIMARY key;

FOREIGN key operation:

To add a foreign key:

Mysql> ALTER TABLE Testtag add constraint fk_test foreign key (ClassID) references TestClass (ID);

Delete foreign key:mysql> ALTER TABLE testtag DROP FOREIGN key ' fk_test ';

Data manipulation:

New data:

mysql> INSERT INTO T1 (id,name)

Select 123456, ' Ssssk ';

mysql> INSERT INTO T1

VALUES (111111, ' ASD ');

mysql> INSERT INTO T1

-Select 111112, ' ABC ' UNION ALL

Select 111113, ' ABCD ';

Query data:

Mysql> SELECT * from T1 where id=123456;

mysql> SELECT ID from TEST ORDER by ID LIMIT 0,3;// display first three rows

Mysql> select DISTINCT ID from TestClass;

Mysql> Select Addy,count (1) as digit from test group by Addy Order by count (1);

Update data:

mysql> Update T1 set name= ' ssss ' where id=123456;

Delete data:

mysql> Delete from T1 where id=111111;

Stored procedures:

(1) Output Parameters:

Mysql> CREATE PROCEDURE test (out s int)

Begin

Select COUNT (1) into S from Tabletest;

-End;

Call a stored procedure

Mysql> call Test (@a);

Query OK, 1 row affected

Mysql> Select @a;

+---------+

| @a |

+---------+

| 1080227 |

+---------+

1 row in Set

(2) Input Parameters:

Mysql> CREATE PROCEDURE test1 (in S BIGINT)

BEGIN

-A SELECT ID from Tabletest WHERE id=s;

-END;

Query OK, 0 rows affected

Mysql> call Test1 (610815855784);

+--------------+

| ID |

+--------------+

| 610815855784 |

+--------------+

1 row in Set

Query OK, 0 rows affected

(3) Table connection:

Mysql> CREATE PROCEDURE test2 (in S BIGINT)

BEGIN

SELECT a.id,b.id,c.id from Testtag a LEFT join Testontag B on a.id=b.tagid left JOIN Test C on B.questionid=c.id WHE RE c.id=s;

-END;

Query OK, 0 rows affected

Mysql> call Test2 (24);

| 24 | 819070732577611835 | NULL |

| 24 | 821895448838848274 | NULL |

| 24 | 822129946375574167 | NULL |

| 24 | 822137865163194930 | NULL |

+----+--------------------+------+

17550 rows in Set

This article is from the "Sukun" blog, make sure to keep this source http://sukunwu.blog.51cto.com/10453116/1687695

Installation and use of MySQL