FrostySun

SQL学习之MySQL数据库

  • 连接MySQL数据库四大基本要素

    • 数据库服务器的IP
    • 数据库服务器的数据库端口号
    • 数据库用户名
    • 数据库密码

  • linux登陆MySQL数据库

    • mysql -u root -p

      • -u 用户（登陆的用户）
      • -p 密码
      • -h host 主机名
      • -P 端口号（MySQL默认端口号3306）
  • Windows电脑使用Navicat工具连接数据库

  • SQL

    • SQL是用于访问和处理数据库的标准计算机语言
    • 指结构化查询语言，全称是 Structured Query Language
    • 对于数据库的操作可分为：增、删、改、查
    • 所有的数据库支持大部分标准化SQL语句，但对于部分语句如top语句等等会有轻微差异

    • linux 后台使用MySQL数据库

      • 查询库：show databases;
      • 切库：use student;
      • 查询所有表：show tables；
      • 查询表中所有数据：select * from table_name;
    • 在SQL语言中所有关键字不区分大小写
    • 分号是区分每条SQL的标准关键字，尽量在每条SQL语句结束添加分号

    • 常用SQL关键字：

      • SELECT 从数据库中提取数据（查询操作）
      • UPDATE 修改数据库中的数据（修改操作）
      • DELETE 从数据库中删除数据（删除操作）
      • INSERT INTO 向数据库中插入新数据（新增操作）
      • CREATE DATABASE 创建新数据库（新增操作）
      • ALTER DATABASE 修改数据库（修改操作）
      • CREATE TABLE 创建新表（新增操作）
      • ALTER TABLE 修改表（修改操作）
      • DROP TABLE 删除表（删除操作）
      • CREATE INDEX 创建索引（新增操作）
      • DROP INDEX 删除索引（删除操作）

    • SELECT 查询语句

      • 从数据库查询数据，结果被存储到结果集中
      • 语法：
SELECT column_name,column_name FROM table_name;
SELECT 需要展示的数据列 FROM 查询的数据源;
SELECT * FROM table_name;

  • SELECT DISTINCT 语句用于返回唯一不同的值

    • 语法：
SELECT DISTINCT column_name,column_name FROM table_name;
  • 实例
SELECT DISTINCT age from student;

  • WHERE 子句用于过滤记录

    • 语法：
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

实例：
SELECT * from student where age > 20 ;
SELECT * from student where name='后秀辽';
  • 运算符：
运算符描述
=等于
<>或!=不等于
>大于
<小于
>=大于等于
<=小于等于
BETWEEN在某个范围内
LIKE搜索某种模式（模糊匹配）
IN针对某个列可能出现的多个值（包含）
  • 实例：
select * from student where age = 20;
select * from student where sex <> '男';
select * from student where sex != '男';
-- 注释
# 注释
# 注释
-- BETWEEN AND 这两个在一起使用，取值范围包前包后
select * from student where age BETWEEN 16 and 20;

  • AND & OR 运算符用于基于一个以上的条件对记录进行过滤

    • AND 逻辑与,汉语可当并且用
    • OR 逻辑或，汉语中可当或者使用
select * from student where age = 18 and sex = '女';
select * from student where age = 18 or sex = '女';

  • ORDER BY 关键字用于对结果集进行排序

    • 语法
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
  • 示例
-- 升序
SELECT * from student ORDER BY age;
-- 降序
SELECT * from student ORDER BY age desc;
-- 多列排序
-- order by 后的字段谁在前，先排谁，如果有相同的，在根据第二个字段进行排序，如果第二个字段也有相同的，则有第三个字段的话会根据第三个字段进行排序...
select * from number ORDER BY age,chengji;

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

    • 语法：
