SQL篇—MySQL(一)

在互联网行业从事数据分析,要学一些必备的技能,比如SQL/HIVE。因此我将之前学习的SQL知识资源进行统一整理一下,以备不时之需。

(一)MySQL初识

1.如何系统学习MySQL
知乎帖子:https://www.zhihu.com/question/21760988
不准备做DBA的可能不需要全看,数据分析其实只需要会使用select查询函数,但涉及复杂的取数逻辑时,如何高效、准确地的从数据库中获得数据,也是一个技能。推荐基础入门书籍:《MySQL必知必会》

2.MySQL安装及使用

(二)MySQL学习笔记

需要注意的是,在不同数据库版本里,SQL语法存在一些差异。

一千行MySQL学习笔记:http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html
MySQL基础入门:https://github.com/jaywcjlove/mysql-tutorial/blob/1fb941c78566efdc2cc95ac68681f2b9fa3d2f91/21-minutes-MySQL-basic-entry.md
子查询与函数嵌套:https://juejin.im/entry/5a28afc1f265da431d3c9144
50道SQL练习题:https://zhuanlan.zhihu.com/p/32137597
牛客网SQL实战:https://www.nowcoder.com/ta/sql
leetcode题库:https://leetcode-cn.com/problemset/database/

其他:
1.对大小写不敏感;
注意:所有的数据库名,表名,表字段都是区分大小写的
分号是在数据库系统中分隔每条 SQL 语句的标准方法,这样就可以在对服务器的相同请求中执行一条以上的 SQL
语句
养成用TABLE.COLUMN方式引用列的习惯,这样可以在两个表具有同名的列的时候避免混淆
SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更(改变)数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引(搜索键)
DROP INDEX - 删除索引

导入sql文件:
exit/quit——在CMD中输入MySQL -h localhost -u root -p mydb2 < e:\MySQL\mydb2.sql
导出sql文件:
MySQLdump -h localhost -u root -p mydb >e:\MySQL\mydb.sql

可以修改MySQL配置文件位置my.ini文件,自己写个ini文件再指定MySQL位置就行了
https://www.cnblogs.com/wawahaha/p/4373493.html

1.mysql服务的启动和停止编辑
net stop mysql
net start mysql
2、登陆mysql编辑
登陆终端(windows cmd或者linux 命令行下)且已经安装mysql-client软件;
语法如下: mysql -h 主机ip -u 用户名 -p 用户密码 主机名默认localhost
例如:mysql -h 192.168.31.194 -u root -p 123456
即可登陆mysql管理
  键入命令:mysql -u root -p, 回车后提示你输入密码,输入:12345,然后回车即可进入到mysql中了,mysql的提示符是:

mysql -u root -p
mysql>

1.创建/删除数据库(对于表的操作,需首先进入库,use+库名)
-- 创建一个名为 samp_db 的数据库,数据库字符编码指定为 gbk
create database samp_db character set gbk;
drop database samp_db; -- 删除 库名为samp_db的库
show databases; -- 显示数据库列表。
use samp_db; -- 选择创建的数据库samp_db
show tables; -- 显示samp_db下面所有的表名字
describe 表名; -- 显示数据表的结构
delete from 表名; -- 清空表中记录
创建数据库
mysql> CREATE DATABASE if not exists test;
特定权限创建
$ mysqladmin -uroot -p create test
Enter password:***
删除数据库
DROP DATABASE test2;
mysql> drop database if exists test2;

