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

This article introduces the relevant knowledge of "database addition, deletion and revision command". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Simple management of data in the table (add, delete and check)(key points)

1. add data

INSERT INTO table name VALUES(value)

INSERT INTO user VALUES(NULL,'ball', 10,120);

2. query data

SELECT * FROM table name;

SELECT * FROM user;

3. modify data

UPDATE table name SET field name you want to modify WHERE id= number

UPDATE user SET name ='Li Xiang', sex='0',age=18 WHERE id=1

4. delete data

Delete FROM table name WHERE id = number;

DELETE FROM user WHERE id=1

Note: There must be a value behind where id, otherwise it is deleted completely.

II. Create a new ordinary user

GRANT SELECT ON *.* TO 'wenzhuo'@'localhost' IDENTIFIED BY '123456'

* * stands for all the preceding * stands for all the libraries * stands for all the tables

@ is preceded by a username followed by a hostname.

If the host name is only local, localhost is the IP address of another host, such as 192. 168.204.138

Key point: GRANT statement can not only create users, but also modify user passwords, but also set user permissions, because GRANT statement is a very, very important statement in mysql.

Third, view all users

SELECT user,host FROM mysql.user;

4. Delete ordinary users

DROP USER username @ hostname;

Example: DROP USER 'jiuqin'@'localhost'

5. Root user modifies his password

SET PASSWORD = PASSWORD ('new password');

Note: The new password must be encrypted using the PASSWORD() encryption function

VI. Root user modifies the password of ordinary user

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('new password')

SET PASSWORD FOR 'wenzhuo'@'localhost'=PASSWORD('wenzhuo520')

VII. Authorization

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'wenshuai'@'localhost' IDENTIFIED BY 'wenshuaiaiwenzhuo'

GRANT SELECT,INSERT,UPDATE,DELETE ON ss21.* TO 'wanli'@'localhost' IDENTIFIED BY 'wanli404'

GRANT: Authorized users can assign these permissions to other users

VIII. Withdrawing permission and viewing permission

1. view permission

SHOW GRANTS FOR 'username'@'hostname'

2. Withdraw authority

REVOKE INSERT,UPDATE,DELETE ON ss21.* FROM 'username'@'hostname'

IX. Data backup

You need to type cmd on the cmd command line windows+r in the run box

Start-> All Programs-> Accessories-> Command Prompt

mysqldump -u username-p library name table name> saved path

For example: mysqldump -uroot -p ss21> C:\ss21.sql

If access is denied in the backup, it means you don't have enough permissions.

Please open the file with super administrator privileges

Can't connect to MySQL server on 'localhost' (10061) Service not started

X. Data restoration

First you must prepare an empty database SS21.

mysql -u username-p library name

mysql -uroot -p ss21 < C:\ss21.sql

Important things need to be said three times. No semicolons. No semicolons.

"Database additions and deletions to change the search command" on the content of the introduction here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.