-- insert into 语句有两种语法
-- 第一种，不指定插入的列，直接提供需要插入的值即可
-- 需要注意的是不指定列，则必须插入的值为所有列，且顺序必须和表中的列顺序保持一致
INSERT INTO table_name
VALUES (value1,value2,value3,...);
-- 第二种，指定插入的列，根据指定的列顺序提供插入的值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

  • UPDATE 语句用于更新表中已存在的记录

    • 语法：
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

    • 注意事项：

      • 执行update语句之前一定要先把where字句后的条件拿到select语句中执行，确保查询到的数据是我们想要更新的数据，在去执行update语句
      • 执行没有where子句的update语句是对表中所有数据的修改，所以一定要慎重执行

    • DELETE 语句用于删除表中的记录（逐行删除）

      • 语法：
    DELETE FROM table_name
WHERE some_column=some_value;
-- 删除所有数据
DELETE * FROM table_name;

    • 注意事项：

      • 执行delete语句之前一定要先把where字句后的条件拿到select语句中执行，确保查询到的数据是我们想要删除的数据，在去执行delete语句
      • 执行没有where子句的delete语句是对表中所有数据的删除，所以一定要慎重执行

    • TOP、LIMIT、ROWNUM 字句

      • MySQL 语法
    SELECT column_name(s)
FROM table_name
LIMIT number;
    • SQL Server语法
    SELECT TOP number * FROM table_name;
    • Oracle语法
    SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

    • LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式(模糊匹配)

      • 语法：
    SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

    • 两个占位符：_和%

      • _表示占一位
      • %表示0个或者多个字符
    • 示例：
    SELECT * from student WHERE name LIKE '后__';
SELECT * from student WHERE name LIKE '%后%';

    • IN 操作符允许在SQL中的where子句中规定多个值

      • 语法
    SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
-- 示例：
select * from student where age = 18 or age = 20 or age = 22;
select * from student where age in (18,20,22);

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

      • 语法：
    SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

-- 示例：
select * from student where age >=18 and age <=22;
select * from student where age BETWEEN 18 and 22;

    • NOT BETWEEN 操作符用于选取不介于两个值之间的数据

      • 语法：
    SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

-- 示例：
select * from student where age <18 or age >22;
select * from student where age NOT BETWEEN 18 and 22;

    • 带有 IN 的 BETWEEN 操作符

      • 语法：
    SELECT column_name(s)
FROM table_name
WHERE (column_name1 BETWEEN value1 AND value2)
AND column_name2 NOT IN (value3, value4);

-- 示例：
SELECT * FROM student WHERE (age BETWEEN 18 AND 20) AND xi NOT IN ('计算机系', '金融系');

    • AS 可以为表名称或列名称指定别名

      • 语法
    SELECT column_name AS alias_name
FROM table_name AS alias_name;
-- 示例：
select name as '姓名',sex as '性别', adder as '地址',xi as '系' FROM student;
select * from student as s,number as n where s.`name`= n.`name`;

    • SQL 连接(JOIN)连接查询，join 用于把来自两个或多个表的行结合起来

      • INNER JOIN 内联，如果连接的两个表中有匹配的连接数据，则返回匹配的，没有匹配的则返回空数据

        • 语法
    SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
-- 示例
SELECT * FROM student INNER JOIN number ON student.name = number.name;
-- 一般内联直接使用JOIN ON关键字，下面的这条SQL语句等同于上面的SQL
SELECT * FROM student JOIN number ON student.name = number.name;
    • 示意图

    • LEFT JOIN 左联，以左表为主，右表为辅，查询出满足条件的左表的所有数据，然后拿右表进行关联，如果有关联数据则显示关联的数据，如果没有，则右表的列展示为空，左表展示满足条件的全部数据

      • 语法
    SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
-- 示例
SELECT * FROM student LEFT JOIN number ON student.name = number.name
    • 示意图

    • RIGHT JOIN 右联，和左联相反

      • 语法
    SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
-- 示例
SELECT * FROM number RIGHT JOIN student ON student.name = number.name
    • 示意图

    • FULL JOIN 全联，左联和右联的集合体，备注：此SQL不支持MySQL数据库

      • 语法
    SELECT column_name(s)
