MySQL Learning Note 001 -- DB Basics

MySQL keyword is case-insensitive, BUT

MySQL databases, tables, and columns names IS case sensitive

Login to MySQL

mysql -h localhost -u root -p

-h + 主机地址;

-u + Username;

-p: prompt for password;

-p + Password (明文输入无引号无空格);

Clear terminal

\c //不加; return you to the mysql> prompt.

Linear display (instead of tabular display)

\G // 放在statement末尾

Create Users

create user 'username'@'hostname' identified by 'password'; // 创建名为 'username' 的用户，并设置密码为 'password'，须加引号
grant all on *.* to 'username'@'hostname'; // grant 所有权限 on 所有 database，须加引号

List all Users

 select user from mysql.user;

Create a new DB

CREATE DATABASE dbname; // OR
CREATE SCHEMA dbname; // OR
CREATE DATABASE IF NOT EXISTS dbname;

Adding options when creating DB

CREATE DATABASE db_name
CHARACTER SET latin1     //Set the default characters to be Latin letters
COLLATE latin1_bin;      //Set sorting in tables to be based on binary Latin characters

It is optional to add options when creating DB

Delete a DB

DROP DATABASE dbname;

Create a new table

create table database.table (col_1 type, col_2 type, ...);

• Maximum columns amount: 255

• Each columns are separated by a comma

column definition

name type [NOT NULL | NULL] [DEFAULT value]

create table database.table (
    col_1 type PRIMARY KEY,
    col_2 type AUTO_INCREMENT, 
    ...)

List all DB

show databases;

• Use LIKE to add query conditions
  • e.g. LIKE  '%s' -- list db endiing with s

Default DB

• information_schema: stores information about the server
• mysql: stores usernames, passwords, and user privileges (Don’t try to change the mysql database directly)
• test: a database for testing (initially empty)

Set current/active database

USE dbname // This allows you to specify table name without preceding dbname.

View all tables in active db

SHOW TABLES;

View table schema

DESCRIBE table_name; //OR
DESC table_name

Or alternatively -- 

SHOW COLUMNS FROM table_name;

​

• Field: the fields or columns of the table created.
• Type: the data type for each field.
• Null: whether each field may contain NULL values.
• Key: whether a field is a key field — an indexed column. (e.g. PRI = Primary key, UNI = Unique, 空白 = not a key field)
• Default: default value for a field.
  • For time field: CURRENT_TIMESTAMP
• Extra: any extra information

  • auto_increment -- the column is auto-generated

Display all entries from a table

SELECT * FROM table_name;

​

Show db-creation/table-creation command

SHOW CREATE DATABASE [db_name];

SHOW CREATE TABLE [table_name];