MySQL之终端（Terminal）管理数据库、数据表、数据的基本操作-白红宇

MySQL之终端（Terminal）管理数据库、数据表、数据的基本操作

阅读量：5242 次

发布时间：2019-06-14

本文共 8786 字，大约阅读时间需要 29 分钟。

sudo /Applications/XAMPP/xamppfiles/bin/mysql.server start

/Applications/XAMPP/xamppfiles/bin/mysql -h localhost -u root -p;

1、

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.16 |

+-----------+

1 row in set (0.07 sec)

1、数据库（database）管理

3、 drop database firstDB;

2、数据表（table）管理

2.1 create表

mysql> create table people

-> (

-> ID int auto_increment primary key,

-> Name varchar(20) not null,

-> AGE int not null,

-> Birthday datetime);

2.4 alter 修改表结构（增、删、改）

1、修改数据库编码

alter table people convert to character set UTF8;

2.4.1 insert 在表中添加列（字段）

alter table people add star bool;

mysql> desc people;

+----------+-------------+------+-----+---------+----------------+

+----------+-------------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| AGE | int(11) | NO | | NULL | |

+----------+-------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

2.4.2 alter 修改表（列）字段

alter table people modify star int;

2.4.3 delete 删除表（列）字段

alter table people drop column star;

2.4.4 rename 重命名表名

rename table newpeople to people;

mysql> show tables;

+------------------------+

| Tables_in_testdatabase |

+------------------------+

| newpeople |

+------------------------+

2.4.5 null or not null

mysql> alter table people modify AGE int(3) NOT NULL;

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc people ;

+----------+-------------+------+-----+---------+----------------+

+----------+-------------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| AGE | int(3) | NO | | NULL | |

+----------+-------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

2.5 create 利用已有数据创建新表

mysql> create table newpeople select * from people;

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc newpeople;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| ID | int(11) | NO | | 0 | |

| AGE | int(3) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

3、数据的操作及管理

3.1 增加数据（增）

mysql> insert into people values (null,'lpw',20,'1990-10-10');

Query OK, 1 row affected (0.10 sec)

mysql> select * from people;

+----+------+-----+---------------------+

| ID | Name | AGE | Birthday |

+----+------+-----+---------------------+

| 1 | lpw | 20 | 1990-10-10 00:00:00 |

+----+------+-----+---------------------+

1 row in set (0.02 sec)

mysql> select * from people;

+----+--------+-----+---------------------+

| ID | Name | AGE | Birthday |

+----+--------+-----+---------------------+

| 1 | lpw | 20 | 1990-10-10 00:00:00 |

| 2 | 张三 | 21 | 1990-10-10 00:00:00 |

| 3 | 李四 | 15 | 1995-01-19 00:00:00 |

| 4 | 王五 | 15 | 1965-04-09 00:00:00 |

+----+--------+-----+---------------------+

3.2 删除数据（删）

delete from PEOPLE where name = 'Lisa';

3.3 修改数据（改）

mysql> update PEOPLE set name='Calvin' where name = 'Garvey';

3.4 查询数据（查）

select * from people;

4、管理视图

视图是从数据库里导出一个或多个表的虚拟表，是用来方便用户对数据的操作。

mysql> create view people_view ( Name,AGE) as select Name,AGE from people;

mysql> show tables;

+------------------------+

| Tables_in_testdatabase |

+------------------------+

| newpeople |

| people |

| people_view |

+------------------------+

3 rows in set (0.00 sec)

mysql> select * from people_view;

+--------+-----+

| Name | AGE |

+--------+-----+

| lpw | 20 |

| 张三 | 21 |

| 李四 | 15 |

| 王五 | 15 |

+--------+-----+

4 rows in set (0.03 sec)

mysql>

mysql> desc people_view;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| AGE | int(3) | NO | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.03 sec)

mysql>

替换视图

mysql> create or replace view people_view(people_ID,people_Name,people_AGE) as select ID,Name,AGE from people;

Query OK, 0 rows affected (0.13 sec)

mysql> select * from people_view;

+-----------+-------------+------------+

| people_ID | people_Name | people_AGE |

+-----------+-------------+------------+

| 1 | lpw | 20 |

| 2 | 张三 | 21 |

| 3 | 李四 | 15 |

| 4 | 王五 | 15 |

+-----------+-------------+------------+

4 rows in set (0.00 sec)

操作视图

当视图数据有变化时（增、删、改），真实的表数据也会随着改变。也就是说，对视图的操作就是对表的数据，所以我们可以把视图当作表。

mysql> insert into people_view values(null,'tao',25);