FROM table_name1
FULL OUTER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
-- 示例
SELECT * FROM number FULL OUTER JOIN student ON student.name = number.name;
    • 示意图

    • SQL UNION操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同

      • UNION语法：
    SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
    • UNION ALL 语法
    SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
    • INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响.
    • 语法
    -- 从一个表中复制所有的列插入到另一个已存在的表中
INSERT INTO table_name2
SELECT * FROM table_name1;
-- 示例
INSERT into student_copy1 SELECT * FROM student

-- 只复制希望的列插入到另一个已存在的表中
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
-- 示例
insert into number_copy1(name) SELECT name from student;

    • SQL CREATE DATABASE 语句，用于创建数据库

      • 语法
    CREATE DATABASE database_name;
-- 示例
CREATE DATABASE `interface_one`
-- 指定数据库字符集和排序规则
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    • CREATE TABLE 语句，用于创建数据库中的表

      • 语法
    CREATE TABLE `table_name`
(
`column_name1` data_type(size),
`column_name2` data_type(size),
`column_name3` data_type(size),
)
-- 参数示意
column_name 参数规定表中列的名称
data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
size 参数规定表中列的最大长度。
   
-- 示例
-- 创建表之前一定检查是否存在相同的表，以下语句可以理解为：删除number这个表，如果存在的话
DROP TABLE IF EXISTS `number`;
CREATE TABLE `number` (
`name` varchar(255),
`kemu` varchar(255),
`chengji` int(11),
`id` int(9)
)

    • 约束（Constraints）SQL 约束用于规定表中的数据规则

      • CREATE TABLE + CONSTRAINT 语法
    CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
...
)

    • SQL中的约束

      • 非空约束：NOT NULL 指定某列不能存储空值

        • 示例
    CREATE TABLE `number` (
-- 对name列做非空约束
`name` varchar(255) NOT NULL,
`kemu` varchar(255),
`chengji` int(11),
`id` int(9)
)

    • 唯一约束：UNIQUE 保证某列的值在一张表中是唯一存在的（可以存在空值，但是只能存在一个）

      • 示例
    -- mysql
CREATE TABLE `number` (
-- 对name列做非空约束
`name` varchar(255) NOT NULL,
`kemu` varchar(255),
`chengji` int(11),
`id` int(9),
-- 对name做唯一约束
UNIQUE(name)
)
     
-- sql server 和Oracle
CREATE TABLE `number` (
-- 对name列做非空约束
-- 对name做唯一约束
`name` varchar(255) NOT NULL UNIQUE,
`kemu` varchar(255),
`chengji` int(11),
`id` int(9)
)
-- 为已经存在的表增加唯一约束
ALTER TABLE number
ADD UNIQUE (name);
-- 增加唯一约束，并自定义约束名称
ALTER TABLE number ADD CONSTRAINT nb_name UNIQUE (name)
-- 撤销约束
-- MySQL
ALTER TABLE number DROP INDEX nb_name
-- SQL server 和 oracle
ALTER TABLE number DROP CONSTRAINT nb_name

    • 主键约束：PRIMARY KEY 是非空约束和唯一约束的集合体，唯一标示表中一行的数据（主键约束可以使用一列或者两列乃至多列共做主键）

      • 语法
    -- mysql
