1: Use the SHOW statement to find out what database currently exists on the server:
mysql> SHOW DATABASES;
2. Create a database MYSQLDATA
mysql> CREATE DATABASE MYSQLDATA;
3: Select the database you created
mysql> USE MYSQLDATA; (When you press the enter key and Database changed appears, it means that the operation is successful!) )
4: See what tables exist in the database now
mysql> SHOW TABLES;
5: Create a database table
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
6: Structure of the display table:
mysql> DESCRIBE MYTABLE;
7: Add records to the table
mysql> insert into MYTABLE values (”hyq”,”M”);
8: Load data into database tables in text (e.g., D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;
9: Import .sql file command (e.g. D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10: Delete the table
mysql>drop TABLE MYTABLE;
11: Empty the table
mysql>delete from MYTABLE;
12: Update the data in the table
mysql>update MYTABLE set sex=”f” where name=’hyq’;
The following is the management experience of using MySql that I accidentally saw on the Internet,
MySql exists as a service in Windows, and before using it, you should make sure that this service has been started and that the net start mysql command is not started. In Linux, you can use the "/etc/rc.d/init.d/mysqld start" command when booting, and note that the initiator should have administrator privileges.
The newly installed MySql contains a root account with an empty password and an anonymous account, which is a big security risk, for some important applications we should improve the security as much as possible, here the anonymous account should be deleted, the root account should set the password, you can use the following command to do so:
use mysql;
delete from User where User=”";
update User set Password=PASSWORD(’newpassword’) where User=’root’;
If you want to restrict the login terminal used by the user, you can update the Host field of the corresponding user in the User table, and after making the above changes, you should restart the database service, and you can use the following command when logging in:
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
The above command parameters are part of the common parameters, please refer to the documentation for details. mydb here is the name of the database you want to log into.
In development and practical application, users should not only use the root user to connect to the database, although it is convenient to use the root user for testing, but it will bring major security risks to the system and is not conducive to the improvement of management technology. We give the most appropriate database permissions to the user used in an application. For example, a user who only inserts data should not be given permission to delete data. MySql's user management is implemented through the User table, and there are two common methods for adding new users: one is to insert the corresponding data columns in the User table, and set the corresponding permissions; The second is to create a user with certain permissions through the GRANT command. The common usage of GRANT is as follows:
grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb. TestTable to NewUserName@HostName identified by “password”;
To give this user the ability to administer permissions on the object, add the WITH GRANT OPTION option after the GRANT. For users added by inserting into the User table, the Password field is updated and encrypted with the PASSWORD function to prevent unscrupulous people from eavesdropping on the password. Users who have exceeded the permission should be cleared in time, and the reclaimed permission can be done by updating the corresponding fields in the User table, or using REVOKE.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Management Permissions:
FILE: Reads and writes files on the MySQL server.
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
Database/Datatable/Datacolumn permissions:
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
Special Permissions:
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。
|
Posted on 2/3/2015 2:38:16 PM
|
|
|
|
