Command line basic operations on mysql _ MySQL

Start mysql service: netstartmysql (use this command to start mysql. you need to register mysql into the service list. if the mysql that comes with wamp is registered to the service list, it is troublesome, if you want to know one or two, log on to mysql: mysql-uroot-p and press enter to start the mysql service:

Net start mysql

(To start mysql using this command, you need to register mysql to the service list. if the mysql that comes with wamp is registered to the service list, there is still some trouble. if you have any experts, please let us know)

Log on to mysql:

Mysql-u root-p Press enter and enter the password.

View user: use mysql press enter, select * from user; press Enter. In this way, all users can be listed, but it is messy. if you only need to view the User name and the Host that allows login, select user, Host from User;

Create a user:

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

With the preceding command, you can create a user with the account zhangxiansen and the password zhangxiansen. The first account is zhangxiansen and the second account is password; 2. @ is followed by a host that can be logged on. it can be an IP address, localhost, or %. % indicates that logon is allowed anywhere. localhost can only be logged on using localhost, the ip address can only be logged on to the machine with the corresponding ip address.

User authorization:

Grant all on *. * to 'hangzhou' @ 'localhost ';

This command grants all permissions on all tables of all data to the zhangxiansen account that can log on with localhost. 1. all indicates all permissions. if only the add or delete permissions are granted, add and delete are used instead of all. 2. the first * indicates all databases. if test_data is specified, use test_data to replace the first *, and the second * to represent all tables. if The stu table is specified, use stu to replace the second *. 3. the user who grants the permission is specified after ", at the same time, you should also specify the allowed login address, that is, the address after.

(Create a user and authorize the user at http://my.oschina.net/u/1179414/blog/202377for detailed explanation .)

Create data:

Create database test_data (database name, defined by yourself); press Enter.

(Before creating a table, you must specify the database to be created. Therefore, you generally need to use the use command, just like using mysql above, to operate the mysql database .)

Create a table:

Create table stu (

StuId int primary key auto_increment, -- primary key sets the primary key, auto_increment sets the column auto-increment.

StuName varchar (15) not null,

RoomId int

);

Create table classRoom (

RoomId int primary key auto_increment,

RoomName int

);

1. -- is the comment in the data; 2. there is no need for a comma after the last column; 3. a semicolon must be added after the ending brackets.

Modify column attributes:

Alter table classRoom modify roomName char (10 );

The above classRoom table name field is int type, we can use the modify command to modify. 1. classRoom is the table for which columns need to be modified; 2. columns for which roomName needs to be modified.

Add column:

Alter table stu add column age int not null;

This command can add the age column to The stu table. 1. for tables whose columns need to be added to stu, the columns to be added by age must follow the column type (required) and various constraints (optional ).

Add a foreign key:

Alter table stu constrain stu_room foreign key (roomId) references classRoom (roomId); this command can add foreign key constraints to the roomId of The stu table. 1. stu table with foreign key constraints to be added; 2. stu_room foreign key name, custom; 3. the first roomId is the column in The stu table, and the second is the column in the classRoom. You can also create a foreign key when creating a table structure. for example:

Create table desk (

Primary ID int primary key auto_increment,

RoomId int,

Constrain rule _room (foreign key name) foreign key (roomId) references classRoom (roomId)

);

In this way, you can also create foreign key constraints.

Create a primary key:

As shown above, the primary key behind the id column can create a primary key. If the primary key has multiple columns, 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 is composed of the wide and len columns. (This is just an example. it is intended to explain how to create a multi-column primary key and there is a problem with the table structure design .)

Add, query, modify, and delete:

Insert into classRoom (roomName) values ('20140901 ');

This command inserts a piece of data in the classRoom table. the roomId is auto-incrementing, so you do not need to manually insert a value.

Insert into desk (roomId) values (1 );

This command can insert a piece of data in the desk table. because roomId is a foreign key, you must have data with a roomId equal to 1 in the classRoom table to insert the data successfully.

Delete from classRoom where roomId = 1;

This command can delete a row of data with roomId = 1 in the classRoom table. because there is a foreign key, the deletion may affect the data in the desk and stu tables. for details, see the introduction of foreign key attributes.

Update classRoom set roomName = '000000' where roomId = 1;

If the data with a roomId equal to 1 exists in the classRoom table, the value of roomName for this row of data will be changed to 403.

Select * from classRoom where roomId = 1;

This command is used to search for data with roomId = 1. To specify the column to be searched, run the following command:

Select roomName from classRoom where roomId = 1;

This command only looks for the value of the roomName column.