CREATE TABLE `number` (
`name` varchar(255),
`kemu` varchar(255),
`chengji` int(11),
`id` int(9) NOT NULL,
-- 为表中增加主键约束
PRIMARY KEY (id)
)
-- sqlserver 和 oracle数据库
CREATE TABLE `number` (
`name` varchar(255),
`kemu` varchar(255),
`chengji` int(11),
-- 为表中增加主键约束
`id` int(9) NOT NULL PRIMARY KEY
)
-- 为已经存在的表增加主键约束
ALTER TABLE number ADD PRIMARY KEY (id);
-- 如需命名主键约束
ALTER TABLE number ADD CONSTRAINT nb_id PRIMARY KEY (id)
-- 撤销主键约束
ALTER TABLE Persons
DROP PRIMARY KEY

    • 自增序列

      • 在数据库中一般使用自增序列作为主键，且自增序列唯一且不为空，在已有数据的表中创建自增序列，数据库会自动填充空值

      • 语法

    -- 创建表时增加自增序列
CREATE TABLE number
(
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(255)
)
-- 建表后增加自增序列
-- 此SQL可以修改已经生成的自增序列，可以指定增长的开始值，也可以修改被删除数据库，如果自增序列会出现断裂，也可以使用以下语句进行重新生成
-- 每次执行此SQL会使数据库中的自增序列按照当前最大的数继续自增
ALTER TABLE number AUTO_INCREMENT=1

    • 外键约束：FOREIGN KEY 保证一个表中的数据匹配另一个表中的值的参照完整性

      • 语法
    -- mysql创建外键约束
