mysql md 123456_mysql/mysql.md · sdbxpjzq/docs - Gitee.com
[TOC]
# 創建數據庫
```sql
CREATE DATABASE zongqi;
```
每個數據庫對應有個opt文件,每個表都有frm文件.有的表有3個文件(和表設置的類型有關).
# 數據庫編碼問題
### utf8 和 utf8mb4(支持表情)
utf8mb4兼容utf8，且比utf8能表示更多的字符。在做移動應用時，會遇到ios用戶會在文本的區域輸入emoji表情，如果不做一定處理，就會導致插入數據庫異常。需要 >= MySQL 5.5.3版本、從庫也必須是5.5的了、低版本不支持這個字符集、復制報錯
### 查看字符集設置
`show variables like 'collation_%';`
`show variables like 'character_set_%';`
# 表操作
## 創建表
```sql
CREATE table sales_activity_honey_center {
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) not null default 0 COMMENT '用戶id',
`ling_num` int(11) COMMENT '領取次數'
`ling_time` timestamp DEFAULT NULL COMMENT '領取事件'
-- 下面2行建議必須寫
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
}ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
## 修改表
### add
```sql
alter table [tb_name] add [字段] [約束條件]
```
### change
```sql
alter table [table_name] change [舊字段] [新字段] [約束條件]
```
### drop
```sql
alter table [tb_name] drop [字段];
```
### modify
```sql
alter table [tb_name] modify [列名] [約束定義];
```
## 刪除表
```sql
drop table if exists tb_name;【若存在 刪除 推薦】 這樣不會報錯，只是警告
```
## 清空表
相當于 delete 語句不寫 where 子句一樣
```sql
truncate [table_name];
```
## 查看創建表語句
```sql
Show create table [table_name]
```
# 列屬性
## unsigned
- 只能用于數值類型,表示無符號的。
- 不允許數據出現負數。
- 緊跟在 數字類型后面，
```sql
create table if not exists USER(
id int(10) unsigned not null auto_increment primary key ,
username varchar(20) not null
);
```
## zerofill
- 只能用于數值類型，在數值之前自動用0 補齊不足的位數
- 當給一個字段使用`zerofill`修飾時，該字段自動應用`unsigned`。
## auto_increment
自動增量屬性。此字段**不允許重復**，只能修飾**整數類**的字段，
設置auto_increment必須設置一個key
## null和 not null
null是特殊的值，代表無值，與0和空字符串都不相同
## default
指定一個默認值
## 時間類型
| 類型 | 大小(字節) | 范圍 | 格式 | 用途 |
| --------- | ------ | --------------------------------------- | ------------------- | ------------ |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值年月日 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 時間值或持續時間時分秒 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和時間值 |
| TIMESTAMP | 8 | 1970-01-01 00:00:00/2038 年某時 | YYYYMMDD HHMMSS | 混合日期和時間值，時間戳 |
- timestamp
需要經常插入或者更新日期為當前系統時間，推薦使用 timestamp來標識.返回String.
# 索引
### 主鍵索引 primary key
一個表只能指定一個主鍵，且主鍵的值不能為空。
### 唯一索引 unique
一個表中可以有多個唯一索引。
主要是為了避免數據出現重復。
主鍵和唯一鍵約束是通過參考索引實施的，如果插入的值均為NULL，則根據索引的原理，全NULL值不被記錄在索引上，所以插入全NULL值時，可以有重復的，而其他的則不能插入重復值。
alter table t add constraint uk_t_1 unique (a,b);
insert into t (a ,b ) values (null,1); # 不能重復
insert into t (a ,b ) values (null,null);#可以重復 .
1. 創建表的時候直接指定
```sql
CREATE TABLE `sales_activity_honey` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint(4) DEFAULT '0' COMMENT '活動類型ID',
`uid` int(11) NOT NULL DEFAULT '0' COMMENT '用戶id',
`num` int(11) DEFAULT NULL COMMENT '連續領取次數',
`honey` int(11) DEFAULT NULL COMMENT '總領取數量',
`last_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `unq_uid_type` (`uid`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
2. 創建索引
```sql
CREATE UNIQUE INDEX indexName ON mytable(username(length))
// length注意:
如果是CHAR，VARCHAR類型，length可以小于字段實際長度；
如果是BLOB和TEXT類型，必須指定 length。
```
3. 修改表結構
```sql
ALTER table mytable ADD UNIQUE [uniq_name] ([字段1,字段2])
```
4. 刪除索引
```sql
alter table [table_name] drop key [key_name]
```
?
### 常規索引 index || key
關鍵字 index 或 key
索引應該建立在搜索，排序，分組等操涉及的數據列上，就是在 where字句，from字句，order by字句，group by字句
不要建立太多索引，索引會消耗系統資源。
1. 創建表的時候直接指定
```sql
create table if not exists you(
id int not null ,
youname CHAR(20),
index indexname(youname(20))
);
```
2. 創建索引
```sql
CREATE INDEX indexName ON mytable(username(length));
```
3. 修改表結構
```sql
ALTER mytable ADD INDEX [indexName] ON (username(length))
```
### 刪除索引
```sql
DROP INDEX [indexName] ON mytable;
```
# CURD
## 插入數據
```sql
insert into [tables_name](column1,column2,...) values(value1, value2,..)
```
### 更新數據
```sql
update [tables_name]set column1 = new_value1,column2 = new_value2,… WHERE definition
```
**update 表達式**
```sql
UPDATE article SET pv = pv+1 WHERE id = 123
```
**同時更新多個表**
```sql
update emp as a,dept as b set
```
## 查詢數據
```sql
select column1,column1,... FROM tb_name
```
## 替換數據
【主鍵或唯一索引沖突，執行替換，否則插入】
# 連表查詢
## 內連接
選出兩張表中相互匹配的記錄，連接的多個數據必須存在才能進行連接 .
方式一:
```sql
select a.ename, b.deptname from emp as a,dept as b where a.deptno=b.deptno; //在沒有用關鍵字的情況下，使用on條件會報錯
```
方式二:
`inner join`關鍵字
```sql
select * from news inner join you on news.id = you.id;
```
方式三:
`using()`關鍵字
要求：**連接的兩個實體之間的字段名稱一致**
```sql
select * from news inner join you using(id);
```
## 外連接
### 左連接(left outer join)
包含所有左邊表中的記錄，甚至是右表中沒有和他相匹配的記錄.
### 右連接(right outer join)
包含所有的右邊的記錄，甚至是左邊表中沒有與他相匹配的記錄
### 注意事項
1. 不支持`where`作為連接條件, 使用 `on`
2. `using`：會去掉結果中的重復字段，并將判斷條件放在第一列
3. 外連接不能缺少連接條件，否則會報錯
```sql
A left join B ON 條件表達式;
select * from news left join you on news.id=you.id;
```
## 自然連接(natural join)
通過mysql自己的判斷完成連接的過程，不需要指定連接條件，mysql會使用多表內的，相同的字段，作為連接條件。
```sql
select * from table1 natrual join table2;
```
分類：
- 內連接：`natural join`
- 左外連接：`natural left join`
- 右外連接：`natural right join`
# SQL中on條件與where條件的區別
數據庫在通過連接兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然后再將這張臨時表返回給用戶。
在使用`left jion`時，`on`和`where`條件的區別如下：
1、 `on`條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。
2、`where`條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有`left join`的含義(必須返回左邊表的記錄)了，條件不為真的就全部過濾掉。
# 聯合查詢 - union ,union all
合并兩個或多個 SELECT 語句的結果集
`union` 、`union all`
## union和union all的區別
- union all是把結果直接合并在一起
- union是將union all的后的結果進行一次`distinct`(去重),之后的結果
栗子:
```sql
(select id from news)
union all
(select id from you);//推薦將select語句用()包起來
```
```txt
id
--------
2
3
4
5
2
3
```
```sql
(select id from news)
union
(select id from you);
```
```txt
id
--------
2
3
4
5
```
## 注意事項
1. **子語句的 order by，只有在配合limit時，才會生效**.
原因：union會對沒有limit 的 order by 忽略。
```sql
(SELECT id,name FROM table_name1 order by id limit 100)
union
(SELECT id,name FROM table_name2 order by id limit 100);
```
對union結果排序
```sql
(SELECT id, name FROM table_name1)
union
(SELECT id,name FROM table_name2)order by id asc|desc;
```
2. **UNION 內部的 SELECT 語句必須擁有相同數量的列**
3. **列也必須擁有相似的數據類型。同時，每條 SELECT 語句中的列的順序必須相同**
# 用戶
## 創建用戶
```sql
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```
username - 你將創建的用戶名,
host - 指定該用戶在哪個主機上可以登陸.`%` 表示任意主機
password - 該用戶的登陸密碼
## 授權
```sql
GRANT privileges ON databasename.tablename TO 'username'@'host'
```
privileges - 用戶的操作權限,如SELECT , INSERT , UPDATE 等. `ALL`表示授予所的權限.
databasename - 數據庫名
tablename-表名,
如果要授予該用戶對所有數據庫和表的相應操作權限則可用`*`表示
栗子:
```sql
GRANT ALL ON zongqi.* TO 'zongqi'@'%'
```
## 撤銷用戶權限
```sql
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
```
注意: 假如你在給用戶'pig'@'%'授權的時候是這樣的(或類似的):
`GRANT SELECT ON test.user TO 'pig'@'%'`, 則在使用`REVOKE SELECT ON *.* FROM 'pig'@'%'`;命令并不能撤銷該用戶對test數據庫中user表的SELECT 操作.
如果授權使用的是`GRANT SELECT ON *.* TO 'pig'@'%'`;則`REVOKE SELECT ON test.user FROM 'pig'@'%'`;命令也不能撤銷該用戶對test數據庫中user表的Select 權限.
## 設置與更改用戶密碼
```sql
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是當前登陸用戶用SET PASSWORD = PASSWORD("newpassword");
```
例子:
```sql
SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
```
## 刪除用戶
```sql
DROP USER 'username'@'host';
```
# 模式問題
## 查看模式
```sql
select @@sql_mode;
SELECT @@GLOBAL.sql_mode;
```
## 修改模式
需要root權限
### 修改配置文件
` vim /etc/my.cnf`
在`[mysqld]`下面添加如下列：
`sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES`
### 執行sql
```sql
SET @@sql_mode="NO_ENGINE_SUBSTITUTION";
SET @@GLOBAL.sql_mode="NO_ENGINE_SUBSTITUTION";
```
http://www.ywnds.com/?p=8865
一鍵復制
編輯
Web IDE
原始數據
按行查看
歷史
總結
以上是生活随笔為你收集整理的mysql md 123456_mysql/mysql.md · sdbxpjzq/docs - Gitee.com的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 入选最受关注AI公司的Kitt.AI：多
- 下一篇： 范德堡大学计算机专业研究生,范德堡大学计