2.创建表
create table 表名称(列声明);
not null声明往表中添加数据时,必须提供对应的值,如主键必须为NOT NULL;
auto_increment自动为该列分配键值,确保在插入新的记录时该列被自动赋予一个唯一的值
varchar(32) 表明该列可以容纳不多于150个字符
CREATE TABLE user_accounts (
id int(100) unsigned NOT NULL AUTO_INCREMENT primary key,
password varchar(32) NOT NULL DEFAULT '' COMMENT '用户密码',
reset_password tinyint(32) NOT NULL DEFAULT 0 COMMENT '用户类型:0-不需要重置密码;1-需要重置密码',
mobile varchar(20) NOT NULL DEFAULT '' COMMENT '手机',
create_at timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
update_at timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
-- 创建唯一索引,不允许重复
UNIQUE INDEX idx_user_mobile(mobile)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
COMMENT='用户表信息';

CREATE TABLE purchases(
purchase_id int NOT NULL AUTO_INCREMENT,
title_id int(11) NOT NULL,
PRIMARY KEY (purchase_id));

3.增删改查
3.1 select
语法:SELECT 列名称 FROM 表名称
语法:SELECT * FROM 表名称
-- 表abc 两个 id 表abc中不包含 字段a=b 的 查询出来,只显示id
SELECT s.id from station s WHERE id in (13,14) and user_id not in (4);
-- 从表 Persons 选取 LastName 列的数据
SELECT LastName FROM Persons
-- 结果集中会自动去重复数据
SELECT DISTINCT Company FROM Orders
-- 表 Persons 字段 Id_P ,等于 Orders 字段 Id_P 的值,
-- 结果集显示 Persons表的 LastName、FirstName字段,Orders表的OrderNo字段
SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.Id_P = o.Id_P

正则表达式(regular expression) https://zh.wikipedia.org/wiki/%E6%AD%A3%E5%88%99%E8%A1%A8%E8%BE%BE%E5%BC%8F
MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
^ 字符开始位置 如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 '\n' 或 '\r' 之后的位置。
字符串结束位置 设置了RegExp 对象的 Multiline 属性, 也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符 要匹配包括 '\n' 在内的任何字符,请使用象 '[.\n]' 的模式
[…] 字符集合,匹配所包含的任意一个字符。 例如, '[abc]' 可以匹配 "plain" 中的 'a'。
[^…] 负值字符集合,匹配未包含的任意字符。 例如, '[^abc]' 可以匹配 "plain" 中的'p'。
P1|P2|p3 匹配 p1 或 p2 或 p3。 例如,'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。

  • 匹配前面的子表达式零次或多次。 例如,zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
  • 匹配前面的子表达式一次或多次。 例如,'zo+' 能匹配 "zo" 以及 "zoo",但不能匹配 "z"。+ 等价于 {1,}。
    {n} n 是一个非负整数。匹配确定的 n 次。 例如,'o{2}' 不能匹配 "Bob" 中的 'o',但是能匹配 "food" 中的两个 o。
    {n,m} m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。

查找name字段中以'st'为开头的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok'; 查找name字段中包含'mar'字符串的所有数据: mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar'; 查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据: mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok';

源文档 http://www.runoob.com/mysql/mysql-regexp.html

like字句 模糊匹配%
WHERE 获取数据的条件
获取 author 字段含有 "COM" 字符的所有记录,就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *
%通配符 两边都使用了百分号,检查字母前后的所有内容,也可以只使用一个百分号 匹配任意字符
下划线也是通配符 匹配任意一个字符
select * from authors where author like "Aaron Webe
" 返回所有作者名是以”Aaron Webe"开始,后面跟任意一个字符的记录
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM%';

3.2 insert into
INSERT INTO 语句用于向表格中插入新的行。
语法:INSERT INTO 表名称 VALUES (值1, 值2,....) 若没有指定列则数值的顺序应该要和定义表时的顺序一致
语法:INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)
引号括起来:字符串+日期时间 不能引号:数字数值+函数+NULL
即时有的列没有指定数值,添加数据时也应该指定所有的列,为它留出位置
-- 向表 Persons 插入一条字段 LastName = Wilson 字段 Address = shanghai
INSERT INTO Persons (LastName, Address) VALUES ('JSLite', 'shanghai');
-- 向表 meeting 插入 字段 a=1 和字段 b=2
INSERT INTO meeting SET a=1,b=2;
--
-- SQL实现将一个表的数据插入到另外一个表的代码
-- 如果只希望导入指定字段,可以用这种方法:
-- INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.id=1;

