MySQL数据类型
1、 数值类型
TINYINT 1
SMALLINT 2
MEDIUMINT 3
INT 4
BIGINT 8
float(n,m) n代表长度，m代表小数点后位数
double(n,m)
decimal(n,m)
/2、日期类型
date:日期
time:时间
datetime:日期时间
timestamp:时间戳
year：年
3、 字符串类型
char:定长字符串
varchar:可变长字符串
binary：定长二进制
varbinary：可变长二进制
blob:二进制
text:大对象，字符，文本
enum:枚举
set:集合
sql的分类
-
DDL(data definitation language):数据定义语言
数据库，表，视图等的创建和销毁 create，drop
2 DML(data manipulation language):数据操纵语言
数据的增删改操作
insert:添加
delete:删除
update:修改
3 DQL(data query language):数据查询语言
数据的查询
select
4 DCL(data control language):数据控制语言
授权和取消授权
grant,revoke
数据库的操作
-
创建数据库
a) 语法: create {database|schema} [if not exists] dbname; 创建数据库 create database if not exists mydb; -
销毁数据库
a) 语法:drop {database|schema} [if exists] dbname; 销毁数据库 drop database if exists mydb; []表示可省略
数据库表的操作
-
数据库中的数据存放在数据库表中，以二维数组的形式存在。
一行代表一条数据记录，称为记录。 一列代表同一域的数据，表示同一属性。称为字段。
DDL操作
a) 创建:create table [if not exists] tname;
创建数据库表
create table `user`(
userid int,
username varchar(20),
`password` varchar(20),
birthday date
);
ad) 查看表结构:desc tname;
ae) 修改表结构: alter
A. 添加字段
alter table tname add [column] col_name col_definition [after|first]
添加sex列(默认最后一列)
alter table `user` add sex varchar(10);
添加到指定列后
alter table `user` add sex varchar(10) after userid;
添加到首列
alter table `user` add sex varchar(10) first;
G. 修改字段
alter table tname change col_name new_col_name col_definition;
修改数据类型
alter table user CHANGE sex sex VARCHAR(10);
修改字段名称
alter table user CHANGE sex usex VARCHAR(10);
修改字段位置
alter table tuser change birthday birthday date after userid;
H. 删除字段
alter table `user` drop col_name;
删除sex列
alter table user drop sex;
I. 重命名
rename table tname to new_tname;
修改user表为tuser
rename table user to tuser;
J. 清空表
清空重置表所有数据，速度比delete快。自增将重置。
truncate table tuser;
完整性约束
a) 完整性即数据的准确性。
af) 实体完整性:保证记录是唯一的，不重复的。
主键约束:primary key
主键字段唯一且不能为空
Ø 创建表时声明主键
create table user(
userid int primary key, #主键
username varchar(20)
);
Ø 创建表时声明主键
create table user(
userid int,
username varchar(20),
primary key(userid)
);
Ø 修改表结构添加约束
alter table user add CONSTRAINT PK_USERID primary key(userid);
唯一约束:unique
唯一约束字段值不能重复
Ø 创建表时声明唯一约束
create table user(
userid int primary key,
username varchar(20),
card varchar(18) unique,
);
Ø 创建表时添加唯一约束
create table user(
userid int primary key,
username varchar(20),
card varchar(18),
unique(card)
);
Ø 修改表结构添加唯一约束
alter table user add CONSTRAINT UN_CAED unique(card);
主键自增:auto_increment
create table user(
userid int primary key auto_increment,
username varchar(20),
card varchar(18)
);
ag) 域完整性：保证列的数据正确性。
类型约束
非空约束：not null
create table user(
userid int primary key auto_increment,
username varchar(20) not null,
card varchar(18) not null
);
默认值约束
create table user(
userid int primary key auto_increment,
username varchar(20) not null,
sex varchar(10) default ‘男’,
card varchar(18) not null
);
ah) 引用完整性：保证引用关系(外键)的正确性，参照完整性
alter table emp add CONSTRAINT PK_DEPTNO foreign key(deptno) REFERENCES dept(deptno)
ai) 自定义完整性(check约束mysql不支持)
添加约束保证部门编号只能在1到120之间
alter table dept add CONSTRAINT CK_AGE check(deptno BETWEEN 1 and 120)
aj) 删除约束
alter table emp drop primary key;
alter table emp drop foreign key;
- 运算符
a) 算术运算符 + - * / div整除
select 1+2;
select 1-2;
select 3*4;
select 3/4;/*0.75*/
select 3/0;/*null*/
select 3 div 4;
ak) 比较运算符 >,<,>=,<=,!=,<>
Is true,is not true,is null,is not null,between and,not between and,,in, not in
select 1<>1;/*0*/
select 1!=1;/*0*/
select 1=1;/*1*/
al) 逻辑运算符 and or !
select 1=1 and 1=2;/*0*/
select !1<>1;/*1*/
select 1=1 or 1=2;/*1*/
am) 位运算符
select 1 | 1;
select 1^2;
select 1&0;
DML操作
a) 添加数据 insert
A. 插入数据
values中的数据值与表的字段位置一致,字符串插入需要使用引号
insert into user values(12,'zs','123');
K. 向特定字段位置插入数据
前提是某些字段允许空值操作
insert into user(userid,username) values(3,'ww');
L. 批量插入数据
insert into user values(1,'zs','123'),(2,'ls','123')
M. 复制已有的表
insert into user(id,name) select userid,username from tuser
an) 修改数据 update
update tname set col_name = col_value
使用update没有使用条件，将修改所有记录的值
update tuser set password = '234' where userid = 2;
ao) 删除数据 delete
使用delete语句，没有where条件将删除所有的数据
//删除所有数据是一行一行删除，因此比truncate速度慢，而且不会重置自增值。
delete from tuser where userid = 1;
DQL操作 select
select 的语法
select * from tbl_name
FROM 子句
where 子句
group by
HAVING
order by
limit
Ø 查询所有数据
select * from user
Ø 查询指定列的数据
select username,password from user
Ø 条件查询(单一):查询年龄>18的用户信息
select * from user where age > 18
Ø 条件查询(组合),多个条件使用and或or连接
查询年龄>18的所有女生
select * from user where age >18 and sex = '女'
Ø 别名(字段)
select userid [as] 编号,username [as] 名称 from user;
Ø 别名(表)
select u.userid,u.username from user u;
Ø 去重(distinct)
select distinct username from user;
Ø 模糊查询 like
%代表匹配0到多个字符
_代表匹配一个字符
查询以z打头的所有用户
select * from user where username like 'z%';
查询以s结尾的所有用户
select * from user where username like '%s';
查询包含s的所有用户
select * from user where username like '%s%';
查询第二个字为s的用户
select * from user where username like '_s%'
Ø 非空 is null
select * from user where sex is [not] null;
Ø 范围查询 between … and …:查询年龄在12和20之间的用户
select * from user where age BETWEEN 12 and 20;
等价于
select * from user where age >=12 and age <=20;
Ø 集合查询：查询用户id为1和2的用户
select * from user where userid in (1,2);
等价于
select * from user where age = 1 or age =2;
Ø 排序:按照年龄升序排序
select * from user order by age [asc]
按照年龄升序排序,如果年龄一样，按照编号降序排序
select * from user order by age,userid desc;
Ø 限制结果数量:用于分页查询 limit m,n:m代表开始索引(从0开始),n代表长度
select * from user limit 0,2;
函数
- 单行函数
a) 数学函数
Ø abs():绝对值
select abs(-1); //1
Ø ceiling()/ceil():向上取整,最接近并且大于等于该值的整数值
select ceil(12.5); //13
select ceil(-12.5); //12
Ø floor():向下取整,最接近并且小于等于该值的整数值
select floor(12.5); //12
select floor(-12.5); //13
Ø mode(m,n):m对n取模
select mode(5,3); //2
Ø PI():取PI的值
Ø pow(m,n):取m的n次方
select POWER(3,2); //9
Ø rand():取随机数
select rand(); //[0,1)之间获取随机数
Ø round(m,n):四舍五入
select round(3.5); //4
select ROUND(13.56,2); //13.56 从小数点后n为开始四舍五入
Ø truncate(m,n):截取m小数点后n位
select TRUNCATE(3.56,1); //3.5
ap) 字符函数
Ø ascii(str):获取str的ascii码值
select ASCII('a'); //97
Ø lower(字段|表达式):将字符串转换为小写
select LOWER('ABC'); //abc
Ø upper(字段|表达式):将字符串转换为大写
select UPPER('abc'); //ABC
Ø concat(str1,str2…):将字符串连接
select CONCAT('aa',2,'cc'); //aa2cc
Ø length(字段|表达式):获取字符串长度
select LENGTH('hello mysql'); //11
Ø substr(str,pos,len):截取字符串,pos开始位置，从1开始；len表示长度
select SUBSTR('my name is gardy_lee',4,4); //name
Ø replace(str,old,new):在str中搜索old，使用new代替
select REPLACE('my name is gardy_lee','my','your');//your name is grady_lee
Ø lpad(str,len,s):str长度不够len，使用s左侧填充
select LPAD('hello',10,'--'); //-----hello
Ø rpad(str,len,s):str长度不够len,使用s右侧填充
select RPAD('hello',10,'-'); //hello-----
Ø trim():去重左右两侧的空格
select trim(' hello'); //hello
aq) 日期函数
Ø NOW()/SYSDATE()/CURRENT_TIMESTAMP():获取当前日期时间
Ø CURRENT_DATE()/CURDATE():获取当前系统日期
Ø CURRENT_TIME()/CURTIME():获取当前系统时间
Ø DATE_ADD(date,INTERVAL expr unit):日期转换
select ADDDATE('1998-08-08',INTERVAL 2 YEAR); //2000-08-08
Ø DAY(date):获取天数
select DAY(‘1998-09-10’); //10
Ø MONTH(date):获取月份
select MONTH('1989-09-10'); //9
Ø YEAR(date):获取年份
select YEAR('1989-09-10'); //1989
Ø week(date):返回一年中的周数
select WEEK('2001-02-05'); //5
Ø weekday(date):返回一周中的第几天(0-6)
select WEEKDAY('2001-02-06'); //1 周二
-
聚合函数
Ø avg():平均值 Ø sum():获取总数 Ø max():获取最大值 Ø min():获取最小值 Ø count():统计数目 -
分组函数
Ø GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 Ø HAVING子句 用于指定 GROUP BY 子句检索行的条件 查询平均工资大于2000的部门编号 select deptno,avg(sal) avg from emp group by deptno having avg>=2000 -
加密函数
Ø select md5('root'); Ø select SHA('root'); Ø select PASSWORD('root');
高级查询
-
多表查询(关联查询，连接查询)
a) 笛卡尔积 emp表15条记录,dept表4条记录。 连接查询的笛卡尔积为60条记录。 ar) 内连接:不区分主从表，与连接顺序无关。两张表均满足条件则出现结果集中。 Ø where子句 select * from emp,dept where emp.deptno = dept.deptno Ø inner join…on… select * from emp inner join dept on emp.deptno = dept.deptno Ø inner join…using… select * from emp INNER JOIN dept using(deptno)
as) 自然连接:寻找两表中字段名称相等的字段进行连接，会自动去重重复列。
select * from emp NATURAL join dept;
at) 外连接:有主从表之分，与连接顺序有关。以驱动表为依据，匹配表依次进行查询；匹配表中找不到数据，则以null填充。
A. 左外连接:left [outer] join…on…
select * from emp
LEFT JOIN dept
on emp.deptno = dept.deptno
N. 右外连接:right [outer] join…on…
select * from dept
LEFT JOIN emp
on emp.deptno = dept.deptno
-
子查询:即嵌套查询，将一个查询结果作为另一个查询条件或组成部分的查询。
Ø 单行子查询:查询工资大于7788号员工的所有员工信息 select * from emp where sal >(select sal from emp where empno =7788); Ø 多行子查询: 返回多值可以使用any或all来修饰。 =any相当于in,<any小于最大值，>any大于最小值； <>all相当于not in,>all大于最大值，<all小于最小值。 Ø 查询超过所在部门平均工资的员工信息 select * from emp e1 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno); Ø 查询薪水大于2000的部门名称 select dname from dept d where deptno in (select deptno from emp e where sal > 2000); 或 select dname from dept d where EXISTS (select * from emp e where sal > 2000 and d.deptno=e.deptno);
Ø in和exists的区别
in先执行子查询，在执行主查询;exists先执行主查询;
exists子查询不返回具体结果，返回true值出现在结果集，否则不出现。
-
集合查询
a) UNION：并集，所有的内容都查询，重复的显示一次 au) UNION ALL：并集，所有的内容都显示，包括重复的 select * from emp where deptno = 20 UNION ALL select * from emp where sal <=2000;
514
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
