如何使用终端操作数据库
１．如何登入数据库？
mysql -uroot -p
2.如何查询数据库中的所有数据库？
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3.如何选中一个数据库进行操作？
mysql> use sys
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>
－－如何退出数据库服务器？
mysql> exit;
Bye
－－如何在数据库服务器中创建我们的数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看某个数据库的所有数据表？
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
－－如何创建一个数据表？
CREATE TABLE pet(
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE);
Query OK, 0 rows affected (0.24 sec)
－－查看数据表是否创建出成功
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
－－查看创建好的数据表的结构
mysql> describe 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 | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
－－如何查看数据表中的记录
mysql> select * from pet;
Empty set (0.00 sec)
－－如何往数据表中添加数据记录？
mysql> INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', '2021-09-09');
Query OK, 1 row affected (0.04 sec)
再次查看数据表中的记录
mysql> select * from pet;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| puffball | Diane | hamster | f | 1990-03-30 | NULL |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
+----------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)
－－mysql常用的数据类型有哪些？
１．数值
类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
２．日期/时间
３．字符串(字符)
－－如何删除数据
mysql> delete from pet name = 'dog'
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name = 'dog'' at line 1
mysql> delete from pet where name = 'dog';
Query OK, 1 row affected (0.03 sec)
mysql> select * from pet -> ;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| puffball | Diane | hamster | f | 1990-03-30 | NULL |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| dog | china | self | m | 1999-09-09 | NULL |
+----------+-------+---------+------+------------+------------+
6 rows in set (0.00 sec)
mysql> select * from pet;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| puffball | Diane | hamster | f | 1990-03-30 | NULL |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
+----------+-------+---------+------+------------+------------+
5 rows in set (0.01 sec)
－－如何修改数据
mysql> select * from pet;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| puffball | Diane | hamster | f | 1990-03-30 | NULL |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| dog | self | china | m | 2018-09-08 | NULL |
+----------+-------+---------+------+------------+------------+
6 rows in set (0.00 sec)
mysql> update pet set name = 'cat' where owner = 'self';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet
-> ;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| puffball | Diane | hamster | f | 1990-03-30 | NULL |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| puffball | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| sofa | Diane | hamster | f | 1990-03-30 | 2021-09-09 |
| cat | self | china | m | 2018-09-08 | NULL |
+----------+-------+---------+------+------------+------------+
6 rows in set (0.00 sec)
－－总结以下：数据记录常见操作
－增加
INSERT
－删除
DELETE
－修改
UPDATE
－查询
SELECT
mysql学习笔记
笨鸟先飞