Mysql 庫表操作初識_ZenDei技術網路在線

Mysql 庫表操作初識終端登錄mysql 這裡只演示win下, cmd 終端. 至於怎麼在win下, linux, mac安裝, 感覺這是一個入門級的百度搜索問題, 安裝都搞不定, 確實有點尷尬, 好尷尬呀. 資料庫操作增刪改查, 註意這裡講的資料庫指的是schema哈. mysql 前提是已 ...

Mysql 庫表操作初識

終端登錄mysql

這裡只演示win下, cmd 終端. 至於怎麼在win下, linux, mac安裝, 感覺這是一個入門級的百度搜索問題, 安裝都搞不定, 確實有點尷尬, 好尷尬呀.

-- win + R 輸入cmd 進入控制台
C:\Users\chenjie> mysql  -u root -p  -- 回車
Enter password: ********  -- 回車

-- 成功進入了mysql客戶端, 如果登錄失敗, 要麼就是密碼不對, 或者是服務沒有開啟.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

-- 列印 "hello,world!" 測試
mysql> select "hello, world!";
+---------------+
| hello, world! |
+---------------+
| hello, world! |
+---------------+
1 row in set (0.00 sec)

-- test
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-10-06 14:11:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate() as "今日日期";
+--------------+
| 今日日期     |
+--------------+
| 2019-10-06   |
+--------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.05 sec)

-- 查看所有的資料庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cj                 |
| flask_db           |
| from_pdm           |
| mysql              |
| new_house          |
| performance_schema |
| python_test_1      |
| sql_advance        |
| stock_db           |
| sys                |
| test1              |
+--------------------+
19 rows in set (0.04 sec)

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.10 sec)

-- 查看資料庫編碼
mysql> show variables like "character%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | C:\AppServ\MySQL\share\charsets\ |
+--------------------------+----------------------------------+
8 rows in set (0.05 sec)

-- 查看某用戶的許可權
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.10 sec)

-- 查看資料庫當前連接數, 併發數
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     | -- 此線程中的空閑線程數
| Threads_connected | 4     |
| Threads_created   | 5     | -- 最近一次啟動服務,做創建的線程數
| Threads_running   | 1     | -- 當前激活線程數 
+-------------------+-------+
4 rows in set (0.05 sec)

-- 查看數據文件存放路徑
mysql> show variables like "%datadir%";
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | C:\AppServ\MySQL\data\ |
+---------------+------------------------+
1 row in set (0.05 sec)

-- 查看資料庫最大連接數
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.03 sec)
...

-- 退出資料庫
mysql> quit;
Bye
C:\Users\chenjie>

資料庫操作

增刪改查, 註意這裡講的資料庫指的是schema哈.

-- 前提是已經登錄,進入終端
-- 先查看有哪些
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cj                 |
| flask_db           |
| from_pdm           |
| mysql              |
| new_house          |
| performance_schema |
| python_test_1      |
| sql_advance        |
| stock_db           |
| sys                |
| test1              |
+--------------------+
19 rows in set (0.04 sec)

-- 新增一個新資料庫 test
mysql> create database test charset=utf8;
Query OK, 1 row affected (0.05 sec)

-- 選擇使用test
mysql> use test;
Database changed
-- 查看當前所在的資料庫
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.05 sec)

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.08 sec)

-- 查看庫中有哪些表
mysql> show tables;
Empty set

-- 刪除資料庫(千萬慎重!!!, 也不要給許可權, 刪了就真的沒了)
mysql> drop database test;
Query OK, 0 rows affected (0.20 sec)

-- 物理刪除哦, 太危險了, 刪庫跑路...
mysql> use test;
1049 - Unknown database 'test'

表結構操作

包括創建,修改, 刪除表, 欄位增刪改, 還是以經典的學生表為例.

創建表

-- 先建個test庫
drop database if exist test;
create database test charset=utf8;
use test;
show tables
-- 查看表
mysql> show tables;
Empty set

-- 創建學生表, 說實話, 我現在見到學生表,選課表, 選課..也想吐...
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default "",
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男', '女', "gg", "?"),
    class_id tinyint unsigned default 1
);

-- 創建班級表
create table classes(
    id tinyint unsigned primary key auto_increment not null,
    name varchar(20) default ''
);

-- out
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| classes        |
| students       |
+----------------+
2 rows in set (0.06 sec)

