SQL学习笔记

SQL学习笔记
是什么？
结构化的查询语言Structured Query Language
为什么？
访问和处理数据系统中的数据
怎么做？

一、数据操作语言
增删改查
查
1.distinct查字段中不同的值：
select distinct col1
from mytable;

2. where有条件的从表中选取数据:
   select col1
   from mytable
   where col1 运算符 值;
   特别点的运算符：不等于!=/<>; between...and/or...; like
   另一个点：值如果是文本类型要单引号，数值类型就不加。
3. 1 like在where中实现模糊查询
   ①以a/A开头：
   select *
   from mytable
   where col2 like ‘a%’;

②以...结尾：
select *
from mytable
where col2 like ‘%中’；

③包含xxx：
select *
from mytable
where col2 like ’%b%’;

③不包含xxx:
select *
from mytable
where col2 not like ‘%b%’;

2.1.1 通配符必须配合like使用

①查第二个字符是b后面为随意的字符

②不知道为啥，mysql5.7不能使用

2.2 in在where中规定多个值：
select *
from mytable
where col2 in (‘value1’,’value2’,....);
注意：因为是值，所以是要具体的，文本值必须加单引号！

2.3 between在where中，选取介于俩个数之间的数据范围：
①使用范围之外的数据
select *
from mytable
where col1 //存在于where中
not between 3 and 4;

3. and/or用在多个条件中：
   select *
   from mytable
   where (col1=1 or col1=2) and lastname=’yuwen’;

4. order by 根据指定列对结果集进行排序:
   asc(默认)/desc
   select col1,col2
   from mytable
   order by col1,col2;
   效果图：

5. limit海量数据中选择数据 :
   select col1,col2
   from mytable
   limit 0,3;
   参数1：从哪开始 ； 参数2：查几条

6. aliases/as 取别名：

7. 表取别名
   select * //不像下面只查两列
   from mytable as table1,mytable2 as table2
   where table1.col2 =’中’ and table2.col2 like ‘m%’;
   注意点见下图：

2.列取别名

7.join 根据多张表关系，查出表中数据：
7.1 引用两张表
两表关系：外键关联

语句：where关联两表

7.2 inner join (内连接)：两张表必须要都有匹配才返回行
from 表1
inner join 表2 on 表1表2 关系

7.3 left join(左连接)：左表返回所有记录，右边不够补空

7.4 right join(右连接)：右表返回所有记录，左表不够补空

7.5 full join/union(全连接)：两张表中所有行，不管是否有匹配
/* 注意：
1.union 内部的 SELECT 语句中列数量、数据类型、顺序必须相同
2.union 选取不同的值 union all 可以允许重复的值 */

很可惜mysql中没有全连接
需要用union（合并多个select结果集）
把左右连接一起合并，达到全连接的目的。

8. select into/create (查出来的数据) 创建表的备份附件
9. 1所有的列插入新表
   create table mytable3( select * from mytable2);

增
1.属性完全表示：
insert into mytable
values(值1,值2,...);
2.指定插入数据的列：
insert into mytable(col1,col2)
values(值1，值2)；

改
1.修改某一行某一列：
update mytable
set col1 = 值
where id = 1；
2.修改某一行若干列：
update mytable
set col1=值，col2=值；
where id=1;

删
1.删除某行：
delete from mytable
where id=1;
2.删除所有行：
delete*from mytale;

特别：
auto_increment自动增长：
修改起始值：alter table mytable auto_increment=100;
create index创建索引，不读整个表，索引更快：
索引是会影响更新速度的，所以只在常常被搜索的列中加索引。
1简单索引
create index index_name
on mytable(col1,col2);//col1 col2建立索引

2唯一索引:索引值不允许重复
create unique index index_name
on mytable(col1 desc); //按照col1降序

3删除索引
alter table mytable
drop index index_name;

view视图：
是什么？视图类似一个sql语句处理过后的临时表
好处：可以更精确提交数据。

1.创建视图
普通视图view_name1
create view view_name1 as
select *
from mytable
where id>5;
从view_name1视图中拿数据再创建新视图
create view view_name2 as
select distinct col1
from view_name1
where col1 desc;

2.查询视图
select * from view_name1; 或 select * from view_name2 where col2=’中’；

3.更新视图
Create/replace view view_name1 as
select id,col1,col2 //更新view_name1 多一个col2字段
from mytable
where col2 like ‘a%’;

4.删除视图
drop view view_name1;

Date/now()日期函数：
如果想要查询更简单，那么请不要再日期中使用时间部分！

1.查：SELECT * FROM Orders WHERE OrderDate='2008-12-26'

2.now()函数查出来带时间：
select now() as PerDate
from mytable;

二、数据定义语言
创建删除表格、定义索引（键）...
2.1数据库
创建：create database test_db;
删除：drop database test_db;
2.2表
创建表
create table mytable3(
id int(4),
col1 varchar(255),
);
2.2.1 Constraints 约束用于限制表的数据类型：
create table mytable3(
id int (六中约束)not null、 unique、 primary key/foreign key 、default 、 check
)
个别约束的用法：
①unique
1.unique与primary key（自动加unique）相似，为列提供唯一性保证。

①②命名，并定义多列
当表创建完之后再加unique:
②命名unique，并定义多列

撤销unique:

②foreign key

foreign key (id_p)(本表列名) references Persons(id_p)(关联表中的id)

创建外键并起名：
alter table order
constraint foreign_name
foreign key(id_p)
references persons(id_p);

表已存在再创建外键：
alter table Order //修改表
add foreign key (id_p) references Persons(id_p)； //再加外键

删除外键：
alter table Order
drop foreign key 外键名；

③check 限制列的范围
约束都包含:
直接定义 如:check（id>0）
取名，并定义多个列 如：constraint check_name check(id>0 and col2=”中”)
表一创建，加check 如：alter table mytable add check(id>0)
删除check 如：alter table mytable drop constraint check_name;

④default 列级别
由于default是列级别，所以修改删除，都要alter两次到达它的位置。
修改默认：
alter table mytable
alter col1 set default “2”;
删除默认：
alter table mytable
alter col1 drop default;

三、数据类型

四、函数
数学中：avg() 、count()、first()、last()、max()、min()、sum()
使用方式：select count(col1) from mytable;

group by 组合相同，结合函数使用：

我想知道每个人的总价，并且安装总结进行排序：
select customer, sum(orderprice) as sum_price
from orders
group by customer
order by sum_price;

having 类似where，但是可以使用函数：
select customer, sum(orderprice) as sum_price
from orders
group by customer
having sum(orderprice)>2000;

ucase()/lcase()将列值全部转换为大写/小写：
//大写
select ucase(col2)
from mytable;
//小写
select lcase(col2)
from mytable;

mid()从文本字段中提取中间值：

select mid(1,3) as SmallCity
from mytable;

round()把数值字段舍入为指定的小数位数：

select round(unitprice,0)
from mytable;

结果：

format()对字段的显示进行格式化：
如：
select format(now(),’yyyy-mm-dd’) as PerDate
from mytable;