MySQL基礎操作與數據類型

目錄

• 1.文件夾(庫)
• 2.文件(表)
• 3.文件的一行內容
• 4.創建表的完整語法
• 5.整型類型
• 6.補充sql_mode
• 7.浮點型
• 8.字元類型
• 9.日期類型
• 10.枚舉與集合類型

1.文件夾(庫)

查看字元集編碼的指令

show variables like "%char%";

MySQL客戶端連接服務端時的完整指令

mysql -h 127.0.0.1 -P 3306 -u root -p

1.增:
create database 資料庫名 charset utf8(指定該庫的編碼方式);
2.改:
alter database 資料庫名 charset gbk;(更改編碼方式)
3.查:
show databases; # 查所有庫的庫名
show create database 資料庫名; # 單獨查看某一個庫的信息
4.刪:
drop database 資料庫名

2.文件(表)

use 資料庫名; # 首先切換文件夾
select database(); # 查看當前所在的文件夾

1.增:
create table 表名(id int,name char);
2.改:
    2.1 修改存儲引擎
        mysql> alter table service 
            -> engine=innodb;

    2.2 添加欄位
        mysql> alter table student10
            -> add name varchar(20) not null,
            -> add age int(3) not null default 22;

        mysql> alter table student10
            -> add stu_num varchar(10) not null after name;                //添加name欄位之後

        mysql> alter table student10                        
            -> add sex enum('male','female') default 'male' first;          //添加到最前面

    2.3 刪除欄位
        mysql> alter table student10
            -> drop sex;

        mysql> alter table service
            -> drop mac;

    2.4 修改欄位類型modify
        mysql> alter table student10
            -> modify age int(3);
        mysql> alter table student10
            -> modify id int(11) not null primary key auto_increment;    //修改為主鍵

    2.5 增加約束（針對已有的主鍵增加auto_increment）
        mysql> alter table student10 modify id int(11) not null primary key auto_increment;
        ERROR 1068 (42000): Multiple primary key defined

        mysql> alter table student10 modify id int(11) not null auto_increment;
        Query OK, 0 rows affected (0.01 sec)
        Records: 0  Duplicates: 0  Warnings: 0

    2.6 對已經存在的表增加複合主鍵
        mysql> alter table service2
            -> add primary key(host_ip,port);        

    2.7 增加主鍵
        mysql> alter table student1
            -> modify name varchar(10) not null primary key;

    2.8 增加主鍵和自動增長
        mysql> alter table student1
            -> modify id int not null primary key auto_increment;

    2.9 刪除主鍵
        a. 刪除自增約束
        mysql> alter table student10 modify id int(11) not null; 

        b. 刪除主鍵
        mysql> alter table student10                                 
            -> drop primary key;
3.查:
show tables; # 查看當前庫下所有的表名
show create table 表名; # 查看表的詳細信息
desc 表名; # 查看表結構
4.刪:drop table 表名;

3.文件的一行內容

1.增:
insert into 資料庫名.表名 values(1,'la'),(2,'lala'),(3,'lalala');
2.改:
update 庫名.表名 set name='sb' where id > 1;
3.查:
select id,name from 庫名.表名;  # 查 id和name
select * from 庫名.表名;    # 查所有
4.刪:
delete from 庫名.表名 where name='qwe';
truncate 庫名.表名; #當數據量比較大的情況下，使用這種方式，刪除速度快 也可刪除自增帶來的問題

創建不同引擎的表

create table t1(id int)engine = innodb;
create table t2(id int)engine = myisam;
create table t3(id int)engine = blackhole;
create table t4(id int)engine = memory;

4.創建表的完整語法

一 創建表的完整語法
create table 表名(
欄位名1 類型[(寬度) 約束條件],
欄位名2 類型[(寬度) 約束條件],
欄位名3 類型[(寬度) 約束條件]
);

#解釋：
類型：使用限制欄位必須以什麼樣的數據類型傳值
約束條件：約束條件是在類型之外添加一種額外的限制

# 註意：
1. 在同一張表中，欄位名是不能相同
2. 寬度和約束條件可選，欄位名和類型是必須的
3、最後一個欄位後不加逗號

create database db37;

5.整型類型

1、作用：id號，各種號碼，年齡，等級
2、分類：
tinyint（**）
int （*****）
bigint（***）

