天天看點
傳回

SQL資料庫操作指令SQL基礎知識MySQL 資料類型SQL Date 資料類型代碼示例

參考：

SQL 教程 

http://www.w3school.com.cn/sql/index.asp SQL 快速參考  http://www.w3school.com.cn/sql/sql_quickref.asp

SQL基礎知識 

SQL 通路和處理資料庫
對大小寫不敏感！
每條 SQL 指令的末端使用分号

文本值, 單引号來環繞
數值，請不要使用引号

RDBMS - 關系資料庫管理系統（Relational Database Management System）

WHERE 關鍵字無法與合計函數一起使用

資料操作語言 (DML) 和 資料定義語言 (DDL)

    # 資料操作語言 (DML)
    SELECT - 從資料庫表中擷取資料
    UPDATE - 更新資料庫表中的資料
    DELETE - 從資料庫表中删除資料
    INSERT INTO - 向資料庫表中插入資料

    # 資料定義語言 (DDL)
    CREATE DATABASE - 建立新資料庫
    ALTER DATABASE - 修改資料庫
    CREATE TABLE - 建立新表
    ALTER TABLE - 變更（改變）資料庫表
    DROP TABLE - 删除表
    CREATE INDEX - 建立索引（搜尋鍵）
    DROP INDEX - 删除索引

ORDER BY 預設升序排序, 降序 DESC

-- 通配符
- % 替代一個或多個字元
- _ 僅替代一個字元
- [charlist]    字元列中的任何單一字元
- [^charlist]或者[!charlist]不在字元列中的任何單一字元

-- SQL 限制
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

MySQL 資料類型 

在 MySQL 中，有三種主要的類型：文本、數字和日期/時間類型。

-- Text 類型：

資料類型  描述
CHAR(size)  固定長度字元串 最多 255 個字元。
VARCHAR(size) 可變長度的字元串 最多 255 個字元。

注釋：如果值的長度大于 255，則被轉換為 TEXT 類型。

TINYTEXT  存放最大長度為 255 個字元的字元串。
TEXT  存放最大長度為 65,535 個字元的字元串。
BLOB  用于 BLOBs (Binary Large OBjects)。存放最多 65,535 位元組的資料。
MEDIUMTEXT  存放最大長度為 16,777,215 個字元的字元串。
MEDIUMBLOB  用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 位元組的資料。
LONGTEXT  存放最大長度為 4,294,967,295 個字元的字元串。
LONGBLOB  用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 位元組的資料。
ENUM(x,y,z,etc.)  
允許你輸入可能值的清單。可以在 ENUM 清單中列出最大 65535 個值。如果清單中不存在插入的值，則插入空值。

注釋：這些值是按照你輸入的順序存儲的。

可以按照此格式輸入可能的值：ENUM('X','Y','Z')

SET 與 ENUM 類似，SET 最多隻能包含 64 個清單項，不過 SET 可存儲一個以上的值。

-- Number 類型：

資料類型  描述
TINYINT(size) -128 到 127 正常。0 到 255 無符号*。在括号中規定最大位數。
SMALLINT(size)  -32768 到 32767 正常。0 到 65535 無符号*。在括号中規定最大位數。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 無符号*。在括号中規定最大位數。
INT(size) -2147483648 到 2147483647 正常。0 到 4294967295 無符号*。在括号中規定最大位數。
BIGINT(size)  -9223372036854775808 到 9223372036854775807 正常。0 到 18446744073709551615 無符号*。在括号中規定最大位數。
FLOAT(size,d) 帶有浮動小數點的小數字。在括号中規定最大位數。在 d 參數中規定小數點右側的最大位數。
DOUBLE(size,d)  帶有浮動小數點的大數字。在括号中規定最大位數。在 d 參數中規定小數點右側的最大位數。
DECIMAL(size,d) 作為字元串存儲的 DOUBLE 類型，允許固定的小數點。
* 這些整數類型擁有額外的選項 UNSIGNED。通常，整數可以是負數或正數。如果添加 UNSIGNED 屬性，那麼範圍将從 0 開始，而不是某個負數。

