[大数据]sql学习笔记

1.查询

1.select * from user #查所有字段
2.select name from user # 查指定字段

2.查询并去重

#查询姓名并去重
select distinct name from user

3.带条件的查询

#查询姓名为Tom的信息
select * from user where name="Tom"

4.多条件的查询

#查询姓名为Tom且年龄大于18
select * from user where name="Tom" and age>18  
# and 满足两个条件
# or 满足任一条件即可

5.查询并排序

 select * from user order by age
 # desc 降序排列  从大到小
 # asc 升序排列  从小到大

6.添加数据

insert into user (name,age,city) value("Jok",23,"上海")

7.修改数据

#注意 update不加where限制条件会默认修改表中所有数据
#将Jok的年龄修改为20
update user set age=20 where name="Jok"

8.删除数据

#注意 delete不加where限制条件会默认删除表中所有数据

delete from user where name="Jok"

9.返回指定条数数据

#返回5条数据
select * from user limit 5
#获取前5条数据
select * from user order by age desc limit 5

10.模糊查询

 #以李结尾的姓名
 select * from user where name like "%李"
#以李开始的姓名
 select * from user where name like "李%"
 #包含李的姓名
 select * from user where name like "%李%"
 #姓李且只有两个字的姓名
select * from user where name like "李_"
 #姓李且只有三个字的姓名
select * from user where name like "李__"
 #有三个字的姓名且以张结尾
select * from user where name like "__张"

11.where指定多个值查询

#查询姓名Tom和Jok信息
select * from user where name in ("Tom","Jok")

12.选取介于两个值之间的数据范围内的值

#返回大于18小于25的人
select * from user where age between 18 and 25

13.查询并设置别名

#将name命名为姓名 表名user设置为用户
select name as "姓名" from user as "用户"

select name "姓名" from user "用户"

14.关联查询

#查询两张表中共有的数据
select a.name,b.age from user a ,user2 b where a.name=b.name 
#两张表中存在至少一个匹配时返回值
select a.name,b,age from user inner join user2 on a.name=b.name 
#左查询 以左边表为主，匹配右边表 如果右边表存在对应数据，返回对应值，不存在返回Null
select a.name,b,age from user left join user2 on a.name=b.name 

#右查询 以右边表为主，匹配左边表 如果左边表存在对应数据，返回对应值，不存在返回Null
select a.name,b,age from user left join user2 on a.name=b.name 
#全连接 左右两张表存在任一数据中则返回行
select a.name,b,age from user full outer join user2 on a.name=b.name 

15.合并两张表 UNION

#UNION 内部的每个 SELECT 语句
#必须拥有相同数量的列。
#列也必须拥有相似的数据类型。
#同时，每个 SELECT 语句中的列的顺序必须相同
# union 两张表中共有的数据不允许重复值
select name,age,city from user
union
select name,age,city from user2

# union all 两张表中共有的数据允许重复值
select name,age,city from user
union all
select name,age,city from user2

16.复制数据

select into from 要求目标表不存在，因为在插入时会自动创建；
insert into select from 要求目标表存在。

#复制所有数据
select * into user from user2
#复制指定字段
select name,age into user from user2
#只复制符合条件的数据
select * into user from user2 where age>20

#一个表中复制所有的列插入到另一个已存在的表中
insert into user2 select * from user
#只复制希望的列插入到另一个已存在的表
insert into user2(name,age,city)
select name,age,city from user

二,数据库的约束

17.创建数据库/表

创建数据库
create database my_data
创建数据表
creat table my_table
(
name varchar(100),
age int(10),
city varchar(100)
);

2.创建带约束的数据表

create table my_table
(
id int(25) not null,
name varchar(100) unique,
age int(10),
city varchar(100)
);

not null - 指示某列不能存储 null值。
unique - 保证某列的每行必须有唯一的值。
primary key - not null 和 unique的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
foreign key- 保证一个表中的数据匹配另一个表中的值的参照完整性。
check - 保证列中的值符合指定的条件。
default- 规定没有给列赋值时的默认值。

3.添加非空约束

#在创建表格时，添加
create table my_table
(
id int(25) not null,
name varchar(100) not null,
age int(10),
city varchar(100)
);

#为已有字段添加
alter table my_table modify age int not null

#删除约束
alter table my_table modify age int null

4.添加唯一约束

#为姓名添加非空和唯一约束
create table my_table
(
id int(25) not null,
name varchar(100) not null unique,
age int(10),
city varchar(100)
);