-- 查看表欄位及約束
mysql> desc students;
+----------+--------------------------+------+-----+---------+----------------+
| Field    | Type                     | Null | Key | Default | Extra          |
+----------+--------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)              | YES  |     |         |                |
| age      | tinyint(3) unsigned      | YES  |     | 0       |                |
| height   | decimal(5,2)             | YES  |     | NULL    |                |
| gender   | enum('男','女','中性','未知') | YES  |     | NULL    |                |
| class_id | tinyint(3) unsigned      | YES  |     | 1       |                |
+----------+--------------------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)

-- 查看表創建語句
mysql> show create table classes;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                        
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| classes | CREATE TABLE `classes` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

修改表-欄位-增改刪

-- 練習 alter tabe ...
-- add, modify, 
mysql> desc classes;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | YES  |     |         |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

-- 表增加欄位 add
mysql> alter table classes add slogan varchar(30);
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc classes;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | YES  |     |         |                |
| slogan | varchar(30)         | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

-- 表修改欄位 modify(不重命名); change(重命名)
mysql> alter table classes modify slogan int;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc classes;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | YES  |     |         |                |
| slogan | int(11)             | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

-- 修改表欄位-重命名 change 舊名 新名 類型約束
mysql> alter table classes change slogan yg_slogan varchar(50);
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc classes;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)         | YES  |     |         |                |
| yg_slogan | varchar(50)         | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

-- 刪除欄位: drop 列名
mysql> alter table classes drop yg_slogan, drop name;

Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc classes;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+---------------------+------+-----+---------+----------------+
1 row in set (0.04 sec)

-- 刪除表: drop table tb_name1, tb_name2...

mysql> drop table classes, students;
Query OK, 0 rows affected (0.22 sec)

mysql> show tables;
Empty set

表修改-增刪改數據

-- 還是用student表, 重寫創建回來
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default "",
    gender enum("男", "女", "中性", "未知"),
    age tinyint unsigned default 0,
    height decimal(5,2),
    class_id int unsigned default 1
);

新增數據 inset

-- 全欄位插入
insert into students values (null, "youge", "男", 23, 174.5, 1);

select * from students;
Query OK, 1 row affected (0.08 sec)

+----+-------+--------+-----+--------+----------+
| id | name  | gender | age | height | class_id |
+----+-------+--------+-----+--------+----------+
|  1 | youge | 男     |  23 | 174.50 |        1 |
+----+-------+--------+-----+--------+----------+
1 row in set (0.05 sec)

-- 部分列插入
insert into students(name, gender, age) values ("郭靖", 1, 28);

mysql> select * from students;
+----+-------+--------+-----+--------+----------+
| id | name  | gender | age | height | class_id |
+----+-------+--------+-----+--------+----------+
|  1 | youge | 男     |  23 | 174.50 |        1 |
|  2 | 郭靖  | 男     |  28 | NULL   |        1 |
+----+-------+--------+-----+--------+----------+
2 rows in set (0.07 sec)

-- 多行插入
insert into students values 
    (null, "黃蓉", 2, 24, 165.32, 2),
    (null, "楊康", 1, 25, 180, 1),
    (123, "華箏", 2, 23, 162.3, 2);
    
mysql> select * from students;
+-----+-------+--------+-----+--------+----------+
| id  | name  | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
|   1 | youge | 男     |  23 | 174.50 |        1 |
|   2 | 郭靖  | 男     |  28 | NULL   |        1 |
|   3 | 黃蓉  | 女     |  24 | 165.32 |        2 |
|   4 | 楊康  | 男     |  25 | 180.00 |        1 |
| 123 | 華箏  | 女     |  23 | 162.30 |        2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.07 sec)

修改數據 update

-- 全部修改 (這也跟 刪表刪庫 一樣危險, 更新不加 where,就全改掉了)
update students set 
    age := 24,  -- := 才是mysql的"賦值", "=" 在update, set時一樣
    class_id = 2;

mysql> select * from students;
+-----+-------+--------+-----+--------+----------+
| id  | name  | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
|   1 | youge | 男     |  24 | 174.50 |        2 |
|   2 | 郭靖  | 男     |  24 | NULL   |        2 |
|   3 | 黃蓉  | 女     |  24 | 165.32 |        2 |
|   4 | 楊康  | 男     |  24 | 180.00 |        2 |
| 123 | 華箏  | 女     |  24 | 162.30 |        2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.07 sec)

-- 按條件修改 where
update students set age:=26 where id=4;

