MySql(2)----Use database

#Tip of the Day:

Create table

Open database

First, we need to login to mysql

mysql -uroot -proot

Have a look at what databases do we have

mysql> SHOW DATABASES;

Select a database that we have, open it.

mysql> USE db_name;

Check current database in used:

SELECT DATABASE();

Create a table

CREATE TABLE table_name (
    column_name data_type,
    ...
)

For example:

CREATE TABLE tb1(
    username VARCHAR(20),
    age TINYINT UNSIGNED,
    salary FLOAT(8,2) UNSIGNED
    );

Display all the tables in this database:

SHOW TABLES;

Display the structure of a table:

SHOW COLUMNS FROM tb1;

Insert records into a table

Insert values for all fields without specifying field names:

INSERT tb1 VALUES('Tom', 25, 7863.25);

In this way, we have to make sure that the number of the parameters is the same as the number of fields in this table, otherwise there will be an error.

Or, we can insert values for part of the fields:

INSERT tb1 (username, salary) VALUES('John', 4500.69);

Then, we can have a look at all the data in our table:

SELECT * FROM tb1;

NULL, NOT NULL:

CREATE TABLE tb2(
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED NULL 
);

Then we must input a username, and we have a choice to insert a null for age.

INSERT tb2 VALUES('Tom', NULL);

AUTO_INCREMENT

Auto_increment must be used together with primary key.

Primary key is Unique. One table only has one primary key.

CREATE TABLE tb3(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL 
);

Then we can insert partial values for this table:

INSERT tb3(username) VALUES('Tom');
INSERT tb3(username) VALUES('John');

Unique key

One table has only one primary key, but can have more than one unique key.

CREATE TABLE tb5(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
  username VARCHAR(20) NOT NULL UNIQUE KEY,
  age TINYINT UNSIGNED
);

INSERT tb5(username, age) VALUES('Tom', 22);

Default value

CREATE TABLE tb6(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL UNIQUE KEY,
  sex ENUM('1', '2', '3') DEFAULT '3'
);

INSERT tb6(username) VALUES('Tom');

SELECT * FROM tb6;

Then the result has a default value.

+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Tom      | 3    |
+----+----------+------+