二、Insert语句
2.1 语法
insert into 表名 [(字段名1,字段名2, ...)] values (字段值1, 字段值2, ...) [, (字段值1, 字段值2, ...)] ;
说明:
-
表名 [(字段名1,字段名2, …)] 中括号表示可以指定某些字段来插入值,如果不指定列,表示表中的所有字段列。
-
[, (字段值1, 字段值2, ...)] 表示可以插入多行数据,即批量插入。
-
字段名和字段值从左到右一一对应,不能多也不能少。
2.2 案例
-
向back表中插入一行数据
-- 字符类型的值需要使用 单引号'', 不能使用双引号 "" insert into back (name, addr) values ('交通银行', '陕西省西安市高新支行') ;
-
向 back表中插入多行数据
insert into back (name, addr) values ('建设银行', '陕西省西安市科技路支行'), ('工商银行', '陕西省西安市唐延路支行'), ('农业银行', '陕西省西安市汉城路支行'), ('西安银行', '陕西省西安市高新四路支行');
-
默认情况下,向back中所有字段插入一行数据
-- 全字段插入时,自增约束会失效 -- 必须按照desc back列出的字段顺序指定字段值 insert into back values(4, '招商银行西安分行', '陕西省西安高新二路90号');
三、简单的Select语句
3.1 语法
select *|<字段名1|表达式, 字段名2|表达式, ...> from 表名 [as 别名] [join on] [where 条件表达式] [group by 分组字段] [having 聚合字段的条件表达式] [order by 排序字段] [limit offset, rows]
3.2 案例
-
查看所有back表中的银行名称, 不查看空的银行名称。
-- name字段值是null select name from back where name is not null;
-- name的字段值是空字符串 select name from back where name != '';
-
查看back表中地址为null的所有银行名称和编号
select id, name from back where addr is null;
-
查看back表中银行名称的前四个字符【扩展】
-- substr(字段或字符串, 起始位置,长度) 截取字段值或某一字符串的子字符串, 起始位置可以使用负数,表示最后的某一位置,如-4倒数第四个字符位置 -- AS 指定列或表达式的别名, 如果区分大小写,则需要双引号 "", AS关键字也可省略 -- length(字段或字符串) 返回字段值或字符串的长度 select name, addr, length(addr) as len, substr(addr, 1, 4) AS "Sub_name" from back where name != '';
如果只看某一函数的功能,可以写成下面的sql语句:
select length('abc') as len;
-
查看back表中银行编号小于10且名称不为空的银行名称、地址信息
-- 多条件时,且的关系使用 and, 或的关系使用 or select id,name,addr from back where id < 10 and (name is not null or name !='');
四、Update语句
4.1 语法
update 表名 set 字段名=字段值|函数|表达式 [, 字段名2=字段值...] [where 条件表达式]
4.2 案例
-
修改back表中银行编号为1的银行名称为'浙江银行', 地址为'西安市高新路119号'
update back set name='浙江银行', address='西安市高新路119号' where id=1;
-
增加back表的tel字段,可以输入12-13位座机号, 11位手机号。字段可以null。
更新所有银行的tel字段值为 '029-88592011'
alter table back add tel varchar(13); update back set tel='029-88592011';
-
增加back表的last_time字段,字段的说明是"最后修改时间",默认情况是系统的当前时间。
更新银行编号为1, 3, 9的last_time为2017-10-22日。
alter table back add last_time timestamp default current_timestamp comment '最后修改时间';
current_date 当前日期 ,如 2019-07-08
current_time 当前时间, 如 13:15:20
current_timestamp 当前日期时间 , 如2019-07-08 13:15:20
update back set last_time='2019-07-08' -- where id=1 or id=3 or id=9; where id in (1, 3, 9);
-
增加 t_order表,包含银行编号(外键约束)、账单号(主键自增)、in_amount(收入), out_amount(支出), order_time(账单发生的时间)
银行编号为1,在2019-06-12日收入100万, 在2019-06-18日支出50。
银行编号为2,在2019-06-13日收入120万, 在2019-06-18日支出80。
drop table if exists t_order; -- order是mysql的关键字,不能直接使用 -- timestamp列值,在修改任一列值时,timestamp字段的值会自动变更为修改的时间 create table t_order( id integer primary key auto_increment comment '账单号', back_id integer comment '银行编号', in_amount float comment '收入(万)', out_amount float comment '支出(万)', order_time time comment '账单发生的时间', order_date date comment '账单发生的日期' ); -- 收入 insert into t_order(back_id, in_amount,order_date) values (1, 100, '2019-06-12'), (2, 120, '2019-06-13'); -- 支出 insert into t_order(back_id, out_amount, order_date) values (1, 50, '2019-06-18'), (2, 80, '2019-06-18');
更新银行编号2,在2019-06-18日支出为55。
更新银行编号1,在2019-06-18日少支出了12万。
update t_order set out_amount=50 where back_id=2 and order_date='2019-06-18'; update t_order set out_amount=out_amount+12 where back_id=1 and order_date='2019-06-18';
五、Delete语句
5.1 语法
delete from 表名 [where 条件表达式]
如果没有where条件,则表示清空表。
5.2 案例
-
删除back表的银行名称为ccccc的记录
delete from back where name='ccccc';
-
删除back表中名称或地址中包含'ShanXi' 字符的所有记录
delete from back where name like '%ShanXi%' or address like '%ShanXi%';
like 是模糊查询的关键字,其中有两个通配符:
-
% 表示任意长度的任意字符
-
_ 表示一位长度的任意字符
-
-
查询back表中地址在"高新"的所有银行
select * from back where address like '%高新%';
-
查询back表中名称的第二个字符为"商"的所有银行
select * from back where name like '_商%';
六、常用函数
6.1 字符函数
-
length() 返回字符串的长度
-
substr(str, start, len) 截取str的子字符串,从start位置开始,截取len个字符,start可以是负数,表示从右边开始。
-
left(str, len) 从字符串str的左右截取len个字符
-
right(str, len) 从字符串str的右边截取 len个字符
-
lower(str) 将str全部转成小写
-
upper(str) 将str全部转成大写
-
replace(str, old_str, new_str) 将str中old_str替换成new_str
-
concat(str1, str2, str3….) 将多个字符串拼接到一块,组成新的字符串
-
trim(str)/ltrim(str)/rtrim() 删除左右两边的空白
6.2 数值函数
-
abs() 绝对值
-
floor() 下行取整, 比它小的最大的整数,floor(1.1) 返回1
-
ceil() 上行取整, 比它大的最小的整数, 如ceil(1.1) 返回2
-
round(num, 小数位数) 四舍五入, 如 round(1.4, 2) 返回1.40
6.3 日期函数
-
dayofweek(date) 返回日期是星期几(1=星期日, 7=星期六)
-
weekday(date) 返回日期是星期几(0=星期一, 6=星期日)
-
dayofmonth(date) 返回日期是月中的第几天
-- 查询18日支出的所有记录的编号、银行编号和出支金额 select id,back_id, order_date, out_amount from t_order where dayofmonth(order_date)=18;
-
monthname(date) 返回日期中月的名称
-
dayname(date) 返回日期的星期几的名称
-
month(date) 返回日期中的月,year(date) 返回日期中的年
-- 查询19年的6月份的所有收入 select id, back_id, in_amount from t_order where month(order_date) = 6 and year(order_date) = 2018 and in_amount is not null;
-
时间差的函数
-
date_add(date, INTERVAL expr TYPE) 日期或日时相加,expr 表达式, INTERVAL是固定关键字, TYPE可选类型
DAY 天, HOUR 小时, MINUTE 分钟, SECOND 秒 , MINUTE_SECOND 分钟:秒 HOUR_MINUTE 小时:分钟 DAY_SECOND 天 小时:分钟:秒 MONTH 月 YEAR 年
-
date_sub(date, INTERVAL expr TYPE) 日期或时间相减。
-- 查看5个小时30分钟之后的时间 select date_add(current_date, interval '5:30' HOUR_MINUTE);
-
6.4 分支函数
-
if(条件表达式, 为True值, 为False值) 函数, 返回第二个或第三个表达式的值。
-- 现有一张A表,表结构如下: +------+-------+--------+ | year | month | amount | +------+-------+--------+ | 1991 | 1 | 1.1 | | 1991 | 2 | 1.2 | | 1992 | 1 | 2.1 | | 1992 | 2 | 2.2 | +------+-------+--------+ -- 查询结果如下: +------+-------+--------+ | year | m1 | m2 | +------+-------+--------+ | 1991 | 1.1 | 1.2 | | 1992 | 2.1 | 2.2 | +------+-------+--------+ -- 写出SQL语句 select year, round(if(month='1', amount, 0),1) m1, round(if(month='2', amount, 0),1) m2 from A +------+-------+--------+ | year | m1 | m2 | +------+-------+--------+ | 1991 | 1.1 | 0 | | 1991 | 0 | 1.2 | | 1992 | 2.1 | 0 | | 1992 | 0 | 2.2 | +------+-------+--------+ -- 使用分组语句group by select year, max(round(if(month='1', amount, 0),1)) m1, max(round(if(month='2', amount, 0),1)) m2 from A group by year; +------+------+------+ | year | m1 | m2 | +------+------+------+ | 1991 | 1.1 | 1.2 | | 1992 | 2.1 | 2.2 | +------+------+------+
-
case 语句
语法-1 等值条件:
select ..., case 字段名 when 可能性值1 then 结果1 when 可能性值2 then 结果2 ... else 其它结果 end as 别名 from 表名;
语法-2: 非等值条件
select ..., case when 条件表达式1 then 结果1 when 条件表达式2 then 结果2 ... else 其它结果 end as 别名 from 表名;
使用case实现等级的设置
-- 查询账单的等级情况 -- 如果支出超过50万,等级黄色警告 -- 如果支出超过100万,等级红色警告 -- 如果支出超出120万,非法操作 select id, back_id, out_amount, case when out_amount>50 then '黄色' when out_amount>100 then '红色' when out_amount>120 then '非法' else '正常' end as '支出等级' from t_order; +----+---------+------------+--------------+ | id | back_id | out_amount | 支出等级 | +----+---------+------------+--------------+ | 1 | 1 | NULL | 正常 | | 2 | 2 | NULL | 正常 | | 3 | 1 | 62 | 黄色 | | 4 | 2 | 50 | 正常 | | 5 | 3 | NULL | 正常 | | 6 | 3 | NULL | 正常 | +----+---------+------------+--------------+
-- 将t_order表中的back_id映射成一个是否推荐的状态(1:推荐, 2: 不推荐) select id, back_id, out_amount, case back_id when 1 then '推荐' when 2 then '不推荐' end as '是否推荐' from t_order;
6.5 其它函数
-
uuid() 生成一个uuid字符串
-
md5() 生成指定字符串的md5签名
范式:
第一范式(1NF)规范要求:所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,相当于与python中的不可变数据,而不是变量。第一范式是关系模式的设计基本要求。
第二范式:(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分。简单理解就是表中必须有一个唯一能够区分实例的属性,必须有一个主键以避免冗余。
第三范式:第三范式(3NF)是第二范式(2NF)的一个子集,即满足第三范式(3NF)必须满足第二范式(2NF)。要求一个关系中不包含已在其它关系已包含的非主关键字信息。
简单理解为:非主键信息只能出现在一个表中,不能在第二个表中出现。同过主键来联系各表。
结构化数据语言类型
数据定义语言:DDL 包括CREATE、DROP、ALTER等语句
数据操作语言:DML 包括 INSERT(插入)、UPDETA(修改)、DELETE(删除语句)
数据查询语言:DQL 包括SELECT 用于从表中获取数据确定数据怎样在应用程序中给出。
数据控制语言: DCL 通过GRANT或REVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
事物处理语言: 他的语句能够被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT和ROLLBACK。
指针控制语言:我们用不到,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。