流水账第十一章

学习MySQL

              --1、登陆MySQL
mysql -uroot -p密码
              --2、查询数据库服务器中所有的数据库
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| 第一次             |
+--------------------+
5 rows in set (0.00 sec)
              --3、选中某一个数据库进行操作
--错误：
mysql> select * from pet;
ERROR 1046 (3D000): No database selected
--步骤1 先用use
use 第一次
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_第一次    |
+---------------------+
| pet                 |
+---------------------+
1 row in set (0.00 sec)
              --步骤2 tables 里面的数据表
select * from pet;

+--------+-----------+---------+------+------------+-------+
| name   | owner     | species | sex  | birth      | death |
+--------+-----------+---------+------+------------+-------+
| 金毛   | 我家的    | 狗      | 公   | 2021-01-01 | NULL  |
+--------+-----------+---------+------+------------+-------+
1 row in set (0.00 sec)

              --选择pet中的一行
select * from pet where name='金毛';

+--------+-----------+---------+------+------------+-------+
| name   | owner     | species | sex  | birth      | death |
+--------+-----------+---------+------+------------+-------+
| 金毛   | 我家的    | 狗      | 公   | 2021-01-01 | NULL  |
+--------+-----------+---------+------+------------+-------+
1 row in set (0.01 sec)

              --4、创建数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
--查询数据库 此时多出test
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| 第一次             |
+--------------------+
6 rows in set (0.00 sec)
--此时该数据库为空
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
              --创建数据表
mysql> create table text_pet(
    -> name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth date);
Query OK, 0 rows affected (0.02 sec)
--查看是否创建成功
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| text_pet       |
+----------------+
1 row in set (0.00 sec)
              --查看创建好的数据表结构
mysql> describe text_pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
              --添加数据记录
mysql> insert into text_pet
    -> values('Curry','James','baller','f','1943-03-21');
Query OK, 1 row affected (0.00 sec)

--查询结果
mysql> select * from text_pet;
+-------+-------+---------+------+------------+
| name  | owner | species | sex  | birth      |
+-------+-------+---------+------+------------+
| Curry | James | baller  | f    | 1943-03-21 |
+-------+-------+---------+------+------------+
1 row in set (0.00 sec)
              --再添加数据 同样步骤
mysql> insert into text_pet
    -> values('张三','李四','人','男','2021-01-01');
Query OK, 1 row affected (0.00 sec)
--此时出现添加结果
mysql> select * from text_pet;
+--------+--------+---------+------+------------+
| name   | owner  | species | sex  | birth      |
+--------+--------+---------+------+------------+
| Curry  | James  | baller  | f    | 1943-03-21 |
| 张三   | 李四   | 人      | 男   | 2021-01-01 |
+--------+--------+---------+------+------------+
2 rows in set (0.00 sec)
--5、MySQL数据类型查询：https://www.runoob.com/mysql/mysql-data-types.html
日期    按照格式  常用 date:yyyy-mm-dd      time:hh:mm;dd  year:yyyy
数字    按照大小  常用 int/integer：大整数   float/dowble：小数
字符串  按照大小  常用 char:定长字符串        varchar:变长字符串  text：长文本字符串

--继续创建
insert into text_pet values('Sub','Dfa','cat','f','1993-01-23');
insert into text_pet values('Tony','Hdjw','dog','m','1853-09-04');
insert into text_pet values('Femg','Jrk','cat','f','2012-10-24');
insert into text_pet values('Owdn','Ywm','cat','m','1999-11-11');
insert into text_pet values('Tatf','Eris','pig','f','2015-06-21');
--查询
  mysql> select * from text_pet;
  +--------+--------+---------+------+------------+
  | name   | owner  | species | sex  | birth      |
  +--------+--------+---------+------+------------+
  | Curry  | James  | baller  | f    | 1943-03-21 |
  | 张三   | 李四   | 人      | 男   | 2021-01-01 |
  | Sub    | Dfa    | cat     | f    | 1993-01-23 |
  | Tony   | Hdjw   | dog     | m    | 1853-09-04 |
  | Femg   | Jrk    | cat     | f    | 2012-10-24 |
  | Owdn   | Ywm    | cat     | m    | 1999-11-11 |
  | Tatf   | Eris   | pig     | f    | 2015-06-21 |
  +--------+--------+---------+------+------------+
  7 rows in set (0.00 sec)
                        --删除数据
mysql> delete from text_pet where name='张三';
Query OK, 1 row affected (0.01 sec)
--查询
mysql> select * from text_pet;
+-------+-------+---------+------+------------+
| name  | owner | species | sex  | birth      |
+-------+-------+---------+------+------------+
| Curry | James | baller  | f    | 1943-03-21 |
| Sub   | Dfa   | cat     | f    | 1993-01-23 |
| Tony  | Hdjw  | dog     | m    | 1853-09-04 |
| Femg  | Jrk   | cat     | f    | 2012-10-24 |
| Owdn  | Ywm   | cat     | m    | 1999-11-11 |
| Tatf  | Eris  | pig     | f    | 2015-06-21 |
+-------+-------+---------+------+------------+
6 rows in set (0.00 sec)
              --修改数据
mysql> update text_pet set name='赵六' where owner=    
;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
--查询
mysql> select * from text_pet;
+--------+-------+---------+------+------------+
| name   | owner | species | sex  | birth      |
+--------+-------+---------+------+------------+
| Curry  | James | baller  | f    | 1943-03-21 |
| 赵六   | Dfa   | cat     | f    | 1993-01-23 |
| Tony   | Hdjw  | dog     | m    | 1853-09-04 |
| Femg   | Jrk   | cat     | f    | 2012-10-24 |
| Owdn   | Ywm   | cat     | m    | 1999-11-11 |
| Tatf   | Eris  | pig     | f    | 2015-06-21 |
+--------+-------+---------+------+------------+
6 rows in set (0.00 sec)