<font size=6 color="#0000FF"> MYSQL </font>
navicat premium
use databases;
show tables;
show databases;
use test;
desc student;
-- 简单显示表结构信息
describe student;
-- 详细显示表结构信息
show create table student;
CREATE TABLE `student`()ENGINE = InnoDB (存储引擎) DEFAULT CHARSET=utf8
MyISAM 不支持事务，支持表级别的锁，锁粒度大
InnoDB 支持事务，支持锁粒度小，既有表锁又有行级别的锁
两种存储引擎的区别：
数据库里面的文件系统，规定了数据怎么存，怎么处理
InnoDB，适合处理电商银行的数据存储。
MyISAM，适合日志（读比写多的地方）（新项目几乎不用）
use test;
-- 给表改名字
alert table studnet rename student_new
-- 给表中的字段改名字（同时还可以把字段对应的类型改了）
alert table student change birthday birthday_new varchar(255);
desc student;
-- 给表添加字段，添加到最后
alter table student
add addr varchar(500)
-- 删除表中的字段
alter table student;
drop addr;
-- 将字段加到score字段后面
alter table student
add addr varchar(500)
after score;
alter table student
modify addr varchar(600) comment '家庭住址';
类似：`addr` varchar(600) DEFAULT NULL COMMENT '家庭住址'
show create table studnet;
insert into person(id,name,salary) values(2,'xx',1.0);
delete from person where name = 'a';
update person set salary = salary + 10,addr = 'yy' where id = 3;
alter table person
add primary key(id);
-- PRIMARY KEY(`id`);(这句在属性的后面)
id int auto_increment, //自增id
name varchar(100),
primary key(id)
delete from person where id in (3,4,5);
CREATE TABLE `person`(
`id` int(11) NOT NULL AUTO_INCREMENT,
primay key(id)
)ENGINE=InnoDB AUTO_INCREMENT=5 //已经即将自增到5了
select database();
create table student(
id int,
name varchar(20),
teacher_id int
);
create table teacher(
id int,
name varchar(20)
);
alter table student
modify teacher_id decimal(18,2)
alter table student
modify teacher_id int
alter table teacher
add PRIMARY key(id)
-- 添加外键
alter table student
add FOREING key (teacher_id) REFERENCES teacher(id);
主键跟外键都会有索引
KEY 代表索引
KEY `teacher_id`(`teacher_id`)
select char_length('中国abc'); -- 数字符的个数
select length('中国'); -- utf8 一个汉字占3个字节
select concat('a','b','c'); -- abc 拼接参数
select concat_ws('=','a','b','c'); -- a=b=c 使用第一个参数作为中间符放入后面参数
select upper('aBcd'); -- 大写
select lower('ABcd'); -- 小写
select substring('系统信息类',1 ,3) -- 截取
select trim(' abc. '); -- 去前后空格
select curdate(); -- 当前日期对应的年月日
select now(); -- 当前日期到秒
select sysdate(); -- 一样，当前日期到秒
-- 返回当前日期时间对应的时间戳（单位：妙）
select unix_timestamp();
select unix_timestamp(curtime());
-- 将时间戳转回日期时间到秒
select from_unixtime(123213213123);
-- 只能到天，-2表示在now()基础上减2天,3的话就是加3天
select adddate(now(),-2);
select if(1=1,'成立','不成立');
-- 如果有null，我就给它添一个值
use test;
select id,name,ifnull(age,0) as age from person_2;
create table student_info(
id int,
name varchar(20),
score decimal(18,2)
);
insert into student_info(id,name,score)
values (1,'小红',99.8)
X4
-- 80以上 A
-- 70-80 B
-- 60-70 C
-- <60 D
create view stu_score as
select id,name,score,
(
case
when score >= 80 then 'A'
when score >= 70 and score < 80 then 'B'
when score >= 60 and score < 70 then 'C'
when score < 60 then 'D'
end
) as rank from student_info;
select id,name,score,rank,
(
case rank
when 'A' then '优'
when 'B' then '中'
when 'C' then '良'
when 'D' then '差'
end
) as ch_rank from stu_score;
-- <> 不等于
select * from stu_score where rank <> 'B'
select * from person where age is null
select * from student_info where name like '%小%'
-- 如果student_id相同，则接下来按score降序继续排序
select distinct name from person order by student_id asc,score desc limit 30
use cm;
select database();
select count(*) from score
group by cno; //not in 清洗重复数据
select cno,avg(degree) as chengji
from score
group by cno;
use test;
select id,name,
(select name from teacher where id = teacher_id) as teacher_name
from student;
-- 子查询第三种场景，跟在where后面，通常和in搭配
use test;
select * from student
where teacher_id = (
select id from teacher
where name = '王老师'
);
-- 这种写法会先对连接的所有的表做一次完全笛卡尔积
-- 如果表很大，这种写法非常耗资源（内存）
select * from student,teacher
where student.teacher_id = teacher.id;
-- 规范写法 (第一行的笛卡尔积不满足条件也就出现不了)
select s.id,s.name,t.name as teacher_name from student s
inner join teacher t on t.id = s.teacher_id //同名字段就选一个起别名
select s.id,s.name,t.name as teacher_name from student s
left join teacher t on t.id = s.teacher_id //一般最重要的表放到左边
//优先使用inner，因为其性能好。如果有丢数据就改为left或其它的
性能优化
慢查询原因（通常DBMS都有自己的慢查询日志）
外部原因：内存太小，本地I/O瓶颈，网络I/O瓶颈。
内部原因：程序本身DB设计不合理，SQL语句使用不合理，无索引或者有索引但未充分利用。
学会使用explain分析简单的查询
数据库设计层面的优化
遵循数据库设计三范式（通俗地讲就是每个字段不可再拆分，尽量减少冗余字段）；
字段类型设计上，能使用数值就不要使用字符串，能使用日期时间就不要使用字符串，最好把字段声明为not null default 默认值； //字符串需要先查字符集转换编码，所以慢
//如果设计成允许null，数据库需要留选一段空间来标识其为null
为了避免表连接查询，必要的冗余字段是可以设置的；
能提前建立的索引要提前建好（经常用在where，group by，order by中的字段最好建索引）
创建索引方式一：alter table student_score add key idx_sc_s_c(sname,cname);
删除索引方式一：alter table student_score drop key idx_sc_s_c;
创建索引方式二：create index idx_sc_c on student_score(cname);
删除索引方式二：drop index idx_sc_c on student_score;
explain select * from student_score;
show create table student_score;
explain select * from student_score where sname = '小王';
加索引会降低写的性能
-- 添加索引
-- alter table student_score
-- add key idx_sc_s (sname);
alter table student_score
add key idx_sc_s_c (sname,cname);
索引命名方式：idex_sc_s sc：哪张表 s,c：那个字段
SQL语句使用层面的优化
尽量不使用select *,而是要具体指定字段，比如select id,name...;
尽量不使用不等于<>;
不使用is null/is not null (虽然也会使用索引，但是性能损耗是由于default null的字段要比not null的字段多出额外的存储空间来标识这个字段的值是不是null)；
不使用or连接不同的字段；
不使用not in；
不在条件字段上使用函数；
不使用前置模糊查询(like '%a');等。
因为上面的使用方式都会产生全表扫描(当然，如果实在没办法优化，全表扫描就扫描吧)
索引优化
建立索引的字段从内容上要有差异要有区分度。
索引提升的是读性能，如果一张表的写操作更多，则尽量不建或者少建索引。
使用where，group by，order by时，尽量充分利用建立索引的字段
数据导出导入
//不要使用客户端倒
mysql/bin/mysqldump.exe //往外倒
mysql/bin/mysqlimport.exe //往里倒
use test;
select sname,
max(
case cname
when 'Java' then score
else 0.0
end
) as Java,
max(
case cname
when 'MySQL' then score
else 0.0
end
) as MySQL
from student_score group by sname;
-- group_concat()把每条记录取出来拼一块,separator分割的是每一行的数据,order by 要放在separator前面而不是后面
select sc.*,group_concat(canme,'=',order by cname score separator '|') as gc from student_score sc group by sname;
JDBC
Build Path->Configure Build Path...
lib->xxx.jar
Add JARs...
JDBC相关的类与接口
java.sql.Driver 接口
java.sql.DriverManager类 //注册驱动 ctrl+o 搜索查看方法
Connection getConnection
java.sql.Connection接口
void close()
Statement createStatement()
PreparedStatement prepareStatement(String sql
java.sql.Statement 接口
void close()
boolean execute(String sql)
ResultSet executeQuery(String sql)
int executeUpdate(String sql)
void addBatch(String sql) //接受多条sql语句
int[] executeBatch()
java.sql.PreparedStatement 接口 extends java.sql.Statement
void setXXX(int parameterIndex,xxx value)
ResultSet executeQuery()
int executeUpdate()
java.sql.ResultSet 接口 //查询的结果组成的二维表
void close()
boolean next()
xxx getXXX(String columnName)
test(){
String className = "";//com.mysql.jdbc.Driver
Class.forName(className);
DriverManager.getConncetion(url,user,password);
//url : jdbc:mysql://127.0.0.1:3306/test //类似sql: use test;
Statement stmt = conn.createStatement();//创建一个语句对象
StringBuffer sql = new StringBuffer();
sql.append(" ");//后面多一个空格以免不必要的麻烦
sql.append(" ");
stmt.execute(sql.toString());
for(int i = 0 ; i < 10 ; i ++){
StringBuffer sql = new StringBuffer();
sql.append("");
sql.append(""+i+"");
stmt.addBatch(sql.toString());//加入批处理
}
int[] res = stmt.executeBatch();
StringBuffer sql = new StringBuffer();
sql.append("");
ResultSet rs = stmt.executeQuery(sql.toString());
while(rs.next()){
int id = rs.getInt("id");
}
sql.append("insert into employee (id,name,salary) ");
sql.append("values (?,?,?); ");
PrepardStatement stmt = conn.prepareStatement(sql.toString()); //为了防止sql注入而开的接口，预编译语句对象，服务端编译一部分，客户端编译一部分
stmt.setInt(1,10);
stmt.setString(2,"小红");
stmt.setDouble(3,9.0);
stmt.executeUpdate();
stmt.close();
conn.close();
}
main(){
try{
test();
}catch(Exception e){
}
}
//演示SQL注入
public interface LoginAuth{
boolean login(String username,String password);
int modifyPassword(String newPwd,String username);
}
public class LoginAuthTest{
private static LoginAuth la;
public static void testPrepared(){
la = new LoginAuthPreparedImpl();
}
public static void testStmt(){
la = new LoginAuthStmtImpl();
}
main(){
testStmt();
//testPrepared();//安全实现
String username = "lhl";
String password = "123";
boolean flag = la.login(username,password);
if(flag){
sout("登陆成功!");
}
}
}
public class LoginAuthStmtImpl{
public boolean login(){
}
public int modify(){
String username = "' or 1=1 or '1'='1";
sql.append("update user_info ");
sql.append("set password = '" + newPwd + "' ");
sql.append("where username = '" + username +"'")；
//把where变为true
//' or 1=1 or '1'='1
stmt.extcuteUpdate(sql.toString());
}
}
public class LoginAuthPrepareImpl{
public int modify(){
String username = "' or 1=1 or '1'='1";
sql.append("update user_info ");
sql.append("set password = '" + newPwd + "' ");
sql.append("where username = '" + username +"'")；
//把where变为true
//' or 1=1 or '1'='1
stmt.extcuteUpdate();
}
}
//基本都是在修改上做注入
Properties 文件的解析
涉及到的类是java.util.Properties
常用方法有
void load(Reader reader)
String getProperty(String key)
String getProperty(String key,String defaultValue)
Properties prp = new Properties();
String src = "xxx" + File.separator + "jdbc.properties";
prp.load(new InputStreamReader(new FileInputStream(src),"utf-8"));
String className = prp.getProperty("className");
连接池：
//数据库连接用完之后进入池子里，而不需要再次创建
DBCP连接池 的操作框架是MyBatis
lib
commons-dbcp2-2.2.0.jar
commons-logging-1.2.jar
commons-pool2-2.5.0.jar
public static void testDBCP() throws Exception{
//DataBase Connection Pool
//导入jar包
//创建连接池对象
BasicDataSource bds = new BasicDataSource();
//加载并配置连接信息className,url,user,password
Properties p = new Properties();
p.load(new FileReader("xxx/xxx/jdbc.properties"))
bds.setDriverClassName("com.mysql.jdbc.Driver");
//bds.setUrl("jdbc:mysql://192.168.56.101:3306/lhl_test");
bds.setUrl(p.getProperty("url"));
bds.setUsername("root");
bds.setPassword("root");
Connection conn = bds.getConnection();//连接池会把toString重写
sout(conn);
conn.close();
}
//连接池单例化
public class Singleton{
private BasicDataSource bds;
private static Singleton st;
private Singleton(){
bds = new new BasicDataSource();
//加载并配置连接信息className,url,user,password
Properties p = new Properties();
p.load(new FileReader("xxx/xxx/jdbc.properties"))
bds.setDriverClassName("com.mysql.jdbc.Driver");
//bds.setUrl("jdbc:mysql://192.168.56.101:3306/lhl_test");
bds.setUrl(p.getProperty("url"));
bds.setUsername("root");
bds.setPassword("root");
}
//如果调一次锁一次是很耗性能的，所以不加在方法上，加在方法里面
public static Singleton getInstance(){
if(st == null){
synchronized (Singleton.class){
if(st == null){
st = new Singleton();
}
}
}
return st;
}
public BasicDataSource getDbs(){
return dbs;
}
}
//Singleton只会new一次，因此两者连接起来就是放在Singleton方法中
项目自动化构建
make(Makefile) 通常用于UNIX/Linux上软件的安装。自动化只局限于打包发布安装的阶段。依赖于OS平台命令，用于生成Makefile的configure文件依然需要开发者手动编写，耗时间。
Ant(build.xml) 可以认为是java版本的make。基于Java开发，只要安装了JDK/JRE就能使用，跨平台，编写build.xml的工作量相对较少，自动化也只是局限于打包发布阶段。
Maven(pom.xml)和Ant一样基于java开发，但是功能强大，修改pom.xml可以自动化支持软件生命周期的几乎所有过程（编译，测试，打包发布，安装）
jar包依赖管理
apache-maven可以放到和jdk同一目录下
环境变量：MAVEN_HOME ,win10的改成M2_HOME , path
mvn 查看
Maven配置：
只需要改仓库位置即可
如果不改的话，会放在 ~/.m2/repository这个目录下，所以要修改
<settings xmlns= //根标签settings
xmlns:xsi=
xsi:schemaLacation= //这个是其遵循的约束的位置
> //xmlns就是xml的规范文件位置
<localRepository>D:\\workspace\\maven\\repository</localRepository>
将settings.xml 复制到.m2下
Preferences->Maven
->User Settings->Global path (这个要与安装目录下的settings保持一致)
User path (与根目录下settings保持一致)
->Installations->Add->Directory..->path: 安装目录到文件夹就行
勾选这个文件夹
clean 是把bin下的文件都删掉，可以勾选clean完再构建这个选项
source folder 与 folder 区别：在构建时source文件夹里的文件会编译成class文件并输出到target文件夹里
new->maven.project->group 组名 ；artifact 项目名
<build>
maven-compiler-plugin 2.3.2
</build>
String src = "jdbc.properties";
p.load(new InputStreamReader(JDBCUtil.class.getClassLoader().getResourceAsStream(src),"utf-8"));
仓库存放位置会以groupId/artifactId/version/来存储
maven资源查找顺序->私服->中央仓库->到其他公共仓库找
阿里云公开的自己的私服maven仓库
<!-- https://mvnrepository.com/artifact/junit/junit -->
<!--
建议把测试代码与程序主代码分离
测试代码通通都迁移到src/test下面
这个目录下的代码不是使用Main方法机制运行的
所以要添加Junit组件
src/test中的内容最终是不会被打包发布的
-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope><!-- 保证Maven最终打包发布的时候不会把这种jar包打到最终包里面,即junit这个jar包仅是测试用的不会打到最终包里 -->
</dependency>
@Test
public void test1(){
}
pom.xml->maven build... ->Goals:clean test ->Run 这样所有的测试方法就会自动跑一遍
mvn clean test
<!-- 如果是在cmd看输出结果的话，下面这个配置就不能加了，否则会乱码 -->
<properties>
<argLine>-Dfile.encoding=UTF-8</argLine>
</properties>
Maven命令
mvn clean
mvn clean compile
mvn clean test
mvn clean package
mvn clean install
mvn clean test
/surefire
/surefire-reports maven测试生成的文件夹
mvn clean
mvn clean compile
Maven打包提供给普通的项目用
mvn clean package
Maven打包提供给Maven Project使用
1.本地跨Maven项目使用
不需要修改pom.xml
mvn clean install
2.跨机器给别人的Maven Project使用
Maven 打包-把jar包当作独立程序直接运行
<build>
<plugin>
org.apache.maven.plugins
maven-compiler-plugin
</plugin>
<plugin>
org.apache.maven.plugins
maven-assembly-plugin
<configuration>
<descripttorRefs>
<descripttorRef>jar-with-dependecies</descripttorRef>
</descripttorRefs>
<archive>
<manifest>
<mainClass>xx.x.x.x</mainClass>
</manifest>
</archive>
</configuration>
</plugin>
</build>
------------------------------------------------------------
------------------------------------------------------------
多表查询
给product中的这个cno添加一个外键约束
alter table product add foreign key(cno) references category(cid)
一对多：商品和分类
原则：在多的一方添加一个外键，指向一方的主键
多对多的建表：
多建一张中间表，将多对多的关系拆成一对多关系，中间表至少要有两个外键，这两个外键分别指向原来的那张表
一对一建表：
原则：
将一对一的情况，当作是一对多情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表
直接将两张表合并成一张表
将两张表的主键建立起连接，让两张表里面主键相等
用途：
个人信息，拆表
SQLyog工具
sn.txt有注册码
内连接
--隐式内连接
select * from product p,category c where p.cno=c.cid;
--显示内连接
select * from product p inner join category c on p.cno=c.cid
--区别：
隐式内连接：在查询出结果的基础上去做的where条件过滤
显示内连接：带着条件去查询结果，执行效率要高
左外连接
左表：product
select * from product p left outer join category c on p.cno=c.cid;