#为已有字段添加唯一约束
ALTER TABLE Persons
ADD UNIQUE (P_Id)
#为多个字段添加唯一约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

#删除唯一约束

ALTER TABLE Persons
DROP INDEX uc_PersonID

5.主键约束 PRIMARY KEY 约束

#创建带有主键约束的表格
create table my_table
(
id int(25) not null,
name varchar(100) not null unique,
age int(10),
city varchar(100),
primary key (p_id)
);

#添加主键约束
alter table user add primary key (p_id)
删除主键约束
alter table user drop primary key

6.外键约束 foreign 约束

#创建带有外键约束的表
create table order (
o_id int not null,
orderNo int not null,
p_id int,
primary key (o_id),
foreign (p_id) references user(p_id)
)
#添加外键约束
alter table order add foreign key (p_id)
references user(p_id)
#删除外键约束
alter table order drop foreign key fk_porder(外键名称)

7.CHECK 约束用于限制列中的值的范围

#创建check约束
create table order (
o_id int not null,
orderNo int not null,
check (o_id>0)
)
#添加check约束
alter table user add check(p_id>0)
#删除check约束
alter table user drop check chk_user

8.默认约束 DEFAULT 约束用于向列中插入默认值

#创建默认约束
create table order (
o_id int not null,
orderNo int not null,
city varchar(255) default  "北京"
)
#添加默认约束
alter table user alter city set default "上海"

#删除默认约束
alter table user alter city drop default 5

9.创建索引 create index 在表中创建索引。

#创建单个索引
create index u_index on user (u_id)
 #创建多个索引
create index u_index on user (u_id,u_name) 

10.撤销索引

alter table user drop index u_id

11.删除数据库/表

#删除数据库
drop database user
#删除数据表
dorp table user
#删除表中数据
truncate table user

12.alter table 在已有的表中添加、删除或修改列

#添加列
alter table user add role varchar(60)
#删除列
alter table user drop column role
#修改列的数据类型
alter table user alter column role char(50)

13.设置字段自动增长

create table user 
(
id int not null auto increment,
name varchar(240) not null,
city varchar(255)
)

14.sql视图

#简单视图
create view user_order as 
(select name,city from user where age>18)
#复杂视图
create view user_order as 
(sleect a.name,a.city,b.orderNum,b.orderPrice from user a,order b where a.u_id=b.u_id)
#更新视图
create or replace view user_order as (
sleect a.name,a.city,b.orderNum,b.orderPrice from user a,order b where a.u_id=b.u_id
)
#删除视图
drop view user_order

15.日期函数

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

16.NULL 值

无法比较 NULL 和 0；它们是不等价的

表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
#查询空值
select * from user where city is null
#Mysql
select * from user where isnull(city)
#查询非空值
select * from user where city is not null
#MySql
select * from user where not isnull(city)

SQL 函数

1.avg() 函数 平均数

#年龄的平均值
select avg(age) from user

2.count() 函数 计数函数

#统计有多少个用户
select count(*) from user
#统计并去重
select count(distinct name) from user

3.first()函数

FIRST() 函数返回指定的列中第一个记录的值
select first(name) from user
#MySql
select * from user limit 1;

4.last()函数

FIRST() 函数返回指定的列中第一个记录的值
select last(name) from user order by name limit 1
#MySql
#返回最后一条数据
select * from user limit 1;

5.max()函数 最大值

#返回年龄最大的
select max(age) from user

6.min()函数 最小值

#返回年龄最小的
select min(age) from user

7.sum() 函数 求和

#求年龄之和
select sum(age) from user

8.group by 分组

#查看用户在那些城市
select * from user group by city

9.having 筛选

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用

select count(b.o_id),a.name from user group by city having city!="深圳"; 

10.exists 运算符 判断记录是否存在

#判断是北京用户
select * from user where exists(select city from user where city ="北京")；

11.字段大小写 （大写ucase|小写lcase）

#大写
select ucase(name) from user
#小写
select lcase(name) from user

12.mid()函数 从文本中取值（类似与字符串截取）

select mid(addres,1,3) from user

13.len()函数 获取字符串的长度

#获取姓名的长度
select name,len(name) from user

14.round()函数 将数值字段舍入为指定的小数位数

#获取商品的总价（保留小数点后两位）
select order_name,price,round(count_price,2) from user
# 
select round(2.626,2)

15.now() 函数 返回系统当前时间

select * from user where insert_time=now()

16.format()函数 格式化字符串

select name,date_format(now(),"%Y-%m-%d") as date from user

菜鸟sql教程