7.21_mysql

此时不应有 D:\Java\jdk-17\jre\bin。

D:\kaifamiao\application\cmder_mini

λ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.30 MySQL Community Server - GPL

Copyright © 2000, 2022, Oracle and/or its affiliates.

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> use company_info;
Database changed

mysql> create table user(name varchar(20) comment '姓名',gender char(1) comment '性别',age int comment '年龄',birthday date comment '生日',phone varchar(20) comment '电话',address varchar(50) comment '地址',email varchar(20) comment '邮箱');

Query OK, 0 rows affected (0.03 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> desc user;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| name | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| birthday | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
±---------±------------±-----±----±--------±------+
7 rows in set (0.02 sec)

mysql> --添加列

mysql> ALTER TABLE user ADD create_time datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| name | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| birthday | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
±------------±------------±-----±----±--------±------+
8 rows in set (0.00 sec)

mysql> alter table user modify create_time timestamp;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| name | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| birthday | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| create_time | timestamp | YES | | NULL | |
±------------±------------±-----±----±--------±------+
8 rows in set (0.00 sec)

mysql> alter table user change birthday birth date;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| name | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| birth | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| create_time | timestamp | YES | | NULL | |
±------------±------------±-----±----±--------±------+
8 rows in set (0.00 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| company_info |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
±-------------------+
6 rows in set (0.00 sec)

mysql> insert into dept values(1,'研发部');
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
±-------±----------+
1 row in set (0.01 sec)

mysql> insert into dept(deptno,deptname) values (2,'人事部'),(3,'安保部'),(5,'宣传部'),(6,'组织部'),(7,'财务部');

Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from dept;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 5 | 宣传部 |
| 6 | 组织部 |
| 7 | 财务部 |
±-------±----------+
6 rows in set (0.00 sec)

mysql> insert into dept(deptno) values (4);
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 5 | 宣传部 |
| 6 | 组织部 |
| 7 | 财务部 |
| 4 | NULL |
±-------±----------+
7 rows in set (0.00 sec)

mysql> select * from company_info.dept;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 5 | 宣传部 |
| 6 | 组织部 |
| 7 | 财务部 |
| 4 | NULL |
±-------±----------+
7 rows in set (0.00 sec)

mysql> select deptname where dept;
ERROR 1054 (42S22): Unknown column ‘deptname’ in ‘field list’
mysql> select deptname from dept;
±----------+
| deptname |
±----------+
| 研发部 |
| 人事部 |
| 安保部 |
| 宣传部 |
| 组织部 |
| 财务部 |
| NULL |
±----------+
7 rows in set (0.00 sec)

mysql> select deptno,deptname from dept;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 5 | 宣传部 |
| 6 | 组织部 |
| 7 | 财务部 |
| 4 | NULL |
±-------±----------+
7 rows in set (0.00 sec)

mysql> select * from dept where deptno = 5;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 5 | 宣传部 |
±-------±----------+
1 row in set (0.01 sec)

mysql> select * from dept where deptno < 5;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 4 | NULL |
±-------±----------+
4 rows in set (0.00 sec)

mysql> select * from dept where deptno <> 4;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 5 | 宣传部 |
| 6 | 组织部 |
| 7 | 财务部 |
±-------±----------+
6 rows in set (0.00 sec)

mysql> select * from dept where deptno != 3;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 5 | 宣传部 |
| 6 | 组织部 |
| 7 | 财务部 |
| 4 | NULL |
±-------±----------+
6 rows in set (0.00 sec)

mysql> select * from dept where deptno = 3 and deptname = '安保部';
±-------±----------+
| deptno | deptname |
±-------±----------+
| 3 | 安保部 |
±-------±----------+
1 row in set (0.00 sec)

mysql> select * from dept where deptno = 5 or deptname = '人事部';
±-------±----------+
| deptno | deptname |
±-------±----------+
| 2 | 人事部 |
| 5 | 宣传部 |
±-------±----------+
2 rows in set (0.00 sec)

mysql> select * from dept where deptno between 1 and 5;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 1 | 研发部 |
| 2 | 人事部 |
| 3 | 安保部 |
| 5 | 宣传部 |
| 4 | NULL |
±-------±----------+
5 rows in set (0.00 sec)

mysql> select * from dept where deptno <= 4 and deptno >= 2;
±-------±----------+
| deptno | deptname |
±-------±----------+
| 2 | 人事部 |
| 3 | 安保部 |
| 4 | NULL |
±-------±----------+
3 rows in set (0.00 sec)

mysql> update dept set deptname = '开发喵·' + deptname;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ‘开发喵·’
mysql> desc dept;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| deptno | int | YES | | 1 | |
| deptname | varchar(20) | YES | | NULL | |
±---------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

mysql> update dept set deptname = concat('开发喵·',deptname);
Query OK, 6 rows affected (0.01 sec)
Rows matched: 7 Changed: 6 Warnings: 0

mysql> select * from dept;
±-------±---------------------+
| deptno | deptname |
±-------±---------------------+
| 1 | 开发喵·研发部 |
| 2 | 开发喵·人事部 |
| 3 | 开发喵·安保部 |
| 5 | 开发喵·宣传部 |
| 6 | 开发喵·组织部 |
| 7 | 开发喵·财务部 |
| 4 | NULL |
±-------±---------------------+
7 rows in set (0.00 sec)

mysql> select concat('abc',null) from dual;
±-------------------+
| concat(‘abc’,null) |
±-------------------+
| NULL |
±-------------------+
1 row in set (0.00 sec)

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2023-07-21 14:21:30 |
±--------------------+
1 row in set (0.01 sec)

mysql> select now() from dual;
±--------------------+
| now() |
±--------------------+
| 2023-07-21 14:22:01 |
±--------------------+
1 row in set (0.00 sec)

mysql> update dept set deptno = 8,deptname ='行政部' where deptno = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from dept;
±-------±---------------------+
| deptno | deptname |
±-------±---------------------+
| 1 | 开发喵·研发部 |
| 2 | 开发喵·人事部 |
| 8 | 行政部 |
| 5 | 开发喵·宣传部 |
| 6 | 开发喵·组织部 |
| 7 | 开发喵·财务部 |
| 4 | NULL |
±-------±---------------------+
7 rows in set (0.00 sec)

mysql> update dept set deptno = deptno + 1;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0

mysql> select * from dept;
±-------±---------------------+
| deptno | deptname |
±-------±---------------------+
| 2 | 开发喵·研发部 |
| 3 | 开发喵·人事部 |
| 9 | 行政部 |
| 6 | 开发喵·宣传部 |
| 7 | 开发喵·组织部 |
| 8 | 开发喵·财务部 |
| 5 | NULL |
±-------±---------------------+
7 rows in set (0.00 sec)

mysql> delete from dept where deptno = 5;
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept;
±-------±---------------------+
| deptno | deptname |
±-------±---------------------+
| 2 | 开发喵·研发部 |
| 3 | 开发喵·人事部 |
| 9 | 行政部 |
| 6 | 开发喵·宣传部 |
| 7 | 开发喵·组织部 |
| 8 | 开发喵·财务部 |
±-------±---------------------+
6 rows in set (0.00 sec)

mysql> create table test AS select * from dept;
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> desc test;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| deptno | int | YES | | 1 | |
| deptname | varchar(20) | YES | | NULL | |
±---------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

mysql> select * from test;
±-------±---------------------+
| deptno | deptname |
±-------±---------------------+
| 2 | 开发喵·研发部 |
| 3 | 开发喵·人事部 |
| 9 | 行政部 |
| 6 | 开发喵·宣传部 |
| 7 | 开发喵·组织部 |
| 8 | 开发喵·财务部 |
±-------±---------------------+
6 rows in set (0.00 sec)

mysql> use company_info;
Database changed
mysql> use user;
ERROR 1049 (42000): Unknown database ‘user’
mysql> desc company_info;
ERROR 1146 (42S02): Table ‘company_info.company_info’ doesn’t exist
mysql> show company_info;
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 ‘company_info’ at line 1
mysql> desc user
-> ;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| name | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| birth | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| create_time | timestamp | YES | | NULL | |
±------------±------------±-----±----±--------±------+
8 rows in set (0.00 sec)

mysql> insert into user(name,gender,age,phone,address,email) values ('王雪','女',22,123455678886,'西安','1234@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
±-------±-------±-----±------±-------------±--------±------------±------------+ | name | gender | age | birth | phone | address | email | create_time | ±-------±-------±-----±------±-------------±--------±------------±------------+ | 王雪 | 女 | 22 | NULL | 123455678886 | 西安 | 1234@qq.com | NULL |
±-------±-------±-----±------±-------------±--------±------------±------------+ 1 row in set (0.00 sec)

mysql> update user set birth = '2001-03-08' where name='王雪';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update user set create_time = '2022-20-01 10:34:56' where name='王雪';
ERROR 1292 (22007): Incorrect datetime value: ‘2022-20-01 10:34:56’ for column ‘create_time’ at row 1
mysql> update user set create_time = '2022-10-01 10:34:56' where name='王雪';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;
±-------±-------±-----±-----------±-------------±--------±------------±--------------------+
| name | gender | age | birth | phone | address | email | create_time |
±-------±-------±-----±-----------±-------------±--------±------------±--------------------+
| 王雪 | 女 | 22 | 2001-03-08 | 123455678886 | 西安 | 1234@qq.com | 2022-10-0
1 10:34:56 |
±-------±-------±-----±-----------±-------------±--------±------------±--------------------+
1 row in set (0.00 sec)