3.3 update 修改表中的数据 以新替旧——同select只是多了SET
语法:UPDATE ——表名称 ——SET ——列名称 = 新值 ——WHERE ——列名称 = 某值

  • update语句设置字段值为另一个结果取出来的字段
    update user set name = (select name from user1 where user1 .id = 1 )
    where id = (select id from user2 where user2 .name='小苏');
    -- 更新表 orders 中 id=1 的那一行数据更新它的 title 字段
    UPDATE orders set title='这里是标题' WHERE id=1;

3.4 alter 修改表的结构 只有ALTER要加table,drop也要加table
添加列
语法:ALTER TABLE 表名 ADD 列名 列数据类型 [after 插入位置];
-- 在表students的最后追加列 address:
alter table students add address char(60);
-- 在名为 age 的列后插入列 birthday:
alter table students add birthday date after age;
-- 在名为 number_people 的列后插入列 weeks:
alter table students add column weeks varchar(5) not null default "" after number_people;

修改列
语法:alter table 表名 change 列旧名称 列新名称 新数据类型;
-- 将表 tel 列改名为 telphone:
alter table students change tel telphone char(13) default "-";
-- 将 name 列的数据类型改为 char(16):
alter table students change name name char(16) not null;
-- 修改 COMMENT 前面必须得有类型属性
alter table students change name name char(16) COMMENT '这里是名字';
-- 修改列属性的时候 建议使用modify,不需要重建表
-- change用于修改列名字,这个需要重建表
alter table meeting modify weeks varchar(20) NOT NULL DEFAULT "" COMMENT "开放日期 周一到周日:0~6,间隔用英文逗号隔开";

删除列
语法:alter table 表名 drop 列名称;
-- 删除表students中的 birthday 列:
alter table students drop birthday;

重命名表
语法:alter table 表名 rename 新表名;
-- 重命名 students 表为 workmates:
alter table students rename workmates;

删除,添加或修改表字段
ALTER TABLE testalter_tbl DROP
ALTER TABLE testalter_tbl ADD i INT; 定义数据类型 (first ,after c)
修改字段类型及名称
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j INT;
修改字段时,你可以指定是否包含只或者是否设置默认值。
mysql> ALTER TABLE testalter_tbl MODIFY j INT NOT NULL DEFAULT 100;
修改字段默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
修改存储引擎:修改为myisam
alter table tableName engine=myisam;
删除外键约束:keyName是外键别名
alter table tableName drop foreign key keyName;
修改字段的相对位置:这里name1为想要修改的字段,type1为该字段原来类型,first和after二选一,这应该显而易见,first放在第一位,after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;

源文档 http://www.runoob.com/mysql/mysql-alter.html

3.5 delete
DELETE 语句用于删除表中的行。
语法:DELETE FROM 表名称 WHERE 列名称 = 值
-- 在不删除table_name表的情况下删除所有的行,清空表。
DELETE FROM table_name
-- 或者
DELETE * FROM table_name
-- 删除 Person表字段 LastName = 'Wilson'
DELETE FROM Person WHERE LastName = 'Wilson'
-- 删除 表meeting id 为2和3的两条数据
DELETE from meeting where id in (2,3);

清空表数据
方法一:delete from 表名; 方法二:truncate from "表名";
DELETE:1. DML语言;2. 可以回退;3. 可以有条件的删除;
TRUNCATE:1. DDL语言;2. 无法回退;3. 默认所有的表内容都删除;4. 删除速度比delete快。
-- 清空表为 workmates 里面的数据,不删除表。
delete from workmates;
-- 删除workmates表中的所有数据,且无法恢复
truncate from workmates;

删除整张表
语法:drop table 表名;
-- 删除 workmates 表:
drop table workmates;

删除整个数据库
语法:drop database 数据库名;
-- 删除 samp_db 数据库:
drop database samp_db;

