【原创】MySQL数据常识知识

此文章为Python_anning原创，未经允许，不得随意转载！

启动命令

mysql -u root -p

查看所有数据库

show databeses;

进入表

use table_name;

查看所有表

show tables;

查看表内字段

show columns from table_name；

指定检索单个列

select 列名 from 表名；

指定检索多列

select 列名，列名... from 表名；

检索所有列

select * from 表名;

检索并且对列去重

select distinct name from 表名;

检索并且指定数量

select * from 表名 limit 5;
或者 从第几条开始，看几条
select * from 表名 limit 5,5;

对数据排序

查看表内所有内容 使用id排序使用其他排序也可以
select * from 表名 order by id;

多个列排序

优先使用id排序 id一样使用title排序 当然 id不可能一样
select id,title,concent from 表名 order by id,title;

倒序排列

select * from 表名 order by title desc;

多个列倒序

优先使用id排序 id一样使用title排序 当然 id不可能一样
select id,title,concent from 表名 order by id,title desc;

多个列查询指定数量

select id,title,concent from 表名 order by id,title desc limit 10;

过滤数据

select * from 表名 where id=1;
select * from 表名 where id<10;
select * from 表名 where id<=10;
select * from 表名 where id>10;
select * from 表名 where id>=10;
select * from 表名 where id<>/!=10;

范围查询

select * from 表名 where 字段 between 5 and 10;

空值检查

返回所有空值
select 字段 from 表名 where 字段 is null;

与或非 中与操作 （AMD）

select * from 表名 where id<50 and title<5;

与或非 中或操作（OR）

select * from 表名 where id<30 or id>30000;

结合使用

select * from 表名 where id<30 or id>30000 and id<31000;
and的优先级比or高  可以使用()给or增强优先级
select * from 表名 where (id<30 or id>30000) and id<31000;

in操作

查询出id里1000和1100的值
select * from 表名 where id in (1000, 1100) order by id;

与或非 非操作（NOT）

查询出所有id中不等于1000和1200的值
select * from 表名 where id not in (1000,1200) order by id;

通配符中的like（模糊查询） %（0个、一个或者多个字符）

select * from 表名 where title like '%政府';  # 以政府两个字结尾
select * from 表名 where title like '政府%';    # 以政府两个字开头  
select * from 表名 where title like '%政府%';   # 只要字段中有政府两个字就匹配
select * from 表名 where title like '政%府';    # 以政开头 以府结尾的字段

通配符中的like（模糊查询） _ （单字符）

select * from 表名 where title like '_府'; # 匹配某府 单字符
select * from 表名 where title like '__府'; # 匹配两个字符 一个_等于一个字符

基本字符匹配

检索所有表中title字段中有首页两个字的字段 与模糊查询 %首页% 结果一样 以id反向排序
select * from 表名 where title regexp '首页' order by id desc;

正则匹配

检索所有表中有 某页 的 字段
select * from 表名 where title regexp '.页';

正则的或操作

regexp '' 中可以写正则表达式 包括[a-z] [\d] 等正则操作
注意 匹配特殊字符 需要加 \\ 比如\\. 查找 .
匹配id中有100和1000的字段  
select * from 表名 where id regexp '100|1000' order by id;
匹配字段中1或者2或者3加上政府的字段
select * from 表名 where title regexp '[123]政府' order by id;

元字符 说明
\f 换页
\n 换行
\r 回车
\t 制表
\v 纵向制表
* 0个或多个匹配
+ 1个或多个匹配（等于{1,}）
? 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（m不超过255）

[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\t]）
[:cntrl:] ASCII控制字符（ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）

计算字段创建 Concat()拼接串