-- Date 類型：

資料類型  描述
DATE()  
日期。格式：YYYY-MM-DD

注釋：支援的範圍是從 '1000-01-01' 到 '9999-12-31'

DATETIME()  
*日期和時間的組合。格式：YYYY-MM-DD HH:MM:SS

注釋：支援的範圍是從 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'

TIMESTAMP() 
*時間戳。TIMESTAMP 值使用 Unix 紀元('1970-01-01 00:00:00' UTC) 至今的描述來存儲。格式：YYYY-MM-DD HH:MM:SS

注釋：支援的範圍是從 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC

TIME()  時間。格式：HH:MM:SS 注釋：支援的範圍是從 '-838:59:59' 到 '838:59:59'
YEAR()  
2 位或 4 位格式的年。

注釋：4 位格式所允許的值：1901 到 2155。2 位格式所允許的值：70 到 69，表示從 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 傳回相同的格式，它們的工作方式很不同。在 INSERT 或 UPDATE 查詢中，TIMESTAMP 自動把自身設定為目前的日期和時間。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

SQL Date 資料類型 

MySQL 使用下列資料類型在資料庫中存儲日期或日期/時間值：

DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
YEAR - 格式 YYYY 或 YY

MySQL Date 函數
下面的表格列出了 MySQL 中最重要的内建日期函數：

函數  描述
NOW() 傳回目前的日期和時間
CURDATE() 傳回目前的日期
CURTIME() 傳回目前的時間
DATE()  提取日期或日期/時間表達式的日期部分
EXTRACT() 傳回日期/時間按的單獨部分
DATE_ADD()  給日期添加指定的時間間隔
DATE_SUB()  從日期減去指定的時間間隔
DATEDIFF()  傳回兩個日期之間的天數
DATE_FORMAT() 用不同的格式顯示日期/時間

代碼示例 

$ mysql -uroot -p
> show databases;
> use demo;
> show tables;

-- 建立表
create table persons(
id int auto_increment primary key,
name varchar(32),
age int,
city varchar(32));

-- 删除表
> drop table persons;

-- 插入資料
insert into persons(name, age, city) value('張三', 20, '北京');
insert into persons(name, age, city) value('李四', 21, '上海');
insert into persons(name, age, city) value('趙倩', 24, '北京');
insert into persons(name, age, city) value('孫力', 23, '天津');
insert into persons(name, age, city) value('周五', 22, '深圳');
insert into persons(name, age, city) value('鄭旺', 20, '重慶');

insert into persons(name, age, city) value('劉穎', 20, '北京');
insert into persons(name, age, city) value('王大', 20, '天津');
insert into persons(name, age, city) value('張開', 20, '深圳');
insert into persons(name, age, city) value('留取', 20, '重慶');

-- 查詢所有
select * from persons;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  1 | 張三   |   20 | 北京   |
|  2 | 李四   |   21 | 上海   |
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
|  5 | 周五   |   22 | 深圳   |
|  6 | 鄭旺   |   20 | 重慶   |
+----+--------+------+--------+

-- 查詢部分字段
mysql> select name from persons;
+--------+
| name   |
+--------+
| 張三   |
| 李四   |
| 趙倩   |
| 孫力   |
| 周五   |
| 鄭旺   |
+--------+

mysql> select age from persons;
+------+
| age  |
+------+
|   20 |
|   21 |
|   24 |
|   23 |
|   22 |
|   20 |
+------+

-- 去重
mysql> select distinct age from persons;
+------+
| age  |
+------+
|   20 |
|   21 |
|   24 |
|   23 |
|   22 |
+------+

-- where條件
mysql> select * from persons where age >22;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
+----+--------+------+--------+


