MySQL学习笔记

学习笔记

上面自动生成的目录有错误，这个目录方便查找。

• 学习笔记
  • 创建数据库
  • 创建数据表
  • 插入数据
  • 更新数据
  • 删除数据
  • 查询数据
    • 基本形式
    • DISTINCT
    • WHERE
      • 精确搜索
      • 模糊搜索LIKE
    • 其他扩展
      • AND OR
      • IN
      • BETWEEN
      • ORDER BY
  • LIMITTOP
  • 别名AS
  • 连接JOIN
    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL JOIN
  • UNION
  • INSERT INTO SELECT
  • ALTER TABLE
  • AUTO INCREMENT
  • 索引
    • 建立索引
      • CREATE INDEX 语法
      • CREATE UNIQUE INDEX 语法
    • 删除索引
      • DROP INDEX 语法
  • DROP
    • DROP DATABASE 语句
    • DROP TABLE 语句
    • TRUNCATE TABLE 语句
  • GROUP BY
  • HAVING
  • 日期
    • Date 函数
    • Date 数据类型
    • 日期格式化
  • 函数
    • AVG
    • COUNT
    • MAX与 MIN
    • SUM
    • UCASE与LCASE
    • MID
    • LENGTH
    • ROUND
    • FORMAT
    • IFNULL
  • 其他
    • MySQL 数据类型
      • Text 类型
      • Number 类型
      • Date 类型
    • SQL通配符
    • 约束
    • SQL语法排序

创建数据库

CREATE DATABASE dbname;

例：

#创建一个名为my_db的数据库
CREATE DATABASE my_db;

创建数据表

CREATE TABLE tablename
(column_name1 data_type(size) [AUTO_INCREMENT] [constraint_name],
column_name2 data_type(size) [constraint_name],
column_name3 data_type(size) [constraint_name],
....
);

参数	意义
tablename	表名
constraint_name	列名
data_type	数据类型 (例如 varchar、integer、decimal、date 等等)
size	数据长度
AUTO_INCREMENT	会在新记录插入表中时生成一个唯一的数字(详细信息)
constraint_name	约束类型 (NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT)

例：

#创建一个数据表
CREATE TABLE student
(Id INT(10) auto_increment PRIMARY KEY, #主键自增
Name VARCHAR(20) not NULL,#非空
Sex VARCHAR(4),
Birth YEAR,
Department VARCHAR(20) NOT NULL,
Address VARCHAR(50));

插入数据

INSERT INTO 语句用于向表中插入新记录。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

NSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

例：

INSERT INTO student (Name,Sex,Birth,Department,Address) 
VALUES ('小王','男','1994','自动化','天津');
INSERT INTO student
VALUES ('','小李','女','1992','计算机','河北');

更新数据

Update 语句用于修改表中的数据。

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例：

UPDATE student_4088 SET Name='小赵' WHERE Id = '2';

注：更新多条信息时用’,’隔开，如下：

UPDATE student_4088 SET Name = '小赵',sex = '女' WHERE Id = '2';

删除数据

DELETE 语句用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值

例：

DELETE FROM student WHERE Id = '2'; 

查询数据

基本形式

SELECT column_name,... FROM table_name;

例：

SELECT Name,Sex FROM student; 
SELECT * FROM student;

DISTINCT

仅列出不同值

SELECT DISTINCT column_name,column_name
FROM table_name;

WHERE

精确搜索

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

例：

SELECT Name 
FROM student 
WHERE Sex = '男';

模糊搜索(LIKE)

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

pattern ‘%str’ ==> 以str结尾的。
‘str%’ ==> 以str开始的。
‘%str%’ ==> 包含str的。
关于 通配符 下文有提到。

例：

SELECT Name FROM student WHERE Birth LIKE '%99%';

其他扩展

AND & OR

多条件
例：

SELECT Name 
FROM student_4088 
WHERE Birth LIKE '%9%'
AND
(
Name LIKE '小%'
OR
Sex like '女' 
);

IN

IN 操作符允许您在 WHERE 子句中规定多个值。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

例：

SELECT *
FROM student_4088 
WHERE Birth IN ('1992', '1994');

BETWEEN

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

例：

SELECT *
FROM student_4088 
WHERE Birth BETWEEN 1992 AND 1994;

ORDER BY

排序

ASC 或缺省 ==> 升序  
DESC ==> 降序

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

例：

SELECT *
FROM student_4088 
WHERE Birth LIKE '%9%'
AND(
Name LIKE '小%'
OR
Sex like '女' )
ORDER BY Id;

LIMIT(TOP)

规定要返回的记录的数目。

SELECT column_name(s)
FROM table_name
LIMIT number;

例：

SELECT *
FROM student_4088 
WHERE Birth LIKE '%9%'
ORDER BY Id 
LIMIT 4;

别名(AS)

通过使用 SQL，可以为表名称或列名称指定别名。

列

SELECT column_name AS alias_name
FROM table_name;

表

SELECT column_name(s)
FROM table_name AS alias_name;

例：

#列的别名
SELECT name AS '姓名'
FROM student_4088 ;

#表的别名
SELECT s.name, c.C_name, c.Grade
FROM student_4088 AS s, score_4088 AS c
WHERE s.Id = c.Stu_id;

连接(JOIN)

JOIN 用于把来自两个或多个表的行结合起来。

语句	意义
INNER JOIN	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表返回所有的行
FULL JOIN	只要其中一个表中存在匹配，则返回行

INNER JOIN

INNER JOIN 关键字在表中存在至少一个匹配时返回行。
交集

