MySQL常用SQL语句

MySQL常用SQL语句

一些概念

关系型数据模型：建立在严格的数据概念基础上，用二维表来描述实体与实体间的联系。

关系：对应通常所说的一张表

元组：表中的一行

属性：表中的一列

主键：表中的某个属性（组），它可以唯一确定一个元组

关系型数据库模型的操作：

SELECT

SELECT...INTO 创建一个查询表

INSERT INTO

UPDATE

DELETE

INNER JOIN 内连接

LEFT JOIN 左连接

RIGHT JOIN 右连接

PARAMETERS

UNION 用于创建一个联合查询

1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。如果不想去掉重复的行，可以使用union all

三大范式

第一范式（1NF）数据库表的每一列都是不可分割的原子数据项

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性

第三范式（3NF）在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

MySQL数据类型

1、整型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节  范围(-128~127)
smallint(m)	2个字节  范围(-32768~32767)
mediumint(m)	3个字节  范围(-8388608~8388607)
int(m)	4个字节  范围(-2147483648~2147483647)
bigint(m)	8个字节  范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。 
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

2、浮点型(float和double)

MySQL数据类型	含义
float(m,d)	单精度浮点型    8位精度(4字节)     m总个数，d小数位
double(m,d)	双精度浮点型    16位精度(8字节)    m总个数，d小数位

设一个字段定义为float(5,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

4、字符串(char,varchar,_text)

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。 
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。 
3.char类型的字符串检索速度要比varchar类型的快。

varchar和text： 
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。 
2.text类型不能有默认值。 
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。 
2._BLOB存储的数据只能整体读出。 
3._TEXT可以指定字符集，_BLO不用指定字符集。

6.日期时间类型

MySQL数据类型	含义
date	日期 '2008-12-2'
time	时间 '12:25:36'
datetime	日期时间 '2008-12-2 22:06:44'
timestamp	自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

数据类型的属性

 

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

 

数据库基本操作

show databases;查看数据库

create database dbname;创建数据库dbname

drop database dbname;删除数据库 dbname

表操作

创建表

CREATE TABLE <表名>

(字段名1，数据类型 [列级别约束条件][默认值],

....

[表级别约束条件]

);

列级别约束条件包括：PRIMARYKEY, not null, UNIQUE, DEFAULT, AUTO_INCREMENT

 

修改表

ALTER TABLE <旧表名> RENAME <新表名>;

ALTER TABLE <表名> MODIFY <字段名> <数据类型>; 修改字段类型

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新字段类型>; 修改字段名

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件][FIRST|AFTER];增加字段

ALTER TABLE <表名> DROP <字段名>;

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;修改字段排序

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>; 删除外键

DROP TABLE [IF EXISTS] 表1 表2 ...;删除没有被关联的表

设置索引

索引是将数据库中单列或者多列的值进行排序的结构，可以优化数据库的查询速度。

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名

ON 表名(属性 [(length)][ASC | DESC]);创建索引

DROP INDEX 索引名ON 表名;删除索引

记录查询

SELECT 属性列表 FROM 表名或视图名

[WHERE 条件表达式]

[GROUP BY属性名[HAVING 条件表达式]]

[ORDER BY 属性名 [ASC|DESC]]

group by 将结果按照group by子句后属性进行分组，having使得满足条件的组才能输出

order by 子句后面的属性进行升序或降序排列

 

LIMIT

使用LIMIT限制查询结果数量，在Java web中用于分页显示查询结果。

limit x, y//第一个参数指定返回记录行的偏移量（从0开始），第二个参数指定返回记录行的最大数目。

select * from student limit 2;返回2行记录

select * from student limit 1,3;返回第2行开始的3行数据。

 

（内）连接查询

select student.sno sname,cno,grade fromstudent,sc

where student.sno=sc.sno;

等同于

select student.sno sname,cno,grade fromstudent

inner join sc onstudent.sno=sc.sno;

 

外连接查询

leftjoin，join左表中所有记录和右表中满足连接的记录

select student.sno sname,cno,grade fromstudent

left join sc on student.sno=sc.sno;

rightjoin，join右表中所有记录和左表中满足连接的记录

select student.sno sname,cno,grade fromstudent

right join sc onstudent.sno=sc.sno;

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。

例如select *from t1 where column1 = (select column1 from t2)

子查询中常用操作符有ANY(SOME)、ALL、IN、EXISTS

比较值<=子查询所返回的每一个值，<=ALL为true

比较值<=子查询所返回的任一个值，<=ANY为true

IN和NOT IN，查询结果是否在内层查询返回的数据列集合中。如：

select sno, sname from student

where sno in (select sno from sc wherecno=1);

合并查询结果

MySQL中使用UNION关键字，可将多个SELECT结果集合并为单个结果集，要求参加合并的结果集对应的列数和数据类型必须相同。语法：

SELECT...

UNION [ALL|DISTINCT]

SELECT...

[UNION [ALL|DISTINCT]

SELECT...]

 

记录更新

插入

INSERT

[LOW_PRIORITY | DELAYED | HIGH_PRIORITY]//可选，表优先级

[IGNORE]//可选，出现错误当做警告处理

[INTO] 表名//可选

[(字段名,...)]//可选，若不选需要插入所有列的数据

VALUES(值1,值2...), (值1,值2...), (值1,值2...)//必选，可插入多条数据

[ON DUPLICATE KEY UPDATE 字段=表达式,...]//可选，如果UNIQUE出现重复值将修改插入的值

 

insert into student (name,age) values (“小明”,20);

insert into student (name,age) values(“小花”,20), (“小白”,20), (“小绿”,20)//插入多条

insert into student .. select ..//可以将SELECT结果集插入表中

 

修改

UPDATE 表名 SET 字段1 = 新值1,字段2=新值2,... where 条件表达式;

删除

DELETE FROM 表名 WHERE 条件表达式;

TRUNCATE [TABLE] 表名;