MySQL Database Basics (reprint)

MySQL Database basics

1. Install MySQL

[email protected] ~]# yum install MySQL mysql-server mysql-devel

2. Start MySQL and set the boot from start

[[Email protected] ~]# service mysqld start/stop/restart/status #启动/shutdown/restart/status Chkconfig mysqld on/off[[email protecte D] ~]# chkconfig--list | grep mysqldmysqld 0:off1:off2:on3:on4:on5:on6:off

3. Related configurations

/etc/my.cnfmysql configuration file/var/lib/mysqlmysql database file port:3306mysql default port

5. Precautions

Mysql> quit #quit, exit, equivalent to ctrl+cbye[[email protected] ~]#; #MySQL a large log statement ends with a semicolon, note that it is a semicolon in English mode. Characters and strings are caused by ' ';

4.mysql Initialization and Login

[[email protected] ~]# mysql-u root-p ' 123456 ' [[email protected] ~]# mysql-u root-p-h localhost "-H" Specify service Address of the device

5. Create, view , and delete the current database

mysql> CREATE DATABASE Ultraera; Query OK, 1 row Affected (0.00 sec) mysql> SHOW databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Test | | Ultraera |+--------------------+4 rows in Set (0.00 sec) mysql> DROP DATABASE Ultraera; Query OK, 0 rows Affected (0.00 sec)

6. Create, view, and delete the current table

#创建表格之前要先选择数据库mysql > use ultraera;Database changedmysql> CREATE TABLE  Ultraera (                           #create   Create a table with at least 1 columns     ->  id int NOT NULL,                                            #NOT  NULL   not empty,int  integral type     -> name char ( NOT NULL,      )                       #char ()   character  , () defines the character length within     -> age int not null     -> ); query ok, 0 rows affected  (0.01 sec) mysql> desc ultraera;+-------+- ---------+------+-----+---------+----------------+| field | type     |  Null | Key | Default | Extra           |+-------+----------+------+-----+---------+----------------+| id     | int (one)   | NO  |     | NULL     |                | |  name  | char ( | no  |     | null)     |                 | |  age   | int (one)   | no  |     |  null    |                 |+-------+----------+------+-----+---------+----------------+3 rows in set  ( 0.00 SEC) mysql> drop table ultraera ; query ok, 0 rows affected  (0.00 SEC)

7. Modify the table information

A. Renaming a table

mysql> ALTER TABLE Ultraera rename ultraera_org;

B. New columns

ALTER TABLE Ultraera add address varchar (200);

C. Deleting a column

mysql> ALTER TABLE ultraera drop column address;

D. Modifying the data type of a column

Mysql> ALTER TABLE Ultraera modify name varchar (200);

E. Renaming a column

mysql> ALTER TABLE ultraera change column name name varchar (200);

8. Insert the data into the table:

A. Global insert

mysql> INSERT INTO Ultraera values (1, ' name ', 18);

B. Insert according to column

mysql> INSERT INTO Ultraera (id,name,age) VALUES (2, ' John ', 19);

9. View the data in the table:

Mysql> select * from Ultraera; # * Match all columns, or you can query only individual columns,mysql> select name from Ultraera;

10.where operator: Conditional judgment Query, query use method: where column operator value;

where supported operators: = equals;> greater than;< less than;<> not equal; >= greater than equals; <= less than equals; between within a range;

Mysql> SELECT * from Ultraera where id>1;mysql> select * from Ultraera where id=2;mysql> select * from Ultraer A where id>=1;mysql> select * from Ultraera where id<1;mysql> select * from Ultraera where id<=1;mysql> SELECT * from Ultraera where ID between 1 and 4;

11. Delete a record in the table (same can match the where operator)

Mysql> Delete from Ultraera where id = 4;mysql> Delete * from Ultraera; #清空一个表格的数据;

12. Update one of the data in the table

mysql> Update Ultraera set age=30 where id=3;

13.MySQL Add Delete a user

Mysql> create user User1 identified by ' 123456 ';            #user1, Password 123456, the new user does not have permission to log in to the database mysql> drop user user1; #删除用户user1

14. Add and remove permissions to users

Mysql> Grant all privileges on * * to ' user1 ' @ ' localhost ' identified by ' 123456 ';mysql> revoke all privileges from U Ser1;

15. Use Mysqldump for database backup (database rename: MySQL cannot rename the database directly, so if you want to duplicate the database, export it, create a new database, and then import the exported SQL file into the new database)

[[email protected] ~]$ mysqldump-u root-p ultraera > Ultraera.sql Backup [[email protected] ~]$ mysql-u root-p new_ult Raera < Ultraera.sql restore to the specified database

All right, here's the whole stuff.

If you have an incorrect place in the article, or you have a better idea.

Welcome to share with me. E-mail:[email protected]

This article from "A cup of boiled water" blog, please be sure to keep this source http://ultraera.blog.51cto.com/6640392/1583524

Original link This article by Bean John Blog Backup expert remote One click release

MySQL Database Foundation (reprint)