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 ...;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/122802.html
標籤：MySQL
上一篇：Mysql練習題(1)