Install MySQL and command learning under Linux

Directory:

1. Install MySQL

2. mysql file deployment

3. mysql Start-Stop command

4. Connect to the database

5. Manipulating Database commands

6. Import and export of data

7. Other

1. Install MySQL

1, install MySQL using Yun, can be installed automatically complete

Yun install MySQL mysql-server  #询问是否要安装, enter Y to install automatically, know the installation is complete

2. Set the password for the root account of MySQL

Mysql_secure_installation

Enter, follow the prompts for Y
Enter Password 2 times, enter
Enter Y as prompted
Last appearance: Thanks for using mysql!
MySQL password setup is complete, restart MySQL:

2.mysql File Deployment

MySQL installs its database files, configuration files, and command files in separate directories

1. Database Directory
/var/lib/mysql/
2. Configuration files
/usr/share/mysql (mysql.server command and configuration file) mysql5.5 after the default installation path, mysql5.5 before is/usr/local/mysql
3. Related commands
/usr/bin (Mysqladmin mysqldump and other commands)
4. Startup script
/etc/rc.d/init.d/(startup script file for MySQL directory)

3.mysql Start-Stop command

1.linux commands to start MySQL:

/etc/init.d/mysqld Start      #启动

2.linux Restart MySQL command:

/ect/init.d/mysqld Restart    #重启

3.linux the command to turn off MySQL:

/ect/init.d/mysqld shutdown   #关闭

The command to stop MySQL under 4.linux:

/etc/init.d/mysqld Stop       #停止

5. Set as boot start

/sbin/chkconfig               #查看自启动列表

4. Connect to the database

1. Connect to MySQL on this machine

Mysql-uroot-proot

2. Modify the MySQL password:

Mysqladmin-u username-p Old password password new password

5. Manipulating Database commands

1. Display the list of databases.

show databases;

2. Display the data table in the library:

Use MySQL;      #打开库show tables;     #显示所有表

3, display the structure of the data table:

 from TableName;

4, build the library:

Create database name; #指定编码格式GBK: Create Database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; Utf8:create DATABASE ' test2 ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

5, build the table:

Use library name; CREATE table table name (field settings list);

6. Deleting the library and deleting the table:

drop database name; #删库drop table name; #删表

7. Empty the records in the table:

Delete from table name;
or TRUNCATE table  name;

8. Display the records in the table:

SELECT * from table name;

9, the revision of the Code
If you want to change the entire MySQL encoding format:
When you start MySQL, the Mysqld_safe command line joins
--default-character-set=gbk
If you want to change the encoding format for a library: Enter a command after the MySQL prompt

ALTER DATABASE db_name default character set GBK;

10. Renaming a table

ALTER TABLE t1 rename T2;

11. Viewing the efficiency of SQL statements

Explain < table_name > For example: Explain select * from T3 where id=3952602;

12. Load data into a database table (for example, d:/mysql.txt) in text mode

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;

13. Query Database Information

Select Now ();       #查询时间select user ();      #查询当前用户select version ();   #查询数据库版本select database ();  #查询当前使用的数据库

6. Import and export of data

1. The text data goes to the database
The format that text data should conform to: The field data is separated by the TAB key, and the null value is used instead.

Cases:
1 Name Duty 2006-11-23
Data incoming command:

Load data local infile "file name" into table name; #只是导入数据

2. exporting databases and tables
Mysqldump is an external command that backs up all tables in the specified database:

Mysqldump-h localhost-uroot-proot databaseName >/tmp/dump.sql

3. Execute SQL file statement

Mysql>source news.sql; (Execute SQL statement table under MySQL command)

7. Other

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

3. Create temporary table: (Create temp table Zengchao)
Create temporary table Zengchao (name varchar (10));

4. Create a table to determine if the table exists first
CREATE table if not exists students (...);

5. Copying the structure of tables from existing tables
CREATE TABLE table2 SELECT * FROM table1 where 1<>1;

6. Copy the table
CREATE TABLE table2 SELECT * FROM table1;

7. Renaming the table
ALTER TABLE table1 Rename as table2;

8. 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

9. Create an index
ALTER TABLE table1 ADD index ind_id (ID);
CREATE INDEX ind_id on table1 (ID);
Create unique index ind_id on table1 (ID);//establishing a unique indexing

10. Delete Index
Drop index idx_id on table1;
ALTER TABLE table1 DROP INDEX ind_id;

11, union character or multiple columns (Connect column ID to ":" and column name and "=")
Select Concat (ID, ': ', name, ' = ') from students;

12, limit (choose 10 to 20) < The first Recordset is numbered 0>
SELECT * FROM students order by ID limit 9, 10;

13, MySQL does not support the function
Transactions, views, foreign key and referential integrity, stored procedures, and triggers

Install MySQL and command learning under Linux