CREATE TABLE student
(
student_id int(9) NOT NULL,
name varchar(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES number(student_id)
)
-- SQLserver和Oracle创建外键约束
CREATE TABLE student
(
name varchar(255),
student_id int(9) FOREIGN KEY REFERENCES number(student_id)
)
-- MySQL、SQLserver、Oracle
CREATE TABLE student
(
name varchar(255),
student_id int(9),
CONSTRAINT fk_StuNumber FOREIGN KEY (student_id)REFERENCES number(student_id)
)
-- 如果表已经存在则使用下面的方式进行创建
ALTER TABLE number
ADD FOREIGN KEY (student_id)
REFERENCES number(student_id)
-- 如需对外键约束重命名，则使用以下方法
ALTER TABLE Orders 
ADD CONSTRAINT fk_StuNumber 
FOREIGN KEY (student_id) 
REFERENCES number(student_id) 
-- 撤销外键约束
-- MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_StuNumber
-- SQLserver和Oracle
ALTER TABLE Orders
DROP CONSTRAINT fk_StuNumber

    • 检查约束: CHECK 检查列中的值要符合检查约束的条件

      • 语法
    -- MySQL
CREATE TABLE student
(
name varchar(255),
student_id int(9),
sex int(2),
CHECK (student_id > 0)
)
-- SQLserver、Oracle
CREATE TABLE student
(
name varchar(255),
student_id int(9) CHECK(student_id > 0),
sex int(2)
)
-- 创建表之后的check约束
ALTER TABLE student
ADD CHECK (student_id>0)
-- 撤销CHECK约束
-- MySQL
ALTER TABLE student
DROP CHECK chk_student
-- SQLserver、Oracle
ALTER TABLE student
DROP CONSTRAINT chk_student

    • 默认约束：DEFAULT 规定没有给列赋值时的默认值

      • 语法
    CREATE TABLE student
(
name varchar(255),
student_id int(9),
adder varchar(255) DEFAULT '中国'
)
-- 当表已经被创建时增加默认约束
-- MySQL
ALTER TABLE student
ALTER adder SET DEFAULT '中国'
-- SQLserver
ALTER TABLE student
ADD CONSTRAINT ab_c DEFAULT '中国' for adder
-- Oracle
ALTER TABLE student
MODIFY adder DEFAULT '中国'
-- 撤销约束
-- MySQL
ALTER TABLE student
ALTER adder DROP DEFAULT
-- SQLserver、Oracle
ALTER TABLE student
ALTER COLUMN adder DROP DEFAULT

    • CREATE INDEX 语句用于在表中创建索引

      • 创建索引
    CREATE INDEX index_name ON table_name (column_name,...)
-- 示例
CREATE INDEX SIndex ON student (name)
CREATE INDEX SIndex ON student (name,student_id)

    • DROP 语句

      • 删除索引
    ALTER TABLE table_name DROP INDEX index_name
    • 删除表
    DROP TABLE table_name;
    • 删除数据库
    DROP DATABASE database_name;
    • TRUNCATE TABLE截断表
    TRUNCATE TABLE table_name;
    • ALTER TABLE 语法
    -- 向数据库中添加列
ALTER TABLE table_name
ADD column_name datatype;
-- 删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name;
-- 改变表中列的数据类型
-- MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
-- SQLserver
ALTER TABLE table_name
ALTER COLUMN column_name datatype
-- Oracle 10g之后版本
ALTER TABLE table_name
MODIFY column_name datatype;

    • CREATE VIEW 视图，是基于 SQL 语句的结果集的可视化的表

      • 语法
    CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
    • 示例
    CREATE VIEW Transcript AS
SELECT
 s.NAME AS '姓名',
 s.sex AS "性别",
 s.studentid AS "学号",
 n.kemu AS '科目',
 n.chengji AS '成绩'
FROM
 student AS s
 JOIN number AS n ON s.NAME = n.NAME;
-- 查询视图
SELECT * FROM Transcript;
    • 删除视图
    DROP VIEW view_name;

    • 函数

      • DATE()函数
    -- 时间格式化
DATE_FORMAT() 格式化时间的函数
DATE_FORMAT(date,format)  date:合法的时间,format规定要展示的日期/时间格式
%a 简写的英文星期
%b 简写的英文月份
%c 数值表示的月份
%D 带有英文后缀的月中的第几天
%d 表示月中的第几天(00-31)
%e 表示月中的第几天(0-31)
%f 表示微秒数
%H 小时（0-23）
%h 小时（00-23）
%I 小时（01-12）
%i 分钟（00-59）
%j 表示年中的第几天(001-366)
%k 小时数（0-23）
%l 小时（1-12）
%M 完整的英文月份
%m 月份（00-12）
%p AM或者PM
%r 十二个小时制的时间(hh:mm:ss AM或者PM)
%S 秒数（00-59）
%T 24小时制的时间（hh:mm:ss）
%U 年中的第几周（00-53）星期日是一周的第一天
%u 年中的第几周（00-53）星期一是一周的第一天
%W 完整的英文星期
%w 一周的第几天（0表示星期日）
%Y 4位表示的年份
%y 2位表示的年份
select date_format(now(),'%Y年%m月%d日 %T')

    • NULL值代表遗漏的未知数据

      • IS NULL
    -- 注：NULL和空字符串还有0是有区别的
SELECT * FROM student WHERE email IS NULL;
    • IS NOT NULL
    SELECT * FROM student WHERE email IS NOT NULL;
    • IFNULL 函数
    -- mysql
SELECT IFNULL(email,"000@00.com") FROM student WHERE email IS NULL;
-- Oracle
SELECT name,NVL(email,000@00.com) FROM student WHERE email IS NULL;
-- SQL Server
SELECT name,ISNULL(email,000@00.com) FROM student WHERE email IS NULL;

    • 数据类型

      • Text 类型
    数据类型描述
    CHAR(size)保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
    VARCHAR(size)保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
    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, size 默认为 6。
    MEDIUMINT(size)带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
    INT(size)带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
    BIGINT(size)带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
    FLOAT(size,d)带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。Float（4,2）
    DOUBLE(size,d)带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。
    DECIMAL(size,d)作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
    • 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。
    • AVG()函数返回数值列的平均值
    SELECT AVG(column_name) FROM table_name
-- 示例
select AVG(age) FROM number;
    • COUNT() 函数返回匹配指定条件的行数
    SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;
-- 示例
select count(*) from student;
select count(name) from student;
    • MAX() 函数返回指定列的最大值。
    SELECT MAX(column_name) FROM table_name;
-- 示例
select max(age) from student;
    • MIN() 函数返回指定列的最小值
    SELECT MIN(column_name) FROM table_name;
-- 示例
select min(age) from student;
    • SUM() 函数返回数值列的总数
    SELECT SUM(column_name) FROM table_name;
-- 示例
select sum(age) from student;
    • ROUND() 函数用于把数值字段舍入为指定的小数位数
    • 字符串处理函数
    函数功能
    concat(s1, s2, … , sn)连接s1, s2, …, sn 为一个字符串
    insert(str, x, y, instr)将字符串str从第x位置开始， y个字符长度的子字符串替换为字符串instr
    lower(str)将字符串str中所有的字符转换为小写
    upper(str)将字符串str中所有的字符转换为大写
    left(str, x)返回字符串str最左边的x个字符
    right(str, y)返回字符串str最右边的y个字符
    lpad(str, n, pad)用字符串pad对str最左边进行填充， 直到长度为n个字符长度
    rpad(str, n, pad)用字符串pad对str最右边进行填充， 直到长度为n个字符长度
    ltrim(str)去掉str中最左边的空格
    rtrim(str)去掉str中最右边的空格
    repeat(str, x)返回str中重复出现x次的结果
    replace(str, a, b)将字符串str中的a更换为b
    strcmp（s1, s2）比较字符串s1, s25
    trim(str)去掉字符串str两边的空格
    substring(str, x, y)返回字符串str x位置开始y个字符长度的字符串

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

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

-- 示例
select xi,avg(age) from student where xi is not null and xi !="" GROUP BY xi;

    • HAVING 关键字

      • SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
      • 语法
    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;
-- 示例，查找成绩总分大于200的学生
select name from number GROUP BY name HAVING sum(chengji)>200

    • 高级查询

      • 简单子查询:子查询是SELECT语句中另外一条SELECT语句，语句内出现表达式的地方都可以使用子查询，子查询可以从任何表中提取数据，只要对该表有适当的访问权限即可，通过在一个子查询或者另一个子查询内嵌套子查询，可以从两个或者多个表组合信息而不必编写复杂的整个组合表然后在过滤掉多余的或者不想管的联合查询的JOIN语句，子查询可以包含联合，WHERE子句、HAVING 子句和 GROUP BY 子句

      • 语法规则：

        1. 子查询的SELECT必须用圆括弧括起来
        2. 不能包括 COMPUTE 或 FOR BROWSE 子句
        3. 如果同时指定TOP语句，则可能只包含 ORDER BY 语句 SELECT top 10 form table_name SELECT * from table_name limit 10,10
        4. 子查询最多可以嵌套32层
        5. 任何使用表达式的地方都可以使用子查询，只要他返回的是单个值
        6. 如果某个表只出现在子查询中而不出现在外部查询中那么该表的列就无法包含在输出中
    select * from A where A.列 in (select b.列 from B )
-- 子查询中没有告诉sql A表和B表有关联的情况下，B表的数据列不能出现在外部查询中

    • 子查询常用的语法格式

      1. 第一种语法格式

        • WHERE 查询表达式 [NOT] IN(子查询)

      2. 第二种语法方式

        • WHERE 查询表达式 比较运算符[ANY | ALL] (子查询)

      3. 第三种语法方式

        • WHERE [NOT] EXISTS(子查询)

    • 子查询与其他的 SELECT 语句之间的区别(除必须在括弧中出现外)

      1. SELECT 语句只能使用那些来自 FROM 子句中的表中的列，子查询不仅可以使用在该子查询的FROM 子句中的表，而且还可以使用子查询的 FROM 子句中表的任何列
      2. SELECT 语句中的子查询必须是返回单一数据列(除from子句的子查询)，根据在查询中的使用方法，(如将子查询结果用作包括子查询的 SELECT 子句中的一个数据项)，包括子查询的查询可能要求子查询返回单个值（而不是来自单列的多个值)
      3. 子查询不能有 ORDER BY 子句(因为用户看不到返回多个数据值的子查询的结果表，所以对于隐藏的中间结果排序没有什么意义)
      4. 子查询必须是由一个 SELECT 语句组成，也就是不能将多个SQL语句用 UNION 组合起来作为一个子查询 外部查询只能有一个 select 剩余的全部在括弧里面

    • SELECT 列表中的子查询（子查询是 SELECT 查询内的返回一个值的表达式，就像返回值中的单个列一样，但是，在一个表达式中，子查询必须只返回一条记录，这样的子查询被称为标量子查询(scalar subquery),也必须被封闭在括弧中）

      • 示例
    根据图书的作者，获取不同作者编写的图书中价格最高的信息
