"Beginner rookie for-mysql basic operation and Rights management of database Tables"

Table Basic Operation Exercises

1 copy all records of user Information table to the UserDB tables .

Mysql> CREATE TABLE UserDB select * FROMUSER;

2 viewing The table structure of the UserDB table

mysql> desc UserDB;

3 Delete all records of the UserDB table

Mysql> Delete from UserDB

4 Set the name field to the index field

Mysql> CREATE index name on USERDB (name)

5 Add Number field ID above all fields, the number used to store the record.

ALTER TABLE USERDB add ID int (4) Not nullprimary key auto_increment first;

6 Import the contents of the/etc/passwd file into the UserDB table.

Load data infile '/etc/passwd ' into Tableuesrdb

Fields terminated by ': '

Lines terminated by ' \ n '

7 modifying The field name of the UID the sex type is enum and the field value can only be gril or boy

mysql> ALTER TABLE USERDB change UID sexenum ("B", "G") is not null; Modify the data within the non-modifiable

mysql> ALTER TABLE UserDB drop des;

Mysql> ALTER TABLE USERDB add sex enum ("B", "G") not null after GID;

8 The GID in the home directory of the users in the family to change to /root

mysql> update UserDB sethome= "/root" where GID between100 and 500;

9 The user is the root bin sync user's shell modified to /sbin/nologin

mysql> update UserDB setshell= "/sbin/nologin" where name in ("Root", "bin", "Sync");

Mysql> SELECT * from UserDB where Namein ("root", "bin", "Sync");

View gid less than ten users are using those shells

Mysql> SELECT * from UserDB where gid<10 group by Shell;

Delete The user whose first name begins with the letter D .

Mysql> Delete from UserDB where nameregexp "^d";

Query The first 5 users of the GID the shell used by the largest

Mysql> Select shell from UserDB order Bygid desc LIMIT 5;

see those users have no home directory

Mysql> select name from UserDB where Homeis null;

Save The first 5 user information with the minimum GID number to the/mybak/min5.txt file.

Mysql> SELECT * from UserDB ORDER by Giddesc limit 5 to outfile '/123654/we.txt ' fields terminated by ': ';

Add the user information you used to log into the system to the UserDB table

INSERT INTO Userdbvalues ("Yeyue", "X", "888", "999", "B", "/yeyue", "/bin/sbin");

Mysql> SELECT * from UserDB where name= "Yeyue";

Remove the des field from the table

mysql> ALTER TABLE UserDB drop des

all field values in the Set table are not allowed to be empty

Mysql> ALTER TABLE USERDB modify HOMEVARCHAR (not null);

Delete The value of the root User home directory field

mysql> Update UserDB set home=null wherename= "root"; a null quoted number will be a character entry

displays the user name home directory and the shell used by the GID greater than

Mysql> Select Name,home,shell from Userdbwhere gid>500;

Delete user records with uid greater than

mysql> Delete from UserDB whereuid>100;

shows how many users have UID numbers in the range of ten to four.

Mysql> SELECT * from UserDB whereuid<30 and uid>10;

The uid number is the type of shell that is used by users within the range of .

Mysql> SELECT * from UserDB whereuid<100 GROUP by Shell;

displays the information for the first ten users with the lowest uid number .

Mysql> SELECT * from UserDB order by Uidlimit 10;

show the table in article No. ten to

Mysql> SELECT * from UserDB ORDER by Namelimit 9, 6;

the details of the user whose uid number is less than and the letter A in the name

Mysql> SELECT * from UserDB where name is like "%a%" anduid<50;

only the user root bin daemon 3 user details are displayed.

Mysql> SELECT * from UserDB wherename= "root" or name= "bin" or name= "daemon";

The details of all users except the root user are displayed.

Mysql> SELECT * from UserDB where name notlike "root";

Mysql> SELECT * from UserDB wherename!= "root";

Statistics username field How many records are recorded

Mysql> Select COUNT (name) from UserDB;

display the name with the letter C User's detailed information

Mysql> SELECT * from UserDB where namelike "%c%";

two . User Authorization Exercises

0.set the password for the database administrator to 999

[Email protected] ~]# mysqladmin-hlocalhost-uroot-p password "Tarena"

Enter Password:

1.who is the user who is currently logged in to the database server?

Mysql> Select User ();

2, check the current login database server user permissions?

Mysql> Show grants ;

3. See which authorized users are currently on the database server.

Mysql> Select User from Mysql.user;

5, the authorized database administrator can access all the databases of the database server in all the hosts in the 192.168.1.0/24 network segment, have full permissions to all libraries and have authorized permission, login password tarena.

Mysql> Grant All on * * [email protected] "192.168.1.%" identified by "123" with GRANT option;

6. Roois not allowed to log on through 192.168.1.0 on the database server .

Mysql> revoke all on * * [email protected] "192.168.1.%";

7, authorized userweb users can access the database server from any host in the network, the Regtab table in the webdb view, update the name field and age Permissions for Fields password userweb888.

Mysql> Grant Select,update (name,age) Onwebdb.regtab to [e-mail protected] "%" identified by "userweb888";

8. Verify the above authorization is successful

8.1 Authorized user Userweb reset their login password to 123456, and verify the ability to login with the new password

8.2 The database administrator modifies the password of the authorized user Userweb to 654321, and the new password can be used to log on to the Landing database server

9. Revoke all authorizations from the above users and remove authorized users.

Mysql> revoke all on * * from [email protected] "L; mysql> Delete user wherename=" Roo ";

only allow the database administrator to log on from the database server and have authorized permissions.

Mysql> Grant All on * * [email protected] "localhost identified by" 123 "with GRANT option;