SQL 入门及常用实例_sql实例

1.数据库 database

1.1.数据持久化 Persistence

1.2.关系型数据库 Relational data model

是以关系型数学模型的基础

通过二维表格及主外键来记录数据及数据关系

使用SQL语言来操作

1.3.数据库管理系统(Database Management System) DBMS

Oracle

… DB2 / SqlServer / SQLite

MySQL

PostgreSQL

1.4.非关系型数据库 NoSQL (not only SQL)

高并发读写 （key-value） Redis，Tokyo Cabinet，Flare

海量数据的高效存储和访问（面向文档）MongeDB，CouchDB

高可扩展性和高可用性 Cassandra , Voldemort

2.基础结构

2.1.结构及名称

数据库 database 表空间 tablespaces(Oracle) 表格 table 字段 column 记录 record 值 value

2.2.字段类型

2.2.1.字符串类型

字符串类型又分为文本字符串和二进制字符串

varchar(M)说明 括号内的M和INT(4)类型的限制不一样,这里M对插入数据的长度有限制,超长就会报错

2.2.2.数值类型

包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECIMAL。

2.2.3.时间类型

包括YEAR、TIME、DATE、DATETIME和TIMESTAMP

注意: TIMESTAMP的范围是1970年到2038年, 可以根据当前时间戳更新

TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。查询时，不同时区显示的时间值是不同的。

2.3.字段角色

2.3.1.主键 primary key

在当前表中这个字段的值要保证唯一, 并且不能为null

2.3.2.外键 foreign key

与另一表的唯一性字段(通常是主键)对应

2.3.3.普通字段

3.SQL 语句

SQL (Structured Query Language) 结构化查询语言

是一个通用的、功能极强的关系性数据库语言。是用于访问和处理关系型数据库的标准的计算机语言

3.1. DCL

数据控制语言 DCL : Data Control Language

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

3.1.1.GRANT：授权

3.1.2.ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点

回滚—ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

3.1.3.COMMIT [WORK]：提交

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。
在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。

3.1.3.1.显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；

3.1.3.2.隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

3.1.3.3.自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

3.1.4.设置时间

show variables like '%time_zone%';

set global time_zone = Asia/Shanghai
1
2
3

3.2.DDL

数据定义语言 DDL : Data Definition Language

3.2.1.表操作

3.2.1.1.创建数据库

语法 : create database 库名

create database sql_test
1

3.2.2.表结构操作

3.2.2.1.建立表

语法 :

create table 表名
(
字段名1 varchar(20),
字段名2 int
)

添加 人员表

create table person
(
   person_id            int not null comment '人员主键 自增长',
   person_sn            varchar(50) comment '人员编号',
   person_name          varchar(50) comment '人员姓名',
   person_birth         date comment '人员生日',
   person_height        int  comment '身高(cm)',
   person_weight        DECIMAL(18,2) comment '体重(kg)',
   person_sex           char(1) comment '人员性别  0 - 女   1 - 男'
);
alter table person comment '人员信息   person';
1
2
3
4
5
6
7
8
9
10
11

添加 人员身份证表

create table person_card
(
    person_card_id       int not null comment '人员身份证主键 自增长',
    person_card_no       varchar(30) comment '人员身份证号码',
    person_name          varchar(50) comment '人员姓名',
    person_birth         date comment '人员生日',
    person_sex           char(1) comment '人员性别  0 - 女   1 - 男',
    person_address       varchar(200) comment '家庭住址'
);
alter table person_card comment '人员身份证   person_card';
1
2
3
4
5
6
7
8
9
10

3.2.2.2.修改字段为主键

语法 : alter table 表名 add primary key (字段名)

alter table person add primary key (person_id);
1

3.2.2.3.修改主键为自增

语法 : alter table 表名 modify 主键名 int auto_increment

alter table person modify person_id int auto_increment;
1

3.2.2.4.修改表 增加字段

语法 : alter table 表名 add 字段 字段类型

给 人员表 增加 身份证 字段

alter table person add person_card int;
1

3.2.2.5.修改表 修改字段类型

语法 : alter table 表名 modify 字段名 改后类型

将 person_card 字段类型修改成 varchar(30)

alter table person modify person_card  varchar(30);
1

3.2.2.6.修改表 修改字段名称

语法 : alter table 表名 rename column 原名 to 现名

alter table person rename column person_card to person_card_no;
1

3.2.2.7.修改表 删除一个字段

语法 : alter table 表名 drop column 字段名

删除 person_sn 字段

alter table person drop column person_sn;
1

3.2.2.8.删除表

语法 : drop table 表名

将 person_card 表中字段转到 person 表中