mysql>select * from students;
+-----+-------+--------+-----+--------+----------+
| id  | name  | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
|   1 | youge | 男     |  24 | 174.50 |        2 |
|   2 | 郭靖  | 男     |  24 | NULL   |        2 |
|   3 | 黃蓉  | 女     |  24 | 165.32 |        2 |
|   4 | 楊康  | 男     |  26 | 180.00 |        2 |
| 123 | 華箏  | 女     |  24 | 162.30 |        2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.08 sec)

刪除數據 delete, 分邏輯刪除和物理刪除

-- 邏輯刪除: 新增一個標誌列, 預設我0表不刪, 1表刪除
alter table students add is_delete bit default 0;
-- update 標記要刪的數據
update students set is_delete := 1 where id in(1,2);

mysql> select * from students;
+-----+-------+--------+-----+--------+----------+-----------+
| id  | name  | gender | age | height | class_id | is_delete |
+-----+-------+--------+-----+--------+----------+-----------+
|   1 | youge | 男     |  24 | 174.50 |        2 | 1         |
|   2 | 郭靖  | 男     |  24 | NULL   |        2 | 1         |
|   3 | 黃蓉  | 女     |  24 | 165.32 |        2 | 0         |
|   4 | 楊康  | 男     |  26 | 180.00 |        2 | 0         |
| 123 | 華箏  | 女     |  24 | 162.30 |        2 | 0         |
+-----+-------+--------+-----+--------+----------+-----------+
5 rows in set (0.11 sec)

-- 其實就是過濾而已,並未真正刪除
mysql> select * from students where is_delete = 0;
+-----+------+--------+-----+--------+----------+-----------+
| id  | name | gender | age | height | class_id | is_delete |
+-----+------+--------+-----+--------+----------+-----------+
|   3 | 黃蓉 | 女     |  24 | 165.32 |        2 | 0         |
|   4 | 楊康 | 男     |  26 | 180.00 |        2 | 0         |
| 123 | 華箏 | 女     |  24 | 162.30 |        2 | 0         |
+-----+------+--------+-----+--------+----------+-----------+
3 rows in set (0.06 sec)

-- 物理刪除: delete ...; truncate tb_anme, 保留表結構
delete from students where id in (1,2,3,666);

mysql> select * from students;
+-----+------+--------+-----+--------+----------+-----------+
| id  | name | gender | age | height | class_id | is_delete |
+-----+------+--------+-----+--------+----------+-----------+
|   4 | 楊康 | 男     |  26 | 180.00 |        2 | 0         |
| 123 | 華箏 | 女     |  24 | 162.30 |        2 | 0         |
+-----+------+--------+-----+--------+----------+-----------+
2 rows in set (0.07 sec)

-- 全刪, 保留表結構
truncate students;

mysql> truncate students;
Query OK, 0 rows affected (0.24 sec)

mysql> select * from students;
Empty set

關於查詢 select, 下篇專門整一整吧.

小結

mysql 在不同平臺(win, linux)下的安裝, 都是自己手動熟悉一遍
終端登錄: mysql -u 用戶名 -p 密碼; 退出: quit;
常用命令
- select database();
- shwo databases;
- use 資料庫名;
- show tables;
- desc 表名;
庫操常用
- create database 庫名 charset=utf8;
- use 庫名;
- show create database 庫名;
- drop database 庫名; (刪庫跑路)
增刪表
- create table 表名 (欄位名, 類型, 約束, ....);
- 類型: 數值, 字元串, 時間日期, 枚舉...
- 約束: primary key; auto_increment; not null; default; unique; foreign key...
- 主鍵約束: id int unsigned primary key auto_increment not null;
- desc 表名; show create table 表名;
- drop table 表名;
增刪改欄位
- alter table 表名 add 欄位名類型 [約束];
- alter table 表名 modify 欄位名類型 [約束];
- alter table 表名 change 現欄位名新欄位名類型 [約束];
- alter table 表名 drop 欄位1, drop 欄位2....;
表數據-增
- insert into 表名 values (col1, co12 ......), ( ...), (...);
- insert into 表名(col1, col2) values (val1, val2) ...;
表數據-改
- update 表名 set 欄位1=值1, 欄位2=值2 ....
- update 表名 set 欄位1=值1, 欄位2=值2 .... where .....
表數據-刪
- delete from 表名 where .....;
- truncate 表名; (會保留表結構)
- alter table 表名 add isDelete bit default ( );
- update 表名 set isDelete := 1 where ...;