mysql資料庫入門命令 --增、刪、查、改

標籤：添加   esc   mysql資料庫   into   ble   tor   word   monit   效果   

添加資料庫
[[email protected] ~]# mysql -uroot -p #進入mysql資料表
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
...... #省略部分內容

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

mysql> create database school; #建立新庫‘school
Query OK, 1 row affected (0.00 sec)

mysql> show databases; #查看所有庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)

添加資料表

mysql>use school;mysql> create table info (id int not null,name char(6),score decimal(5,2),age int(4))；Query OK, 0 rows affected (0.04 sec)mysql> desc info;   #查看錶結構+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id       | int(11)      | NO   |     | NULL    |       || name  | char(6)   | YES  |     | NULL    |       || score | decimal(5,2) | YES  |  NULL   |       || age   | int(4)        | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+添加資料表內容mysql> insert into info (id,name,score,age)values(1,‘張三‘,88,33);Query OK, 1 row affected (0.01 sec)mysql> insert into info (id,name,score,age)values(2,‘李四‘,48,31);Query OK, 1 row affected (0.01 sec)mysql> insert into info (id,name,score,age)values(3,‘王五‘,68,27);Query OK, 1 row affected (0.00 sec)mysql> select * from info;   #查看錶內容+----+--------+-------+------+| id | name   | score | age  |+----+--------+-------+------+|  1 | 張三   | 88.00 |   33 ||  2 | 李四   | 48.00 |   31 ||  3 | 王五   | 68.00 |   27 |+----+--------+-------+------+添加表列mysql> alter table info add column score decimal(5,2);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from info;   #查看錶內容+----+--------+--------+-------+| id | name   | hobbly | score |+----+--------+--------+-------+|  1 | zl        | 看書   |  NULL ||  2 | 李四   | 上網   |  NULL ||  5 | kl        | 遊泳   |  NULL |+----+--------+--------+-------+更改資料mysql> update info set score=82 where id=1;  #更改id=1的學產生績為82分Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from info;  #查看更改效果+----+--------+-------+------+| id | name   | score | age  |+----+--------+-------+------+|  1 | 張三   | 82.00 |   33 ||  2 | 李四   | 48.00 |   31 ||  3 | 王五   | 68.00 |   27 |+----+--------+-------+------+刪除資料表mysql> drop table info;Query OK, 0 rows affected (0.02 sec)mysql> show tables;     #查看是否刪除Empty set (0.00 sec)     #已經刪除（空表）刪除庫mysql> drop database school;Query OK, 0 rows affected (0.01 sec)mysql> show databases;     #查看school資料庫是否刪除+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+刪除表列（記錄條數）mysql> delete from info where hobbly=1;Query OK, 1 row affected (0.01 sec)mysql> select * from info;+----+--------+-------+------+--------+| id | name   | score | age  | hobbly |+----+--------+-------+------+--------+|  1 | 張三   | 82.00 |   33 |     3        ||  2 | 李四   | 48.00 |   31 |     5      ||  3 | 王五   | 68.00 |   27 |      4    ||  2 | ff        |  55      | 44  |      2     ||  3 |ww      | 33       |3 1  |     3      |+----+--------+-------+------+--------+刪除表列（欄位）mysql> select * from info;  #查看資料表+----+--------+-------+--------+| id | name   | score | hobbly |+----+--------+-------+--------+|  1 | zl        | 88       | 看書   ||  2 | 李四    | 68      | 上網   ||  5 | kl        | 88       | 遊泳   |+----+--------+-------+--------+mysql> alter table info drop column score;   #用alter刪除表列mysql> select * from info; #再次查看資料表+----+--------+--------+| id | name   | hobbly |+----+--------+--------+|  1 | zl         | 看書   ||  2 | 李四    | 上網   ||  5 | kl         | 遊泳   |+----+--------+--------+

mysql資料庫入門命令 --增、刪、查、改