alter table person add person_address varchar(200);
1

再删除 person_card 表

drop table person_card;
1

3.2.2.9.增加 person_type 人员类型表

-- 如果 person_type表存在, 删除
drop table if exists person_type;

-- 增加  person_type表
create table person_type
(
   person_type_id       int not null auto_increment comment '人员类型主键',
   person_type_name     varchar(50) comment '类型名称',
   person_type_info     varchar(200) comment '类型说明',
   primary key (person_type_id)
);

alter table person_type comment '人员类型 person_type
学龄前   学生   大学生  工作   自由职业  退休';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.2.2.10. person表增加外键

-- 增加外键字段 person_type_id
alter table person add person_type_id int;

-- 将 person_type_id 与 person_type关联,  外键 起名FK_Reference_12

alter table person add constraint FK_Reference_12 foreign key (person_type_id)
    references person_type (person_type_id) on delete restrict on update restrict;
1
2
3
4
5
6
7

3.2.2.11.person表创建sql

drop table if exists person;

/*==============================================================*/
/* Table: person                                                */
/*==============================================================*/
create table person
(
   person_id            int not null auto_increment comment '人员主键 自增长',
   person_type_id       int comment '人员类型主键',
   person_name          varchar(50) comment '人员姓名',
   person_birth         date comment '人员生日',
   person_sex           char(1) comment '人员性别  0 - 女   1 - 男',
   person_card_no       varchar(20) comment '身份证号',
	 person_height        int  comment '身高(cm)',
	 person_weight        DECIMAL(18,2) comment '体重(kg)',
   person_addr          varchar(200) comment '家庭住址',
   primary key (person_id)
);

alter table person comment '人员信息   person';

alter table person add constraint FK_Reference_12 foreign key (person_type_id)
      references person_type (person_type_id) on delete restrict on update restrict;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.3.DML

3.3.1.基本语法

3.3.1.1.增加一条

语法 :

insert into 表名( 字段1, 字段2 )
values( 值1,值2 )

INSERT INTO `person_type` (person_type_id, person_type_name, person_type_info ) VALUES ('1', '学龄前', '6岁之前');
INSERT INTO `person_type` VALUES ('2', '小学生', '6~12岁之间');
INSERT INTO `person_type` VALUES ('3', '大学生', '18岁');
INSERT INTO `person_type`(person_type_id, person_type_name ) VALUES ('4', '工作');
INSERT INTO `person_type` VALUES ('5', '自由职业', '没有固定工作单位');
INSERT INTO `person_type` VALUES ('6', '退休', '已经退休');
INSERT INTO `person_type` VALUES ('7', '读书', '应该也是在上学');
INSERT INTO `person_type` VALUES ('8', '修仙', '也是一种自由职业');
INSERT INTO `person_type` VALUES ('9', '待业', '寻找工作中...');
1
2
3
4
5
6
7
8
9

3.3.1.2.查询表中的记录

语法 :

select 字段名
from 表名

select person_type_id, person_type_name, person_type_info
from person_type
1
2

3.3.1.3.修改记录

语法 :

update 表名
set 字段1 = 值1,
字段2 = 值2
where 条件

update person_type
set
    person_type_name = '中学生',
    person_type_info = '12~18岁'
where
    person_type_id = 7
1
2
3
4
5
6

3.3.1.4.删除记录

语法 :

delete from 表名
where 条件

delete from person_type where person_type_id = 8
1

3.3.1.5.添加person数据

3.3.2.语法细节

3.3.2.1.起别名

select 语句 中 字段 起别名 使用 as , 也可以省略

select person_type_id as id, person_type_name name, person_type_info info
from person_type
1
2

from 语句 起别名, 不使用 as

select pt.person_type_id, pt.person_type_name, pt.person_type_info
from person_type pt
1
2

3.3.2.2.where 语句

where 语句返回 boolean 型结果

– 恒真 恒假

select person_type_id, person_type_name, person_type_info  from person_type  WHERE  1=1;
select person_type_id, person_type_name, person_type_info  from person_type  WHERE  1!=1;
1
2

– 模糊条件

like %(匹配 n 个字符) _ (匹配 1 个符)

select person_type_name, person_type_info  from person_type  WHERE person_type_name like '学%';
select person_type_name, person_type_info  from person_type  WHERE person_type_name like '%学%';

select person_type_name, person_type_info  from person_type  WHERE person_type_info like  '_岁%';
1
2
3
4

– 精确

= != <>

select person_type_id, person_type_name, person_type_info  from person_type  WHERE person_type_id = 5 ;
select person_type_id, person_type_name, person_type_info  from person_type  WHERE person_type_id != 5;
select person_type_id, person_type_name, person_type_info  from person_type  WHERE person_type_id  <> 5;
1
2
3

