1, the system management(1) to connect MySQL
Format: mysql -h host address - u username - p user password
Example 1: connect to the MySQL on the unit,
Hadoop @ ubuntu: ~ $mysql - uroot - pmysql;
Example 2: connect to the remote host to MYSQL,
Hadoop @ ubuntu: ~ $mysql - h 127.0.0.1 uroot - pmysql;
(2) to modify new password
The terminal input: mysql -u username -p password, enter into the mysql,
The use of mysql;
Update the user set the password=password (' new password ') where the user='user name';
Flush privileges; # update permissions
quit; # exit
(3) adding new user
Format: grant select on the database. * to login user name @ host identified by 'password'
Example:
Example 1: add a user test1 password for ABC, let he can login on any of the host, and for all database has
Query, insert, modify, and delete permissions, first of all to the root user connected to MySQL, then type the following command:
Mysql> Grant, the select, insert, update, delete on *. * to root @ localhost identified by 'mysql';
Or
Grant all privileges on *. * to root @ localhost identified by 'mysql';
Then set permissions,
Flush privileges;
Case 2: if you don't want to be in the password database operation "mydb" root of the data table, can call off a command to password,
Grant, the select, insert, update, delete on mydb. * to root @ localhost identified by ';
(4) delete user
Hadoop @ ubuntu: ~ $mysql -u username -p password
Mysql> The delete from the user where the user='user name' and host='localhost'
Mysql> Flush privileges;
//delete user database
Mysql> The drop database dbname;
2, the database operations
(1) shows all database
Mysql> Show the databases; There is a s (note: the last)
(2) create a database
Mysql> The create database test;
(3) to connect to the database
Mysql> Use the test;
(4) to view the current database used by
Mysql> Select the database ();
(5) of the table information included in the current database
Mysql> Show tables; There is a s (note: the last)
(6) to delete the database
Mysql> Drop the database test;
3, table operation
Note: before the use of "use & lt; The database name & gt;" Should some database connection,
(1) to build table
Command: create table & lt; The table name & gt; (
Example:
Mysql> The create table MyClass (
Id int (4) not null primary key auto_increment,
The name char (20) not null,
Sex int (4) not null default '0',
Degree double (16, 2));
(2) the access table structure
Command: desc table names, or show the columns from the table name
Example:
Mysql> The describe MyClass
Mysql> Desc MyClass.
Mysql> Show the columns from MyClass;
(3) delete table
Command: drop table & lt; The table name & gt;
For example: delete table table called MyClass
Mysql> Drop table MyClass.
(4) insert data
Command: insert into & lt; The table name & gt; [(& lt; field name 1 & gt; [,., & lt; field name n & gt;])] values (1) [, value (n)]
Example:
Mysql> Insert into MyClass values (1, 'Tom', 96.45), (2, 'Joan, 82.99), (2,' Wang ', 96.59);
(5) the data in a query table
Query all the lines
Mysql> Select * from MyClass.
Query a few rows of data before
For example: see table MyClass first 2 rows of data
Mysql> Select * from MyClass order limit by id 0, 2;
Or
Mysql> Select * from MyClass limit 0, 2;
(6) to delete data in the table
Command: delete from the name of the table where the expression
For example: delete the Numbers for 1 record in table MyClass
Mysql> The delete from MyClass where id=1;
(7) to modify the data in the table
Command: the update table name set field=new values,... The where condition
Mysql> The update MyClass set name='Mary' where id=1;
(8) in the table field
Command: the alter table table name add the field type other;
In table, for example, the MyClass passtest, added a field in a type of int (4), the default value is 0
Mysql> The alter table MyClass add passtest int (4) the default '0'
(9) to change the name of the table
Command: rename the table the original name of the table to the new name of the table;
For example: in table MyClass name changed to YouClass
Mysql> Rename table MyClass YouClass),
(10) to update the field content
Command: the update table name set the field name=new content
Update table name set the field name=replace (field name, the 'old content', 'new');
For example: the article to join four Spaces in front of the
Update the article set the content=concat (' ', content);
3, the database import and export
Derived from the database database file * * * * * *
The use of "mysqldump" command
Enter the DOS interface first, and then for the following operation,
1) export all database
Format: the mysqldump -u [database user name] - p - A> [backup file save the path]
2) export data and data structure
Format: the mysqldump -u [database user name] -p/to backup database name & gt; [backup file save the path]
Example:
Case 1: the database mydb export to e: \ MySQL \ mydb SQL file,
Open the start - & gt; Run - & gt; Type "CMD", enter the command line mode,
C: \ & gt; The mysqldump -h localhost -u root -p mydb & gt; E: \ MySQL \ mydb SQL
Then enter the password, wait for a while export success, can to the target file to check whether success,
Example 2: the database mydb mytable of export to e: \ MySQL \ mytable SQL file,
C: \ & gt; The mysqldump -h localhost -u root -p mydb mytable> E: \ MySQL \ mytable SQL
Example 3: the structure of the database mydb export to e: \ MySQL \ mydb_stru SQL file,
C: \ & gt; The mysqldump -h localhost -u root -p mydb - add - drop - table & gt; E: \ MySQL \ mydb_stru SQL
Note: - h localhost can be omitted, its general use in the virtual host,
3) export data not only export data structure
Format:
Mysqldump -u/database username - p - t [to backup database name] & gt; [backup file save the path]
4) is derived in the database Events
Format: the mysqldump -u/database username - p - E/database user name & gt; [backup file save the path]
5) export database stored procedures and functions of the
Format: the mysqldump -u/database username - p - R/database user name & gt; [backup file save the path]
Database from the external file to import * * * * * *
1) the use of "source" command
Enter "mysql" command console first, and then create a database, and then use this database, perform the following operation finally,
Mysql> The source/backup file save path
2) the use of "& lt;" The symbols
Enter the "mysql" command console first, and then create a database, and then exit the mysql, entering DOS interface, perform the following operation finally,
Mysql -u root -p & lt; [backup file save the path]
CodePudding user response:
The first collection, just in learning