mysql> select * from persons where name='趙倩';
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  3 | 趙倩   |   24 | 北京   |
+----+--------+------+--------+

-- 多條件查詢
mysql> select * from persons where age=20 and city='北京';
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  1 | 張三   |   20 | 北京   |
+----+--------+------+--------+

mysql> select * from persons where (age=20 or age =24) and city='北京';
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  1 | 張三   |   20 | 北京   |
|  3 | 趙倩   |   24 | 北京   |
+----+--------+------+--------+

-- 排序，預設從小到大
mysql> select * from persons order by age;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  1 | 張三   |   20 | 北京   |
|  6 | 鄭旺   |   20 | 重慶   |
|  2 | 李四   |   21 | 上海   |
|  5 | 周五   |   22 | 深圳   |
|  4 | 孫力   |   23 | 天津   |
|  3 | 趙倩   |   24 | 北京   |
+----+--------+------+--------+

-- 多字段排序
mysql> select * from persons order by age, id desc;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  6 | 鄭旺   |   20 | 重慶   |
|  1 | 張三   |   20 | 北京   |
|  2 | 李四   |   21 | 上海   |
|  5 | 周五   |   22 | 深圳   |
|  4 | 孫力   |   23 | 天津   |
|  3 | 趙倩   |   24 | 北京   |
+----+--------+------+--------+

-- 更新資料
mysql> update persons set name='張大拿' where name='張三';
mysql> select * from persons;
+----+-----------+------+--------+
| id | name      | age  | city   |
+----+-----------+------+--------+
|  1 | 張大拿    |   20 | 北京   |
|  2 | 李四      |   21 | 上海   |
|  3 | 趙倩      |   24 | 北京   |
|  4 | 孫力      |   23 | 天津   |
|  5 | 周五      |   22 | 深圳   |
|  6 | 鄭旺      |   20 | 重慶   |
+----+-----------+------+--------+

-- 删除資料，慎重！！！
mysql> delete from persons where name='張大拿';
mysql> select * from persons;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
|  5 | 周五   |   22 | 深圳   |
|  6 | 鄭旺   |   20 | 重慶   |
+----+--------+------+--------+

-- 設定查詢數量
mysql> select * from persons limit 2;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
|  3 | 趙倩   |   24 | 北京   |
+----+--------+------+--------+

-- 模糊查詢
mysql> select * from persons where city like '上%';
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
+----+--------+------+--------+

mysql> select * from persons where city not like '上%';
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
|  5 | 周五   |   22 | 深圳   |
|  6 | 鄭旺   |   20 | 重慶   |
+----+--------+------+--------+

-- 成員條件查詢
mysql> select * from persons where name in ('趙倩', '周五');
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  3 | 趙倩   |   24 | 北京   |
|  5 | 周五   |   22 | 深圳   |
+----+--------+------+--------+

mysql> select * from persons where name not in ('趙倩', '周五');
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
|  4 | 孫力   |   23 | 天津   |
|  6 | 鄭旺   |   20 | 重慶   |
+----+--------+------+--------+

-- 範圍查詢
mysql> select * from persons where age between 22 and 24;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
|  5 | 周五   |   22 | 深圳   |
+----+--------+------+--------+

mysql> select * from persons where age not between 22 and 24;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
|  6 | 鄭旺   |   20 | 重慶   |
+----+--------+------+--------+

-- 建立citys表
create table citys(
id int auto_increment primary key,                                       
city varchar(32),
address varchar(64));

insert into citys(city, address) values('北京', '北京市');
insert into citys(city, address) values('上海', '上海市');
insert into citys(city, address) values('深圳', '廣東省');
insert into citys(city, address) values('成都', '四川省');
insert into citys(city, address) values('大連', '遼甯省');
insert into citys(city, address) values('天津', '天津市');
insert into citys(city, address) values('廣州', '廣東省');


