mysql常見操作

weixin_33850890發表於2018-03-11

MySql

說明：本文旨在記錄常見資料庫操作

logo.png

資料庫相關

增加資料庫;
create database 資料庫的名字 charset=字元編碼;
```
create database testdb charset=utf8;
```

查詢資料庫

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mock_db            |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.01 sec)

選擇資料庫
use testdb;

mysql> use testdb;
Database changed
mysql>

檢視正在使用的資料庫

select database();

mysql> select database();
+------------+
| database() |
+------------+
| testdb     |
+------------+
1 row in set (0.00 sec)

mysql>

檢視使用的資料庫的所有表

show tables;

mysql> show tables;
Empty set (0.00 sec)

mysql>

刪除資料庫

drop database 資料庫名稱;

mysql> drop database testdb;
Query OK, 0 rows affected (0.00 sec)

mysql>

資料庫備份

這裡用到的是終端的一個重定向>的命令

Mac下資料庫的資料儲存的路徑/usr/local/mysql/data

進入上面目錄

 sudo cd  /usr/local/mysql/data

使用mysqldump命令將資料備份到桌面下~/Desktop/kkk.sql

➜  ~ mysqldump -u root -p testdb > ~/Desktop/kkk.sql;
Enter password:

資料密碼就可以在桌面看到kkk.sql了

我們先把舊的testdb刪除掉

drop database testdb;

檢視一下

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

mysql>

我們來建立一個空的名為testdb的資料庫

create database testdb2 charset=utf8;

我們檢視下testdb

```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mock_db            |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)


mysql> use testdb;
Database changed

mysql> show tables;
Empty set (0.00 sec)

```

恢復資料

```
➜  ~ mysql -u root -p testdb < ~/Desktop/kkk.sql;
Enter password:
```

再查一次

```
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| person           |
+------------------+
1 row in set (0.00 sec)

mysql> select *from person;
+----+----------------+------+
| id | name           | city |
+----+----------------+------+
|  1 | zhangsanfeng   |      |
|  3 | zhaomin        |      |
|  4 | xiaozhao       |      |
|  5 | jinnmaoshiwang |      |
|  6 | baimei         |      |
+----+----------------+------+
5 rows in set (0.00 sec)

mysql>
```

表相關

增加表

create table 表名(列及型別);

mysql> create table person( 
     > id int auto_increment primary key,
     > name varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)

通過show tables檢視

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| person           |
+------------------+
1 row in set (0.00 sec)

mysql>

檢視錶屬性資訊

desc 表名稱;

mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

刪除表

drop table 表名;

現在有2張表

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| person           |
| test2            |
+------------------+
2 rows in set (0.00 sec)

刪除test2表

mysql> drop table test2;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| person           |
+------------------+
1 row in set (0.00 sec)

修改表

alter table 表名 add | change | drop 列名型別;

比如：

將person表的gender 改成gender2;
```
alter table person change gender gender2 bit;
```
給person增加city列
```
alter table person add city varchar(20);
```
刪除person的gender2
```
alter table person drop gender2;
```

資料相關

查詢表資料

select * from 表名;

新增資料

(1). 全列增加單個： insert into 表名 values(...);

mysql> insert into person values(0,'zhangsanfeng');
Query OK, 1 row affected (0.01 sec)
mysql>

查詢結果

mysql> select * from person;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | zhangsanfeng |
+----+--------------+
1 row in set (0.00 sec)

mysql>

(2). 全列增加多個： insert into 表名 values(...),(...);

mysql> insert into person values(0,'zhangwuji'),(0,'zhaomin');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

檢視一下

mysql> select * from person;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | zhangsanfeng |
|  2 | zhangwuji    |
|  3 | zhaomin      |
+----+--------------+
3 rows in set (0.00 sec)

mysql>

(3). 單列增加單個： insert into 表名(列名) values(...);

mysql> insert into person(name) values('xiaozhao');
Query OK, 1 row affected (0.01 sec)

檢視

mysql> select * from person;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | zhangsanfeng |
|  2 | zhangwuji    |
|  3 | zhaomin      |
|  4 | xiaozhao     |
+----+--------------+
4 rows in set (0.00 sec)

mysql>

(4). 單列增加多個： insert into 表名(列名) values(...),values(...);

mysql> insert into person(name) values('jinnmaoshiwang'),('zhangcuishan');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

檢視

```
mysql> select * from person;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | zhangsanfeng   |
|  2 | zhangwuji      |
|  3 | zhaomin        |
|  4 | xiaozhao       |
|  5 | jinnmaoshiwang |
|  6 | zhangcuishan   |
+----+----------------+
6 rows in set (0.00 sec)

mysql>
```