SQL：
SELECT
    tb_book_author,
    tb_author_department,
    ( SELECT MAX( book_price ) FROM tb_book WHERE tb_book_author.tb_book_author = tb_book.tb_book_author ) 
FROM
    tb_book_author;
    • 表：tb_book_author

      | tb_book_author | tb_author_department | tb_author_resume | tb_book_author_id |

    潘一PHP程序设计1
    刘一PHP程序设计2
    郭一C++应用程序开发3
    王一C++应用程序开发4
    • 表：tb_book
    tb_book_idbook_namebook_sortbook_numberbook_pricetb_book_author
    1PHP函数参考大全PHP1001-100-10289.00潘一
    2PHP示例宝典PHP1001-100-10378.00刘一
    3C++经验技术宝典C++1001-101-10179.00王一
    4SQL Server开发技术大全SQL数据库1001-102-10069.00李一
    5PHP网络编程自学手册PHP1001-100-10452.00潘一
    6Visual C++从入门到精通C++1001-101-10189.00郭一
    • SQL执行结果

      | tb_book_author | tb_author_department | (无列名) |

    潘一PHP89.00
    刘一PHP78.00
    郭一C++89.00
    王一C++79.00

    • 多列子查询(多列子查询就是返回值有多列)

      • 示例：
      • person 表：

      | id | name | sex |

    1小一女
    2小二男
    3小三女
    4小四男
    5小五女
    • job表

      | id | name | sex |

    1小二男
    2小三女
    3小四男
    SQL：
