Linux下MySQL 数据库，数据表使用

今天学习在数据库中创建数据表和插入数据

创建数据库：create database 库名

创建数据表：create table        表名  字段

插入数据 ：   INSERT INTO     数据表名

里面

name 是字符串类型的字段

 VALUES是一个关键字，用于在INSERT INTO语句中指定要插入的值

name	sex	age	class	score

 

创建完毕后在数据表中插入如下数据：

tom	male	20	1	85
lily	female	21	2	68
tony	male	23	1	65
bob	male	22	2	78
john	male	21	3	89
lucy	female	24	3	59

创建表mysql> create table info (name VARCHAR(30),sex VARCHAR(50),age VARCHAR(30),classlass
VARCHAR(40),score VARCHAR(50)) ;
Query OK, 0 rows affected (0.00 sec)

插入数据mysql> INSERT INTO info (name, sex, age, class, score)  VALUES ('tom', 'male', '20', '1', '85'),         ('lily', 'female', '21', '2', '68'),         ('tony', 'male', '23', '1', '65'),         ('bob', 'male', '22', '2', '78'),
    ->        ('john', 'male', '21', '3', '89'),         ('lucy', 'female', '24', '3', '59');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

查看mysql> SELECT*FROM info;
+------+--------+------+-------+-------+
| name | sex    | age  | class | score |
+------+--------+------+-------+-------+
| tom  | male   | 20   | 1     | 85    |
| lily | female | 21   | 2     | 68    |
| tom  | male   | 20   | 1     | 85    |
| lily | female | 21   | 2     | 68    |
| tony | male   | 23   | 1     | 65    |
| bob  | male   | 22   | 2     | 78    |
| john | male   | 21   | 3     | 89    |
| lucy | female | 24   | 3     | 59    |
+------+--------+------+-------+-------+
8 rows in set (0.00 sec)

创建库和数据表做成这样

基本和上面一样但是我们要做汉化不然识别不了

ALTER TABLE 后面是数据表名字

mysql> ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入数据mysql> INSERT INTO products (名称, 种类, 成本, 出场日期) 
    -> VALUES ('西瓜', '水果', 4.1000, '2017-05-06 00:00:00.000'), 
    ->        ('芹菜', '蔬菜', 1.0000, '2017-04-01 00:00:00.000'), 
    ->        ('番茄', '蔬菜', 2.9000, '2017-05-09 00:00:00.000'), 
    ->        ('黄瓜', '蔬菜', 2.2000, '2017-05-05 00:00:00.000'), 
    ->        ('香蕉', '水果', 6.1000, '2017-05-23 00:00:00.000'), 
    ->        ('核桃', '坚果', 28.5000, '2017-03-03 00:00:00.000'), 
    ->        ('开心果', '坚果', 3.8000, '2017-02-22 00:00:00.000'), 
    ->        ('蓝莓', '水果', 50.2000, '2017-05-09 00:00:00.000');
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

 

查看  mysql> SELECT*FROM products;
+-----------+--------+---------+-------------------------+
| 名称      | 种类   | 成本    | 出场日期                |
+-----------+--------+---------+-------------------------+
| 西瓜      | 水果   | 4.1000  | 2017-05-06 00:00:00.000 |
| 芹菜      | 蔬菜   | 1.0000  | 2017-04-01 00:00:00.000 |
| 番茄      | 蔬菜   | 2.9000  | 2017-05-09 00:00:00.000 |
| 黄瓜      | 蔬菜   | 2.2000  | 2017-05-05 00:00:00.000 |
| 香蕉      | 水果   | 6.1000  | 2017-05-23 00:00:00.000 |
| 核桃      | 坚果   | 28.5000 | 2017-03-03 00:00:00.000 |
| 开心果    | 坚果   | 3.8000  | 2017-02-22 00:00:00.000 |
| 蓝莓      | 水果   | 50.2000 | 2017-05-09 00:00:00.000 |
+-----------+--------+---------+-------------------------+
8 rows in set (0.00 sec)

 做一下汉化 插入数据 完成

 在切换尝试    数据表：sales

创建表mysql>  CREATE TABLE sales ( 名称 VARCHAR(30), 销售地点 VARCHAR(30), 销售价格 DECIMAL(10, 4) );
Query OK, 0 rows affected (0.00 sec)

 注意要换数据库名字

汉化   mysql> ALTER TABLE sales CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_
_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 创建

创建表mysql>  CREATE TABLE sales ( 名称 VARCHAR(30), 销售地点 VARCHAR(30), 销售价格 DECIMAL(10, 4) );
Query OK, 0 rows affected (0.00 sec)

 插入

插入数据mysql> INSERT INTO sales (名称, 销售地点, 销售价格)   VALUES ('苹果', '河北', '5.0000'), ('香蕉', '湖南', '6.2000'), ('番茄', '北京', '3.1500'), ('黄瓜', '湖北',
'22.4500'), ('芹菜', '河北', '1.1100'), ('草莓', '北京', '10.0000'), ('哈密瓜', '北京', '8.9800'), ('蓝莓', '上海', '59.9000'), ('核桃', '海南', '35.8000');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

 查看

mysql> SELECT*FROM sales;
+-----------+--------------+--------------+
| 名称      | 销售地点     | 销售价格     |
+-----------+--------------+--------------+
| 苹果      | 河北         |       5.0000 |
| 香蕉      | 湖南         |       6.2000 |
| 番茄      | 北京         |       3.1500 |
| 黄瓜      | 湖北         |      22.4500 |
| 芹菜      | 河北         |       1.1100 |
| 草莓      | 北京         |      10.0000 |
| 哈密瓜    | 北京         |       8.9800 |
| 蓝莓      | 上海         |      59.9000 |
| 核桃      | 海南         |      35.8000 |
+-----------+--------------+--------------+
9 rows in set (0.00 sec)
'35.8000');

 流程图