3、測試：預設整型都是有符號的
create table t1(x tinyint);
insert into t1 values(128),(-129);

create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);

create table t3(x int unsigned);
#4294967295
insert into t3 values(4294967296);

create table t4(x int(12) unsigned);
insert into t4 values(4294967296123);


4、強調：對於整型來說，數據類型後的寬度並不是存儲限制，而是顯示限制
    所以在創建表示，如果欄位採用的是整型類型，完全無需指定顯示寬度，
    預設的顯示寬度，足夠顯示完整當初存放的數據

# 顯示時，不夠8位用0填充，如果超出8位則正常顯示
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);
insert into t5 values(1);

6.補充sql_mode

# 查看sql_mode
mysql> show variables like "%sql_mode%";
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR        |
| block_encryption_mode      | aes-128-ecb         |
| gtid_mode                  | OFF                 |
| innodb_autoinc_lock_mode   | 1                   |
| innodb_strict_mode         | OFF                 |
| pseudo_slave_mode          | OFF                 |
| slave_exec_mode            | STRICT              |
| sql_mode                   | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.00 sec)
#修改sql_mode為嚴格模式：在該模式下，如果插入的數據超過限制，則會立即報錯

# 模式設置和修改(以解決非嚴格模式下,插入大於限制的數不報錯):

    方式一:先執行select @@sql_mode,複製查尋出來的值並將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除，然後執行set sql_mode='修改後的值'或者set session sql_mode='修改後的值';，例如：set session sql_mode='STRICT_TRANS_TABLES';改為嚴格模式 # session可以不用寫

    # 此方法只在當前會話中生效，關閉當前會話就不生效了。

    方式二：先執行select @@global.sql_mode,複製查詢出來的值並將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除，然後執行set global sql_mode = '修改後的值'。

    此方法在當前服務中生效，重新MySQL服務後失效
    方法三：在mysql的安裝目錄下，或my.cnf文件(windows系統是my.ini文件)，新增 sql_mode = STRICT_TRANS_TABLES

添加my.cnf如下：
[mysqld]            sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

7.浮點型

作用：存儲身高、體重、薪資
分類：
float （*****）
double （**）
decimal （**）

測試：
#相同點
#1、對於三者來說，都能存放30位小數，
#不同點：
1、精度的排序從低到高：float,double,decimal
2、float與double類型能存放的整數位比decimal更多


create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);

mysql> select * from t9;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t10;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t11;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

8.字元類型

1、作用：姓名，地址，描述類的信息

2、分類：
char 定長
varchar 變長

3、測試:字元的寬度限制單位是字元個數
create table t12(x char(4)); # 超出4個字元則報錯，不夠4個字元則用空格補全成4個字元
create table t13(y varchar(4));# 超出4個字元則報錯，不夠4個字元那麼字元有幾個就存幾個

insert into t12 values('hello');
insert into t13 values('hello');

insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
select char_length(x) from t12; #4
select char_length(y) from t13; #1

# 註意：
針對char類型，mysql在存儲時會將數據用空格補全存放到硬碟中
但會在讀出結果時自動去掉末尾的空格，因為末尾的空格在以下場景中是無用
mysql> select * from t14 where name="lxx"; # name欄位明確地等於一個值，該值後填充空格是沒有用


mysql> select * from t14 where name like "lxx"; # name欄位模糊匹配一個值，該值後填充空格是有用的

# 對比char與varchar

name char(5)
# 缺點：浪費空間
# 優點：存取速度都快
egon alex lxx  wxx  yx

name varchar(5)
# 缺點：存取速度都慢
# 優點：節省空間
(1bytes+egon)(1bytes+alex)(1bytes+lxx)

9.日期類型

1、作用：時間相關
2、分類：
date：1999-01-27
time: 11:11:11
datetime:1999-01-27 11:11:11
year:1999
3、測試
create table student(
    id int,
    name char(16),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
);
insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');

10.枚舉與集合類型

枚舉類型(enum)
# 示例:
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

集合類型(set)
A SET column can have a maximum of 64 distinct members.
# 示例：
    CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
    INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

# 作用與分類：
枚舉enum:多選一個
集合set:多選多,如選著多個相同的會自動去重