SELECT * FROM person WHERE name IN (SELECT name FROM job);
SELECT * FROM person WHERE (name,sex) IN (SELECT name,sex FROM job);

    • 使用比较运算符连接子查询

      • 在 WHERE 子句中可以使用单行比较运算符来比较某个表达式与子查询的结果，可以使用的比较运算符包括：=、>、>=、<、<=、<>、!=等这些运算符各连接一个子查询,但是返回的值只能是单个的值（子查询的列基本都要是函数）；且在使用 ALL 或者 ANY 修饰的比较运算符连接子查询时，必须保证所返回的结果集中只有单行数据，否则查询报错
    分析SQL：
SELECT cat_id,good_name FROM goods WHERE cat_id>(SELECT cat_id FROM brand WHERE name = "iPhone")

    • 在子查询中使用聚合函数

      • 聚合函数 SUM()、COUNT()、MAX()、MIN()、和 AVG()都返回的是单个值，在子查询中应用聚合函数，并将该函数返回的结果应用到WHERE 子句的查询条件中
    示例：使用 MIN()函数获取 tb_min 表中 number1 和 number2 字段的最小值
SQL: ABS()绝对值
SELECT MIN(((number1+number2)-ABS(number1-number2))/2) AS 最小数 FROM (SELECT * FROM tb_min WHERE (number1>0 AND number2>0)) AS a;
示例：使用聚合函数 AVG() 求emp员工表中员工的平均工资，并将结果作为 WHERE 子句的查询条件，通过SQL获取工资大于平均工资的员工信息。
SQL：
SELECT ename,sal,job FROM student.emp WHERE sal > (SELECT AVG(sal) FROM emp);
    陕ICP备19022109号-1