重温javase—MySQL数据库基础

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表示查询多少条数据。