MySQL

MySQL数据类型

整数类型

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
INT	4	0~4294967295	-2147483648~2147482647
BIGINT	8	0~1844674407370955615	-9223372036854775808~9223372036854775807

 

 

 

 

 

 

 

浮点类型

FLOAT	4字节
DOUBLE	8字节

 

 

 

日期与时间类型：

数据类型	字节数	日期格式
YEAR	1	YYYY
DATE	4	YYYY-MM-DD
TIME	3	HH:MM:SS
DATETIME	8	0000-00-00 00:00:00
TIMESTAMP	4	0000-00-00 00:00:00

 

 

 

 

 

 

 

字符串和二进制类型：

数据类型	类型说明
CHAR	固定长度字符串
VARCHAR	可变长度字符串
BINARY	固定长度二进制数据
VARBINARY	可变长度二进制数据
BLOB	二进制大数据
TEXT	大文本数据
ENUM	枚举类型
SET	字符串对象，可以有零或多个值
BIT	表示位字段类型

 

 

 

 

 

 

 

 

 

 

 

 

数据库和表的基本操作
创建和使用数据库：

CREATE DATABASE 数据库名称;

USE 数据库名称

SHOW DATABASES;
SHOW CREATE DATABASE 数据库名称;

删除数据库

DROP DATABASE 数据库名称

 创建数据表

CREATE TABLE 表名(
字段名1 数据类型[完整性约束条件],
字段名2 数据类型[完整性约束条件],
    .
    .
    .
字段名n,数据类型[完整性约束条件],
);


#创建临时表(当关闭连接时，MySQL会自动删除临时表并释放所有空间)

CREATE TEMPORARY TABLE 表名(
字段名1 数据类型[完整性约束条件],
字段名2 数据类型[完整性约束条件],
    .
    .
    .
字段名n,数据类型[完整性约束条件],
);

SHOW CREATE TABLE 表名;

查看字段信息

DESC 表名;

修改字段名

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

添加字段

ALTER TABLE 表名 ADD 字段名 数据类型[约束条件];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段排列位置

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;

修改字段数据类型

ALTER TABLE 表名 MODIFY 字段名 数据类型;

删除数据表

DROP TABLE 表名;

 

MySQL约束

在MySQL中，主要有六种约束（约束是指对表中数据的限制，为了保证数据的可靠性和准确性）：

一.主键约束(PRIMARY KEY)：字段名 数据类型 PRIMARY KEY

　　为了快速查找到表中某条信息，通过设置主键实现。

　　多字段主键：

PRIMARY KEY(字段名1,字段名2,...,字段名n)

　　常常设置主键同时设置表的字段值自动增加：

字段名 数据类型 AUTO_INCREMENT；

二.

外键约束(FOREIGN KEY)：

ALTER TABLE 表名 ADD CONSTRAINT FK_ID FOREIGN KEY(外键字段名) REFERENCES 主表表名(主键字段名);  

建表时添加外键约束：

CONSTRAINT FK_ID FOREIGN KEY(外键字段名) REFERENCES 主表表名(主键字段名);

外键指引用另一个表的的一列或者多列，被引用的列应该具有主键约束或唯一约束。

 

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

 

三.非空约束(NOT NULL)：字段名 数据类型 NOT NULL;

　　字段的值不能为空。

四.唯一约束(UNIQUE)：字段名 数据类型 UNIQUE;

　　保证数据表中字段的唯一性。

五.默认值约束(DEFAULT):

　　默认值约束用来约束不设置值的某一字段，自动为其添加一个已经设置好的值。

六.检查约束（MySQL不支持，因此在这里不进行学习）

 

索引

在数据库操作中，也许会有 SELECT * FROM 表名 WHERE id=99999 的操作，这样数据库会从id=1开始遍历，

直到找到id为99999的数据，这样会使效率非常低下，MySQL中允许建立索引表提高查询或者排序效率。

1.普通索引

由KEY或者INDEX定义的索引

2.唯一性索引

由UNIQUE定义，所在字段的值必须是唯一的

3.全文索引

全文由FULLTEXT定义,只能创建在CHAR,VARCHAR,TEXT类型的字段上，只有MyISAM存储引擎支持全文索引。

