Shulou(Shulou.com)06/01 Report--

MySQL Learning Notes-installation and basic Operation

1. Install MySQL

1 # yum-y install mysql mysql-server mysql-devel

Add / etc/my.conf configuration:

1 vi / etc/my.conf2 [mysqld] 3 default-character-set=utf84 character_set_server=utf85 [client] 6 default-character-set=utf8

two。 Start and stop the MySQL service:

# service mysqld start# service mysqld stop

3. Log in and out of MySQL, set the mysql password, and allow remote login to mysql:

1 # mysql-uroot / / Log in mysql1 mysql > exit / / exit mysql

4. Set mysql password

Mysql >; USE mysql; mysql >; UPDATE user SET Password=PASSWORD ('newpassword') WHERE user='root'; mysql >; FLUSH PRIVILEGES

The mysql password can also be set with the: mysql_secure_installation command

5. Allow remote login

1 mysql-u root-pEnter Password: mysql > GRANT ALL PRIVILEGES ON *. * TO 'username' @'% 'IDENTIFIED BY' password 'WITH GRANT OPTION

When you are finished, you can use mysql-front to remotely manage mysql.

6. Modify the MySQL prompt

1 # mysql-uroot-proot-- prompt'/ / not used in windows

Or modify after connecting to MySQL:

Mysql > prompt

5.MySQL prompt:

\ d complete date

\ d current database

\ h Server name

\ U current user

Example:

1 # mysql-uroot-- prompt'\ u @\ h\ dong2 root@localhost (none)

Or

Mysql > prompt\ u @\ h\ d > PROMPT set to'\ u @\ h\ d > 'root@localhost (none) >

6.MySQL common commands

Display the current server version number:

Mysql > SELECT VERSION (); +-+ | VERSION () | +-+ | 5.1.71 | +-+ 1 row in set (0.00 sec)

Show the current user:

Mysql > SELECT USER (); +-+ | USER () | +-+ | root@localhost | +-+ 1 row in set (0.00 sec)

Displays the current date and time:

Mysql > SELECT NOW (); +-+ | NOW () | +-+ | 0-11-20 21:32:55 | +-+ 1 row in set (0.00 sec)

7.MySQL statement specification:

* keywords and function names are all capitalized

* Database name, table name, field name are all lowercase

* SQL statements must end with a semicolon

8. Basic database operations:

Database creation:

Mysql > CREATE DATABASE test1

Plus IF NOT EXISTS, if the database already exists, a warning message will be prompted:

Mysql > CREATE DATABASE IF NOT EXISTS test1;Query OK, 1 row affected, 1 warning (0.00 sec)

View warning messages:

Mysql > SHOW WARNINGS +- -+ | Level | Code | Message | +- -+ | Error | 1064 | You have an error in your SQL syntax Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNING' at line 1 | + -+ 1 row in set (0.00 sec)

View the list of databases:

Mysql > SHOW DATABASES;+-+ | Database | +-+ | information_schema | | mysql | | test | | test1 | +-+ 4 rows in set (0.00 sec)

Displays the instructions used when the database was created:

Mysql > SHOW CREATE DATABASE test1 +-+-+ | Database | Create Database | + -- + | test1 | CREATE DATABASE `test1` / *! 40100 DEFAULT CHARACTER SET utf8 * / | +-- -- + 1 row in set (0.00 sec)

Create a database encoding that is different from the configuration file (for example, GBK):

Mysql > CREATE DATABASE IF NOT EXISTS test2 CHARACTER SET gbk;Query OK, 1 row affected (0.00 sec) mysql > SHOW CREATE DATABASE test2 +-+-+ | Database | Create Database | +-+- -- + | test2 | CREATE DATABASE `test2` / *! 40100 DEFAULT CHARACTER SET gbk * / | +-- -- + 1 row in set (0.00 sec)

Modify the database encoding method:

Mysql > ALTER DATABASE test2 CHARACTER SET utf8

Or

Mysql > ALTER DATABASE test2 CHARACTER SET = utf8

Delete the database:

Mysql > DROP DATABASE test1

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.