数据表命令

数据表操作

-- 创建数据库
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 NULL  ORDER 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
SELECT  1 = 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}$'

正则表达式在线参考