SQL基础教程-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_40145104/article/details/99765577

一、入门语句

1、查看库

show databases;

2、选择库

use [库名];

3、删除库

drop database [库名];

4、创建库

create database [库名] charset [字符集];

5、查看表

show tables;

6、删除表

drop table [表名];

7、清空表

truncate [表名];

二、建表

建表过程相当于建立表头，定义字段，设置宽度（设置字段类型长度）

1、字段类型

数值型：

整型：
tinyint smallint mediumint int bigint
1字节 2字节 3字节 4字节 8字节
$-2^7$ ~ $2^7-1$ $-2^1^5$ ~ $2^1^5-1$ $-2^2^3$ ~ $2^2^3-1$ $-2^3^1$ ~ $2^3^1-1$ $-2^6^3$ ~ $2^6^3-1$

参数声明：（M） unsigned zerofill

unsigned ：表示无符号型无参数声明时候，默认为有符号型

M必须与zerofill配合使用：表示不是某个长度时，用0填冲

浮点型：float（M，D），decimal（M，D），其中M表示总位数，D表示小数点后的位数。 decimal为定点存储

字符串：

char(N)	定长，长度为N，不够N个长度时，在末尾空格补齐。该存储方式效率高、查找快，但是浪费空间，空间利用率可能达到百分之百
varchar(N)	变长，可存储0~N个长度，每个字符串都有一个前缀保存字符串长度，该存储方式效率低、查找慢，但节约空间，空间利用率不可能达到百分之百
test(N)	文本类型，可以存储较大的文本字段，搜索速度稍慢
bloc(N)	二进制信息，用来存储图像、音频等二进制信息，防止因字符集的问题而导致的信息丢失

日期/时间类型

日期	date	YYYY-MM-DD
日期时间	datetime	YYYY-MM-DD HH-ii-ss
时间	time	HH-ii-ss
年份	year	YYYY/YY
时间戳	timestamp	YYYY-MM-DD HH-ii-ss current_timestamp 插入当前时间

2、建表语句

create table 表名(
字段1 字段声明 约束，
字段2 字段声明 约束，
.
.
字段3 字段声明 约束
)engine 存储引擎 charset 字符集;

eg:建立表如下

姓名	年龄	性别	生日

建表如下：

create table class(
pname varchar(10) not null default '',
age tinyint unsigned not null default 0,
gender char(1) not null default '',
birthday date not null default '0000-00-00'
)engine myisam charset utf8;

三、修改表结构的基本语法（增删改）关键字alter

1、增加字段关键字 add

alter table 表名 add 字段名 字段声明 约束;   //该字段默认添加在最后面

alter table 表名 add 字段名 字段声明 约束 after 某字段;   //添加在某个字段后

alter table 表名 add 字段名 字段声明 约束 first;   //添加在最前面

2、删除字段关键字 drop

alter table 表名 drop 字段名;   //删除某个字段

3、修改字段类型（不能修改列名）关键字 modify

alter table 表名 modify 字段名 新的声明 新的约束;

4、修改字段名和字段类型关键字 change

alter table 表名 change 旧字段名 新字段名 新的声明 新的约束;
//注意：新的声明只能往大改 例如 int型只能修改为bigint型 不能修改成tinyint等 否则容易造成数据丢失

四、修改表中数据--增删改查

1、增加数据关键字 insert

insert into 表名 (字段1,字段2,...,字段n) values (值1,值2,...,值n);   //部分字段插入 字段与值一一对应

insert into 表名 values (值1,值2,...,值n);   //插入全字段 (字段1,字段2,...,字段n)可以省略

insert into 表名 values (值1,值2,...,值n),(值1,值2,...,值n),(值1,值2,...,值n);   //一次插入多条数据

eg：在class表中插入一些数据

insert into class values
('张三',16,'男','2012-03-04'),
('李四',14,'男','2012-06-21'),
('王五',17,'女','2012-02-27'),
('赵六',16,'男','2012-08-29'),
('钱七',15,'女','2012-12-28'),
('李明',17,'男','2012-04-03'),
('孙亮',16,'女','2012-10-22');

2、改表中数据关键字 update

update 表名 set 字段1 = 新值1,字段2 = 值2,...,字段n = 值n where 条件;   //满足条件 则会被修改

3、删除表中数据关键字 delete

delete from 表名;   //删除表中所有数据

delete from 表名 where 条件;   //满足条件 则删除

4、查询表中数据关键 select

select * from 表名;   // 查询所有数据 

select * from 表名 where 条件;   //条件查询

select 字段1,字段2,...,字段n from 表名 where 条件;   //非全字段查询

四、select查询的几个字句

1、条件查询------where

select * from 表名 where 条件;