SELECT column_name(s)
FROM table1
[INNER] JOIN table2
ON table1.column_name=table2.column_name;

例：

SELECT s.name, c.C_name, c.Grade
FROM student_4088 AS s
INNER JOIN score_4088 AS c
ON s.Id = c.Stu_id;

LEFT JOIN

LEFT JOIN 从左表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为 NULL。
左边都有右边看情况。

SELECT column_name(s)
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column_name=table2.column_name;

例：

SELECT s.name, c.C_name, c.Grade
FROM student_4088 AS s
LEFT JOIN score_4088 AS c
ON s.Id = c.Stu_id;

RIGHT JOIN

RIGHT JOIN 关键字从右表返回所有的行，即使左表中没有匹配。如果左表中没有匹配，则结果为 NULL。
右边都有左边看情况。

SELECT column_name(s)
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column_name=table2.column_name;

例：

SELECT s.name, c.C_name, c.Grade
FROM student_4088 AS s
RIGHT JOIN score_4088 AS c
ON s.Id = c.Stu_id;

FULL JOIN

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

例：

SELECT * 
FROM score
FULL JOIN student
ON stu_id = student.Id;

UNION

UNION 操作符合并两个或多个 SELECT 语句的结果。
两个表的结构要相同。

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

INSERT INTO SELECT

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

例：

INSERT INTO score
(Id)
SELECT  Stu_id
FROM student;

ALTER TABLE

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name
DROP COLUMN column_name

要改变表中列的数据类型

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

例：

#添加列
ALTER TABLE student
ADD test VARCHAR(10);

#修改参数类型
ALTER TABLE student
MODIFY COLUMN test INT(10);

#删除列
ALTER TABLE student
DROP COLUMN test;

AUTO INCREMENT

Auto-increment 会在新记录插入表中时生成一个唯一的数字。
下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID));

使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

索引

建立索引

CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。不允许使用重复的值

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

例：

CREATE INDEX test1
ON student (Name);

删除索引

DROP INDEX 语法

ALTER TABLE table_name DROP INDEX index_name

例：

ALTER TABLE student DROP INDEX test1;

DROP

DROP DATABASE 语句

DROP DATABASE 语句用于删除数据库。

DROP DATABASE database_name

DROP TABLE 语句

DROP TABLE 语句用于删除表。

DROP TABLE table_name

TRUNCATE TABLE 语句

删除表内的数据，但并不删除表本身。

TRUNCATE TABLE table_name

GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例：

SELECT C_name, COUNT(C_name) 
FROM score_4088
GROUP BY C_name;

HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value; 

日期

Date 函数

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

Date 数据类型

类型	格式
DATE	格式：YYYY-MM-DD
DATETIME	格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP	格式：YYYY-MM-DD HH:MM:SS
YEAR	格式：YYYY 或 YY

日期格式化

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

DATE_FORMAT(date,format)

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

函数

AVG()

AVG() 函数返回数值列的平均值。

SELECT AVG(column_name) FROM table_name

COUNT()

COUNT() 函数返回匹配指定条件的行数。

SELECT COUNT(column_name) FROM table_name;

COUNT(DISTINCT column_name) 返回指定列的不同值的数目。

SELECT COUNT(DISTINCT column_name) FROM table_name;

MAX()与 MIN()

MAX()/MIN()返回指定列的最大/小值。

SELECT MAX(column_name)/MIN(column_name) FROM table_name;

SUM()

SUM() 函数返回数值列的总数。

SELECT SUM(column_name) FROM table_name;

UCASE()与LCASE()

UCASE()/LCASE()把字段的值转换成大/小写。

SELECT UCASE(column_name)/LCASE(column_name) FROM table_name;

MID()

MID() 函数用于从文本字段中提取字符。

SELECT MID(column_name,start[,length]) FROM table_name;

参数	意义
column_name	字段
start	开始位置默认为1
length	长度

LENGTH()

LENGTH() 函数返回文本字段中值的长度。

SELECT LENGTH(column_name) FROM table_name;

ROUND()

ROUND() 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM table_name;

参数	意义
column_name	字段
decimals	小数位数

FORMAT()

FORMAT() 函数用于对字段的显示进行格式化。

SELECT FORMAT(column_name,format) FROM table_name;

参数	意义
column_name	字段
format	规定格式。’%Y-%m-%d’

SELECT DATE_FORMAT(Now(),'%Y-%m-%d') FROM Websites; 

IFNULL()

IFNULL()函数用于在发现参数为空时为参数赋值。

例：

SELECT IFNULL(grade,0)
FROM score;

其他

MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串。在括号中指定字符串的最大长度。最多 255 个字符。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。可以按照此格式输入可能的值： ENUM(‘X’,’Y’,’Z’)
SET	与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615
FLOAT(size,d)	带有浮动小数点的小数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。在size参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

*这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。
如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD、注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME()	时间。格式：HH:MM:SS注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()	2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。
在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。
TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

SQL通配符

通配符可用于替代字符串中的任何其他字符。

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或[!charlist]	不在字符列中的任何单一字符

注意：”_”不能匹配中文
[charlist]，[^charlist]不能用like语句。

注：后两个与正则表达式有关。

约束

如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

语句	意义
NOT NULL	指示某列不能存储 NULL 值。
UNIQUE	保证某列的每行必须有唯一的值。
PRIMARY KEY	NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY	(外键)保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK	保证列中的值符合指定的条件。
DEFAULT	规定没有给列赋值时的默认值。

SQL语法排序

From
On
Join
Where
Group By
With
Having
Select
Distinct
Order By
TOP