MySQL语句基础
DML(数据操作语言):inset、update、delete
DDL(数据定义语言):create、alter、drop、truncate
DCL(数据控制语言):grant、revoke
ENGINE= MyISAM,强制使用MyISAM存储机制。
ENGINE=InnoDB,强制使用InnoDB存储机制(默认)
DDL语句
常见的数据库对象:表(table)、数据字典、约束(constraint)、视图(view)、索引(index)、函数(function)、存储过程(procedure)、触发器(trigger)
//创建表
create table t_heimao(
t_name varchar(255),
t_age int,
t_profession varchar(255)
)
//创建t_zz与上表相同
create table t_zz as select * from t_heimao;
//增加t_emal列,及增加一个表属性
alter table t_heimao add t_email varchar(255);
//修改t_email类型为char
alter table t_heimao modify t_email char;
//删除t_email这列
alter table t_heimao drop t_email;
//重命名t_zz为t_xiaozhang
alter table t_zz rename to t_xiaozhang;
//将t_profession名改为t_zhiye
alter table t_heimao change t_profession t_zhiye varchar(255);
//删除表t_xiaozhang
drop table t_xiaozhang;
truncate表是清空表数据,保留表结构,相对于delete而言,速度要快很多,而且不像delete那样指定数据
//语法:清空T_heimao
truncate table t_heimao;
数据库约束
not null 非空,数据不能为空,默认为空
unique 唯一约束,数据不能重复
primary key 主键,本列可以唯一的标识这条记录
foreign key 外键,这条记录从于主表中的一条记录,主要用于保证参照完整性
check 指定一个布尔表达式,指定的列的值必须满足该条件,MySQL不支持,ORACLE可以用
对数据列的约束:单列,多列,建表的同时指定约束,建表后添加约束
例子
//建表后添加给t_heimao的t_name添加所有的非空约束
alter table t_heimao modify t_name varchar(255) not null;
//唯一约束不允许该列值重复,但是允许多个null存在
create table t_heimao(
t_id int,
t_name varchar(255) not null unique, --直接指定该列唯一非空
t_age int,
t_profession varchar(255),
unique(t_age),--表级添加唯一约束
constraint u_heimao unique (t_profession) --表级添加,指定了一个约束名为u_heimao
)
//primary key约束,一般用于id如上面,建表后添加
alter table t_heimao add primary key(t_id);--表级添加,可以添加多列
alter table t_heimao modify t_id int primary key;--为该列指定主键约束
//foreign key约束
//主表
create table t_teacher(
t_id int auto_increment,--auto_increment为内置函数,代表自增
t_name varchar(255),
primary key(t_id)
)
//从表,并添加外键约束,指定java_teacher外键为t_teacher表的t_id,虽然MySQL支持使用列级约束语法来建立外键约束,但是不会生效,应该使用表级约束语法。
create table t_student(
t_id int auto_increment primary key,
t_name varchar(255),
java_teacher int references t_teacher(t_id)
)
//改正
create table t_student(
t_id int auto_increment primary key,
t_name varchar(255),
java_teacher int,
foreign key(java_teacher) references t_teacher(t_id)
)
其它外键添加同上的约束,在表建好后也一样
//check约束,年龄当然大于0,输入数据为-1则会返回违反检查约束错误
create table check_test(
t_id int auto_increment primary key,
t_name varchar(25),
age int,
check(age > 0)
)
索引
目的就是加速对表的查询,通过快速路径访问方法来快速定位数据,减少磁盘的I/O
创建索引
自动:表上定义主键约束,唯一约束和外键约束时会自动添加
手动:通过create index…语句来创建
删除索引
自动:数据表被删除时,自动删除索引
手动:通过drop index…
//创建语法
create index index_name on table_name(column1,column2....);
//删除语法
drop index index_name on table_name
索引虽然可以加快查询,但是系统需要维护索引,所以需要一定的系统开销,不要乱加缩影
视图
只是一个或多个数据表中的数据的逻辑展示
//语法
create or replace view view_name as subquery(查询结果)
DML语句语法
insert into
//给t_heimao添加一行数据
insert into t_heimao(t_id,t_name,t_age,t_profession) values (1,"黑猫",22,"学生");
//使用子查询的数据来插入
insert into t_teacher(t_name) select t_name from t_heimao;
update
//标准更新
update t_teacher set t_name = "黑猫" where t_id = 1;
//t_id大于1的,将其姓名都改为"黑猫"
update t_teacher set t_name = "黑猫" where t_id > 1;
delete from
//删除t_teacher表中t_id=1的数据
delete from t_teacher where t_id = 1;
//删除t_teacher表中t_id>2的所有的数据
delete from t_teacher where t_id > 2;
单表查询
//查询所有行内容
select * from t_heimao;
//查询t_heimao中t_id > 3的所有数据
select * from t_heimao where t_id > 3;
//在select查询时只用算术运算符形成算术表达式,日期也可以加减,两列之间也可以,数据列实际可以当成一个变量
select t_id+5 from t_teacher;
//查询t_teacher中t_id * 3 > 4 的记录
select * from t_teacher where t_id*3>4;
//选择出t_name与‘xx’字符串连接后的结果
select concat(t_name,'xx') from t_teacher;
//如果不希望用列名做表头,可以用as来弄,例如
select t_name as "姓名" from t_heimao;
//当然可以区别名,查询过长需要用到
select t_id id from t_heimao;
//dual为虚拟表
select 5+4 from dual;--计算5+4
//distinct表示取出不同的数据,重复的话只会显示一条
select distinct java_teacher,t_student form t_student;
//特殊运算符
expr1 between expr2 and expr3
expr1 in (expr2,expr3....)
like 字符串匹配
is null 要求指定值为Null
//between in说明
select * from t_teacher where t_id between 2 and 4;--取出t_id在2到4之间的数据,实际会返回t_id=2,3,4的数据
select * from t_teacher where 2 between t_id and java_teacher;取出t_id小于2和java_teacher大于2的数据
//in说明
select * from t_teacher where t_id in(2,4);--取出t_id=2和4的数据
//like说明:主要用于模糊查询,例如查询所有姓"孙"的人,就要用到%:_下划线代表任意一个字符,%百分号代表任意多个字符,但是如果不希望将这两个符号当成通配符,就用/转义。
select * from t_student where t_name like '孙%';
select * from t_student where t_name like '__';
//is null说明,取出为空的数据,不要用=号判断,因为null = null ,返回Null,不反回true
select * from t_heimao where t_name is null;
//not and or 说明:优先级,所有比较运算符:1 not:2 and:3 or:4
select * from t_student where(t_id > 3 or t_name like '张_') and java_teacher > 1;
//order by说明,排序专用,默认是升序排列
select * from t_student order by java_teacher;
select * from t_student order by java_teacher desc,t_name;--先按着java_teacher进行降序排列,当java_teacher相同时,再按着t_name升序排列。
数据库函数
//返回t_name的长度
char_length(t_name)
//返回1.5的sin值
sin(1.5)
//给日期添加一定的时间
select date_add('2016-03-07',interval 2 month);
select adddate('2016-03-07',3);
//获取当前日期
select curdate();
//获取当前时间
select curtime();
//md5加密函数
select md5('testing');
//处理Null的函数
isnull(exp1,exp2):如果exp1为null,则返回exp2,否则返回exp1
nullif(exp1,exp2):如果exp1=exp2,返回null,否则返回exp1
if(exp1,exp2,exp3):类似三目运算符,exp1为true,不等于0切不为null,返回exp2,否则返回exp3
isnull(exp1):判断exp1是否为null,如果为Null则返回true,否则返回false
//MySQL的case函数
select t_name,case java_teacher
when 1 then 'java老师' //为1时返回java老师
when 2 then 'Ruby老师' //为2时返回ruby老师
else '其他老师'
end
from t_student;
//第二种用法
case
when condition1 then result1
when condition2 then result2
...
else result
end
condition为bool表达式,判断true还是false
分组和组函数
avg([distinct|all]exp)://计算平均值
count(*|[distinct|all]exp)://计算多行exp的总条数,distinct和*不能同时使用
max(exp):计算多行exp的最大值
min(exp):计算多行exp的最小值
sum([distinct|all]exp):计算多行exp的总和
//使用group by
select count(1) from t_student group by java_teacher;
--档java_teacher与t_name都相同时才会被当成一组
select count(*) from t_student group by java_teacher,t_name;
//having字句与where的区别
where不能过滤组,过滤组必须用having
不能在where字句中使用组函数,having字句才可使用组函数
//对组进行过滤
select * from t_student group by java_teacher having count(*) > 2;
多表链接查询
*SQL 92的链接查询*
//就写一个简单例子,s和t是别名,上面提到过
select s.*,t.t_name from t_student s,t_teacher t where s.java_teacher = t .t_id;
//外链接,*或+在左边是左外连接,在右边是右外连接
select s.*,t.t_name from t_student s,t_teacher t where s.java_teacher = t.t_id(*);右外链接
*SQL 99的连接查询*
//交叉连接
select s.*,t.t_name from t_student s cross join t_teacher t;
//自然连接,以同名列为连接条件,如果没有同名列,则效果同交叉连接
select s.*,t.t_name from t_student s natural join t_teacher t;
//using字句连接
select s.*,t.t_name from t_student s join t_teacher t using(java_teacher);--会出现错误,因为t_teacher中没有java_teacher这列
//on字句连接
select s.t_name,t.t_name from t_student s join t_teacher t on s.t_id = t.t_id;
//左 右 外连接
#右外连接
select * from t_student s RIGHT JOIN t_teacher t on s.java_teacher = t.t_id;
#左外连接
select * from t_student s LEFT JOIN t_teacher t on s.java_teacher > t.t_id;
#全外连接,连接条件是等值连接
select * from t_student s FULL JOIN t_teacher t on s.java_teacher = t.t_id;--出现错误,mysql并不是全外连接
子查询
就是嵌套查询
select * from (select * from t_student) t where t.java_teacher > 1;
//any all 用法
//any表示任一值,all表示所有值
select * from t_student s where s.id = any(select t_id from t_teacher);--all用法一样
集合查询
类似于数学中的交 差 并
//union 查询所有老师的信息和主键小于4的学生信息
select * from t_teacher union select t_id , t_name from t_student;
//minus mysql中不支持
select 语句 minus select 语句
//intersect mysql不支持该查询
select 语句 intersect select 语句
最后附一个以前写分页用到的查询
select * from table_name limit 5,10;表示查询记录行为6到15的语句,5表示从第5+1行开始,10表示查询多少条数据。