C:\WINDOWS\system32>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database shujuku
-> ;
Query OK, 1 row affected (0.12 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shujuku |
| sys |
+--------------------+
5 rows in set (0.08 sec)
mysql> drop database shujuku
-> ;
Query OK, 0 rows affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database company
-> ;
Query OK, 1 row affected (0.03 sec)
mysql> use company
Database changed
mysql> create table employee(
-> id int,
-> name varchar(40)
-> sex char(4),
-> birthday date,
-> job varchar(100)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sex char(4),
birthday date,
job varchar(100)
)' at line 4
mysql> create table employee(
-> id int,
-> name varchar(40)
-> sex char(4),^C
mysql> create table employee(
-> id int,
-> name varchar(40),
-> sex char(4),
-> birthday date,
-> job varchar(100)
-> );
Query OK, 0 rows affected (0.22 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee |
+-------------------+
1 row in set (0.03 sec)
mysql> rename table employee to user;
Query OK, 0 rows affected (0.09 sec)
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql> alter table user add image blob;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| image | blob | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table user character set utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| image | blob | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> alter table user drop sex;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| image | blob | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> alter table user change job work;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table user change job work varchar(100);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| work | varchar(100) | YES | | NULL | |
| image | blob | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> create table employee(
-> id int,
-> score int
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee |
| user |
+-------------------+
2 rows in set (0.00 sec)
mysql> delete from employee;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee |
| user |
+-------------------+
2 rows in set (0.00 sec)
mysql> describe employee;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> drop table employee;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> drop table if exists employee;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| work | varchar(100) | YES | | NULL | |
| image | blob | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql> alter table user change work job varchar(100);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| image | blob | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table user change image sex varchar(40);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| sex | varchar(40) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table user add salary int;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| sex | varchar(40) | YES | | NULL | |
| salary | int | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> INSERT INTO
-> user(id,name,birthday,job,sex,salary)
-> VALUES
-> (1,"张三","1976-11-11","teacher","male",3000),
-> (2,"赵敏","1989-02-13","doctor","female",4000),
-> (3,"赵四","1990-01-01","worker","male",1000),
-> (4,"李武","2001-05-29","student","male",0),
-> (5,"MARRY","2000-12-12","secretary","female",5000);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> show user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user' at line 1
mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| job | varchar(100) | YES | | NULL | |
| sex | varchar(40) | YES | | NULL | |
| salary | int | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from user;
+------+-------+------------+-----------+--------+--------+
| id | name | birthday | job | sex | salary |
+------+-------+------------+-----------+--------+--------+
| 1 | 张三 | 1976-11-11 | teacher | male | 3000 |
| 2 | 赵敏 | 1989-02-13 | doctor | female | 4000 |
| 3 | 赵四 | 1990-01-01 | worker | male | 1000 |
| 4 | 李武 | 2001-05-29 | student | male | 0 |
| 5 | MARRY | 2000-12-12 | secretary | female | 5000 |
+------+-------+------------+-----------+--------+--------+
5 rows in set (0.01 sec)
mysql> insert into user(id,name) values(6,'黄四郎');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+------+--------+------------+-----------+--------+--------+
| id | name | birthday | job | sex | salary |
+------+--------+------------+-----------+--------+--------+
| 1 | 张三 | 1976-11-11 | teacher | male | 3000 |
| 2 | 赵敏 | 1989-02-13 | doctor | female | 4000 |
| 3 | 赵四 | 1990-01-01 | worker | male | 1000 |
| 4 | 李武 | 2001-05-29 | student | male | 0 |
| 5 | MARRY | 2000-12-12 | secretary | female | 5000 |
| 6 | 黄四郎 | NULL | NULL | NULL | NULL |
+------+--------+------------+-----------+--------+--------+
6 rows in set (0.00 sec)
mysql> update user set job='CEO' where name='黄四郎';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+------+--------+------------+-----------+--------+--------+
| id | name | birthday | job | sex | salary |
+------+--------+------------+-----------+--------+--------+
| 1 | 张三 | 1976-11-11 | teacher | male | 3000 |
| 2 | 赵敏 | 1989-02-13 | doctor | female | 4000 |
| 3 | 赵四 | 1990-01-01 | worker | male | 1000 |
| 4 | 李武 | 2001-05-29 | student | male | 0 |
| 5 | MARRY | 2000-12-12 | secretary | female | 5000 |
| 6 | 黄四郎 | NULL | CEO | NULL | NULL |
+------+--------+------------+-----------+--------+--------+
6 rows in set (0.00 sec)
mysql> delete from user where id = 6;
Query OK, 1 row affected (0.06 sec)
mysql> select * from user;
+------+-------+------------+-----------+--------+--------+
| id | name | birthday | job | sex | salary |
+------+-------+------------+-----------+--------+--------+
| 1 | 张三 | 1976-11-11 | teacher | male | 3000 |
| 2 | 赵敏 | 1989-02-13 | doctor | female | 4000 |
| 3 | 赵四 | 1990-01-01 | worker | male | 1000 |
| 4 | 李武 | 2001-05-29 | student | male | 0 |
| 5 | MARRY | 2000-12-12 | secretary | female | 5000 |
+------+-------+------------+-----------+--------+--------+
5 rows in set (0.00 sec)
mysql> select * from employee where sex='female';
ERROR 1146 (42S02): Table 'company.employee' doesn't exist
mysql> select * from user where sex='female';
+------+-------+------------+-----------+--------+--------+
| id | name | birthday | job | sex | salary |
+------+-------+------------+-----------+--------+--------+
| 2 | 赵敏 | 1989-02-13 | doctor | female | 4000 |
| 5 | MARRY | 2000-12-12 | secretary | female | 5000 |
+------+-------+------------+-----------+--------+--------+
2 rows in set (0.00 sec)
mysql> select job,name from user;
+-----------+-------+
| job | name |
+-----------+-------+
| teacher | 张三 |
| doctor | 赵敏 |
| worker | 赵四 |
| student | 李武 |
| secretary | MARRY |
+-----------+-------+
5 rows in set (0.00 sec)
mysql> exit
Bye