Shulou(Shulou.com)05/31 Report--

This article mainly introduces Mysql how to view, create users, empower, delete table user database and other operations, the article is very detailed, has a certain reference value, interested friends must read it!

Create users, weights, tablespaces

-

Mysql-u root-p

Enter

Show databases

Use pacs

Show tables

-

View currently logged in user

Mysql > select user ()

View the current database

Mysql > select database ()

View tablespace information

Mysql > show variables like'% innodb_data_file_path%'

Modify the password

Format: mysqladmin-u username-p old password password new password. P and old passwords have no spaces, for example

1. If root does not have a password, add a password to root. First enter the directory mysql\ bin under DOS, and then type the following command

Mysqladmin-u root password 123

2. Change the password of root to 234p and the old password has no spaces.

Mysqladmin-u root-p123 password 234

Create a database

1. CREATE DATABASE database name

2. GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON database name. * TO database name @ localhost IDENTIFIED BY 'password'

3. SET PASSWORD FOR 'database name' @ 'localhost' = OLD_PASSWORD (' password')

Execute three commands in turn to complete the database creation. Note: Chinese "password" and "database" are set by users themselves.

Delete databases and data tables

Mysql > drop database database name

Mysql > drop table datasheet name

Show data table structure

Mysql > describe table name; desc abbreviation

Mysql > status;-- View the current database character set

Select * from table name limit per page

Select * from pis_study_info name limit 100

Create a user:

Mysql > insert into mysql.user (Host,User,Password) values ("localhost", "test", password ("1234"))

Mysql > insert into mysql.user (Host,User,Password) values ("%", "cdr", password ("123"))

This creates a user named: test password: 1234.

Note: the "localhost" here means that the user can only log in locally, not remotely on another machine. If you want to log in remotely, change "localhost" to "%", which means you can log in on any computer. You can also specify that a machine can log in remotely.

Authorize the user:

1. Log in as ROOT

Mysql-u root-p

two。 First create a database for the user

Mysql > create database pacsdb

3. Authorized cdr users have all the permissions of the pacsdb database (all permissions of a database):

Mysql > grant all privileges on pacsdb.* to cdr@localhost identified by '123'

Mysql > flush privileges;// refresh the system permissions table

Format: grant permission on database. * to user name @ login host identified by "password";

4. If you want to assign partial permissions to a user, you can write like this:

Mysql > grant select,update on testDB.* to test@localhost identified by '1234'

Mysql > flush privileges; / / refresh the system permissions table

5. Authorized test users have certain permissions for all databases:

Mysql > grant select,delete,update,create,drop on *. * to test@ "%" identified by "1234"

/ / the test user has select,delete,update,create,drop permissions on all databases.

/ / @ "%" indicates authorization for all non-local hosts, excluding localhost. (the localhost address is set to 127.0.0.1. If it is set to the real local address, I do not know whether it is possible or not, and there is no verification. )

/ / A pair of localhost licenses: add the sentence grant all privileges on testDB.* to test@localhost identified by '1234 licenses;.

Empower remote servers

Allow users to log in remotely at the specified IP. If you want to not limit the IP of the link, set it to "%".

Grant all PRIVILEGES on bidb.* to root@'%' identified by '123'

Grant all PRIVILEGES on pacs.* to root@'%' identified by '123'

Grant all PRIVILEGES on mysql.* to root@'%' identified by '123'

Grant all PRIVILEGES on test_db.* to root@'192.168.1.101' identified by '123456'

Grant all on *. * to root@'%' identified by 'root' with grant option

Delete user

@ > mysql-u root-p

@ > password

Mysql > Delete FROM user Where User='test' and Host='localhost'

Mysql > flush privileges

Mysql > drop database testDB; / / Delete the user's database

Delete account and permissions: > drop user user name @'%'

> drop user user name @ localhost

The above is all the contents of the article "how to view, create users, empower, delete table user database and so on in Mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.