mysql> select * from people_view;

+-----------+-------------+------------+

| people_ID | people_Name | people_AGE |

+-----------+-------------+------------+

| 1 | lpw | 20 |

| 2 | 张三 | 21 |

| 3 | 李四 | 15 |

| 4 | 王五 | 15 |

| 5 | tao | 25 |

+-----------+-------------+------------+

5 rows in set (0.00 sec)

mysql> select * from people;

+----+--------+-----+---------------------+

| ID | Name | AGE | Birthday |

+----+--------+-----+---------------------+

| 1 | lpw | 20 | 1990-10-10 00:00:00 |

| 2 | 张三 | 21 | 1990-10-10 00:00:00 |

| 3 | 李四 | 15 | 1995-01-19 00:00:00 |

| 4 | 王五 | 15 | 1965-04-09 00:00:00 |

| 5 | tao | 25 | NULL |

+----+--------+-----+---------------------+

5 rows in set (0.00 sec)

删除视图

mysql> drop view people_view;

mysql> select database();

+------------+

| database() |

+------------+

| firstdb |

+------------+

1 row in set (0.00 sec)

mysql> mysql> select * from newduanzitabl (news_id in (1,4,5));

+---------+----------------------------------------------------------+---------------------+--------------+-----------+-----------------+------+

+---------+----------------------------------------------------------+---------------------+--------------+-----------+-----------------+------+

| 1 | 迟早要沉的泰坦尼克号 | 2014-09-27 18:42:01 | | 9 | 百思不得姐 | 0 |

| 4 | 景德镇淘的感觉自己要发财了 | 2014-09-27 18:06:01 | | 9 | 百思不得姐 | 0 |

| 5 | 见过草船借箭的开灰机借箭的哪个见过？ | 2014-09-27 17:54:01 | | 9 | 百思不得姐 | 0 |

+---------+----------------------------------------------------------+---------------------+--------------+-----------+-----------------+------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM newduanzitable where news_content like '绝对%';

+---------+-----------------+---------------------+--------------+-----------+-----------------+------+

+---------+-----------------+---------------------+--------------+-----------+-----------------+------+

| 15 | 绝对亲生的 | 2014-09-27 15:30:02 | | 9 | 百思不得姐 | 0 |

+---------+-----------------+---------------------+--------------+-----------+-----------------+------+

1 row in set (0.00 sec)

mysql> desc sw_goods;

+-------------------+---------------+------+-----+---------+----------------+

+-------------------+---------------+------+-----+---------+----------------+

| goods_id | int(11) | NO | PRI | NULL | auto_increment |

| goods_weight | int(11) | NO | | 0 | |

| goods_price | decimal(10,2) | NO | | 0.00 | |

| goods_number | int(11) | NO | | 100 | |

| goods_category_id | int(11) | NO | | 0 | |

| goods_brand_id | int(11) | NO | | 0 | |

| goods_create_time | int(11) | NO | | 0 | |

| goods_last_time | int(11) | NO | | 0 | |

| abc | varchar(32) | NO | | NULL | |

+-------------------+---------------+------+-----+---------+----------------+

mysql> select goods_category_id,avg(goods_price) from sw_goods group by goods_category_id;

+-------------------+------------------+

| goods_category_id | avg(goods_price) |

+-------------------+------------------+

| 0 | 2027.055556 |

| 2 | 823.330000 |

| 3 | 1857.066667 |

| 4 | 2904.333333 |

| 5 | 4000.000000 |

| 8 | 111.000000 |

| 13 | 33.500000 |

| 14 | 54.000000 |

| 15 | 45.000000 |

| 23 | 3456.000000 |

+-------------------+------------------+

10 rows in set (0.07 sec)

mysql> select max(goods_price) as tp_max from sw_goods;

+----------+

| tp_max |

+----------+

| 12000.00 |

+----------+

1 row in set (0.00 sec)

mysql>

mysql> select count(*) as tp_count from sw_goods where goods_price >10000;

+----------+

| tp_count |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/lpwlpw/p/5198503.html

你可能感兴趣的文章

高仿BiliBili的iOS客户端源码项目

查看>>

poj 1228 凸包

查看>>

webapi返回json格式优化转载https://www.cnblogs.com/GarsonZhang/p/5322747.html

查看>>

Hibernate关系映射（三）多对一和一对多

查看>>

网络上找到的 SQL 存储过程学习

查看>>

ZipFile解压文件不改变压缩包内文件修改日期的方法

HDU 1007 Quoit Design竟然要分治

Not enough free disk space on disk '/boot'（转载）