1. view the database version SQL-status; 1. netstartmysql starts mysql Service 2. netstopmysql stops mysql service 3. mysql-h host address-u user name-p User Password enters mysql database 4. quit exits mysql operation
1. view the database version SQL-status; 1. net start mysql // start mysql Service 2. net stop mysql // stop mysql service 3. mysql-h host address-u user name-p User Password // enter mysql database 4, quit // exit mysql operation-5, mysqladmin-u username-p old password new password // change
1. Check the database version SQL> status;
1. net start mysql // start the mysql Service
2. net stop mysql // stop mysql Service
3. mysql-h host address-u user name-p User Password // enter the mysql database
4. quit // exit mysql
-
5. mysqladmin-u username-p old password new password // Change password
6. grant select on database. * to username @ login host identified by "password" // Add new user
Exemple:
Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "abc ";
If you do not want test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified "";
Ii. Database Operations
1. show databases; // list databases
2. use database_name // use database_name Database
3. create database data_name // create a database named data_name
4. drop database data_name // delete a database named data_name
Iii. Table operations
1. show databases; // list all databases
Use Database Name; // enter a database
Show tables // list all tables
Create table tab_name (
Id int (10) not null auto_increment primary key,
Name varchar (40 ),
Pwd varchar (40)
) Charset = gb2312; create a new table named tab_name
2. drop table tab_name delete a data table named tab_name
3. describe tab_name // display the data structure of the table named tab_name
4. show columns from tab_name // same as above
5. delete from tab_name // clear records in Table tab_name
6. select * from tab_name // displays records in tab_name.
7. mysqldump-uUSER-pPASSWORD -- no-data database table> table. SQL // copy the TABLE structure
4. Modify the table structure
1. alter table tab_name add primary key (col_name)
Note: You can change the table definition to set a column as a primary key.
2. alter table tab_name drop primary key (col_name)
Delete the definition of the primary key
3. alter table tab_name add col_name varchar (20); // add a field named col_name in the tab_name table and its type is varchar (20)
4. alter table tab_name drop col_name // Delete the col_name field in tab_name
5. alter table tab_name modify col_name varchar (40) not null // modify the field attribute. Note that if not null is added, no data under the original field is required.
The statement in SQL Server200 is: Alter Table table_name Alter Column col_name varchar (30) not null;
6. How to modify the table name: alter table tab_name rename to new_tab_name
7. How to modify the field name: alter table tab_name change old_col new_col varchar (40); // you must specify the data type and other attributes for the current field; otherwise, the field cannot be modified.
8. create table new_tab_name like old_tab_name // use an existing table to create a new table, but it does not contain the data of the old table.
V. data backup and recovery
Import external data text:
1. Execute External SQL scripts
Run mysql <input. SQL on the current database.
Run the following command on the specified database: mysql [Table name] <input. SQL
2. data Import command load data local infile "[file name]" into table [table name];
Back up the database: (under dos)
Mysqldump -- opt school> school. bbb
Mysqldump-u [user]-p [password] databasename> filename (Backup)
Mysql-u [user]-p [password] databasename <filename (restored)
6. Uninstall
Uninstall mysql: sudo apt-get remove mysql-server mysql-client
Sudo apt-get autoremove