mysql> select * from citys;
+----+--------+-----------+
| id | city   | address   |
+----+--------+-----------+
|  1 | 北京   | 北京市    |
|  2 | 北京   | 北京市    |
|  3 | 上海   | 上海市    |
|  4 | 深圳   | 廣東省    |
|  5 | 成都   | 四川省    |
|  6 | 大連   | 遼甯省    |
|  7 | 天津   | 天津市    |
|  8 | 廣州   | 廣東省    |
+----+--------+-----------+

-- 修改表名
mysql> alter table cards rename to citys;

-- 别名
mysql> select p.name,p.city,c.address from persons as p, citys as c where p.city=c.city;
+--------+--------+-----------+
| name   | city   | address   |
+--------+--------+-----------+
| 趙倩   | 北京   | 北京市    |
| 趙倩   | 北京   | 北京市    |
| 李四   | 上海   | 上海市    |
| 周五   | 深圳   | 廣東省    |
| 孫力   | 天津   | 天津市    |
+--------+--------+-----------+

-- 内連接配接
mysql> select persons.name, persons.city, citys.address from persons inner join citys on persons.city=citys.city;
+--------+--------+-----------+
| name   | city   | address   |
+--------+--------+-----------+
| 趙倩   | 北京   | 北京市    |
| 趙倩   | 北京   | 北京市    |
| 李四   | 上海   | 上海市    |
| 周五   | 深圳   | 廣東省    |
| 孫力   | 天津   | 天津市    |
+--------+--------+-----------+

-- 左連接配接
mysql> select persons.name, persons.city, citys.address from persons left join citys on persons.city=citys.city;
+--------+--------+-----------+
| name   | city   | address   |
+--------+--------+-----------+
| 趙倩   | 北京   | 北京市    |
| 趙倩   | 北京   | 北京市    |
| 李四   | 上海   | 上海市    |
| 周五   | 深圳   | 廣東省    |
| 孫力   | 天津   | 天津市    |
| 鄭旺   | 重慶   | NULL      |
+--------+--------+-----------+

-- 右連接配接
mysql> select persons.name, persons.city, citys.address from persons right join citys on persons.city=citys.city;
+--------+--------+-----------+
| name   | city   | address   |
+--------+--------+-----------+
| 李四   | 上海   | 上海市    |
| 趙倩   | 北京   | 北京市    |
| 趙倩   | 北京   | 北京市    |
| 孫力   | 天津   | 天津市    |
| 周五   | 深圳   | 廣東省    |
| NULL   | NULL   | 四川省    |
| NULL   | NULL   | 遼甯省    |
| NULL   | NULL   | 廣東省    |
+--------+--------+-----------+

#  mysql 不支援full join

create table names(
id int auto_increment primary key,
name varchar(32));

insert into names(name) values('李四');
insert into names(name) values('張林');
insert into names(name) values('鬥舞');
insert into names(name) values('名天');
insert into names(name) values('高興');
insert into names(name) values('王二');

mysql> select * from names;
+----+--------+
| id | name   |
+----+--------+
|  1 | 李四   |
|  2 | 張林   |
|  3 | 鬥舞   |
|  4 | 名天   |
|  5 | 高興   |
|  6 | 王二   |
+----+--------+

-- 合并結果集
mysql> select name from persons union select name from names;
+--------+
| name   |
+--------+
| 李四   |
| 趙倩   |
| 孫力   |
| 周五   |
| 鄭旺   |
| 張林   |
| 鬥舞   |
| 名天   |
| 高興   |
| 王二   |
+--------+

-- 列出所有值
mysql> select name from persons union all select name from names;
+--------+
| name   |
+--------+
| 李四   |
| 趙倩   |
| 孫力   |
| 周五   |
| 鄭旺   |
| 李四   |
| 張林   |
| 鬥舞   |
| 名天   |
| 高興   |
| 王二   |
+--------+

# mysql 不支援select inot

mysql> desc persons;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| city  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 添加限制unique
mysql> alter table persons add unique(name);

