MySQL里的数据类型
MySQL里的数据类型
1.整数类型
数据类型 | 字节数 | 无符号数的取值范围 | 有符号数的取值范围 |
---|---|---|---|
TINYINT | 1 | 0~255 | -128~127 |
SMALLINT | 2 | 0~65535 | -32 768~32 767 |
MEDIUMINT | 3 | 0~16 777 215 | -8 388 608~8 388 607 |
INT | 4 | 0~4 294 967 295 | -2 147 483 648~2 147 483 647 |
BIGINT | 8 | 0~18 446 744 073 7009 551 615 | -9 223 372 036 857 775 808~9 223 372 036 857 775 807 |
2.浮点数类型和定点数类型
数据类型 | 字节数 | 负数的取值范围 | 非负数的取值范围 |
---|---|---|---|
FLOAT | 4 | -3.402 823 466E+38~~-1.175 494 351E-38 | 0和1.175 494 351E-38~~3.402 823 466E+38 |
DOUBLE | 8 | -1.797 693 134 862 315 7E+308~~-2.225 073 858 507 201 4E-308 | 0和2.225 073 858 507 201 4E-308~~1.797 693 134 862 315 7E+308 |
DECIMAL(M,D) | M+2 | -1.797 693 134 862 315 7E+308~~2.225 073 858 507 201 4E-308 | 0和2.225 073 858 507 201 4E-308~~1.797 693 134 862 315 7E+308 |
M表示数据的长度,D表示小数点后的长度,比如,数据类型是DECIMAL(6.2)的数据3.1415放进数据库后显示为3.14.只显示小数点后两位
3.日期与时间类型
数据类型 | 字节数 | 取值范围 | 日期格式 | 零值 |
---|---|---|---|---|
YEAR(year) | 1 | 1901~~2155 | YYYY | 0000 |
DATE(data) | 4 | 1000-01~~9999-12-3 | YYYY-MM-DD | 0000-00-00 |
TIME(time) | 3 | -838:59:59~~838:59:59 | HH:MM:SS | 00:00:00 |
DATATIME(datatime) | 8 | 1000-01-01 00:00:00~~999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
TIMESTAMP (timestamp) | 4 | 1970-01-01 00:00:01~~2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
4.字符串和二进制类型
数据类型 | 类型说明 |
---|---|
CHAR | 用于表示固定长度的字符串 |
VARCHAR | 用于表示可变长度的字符串 |
BINARY | 用于表示固定长度的二进制数据 |
VARBINARY | 用于表示可变长度的二进制数据 |
BLOB | 用于表示二进制大数据 |
TEXT | 用于表示大文本数据 |
ENUM | 表示枚举类型,只能存储一个枚举字符串 |
SET | 表示字符串对象,可以有零或多个值 |
BIT | 表示位字段类型 |
CHAR和VARCHAR
插入值 | CHAR(4) | 存储需求 | VARCAHR(4) | 存储需求 |
---|---|---|---|---|
‘ ’ | ’ ’ | 4个字节 | ’ ’ | 1个字节 |
‘ ab ’ | ‘ab’ | 4个字节 | ‘ab’ | 3个字节 |
’ abc’ | ‘abc’ | 4个字节 | ‘abc’ | 4个字节 |
‘abcd’ | ‘abcd’ | 4个字节 | ‘abcd’ | 5个字节 |
‘abcdef’ | ‘abcd’ | 4个字节 | ‘abcd’ | 5个字节 |
特点:CHAR(4)类型时,不管插入值的长度为多少,所占的存储空间都是4个字节
VARCAHR(4)类型时,所占字节为实际长度加1.
数据库的常见操作
1.创建数据库
CREATE DATABAESE 数据库名称;
CREATE DATABASE IF NOT EXISTS 数据库名称;
2.查看所存在的数据库
SHOW DATABASES;
3.查看单个数据库
SHOW CREATE DATABASE 数据库名称;
4.1.修改数据库
ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET 编码方式 COLLATE 编码方式_BIN;
4.2.创建数据库并修改编码方式
bashCREATE DATABASE 数据库名称 CHARACTER SET gbk;
5.使用数据库
USE 数据库名称;
6.删除数据库
drop database 数据库名称;
数据表的基本操作
1.创建数据表
CREATE TABLE 表名
(
字段名1,数据类型[完整性约束条件],
字段名2,数据类型[完整性约束条件],
....
字段名n,数据类型[完整性约束条件],
)
2.1查看所有存在数据表
SHOW TABLES;
3.1查看创建的数据表
SHOW CREATE TABLE 表名;
3.2使用DESCRIBE查看数据表
DESCRIBE 表名;
简写:DESC 表名;
修改数据表
1.修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;
2.修改表字段名
ALTER TABLE 表名 CHANGE 旧字段名 新表名 新数据类型;
注:个人认为也可以改字段名的同时可以不变字段名,只改数据类型。用来改数据类型
3.修改数据类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;
4添加字段
ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件][FIRST|AFTER 已存在的字段名];
FIRST 用于将新添加的字段设置为表的第一个字段,AFTER 用于将新添加的字段添加到指定的字段后面。
5.删除字段
ALTER TABLE 表名 DROP 字段名;
6.修改字段的排列的位置
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;
也可以改变数据类型
删除数据表
DROP TABLE 表名;
注意:创建表的时候,很多表可能存在关联,删除的时候要注意。
表的约束条件
约束条件 | 说明与含义 |
---|---|
PRIMARY KEY | 主键约束:不能为空值和必须唯一,算是非空约束和唯一约束的合体 |
FOREING KEY | 外键约束 |
NOT NULL | 非空约束,不能为空值 |
UNIQUE | 唯一性约束 |
DEFAULT | 默认值约束,用于设置字段的默认值 |
1.主键约束
1.1单字段主键
只能有一个主键
字段名 数据类型 PRIMARY KEY;
例子:CREATE TABLE TEST (
ID INT PRIMARY KEY,
NAME VARCHAR(20),
GRADE FLOAT);
1.2多字段主键
PRIMARY KEY(字段名1,字段名2,...,字段名n)
例子:CREATE TABLE TEST2 (
ID INT,
COURSE INT,
GRADE FLOAT,
PRIMARY KEY (ID,COURSE,GRADE)
);
2.非空约束
字段名 数据类型 NOT NULL;
3.唯一约束
字段名 数据类型 UNIQUE;
4.默认约束
字段名 数据类型 DEFAULT 默认值;
设置表的字段值自动增加
字段名 数据类型 AUTO INCREMENT;
例子:CREATE TABLE TEST (
ID INT PRIMARY KEY AUTO_INCREMENT,
STU_ID INT UNIQUE,
GRADE FLOAT NOT NULL
);
索引
-
普通索引:由INDEX或KEY定义的索引,是MySQL里基本的索引类型,可以创建在任何数据类型中。
-
唯一索引:由UNIQUE定义的索引,该索引在的字段必须是唯一的。
-
全文索引:由FULLTEXT定义的索引,但只能创建在CHAR ,VARCHAR或TEXT类型的字段上,而且现在只有MyISAM存储引擎支持全文索引。
-
单列索引:他可以是普通索引 唯一索引 全文索引,但要该索引对应表中一个字段。
-
在表中多个字段上创建索引,但要查询条件中使用了这些字段的第一个字段时,该索引才被使用
-
空间索引:由SPATIAL定义的索引,它只能创建在空间数据类型的字段上,MySQL里的空间数据类型有4种分别时GEOMTRY,POINT,LINESTRING,和POLYGON.
注意:创建空间索引的字段,必须将其声明为NOT NULL,并且空间索引只能在存储引擎为MyISAM的表中创建。
1.创建表的时候创建索引
CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件],
字段名 数据类型 [完整性约束条件],
...
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名](字段名1 [(长度)] [ASC|DESC])
);
ASC 升序排序,DESC降序排序
1.1.创建普通索引
CREATE TABLE TEST1 (
ID INT,
NAME VARCHAR(20),
INDEX(ID)
);
查看表的结构
SHOW CREATE TABLE TEST1\G
EXPLAIN SELECT *FROM 表名 WHERE 字段名=1 \G
1.2.创建唯一索引
CREATE TABLE TEST2 (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SCORE FLOAT,
UNIQUE INDEX unique_id(ID ASC)
);
1.2.创建全文索引
注意:只能创建在CHAR ,VARCHAR或TEXT类型的字段上,而且现在只有MyISAM存储引擎支持全文索引。
CREATE TABLE TEST3 (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SCORE FLOAT,
FULLTEXT INDEX fulltext_name (NAME)
) ENGINE =MyISAM;
1.3.创建单列索引
CREATE TABLE TEST4 (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SCORE FLOAT,
INDEX signle_name (NAME(20))
);
1.4.创建多列索引
CREATE TABLE TEST5(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
SCORE FLOAT,
INDEX multi(ID ,NAME(20))
);
注意:在使用多列索引中,只有查询条件中使用了这些字段中第一个字段后,多列索引才会被使用。
1.5.创建空间索引
注意:只能创建在空间数据类型的字段上,MySQL里的空间数据类型有4种分别是GEOMTRY,POINT,LINESTRING,和POLYGON.,并且必须将其声明为NOT NULL,并且空间索引只能在存储引擎为MyISAM的表中创建
CREATE TABLE TEST6(
SPACE GEOMETRY NOT NULL,
SPATIAL INDEX spacetest(space)
)ENGINE=MyISAM;
2.使用CREATE INDEX 语句在已经存在的表上创建索引
语法格式:CREATE [UNIQUE |FULLTEXT |SPATIAL] INDEX 索引名 ON 表名 (字段名 [(长度)] [ASC |DESC]);
先建立一个表,在来添加
CREATE TABLE BOOK(
BOOKID INT NOT NULL,
BOOKNAME VARCHAR(255) NOT NULL,
AUTHORS VARCHAR(255) NOT NULL,
INFO VARCHAR(255) NULL,
COMMENT VARCHAR(255) NULL,
PUBLICYEAR YEAR NOT NULL
);
2.1创建普通索引
CREATE INDEX index_id(索引名) ON BOOK(表名)(BOOKID);
查看表的结构
SHOW CREATE TABLE BOOK \G
EXPLAIN SELECT * FROM 表名 WHERE ID=1 \G
2.2创建唯一性索引
CREATE UNIQUE INDEX uniqueindextest(索引名) ON BOOK(表名)(BOOKID);
2.3创建单列索引
CREATE INDEX signindex ON BOOK(COMMMENT)
2.4创建多列索引
CREATE INDEX mlitindex ON BOOK(AUTHORS(20),INFO(20));
2.5创建全文索引
注意:全文索引只能创建在CHAR ,VARCHAR或TEXT类型的字段上,而且现在只有MyISAM存储引擎支持全文索引。
修改引擎
ALTER TABLE BOOK ENGINE=MiISAM;
CREATE FULLTEXT INDEX fulltextindex ON BOOK(INFO);
2.6创建空间索引
1.先创建一个表,数据类型要是GEOMTRY,POINT,LINESTRING,和POLYGON.,还有引擎MyISAM
注意:只能创建在空间数据类型的字段上,MySQL里的空间数据类型有4种分别是GEOMTRY,POINT,LINESTRING,和POLYGON.,并且必须将其声明为NOT NULL,并且空间索引只能在存储引擎为MyISAM的表中创建
CREATE TABLE TEST7(
G GEOMETRY NOT NULL
) ENGINE=MyISAM;
2.创建空间索引
CREATE SPATIAL INDEX spatialindex ON TEST7(G);
3.使用ALTER TABLE 语句在已经存在的表上创建索引
语法:`CREATE TABLE 表名 ADD [UNIQUE | FULLTEXT |SPATIAL] INDEX 索引名(字段名 [(长度)[ ASC | DESC]])
3.1.创建普通索引
ALTER TABLE BOOK ADD INDEX index_test (BOOKID);
3.2.创建唯一性索引
ALTER TABLE BOOK ADD UNIQUE uniqueindextest(BOOK);
3.3.创建单列索引
ALTER TABLE BOOK ADD INDEX singeindex (COMMENT(50));
3.4.创建多列索引
ALTER TABLE BOOK ADD INDEX multindex(AUTHORS(20),INFO(50));
3.5.创建全文索引
注意:全文索引只能创建在CHAR ,VARCHAR或TEXT类型的字段上,而且现在只有MyISAM存储引擎支持全文索引。
ALTER TABLE BOOK ADD FULLTEXT INDEX fulltextindex(INFO);
3.6.创建空间索引
先创建一个数据类型是GEOMTRY, POINT, LINESTRING, POLYGON,还有引擎是MyISAM的。
注意:只能创建在空间数据类型的字段上,MySQL里的空间数据类型有4种分别是GEOMTRY,POINT,LINESTRING,和POLYGON.,并且必须将其声明为NOT NULL,并且空间索引只能在存储引擎为MyISAM的表中创建
CREATE TABLE TEST9{
SPACE GEOMETRY NOT NULL
)ENGINE=MyISAM;
创建空间索引
ALTER TABLE TEST8 ADD SPATIAL INDEX saptindex(SPACE);
删除索引
1.使用ALTER TABLE 删除索引
ALTER TABLE 表名 DROP INDEX 索引名:
2.使用DROP INDEX 删除索引
DROP INDEX 索引名 ON 表名;
添加、更新与删除数据
1.添加数据
1.1为表中所有的字段添加数据
使用INSERT语句指定所有字段名
INSERT INTO 表名 (字段名1,字段名2,...)
VALUES(值1,值2,...);
1.2用INSERT INTO 不指定插入数据
注意“没有指定字段名,但添加的值必须和字段在表中定义的顺序相同
INSERT INTO 表名 VALUES (值1,值2....);
例子:INSERT INTO STUDENT VALUES (3,'WANGWU',61.5);
1.3用INSERT INTO插入指定数据
INSERT INTO 表名 (字段1,字段2,...)
values (值1,值2,....);
例子:INSERT INTO STUDENT(ID,NAME) VALUES(4,'zhangsan')
;
1.4INSERT 语句的其他写法
INSERT INTO 表名
SET 字段名1=值1,字段名2=值2,...;
例子:INSERT INTO STUDENT SET ID=1,NAME='ZHANGSAN',GRADE =99;
1.5INSERT 语句同时添加记录
INSERT INTO 表名 [字段名1,字段名2,...]
VALUES (值1,值2,...)
(值1,值2,...)
(值1,值2,...)
...;
例子:
INSERT INTO STUDENT
VALUES (1,'lisi',89),
(2,'hanmeimei',100),
(3,'laowang',70);`
更新数据
语法:
UPDATE 表名
SET 字段名1=值1,字段名2=值2,...
[WHERE 条件表达式]
UPDATE 更新部分数据
例子:
UPDATE STUDENT
SET NAME='zhangsan',GRADE=70
WHERE ID=2;
UPDATE STUDENT
SET GRADE =100
WHERE ID<4;
UPDATE 更新全部数据
** 语句里如果没有使用WHERE语句,不然会将表中所有记录的指定字段都更新**
例子:
UPDATE STUDENT
SET GRADE =90;
删除数据
语法格式:DELETE FROM 表名 [WHERE 条件表达式]
1.1DELETE 删除部分数据
例子:
DESECT FROM STUDENT
WHERE ID<2;
1.2DELETE 删除全部数据
语句里如果没有使用WHERE语句,不然会将表中所有记录的指定字段都更新
DELETE FROM STUDENT;
删除数据2.0
TRUNCATE 效率高,是推到重新建
2.1 语法
bash
TRUNCATE [TABLE] 表名
例子:
TRUNCATE TABLE STUDENT;
DELETE 与TRUNCATE 的区别
1.DELETE 语句是DML语句,TRUNCATE 语句通常是DDL语句
2.DELETE 语句后面可以跟 WHERE语句,可以只删除部分数据,而 TRUNCATE 只能用于删除所有数据。
3.用TRUNCATE 语句删除表中的数据,再次向表中添加记录时,自动增加的字段的默认值重新由1开始,而DELETE 语句删除表中记录后,再向表中添加记录时,自动增加字段的值为删除时该字段的最大值加1.
单表查询
简单查询
SELECT [DISTINCT] * | 字段名1,字段名2,字段名3,...
FROM 表名
[WHERE 条件表达式 1]
[GROUP BY 字段名 [HAVING 条件表达式]]
[ORDER BY 字段名 [ASC|DESC]]
[LIMIT [OFFSET] 记录数]
注意:1.*
星号通配符表示表中所有字段
DISTINCT 时可选参数,用于剔除查询结果中重复的数据。
2. FROM 表名:表示从指定的表中查询数据。
3. WHERE 条件表达式:WHERE 是可选参数,用于指定查询条件
4. GROUP BY字段名 [ HAVING 表达式]:GROUP BY 是可选参数,用于将查询结果按照指定字段进行分组,HAVING 也是可选参数,用于对分组后的结果进行过滤。
5. ORDER BY 字段名 [ASC| DESC]:ORDER BY 是可选参数,用于将查询结果按照指定字段进行排序,ASC 表示升序,DESC 表示降序。如果不指定参数,默认升序排序。
6. LIMT [OFFSET ]记录数:LIMT 是可选参数,用于限制查询结果的数量.,LIMT后面可以跟两个参数,第一个OFFSET 表示偏移量,如果偏移量为0,则从查询结果的第一条记录开始 .偏移量为1则从查询结果的第二条记录开始 。如果不设置默认值为0,第二个参数“记录数”表示返回查询记录的条数
1.查询所有字段
SELECT 字段名1,字段名2,...FROM 表名
SELECT ID,NAME,GRADE,GENDER FROM STUDENT;
查询所有字段(2.0)
SELECT * FROM 表名;
SELECT * FROM STUDENT;
2.查询指定字段
SELECT 字段名1 ,字段名2,...FROM 表名;
SELECT NAME,GENDER FROM STUDENT;
按条件查询
1.带关系运算符的查询
SELECT 字段名1 ,字段名2,...
FROM 表名
WHERE 条件表达式
重要!
关系运算符 | 说明 | 关系运算符 | 说明 |
---|---|---|---|
= | 等于 | > | 大于 |
< > | 不等于 | <= | 小于等于 |
!= | 不等于 | >= | 大于等于 |
< | 小于 |
例子:
SELECT NAME,NAME,GENDER FROM STUDENT WHERE NAME='小明';
SELECT NAME,GRADE,FROM STUDENT WHERE GRADE>90;
2.带 IN 关键字的查询
用于判断某些字段的值是否在指定的集合中
SELECT * |字段名1,字段名2,...
FROM 表名
WHERE 字段名 [NOT] IN(元素1,元素2,...)
SELECT ID,GRADE,NAME,GENDER FROM STUDENT WHERE ID IN (1,2,3);
SELECT ID,GRADE,NAME,GENDER FROM STUDENT WHERE ID NOTI IN (1,2,3);
2.带 BETWEEN AND关键字的查询
用于判断某些字段的值是否在指定的范围之内
SELECT * | [字段名1,字段名2,...]
FROM 表名
WHERE 字段名 [NOT] BETWWEEN 值1 AND 值2
例子:
SELECT ID,NAME FROM STUDENT WHERE ID BETWEEN 2 AND 5;
SELECT ID,NAME FROM STUDENT WHERE ID NOT BETWEEN 2 AND 5;
空值查询
SELECT * |字段名1,字段名2,...
FROM 表名
WHERE 字段名 IS [NOT] NULL
例子:
SELECT ID,NAME,GRADE,GENDER FROM STUDENT WHERE GENDER IS NULL;
SELECT ID,NAME,GRADE,GENDER FROM STUDENT WHERE GENDER IS NOT NULL;
带DISTINCT 关键字查询
作用:查询表中某些重复的值,
SELECT DISTINCT 字段名 FROM 表名;
例子:
SELECT DISTINCT GENDER FROM STUDENT;
带DISTINCT 关键字作用与多个字段
SELECT DISTINCT 字段名1, 字段名2,...
FROM 表名;
SELECT DISTINCT GENDER ,NAME FROM STUDENT;
注意:DISTINCT 作用于GENDER 和NAME 两个字段时,只有两个字段名的值都相同的时候才能被认为是重复记录。
带LIKE 关键字查询
SELECT * | (字段名1,字段名2,...)
FROM 表名
WHERE 字段名 [NOT] LIKE '匹配字符串';
1.百分号(%)通配符
注意:1.LIKE后面的通配符里有空格的话,会影响.
2.LIKE后面的通配符的单引号,也可以换成双引号,但注意只能是英文下的符号,不可以是中文的单双引号!
3百分号通配符可以出现在通配符字符串的任意位置。
1.例子:查询开头是S的字段名
SELECT ID,NAME FROM STUDENT WHERE NAME LIKE'S%';
2.例子:查询开头W,结尾是G的字段名
SELECT ID,NAME FROM STUDENT WHERE NAME LIKE'W%G';
3.当百分号(%)放在在字符串前后个时,会查询所有带关于Y这个字符的字段,无论Y这个字符在什么位置
SELECT ID,NAME FROM STUDENT WHERE NAME LIKE '%Y%';
例子:NOT
SELECT ID NAME FROM STUDENT WHERE NAME NOT LIKE '%Y%'
2.下划线(_)通配符
注意:一个下划线代表一个字符
例子:查询开头时WU,结尾时ONG,中间只有一个未知字符的字段。
SELECT * FROM STUDENT WHERE NAME LIKE 'WU_ONG';
查询关于百分号 %和下划线
因为百分号和下划线都是通配符,他们在字符串中都有特色含义,因此要匹配字符串中的百分号和下划线,就需要在字符串中使用右斜线( \ ),对百分号和下划线进行转义。
要想匹配百分号(%)的写法:\%
,要想匹配下划线(_)的写法:_.
SELECT * FROM STUDENT WHERE NAME LIKE '%\%%'
全文查询 百分号(%):%\%%
带AND关键字查询
语法:
SELECT * |字段名1,字段名2,...
FROM 表名
WHERE 条件表达式1 AND 条件表达式2 ....AND 条件表达式n;
例子:
SELECT * FROM STUDENT WHERE ID<5 AND GENDER='女';
例子2
SELECT * FROM STUDENT WHERE ID IN (1,2,3) AND LIKE'张&' AND GRADE>90;
注意:返回的记录同时满足AND关键字的三个条件表达式
带OR关键字查询
语法
SELECT * | 字段名1,字段名2,...
FROM 表名
WHERE 条件表达式1 OR 条件表达式2 ....OR 条件表达式n;
例子:
SELECT * FROM STUDENT WHERE ID>3 OR GENDER='男';
注意:会查询出ID>3和性别为男的。只要记录满足OR关键字连接的任意条件会被查询,而不需要同时满足两个条件。
多学一招:OR 和AND关键字一起使用的情况
注意:AND的优先级高于OR,当两者在一起使用时,先运算AND两边的条件表达式,在运算OR 两边的条件表达式。
例子:
SELECT *
FROM STUDENT
WHERE GENDER ='女'OR GENDER='男'AND GRADER>90;
先运算AND两边的条件表达式,判断是否有男的成绩>90,输出和关于女的记录。
改一下例子:SELECT *
FROM STUDENT
WHERE GENDER ='男'OR GENDER='女'AND GRADER>90;
更改后的是先判断是否有女的成绩>90,输出和关于男的记录。
更换位置会结果不一样!!!
高级查询
聚合函数
1.COUNT 函数
语法:SELECT COUNT(*) FROM 表名;
SELECT COUNT(*) FROM STUDENT;
2.SUM函数
语法:SELECT SUM(字段名) FROM 表名;
SELECT SUM(GRADE) FROM 表名;
3.AVG函数
语法:SELECT AVG(字段名) FROM BOOK;
SELECT AVG(MONEY) FROM BOOK;
4.MAX函数
SELECT MAX(字段名)FROM 表名;
SELECT MAX(MONEY) FROM BOOK;
5.MIN函数
SELECT MIN(字段名) FROM 表名;
SELECT MIN(MONEY) FROM 表名;
对查询结果进行排序
语法:
SELECT 字段名1,字段名2,...
FROM 表名
ORDER BY 字段名1[ASC | DESC],字段名2[ASC | DESC]...
例子:
SELECT * FROM BOOK
ORDER BY BOOKID ASC,MONEY ASC;
注意:进行升序排列时,如果有字段的值为NULL,则这条会在第一条显示,null会被认为时最小值,汉字的升序排列在gbk里会按照首字母进行排序
分组查询
语法:
SELECT 字段名1,字段名2,...
FROM 表名
GROUP BY 字段名1,字段名2,...[HAVING 条件表达式];
1.单独使用GROUP BY分组
SELECT *FROM STUDENT GROUP BY GRADE;
2.GROUP BY 和聚合函数一起使用
GROUP BY 和聚合函数一起使用可以统计出某个或者某个字段在一个分组的最大值,最小值,平均值等。
例子:
SELECT COUNT(*),GENDER FROM STUDENT GROUP BY GENDER;
使用LIMIT限制查询结果的数量
注意:LIMIT 后面可以更两个函数,第一个参数"OFFSET"表示偏移量,如果偏移量为0,则从查询结果的第一条记录开始,偏移量为1,则从查询记录中的第二条结果开始,以此类推。
语法:`SELECT 字段名1,字段名2,...
FROM 表名
LIMIT [OFFSET,] 记录数 `
这是只返回前4条记录
例子:`SELECT * FROM STUDENT LIMIT 4;`
这时返回第四条结果到八
SELECT * FROM STUDENT OEDER BY GRADE DESC LIMIT 4,4;
函数
数学函数
函数名称 | 作用 |
---|---|
ABS(X) abs | 返回x的绝对值 |
SQRT(X) aqrt | 返回x的非负2次方根 |
MOD(X,Y) mod | 返回x被y除后的余数 |
CEILING(X)ceiling | 返回不小于x的最小整数 |
FLOOR(X)floor | 返回不大于x的最小整数 |
ROUND(X,Y)round | 对x进行四舍五入操作,小数点后保留y位 |
YRANCATE(x,y)trancate | 舍去x中小数点y位后面的数 |
SIGN(X)sign | 返回x的符号,-1,0,或者1 |
字符串函数
函数名称 | 作用 |
---|---|
LENGTH(str) | 返回字符串str的长度 |
CONCAT(s1,s2,…) | 返回一个或多个字符串连接产生的新字符串 |
TRIM(str) | 删除字符串两侧的空格 |
REPLACE(str1,s1,s2) | 使用字符串s2替换字符串str中所有的字符串 |
SUBSTRIN(str,n,len) | 返回字符串str的子串,起始位置为n,长度为len |
REVERSE(str) | 返回字符串反转后的结果 |
LOCATE(s1,str) | 返回子串s1在字符串str中的起始位置 |
日期和时间函数
函数名称 | 作用 |
---|---|
CURDATE() | 获取系统当前日期 |
CURTIME() | 获取系统当前系统时间 |
SYSDATE() | 获取当前系统日期和时间 |
TIME_TO_SEC_() | 返回将时间转换成秒的结果 |
ADDDATE() | 执行日期的加运算 |
SBUDATE | 执行日期的减运算 |
DATE_FORMAT() | 格式化输出日期和时间的值 |
条件判断函数
函数名称 | 作用 |
---|---|
IF(expr,v1,v2) | 如果expr表达式为true 返回v1,否则返回v2 |
IFNULL(v1,v2) | 如果v1不为null,返回v1,否则返回v2 |
CASE expr WHEN v1 THEN r1 [WHERE v2 THEN r2 …][ELSE rn] END | 如果expr 值等于v1v2等,返回对应位置THEN 后面的结果,否则返回ELSE 后面的结果rn |
加密函数
函数名称 | 作用 |
---|---|
MDS(str) | 对字符串str进行加密 |
ENCODE(str,pwd_str) | 使用pwd作为密码加密字符串str |
DECODE(str,pwd_str) | 使用pwd作为密码解密字符串str |
为表和字段取别名
在查询数据时,可以为表和字段取别名,这个别名可以代替其指定的表和字段
1.为表取别名
语法:SELECT * FROM [AS] 别名;
例如:SELECT * FROM STUDENT AS S WHERE S.grade='女';
2.为字段名取别名
语法:SELECT 字段名 [AS] 别名 [,字段名 [AS] 别名,...] FROM 表名
例子:SELECT name AS stu_name,gender stu_gender FROM student;
多表操作
外键
定义:外键是引用另一个表中一列或多列,被引用的列应该具有主键约束或唯一约束。外键用于建立和加强两个表数据之间的连接。也可以明确声明表与表之间关系字段参照关系,用于维护这些关系
为表添加外键约束
语法:ALTER TABLE 表名 add constraint FK_ID foreign key(外键字段名) REFERENCE 主表名(主键字段名 );
例子:alter table student add constraint KF_ID foreign key(gid) REFERENCESS grade (id)
但注意:建立外键的表必须是InnoDB型,不能是临时表,因为在MySQL里只有InnoDB类型的表才支持外键。
定义外键名时,不能加引号,;例如:constraint 'FK_ID’或constraint"FK_ID"等都是错误的。
删除外键约束
语法:ALTER TABLE 表名 drop foreign key 外键名;
例子:alter table student drop foregin key FK_ID;
操作关联表
1。多对一
2.多对多
3.一对一
添加数据
alter table student add constraint FK_ID key (gid) REFERENCES grade(id);
删除数据
注意:在删除两个有关联关系的表里的数据时,一定要先删除从表中的数据,然后在删除主表中的数据,否则会报错。。。。。
如果只想删除其中一个表,要先把表中数据改为NULL,
delect from student where sname ='王红'
连接查询
交叉连接
交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积
语法:SELECT * FROM 表1 CROSS JOIN 表2;
例子:SELECT * FROM department CROSS JOIN employee;
表一:department 表二:employee
内连接
内连接又称为简单连接或自然连接,是一种常见的连接查询
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段
SELECT employee.name,department.dname FROM department JOIN employee ON department.did=employee.did;
也可以使用where来实现同样的功能
例如:SELECT employee.name,department.dname FROM department,employee WHERE department.did=employee.did
例如:SELECT employee.name,department.dname FROM department,employee WHERE department.did=employee.did
但注意:两个语句结果虽然相同,但INNER JOIN 是内连接语句,WHERE是条件判断语句,在WHERE语句后可以直接添加其他条件,而INNER JOIN 语句不可以
外连接
语法格式:SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON 表2.关系字段=表2.关系字段 WHERE 条件
分为左连接和右连接
左连接
定义:左连接的结果包括LEFT JOIN 子句中指定的左表的所有记录,以及所有满足连接条件的记录。如果左表的某些记录在右不存在,则在右表中显示为空。
SELECT department.did,department.dname,employee.name FROM department LEFT JOIN employee on department.did=employee.did;
右连接
定义:右连接和左连接正好相反,返回右表中所有指定的记录和所有满足连接条件的记录,如果右表的某些记录在左不存在,则在左表中返回空值。
SELECT department.did,department.dname,employee.name FROM department RIGHT JOIN employee ON department.did=employee.did;