一个栏中现在2个字段 使用括号吧字段括起来 括号记得要加引号
给每个字段加上参数 RTRIM(字段）可以去除边上的空格   
给字段命名 加上as 名字 concat(字段1,'(',字段2,')') as finename
select concat(字段1,'(',字段2,')') from 表名 order by id;
结果
字段1(字段2)
....

执行算术计算

在查询字段  可以使用数学计算 主要计算 + - * /
select id,title,money*id from 表名 where id = 2008; 

使用函数 （文本处理函数）用Upper()

upper() 将内容全部大写
Lower() 将串转换为小写
Left() 返回串左边的字符
Length() 返回串的长度
Locate(）找出一个串的字串
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符


select id, upper(title) as tname from 表名 order by id;

使用函数 （时间函数）CurTime()

AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

将数据库里的时间数据取出是 使用date将时间转换格式 保证数据查的准确度
可以使用year和month取出时间字段中的年和月出来 进行查询
使用 and 方法 可以取出一个时间段的数据
select id,times from 表名 where Date(times)='2005-09-01'

使用函数 （数据处理函数）

Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

使用函数 （聚集函数）

AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

组合使用
selece count(*) as a, min(id) as b, max(id) as c, avg(id) as d from 表名

数据分组 HAVING() 过滤分组

having 和 where非常相似 where过滤行 而 having是过滤组
得到分组之后的数据  以字段1分组 显示字段2大于10之和大于10 的值
select 字段1, count(字段2) as a from order by 字段1 having count(字段2)>10;

子查询（嵌套查询）

select id from 表名 where title in (
    select title from 表名 where id > 100 
);


select id，title,(
    select count(*) from 表名 where id = 表名.id
) as a from 表名 order by id;

使用from进行多表查询

select aname,bname,money from a表，b表 order by aname,bname;

联合查询多表

select a_name,b_name,a_money,size from a,b,c where a_id = b_id and a_money = b_money and size = 200; 

多表嵌套查询

select name,password from 表1 where id in (
    select id from 表2 where num in (
        select num from 表3 where id > 30
    )
);

高级级联方法

select concat(RTrim(name),'(',RTrim(money),')') as nmoney from 表名 order by id;


select a_name,a_content from 表1 as 1 , 表2 as 2， 表3 as 3 where 1.id = 2.id and 3.id = 2.id and id > 30; 

常用级联

select id, name from 表1 where id = (
    select id from 表1 where like name='%公司'
);

全文本搜索

其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。 使用like效果一样
select content_text from 表名 where match(content_text) Against('大风车')

数据插入 （插入完整行）

插入数据 值与表内行对应
insrt into 表名 value('a','b',...);

更安全的数据插入 （插入完整行）

insert into 表名(字段1，字段2， ...) value('值1', '值2'...);

修改数据

将表1里的name字段修改为周先生，指定id为7。 这里要注意 一定要指定 否则表内所有name都将修改
updata 表1 set name = '周先生' where id = 7;

多条修改
updata 表1 set name = '周先生', phone = '13423232323' where id = 7;

NULL 表示为空
updata 表1 set name = NULL where id = 7;

删除数据 * 慎行

删除
delete from 表1 where id = 7;

创建和操纵表

create table 表名(
    id int not null auto_increment,
    name varchar(20) not null,
    ress varchar(30) not null,
    zip varchar(30) not null,
    primary key (id)
) engine=innodb

约束

primary key (id) # 主键约束
auto_increment  # 自增长约束
default  # 默认约束

MySQL引擎

engine = 引擎
默认引擎 myisam

 InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；

 MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；

 MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

删除表

drop table 表名;

表重命名

rename table 表名1 to 表名2;

创建视图

创建了一个视图表  视图表级联了3张表
create view 视图名 as select a,b,c from 表1，表2，表3 where a_id = b_id and c_id = b_id ;

定义外键

foreign key (本表的一个字段) references 外键表名(外键字段);

改善性能

 LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
如果你有一些表，它们收集数据且不经常被搜索，则在有必要之
前不要索引它们。（索引可根据需要添加和删除。）

存储的数据类型

CHAR 1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)

ENUM 接受最多64 K个串组成的一个预定义集合的某个串

LONGTEXT 与TEXT相同，但最大长度为4 GB 

MEDIUMTEXT 与TEXT相同，但最大长度为16 K 

SET 接受最多64个串组成的一个预定义集合的零个或多个串

TEXT 最大长度为64 K的变长文本

TINYTEXT 与TEXT相同，但最大长度为255字节

VARCHAR 长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）

BIT 位字段，1～64位。（在MySQL 5之前，BIT在功能上等价于 TINYINT

BIGINT 整数值，支持-9223372036854775808～9223372036854775807（如果是UNSIGNED，为0～18446744073709551615）的数

BOOLEAN（或BOOL） 布尔标志，或者为0或者为1，主要用于开/关（on/off）标志

DECIMAL（或DEC） 精度可变的浮点值

DOUBLE 双精度浮点值

FLOAT 单精度浮点值

INT（或INTEGER） 整数值，支持2147483648～2147483647（如果是UNSIGNED，为0～4294967295）的数

MEDIUMINT 整数值，支持8388608～8388607（如果是UNSIGNED，为0～16777215）的数

REAL 4字节的浮点值

SMALLINT 整数值，支持32768～32767（如果是UNSIGNED，为0～65535）的数

TINYINT 整数值，支持128～127（如果为UNSIGNED，为0～255）的数

DATE 表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD

DATETIME DATE和TIME的组合

TIMESTAMP 功能和DATETIME相同（但范围较小）

TIME 格式为HH:MM:SS

YEAR 用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年

BLOB Blob最大长度为64 KB 

MEDIUMBLOB Blob最大长度为16 MB 

LONGBLOB Blob最大长度为4 GB 

TINYBLOB Blob最大长度为255字节