javaWeb学习笔记
MySQL数据库
JDBC可以完成一下三个基本动作
- 与数据库连接
- 执行SQL语句
获得SQL语句的执行结果
DBMS有一个数据字典（有时也被称为系统表），用于存储它拥有的每个事物的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也被称为元数据(metadata).
数据库级别
show databases;
drop database 数据库名；
create database[if not exists] 数据库名；
use 数据库名；
show tables;
desc 表名；
连接数据库
mysql -p-u root -h127.0.0.1
输入密码
执行SQL脚本
source D:\PDF书籍\JAVA\疯狂java讲义\codes\13\13.2\select_data.sql
SQL语句
1 DML Data Manipulation Language 数据操作语言 insert update delete
2 DDL Data Definition Language 数据定义语言 create alter drop truncate
3 DCL Data Controll Language 数据控制语言 grant revoke (无需程序员操作)
4 事务控制语句 主要有commit rollback savepoint三个关键字组成
关于 函数（function） 存储过程（procedure） 触发器（trigger）
1 function 完成一次特定的计算，具有一个返回值
2 procedure 完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
3 trigger 相当于事件监听器。当数据库发生特定事件后，触发器被触发，完成响应的处理
创建表
create table [模式名.]表名
(–可以有多个列定义
columnNam1 datatype [default expr],
)create table test
(test_id int,
–小数点数
test_price decimal,
test_name varchar(255) default ‘xxx’,
–大文本类型
test_desc text,
text_img blob,
test_date datetime
)
利用子查询建立数据表
create table hah
as
select * from user_inf;
修改表结构
alter table 表名
add(column_name1 datatype [default expr],
….
)alter table hehe
add(aaa varchar(255) default ‘xxx’)alter table 表名
modify column_name1 datatype [default expr] [first|after col_name];alter table hehe
modify hehe_id varchar(255);alter table 表名
drop column_namealter table hehe
drop aaa
删除表
drop table 表名
drop table wawa;
约束概述
- NOT NULL
- UNIQUE
- PRIMARY KEY:主键 指定该列的值可以唯一地标识该条记录
- FOREIGN KEY: 外键 指定该行记录从属于主表中的一条记录，主要用于保证参照完整性
- CHECK :检查 指定一个布尔表达式，用于指定对应列的值必须满足该表达式
1.not null
create table hehe(
hehe_id int not null,
hehe_name varchar(255) default ‘xyz’ not null,
—可以为空
hehe_gender varchar(2) null
)
2. unique
create table unique_test
(
test_id int not null,
—指定其中的一列为唯一约束
test_name varchar(255) unique
)create table unique_test2
(
test_id int not null,
test_name varchar(255),
test_pass varxhar(255),
unique(test_name),
—约束的名字叫 test2
constraint test2 unique(test_pass)
)
create table unique_test2
(
test_id int not null,
test_name varchar(255),
test_pass varxhar(255),
—约束的名字叫 test2
constraint test2 unique(test_pass,test_name)
)
3.primary key 相当于非空约束和唯一约束
create table primaru_test
(
test_id int primary key,
test_name varchar(255)
)
create table primary_test2(
test_id int not null,
test_name varchar(255),
constraint test2_pk primary key(test_id)
)
create table primary_test3
test_name varchar(255),
test_password varchar(255),
primary key(test_name ,test_password )
)
create table primary_test3
–建立主键约束，使用自增长
test_id int auto_increment primary key,
test_name varchar(255),
test_password varchar(255),
)
4.FOREIGN KEY
references 指定该列（foreingn key）参照的哪个主表，以及参照主表的哪一列
create table teacher_table1
(
teachar_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
create tavle student_table1
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int references teacher_table1(teachar_id)
create table teacher_table1
(
teachar_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
create tavle student_table1
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table1(teachar_id)
)
create table teacher_table2
(
teachar_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
create tavle student_table2
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
—–使用表级约束建立外键约束，指定外键约束的名为student_teacher_fk
constraint student_teacher_fk foreign key(java_teacher) references teacher_table2(teachar_id)
)
create table teacher_table3
(
teachar_password varchar(255),
teacher_name varchar(255),
primary key(teacher_id,teacher_password)
);
create table student_table3
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher_name varchar(255),
java_teacher_pass varchar(255),
foreign key(java_teacher_name ,java_teacher_pass )
references teacher_table3(teacher_name ,teachar_password )
);
create table teacher_table4
(
teachar_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
create tavle student_table4
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
—–使用表级约束建立外键约束，指定外键约束的名为student_teacher_fk
foreign key(java_teacher) references teacher_table4(teachar_id) on delete cascade
)
5.CHECK
create table check_test
(
emp_id int auto_increment,
emp_name varchar(255),
emp_salary decimal,
primary key(emp_id),
check(emp_salary>0)
)
索引
1.添加索引
create index index_name
on table_name (column[,column]..);
create index emp_last_name_idx
on employees(last_name);
2.删除索引
drop index 索引名 on 表名
视图
create or replace view 视图名
as subquerycreate or replace vie view_test
as
select teacher_nam from teacher_table
with check optiondrop view view_test
DML语句
- DDL主要操作数据库对象
- DML主要操作数据表里的数据
- DML主要完成一下三个任务
插入新数据
修改已有的数据
删除不需要的数据
insert into update deletefrom
insert into
insert into table_name [(column[,column…])]
values(value[,value…]);insert into teacher_table2(teacher_name)
values(‘xyz’)insert into teacher_table2
values(null,’abc’);insert into student_table2(student_name)
—-使用子查询的值来插入
select teacher_name from teacher_table2;
update
update table_name
set column1 = value1[,column2=value2]…
[where condition]update teacher_table2
set teacher_name=’tom’
where teacher_id > 1;
delete from
delete from table_name
[where conditon];delete from student_table
where teacher_id > 2;
select 语句和SQL函数
select column1,column2…
from 数据源
[where condition]
select teacher_id =5
from teacher_table;select * from teacher_table
where teacher_id * 3 > 4;
+ 和 concat进行字符串连接
select concat(teacher_name,’xxx’)
from teacher_table;
—给列起别名
select teacher_id + 5 as MY_ID –将显示的结果名改为MY_ID 并且ID号加5
from teacher_table;select teacher_id+5 MyID,teacher_name 老师名
from teacher_table
—给表起别名
select teacher_id +5 MY_ID,teacher_name ‘老师名’
from teacher_table t;
—-去掉重复行 distinct
select distinct sutent_name,java_teacher
from student_table;select * from student_table
where student_id between 2 and 4;select * from student_table
where student_id in(2,4)select * from student_table
where 2 in(student_id,java_teacher);
下划线（_）一个任意字符
（%)任意多个
select * from student_table
where student_name like ‘孙%’shelect * from student_table
–两个下划线代表两个字符
where student_name like ‘__’
转义字符 \ escape 显示转义
select * from student_table
where student_name like ‘_%’ escape ‘\’select * from student_table
where student_nameis nullselect * from student_table
where student_name like ‘__’ and student_id > 3;select * from student_table
where not student_name like ‘_%’select * from student_table
where (student_id >3 or studen_name > ‘张’）
and java_teacher >1;order by column1 [desc] ,column_name2… desc asc
select * from stuent_table
order by java_teacher desc,student_name;
数据库函数
单行函数 每一行单独起作用
多行函数
select char_length(teacher_name)
from teacher_tableselect sin(char_length(teacher_name))
from teachea_table;select ifnull (student_name,’没有名字’)；
from student_table;select student_name,case java_teacher
when 1 then ‘java老师’
when 2 then ‘Ruby老师’
else ‘其他老师’
end
from student_table;
分组和组函数 多行函数
select count(*)
from student_tableselect count(distinct java_teacher)
from student_tableslect sum(student_id)
from student_table;select avg(ifnull(java_teacher,0))
from student_table;select count(*)
from student_table
—将java_teacher列值相同的记录当成一组
group by java_teacher;select count(*)
from student_table
—将java_teacher,和student_name两列值相同的记录当成一组
group by java_teacher,student_name
- 不能在where子句中过滤组，where子句仅用于过滤行.过滤组必须使用having子句
- 不能在where 子句中使用组函数，having 子句才可使用组函数
select *
from student_table
group by java_teacher
having count(*)>2
多表连接查询和子查询
SQL92的连接查询
select s.*,teacher_name
–多个数据表，并指定表别名
from student_table s,teacher_table t
–使用where 指定连接条件
where s.java_teacher = t.teacher_id
外连接 连接
SQL99
cross jon 交叉连接
select t.* ,teacher_name
from student_table s
–笛卡尔积
cross join teacher_table t;select s.*,teacher_name
from student table s
—两个表中的同名列
natural join teacher_table t;select s.* ,teacher_name
from student_table s
join teacher_table t
using(teacher_id)select s.*，teacher name
from student_table s
join teacher table t
–使用On指定连接条件
on s.java_teacher =t.teacher_idselect s.*，teacher name
from student_table s
join teacher table t
–使用On指定连接条件
on s.java_teacher > t.teacher_id
左 右 外连接
select s.* ,teacher_name
from student_table s
full join teacher_table t
on s.java_teacher = t.teacher_idselect s.* ,teacher_name
from student_table s
right join teacher_table t
on s.java_teacher < t.teacher_idselect s.* ,teacher_name
from student_table s
left join teacher_table t
on s.java_teacher > t.teacher_id
子查询
select *
–把子查询当数据表
from(select * from student_table) t
where t.java_teacher > 1;select *
from student_table
where java_teacher >
(select teacher_id
from teacher_table
where teacher_name=’Yeeku’
);select *
from student_table
where student_id in
(select teacher_id from teacher_table);select *
from student_table
where student_id =
any(select teacher_id from teacher_table);select *
from student_table
where student_id >
all(select teacher_id
from teacher_table)select *
from student_table
where (student_id,student_name)
=any(select teacher_id,teacher_name
from teacher_table);
扫一扫
172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