3.6 其他语法
WHERE 子句用于规定选择的标准。
语法:SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
-- 从表 Persons 中选出 Year 字段大于 1965 的数据
SELECT * FROM Persons WHERE Year>1965

AND - 如果第一个条件和第二个条件都成立;
OR - 如果第一个条件和第二个条件中只要有一个成立;
-- 删除 meeting 表字段
-- id=2 并且 user_id=5 的数据 和
-- id=3 并且 user_id=6 的数据
DELETE from meeting where id in (2,3) and user_id in (5,6);
-- 使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人:
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';
-- 使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人:
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

ODER BY语句默认按照升序对记录进行排序。
ORDER BY - 语句用于根据指定的列对结果集进行排序。
DESC - 按照降序对记录进行排序。
ASC - 按照顺序对记录进行排序。
-- Company在表Orders中为字母,则会以字母顺序显示公司名称
SELECT Company, OrderNumber FROM Orders ORDER BY Company
-- 后面跟上 DESC 则为降序显示
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
-- Company以降序显示公司名称,并OrderNumber以顺序显示
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

IN - 操作符允许我们在 WHERE 子句中规定多个值。
IN - 操作符用来指定范围,范围中的每一条,都进行匹配。IN取值规律,由逗号分割,全部放置括号中。 语法:SELECT "字段名"FROM "表格名"WHERE "字段名" IN ('值一', '值二', ...);
-- 从表 Persons 选取 字段 LastName 等于 Adams、Carter
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

NOT - 操作符总是与其他操作符一起使用,用在要过滤的前面。
SELECT vend_id, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

as - 可理解为:用作、当成,作为;别名
语法:select column_1 as 列1,column_2 as 列2 from table as 表
SELECT * FROM Employee AS emp
-- 这句意思是查找所有Employee 表里面的数据,并把Employee表格命名为 emp。
-- 当你命名一个表之后,你可以在下面用 emp 代替 Employee.
-- 例如 SELECT * FROM emp.
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
-- 列出表 Orders 字段 OrderPrice 列最大值,
-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice
-- 显示表 users_profile 中的 name 列
SELECT t.name from (SELECT * from users_profile a) AS t;
-- 表 user_accounts 命名别名 ua,表 users_profile 命名别名 up
-- 满足条件 表 user_accounts 字段 id 等于 表 users_profile 字段 user_id
-- 结果集只显示mobile、name两列
SELECT ua.mobile,up.name FROM user_accounts as ua INNER JOIN users_profile as up ON ua.id = up.user_id;

join用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
JOIN: 如果表中有至少一个匹配,则返回行
INNER JOIN:在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配,就返回行

NULL
= 和 != 运算符是不起作用的
必须使用 IS NULL 和 IS NOT NULL

4.sql函数
COUNT 让我们能够数出在表格中有多少笔资料被选出来。
语法:SELECT COUNT("字段名") FROM "表格名";
-- 表 Store_Information 有几笔 store_name 栏不是空白的资料。
-- "IS NOT NULL" 是 "这个栏位不是空白" 的意思。
SELECT COUNT (Store_Name) FROM Store_Information WHERE Store_Name IS NOT NULL;
-- 获取 Persons 表的总数
SELECT COUNT(1) AS totals FROM Persons;
-- 获取表 station 字段 user_id 相同的总数
select user_id, count(*) as totals from station group by user_id;

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
语法:SELECT MAX("字段名") FROM "表格名"
-- 列出表 Orders 字段 OrderPrice 列最大值,
-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

8.MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country(只有不同的值):
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country(也有重复的值):
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

9.分组
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。其实Group by name可以实现统计
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name【 WITH ROLLUP】;

select coalesce(a,b,c);
如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)

