MySQL数据库
重点:
特别说明的类型如下:
decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为'ab '
varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab'
字符串text表示存储大文本,当字符大于4000时推荐使用
对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
约束
(主键和唯一,外键用drop 去掉,格式:ALTER TABLE users DROP PRIMARY KEY;)
(其他用modify,格式:ALTER TABLE users MODIFY id INT;(不写就是删除))
主键primary key:物理上存储的顺序(可以多表联合主键)(可以后期加)
非空not null:此字段不允许填写空值 (可以后期加)
惟一unique:此字段的值不允许重复(可以多表)(可以后期加)
默认default:当不填写此值时会使用默认值,如果填写时以填写为准 (可以后期加)
外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
说明:虽然外键约束可以保证数据的有效性,但是在进行数据的crud(增加、修改、删除、查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制
自增 可以后期加
1、from 找到表
2、where 拿着where指定的约束条件,去文件/表中取出一条条记录
3、group by 将取出的一条条记录进行分组group by ,如果没有group by ,则整体作为一组
4、select 执行select
5、distinct 去重
6、having 将分组的结果进行having过滤
7、order by 将结果按照条件排序:order by desc; order by asc;
8、limit 限制结果的显示条数
MySQL的使用
MySQL服务的启动
命令行
net start MySQL服务名
net stop MySQL服务名
客户端连接MySQL
1、命令行客户端:
mysql -h 主机IP地址 -P 端口号 -u 用户名 -p回车
Enter Password:密码
如果访问本机,-h localhost可以省略
如果端口号没有修改,-P 3306可以省略
除了-p与密码之间不要空格外,其他的-h,-P,-u与后面的参数值之间可以有空格
想要连接成功,必须保证服务开启的
SQL语言
SQL语言的分类
DDL:数据定义语言,定义库,表结构等,包括create,drop,alter等
DML:数据操作语言,增删改查数据,包括insert,delete,update,select[DQL:数据查询语言]等
DCL:数据控制语言,权限,事务等管理。
MySQL的语法规范和要求
(1)mysql的sql语法不区分大小写
MySQL的关键字和函数名等不区分大小写,但是对于数据值是否区分大小写,和字符集与校对规则有关。
(2)命名时:尽量使用26个英文字母大小写,数字0-9,下划线,不要使用其他符号
(3)建议不要使用mysql的关键字等来作为表名、字段名等,如果不小心使用,请在SQL语句中使用`(飘号)引起来
(4)数据库和表名、字段名等对象名中间不要包含空格
(5)同一个mysql软件中,数据库不能同名,同一个库中,表不能重名,同一个表中,字段不能重名
(6)标点符号:
必须成对
必须英文状态下半角输入方式
字符串和日期类型可以使用单引号’’
列的别名可以使用双引号"",给表名取别名不要使用双引号。取别名时as可以省略
如果列的别名没有包含空格,可以省略双引号,如果有空格双引号不能省略。
(7)SQL脚本中如何加注释
单行注释:#注释内容
单行注释:–空格注释内容 其中–后面的空格必须有
多行注释:/* 注释内容 */
MySQL数据类型
整型系列:xxxInt
浮点型系列:float,double
定点型系列:decimal
字符串类型:char,varchar(M),text
日期时间类型:year, date, datetime, timestamp
其他类型:bit, xxBlob, 枚举,集合等
DDL
1、查看所有数据库
show databases;
2、指定使用某个数据库
use 数据库名;
3、创建数据库
create database 数据库名;
create database 数据库名 charset 'utf8'; #在mysql中字符集名称不要使用utf-8
create database 数据库名 charset 'gbk';
4、删除数据库
drop database 数据库名;
5、查看某个库下的所有表格
show tables ; #前提是前面有use 数据库名;的语句
show tables from 数据库名;
6、创建表格
create table 【数据名.】表名(
字段名1 数据类型,
字段名2 数据类型,
....
);
7、删除表格
drop table 【数据库名.]表名称;
8、查看某个表结构
describe 【数据库名.]表名称;
desc 【数据库名.]表名称;
9、增加一列
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 first;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 after 另一个字段;
10、删除一列
alter table 【数据库名.]表名称 drop 【column】 字段名;
11、修改列数据类型
alter table 【数据库名.]表名称 modify 【column】 字段名 新数据类型;
12、修改列名
alter table 【数据库名.]表名称 change【column】 旧字段名 新字段名 新数据类型;
13、修改列的位置
alter table 【数据库名.]表名称 modify 【column】字段名 数据类型 first;
alter table 【数据库名.]表名称 modify 【column】字段名 数据类型 after 另一个字段;
14、修改表名称
alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;
3.5 DML
1、添加数据
insert into 【数据库名.]表名称 values(值列表);
#要求值列表的顺序、个数、类型,要与表格中的字段的顺序、个数、类型一一匹配
insert into 【数据库名.]表名称(部分字段列表) values(值列表);
#要求列表的顺序、个数、类型,要与前面的(部分字段列表)的顺序、个数、类型一一匹配
insert into 【数据库名.]表名称 values(值列表1),(值列表2)。。。;
insert into 【数据库名.]表名称(部分字段列表) values(值列表1),(值列表2)。。。;
2、修改数据
update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】;
如果没有加where条件,表示修改所有行,这个字段的值
3、删除数据
delete from 【数据库名.]表名称 【where 条件】;
如果没有where条件,表示删除整张表的数据;
truncate 【数据库名.]表名称;#删除整张表的数据,还可以使用这个语句,效率更高,但是它不能回滚
用delete删除整张表和用truncate删除整张表的数据的区别?
(1)truncate速度快
(2)truncate无法回滚
truncate因为底层是把表drop掉,然后新建了一张空表。
delete因为底层是一行一行删数据。
4、DQL简单查询
select * from 【数据库名.]表名称; #查询整张表的所有数据
select 字段列表 from 【数据库名.]表名称; #查询部分列表
select * from 【数据库名.]表名称 【where 条件】;
select 字段列表 from 【数据库名.]表名称 【where 条件】;
使用distinct可以对查询结果进行去重
约束与索引
2、根据约束的特点,分为几种:
- 键约束:主键约束、外键约束、唯一键约束
- Not NULL约束:非空约束
- Check约束:检查约束
- Default约束:默认值约束
- 自增约束
Mysql会在主键、唯一键、外键列上自动创建索引,其他列需要建立索引的话,需要手动创建。
其中主键删除,对应的索引也会删除
删除唯一键的方式是通过删除对应的索引来实现的
删除外键,外键列上的索引还在,如果需要删除,需要单独删除索引
DDL(v2.0)
1、查看某个表的约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
或
SHOW CREATE TABLE 表名;
2、主键约束:primary key
主键分为单列主键和复合主键:
主键的特点:(1)唯一并且非空(2)一个表只能有一个主键约束(3)主键约束名就叫做PRIMARY(4)创建主键会自动创建对应的索引,同样删除主键对应的索引也会删除。
(1)如何在建表时指定主键约束
create table 【数据名.】表名(
字段名1 数据类型 primary key ,
....
);
或
create table 【数据名.】表名(
字段名1 数据类型,
....,
primary key(字段名1)
);
或
create table 【数据名.】表名(
字段名1 数据类型,
字段名2 数据类型,
....,
primary key(复合主键字段列表)#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
);
(2)如何在建表后指定主键约束
alter table 表名称 add primary key (主键字段列表);
(3)如何删除主键约束
alter table 表名称 drop primary key;
3、唯一键约束:unique key
(1)唯一键约束特点
-
唯一键约束列允许为null。
-
同一个表可以有多个唯一约束。
-
唯一约束可以是某一个列的值唯一,也可以多个列组合值的唯一。
-
MySQL会给唯一约束的列上默认创建一个唯一索引。
-
删除唯一键只能通过删除对应索引的方式删除,删除时需要指定唯一键索引名
(2)如何在建表时指定唯一键约束
create table 【数据名.】表名(
字段名1 数据类型 primary key ,
字段名2 数据类型 unique key,
....
);
create table 【数据名.】表名(
字段名1 数据类型 primary key ,
字段名2 数据类型,
字段名3 数据类型,
....,
unique key(复合唯一字段列表)#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
);
(3)如何在建表后增加唯一键约束
alter table 表名称 add 【constraint 约束名】 unique 【key】 (字段名列表);
#如果没有指定约束名,(字段名列表)中只有一个字段的,默认是该字段名,如果是多个字段的默认是字段名列表的第1个字段名。也可以通过show index from 表名;来查看
(4)如何删除唯一键约束
ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意:如果忘记名称,可以通过“show index from 表名称;”查看
4、外键约束:foreign key
(1)外键特点
- 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
- 在创建外键约束时,如果不给外键约束名称,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。
- 当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是列名,不是外键的约束名。
- 删除外键时,关于外键列上的普通索引需要单独删除。
(2)要求
- 在从表上建立外键,而且主表要先存在。
- 一个表可以建立多个外键约束
- 从表的外键列,在主表中引用的只能是键列(主键,唯一键,外键),推荐引用主表的主键。
- 从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样
(3)约束关系:约束是针对双方的
- 添加了外键约束后,主表的修改和删除数据受约束
- 添加了外键约束后,从表的添加和修改数据受约束
- 在从表上建立外键,要求主表必须存在
- 删除主表时,要求从表从表先删除,或将从表中外键引用该主表的关系先删除
(4)5个约束等级
- Cascade方式:在父表上update/delete记录时,同步update/delete掉子表的匹配记录
- Set null方式:在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为not null
- No action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
- Restrict方式:同no action, 都是立即检查外键约束
- Set default方式(在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别
如果没有指定等级,就相当于Restrict方式
(5)如何在建表时指定外键约束
create table 【数据名.】从表名(
字段名1 数据类型 primary key ,
字段名2 数据类型 【unique key】,
....,
【constraint 外键约束名】 foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
#外键只能在所有字段列表后面单独指定
#如果要自己命名外键约束名,建议 主表名_从表名_关联字段名_fk
);
create table 【数据名.】表名(
字段名1 数据类型,
字段名2 数据类型,
....,
primary key(复合主键字段列表),#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
unique key(复合唯一字段列表),#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
#外键只能在所有字段列表后面单独指定
);
(6)如何在建表后指定外键约束
alter table 从表名称 add 【constraint 外键约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名) 【on update xx】[on delete xx];
(7)如何删除外键约束
ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
#查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#删除外键约束不会删除对应的索引,如果需要删除索引,需要用ALTER TABLE 表名称 DROP INDEX 索引名;
#查看索引名 show index from 表名称;
7、非空约束:not null
NOT NULL 非空约束,规定某个字段不能为空
(1)如何在建表时给某个字段指定非空约束
create table 【数据名.】表名(
字段名1 数据类型 primary key ,
字段名2 数据类型 【unique key】 【not null】,
....,
foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
#外键只能在所有字段列表后面单独指定
);
create table 【数据名.】表名(
字段名1 数据类型 【not null】,
字段名2 数据类型 【not null】,
....,
primary key(复合主键字段列表),#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
unique key(复合唯一字段列表),#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
#外键只能在所有字段列表后面单独指定
);
(2)如何在建表后指定某个字段非空
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL 【default 默认值】;
#如果该字段原来设置了默认值约束,要跟着一起再写一遍,否则默认值约束会丢失
(3)如何在建表后取消某个字段非空
ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;
#如果该字段原来设置了默认值约束,要跟着一起再写一遍,否则默认值约束会丢失
8、默认值约束:default
(1)如何在建表时给某个字段指定默认约束
create table 【数据名.】表名(
字段名1 数据类型 primary key ,
字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
....,
foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
#外键只能在所有字段列表后面单独指定
);
create table 【数据名.】表名(
字段名1 数据类型 【not null】 【default 默认值】,
字段名2 数据类型 【not null】 【default 默认值】,
....,
primary key(复合主键字段列表),#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
unique key(复合唯一字段列表),#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
#外键只能在所有字段列表后面单独指定
);
(2)如何在建表后指定某个字段的默认值约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】 【NOT NULL】;
#如果该字段原来设置了非空约束,要跟着一起再写一遍,否则非空约束会丢失
(3)如何在建表后取消某个字段的默认值约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 【NOT NULL】;
#如果该字段原来设置了非空约束,要跟着一起再写一遍,否则非空约束会丢失
9、检查约束:check
检查约束,mysql暂不支持
create table stu(
sid int primary key,
sname varchar(20),
gender char check ('男'or'女')
);
insert into stu values(1,'张三','男');
insert into stu values(2,'李四','妖');
使用枚举类型解决如上问题:
create table stu(
sid int primary key,
sname varchar(20),
gender enum ('男','女')
);
10、自增约束:auto_increment
(1)关于自增长auto_increment:
- 一个表最多只能有一个自增长列
- 自增长列必须是键列(主键列,唯一键列,外键列),并且要求非空。
- 自增列必须是整数类型
- InnoDB表的自动增长列可以手动插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值。
(2)如何在建表时指定自增长列
create table 【数据名.】表名(
字段名1 数据类型 primary key auto_increment,
字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
....
);
或
create table 【数据名.】表名(
字段名1 数据类型 primary key ,
字段名2 数据类型 【unique key not null】 auto_increment,
....
);
(3)如何在建表后指定自增长列
alter table 【数据名.】表名 modify 自增字段名 数据类型 auto_increment;
(4)如何删除自增约束
alter table 【数据名.】表名 modify 自增字段名 数据类型;
4.3 DML(v2.0)
1、如果某列有自增约束,怎么添加该字段的值
添加数据时,对于自增列
insert into 【数据库名.]表名称 values(值列表);#在值列表中,对应自增列可以赋值为null和0
insert into 【数据库名.]表名称(部分字段列表) values(值列表);#自增列在(部分字段列表)中不写就可以
2、如果某列有默认值约束,怎么添加、修改该字段的值
添加数据时,对于有默认值列
insert into 【数据库名.]表名称 values(值列表);#在值列表中,对应默认值列,如果想用默认值,用default
insert into 【数据库名.]表名称(部分字段列表) values(值列表);#对应默认值列,如果想用默认值,在(部分字段列表)中不写就可以
修改数据
update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】; #对应默认值列,如果想用默认值,写字段名 = default就可以
第五章 MySQL运算符
1、算术运算符
加:+
减:-
乘:*
除:/ div(只保留整数部分)
模:% mod
5.2、比较运算符
大于:>
小于:<
大于等于:>=
小于等于:>=
等于:= 不能用于null判断
不等于:!= 或 <>
安全等于:<=> 可以用于null值判断
5.3、逻辑运算符(建议用单词,可读性来说)
逻辑与:&& 或 and
逻辑或:|| 或 or
逻辑非:! 或 not
逻辑异或:^ 或 xor
5.4、范围
区间范围:between x and y
not between x and y
集合范围:in (x,x,x)
not in(x,x,x)
5.5、模糊查询(只针对字符串类型,日期类型)
like 'xxx'
如果想要表示0~n个字符,用%
如果想要表示确定的1个字符,用_
5.6、位运算符(很少使用)
左移:<<
右移:>>
按位与:&
按位或:|
按位异或:^
5.7、特殊的null值处理
函数
1单行函数**
- 只对一行进行变换,每行返回一个结果
- 可以嵌套
- 参数可以是一字段或一个表达式或一个值
6.1.1 字符串函数
函数 | 用法 |
---|---|
CONCAT(S1,S2,…,Sn) | 连接S1,S2,…,Sn为一个字符串 |
CONCAT_WS(s, S1,S2,…,Sn) | 同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上s |
CHAR_LENGTH(s) | 返回字符串s的字符数 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
INSERT(str, index , len, instr) | 将字符串str从第index位置开始,len个字符长的子串替换为字符串instr |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(s,n) | 返回字符串s最左边的n个字符 |
RIGHT(s,n) | 返回字符串s最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(【BOTH 】s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(【LEADING】s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(【TRAILING】s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1,s2 |
SUBSTRING(s,index,len) | 返回从字符串s的index位置其len个字符 |
- 举例1:大小写控制函数
函数 | 结果 |
---|---|
LOWER(‘SQL Course’) | sql course |
UPPER(‘SQL Course’) | SQL COURSE |
这类函数改变字符的大小写。
- 举例2:字符控制函数
函数 | 结果 |
---|---|
CONCAT(‘Hello’,‘World’) | HelloWorld |
SUBSTR(‘HelloWorld’,1,5) | Hello |
LENGTH(‘HelloWorld’) | 10 |
INSTR(‘HelloWorld’,‘W’) | 6 |
LPAD(salary,10,’*’) | *****24000 |
RPAD(salary,10, ‘*’) | 24000***** |
TRIM(‘H’ FROM ‘HelloWorld’) | elloWorld |
REPLACE(‘abcd’,‘b’,‘m’) | amcd |
6.1.2 数值函数/数学函数
函数 | 用法 |
---|---|
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回小于x的最大整数值 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根 |
POW(x,y) | 返回x的y次方 |
- 举例1:ROUND:四舍五入
ROUND(45.926, 2) --> 45.93
- 举例2:TRUNCATE:截断
TRUNCATE(45.926) --> 45
- 举例3:MOD:求余
MOD(1600, 300) --> 100
6.1.3 日期函数
函数 | 用法 |
---|---|
CURDATE() 或 CURRENT_DATE() | 返回当前日期 |
CURTIME() 或 CURRENT_TIME() | 返回当前时间 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期时间 |
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
WEEK(date) / WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFWEEK() | 返回周几,注意:周日是1,周一是2,。。。周六是7 |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
DAYNAME(date) | 返回星期:MONDAY,TUESDAY…SUNDAY |
MONTHNAME(date) | 返回月份:January,。。。。。 |
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2) | 返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔 |
DATE_ADD(datetime, INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
DATE_FORMAT(datetime ,fmt) | 按照字符串fmt格式化日期datetime值 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
其中:
(1)DATE_ADD(datetime,INTERVAL expr type)
表达式类型:
参数类型 | 参数类型 |
---|---|
YEAR | YEAR_MONTH |
MONTH | DAY_HOUR |
DAY | DAY_MINUTE |
HOUR | DAY_SECOND |
MINUTE | HOUR_MINUTE |
SECOND | HOUR_SECOND |
MINUTE_SECOND |
举例:
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH); #需要单引号
(2)DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)
格式符 | 说明 | 格式符 | 说明 |
---|---|---|---|
%Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
%M | 月名表示月份(January,…) | %m | 两位数字表示月份(01,02,03。。。) |
%b | 缩写的月名(Jan.,Feb.,…) | %c | 数字表示月份(1,2,3,…) |
%D | 英文后缀表示月中的天数(1st,2nd,3rd,…) | %d | 两位数字表示月中的天数(01,02…) |
%e | 数字形式表示月中的天数(1,2,3,4,5…) | ||
%H | 两位数字表示小数,24小时制(01,02…) | %h和%I | 两位数字表示小时,12小时制(01,02…) |
%k | 数字形式的小时,24小时制(1,2,3) | %l | 数字形式表示小时,12小时制(1,2,3,4…) |
%i | 两位数字表示分钟(00,01,02) | %S和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) | %a | 一周中的星期缩写(Sun.,Mon.,Tues.,…) |
%w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
%j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%u | 以数字表示年中的第几周,(1,2,3。。)其中Monday为周中第一天 | ||
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |
6.1.4 流程函数
函数 | 用法 |
---|---|
IF(value,t ,f) | 如果value是真,返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
- 举例1:
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;
- 举例2:
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "实发工资"
FROM employees;
- 举例3:
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;
6.1.5 其他函数
函数 | 用法 |
---|---|
database() | 返回当前数据库名 |
version() | 返回当前数据库版本 |
user() | 返回当前登录用户名 |
password(str) | 返回字符串str的加密版本,41位长的字符串 |
md5(str) | 返回字符串str的md5值,也是一种加密方式 |
6.2 分组函数
- 什么是分组函数
分组函数作用于一组数据,并对一组数据返回一个值。
- 组函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- **COUNT() **
可以对数值型数据使用AVG 和 SUM 函数。
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
COUNT(*)count(1返回表中记录总数,适用于任意数据类型。
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
•COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
-
问题:用count(*),count(1)谁好呢?
其实,对于myisam引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*)直接读行数,效率很低,因为innodb真的要去数一遍。
select语句的7大子句
7大子句顺序
(1)from:从哪些表中筛选
(2)on:关联多表查询时,去除笛卡尔积
(3)where:从表中筛选的条件
(4)group by:分组依据
(5)having:在统计结果中再次筛选
(6)order by:排序
(7)limit:分页
必须按照(1)-(7)的顺序编写子句。
having与where的区别?
(1)where是从表中筛选的条件,而having是统计结果中再次筛选
(2)where后面不能加“分组/聚合函数”,而having后面可以跟分组函数
order by
order by:
降序:desc
升序:用么默认,要么加asc
limit
limit:
limit m,n
m = (第几页 - 1)*每页的数量
n = 每页的数量
子查询
嵌套在另一个查询中的查询,根据位置不同,分为:where型,from型,exists型。注意:不管子查询在哪里,子查询必须使用()括起来。
1、where型
①子查询是单值结果,那么可以对其使用(=,>等比较运算符)
②子查询是多值结果,那么可对其使用(【not】in(子查询结果),或 >all(子查询结果),或>=all(子查询结果),<all(子查询结果),<=all(子查询结果),或 >any(子查询结果),或>=any(子查询结果),<any(子查询结果),<=any(子查询结果))
查询全公司最高工资的员工信息
select * from 员工表 where 薪资 = (select max(薪资) from 员工表);
select * from 员工表 where 薪资 > all(select salary from 员工表 where 员工姓名 in(...));
2、from型
子查询的结果是多行多列的结果,类似于一张表格。
必须给子查询取别名,即临时表名,表的别名不要加“”和空格。
查询每个部门的编号,名称,平均工资
select 部门编号, 部门名称, 平均工资
from 部门表 inner join (select 部门编号,avg(薪资) from 员工表 group by 部门编号) temp
on 部门表.部门编号 = temp.部门编号
3、exists型
查询那些有员工的部门
select 部门编号, 部门名称 from 部门表
where exists (select * from 员工表 where 部门表.部门编号 = 员工表.部门编号);
事务
10.1 事务概述
1、事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改,整个事务回滚(rollback)到最初状态。
2、事务的ACID属性:
(1)原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
(2)一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
(3)隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
(4)持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响
-- 缓存区 --> 增删改
-- ① 将影响存储在缓存区中 ② 提交(将缓存区的影响直接到表格持久化)
-- MySQL自动提交的机制
-- SSM -- java
-- 1. 将提交方式设置为手动提交
SET autocommit=FALSE;
UPDATE emp SET salary=26000 WHERE NAME='tom';
UPDATE emp SET salary=33000 WHERE NAME='rose';
-- 2. 提交
COMMIT;
-- 3. 回滚
ROLLBACK;