Start to Use the MySQL

Enter my practice set

Start MySQL monitor
  Client persistence for operating the Character User Interface of MySQL

    // use cmd with administrator, start mysql (if mysql version is 96)
net start mysql96

    // log mysql
mysql -u (username) -p (password)
mysql -u -root -p

Start doing some operations

    // There're many way to create a database, below is one of them
CREATE DATABASE dbname;


    // show all databases
SHOW DATABASE;
    // Check the database currently in use
SELECT DATABASE();


    // Specify the database to be used next
use dbname;

Stop MySQL monitor

    // stop mysql
net stop mysql96

Setting

    // set password '1234' for root user
SET PASSWORD FOR root@localhost=PASSWORD('1234');

    // create new user
CREATE USER newusername IDENTIFIED BY 'newpassword';
    
        // set user permissions
GRANT (Permissions) ON databasename.tablename TO username;
            // Permissions:
                ALL :            all permissions
                SELECT, UPDATE : select and update only
            // databasename.tablename:
                databasename.* : all table in 'databasename'
                *.* :            all tables in all databases

Table operations

CREATE TABLE tableName(columnName1 dataType1, columnName2 dataType2...);

// e.g.
CREATE TABLE tbname1(empid VARCHAR(10), name VARCHAR(10), age INT);

Confirmation table structure

DESC tbname1;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | Varchar(10) | Yes  |     | NULL    |       |
| name  | Varchar(10) | Yes  |     | NULL    |       |
| age   | int(11)     | Yes  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 row in set (0.05 sec)

SHOW TABLES;

+-----------------------+
| TABLE_in_databasename |
+-----------------------+
| tbname1               |
+-----------------------+
1 row in set (0.00 sec)

SELECT * FROM dbname2.tbname;

// "Default" means "This value will be used if no value is entered." 
// "Null" means: Allow no value to be entered

INSERT INTO tableName VALUES(data1, data2...);

// e.g.
INSERT INTO tbname1 VALUES('A101', '佐藤', 40);
INSERT INTO tbname1 VALUES('A102', '高桥', 28);
INSERT INTO tbname1 VALUES('A103', '中川', 20);

INSERT INTO 
    tableName(columnNmae1, columnName2...) 
    VALUES(data1, data2...);

// e.g.
INSERT INTO 
    tbname1(age, name, empid) 
    VALUES(23, '渡边', 'A104');

INSERT INTO 
    tableName(columnNmae1, columnName2...) 
    VALUES(data1, data2...), (data1, data2...), (data1, data2...)...;

// e.g.
INSERT INTO 
    tbname1(age, name, empid)
    VALUES('A101', '佐藤', 40), ('A102', '高桥', 28), ('A103', '中川', 20), ('A104', '渡边', 23), ('A105', '西泽', 35);

// show data
SELECT columnNmae1, columnNmae2... FROM tableName;
SELECT * FROM tableName;

// e.g.
SELECT empid, name FROM tbname1;


+-------+------+
| empid | name |
+-------+------+
| A101  | 佐藤 |
| A102  | 高桥 |
| A103  | 中川 |
| A104  | 渡边 |
| A105  | 西泽 |
+-------+------+