一.导入数据库
这个是在终端导入的,workbench里面不能使用source命令 不知道为啥
# 下载之后导入
CREATE DATABASE IF NOT EXISTS yiibaidb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
use yiibaidb;
source C:\Users\87671\Desktop\yiibaidb.sql;
select city,phone,country from offices;
二.SQL是什么?MySQL是什么?
SQL: SQL(发音为字母 S-Q-L或 sequel)是 Structured Query Language(结构化查询语言)的缩写。SQL是一种专门用来与数据库沟通的语言。
MySQL: MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。
本质区别:SQL是一种语言,而MySQL是一种数据库
三.select检索语句
select----from----where----group by----having----order by----limit
#检索多个列
select prod_id,prod_name,prod_price from products;
#检索所有列
select * from products;
#检索不重复的行
select vend_id from products;
select distinct vend_id from products;
#限制结果(返回前几行)
select prod_name from products;
select prod_name from products limit 5; # 前5行
select prod_name from products limit 3,4;#从第4行(索引是3)开始的4行
select prod_name from products limit 4 offset 3;#从第4行(索引是3)开始的4行
#使用完全限定的表名
select products.prod_name from products;
select products.prod_name from test.products; # products来自test数据库
CASE...END判断语句
解释:
when 之后的表达式为真,则执行then之后的命令(then 后面可以是表中的某列,比如下面的例子);否则执行下一个when;
case表达式最后返回一个值
举个例子:
例子2:
四. 筛选语句 WHERE
注意:
where子句中不能使用聚合函数, 聚合函数可以再select,having,order by之后出现
1.where 与谓词between/ like/ in / is null / is not null / exists搭配
like+通配符%:代表0个及以上的任意字符串
_ : 代表任意1个字符串
-- 1.ddd%前方一致查询
select * from SampleLike
where strcol like 'ddd%';
between
## between的结果包含临界值
select product_name, sale_price from product
where sale_price between 100 and 1000;
is null、is not null
## is null/is not null 选取某些值为Null的数据
select product_name, sale_price from product
where purchase_price is null;
in、not in
## in (or的简便用法)/not in
#笨拙用法
select product_name,purchase_price
from product
where purchase_price = 320
or purchase_price = 500
or purchase_price = 5000;
#简单用法
select product_name,purchase_price
from product
where purchase_price in (320,500,5000);
注意
-- 1.在使用in/not in的时候是无法取出null数据的,null数据还是需要is null/is not null
-- 2.in(not in)有其他谓词所没有的用法,就是可以使用子查询作为参数,比如
select product_name,sale_price from product
where product_id in (select product_id from shopproduct
where shop_name = "大阪");
2. where与and、or搭配
#and or共用时候要加上小括号指定次序
select prod_name,prod_price from products
where (vend_id=1002 or vend_id =1003) and prod_price >=10;
3.where与正则表达式搭配
待完善
where的常见错误
五. 分组语句 GROUP BY
注意:
1.除了聚集语句和常数外,select语句中每个列都必须在group by子句中给出
也就是说
SELECT 子句中只能存在以下三种 元素。
-- ● 常数 ● 聚合函数 ● GROUP BY子句中指定的列名(也就是聚合键)
错误示范
SELECT product_name, purchase_price, COUNT(*) -- 列名product_name不能包含
FROM Product GROUP BY purchase_price;
但有一种情况特殊(我也不知道为啥)和join在一起时
##使用带聚集函数的查询
select customers.cust_name,
customers.cust_id,
count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id=orders.cust_id
group by customers.cust_id;
2.groupby中不能使用列别名 order by可以用别名
3.如果分组列中有NULL值,则NULL将作为一个分组返回
4.注意groupby在where之后,order by之前
5.groupby+having过滤
where过滤行,having过滤分组;也可以这样理解:where在数据分组前过滤,having在数据分组后过滤
#看下面的语句 因为price是没有分组的 所以用where ;count(*)是分组后的所以用having
select vend_id,count(*) as num_prods from products
where prod_price >=10
group by vend_id
having count(*)>=2;
注意:
having常见错误
HAVING 子句中 能够使用的 3种要素如下所示● 常数 ● 聚合函数 ● GROUP BY子句中指定的列名(即聚合键)
错误举例:
SELECT product_type,COUNT(*) FROM Product
GROUP BY product_type
HAVING product_name = '圆珠笔';
但是呢,group by 后面的列最好用where,这样一来having只有2中选择了,更有条理
-- 2)相对于having子句更适合在where子句出现
-- 聚合键(groupby的key)既可以在having也可以在where
###但是最好还是写在where字句上,这样一来having只有常数聚合函数可以选择了
SELECT product_type, COUNT(*) FROM Product
GROUP BY product_type
HAVING product_type = '衣服';
#等价于order
SELECT product_type, COUNT(*) FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;
六. 排序语句 ORDER BY
#排序数据
select prod_name from products order by prod_name;#按照字母顺序排列
#按多个列排序 (有先后顺序)
select prod_id,prod_price,prod_name from products
order by prod_price,prod_name;#在price相同时才能按照name排序
#指定排序方向
select prod_id,prod_price,prod_name from products
order by prod_price DESC;#降序排序 ,默认是升序
select prod_id,prod_price,prod_name from products
order by prod_price DESC,prod_name; #前面的降序 后面的升序
七. 函数
-- 1.常见算术函数
1.abs_col:ABS(m)的返回值(绝对值)
2.mod_col:MOD(n,p)的返回值(n÷p的余数)
3.round_col:ROUND(m,n)的返回值(四舍五入的结果
-- 2.常见字符串函数
#1.拼接concat()
SELECT str1, str2, str3, CONCAT(str1, str2, str3)
AS str_concat FROM SampleStr;
#2.length()
#3.lower() upper()
#4.REPLACE(对象字符串,替换前的字符串,替换后的字符串)
insert(对象字符串,起始位置,替换长度,替换的后的字符串)
#5.substring(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
-- 3.常见日期函数
#1.select current_date 返回当前日期
#2.select current_time 返回当前时间
#3.select current_timestamp;返回当前时间和日期
#4.extract(日期元素 from 日期)
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
-- 4.转换函数
-- 类型转换&值的转换
-- 类型转换
#1.CAST(转换前的值 AS 想要转换的数据类型)
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
SELECT CAST('2009-12-14' AS DATE) AS date_col;
-- 值的转换
#1. 将null转换其他值
-- COALESCE(数据1,数据 2,数据 3……)
-- 该函数会返回可变参数 A 中左侧开 始第1个不是 NULL的值
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
SELECT COALESCE(str2, 'NULL') FROM SampleStr;-- 将null变成了'null'
select str2 from samplestr;
https://www.cnblogs.com/geaozhang/p/6739303.html 字符串函数详解
八 SQL注释
单行用--
多行用/* 注释内容*/
九 代码规范
https://zhuanlan.zhihu.com/p/27466166
十.作业
作业要求
第一题代码:
-- 创建表
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255)
);
-- 插入数据
INSERT INTO email VALUES('1','a@b.com');
INSERT INTO email VALUES('2','c@d.com');
INSERT INTO email VALUES('3','a@b.com');
select Email from email where ID in
(select ID from email group by email having count(*) = 1) ;
第二题代码:
-- 创建表
CREATE TABLE World (
name VARCHAR(50) NOT NULL,
continent VARCHAR(50) NOT NULL,
area INT NOT NULL,
population INT NOT NULL,
gdp INT NOT NULL
);
-- 插入数据
INSERT INTO World VALUES( 'Afghanistan', 'Asia',652230,25500100,20343000);
INSERT INTO World VALUES( 'Albania', 'Europe' ,28748,2831741,12960000);
INSERT INTO World VALUES( 'Algeria', 'Africa' ,2381741,37100000,188681000);
INSERT INTO World VALUES( 'Andorra' , 'Europe' ,468,78115,3712000);
INSERT INTO World VALUES( 'Angola' , 'Africa' ,1246700,20609294,100990000);
select name,population,area from world
where area > 3000000 or (population >25000000 and gdp > 20000000);