mysql> desc persons;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  | UNI | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| city  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 删除限制unique
mysql> alter table persons drop index name;

mysql> desc persons;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| city  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

--  删除primary key
mysql> alter table persons drop primary key;

mysql> show create table persons \G
Create Table: CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `city` varchar(32) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

-- 添加primary key
mysql> alter table persons add primary key(id);

mysql> show create table persons \G
Create Table: CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `city` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

-- 外鍵
create table familys(
id int primary key auto_increment,
person_id int,
menber_num int,
foreign key (person_id) references persons(id));

mysql> show create table familys \G
Create Table: CREATE TABLE `familys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) DEFAULT NULL,
  `menber_num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `familys_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


-- 删除外鍵
mysql> alter table familys drop foreign key familys_ibfk_1;

-- 添加外鍵
mysql> alter table familys add foreign key(person_id) references persons(id);

-- check限制 InnoDB中不支援check限制
create table student(
id int,
age int,
check(age>0 and age<100));

mysql> show create table student\G
Create Table: CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 預設值default
mysql> desc persons;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| city  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 添加預設值
mysql> alter table persons alter age set default 0;

mysql> desc persons;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | 0       |                |
| city  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 删除預設值
mysql> alter table persons alter age drop default;
mysql> desc persons;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| city  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 建立索引
mysql> create index person_index on persons(name);

mysql> show create table persons\G
Create Table: CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11),
  `city` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `person_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

-- 删除索引
mysql> alter table persons drop index person_index;

mysql> show create table persons\G
Create Table: CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11),
  `city` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8


-- 建立資料庫
mysql> create database data1;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data1              |
| demo               |
| mydata             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-- 删除資料庫
mysql> drop database data1;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mydata             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-- 删除表格
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| citys          |
| familys        |
| mytable        |
| names          |
| persons        |
| student        |
+----------------+

mysql> drop table student;

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| citys          |
| familys        |
| mytable        |
| names          |
| persons        |
+----------------+

-- 清空資料，保留表結構
mysql> select * from names;
+----+--------+
| id | name   |
+----+--------+
|  1 | 李四   |
|  2 | 張林   |
|  3 | 鬥舞   |
|  4 | 名天   |
|  5 | 高興   |
|  6 | 王二   |
+----+--------+

mysql> truncate table names;

mysql> select * from names;
Empty set (0.00 sec)

mysql> desc names;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+


-- 添加列
mysql> alter table names add alias varchar(32);

mysql> desc names;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| alias | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 删除列
mysql> alter table names drop column alias;

mysql> desc names;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 修改列屬性
mysql> alter table names modify column name varchar(64);

mysql> desc names;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 自增删除AUTO_INCREMEN
mysql> alter table names modify column id int;
mysql> desc names;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 添加自增屬性
mysql> alter table names modify id int auto_increment;

mysql> desc names;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 添加時間字段
mysql> alter table names add column birthday date;

mysql> desc names;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(64) | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

mysql> insert into names(name, birthday) values('tom', '2018-2-12');

mysql> select * from names;
+----+------+------------+
| id | name | birthday   |
+----+------+------------+
|  1 | tom  | 2018-02-12 |
+----+------+------------+




-- null查詢
mysql> select * from names;
+----+------+------------+
| id | name | birthday   |
+----+------+------------+
|  1 | tom  | 2018-02-12 |
|  2 | Jack | NULL       |
|  3 | Jimi | NULL       |
+----+------+------------+

mysql> select * from names where birthday is null;
+----+------+----------+
| id | name | birthday |
+----+------+----------+
|  2 | Jack | NULL     |
|  3 | Jimi | NULL     |
+----+------+----------+

-- 函數
-- 平均值
mysql> select * from persons;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
|  5 | 周五   |   22 | 深圳   |
|  6 | 鄭旺   |   20 | 重慶   |
+----+--------+------+--------+
5 rows in set (0.00 sec)

