MYSQL database usage notes

Source: Internet
Author: User

1. Installation

Sudo apt-Get install mysql-server mysql-Client

2. Restart, start, and stop

Sudo/etc/init. d/MySQL restart | START | stop

You can also use this command to shut down the database mysqladmin-u root-p123456 shutdown.

3. Add and modify the root password

Use the mysqladmin command line to modify the user name and password. The most correct format is as follows:

D:/MySQL/bin> mysqladmin-u root-P password 123456

Next, you will be prompted

Enter password:

If you have not modified the password for the first login, press enter (mysql5.0 is used, and the initial password for versions earlier than 4.0 is root ).

The root password is changed to 123456.

Do not use the following format. Otherwise, the password is changed to '123456', which is an 8-character instead of a 6-character password.

D:/MySQL/bin> mysqladmin-u root-P password '000000'

4. Refresh the user permission table

Mysqladmin flush-Privileges

5. Add new users and grant permissions

 

Bin> mysql-u Root

Mysql>Grant Permission 1, permission 2,... Permission n OnDatabase Name. Table Name ToUsername @ user address Identified'Connection password';

 

Permission 1, permission 2,... Permission n is replaced by all privileges or all, indicating that all permissions are granted to the user.

Database Name. The table name is replaced by *. *, which grants the user the permission to operate all the tables in the database on the server.
The user address can be localhost, IP address, machine name, or domain name. You can also use '%' to connect from any address
The 'Connection password' cannot be blank; otherwise, creation fails.

 

Mysql> grant all privileges on *. * to 'Sean '@' % 'identified by '123 ';

 

Create an sean user with a password of 123 and have all permissions for all databases on the server

 

6. Case sensitivity of MySQL Databases in Linux

In Linux, the database name, table name, column name, And alias are case-sensitive:

1. The database name and table name are case sensitive;

2. Table aliases are case sensitive;

3. The column name and column alias are case-insensitive in all cases;

4. Variable names are case sensitive;

After logging on with the root account, add lower_case_table_names = 1 after [mysqld] in/etc/my. cnf,

Restart the MYSQL service. The setting is successful: case insensitive to the table name;

Lower_case_table_names parameters:

Lower_case_table_names = 0

0: Case Sensitive, 1: case insensitive

7. datetime Field Type

Use: columnName DATETIME not null to create a value. Use: NOW () or CURRENT_DATE to assign a value.

8. Create a table using the following character sets and table parameters:

ENGINE = MyISAM default charset = utf8 COLLATE = utf8_bin COMMENT = 'knowledge point ';

9. SQL statement comments, single row use #, multi-row use /*...*/

10. add or delete users, change passwords, grant permissions, and revoke permissions

1. create user syntax 

Create user user identified by '20140901'

2. Drop user syntax 

Drop user @ localhost;

3. Set Password syntax 

Set password for user = password ('20140901 ');

4. Grant and revoke syntax 

Grant all privileges on db. * to user @ localhost identified by '20140901 ';

Revoke all privileges on DB. * from user @ '192. 168.1.1 ';

5. permission settings take effect

Flush privileges;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.