MySQL common statements, commands (add and revise function)

Modifying the character set of a database
Mysql>use MyDB
Mysql>alter database mydb character set UTF8;
Create a database character set for a specified database
Mysql>create database mydb character set UTF8;

View database's character set!

Show variables like ' collation_% ';
Show variables like ' character_set_% ';

First, the system operation

1. Open the service: net start MySQL (when MySQL is configured, the name can be customized)

2. Shutdown service: net stop MySQL

3. Enter MySQL from cmd mode

(1). mysql-u User Name-P Enter > enter correct password > Enter Welcome

(2). Mysql-h IP (native localhost)-u user name-P Enter > enter correct password > Enter Welcome

3. Exit: Exit/quit;

4. Modify user password: mysqladmin-u user name-p password New password

5. Add an Administrator account: Grant all on * * to [email protected] identified by "password";

Second, Delete and change the statement

2. Displays the name of the table field:d escribe ;
   
4. Current Library data table structure : show tables;
6. ALTER table [table name ] ADD COLUMN [field name ] DATATYPE
8. ALTER TABLE [table name ] ADD PRIMARY key ([field name ]) Description: Change the definition of a table to set a field as the primary key.   
10. Add: INSERT into [id,name ... Table name] VALUES (' ', ' Wang Le ',......   sequentially arranged data); Or:insert into table name (id,name) VALUES (0, ' Yin ')
12. Delete: delete from [table name] WHERE ([condition]); Delete columns in table: ALTER TABLE name drop column name;
14. Modify:UPDATE [table name ] SET [modify content such as name = ' Mary ' column name = ' new value, non-numeric plus single quotation mark '] WHERE [condition such as: id=3];
16. Data-Incoming command, load data local infile "[File name ]" into table [table name ];
18. Paged query: Select *from table name Limit number offset offset per page;
20. CREATE TABLE table name (ID int auto_increment primary key,name varchar) DEFAULT CHARSET=GBK /c1>
22. Add primary FOREIGN key: ALTER TABLE exterior name add constraint fk_ name foreign KEY (outer column) references main table name (primary column)

If the OID column of the existing two table primary table Tbl_order child table tbl_orderdetail the current child table Tbl_orderdetail refers to the OID column of the main table Tbl_order, the command is as follows:

ALTER TABLE Tbl_orderdetail add constraint fk_oid foreign key (OID) references Tbl_order (OID);

Query time: Select Now ();

Query Current User: Select User ();

Querying database version: Select version ();

Query the database currently in use: select databases ();

Third, Operation instructions

1. Delete the students data sheet from the Student_course database:

Rm-f student_course/students.*

2. Back up the database: (Backup DATABASE Test)

Mysqldump-u root-p Test>c:\test.txt

Backup table: (Back up the MyTable table under test database)

Mysqldump-u root-p Test mytable>c:\test.txt

Import the backup data into the database: (Back to test database)

Mysql-u root-p Test

//

MySQL database import and Export

Import: Mysql-uroot-ptian test<test.sql
Export: Mysqldump-uroot-ptian test>test.sql

Where-uroot represents the user name

-ptian means password

Test indicates the database name (already exists)

Test.sql represents the external script file (file name, format, for example: A.sql,a.abc ... ）

3. Create temporary table: (Create temp table Zengchao)

Create temporary table Zengchao (name varchar (10));

4. Copy table: CREATE TABLE table2 select * FROM table1;

5, rename the table ALTER TABLE table1 rename as table2;

6. Modify the type of the column

ALTER TABLE table1 Modify ID int unsigned;//The type of the modified column ID is int unsigned

ALTER TABLE table1 change ID sid int unsigned;//the name of the modified column ID is SID, and the property is modified to int unsigned

7. Create an index ALTER TABLE table1 ADD index ind_id (ID);

8, union character or multiple columns (Connect column ID to ":" and column name and "=")

Select Concat (ID, ': ', Name, ': ', age) as student ages from students;

9, add a user test2 password for ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb operation

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by \ "Abc\"; If you want the user to be able to log on to MySQL on any machine , change localhost to "%".

MySQL common statements, commands (add and revise function)