比较运算符
<	小于	！= 或 <>	不等于
<=	小于等于	>=	大于等于
=	等于	>	大于
in	在某个集合内	between...and...	在某个范围内

逻辑运算符
not 或！	逻辑非
or 或 \|\|	逻辑或
and 或 &&	逻辑与

like 模糊匹配
%	通配任意长度字符串
_	通配单一字符串

对class表进行查询，仅举部分说明where查询：

查询年龄大于等于15的学生姓名和年龄
select pname,birthday from class where age >= 15;
查询生日在2012-04-01 到 2012-08-30的学生姓名和生日
select pname,birthday from class where birthday between '2012-04-01' and '2012-08-30';
查询年龄为15 或者 16 的学生信息
select * from class where age in (15,16);   //使用in

select * from class where age = 15 or age = 16;   //使用or
查询年龄为15岁的女生信息
select * from class where gender = '女' and age = 15;
查询名为三的学生信息
select * from class where pname like '%三';

2、分组查询------group by

聚合函数
求最大值	max()
求最小值	min()
求和	sum()
求平均值	avg()
求总行数	count()

查询年龄最大的学生信息----max

select max(age) from class;   //查询最大的年龄

select * from class where age =  (select max(age) from class);   //查最大年龄的学生信息

查询年龄最小的学生信息---min

select min(age) from class;   //查询最小的年龄

select * from class where age =  (select min(age) from class);   //查最小年龄的学生信息

分别计算男生和女生的平均年龄

select gender,avg(age) from class group by gender;

统计男生和女生的人数

select gender,count(*) from class group by gender;

3、having------可以对临时的结果集进行筛选

先准备一张成绩表和数据

create table grade(
pname varchar(10) not null default '',
chinese tinyint unsigned not null default 0,
math tinyint unsigned not null default 0,
english tinyint unsigned not null default 0
)engine innodb charset utf8;

insert into grade values
('张三',95,80,78),
('李四',79,67,91),
('王五',67,56,68),
('赵六',67,51,69),
('钱七',59,58,48),
('李明',68,85,72),
('孙亮',59,76,84);

查询平均成绩大于80的学生成绩以及平均成绩
select pname,((chinese + math + english)/3) as ag from grade having ag > 70;

4、排序查询------order by

语法如下：

order by desc/asc (limit[offset] N);  
 //desc表示降序 asc表示升序 有limit时候 表示从（offset+1）条记录开始 选取N条记录 无limit限制时，选所有记录

计算学生成绩的排名从大到小
select pname,(chinese + math + english) as sg from grade order by sg desc;
计算英语成绩的排名顺序从小到大
select pname,english from grade order by english asc;
班级总分前三名
select pname,(chinese + math + english) as sg from grade order by sg desc limit 0,3;

五、子查询

1、where型子查询

把内层的查询结果作为外层查询的比较条件

如果where 字段 = （内层sql语句），则内行的结果必须是单行单列
如果where 字段 in （内层sql语句），则内层的结果为多行多列

2、from型子查询

内层的sql语句查询结果，当成一张临时表，再次查询

3、exists型子查询

是指把外层sql查询结果，拿到内层sql去测试，如果内层sql成立则取该行

六、连接查询

1、左连接

左表不动右表动，以A表为准

A left join B on 条件;

2、右连接

以B表为准

A right join B on 条件;

3、内连接

只有查询到了对应的行才取

A inner join B  on 条件;

4、左连接右连接内连接的关系

A left join B on 条件 <=> B right join A on 条件;

内连接是左右连接的交集

七、union查询

语法：

sql1 union sql2;   //将两个sql语句的查询结果合并

注意：可以通过别名将两张结果集的列保持一致，如果不改则会以第一个结果集的列为准。列数不同不能union。如果Unoin后的结果集有重复会自动去重复

与order by一起使用的注意事项：

内层的order by语句单独使用，在执行期间被mysql的代码分析器优化了，不会执行。内层的order by语句必须能够影响结果集才有意义，比如与limit结合使用

八、视图

视图view可以看成一张虚拟表，是表通过某种映射得到的一个投影

语法

create view 视图名 as select语句;

作用

简化查询
安全权限
分表查询

视图与表的关系

表变化，视图也会跟着变换
视图与表中的数据一一对应的时候，视图才能影响表

视图中的algorithm

merge 合并查询语句
temptable 建立临时表

undefined 未定义由系统指定

create [algorithm = merge/temptable/undefined]
    view 视图名 
    as select语句;

tinyint	smallint	mediumint	int	bigint
1字节	2字节	3字节	4字节	8字节
$-2^7$ ~ $2^7-1$	$-2^1^5$ ~ $2^1^5-1$	$-2^2^3$ ~ $2^2^3-1$	$-2^3^1$ ~ $2^3^1-1$	$-2^6^3$ ~ $2^6^3-1$

MySQL基础语法