4.单列索引

单列索引指的是在表中单个字段上创建索引，它可以是普通索引，唯一索引或者全文索引，只要保证该索引只对应一个字段就行。

5.多列索引

在表中多个字段建立索引，只有在查询条件中使用了这些字段中的第一个字段，该索引才会被使用

6.空间索引

由SPACIAL定义，只能在MyISAM引擎中创建。

注：创建完索引后可以通过EXPLAIN SELECT语句查看索引使用的情况。

使用CREATE INDEX语句对已有的表上添加索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名[(长度)] [ASC|DESC]);

也可以使用ALTER TABLE 对已存在的表添加索引

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (字段名 [(长度)] [ASC|DESC]);

删除索引

ALTER TABLE 表名 DROP INDEX 索引名; 

DROP INDEX 索引名 ON 表名;

 

添加丶更新与删除数据

添加

INSERT INTO 表名(字段名1,字段名2) VALUES (值1,值2,...);

更新

UPDATE 表名 SET 字段名1=值1[,字段名2=值2,...] [WHERE 条件表达式];

删除

DELETE FROM 表名 [WHERE 条件表达式];

如果没有WHERE子句，则会将表中所有记录删除

DELETE FROM 表名;

还有一种删除表中所有记录的方式

TRUNCATE [TABLE] 表名;

TRUNCATE和DELETE的区别：

1.DELETE是DML语句，TRUNCATE是DDL语句

2.DEL后面可以跟WHERE语句，TRUNCATE只能删除所有记录

3.使用TRUNCATE删除表中数据后再次向表中添加数据，自增字段的默认值从1开始，而DELETE后自动增加的值为删除字段的值加一

 

指定查询

SELECT 字段名1,字段名2... FROM 表名;

按条件查询

SELECT 字段名1,字段名2,... FROM 表名 WHERE 条件表达式;

关系运算符

=

<>不等于

!=不等于

<  >

<=

>=

 

 

 

 

 

 

 

 

 

带IN关键字的查询

SELECT 字段名1,字段名2,... FROM 表名 WHERE 字段名 [NOT] IN(元素1,元素2...);

带BETWEEN AND 的查询（指定范围）

SELECT 字段名1,字段名2,... FROM 表名 WHERE 字段名 [NOT] BETWEEN 值1 AND 值2;

空值查询（IS NULL）

带DISTINCT 的查询（去掉重复）

SELECT DISTINCT 字段名 FROM 表名;

带LIKE的查询（模糊查询）

SELECT 字段名1,字段名2,... FROM 表名 WHERE 字段名 [NOT] LIKE'匹配字符串';

通配符

% (匹配多个字符)

_ (匹配一个字符)

 

 

 

 

注：如果要查询有%的数据,在%前添加 \  转义即可

 

带AND的查询

SELECT 字段名1,字段名2,... FROM 表名 WHERE 条件表达式1 AND 条件表达式2 [AND ...];

带OR 的查询

SELECT 字段名1,字段名2,... FROM 表名 WHERE 条件表达式1 OR 条件表达式2 [OR ...];

注：AND和OR一起用，AND优先级高，OR后执行

 

聚合函数

函数名称	作用
COUNT()	返回某列行数
SUM()	返回某列的和
AVG()	返回某列平均值
MAX()	返回某列最大值
MIN()	返回某列最小值

 

 

 

 

 

 

 

 

对查询结果排序

SELECT 字段名1,字段名2,... FROM 表名 ORDER BY 字段名 [ASC | DESC];

默认ASC,升序，DESC降序

 

分组查询(按类统计)

SELECT 字段名1,字段名2,... FROM 表名 GROUP BY 字段名1,字段名2...[HAVING 条件表达式];

常常会有GROUP BY 和聚合函数一起使用的情况

计算分组的平均值，最大值，最小值等等

---------------------------------------------------------------------------------

更新：

今天写上机作业的时候遇到的问题：

表数据是这样的：

 

 

 题目：求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出.

我的代码：SELECT SNo,SUM(Score) FROM SC GROUP BY SNo HAVING COUNT(CNo)>3 AND Score>=60 ORDER BY Score DESC;

报错：ERROR 1054 (42S22): Unknown column 'Score' in 'having clause'