5.添加索引
普通索引(INDEX)
语法:ALTER TABLE 表名字 ADD INDEX 索引名字 ( 字段名字 )
-- –直接创建索引
CREATE INDEX index_user ON user(title)
-- –修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
-- 给 user 表中的 name字段 添加普通索引(INDEX)
ALTER TABLE table ADD INDEX index_name (name)
-- –创建表的时候同时创建索引
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
INDEX index_name (title(length))
)
-- –删除索引
DROP INDEX index_name ON table

主键索引
语法:ALTER TABLE 表名字 ADD PRIMARY KEY ( 字段名字 )
-- 给 user 表中的 id字段 添加主键索引(PRIMARY key)
ALTER TABLE user ADD PRIMARY key (id);

唯一索引
语法:ALTER TABLE 表名字 ADD UNIQUE (字段名字)
-- 给 user 表中的 creattime 字段添加唯一索引(UNIQUE)
ALTER TABLE user ADD UNIQUE (creattime);

全文索引(FULLTEXT)
语法:ALTER TABLE 表名字 ADD FULLTEXT (字段名字)
-- 给 user 表中的 description 字段添加全文索引(FULLTEXT)
ALTER TABLE user ADD FULLTEXT (description);

添加多列索引
语法: ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3)
-- 给 user 表中的 name、city、age 字段添加名字为name_city_age的普通索引(INDEX)
ALTER TABLE user ADD INDEX name_city_age (name(10),city,age);

建立索引的时机
在WHERE和JOIN中出现的列需要建立索引,但也不完全如此:
MySQL只对<,<=,=,>,>=,BETWEEN,IN使用索引
某些时候的LIKE也会使用索引。
在LIKE以通配符%和_开头作查询时,MySQL不会使用索引。
-- 此时就需要对city和age建立索引,
-- 由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。
SELECT t.Name
FROM mytable t LEFT JOIN mytable m ON t.Name=m.username
WHERE m.age=20 AND m.city='上海';
SELECT * FROM mytable WHERE username like'admin%'; -- 而下句就不会使用:
SELECT * FROM mytable WHEREt Name like'%admin'; -- 因此,在使用LIKE时应注意以上的区别。

键:独一无二
外键:通过包括另外一张表的键形成的表之间的连接被称作外键
关系:一一, 一多,多多
规范化:范式
第一范式:无重复列,所有列只能包含一个值——同一行内的冗余
第二范式:

5.查询
order by col desc/asc
where: <>表示不等于 between a and b is(not) null判断非空,控制不满足任何查询比较条件,结果都为false
AND/OR: null and false:false null or true:true AND运算符的运算级别高于OR运算符
select sal from teacher where dname='计算机' or dname='生物' and sal>1000 order by sal
计算机所有老师和生物系中工资大于1000的教师的相关信息
建议使用括号来实现需要的执行顺序
IN: 满足多个条件中的一个, not in
NOT: 求反 null的反还是null not dname = '计算机' not between A and B
LIKE:模糊查询 通配符:%, -, [] 针对的是CHAR,VARCHAR和TEXT类型
‘%计算机'以计算机结尾 开始 '计算机%' 中间 '%计算机%'
-表示一个任意字符的匹配,--两个,需确定查询的字符串的个数才能使用 ‘------’得到小于等于6个字的字符串
[] 指定一系列的字符,满足其一且位置为通配符位置
‘[计生]%’以计或生开头 []内字符前加^表示否定 ‘[^计生]%’= not cname like‘[计生]%’
ESCAPE:定义转义符,告诉DBMS紧跟在准义字符后的字符看作是实际值,而不是通配符
LIKE ‘%M%' ESCAPE 'M' 定义了转义字符M,将第二个百分号看做是实际值,查询所有以%结尾的字符串