更新資料

update 表名 set 列名=值 where 條件

mysql> update person set name='baimei' where id=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

檢視

mysql> select *from person;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | zhangsanfeng   |
|  2 | zhangwuji      |
|  3 | zhaomin        |
|  4 | xiaozhao       |
|  5 | jinnmaoshiwang |
|  6 | baimei         |
+----+----------------+
6 rows in set (0.00 sec)

mysql>

刪除資料

delete from 表名 where 條件

在person表中刪除id為2的資料

mysql> delete from person where id=2;
Query OK, 1 row affected (0.01 sec)

檢視

mysql> select *from person;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | zhangsanfeng   |
|  3 | zhaomin        |
|  4 | xiaozhao       |
|  5 | jinnmaoshiwang |
|  6 | baimei         |
+----+----------------+
5 rows in set (0.00 sec)

mysql>

常見資料查詢操作

1. 模糊查詢

like
%表示任意多個，也即是可有可無
_表示一個任意字元，至少一個

person表內容

mysql> select * from person;
+----+----------------+----------+
| id | name           | city     |
+----+----------------+----------+
|  1 | zhangsanfeng   | Shanghai |
|  3 | zhaomin        | 北京      |
|  4 | xiaozhao       |          |
|  5 | jinnmaoshiwang | 北京     |
|  6 | baimei         |          |
+----+----------------+----------+
5 rows in set (0.00 sec)

mysql>

查詢城市是北京的人的名字

mysql> select name from person where city  like '北京';
+----------------+
| name           |
+----------------+
| zhaomin        |
| jinnmaoshiwang |
+----------------+
2 rows in set (0.01 sec)

mysql>

查詢以北開頭的人的名字，使用%和_都可以

mysql> select name from person where city  like '北%';
+----------------+
| name           |
+----------------+
| zhaomin        |
| jinnmaoshiwang |
+----------------+
2 rows in set (0.00 sec)


mysql> select name from person where city  like '北_';
+----------------+
| name           |
+----------------+
| zhaomin        |
| jinnmaoshiwang |
+----------------+
2 rows in set (0.00 sec)

修改一下zhaomin的city區分一下%和_;

mysql> update person set city='北' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person;
+----+----------------+----------+
| id | name           | city     |
+----+----------------+----------+
|  1 | zhangsanfeng   | Shanghai |
|  3 | zhaomin        | 北       |
|  4 | xiaozhao       |          |
|  5 | jinnmaoshiwang | 北京     |
|  6 | baimei         |          |
+----+----------------+----------+
5 rows in set (0.00 sec)

再執行下上面的

mysql> select name from person where city  like '北%';
+----------------+
| name           |
+----------------+
| zhaomin        |
| jinnmaoshiwang |
+----------------+
2 rows in set (0.00 sec)

mysql>
mysql> select name from person where city  like '北_';
+----------------+
| name           |
+----------------+
| jinnmaoshiwang |
+----------------+
1 row in set (0.00 sec)

2. 聚合查詢

簡寫	含義
min	最小值
max	最大值
avg	平均值
sum	求和
count	數量

mysql> select *from person;
+----+----------------+--------+
| id | name           | city   |
+----+----------------+--------+
|  1 | zhangsanfeng   | 上海   |
|  3 | zhaomin        | 北     |
|  4 | xiaozhao       | 上海   |
|  5 | jinnmaoshiwang | 北京   |
|  6 | baimei         | 上海   |
+----+----------------+--------+
5 rows in set (0.00 sec)

求city是上海的人的總和

mysql> select count(*) from person where city='上海';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

求city是上海的人中id最大的的人

mysql> select max(id) from person where city='上海';
+---------+
| max(id) |
+---------+
|       6 |
+---------+
1 row in set (0.01 sec)

其他的和上面的類似

3. 分組查詢

查詢每個city的人的總數

mysql> select city ,count(*) from person group by city;
+--------+----------+
| city   | count(*) |
+--------+----------+
| 上海   |        3 |
| 北     |        1 |
| 北京   |        1 |
+--------+----------+

對結果進行排序,使用order by

將上面的結果按照總數進行，升序排序

mysql> select city ,count(*) as 總數 from person group by city order by 總數 asc;
+--------+--------+
| city   | 總數   |
+--------+--------+
| 北     |      1 |
| 北京   |      1 |
| 上海   |      3 |
+--------+--------+
3 rows in set (0.00 sec)

上面的as是取別名

單獨看上海的有多少人，使用having

