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

时间:2021-12-19 04:45:45

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

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

 

 

http://www.cnblogs.com/GarveyCalvin/p/4297282.html#createDatabase

 

 

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;

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

| Field    | Type        | Null | Key | Default | Extra          |

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

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

| Name     | varchar(20) | NO   |     | NULL    |                |

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

| Birthday | datetime    | YES  |     | NULL    |                |

| star     | tinyint(1)  | YES  |     | 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 ;

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

| Field    | Type        | Null | Key | Default | Extra          |

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

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

| Name     | varchar(20) | NO   |     | NULL    |                |

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

| Birthday | datetime    | YES  |     | 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;

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

| Field    | Type        | Null | Key | Default | Extra |

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

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

| Name     | varchar(20) | NO   |     | NULL    |       |

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

| Birthday | datetime    | YES  |     | 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;

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

| Field | Type        | Null | Key | Default | Extra |

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

| Name  | varchar(20) | NO   |     | NULL    |       |

| 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));

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

| news_id | news_content                                             | news_timestr        | news_graburl | news_type | news_sourcename | love |

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

|       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 '绝对%';

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

| news_id | news_content    | news_timestr        | news_graburl | news_type | news_sourcename | love |

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

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

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

1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

mysql> desc sw_goods;

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

| Field             | Type          | Null | Key | Default | Extra          |

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

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

| goods_name        | varchar(128)  | NO   |     |         |                |

| 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_introduce   | text          | YES  |     | NULL    |                |

| goods_big_img     | varchar(128)  | NO   |     |         |                |

| goods_small_img   | varchar(128)  | NO   |     |         |                |

| 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)