– 范围

< <= > >= between and

select person_type_id, person_type_name  from person_type  WHERE person_type_id   >= 5;
select person_type_id, person_type_name  from person_type  WHERE person_type_id  <= 5;
select person_type_id, person_type_name  from person_type  WHERE person_type_id   BETWEEN  2  AND 5;
1
2
3

– 筛选

in , not in

select person_type_id, person_type_name  from person_type  WHERE person_type_id  in (1 , 2 , 3  );
select person_type_id, person_type_name  from person_type  WHERE person_type_id not in (1 , 2 , 3   );
1
2

– 判断为空

is null is not null

select  person_type_name, person_type_info  from person_type  WHERE person_type_info is null;
select  person_type_name, person_type_info  from person_type  WHERE person_type_info  is not null;
1
2

– 条件 连接

and , or

select person_type_id, person_type_name, person_type_info  from person_type  
WHERE person_type_id > 5 and person_type_name like '%学%' ;

select person_type_id, person_type_name, person_type_info  from person_type  
WHERE person_type_id > 5 or person_type_name like '%学%' ;
1
2
3
4
5

– 反选

not

select person_type_id, person_type_name, person_type_info  from person_type  
WHERE not( person_type_id > 5 );
1
2

3.3.2.3.select 查询

– 查询全字段

select * from person

select person_id, person_type_id, person_name, person_birth, person_sex, 
       person_card_no, person_height, person_weight, person_addr
from person
1
2
3
4
5

– 字段运算

select person_id, person_type_id,
       concat('姓名:', person_name) name,
       person_birth,
       person_sex,
       case person_sex
            when 0 then '女'
            when 1 then '男'
            else '不清楚'
        end sex,
       person_card_no,
       person_height+200 height,  person_height,
       person_weight*0.5 weight,  person_weight ,
       person_addr
from person
1
2
3
4
5
6
7
8
9
10
11
12
13
14

– case when then else end

case 开始
when 条件(是不是)
then 如果是
else 以上的when都没有满足
end 结束

select person_type_id, person_type_name, person_type_info
,case
    when person_type_name like '%学%' then '未成年'
    else '成年'
    end age
from person_type
1
2
3
4
5
6

– 去重复

select distinct person_type_id from person
1

3.3.2.4.order by 排序

order by 字段名 asc(升序)/desc(降序)

– desc 降序 asc 升序(默认)

select person_id, person_type_id, person_name, person_birth, person_sex,
       person_card_no, person_height, person_weight, person_addr
from person
order by person_height desc , person_weight asc
1
2
3
4

– 乱序

select person_id, person_type_id, person_name, person_birth, person_sex,
       person_card_no, person_height, person_weight, person_addr
from person
order by rand()
1
2
3
4

3.3.2.5.聚合函数

– (聚合)统计函数 count()数个数 sum() 求和 avg() 平均数 min() 最小值 max()最大值

select count(*),sum(person_height),avg(person_height), min(person_height),max(person_height)
from person;
1
2

3.3.2.6.group by 分组

– 一般 GROUP BY 是和 聚合函数配合使用
– group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
– having 分组信息的筛选条件

SELECT person_type_id, COUNT(*), avg(person_weight)
FROM person
where person_sex = '1'
GROUP BY person_type_id
HAVING COUNT(*)>50
1
2
3
4
5

3.3.2.7.select子查询

select
     (
         select count(*) from person where person_sex='1'
     ) 男人数,
     (
         select count(*) from person where person_sex='0'
     ) 女人数;
1
2
3
4
5
6
7

select person_type_id, person_type_name, person_type_info,
    ( select count(*) from person where person.person_type_id = person_type.person_type_id) count
from person_type;
1
2
3

3.3.2.8.from子查询

select *
from (
         select person_type_id, avg( person_weight) ww
         from person
         group by person_type_id
     ) ll
where ll.ww > 200
1
2
3
4
5
6
7

3.3.2.9.where子查询

in 包含 all 全部 any 任意 exists 存在

– all

– all 全部 --全部都满足,才会取出来
– > ALL：比子查询返回结果中的所有值都大。
– < ALL ：比子查询返回结果中的所有值都小。

select person_name, person_height
from person
where person_type_id =  7
    and  person_height > ALL (
        select person_height
        from person
        where person_type_id =  2
);
1
2
3
4
5
6
7
8

– any

– >ANY：比子查询返回结果中的某个值大。
– =ANY：与子查询返回结果中的某个值相等。
– <ANY：比子查询返回结果中的某个值小。