mysql> select avg(age) from persons;
+----------+
| avg(age) |
+----------+
|  22.0000 |
+----------+


-- 計數
mysql> select count(age) from persons;
+------------+
| count(age) |
+------------+
|          5 |
+------------+

-- 取第一個
mysql> select * from persons limit 1;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
+----+--------+------+--------+


-- max min sum
mysql> select min(age) from persons;
+----------+
| min(age) |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

mysql> select max(age) from persons;
+----------+
| max(age) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(age) from persons;
+----------+
| sum(age) |
+----------+
|      110 |
+----------+

-- 分組

mysql> select * from persons;
+----+--------+------+--------+
| id | name   | age  | city   |
+----+--------+------+--------+
|  2 | 李四   |   21 | 上海   |
|  3 | 趙倩   |   24 | 北京   |
|  4 | 孫力   |   23 | 天津   |
|  5 | 周五   |   22 | 深圳   |
|  6 | 鄭旺   |   20 | 重慶   |
|  7 | 劉穎   |   20 | 北京   |
|  8 | 王大   |   20 | 天津   |
|  9 | 張開   |   20 | 深圳   |
| 10 | 留取   |   20 | 重慶   |
+----+--------+------+--------+

mysql> select city,sum(age) from persons group by city;
+--------+----------+
| city   | sum(age) |
+--------+----------+
| 上海   |       21 |
| 北京   |       44 |
| 天津   |       43 |
| 深圳   |       42 |
| 重慶   |       40 |
+--------+----------+

mysql> select city, sum(age) from persons group by city having sum(age)>40;
+--------+----------+
| city   | sum(age) |
+--------+----------+
| 北京   |       44 |
| 天津   |       43 |
| 深圳   |       42 |
+--------+----------+

-- ucase轉換為大寫 
mysql> select * from names;
+----+------+------------+
| id | name | birthday   |
+----+------+------------+
|  1 | tom  | 2018-02-12 |
|  2 | Jack | NULL       |
|  3 | Jimi | NULL       |
+----+------+------------+
3 rows in set (0.00 sec)

mysql> select ucase(name), birthday from names;
+-------------+------------+
| ucase(name) | birthday   |
+-------------+------------+
| TOM         | 2018-02-12 |
| JACK        | NULL       |
| JIMI        | NULL       |
+-------------+------------+

-- lcase 轉小寫
mysql> select lcase(name), birthday from names;
+-------------+------------+
| lcase(name) | birthday   |
+-------------+------------+
| tom         | 2018-02-12 |
| jack        | NULL       |
| jimi        | NULL       |
+-------------+------------+

-- mid截取字元
mysql> select mid(name, 1, 2), birthday from names;
+-----------------+------------+
| mid(name, 1, 2) | birthday   |
+-----------------+------------+
| to              | 2018-02-12 |
| Ja              | NULL       |
| Ji              | NULL       |
+-----------------+------------+

-- round保留小數位
mysql> select avg(age) from persons;
+----------+
| avg(age) |
+----------+
|  21.1111 |
+----------+
1 row in set (0.00 sec)

mysql> select round(avg(age),1) from persons;
+-------------------+
| round(avg(age),1) |
+-------------------+
|              21.1 |
+-------------------+

-- now() 傳回目前時間
mysql> select name,now() from names;
+------+---------------------+
| name | now()               |
+------+---------------------+
| tom  | 2018-05-03 10:49:09 |
| Jack | 2018-05-03 10:49:09 |
| Jimi | 2018-05-03 10:49:09 |
+------+---------------------+
sql 關系型資料庫 mysql 資料庫 sql資料操作 sql指定資料 sql資料 SQL腳本資料 分區表sql資料
上一篇: SpringMVC架構基礎知識（03）
下一篇: 使用MyBatis時，無法綁定抽象方法與配置SQL語句的異常解決方法

繼續閱讀