Common commands for MySql management operations and Common commands for mysql operations

Common commands for MySql management operations and Common commands for mysql operations

Log on to mysql

mysql -u username -p

Create a user name and Configure permissions. Here, you can configure all permissions for the tablename table for this user. You can also specify

GRANT ALL PRIVILEGES ON tablename.* TO 'username'@'hostname' IDENTIFIED BY 'password';

Delete user permissions and use revoke

REVOKE ALL PRIVILEGES ON table.* FROM 'username'@'host';

Delete a user

DROP USER ‘username'@'host';

 

Basic MySQL commands

Start: net start mySql;
Enter: mysql-u root-p/mysql-h localhost-u root-p databaseName;
List databases: show databases;
Select Database: use databaseName;
List tables: show tables;
Show columns from tableName;
Create a database: source fileName.txt;
Matching character: wildcard _ can be used to represent any character, and % represents any string;
Add a field: alter table tabelName add column fieldName dateType;
Add multiple fields: alter table tabelName add column fieldName1 dateType, add columns fieldName2 dateType;
Multi-line command input: note that words cannot be broken; when data is inserted or changed, the field strings cannot be expanded into multiple rows; otherwise, the hard press enter will be stored in the data;
Add an Administrator Account: grant all on *. * to user @ localhost identified by "password ";
After each statement is entered, enter the plus sign ';' At the end, or add '\ G;
Query time: select now ();
Query the current user: select user ();
Query the database version: select version ();
Query the currently used database: select database ();

1. Delete the students data table in the student_course database:
Rm-f student_course/students .*

2. Back up the database: (back up the database test)
Mysqldump-u root-p test> c: \ test.txt
Backup table: (back up the mytable table under the test database)
Mysqldump-u root-p test mytable> c: \ test.txt
Import the backup data to the database: (import back to the test database)
Mysql-u root-p test

3. Create a temporary table: (create a temporary table zengchao)
Create temporary table zengchao (name varchar (10 ));

4. To create a table, first determine whether the table exists.
Create table if not exists students (......);

5. Copy the table structure from an existing table
Create table table2 select * from table1 where 1 <> 1;

6. Copy a table
Create table table2 select * from table1;

7. Rename the table
Alter table table1 rename as table2;

8. Modify the column type
Alter table table1 modify id int unsigned; // modify the column id type to int unsigned
Alter table table1 change id sid int unsigned; // modify the name of the column id ...... the remaining full text>

Mysql basic statements

According to their online data, they are all written in the same way. At the same time, many of them are not well written. The following is my summary of mysql usage details, it is also some of my records in the learning process. I hope it will be helpful to you. I have not summarized the stored procedure and other related operations. I will give it to you next time, haha ~~~~~

MySql Study Notes

MySql Overview: MySql is an associated database management system. The so-called associated database stores data in different tables, rather than placing all the data in a large warehouse. This increases the speed and flexibility. MySql is an open source software.

Note: The maximum TimeStamp range supported by MySql is that on 32-bit machines, the supported value range is that the year should not exceed 2030. If the value is on a 64-bit machine, the year can reach 2106, but for date and datetime types, there is no relationship between them. They can all be expressed as 9999-12-31, so pay attention to this. Also, when installing MySql, we generally choose Typical (Typical installation). Of course, if there are other purposes, we 'd better choose Complete (full installation ); during the installation process, you are usually asked to select the Server type. There are three Server types, Developer and Server Machine), Dedicated MySql Server Machine (Dedicated MYSQL Server), select which type of Server will only affect the Configuration Wizard on memory, or other aspects will have no impact; therefore, if we are a developer, you can select the development machine. Then, you can select the database usage dialog box by default;

Connect to and disconnect the server:
Connection: enter a command set similar to the following at a windows Command Prompt: mysql-h host-u user-p
For example, when I use mysql-h localhost-u root-p
Then you will be prompted to enter the user password. At this time, if you have a password, enter the password and press Enter. If there is no password, press enter to access the database client; to connect to mysql on the remote host, run the following command: mysql-h 159.0.45.1-u root-p 123

Disconnect the server: after entering the client, you can directly enter quit and press Enter;

The following describes database commands.
You can enter the following command to perform related operations on the database table or database, which is omitted here and then directly explained in text;

Select version (), current_date; // obtain the current mysql version and date from the server.
Select user (); // obtain all users of the current database
Use databasename; enter the specified database, and then you can operate the tables in the database.
Show databases; // query all databases in the current database and display them;
Create batabase databasename; create a database, for example, Create database manager;
Show tables; // view all tables in the current database;
Create table tablename (colums); create a table and specify related columns for the table, for example, Create table pet (name varchar (20), owner varchar (20 ), species varchar (20), sex char (1), birth date, death date );

Describe tablename; display all the information in the table in detail, for example, descr ...... remaining full text>