数据表命令

2017-01-13 10:46:57来源:oschina作者:口十耳人点击



## 数据表操作
```
-- 创建数据库
CREATE DATABASE mydb
-- 使用数据库
USE mydb
-- 查看数据表
SHOW TABLES
-- 创建表
CREATE TABLE pet (
NAME VARCHAR(20),
ownerr VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE);
-- 显示表结构
DESCRIBE pet;
-- 改表的名字 
ALTER TABLE pet RENAME TO pet2; 
ALTER TABLE pet2 RENAME TO pet; 
-- 约束:约束(主键Primary key、唯一性Unique、非空Not Null)
-- 自动增张 auto_increment 
-- 外键Foreign key-----与reference table_name(col_name列名)配合使用,建表时单独使用 
-- 删除多个表中有关联的数据----设置foreign key 为set null
-- 删除表
DROP TABLE IF EXISTS pet;
-- 增加一个字段: 
ALTER TABLE pet ADD COLUMN (phone VARCHAR(11));
-- 删除一个字段
ALTER TABLE pet DROP phone; 
-- 修改字段名称/类型
ALTER TABLE pet CHANGE phone tel VARCHAR(11)
ALTER TABLE pet CHANGE tel phone VARCHAR(11)
-- 插入数据
INSERT INTO pet VALUES
('Fluffy','Harold','cat','f','1993-02-04',NULL),
('Claws','Gwen','cat','m','1994-03-17',NULL),
('Buffy','Harold','dog','f','1989-05-13',NULL),
('Fang','Benny','dog','m','1990-08-27',NULL),
('Bowser','Diane','dog','m','1979-08-31','1995-07-29'),
('Chirpy','Gwen','bird','f','1998-09-11',NULL),
('Whistler','Gwen','bird',' ','1997-12-09',NULL),
('Puffball','Diane','hamster','f','1999-03-30',NULL),
('Slim','Benny','snake','m','1996-04-29',NULL);
-- 查询表数据
SELECT * FROM pet
-- 清空表数据
DELETE FROM pet
-- 此方法也会使表中的取号器(ID)从1开始
TRUNCATE TABLE pet;
-- 修改字段值
UPDATE pet SET birth = '1989-08-31' WHERE NAME = 'Bowser'
SELECT * FROM pet WHERE NAME = 'Bowser'
```
## 数据表查询
### 基本查询
```
-- DISTINCT 检索出每个唯一的输出记录
SELECT DISTINCT ownerr FROM pet
-- 排序 order by,对于等同但大小写不同的列,并未定义其顺序,某一列,可以使用BINARY强制执行区分大小写的分类功能,如:ORDER BY BINARY col_name.
SELECT NAME,ownerr,birth FROM pet ORDER BY birth
-- 默认排序是升序,可以利用关键字进行控制。DESC(降序)、ASC(升序)
SELECT NAME,ownerr,birth FROM pet ORDER BY birth ASC
-- 多列进行排序
SELECT NAME,ownerr,birth FROM pet ORDER BY ownerr,birth DESC
```
### 日期计算
```
-- YEAR()-提取日期的年部分,MONTH()-提取月份,DAYOFMONTH()-提取所在月份中的日
SELECT birth, YEAR(birth),MONTH(birth),DAYOFMONTH(birth) FROM pet
-- RIGHT(birth,5)-提取birth的右边5个字符,LEFT - 从左边开始取,MID - 从指定位置(包括)开始取指定长度
SELECT RIGHT('1234567890',5),LEFT('1234567890',5),MID('1234567890',2,4)
-- 提出已近死亡的
-- 出生月份大于当前月份,年龄-1。
SELECT
NAME,
birth,
CURDATE(),
(YEAR(CURDATE()) - YEAR(birth)) - (RIGHT(CURDATE(), 5) < RIGHT(birth, 5)) AS age
FROM
pet WHERE death IS NULLORDER BY age -- 查询已死亡的年龄
SELECT
NAME,
birth,
death,
(YEAR(death) - YEAR(birth)) - (RIGHT(death, 5) < RIGHT(birth, 5)) AS age
FROM
pet WHERE death IS NOT NULL ORDER BY age
-- 查询下一个月谁过生日
SELECT * FROM pet ORDER BY MONTH(birth)
SELECT * FROM pet WHERE MONTH(birth) = MONTH(CURDATE()) + 1
SELECT * FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
```
### NULL值
```
-- NULL 值的判断,不能使用比较运算符,只能用 is null 或 is not null
SELECT1 = NULL, 1 > NULL, 1 < NULL,1 IS NULL,1 IS NOT NULL
-- 0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
```
### 模糊匹配
```
-- MySQL提供标准的SQL模式匹配,以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。
-- "_" 匹配任何单个字符;"%" 匹配任意数目字符(包括零字符),SQL的模式默认是忽略大小写。关键字 LIKE 或 NOT LINK
-- 查找以b开头的name
SELECT * FROM pet WHERE NAME LIKE 'b%'
-- 查找以'fy'结尾的名字
SELECT * FROM pet WHERE NAME LIKE '%fy'
-- 查找包含w的名字
SELECT * FROM pet WHERE NAME LIKE '%w%'
-- 查找正好是5个字符的名字
SELECT * FROM pet WHERE NAME LIKE '_____'
SELECT * FROM pet WHERE LENGTH(NAME) = 5
-- MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时,使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE,它们是同义词)。
-- "." 匹配任何单个字符;
-- "[...]" 匹配在方括号内的任何字符,为了命名字符的范围,使用一个“-”。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
-- "*" 匹配零个或多个在它前面的字符。例如,“x*”匹配任何数量的“x”字符,“[0-9]*”匹配任何数量的数字,而“.*”匹配任何数量的任何字符。
-- 如果REGEXP模式与被测试值的任何地方匹配,模式就匹配(这不同于LIKE模式匹配,只有与整个值匹配,模式才匹配)。
-- 为了定位一个模式以便它必须匹配被测试值的开始或结尾,在模式开始处使用“^”或在模式的结尾用“$”。
-- 查找以b开头的name
SELECT * FROM pet WHERE NAME REGEXP '^b'
-- 查找以'fy'结尾的名字
SELECT * FROM pet WHERE NAME REGEXP 'fy$'
-- 查找包含w的名字
SELECT * FROM pet WHERE NAME REGEXP 'w'
-- 查找正好是5个字符的名字
SELECT * FROM pet WHERE NAME REGEXP '^.....$'
SELECT * FROM pet WHERE NAME REGEXP '^.{5}$'
```
[正则表达式在线参考](http://tool.oschina.net/uploads/apidocs/mysql-5.1-zh/regexp.html)

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台