MySQL Learning Notes: a preliminary introduction to MySQL

1. Install MySQL under Linux

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

Modify character set: / etc/my.conf configuration file

Vi / etc/ my.conf[mysqld] default-character-set=utf8character_set_server= utf8[client] default-character-set=utf8

two。 Start and stop the MySQL service

# service mysqld start

3. Log in to MySQL:

# mysql-uroot

4. Modify the MySQL prompt:

Modify the MySQL prompt

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

Or modify after connecting to MySQL:

Mysql > prompt

Commonly used MySQL prompt:

\ d

Full date

\ d current database

\ h Server name

\ U current user

Example:

# mysql-uroot-- prompt'\ u@\ h\ d'root@localhost (none) / / or mysql > prompt\ u@\ h\ d > PROMPT set to'\ u@\ h\ d > 'root@localhost (none) >

5.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)

Check the warning message:.

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