mySQL基本语法

最近折腾vps，想要手动搭建一个LNMP环境，那必然少不了要补补课，简单学习一下mysql的基本语法。

install mySQL

install on Centos

$ sudo yum update
$ sudo yum install mysql-server mysql-client
$ sudo yum install mysql-devel # install sdk of mysql
# /usr/include/mysql/mysql.h

install on Ubuntu

sudo apt-get update
sudo apt-get install mysql-server mysql-client
sudo apt-get install libmysqlclient15-dev || sudo apt-get install libmysqlclient-dev

mySQL cmd

login

$ mysql -u USERNAME -p
$ mysql -u root -p
mysql> \h

add user

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# e.g.
mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

set password

# 为当前用户设置新的密码
mysql> SET PASSWORD = PASSWORD("newpassword");

# 为其它用户重设密码
mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

delete user

DROP USER 'username'@'host'

grant privileges

# 为用户授权
# privileges: SELECT, INSERT, UPDATE, ALL, ...
mysql> GRANT privileges ON databasename.tablename TO 'username'@'host';
# 刷新缓存，使授权生效
mysql> FLUSH PRIVILEGES;

# e.g.
mysql> GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
mysql> GRANT ALL ON *.* TO 'pig'@'%';

databases and tables

• create database or table

# create database: vps
mysql> CREATE DATABASE vps;
Query OK, 1 row affected (0.00 sec)

# show databases
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| vps                |
+--------------------+
5 rows in set (0.00 sec)

# use database
mysql> USE vps;

# create table
mysql> CREATE TABLE test_tbl(
    -> test_id INT NOT NULL AUTO_INCREMENT,
    -> test_title VARCHAR(100) NOT NULL,
    -> test_author VARCHAR(40) NOT NULL,
    -> test_date DATE,
    -> PRIMARY KEY ( test_id )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

# show tables
mysql> SHOW TABLES;
+---------------+
| Tables_in_vps |
+---------------+
| test_tbl      |
+---------------+
1 row in set (0.00 sec)

# describe table
mysql> DESC test_tbl;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| test_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| test_title  | varchar(100) | NO   |     | NULL    |                |
| test_author | varchar(40)  | NO   |     | NULL    |                |
| test_date   | date         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

# drop table
mysql> DROP TABLE runoob_tbl;
Query OK, 0 rows affected (0.01 sec)

in conclusion

mysql> CREATE DATABESE dataBaseName;
mysql> SHOW DATABASES;
mysql> USE dataBaseName;
mysql> CREATE TABLE tableName(content);
mysql> SHOW TABLES;
mysql> DESC tableName;
mysql> DROP tableName;

• handle tables

reference

• http://www.runoob.com/mysql/mysql-tutorial.html