SQL学习笔记

1、创建数据库
1.1 创建数据库：create database 数据库名称;
1.2 查看创建好的数据库：show create database 数据库名称；
1.3 查看所有的数据库列表：show databases;
1.4 使用数据库：use 数据库名称；
1.5 删除数据库：drop database 数据库名称；

2、创建数据表
2.1 创建数据表步骤：
use 数据库名称；
create table 表名（
字段名1 数据类型（长度） 约束条件，
字段名2 数据类型（长度） 约束条件，
。。。
字段名n 数据类型（长度） 约束条件,
primary key (主键字段名)，
foreign key (外键字段名）references 引用表名（引用外键字段名）
）;
2.2 查看数据表：show tables;
2.3 删除数据表：drop table 表名；
2.4 拷贝数据表
create table 新表名 as select * from 被拷贝的表；
2.5 将查询结果存为新表以方便在后续使用
create table 新表名（查询语句如select 字段 from 数据源…）；

3、数据类型
3.1 整数型
3.1.1 int
有符号范围 -2147483648~2147483647
无符号范围 0~4294967295
最大显示宽度为11，即int(11)，且默认宽度为11
3.1.2 tinyint
有符号范围 -128~127
无符号范围 0~255
最大显示宽度为4，即tinyint(4)
3.1.3 smallint
有符号范围 -32768~32767
无符号范围 0~65535
最大显示宽度为6，即samllint(6)，且默认宽度为6
3.1.4 mediumint
有符号范围 -8388608~8388607
无符号范围 0~16777215
最大显示宽度为9，即mediumint(9)，且默认宽度为9
3.1.5 bigint
分为有符号的和无符号的，最大显示宽度为20，即bigint(20)，且默认宽度为20
注：在指定字段数据类型时，如果在数据类型后加上unsigned属性可以禁止负数
3.2 小数型
3.2.1 float(显示宽度,小数位数)
只能为有符号的，4字节，默认为（10,2）
3.2.2 double(显示宽度,小数位数)
只能为有符号的，8字节，默认为（16,4）
3.2.3 decimal(显示宽度，小数位数)
只能为有字符的
3.3 日期时间型
3.3.1 date
YYYY-MM-DD格式，在1000-01-01和9999-12-31之间
3.3.2 datetime
YYYY-MM-DD HH:MM:SS格式，位于1000-01-01 00:00:00和9999-12-31 23:59:59之间
3.3.3 time
HH:MM:SS格式，位于-838:59:59~838:59:59
3.3.4 timestamp
时间戳，位于1970-01-01 00:00:00和2037-12-31 23:59:59之间，
3.3.5 year(2|4)
以2位或4位格式储存年份值
如果是2位，1970~2069
如果是4位，1901~2155
默认长度为4位
3.4 字符串
3.4.1 char(长度)
固定长度字符串，长度为1-255。如果内容小于指定长度，右边填充空格，如果不指定长度，默认为1
3.4.2 varchar(长度)
可变长度字符串，长度为1-255。必须指定长度
3.4.3 BLOB或TEXT
最大长度65535，存储二进制大数据，如图片。不能指定长度，其中BLOB大小写敏感
3.4.4 TINYBLOB和TINYTEXT
最大长度255，不能指定长度
3.4.5 MEDIUMBLOB或MEDIUMTEXT
最大长度16777215字符
3.4.6 LONGBLOB或LONGTEXT
最大长度4294967295字符
注：BLOB都用于BLOBs（Binary Large OBjects）
3.4.7 ENUM
枚举，允许输入可能值的列表，可以在ENUM列表中列出最多65535个值。若列表中不存在插入的值，则插入空值
如ENUM(‘A’,‘B’,‘C’),NULL值也可
3.4.8 set
与ENUM类似，但set最多只能包含64个列表项目且set可存储一个以上的选择

4、约束条件
4.1 主键约束
语法：字段名 数据类型 primary key
或在定义数据表的最后加上primary key(主键字段名)
4.2 非空约束
语法：字段名 数据类型 not null
4.3 唯一约束（允许为空，但只能出现一个空值）
语法：字段名 数据类型 unique
4.4 自增字段
语法：字段名 数据类型 auto-increment
一个表只能有一个自增字段，自增字段必须为主键的一部分，默认情况下从1开始自增
4.5 默认值
语法：字段名 数据类型 default 默认值

5、填充数据
5.1 insert into
语法：
insert into 表名（字段名1，字段名2…）#若不指定字段，括号内部分可删除
values
(记录1),
(记录2),
…
(记录n)；
5.2 导入外部数据
语法 ：
load data local infile ‘文件路径.文件格式’
into table 表名
fields terminated by ‘分割符’
ignore 忽略行数 lines;
注：文件路径要求是纯英文的，且斜杠号应为左斜
5.3 检查表数据
5.3.1 检查导入内容
select * from 表名；
5.3.2 检查导入数据总行数
select count(*) from 表名；
5.3.3 检查表结构
desc 表名；
5.4 修改数据
5.4.1 为指定字段赋值
语法：update 表名 set 字段1=值1，字段2=值2… where 赋值条件；
5.4.2 删除数据
语法：delete from 表名 where 删除条件；
若省略where删除条件，则会删除表中所有数据记录

6、修改数据表
6.1 修改表名
语法：alter table 原表名 rename 新表名；
6.2 修改字段数据类型
语法：alter table 表名 modify 字段名 新数据类型；
6.3 修改字段名
语法：alter table 表名 change 原字段名 新字段名 原数据类型；
6.4 同时修改字段名与数据类型
语法：alter table 表名 change 原字段名 新字段名 新数据类型；
6.5 添加新字段
语法：alter table 表名 add 新增字段名 新字段数据类型 新字段约束条件；
6.6 修改字段排列顺序为第一位
语法：alter table 表名 modify 字段名 数据类型 first;
6.7 修改字段m排列顺序为字段n之后
语法：alter table 表名 modify m字段名 m数据类型 after n字段名;
6.8 删除字段
语法：alter table 表名 drop 字段名；
6.9 加入外键
语法：alter table 表名 add foreign key (外键字段名) references 引用表名(引用外键字段名) on delete set null|on delete cascade;

7、函数
7.1 处理数字值的常用函数
abs(x) 返回x的绝对值
log(x,y) 返回x的以y为底的对数
mod(x,y) 返回x/y的模（余数）
rand() 返回0-1内的随机值，可以通过提供一个参数（种子）使rand()随机数生成器生成一个指定的值
ceiling(x) 返回大于x的最小整数值，即进一取整
round(x,y) 返回参数x的四舍五入的有y位小数的值
sqrt(x) 返回一个数的平方根
7.2 处理数字值的其他函数
bin(x) 返回x的二进制，otc返回八进制，hex返回十六进制
exp(x) 返回值e的x次方
greatest(x1,x2…xn) 返回集合中最大的值
least(x1,x2…xn) 返回集合中最小的值
ln(x) 返回x的自然对数
pi() 返回π的值
floor(x) 返回小于x的最大整数值，即去小数取整
truncate(x,y) 返回数字x截短为y位小数的结果
sign(x) 返回数字x的符号的值，正数1，负数-1，0返回0
7.3 处理字符串的常用函数
concat(s1,s2…sn) 将s1,s2…sn连接成新的字符串
left(str,x) 返回字符串str最左边的x个字符
right(str,x) 返回字符串str最右边的x个字符
length(str) 返回字符串str中的字符数
7.4 处理字符串的其他函数
ascii(char) 返回字符串的ASCII码值
bit_length(str) 返回字符串的比特长度
concat_ws(sep,s1,s2…sn) 将s1,s2…sn连接成字符串，并用sep字符间隔
insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
find_in_set(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置
lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写后的结果
ucase(str)或upper(str) 返回将字符串str中所有字符改变为大写后的结果
position(substr,str) 返回子串substr在字符串str中第一次出现的位置
quote(str) 用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr) 返回字符串str重复x次的结果
reverse(str) 返回颠倒字符串str的结果
ltrim(str) 去掉字符串str首部的空格
rtrim(str) 去掉字符串str尾部的空格
trim(str) 去掉字符串str首部和尾部的所有空格
7.5 处理日期时间型数据的常用函数
now() 返回当前的日期和时间
date(datetime) 返回datetime的日期值
time(datetime) 返回datetime的时间值
7.6 处理日期时间型数据的其他函数
date_format(date.fmt) 根据指定的fmt格式，格式化日期date值
from_unixtime(ts.fmt) 根据指定的fmt格式，格式化unix时间戳ts
monthname(date) 返回date的月份英文名
dayname(date) 返回date的星期英文名
curdate()或current_date() 返回当前日期
curtime()或current_time() 返回当前时间
quarter(date) 返回date在一年中为第几季（1-4）
week(date) 返回date在一年中为第几周（0-53）
dayofyear(date) 返回date在一年中为第几天（1-366）
dayofmonth(date) 返回date在一个月中为第几天（1-31）
dayofweek(date) 返回date在一周中为第几天（1-7）
year(date) 返回date的年份（1000-9999）
month(date) 返回date的月份（1-12）
day(date) 返回date的天数部分
hour(time) 返回time的小时值（0-23）
minute(time) 返回time的分钟值（0-59）
second(time) 返回time的秒值（0-59）
7.7 其他函数
7.7.1 group_concat(col)
返回由属于一组的列值连接组合而成的结果，常与关键字group by一起使用，能将分组后指定的字段值都显示出来
7.7.2 cast()
将一个值转换为指定的数据类型

8、查询语句
8.1 select语句的操作符
8.1.1 算术操作符
+（加号）、-（减号）、*（乘号）、/（除号）
8.1.2 比较操作符
=（等于）、>（大于）、<（小于）、<=（小于等于）、>=（大于等于）、!=或<>（不等于）、!>（不大于）、!<（不小于）
8.2 聚合类函数
8.2.1 avg()
按列计算平均值
8.2.2 sum()
按列计算值的总和
8.2.3 max()
求一列中的最大值
8.2.4 min()
求一列中的最小值
8.2.5 count()
按列值计算个数
count(1),count(星号)会计算所有记录的数量，一般用count(星号)
count(字段名)会忽略null的数据记录
8.3 select语句的语法
select 目标列组 #若有group by ，则目标列组一般先写上分组列名
from 数据源表名
where 元组选择条件
group by 分组列 [having 组选择条件]
order by 排列序1，排序列2… asc|desc;
8.4 多表查询join
select 目标列组
from 表名1 left/inner/right join 表名2
on 表1.匹配字段=表2.匹配字段；
8.5 联合查询union
8.5.1 合并表，消去重复行
select 表1.*from 表1
union
select 表2.*from 表2;
8.5.2 合并表，保留重复行
select 表1.*from 表1
union all
select 表2.*from 表2;

9 查询操作符
9.1 and
联合多个条件进行查询，条件与条件间关系为“和”
语法：条件表达式1 and 条件表达式2 and…and 条件表达式n
9.2 or
联合多个条件进行查询，条件与条件间关系为“或”
语法：条件表达式1 or 条件表达式2 or…or 条件表达式n
9.3 in
判断某个字段的值是否在指定的集合中，若在则满足查询条件，反之不满足
语法：in（元素1，元素2…元素n）
操作符not in与in完全相反
9.4 between
判断某个字段的值是否在指定范围中
语法：between 取值1 and 取值2
操作符not between与between完全相反
9.5 like
用来匹配字符串是否相等，字符串参数的值可以使一个完整的字符串，也可以是包含%或_的通配符
%代表任意长度的字符串
_代表单个字符，有几个_就代表几个单个字符
操作符not like与like完全相反
9.6 is null
用来判断字段的值是否为空值
操作符is not null与is null完全相反
9.7 distinct
用来消除重复记录
语法：select distinct 字段名 from…;

10 子查询
写在（）中，把内层查询结果当做外层查询参照的数据表来用
10.1 any
表示满足其中任意一个条件，只要满足内层查询语句返回的结果值中的任一个，就可以通过该条件来执行外层查询语句
例：用any操作符与子查询语句来查询fruits表中所有f_id对应的f_price在10元到20元之间的水果记录
select * from fruits where f_id = any
(select f_id from fruits where f_price between 10 and 20);
10.2 all
表示满足所有条件，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句
例：用all操作符与子查询语句来查询所有f_price大于25的水果记录
select * from fruits where f_price>all
(select f_price from fruits where f_price<=20);
10.3 exists
表示存在，内层查询语句不返回查询的记录，而只是返回一个真假值，若内层查询语句查询到满足条件的记录，就返回真值，否则返回假值
当返回真值时，外层查询语句将进行查询
当返回假值时，外层查询语句不进行查询或查询不出任何记录
例：用exists操作符与子查询语句来查询是否存在f_price大于30的水果记录
select * from fruits where exists
(select * from fruits where f_price>30);
10.4 直接嵌套
select 需要字段 from (查询语句如select 字段 from 数据源…) ；#相当于括号里的查询语句建立了一个新的表，通过直接嵌套从新表里查询所需内容

11、case条件语句
case
when 条件1 then 结果1
when 条件2 then 结果2
…
when 条件n then 结果n
else 结果
end;

12、其他
as：可以将表或字段名重新命名为别的名称使用，只在当前查询语句中有效
limit：查询后只显示limit指定的行数结果