Basic command-line operation for MySQL

To start the MySQL service :

net start MySQL

(using this command to start MySQL, you need to register MySQL to the list of services, if it is Wamp's own MySQL registered to the service list is a bit troublesome, if there are experts want to tell one or two)

log in to MySQL:

Mysql-u Root-p Enter the password.

View User: Usemysql Enter, select * from user; Enter. This will be able to list all users, but it is messy, if you only need to view the user name, allow login host, then use select User,host from User;

To create a user:

Create user ' Zhangxiansen ' @ ' localhost ' identified by ' Zhangxiansen ';

As above command can create an account for zhangxiansen,1, password for Zhangxiansen users, the first Zhangxiansen is the account, the second Zhangxiansen is the password, 2, after the @ is allowed to log on the host, can be IP address, localhost,%,% representatives allow access anywhere, localhost only allows the use of localhost login, IP address can only be on the corresponding IP machine landing.

User authorization:

Grant all on * * to ' zhangxiansen ' @ ' localhost ';

This command grants all permissions to all tables of all data to the Zhangxiansen account that can be logged on with localhost. 1, all on behalf of all permissions, if only to grant permission to delete, then use Add,delete instead of ALL;2, the first * represents all databases, if you specify Test_data database, Test_data instead of the first *, the second * represents all tables, if you specify a Stu table, The Stu instead of the second *;3, to the user that specifies the permission, and the address that is allowed to log in, that is, after the @.

(Create user and authorization in http://my.oschina.net/u/1179414/blog/202377 have a very detailed explanation.) ）

To create the data:

Create Database Test_data (db name, own definition); Enter.

(You need to specify which database to build before you create the table, so you'll typically need to use the using command, as you would with MySQL, to specify the operation of the MySQL database.) ）

To Create a table:

CREATE TABLE Stu (

Stuid int PRIMARY Key auto_increment,--primary key set Primary key, Auto_increment set column self-increment.

Stuname varchar () NOT NULL,

Roomid int

);

CREATE TABLE Classroom (

Roomid int primary Key auto_increment,

Roomname int

);

1 、--is a comment in the data, 2, no comma is required after the last column, 3, and a semicolon after the final closing parenthesis.

To Modify a column property:

ALTER TABLE classroom Modify Roomname char (10);

Above I create classroom table name field is int type, we can be modified with modify command. 1, classroom is the table that needs to modify the column, 2, roomname need to modify the column.

To Add a column:

ALTER TABLE stu Add column age int is not NULL;

The command can add an age column to the Stu table. 1, Stu need to add a column of the table, age needs to add the column, followed by the above type (must) and various constraints (optional).

To Add a foreign key:

ALTER TABLE Stu constrain stu_room foreign key (Roomid) references classroom (ROOMID); This command can add a foreign key constraint to the roomid of the Stu table. 1, Stu need to add foreign key constraint table, 2, stu_room foreign key name, custom; 3, the first Roomid is the column in the Stu table, the second is the column in classroom. Adding foreign keys can also be created when you create a table structure, for example:

CREATE TABLE Desk (

Deskid int primary Key auto_increment,

Roomid int,

Constrain Desk_room (foreign key name) foreign key (Roomid) references classroom (ROOMID)

);

This also allows you to create a foreign key constraint.

To create a primary key:

As above, primary key after the ID column can create a primary key. If the primary key has more than one column, for example:

CREATE TABLE Rectangle (

wide int NOT NULL,

Len int NOT NULL,

Primary KEY (Wide,len)

);

In this way, the primary key of the rectangle table consists of the wide and Len columns. (This is just an example of how to create a multi-column primary key, which is problematic in the table structure design.) ）

additions and deletions to check and change:

INSERT INTO classroom (Roomname) VALUES (' 402 ');

The command inserts a piece of data into the classroom table, and the roomid is self-growing, so you do not need to insert the value manually.

INSERT into desk (Roomid) values (1);

This command inserts a piece of data into the desk table, and since Roomid is a foreign key, you must have roomid equal to 1 in the classroom table to successfully insert.

Delete from classroom where roomid=1;

This command can delete the roomid=1 row of data in the classroom table, because of the foreign key, the deletion may affect the data in the desk, Stu table, you can see the properties of the foreign key description.

Update classroom set Roomname= ' 403 ' where roomid=1;

If there is data in the classroom table that is equal to 1 roomid, the Roomname value of that row's data will be modified to 403.

SELECT * from classroom where roomid=1;

This command looks for roomid=1 data. If you need to specify a lookup column, use the following command:

Select Roomname from classroom where roomid=1;

This command finds only the value of the Roomname column.

If there is wrong, I hope to correct, I also in the study.

This article is from the "Zhang Xianxin blog" blog, make sure to keep this source http://supermezhang.blog.51cto.com/9062111/1696714

Basic command-line operation for MySQL