7.连接符,数值运算和函数
连接符:+或者|| mysql不支持连接符,CONCAT: select concat(tname, '(',dname,')') 连接的列有相同或者相近的数据类型
select tname + '('+dname+')' AS info, age from teacher where子句不可使用别名
select tname + cast(sal as varchar(5)), age from teacher cast转换数据类型
SELECT执行顺序:from > where > group by > having > select > order by
数值运算: + - * / %余
CASE WHEN :
SELECT cname,ctime,credit=
CASE
WHEN ctime >= 40 THEN 5
WHEN ctime >=30 THEN 4
WHEN ctime >=20 THEN 3
ELSE 2
END
FROM course
函数:截取字符串:substring() 日期转换convert 截取当前日期curdate() 不同数据库函数不同
SQL SERVER: 字符转换 UPPER/LOWER SELECT UPPER(bookname) as book from bookitem
去空格 LTRIM/RTRIM 字符串前面/后面的空格 RTRIM(tname)
取字符串 LEFT/RIGHT/SUBSTRING LEFT(bookname,6)从左边开始取6位
字符串比较 CHARINDEX查询字符串在目标中第一次出现的位置 CHARINDEX('boy', bookname) as position
字符串操作 REPLACE替换 REPLACE (bookname, 'Procing', 'Pro.') AS replace
四舍五入 ROUND round(price, 0) as round_price
日期函数: DAY/MONTH/YEAR 返回日期、月份、年份 month(ctest) 6
DATENAME() 以字符串的形式返回日期指定部分 DATENAME(month,ctest) june
DATEPART()以整数值形式返回日期指定部分 6
covert 可以实现转换日期,时间类型

8.聚合分析与分组
聚合分析:对表中一列或者多列数据的统计分析
聚合函数:SUM MAX MIN AVG COUNT
聚合函数是对列中的一系列数据进行处理,返回单个统计值;前面的函数是对列中的单个数据进行处理
1.SELECT SUM() FROM TABLE WHERE …
只有count()计算NULL值
2.COUNT()
SELECT Count(cast( tmo as varchar(5)) + tname) as t_noname from teacher 多列计数
查询两列的行数,由于数据类型不一致,要用CAST表达式将他们转换为相同的数据类型
Count()函数只对参数不是NULL的行计数
Count(
)返回表中的总行数
3.MAX/MIN()
数值,字符串,日期
返回年龄最大的教师的教工号,姓名,性别
错误:SELECT tname, dname, tsex, sal, MAX(age) FROM teacher
聚合函数处理的是数据组,本例中MAX()将整个teacher表看成一组,而其他数据没有进行任何分组,SELECT没有任何逻辑意义
SELECT tname, dname, tsex, sal, age FROM teacher WHERE age=MAX(age) 同样错误
正确:SELECT tname, dname, tsex, sal,age FROM teacher WHERE age=(SELECT MAX(age) FROM teacher)
4.AVG()
数值型, 总sum除以总行数时忽略NULL的行
同MAX(),不能直接作用于WHERE字句,必须以子查询的形式使用
SELECT *
FEOM teacher
WHERE age>= (SELECT AVG(age) FROM teacher)
ORDER BY age
查询所有年龄高于平均年龄的教师的信息
5.重值
SELECT AVG([all / distinct] column_name) FROM table_name
SELECT COUNT(DISTINCT sal) AS distinct_count FROM teacher

组合查询
分组聚合,将查询对象按一定条件分组,再对每一个组进行聚合分析
1.Group by
Group by是根据所选列的数据进行分组,将该列具有相同值的行化为一组
归纳类型,汇总数据
SELECT tsex, avg(sal) as avg_sal FROM teacher GROUP BY sex; 同时得到男教师和女教师的平均工资
SELECT column, sum(column) FROM table WHERE COLUMN=… GROUP BY column2
根据column2进行分组,column2只能来自于table, 不能根据实际值,聚合函数或者其他表达式计算的值进行分组
2.ROLLIP运算符和CUBE运算符
在group by子句中使用,扩展查询结果,计算分组后的每个组的组合信息
SELECT DNAME, TSEX, COUNT() AS TOTAL_NUM FROM teacher
GROUP BY dname, sex WITH ROLLUP/CUBE
ORDER BY dname
查询各系男女教师数量+统计各系(DNAME)教师总人数----各系增加了一行汇总人数且总人数增加一行
而cube,包含多列组合的统计结果,包含了整表的统计结果和各单列的统计结果(分别对性别进行的统计,和各系的统计)
3.HAVING子句
通常与GROUP BY子句同时使用,可以在分组中删除一些不满足条件的行组
SELECT dname, count(
) AS num_teacher FROM teacher
GROUP BY dname
HAVING count(*) >=2
Teacher表中至少有两位教师的系及教室人数
HAVING与where的不同之处是,HAVING字句与组有关,而不是单个的行
HAVING DNAME=”生物” 错误语句
WHERE字句仅可以接受来自FROM的输入,而having字句可以接受来自GROUP BY子句,where子句,from子句
WHERE子句在分组之前过滤数据,而HAVING子句则过滤分组后的数据,可以联合使用