mysql> select city ,count(*) from person group by city having city='上海';
+--------+----------+
| city   | count(*) |
+--------+----------+
| 上海   |        3 |
+--------+----------+
1 row in set (0.00 sec)

分頁查詢，使用limit

select * from 表名 limit start,count

start，表示從第幾個元素開始；
count，查詢幾個

mysql> select * from person;
+----+----------------+--------+
| id | name           | city   |
+----+----------------+--------+
|  1 | zhangsanfeng   | 上海   |
|  3 | zhaomin        | 北     |
|  4 | xiaozhao       | 上海   |
|  5 | jinnmaoshiwang | 北京   |
|  6 | baimei         | 上海   |
|  7 | zhangsan1      | 海南   |
|  8 | zhangsan2      | 海南   |
|  9 | zhangsan3      | 海南   |
| 10 | zhangsan4      | 海南   |
| 11 | zhangsan5      | 海南   |
| 12 | zhangsan6      | 海南   |
| 13 | zhangsan7      | 海南   |
| 14 | zhangsan8      | 海南   |
+----+----------------+--------+
13 rows in set (0.00 sec)

需求：分頁查詢，每頁2個，從0開始；

第一頁
mysql> select * from person limit 0,3;
+----+--------------+--------+
| id | name         | city   |
+----+--------------+--------+
|  1 | zhangsanfeng | 上海   |
|  3 | zhaomin      | 北     |
|  4 | xiaozhao     | 上海   |
+----+--------------+--------+
3 rows in set (0.00 sec)

第二頁
mysql> select * from person limit 3,3;
+----+----------------+--------+
| id | name           | city   |
+----+----------------+--------+
|  5 | jinnmaoshiwang | 北京   |
|  6 | baimei         | 上海   |
|  7 | zhangsan1      | 海南   |
+----+----------------+--------+
3 rows in set (0.00 sec)

第三頁
mysql> select * from person limit 6,3;
+----+-----------+--------+
| id | name      | city   |
+----+-----------+--------+
|  8 | zhangsan2 | 海南   |
|  9 | zhangsan3 | 海南   |
| 10 | zhangsan4 | 海南   |
+----+-----------+--------+
3 rows in set (0.00 sec)

having和where區別

where是對原始資料進行篩選；
having是對篩選的結果進行篩選。

該文會持續補充。。。

Centos 常見操作
2020-12-18
CentOS
Mysql系列第四講 DDL常見操作彙總
2020-09-26
MySql
常見的DOM操作
2018-07-19
JS常見的字串操作
2019-02-21
JS字串
Linux 常見高危操作
2014-05-23
Linux
UE常見快捷鍵操作
2010-01-29
Oracle redo log 常見操作
2007-11-10
Oracle Redo
adb常見操作命令
2024-04-26
MySQL 常見錯誤
2019-06-04
MySql
Mysql 常見面試題
2021-11-18
MySql面試題
MySQL常見索引概念
2018-07-09
MySql索引
mysql常見問題
2013-07-17
MySql
MySQL 常見問題
2010-07-20
MySql
【MySQL】常見的mysql 程式state
2015-02-11
MySql
JavaScript 陣列常見操作(一)
2022-02-05
JavaScript陣列
JavaScript 陣列常見操作 (二)
2022-02-06
JavaScript陣列
Linux 下常見操作命令
2021-09-08
Linux
selenium定位與常見操作
2020-10-28
Linux常見操作小結
2020-10-22
Linux
華為交換機常見QinQ操作
2021-10-26
表空間常見的操作
2015-01-08
linux常見的操作（轉）
2012-06-25
Linux
mysql面試常見題目
2018-05-20
MySql面試
mysql replication常見錯誤
2013-04-11
MySql
Ceph常規操作及常見問題梳理
2016-06-08
華為交換機常見STP/RSTP操作
2021-10-26
Python常見資料框操作①
2018-06-13
Python
mysql常見資料型別
2020-07-14
MySql資料型別
MySQL 安裝常見錯誤
2019-10-12
MySql
mysql8 常見錯誤
2019-10-22
MySql
mysql常見問題總結
2021-04-17
MySql
MySQL 之索引常見內容
2021-01-20
MySql索引
MySQL Replication常見錯誤整理
2013-11-06
MySql
MYSQL常見出錯程式碼
2011-11-17
MySql
大資料學習之路——MySQL基礎（一）——MySQL的基礎知識與常見操作
2021-05-29
大資料MySql
git 常見問題及操作方法
2018-10-27
Git
Linux 和 macos 常見的埠操作
2021-10-27
LinuxMac
mysql常識和基本操作(轉)
2007-08-16
MySql