select person_name, person_height
from person
where person_type_id =  7
    and  person_height > ANY (
        select person_height
        from person
        where person_type_id =  2
);
1
2
3
4
5
6
7
8

– exists

–判断子查询是否有结果（强调有无，不关心具体是什么）
–exists：子查询至少返回一行时条件为true。
–not exists：子查询不返回任何一行时条件为true。

-- 有返回
select person_type_id, person_type_name, person_type_info
from person_type
where exists (
              select 1 from person_type  
          );

-- 无返回
select person_type_id, person_type_name, person_type_info
from person_type
where exists (
              select 1 from person_type  where person_type_name='abc'
          );
1
2
3
4
5
6
7
8
9
10
11
12
13

not exists：特别应用，当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据。

-- 无返回, 因为 子查询有结果
select person_type_id, person_type_name, person_type_info
from person_type pt1
where not exists (
  select 1 from person_type pt2
  where pt2.person_type_name='小学生'
);

-- 有返回 , 但不包含 '小学生'
select person_type_id, person_type_name, person_type_info
from person_type pt1
where not exists (
  select 1 from person_type pt2
  where pt2.person_type_name='小学生'
             and  pt2.person_type_name=pt1.person_type_name
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.3.2.10.横向连表

– 笛卡尔乘积

select * from person p, person_type pt where p.person_type_id = pt.person_type_id
1

– 左连 left

select p.*, pt.*
from person p left join person_type pt on p.person_type_id = pt.person_type_id
1
2

– 右连 right

select p.*, pt.*
from person p right join person_type pt on p.person_type_id = pt.person_type_id
1
2

– 内连 inner join

select p.*, pt.*
from person p inner join person_type pt on p.person_type_id = pt.person_type_id
1
2

– 全连 full (mysql 不支持)

select p.*, pt.*
from person p full join person_type pt on p.person_type_id = pt.person_type_id
1
2

3.3.2.11.纵向连表

合的是记录

– union all 合集

select c.company_name name, c.company_address addr from company c
union all
select s.school_name name, s.school_address addr from school s;
1
2
3

– union 去重复 排序 并集

select c.company_name name, c.company_address addr from company c
union
select s.school_name name, s.school_address addr from school s;
1
2
3

– intersect 交集

select c.company_address addr from company c
intersect
select s.school_address addr from school s;
1
2
3

– minus 在前不在后的元素集合 差集

select c.company_address addr from company c
minus
select s.school_address addr from school s;
1
2
3

3.3.2.12. 分页 limit

limit firstRow, maxRows limit 从哪条开始( 从 0 开始 ) , 最多显示多少条

select * from person limit 10, 10;
1

3.3.3.执行顺序

select [呈现形式] 代替表达式(与from 连接)
from [数据来源] 临时表
where [过滤条件] 嵌套查询
group by [分组模式]
having [分组过滤条件]
order by [排序]
limit [n],[m] 显示 [ n + 1, n + m ]

SQL执行顺序
1 from
2 where
3 group by
4 having
5 select
6 distinct
7 union
8 order by
9 limit

4.MySQL常用函数

4.1.数字

– 取余 mod(a,b)

select mod(10, 3);
1

– 四舍五入

– round(a)
– round(a,n) 保留小数点后n位

select round(10.345), round(10.345, 2);
1

– 只舍 floor ,只入 ceil

select floor(10.345), ceil(10.345);
1

– 随机数[0.0, 1.0) rand()

select rand(), rand(), rand();
1

– 随机整数[20, 30)

– floor(rand() * 整数个数 + 从几开始)

select floor(rand()*10 + 20);
1

4.2.字符串

– 空值转换

ifnull(字段名,替换的值)

select  person_type_name, person_type_info, ifnull(person_type_info,'没有说明') from person_type;
1

select  if(person_type_info is null , '~~没有信息~~', person_type_info) pi
from person_type;
1
2

– 字符串长度

char_length(字段名)

select person_type_id, person_type_name, person_type_info, char_length(person_type_info) from person_type;
select length(person_type_info), person_type_info  from person_type;
1
2

– 截取字段串

– substr(字段名,从哪开始,截取多长)
– substr(字段名,从哪开始) 默认到最后

select person_type_info, substr(person_type_info, 2, 1), substr(person_type_info, 2) from person_type;
1

– 字符串拼接

concat(字符串1,字符串2…)

select person_type_name, concat('名称:', person_type_name) from person_type;

select person_type_name from person_type where person_type_name like concat('%', '学', '%');
1
2
3

– 字段信息拼接

将多条记录字段信息用 , 拼接到一起

select group_concat(person_type_name) names from person_type;
select group_concat(person_type_name separator '-') names from person_type;
select group_concat(distinct person_type_name order by person_type_info ) names from person_type;
1
2
3

– 填充字符串

lpad(str,len,padstr) rpad(str,len,padstr)

返回字符串str，左(右)填充用字符串padstr填补到len字符长度。 如果str为大于len长，返回值被缩短至len个字符(即，不能超过 len 长)。

select lpad('hi',4,'??'),lpad('hello',4,'??'), rpad('hi',4,'??');
1

– 替换 replace()

update person_type
set person_type_info = replace(person_type_info, '~', '--');
1
2

4.3.时间

– 当前日期 now()

select now(), sysdate(), curdate(), current_time();
1

– 把字符串转成日期

select str_to_date('2000-01-01 13:34:56','%Y-%m-%d %H:%i:%s');
1

– 日期转成字符串

– date_format(date,‘%Y-%m-%d %H:%i:%s’)
– %W 星期几

select date_format(now(),'%Y-%M-%D %H:%I:%S %W'), date_format(now(),'%y-%m-%d %h:%i:%s %w');
select date_format(now(),'%Y年%m月%d日 %H:%i')

select date_format(now(), '%Y');
select year(now());
select date_format(now(), '%H');
select hour(now());

-- 从年份0开始的天数
select to_days(now());

1
2
3
4
5
6
7
8
9
10
11

– 调整时间

date_add(日期，INTERVAL 数量 类型) 增加时间

date_sub(日期，INTERVAL 数量 类型) 减少时间

select date_add(now(), INTERVAL 10 DAY);
select date_add('2013-01-18', interval '1,2' YEAR_MONTH);
select date_add('2013-02-18', interval -1 YEAR_MONTH);

select date_sub(now(),INTERVAL 10 DAY);
1
2
3
4
5

– 时间差

timestampdiff(类型, 时间1, 时间2)

select timestampdiff(DAY, '2013-01-18', '2013-02-18' ), to_days('2013-01-18')-to_days('2013-02-18');
select timestampdiff(MONTH, '2012-01-18', '2013-02-18' );
1
2

生日 求年龄

select timestampdiff(YEAR, 生日, now() );
1

– 返回当前时间的时间戳

– 返回值：自’1970-01-01 00:00:00’的到当前时间的秒数差

select unix_timestamp();
select unix_timestamp('2018-12-05 01:10:00');
1
2

4.4.匹配

– 准备

DROP TABLE IF EXISTS `set_test`;
CREATE TABLE `set_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg` varchar(50) DEFAULT NULL,
  `info` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `set_test` VALUES ('1', '1', '刘首一');
INSERT INTO `set_test` VALUES ('2', '12', '王小二');
INSERT INTO `set_test` VALUES ('3', '3', '李小三');
INSERT INTO `set_test` VALUES ('4', '4', '赵小四');
INSERT INTO `set_test` VALUES ('5', '1,12', '刘首一,王小二');
INSERT INTO `set_test` VALUES ('6', '1,12,3', '刘首一,王小二,李小三');
INSERT INTO `set_test` VALUES ('7', '12,4', '王小二,赵小四');
INSERT INTO `set_test` VALUES ('8', '1,3,12,4', '刘首一,李小三,王小二,赵小四');
INSERT INTO `set_test` VALUES ('9', '1,3', '刘首一,李小三');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

– find_in_set

-- 在 set_test 表中 msg 字段 包含 1的记录

select id, msg, info
from set_test
where find_in_set('1', msg);
1
2
3
4
5

– locate

select id, msg, info
from set_test
where locate('1,12',msg);
1
2
3

4.5.从一表复制到另一个表

insert into tzd_engineering_material_count(material_id, engineering_id , material_count  )
select materia_id, project_id, purchase_quantity
from  materia
1
2
3

5.@ 用户变量

@变量名 : 定义一个用户变量.
= 对该用户变量进行赋值.
用户变量赋值有两种方式: 一种是直接用" = “号，另一种是用” := “号。
其区别在于:
使用set命令对用户变量进行赋值时，两种方式都可以使用；
用select语句时，只能用” := “方式，因为select语句中，” = "号被看作是比较操作符。

– 创建常量，起别名a

select @i := 0 a;
1

– 作为临时表使用

SELECT * FROM person_type,(SELECT @i:= 0 a) t;
1

– 数据累计计算

SELECT *,(@i:=  @i + pt.person_type_id) idadd FROM person_type pt,(SELECT @i:= 0 ) t;

SELECT pt.*, @i := @i +1 rownum FROM person_type pt,(SELECT @i:= 0 a) t;
1
2
3