顺序:
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
SELECT dname, count(tsex) AS num_girl
FROM teacher
WHERE tsex=’女’
GROUP BY dname
HAVING count(tsex)>=2
ORDER BY num_girl
从TEACHER表中查询至少有两名女教师的系及拥有的女教师数量,并按照女教师的数量升序的顺序排列结果

9.多表查询 JOIN
WHERE: 二表连接: SELECT name, test FROM teacher, course WHERE teacher.cno = course.cno
多表连接: and
JOIN: select colum from table join_type table on(连接条件) 自然连接,内连接,外连接,交叉连接
自连接:表与其自身进行连接
SELECT DISTINCT xuehao from student where mark<60
SELECT sname, dname,cno,mark from student where xuehao in('123','234','554') order by sname
查询成绩中存在不及格课程的学生的姓名,所在系,所有课程和成绩---------首先查成绩有不及格的学号,再根据学号查所有课程
SELECT DISTINCT S1.sname, S1.dname, S1.cno, S1.mark 否则有重复
FROM student as S1, student as s2
WHERE S1.xuehao = S2.xuehao
AND S2.mark < 60
OEDER BY S1.sname
自然连接:自动判断具有相同名称的列,自动进行匹配,不能人为指定列进行匹配
SELECT SNAME,DNAME,CNO,TNAME FROM student NATURAL JOIN teacher
等价于
SELECT SNAME teacher.dname, teacher.cno, tname from student teacher where student.dname=teacher.dname AND student.cno=teacher.cno
内连接:返回结果是两个表中所有相匹配的数据
等值连接
SELECT sname, s.dname,s.dno, tname FROM student as s INNER JOIN teacher as t ON s.cno=t.cno ORDER BY sname
等价于 where S.CNO=T.CNO
不等连接:> , <, <>, >=, <=, !>, !<
SELECT sname, s.dname,s.dno, tname FROM student as s
INNER JOIN teacher as t
ON s.dname <> t.dname
AND s.cno=t.cno
INNER JOIN COURSE as c
ON t.cno=c.cno
ORDER BY sname 非本系老师开课
外连接:不仅包括符合连接条件的行,也包括左/右/全部表中的所有数据行
LEFT JOIN: 内连接+ 左边表未匹配的行 缺少的右边表的属性值用NULL表示 等价于where子句中的“=”
RIGHT JOIN: 内 + 右 “=

FULL OUTER JOIN :内+左+右
交叉连接:CROSS JOIN
SELECT * FROM table1, table2 = SELECT * FROM table1 CROSS JOIN table2 等到m*n行,a+b列的新数据表
生成的是两个表的笛卡尔积,所以不能使用ON关键字,只能在WHERE子句中定义搜索条件
UNION 与UNION JOIN:对集合进行关系并运算 集合并
SELECT sno FROM teacher where cno=1 UNION SELECT * FROM teacher where cno=2 等价,自动去除重复元组
SELECT DISTINCT sno FROM teacher where cno=1 OR cno=2
UNION只要求列的类型匹配,而对应列的列名可以不同(sname, tname)
A UNION B UNION C 多表并