MySQL数据训练使用

最新推荐文章于 2022-09-07 10:13:08 发布
VIP文章我不是猫叔
最新推荐文章于 2022-09-07 10:13:08 发布
阅读量446
点赞数 1
分类专栏：数据分析师文章标签： mysql 数据库 sql 数据训练数据使用
江南一只猫
本文链接：https://blog.csdn.net/JN_Cat/article/details/109070422
版权
Last login: Wed Oct 14 10:24:46 on ttys000

The default interactive shell is now zsh.
To update your account to use zsh, please run `chsh -s /bin/zsh`.
For more details, please visit https://support.apple.com/kb/HT208050.
reindeMacBook-Pro:~ reinse$ /usr/local/MySQL/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 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 dbname;
Query OK, 1 row affected (0.00 sec)

mysql> create database wddb;
Query OK, 1 row affected (0.00 sec)

mysql> use wddb;
Database changed

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

mysql> show tables;
Empty set (0.00 sec)

mysql> drop database dbname;
Query OK, 0 rows affected (0.01 sec)

mysql> use wddb;
Database changed
mysql> create table job(name varchar(20),sex varchar(2),age int(2),hiredate date,wage decimal(10,2));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> desc job;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | varchar(20)   | YES  |     | NULL    |       |
| sex      | varchar(2)    | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| wage     | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> show create table job \g;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| job   | CREATE TABLE `job` (
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `wage` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> alter table job modify name varchar(25);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table job add home varchar(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table job drop column home;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table job change wage salary decimal(10,2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc job;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | varchar(25)   | YES  |     | NULL    |       |
| sex      | varchar(2)    | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| salary   | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select *from job;
Empty set (0.00 sec)

mysql> alter table job rename worker;
Query OK, 0 rows affected (0.01 sec)

mysql> desc worker;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | varchar(25)   | YES  |     | NULL    |       |
| sex      | varchar(2)    | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| salary   | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> alter table worker drop column salary;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(25) | YES  |     | NULL    |       |
| sex      | varchar(2)  | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table worker add birthday date;
Query OK, 0 rows affected (0.00 sec)

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(25) | YES  |     | NULL    |       |
| sex      | varchar(2)  | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert into worker values("wd01","男",23,"2020-01-01","1997-12-01");
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values("wd02","男",24,"2020-02-01","1997-01-01"),
    -> ("wd03","女",22,"2020-03-01","1997-01-03"),
    -> ("wd04","女",26,"2020-03-05","1997-01-04"),
    -> ("wd05","男",27,"2020-05-05","1997-04-04");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *from worker;
+------+------+------+------------+------------+
| name | sex  | age  | hiredate   | birthday   |
+------+------+------+------------+------------+
| wd01 | 男   |   23 | 2020-01-01 | 1997-12-01 |
| wd02 | 男   |   24 | 2020-02-01 | 1997-01-01 |
| wd03 | 女   |   22 | 2020-03-01 | 1997-01-03 |
| wd04 | 女   |   26 | 2020-03-05 | 1997-01-04 |
| wd05 | 男   |   27 | 2020-05-05 | 1997-04-04 |
+------+------+------+------------+------------+
5 rows in set (0.00 sec)

mysql> update worker set age=26 where name="wd02";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from worker where name="wd02";
Query OK, 1 row affected (0.00 sec)

mysql> select *from worker;
+------+------+------+------------+------------+
| name | sex  | age  | hiredate   | birthday   |
+------+------+------+------------+------------+
| wd01 | 男   |   23 | 2020-01-01 | 1997-12-01 |
| wd03 | 女   |   22 | 2020-03-01 | 1997-01-03 |
| wd04 | 女   |   26 | 2020-03-05 | 1997-01-04 |
| wd05 | 男   |   27 | 2020-05-05 | 1997-04-04 |
+------+------+------+------------+------------+
4 rows in set (0.00 sec)

mysql> select name,sex,age,hiredate,birthday from worker;
+------+------+------+------------+------------+
| name | sex  | age  | hiredate   | birthday   |
+------+------+------+------------+------------+
| wd01 | 男   |   23 | 2020-01-01 | 1997-12-01 |
| wd03 | 女   |   22 | 2020-03-01 | 1997-01-03 |
| wd04 | 女   |   26 | 2020-03-05 | 1997-01-04 |
| wd05 | 男   |   27 | 2020-05-05 | 1997-04-04 |
+------+------+------+------------+------------+
4 rows in set (0.00 sec)

mysql> insert into worker values ("wd02","男",24,"2020-02-01","1997-01-01"), ("wd06","女",22,"2020-03-01","1997-01-03"), ("wd07","女",26,"2020-03-05","1997-01-04"), ("wd08","男",27,"2020-05-05","1997-04-04");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *from worker;
+------+------+------+------------+------------+
| name | sex  | age  | hiredate   | birthday   |
+------+------+------+------------+------------+
| wd01 | 男   |   23 | 2020-01-01 | 1997-12-01 |
| wd03 | 女   |   22 | 2020-03-01 | 1997-01-03 |
| wd04 | 女   |   26 | 2020-03-05 | 1997-01-04 |
| wd05 | 男   |   27 | 2020-05-05 | 1997-04-04 |
| wd02 | 男   |   24 | 2020-02-01 | 1997-01-01 |
| wd06 | 女   |   22 | 2020-03-01 | 1997-01-03 |
| wd07 | 女   |   26 | 2020-03-05 | 1997-01-04 |
| wd08 | 男   |   27 | 2020-05-05 | 1997-04-04 |
+------+------+------+------------+------------+
8 rows in set (0.00 sec)

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(25) | YES  |     | NULL    |       |
| sex      | varchar(2)  | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select distinct age from worker;
+------+
| age  |
+------+
|   23 |
|   22 |
|   26 |
|   27 |
|   24 |
+------+
5 rows in set (0.00 sec)

mysql> select *from worker where age=24;
+------+------+------+------------+------------+
最低0.47元/天解锁文章
我不是猫叔
关注
1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据训练使用

Last login: Wed Oct 14 10:24:46 on ttys000The default interactive shell is now zsh.To update your account to use zsh, please run `chsh -s /bin/zsh`.For more details, please visit https://support.apple.com/kb/HT208050.reindeMacBook-Pro:~ reinse$ /usr/l
复制链接

扫一扫