原因：根据表查询学生所有课程的成绩大于60，使用分组需要注意Score字段的时候需要使用聚合函数来配合分组

修改后的代码：SELECT SNo,SUM(Score) FROM SC GROUP BY SNo HAVING COUNT(CNo)>3 AND MIN(Score)>=60 ORDER BY Score DESC;

 

 这样就不会报错了，原题目是没有学生符合条件的，所以输出Empty set

总结：记住GROUP BY ... HAVING 后面使用聚合函数。

----------------------------------------------------------------------------

使用LIMIT限制查询结果的数量

SELECT 字段名1,字段名2,... FROM 表名 LIMIT [OFFSET,] 记录数;

为表取别名

SELECT * FROM 表名 [AS] 别名;

为字段取别名

SELECT 字段名 [AS] 别名 [,字段名 [AS] 别名,...]  FROM 表名;

 

存储过程、存储函数

假设需要执行一系列语句，或者遇到经常重复使用同一功能的情况，可以将这些语句封装到一个程序当中，而不需要一次一次执行sql语句，在需要时随时调用这个程序。

那除了sql语句，还可以使用变量来存储结果，这期间可以使用IF，CASE，循环语句等等.

存储的函数（FUNCTION）和存储的过程(PROCEDURE)都称为存储例程。

特性：存储函数具有返回值，存储过程没有返回值；

　　　所有的代码写在BEGIN和END之间；

　　　存储函数可以在SELECT语句中直接调用；

　　　可以使用CALL语句调用存储过程；

　　　由于存储例程中的语句应以分隔符结尾，因此必须更改MySQL的分隔符(例如 ：DELIMITER $$)，以便MySQL不会用正常语句解释存储例程中的SQL语句；

　　　参数IN的作用(代表输入，意思说你的参数要传到存过过程的过程里面去)；

　　　参数OUT的作用(代表输出)；

　　　INOUT的作用（既能输入一个值又能输出一个值）；

 

示例：

 

CREATE TABLE student(
id INT(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
grade FLOAT,
gender CHAR(2)
);

INSERT INTO student(name,grade,gender)
VALUES
('tom',60,'男'),
('rose',90,'女')

 

定义一个存储过程：

 DELIMITER $$
 CREATE PROCEDURE Proc()
 BEGIN
 SELECT * FROM student;
 END $$

定义完之后记得修改结束符：
DELIMITER ;

 

PROCEDURE不能使用SELECT调用

 

 

变量的使用：

DECLARE语句可以在存储过程中声明并使用

DECLARER var_name[,var_name]...data_type [DEFAULT value];

示例：
DECLARE var1 INT DEFAULT 100;

定义变量之后可以为变量赋值
SET var1=10;

 时间函数：

CURTIME() 返回当前时间 current time

CURDATE() 返回当前日期 current date

NOW() 返回当前日期和时间 

NOW()+0 返回格式为YYMMDDMMSS

NOW(n) 返回精确到的小数点后n位

SYSDATE() 返回服务器当前时间，一般使用NOW()而不使用SYSDATE()

NOW()和SYSDATE()的区别：SYSDATE返回函数执行的时间，NOW返回语句执行的时间

 

　　可以看到两个SYSDATE()函数间隔了两秒

TIMEDIFF(expr1,expr2) 返回expr1-expr2相差的时间数

 

在现在时间的基础上再加N天N月N年  DATE_ADD()

 

在现在时间的基础上再减N天N月N年 DATE_SUB()

 

 

 

 

 

面试题目：

MySQL具体有几种存储引擎:

SHOW ENGINES;

 

 Support表示MySQL支持使用的引擎，Comment是对各种引擎的简单介绍，Transactions表示是否支持事物，最常用的就是myISAM和InnoDB，其它很少会用，MEMORY基于内存来存储，效率低下。

MyISAM和InnoDB的区别：

1.存储文件：MyISAM每个表有两个文件，一个是MYD，一个是MSI，MYD数据文件，MYI是索引文件；InnoDB只有一个文件idb，索引和数据存在一起。

2.InnoDB支持事务，支持行锁，支持外键

3.InnoDB支持XA事务，支持SavePoints