学习笔记
上面自动生成的目录有错误,这个目录方便查找。
创建数据库
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