mysql基础

查看所有数据库	show databases;
创建数据库	create database 数据库名称;
查看数据库详情	show create database 数据库名称;
创建数据库指定字符集	create database 数据库名称 character set utf8/gbk;
删除数据库	drop database 数据库名称;
使用数据库	use 数据库名称;

创建表	create table 表名(字段1名 字段1类型,.....);
查询所有表	show tables;
查看表详情	show create table 表名;
创建表时指定引擎和字符集	create table 表名(字段1名 字段1类型,.....) engine=innodb  charset=utf8/gbk;
查看表字段信息	desc 表名;
删除表	drop table 表名;

修改表名	rename table 原名 to 新名
修改表引擎和字符集	alter table 表名 engine=innodb/myisam charset=utf8/gbk;
添加表字段	alter table 表名 add 字段名称 字段类型;(默认最后,可first或after 字段名)
删除表字段	alter table 表名 drop   字段名称;
修改表字段的名称和类型	alter table 表名 change 原字段名 新字段名 新字段类型;
修改表字段的类型和位置	alter table 表名 modify 字段名 新类型 first/after xxx;

指定字段插入	insert into 表名 (id,name) values(值1,值2);
批量插入数据	insert into person values(10,'悟空',500),(11,'八戒',250);
查询数据	select 字段信息 from 表名 where id<5;
修改数据	update 表名 set age=10 where id=2;
删除数据	delete from 表名 where id=2;(如果不写条件则删除表内所有数据)

主键约束

- 什么是主键：用于表示数据唯一性的字段称为主键。

- 约束：是给表字段添加的限制条件。

- 主键约束：限制主键字段的值,唯一并且非空。

- 格式： create table t1(id int primary key,name varchar(10));

###主键约束+自增 primary key auto_increment 自增数值只增不减,从历史最大值基础上+1   

###删除表并且创建新表truncate table t2; //自增数值清零

- 外键：用于建立关系的字段称为外键

事务

事务是数据库中执行同一业务多条sql语句的工作单元，可以保证多条sql语句要么全部执行成功，要么全部执行失败。

- 创建用户表：

create table user(id int,name varchar(10),money int,state varchar(5));

- 插入数据：

insert into user values(1,'超人',50,'冻结'),(2,'蝙蝠侠',2000,'正常');

- 蝙蝠侠给超人转账200块钱 无事务保护：

update user set money=money-200 where id=2 and state='正常';

update user set money=money+200 where id=1 and state='正常';

- 有事务保护： 转账失败

begin;     //开启事务

update user set money=money-200 where id=2 and state='正常';                 

update user set money=money+200 where id=1 and state='正常';

rollback;   //回滚

- 有事务保护：转账成功          

insert into user values(3,'海王',10,'正常');

-蝙蝠侠给海王转账500

begin;

update user set money=money-500 where id=2 and state='正常';

update user set money=money+500 where id=3 and state='正常';

commit;  //提交

- 保存回滚点 savepoint 标识;

begin;

update user set money=money-10 where id=2;

savepoint s1;

update user set money=money-50 where id=2;

update user set money=money-100 where id=2;

rollback to s1;

- 涉及分配用户权限相关的SQL

###数据库的数据类型

####整数

- int对应java里面的int  bigint对应java中的long

- int(m) m代表显示长度 需要结合zerofill使用

create table t_int(id int,age int(10) zerofill);

insert into t_int values(1,18);

select * from t_int;

####浮点数

- double(m,d) m代表总长度 d代表小数长度   76.234  m=5  d=3

- decimal超高精度浮点数，涉及超高精度运算时使用

####字符串

- char(m): 不可变长度字符串   m=10  "abc" 所占长度为10 ，执行效率略高。

- varchar(m):可变长度字符串  m=10  "abc" 所占长度3  好处是节省空间 最大长度为65535，但是建议长度不要超过255 超过255建议使用text。

- text(m):可变长度字符串 m=10  "abc" 所占长度3  最大长度65535。

####日期

- date：只保存年月日

- time：只保存时分秒

- datetime：年月日时分秒 默认值null 最大值9999-12-31

- timestamp：年月日时分秒 默认值当前时间 最大值2038-1-19，以时间戳的形式保存时间

create table t_date(t1 date ,t2 time ,t3 datetime ,t4 timestamp);

insert into t_date values('2019-1-15',null,null,null);

insert into t_date values(null,'16:57:38','2018-10-22 20:18:30',null);

####导入*.sql数据到数据库中

- window系统:把*.sql 文件保存到 d盘的根目录,在命令行中执行  source d:/tables.sql

- linux系统:把*.sql 文件保存到桌面,在命令行中执行  source /home/soft01/桌面/tables.sql

#### is null 和 is not null

eg.select ename,sal,comm from emp where comm is null;

####别名 select 字段名 别名(可中文)

####去重 distinct(放在前面修饰select的字段)

####比较运算符 >,<,>=,<=,=,!=(<>)

####and和or(java 中的&&/||效果类似)

### in 当查询某个字段的值为多个值的时候使用

select * from emp where sal (not) in(5000,800,950);

###between x and y

select ename,sal from emp where sal (not) between 1000 and 4000;

###模糊查询 like:   _代表单个未知字符,%代表0或多个未知字符

###排序 order by 字段名

- 字段名后面 什么都不加默认为升序 添加 desc为降序    asc升序

- 多字段排序，如果需要多个字段排序则在order by 后面写多个字段名通过逗号分隔即可

 

分页查询

- 格式: limit 跳过的条数,请求的条数/每页的条数

- 请求前20条  limit 0,20

- 请求第三页每页8条   limit 16,8    //(3-1)*8,8

###数值计算 + - * /   7%2  mod(7,2)

日期相关的函数

1. 获取当前的日期+时间  select now();

2. 获取当前的年月日     select curdate();

3. 获取当前的时间      select curtime();

4. 从完整年月日时分秒中提取年月日 和提取时分秒

select date(now()),time(now());

5. 从完整年月日时分秒中提取时间分量 extract()

查询每个员工的姓名和入职的年份

select ename,extract(year from hiredate) year from emp;

6. 日期格式化 date_format(日期,格式)

- format：

- %Y 四位年  %y 两位年

- %m 两位月  %c  一位月    

- %d  日

- %H  24小时  %h 12小时

- %i  分

- %s  秒

- 把now()转换成 2019年01月16号 15点45分22秒 select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');

7. 把自定义时间格式转回标准格式 str_to_date(自定义的字符串时间,格式)

- 14.08.2008 08:00:00

select str_to_date('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');

ifnull(x,y)函数

- age=ifnull(x,y) 如果x的值为null 则age=y 如果不为null则age=x

1. 修改emp表中奖金为null的改为0，不为null则不变

update emp set comm=ifnull(comm,0);

聚合函数

平均值 avg(字段名)、最大值 max(字段名)、最小值 min(字段名)、求和 sum(字段名)、统计数量 count(字段名)。

字符串相关的函数

字符串拼接	concat(s1,s2)
获取字符串的长度	char_length(str)
获取字符串在另外一个字符串中出现的位置	instr(str,substr)
插入字符串	insert(str,start,length,newstr)
转大小写	select upper('nba'),lower('ABC');
去两端空白	trim
截取字符串	select substring('abcdefg',2); //从2截取到最后 select substring('abcdefg',2,3);//从2开始 长度为3
重复	repeat(str,count)
替换	replace(str,old,new)
反转	reverse(str)

 

 

 

 

 

 

 

 

 

 

数学相关的函数

向下取整	floor(num)
四舍五入	round(num)，round(num,m)
非四舍五入	truncate(num,m)
随机数	rand()  0-1

 

 

 

 

分组查询  

group by 字段名

having

where后面只能写普通字段的条件 不能写聚合函数的条件。having和where类似都是用于添加条件的，having后面可以写普通字段的条件也可以写聚合函数的条件，但是建议写聚合函数的条件，而且要结合group by 使用。

子查询（嵌套查询）

可以在查询语句中嵌套另一条sql语句，可以嵌套多层

子查询总结：

1. 嵌套在sql语句中的查询语句称为子查询。

2. 子查询可以嵌套n层。

3. 子查询可以 写在where和having的后面当做查询条件的值或在创建表的时候 。

-格式： create table 表名 as (子查询)

create table newemp as (select * from emp where deptno=10);

- 写在from后面当成一个虚拟表  **必须有别名**

select ename from (select * from emp where deptno=10) newtable;

关联查询

同时查询多张表称为关联查询,查询在new york工作的所有员工信息。

等值连接和内连接

等值连接和内连接都是关联查询的查询方式。等值连接和内连接查询到的结果一样，都为两张表的交集数据。

等值连接：select * from A,B where A.x=B.x and A.age=18;

内连接：select * from A join B on A.x=B.x where A.age=18;

####外链接 select * from A left/right join B on A.x=B.x where A.age=18;

关联查询总结：

1. 关联查询的查询方式包括三种：等值连接、内连接和外链接

2. 如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接

3. 如果查询一张表的全部数据和另外一张表的交集数据使用外连接，外链接掌握一种即可

表设计之关联关系

一对一

- 什么是一对一：有A和B两张表，A表中的一条数据对应B表中的一条数据，同时B表的一条数据也对应A表的一条，称为一对一的关系。

- 应用场景： 用户表和用户信息扩展表 ，商品表和商品详情表

- 如何建立一对一的关系：在从表中添加外键指向主表的主键建立关系

- 练习：创建用户表和用户详情表并保存以下信息

user:id,username,password     userinfo:user_id,nick,qq

- 创建表

create table user(id int primary key auto_increment,username varchar(10),password

varchar(10));

create table userinfo(user_id int,nick varchar(10),qq varchar(15));

用户名       密码        昵称        qq

libai admin 小白白 112233

liubei 123456 刘皇叔 667788

diaochan 112233 媳妇儿 998877

-插入数据：

insert into user (username,password) values('libai','admin'),('liubei','123456'),('diaochan','112233');

insert into userinfo  values(1,'小白白','112233'),(2,'刘皇叔','667788'),(3,'媳妇儿','998877');

1. 查询每个用户的用户名，昵称和qq

select u.username,ui.nick,ui.qq

from user u join userinfo ui

on u.id=ui.user_id;

2. 查询小白白的用户名和密码

select u.username,u.password

from user u join userinfo ui

on u.id=ui.user_id where ui.nick='小白白';

3. 查询貂蝉的所有信息

select *

from user u join userinfo ui

on u.id=ui.user_id where u.username='diaochan';

一对多

- 什么是一对多：有AB两张表：A表中的一条数据对应B表的多条数据，同时B表的一条数据对应A表的一条

- 应用场景：

商品表和分类表   

员工表和部门表

- 如何建立关系： 在两张表中多的表中添加外键指向另外一张表的主键

- 练习：创建数据库db5并使用，创建员工表和部门表并保存以下数据

emp:id name dept_id       dept:id name  

- 创建表：

create database db5;

use db5;

create table emp(id int primary key auto_increment,name varchar(10),dept_id int);

create table dept(id int primary key auto_increment,name varchar(10));

神仙部的员工 悟空和八戒

妖怪不的员工 蜘蛛精和白骨精

- 插入数据：

insert into dept values(null,'神仙'),(null,'妖怪');

insert into emp values(null,'悟空',1),(null,'八戒',1),(null,'蜘蛛精',2),(null,'白骨精',2);

1. 查询每个部门对应的员工姓名

select d.name,e.name

from emp e join dept d

on e.dept_id=d.id;

2. 查询八戒的部门名称

select d.name

from emp e join dept d

on e.dept_id=d.id where e.name='八戒';

3. 查询妖怪部有谁

select e.name

from emp e join dept d

on e.dept_id=d.id where d.name='妖怪';

多对多

- 什么是多对多：AB两张表，A表中一条数据对应B表多条，同时B表中一条数据对应A表多条称为多对多

- 应用场景：老师表和学生表

- 如何建立关系：创建一张关系表，在关系表中添加两个外键指向另外两张表的主键

- 练习：创建老师和学生表保存以下信息

- 创建表：teacher：id name     student：id name    t_s:tid,sid

create table teacher(id int primary key auto_increment,name varchar(10));

create table student(id int primary key auto_increment,name varchar(10));

create table t_s(tid int,sid int);

- 保存以下数据：

李老师：小明 小红 小绿 小黄

王老师：小明 小红

insert into student values(null,'小明'),(null,'小红'),(null,'小绿'),(null,'小黄');

insert into teacher values(null,'李老师'),(null,'王老师');

insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2);

1. 查询每个老师姓名和对应的学生姓名

select t.name,s.name

from teacher t join t_s ts

on t.id=ts.tid

join student s  

on s.id=ts.sid;

2. 查询李老师的学生姓名

select s.name

from teacher t join t_s ts

on t.id=ts.tid

join student s  

on s.id=ts.sid where t.name='李老师';

3. 查询小明的老师都有谁

select t.name

from teacher t join t_s ts

on t.id=ts.tid

join student s  

on s.id=ts.sid where s.name='小明';

 

JDBC

Java DataBase Connectivity：Java数据库连接，实际上是java中和数据库交互的API。

为什么使用JDBC：因为Java程序员需要连接多种数据库，为了避免每一种数据库都学习一套新的API，Sun公司提供了一个JDBC接口，各个数据库厂商根据此接口写实现类(驱动)，这样Java程序员只需要掌握JDBC接口中各个方法如何调用，就可以访问任何数据库。

####如何使用JDBC

1. 创建Maven工程

2. 登录maven私服： 外网：maven.aliyun.com

3. 在首页搜索mysql   找到5.1.6版本  复制坐标到工程中

4. 在工程中的pom文件里面 的project标签内部添加 dependencies标签，然后把复制的坐标粘贴到里面 保存，如果侧边栏出现”几本书”并且里面有”奶瓶”说明操作成功。

####执行sql方法

1. execute(sql) 可以执行任意sql,但是推荐执行DDL（create drop alter truncate）,方法返回值 代表是否有结果集

2. executeUpdate(sql) 执行增删改相关的sql,方法返回值为int类型的数值，代表生效的行数

3. executeQuery(sql) 执行select相关sql,方法返回值ResultSet结果集对象

####Java类型和数据库类型对比

     数据库            java  

  int               getInt()

  float/double      getFloat()/getDouble()

  varchar           getString()

 datetime/timestamp     getDate()

####ResultSet获取数据的方式

1. 通过字段名称获取  getString(字段名称)

2. 通过查询字段的位置获取   getString(字段位置)  

####读取*.properties配置文件

//创建读取配置文件的对象

Properties prop = new Properties();

//获取文件输入流

InputStream ips = Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties");

//把文件流加载到配置文件对象中

prop.load(ips);

//获取配置文件中的数据

String name = prop.getProperty("name");

String age = prop.getProperty("age");

System.out.println(name+":"+age);

###数据库连接池DBCP（DataBase Connection Pool）

- 为什么使用数据库连接池：如果不使用连接池，每一次和数据库的交互都需要建立一次连接，交互完之后则断开连接，一万次业务交互则需要有一万次开建立连接和关闭连接，频繁开关连接浪费资源，通过数据库连接池可以将用完之后的连接进行重用，避免资源的浪费

- 如何使用连接池

从maven私服中搜索 dbcp

PreparedStatement预编译sql执行对象

- 好处：

1. 代码更直观，不易出错，避免了拼接字符串

2. 执行效率略高于Statement

3. 可以避免SQL注入的风险，因为在创建对象编译sql时就已经把sql语句的逻辑固定（锁死），不会因为后续替换？的值而改变，从而避免了sql注入风险

- 什么时候使用PrepareStatement？

如果sql语句中出现变量则使用PrepareStatement

###批量操作

- 可以将多次web服务器和数据库服务器的数据交互，合并成一次交互，从而提高执行的效率

####分页查询练习

- 程序运行提示：请输入请求的页数     然后提示：请输入请求的条数

####事务练习

create table hero(id int primary key auto_increment,name varchar(10),money int);

insert into hero values(null,'超人',50),(null,'蝙蝠侠',5000);

- 事务相关方法

- conn.setAutoCommit(false); //关闭自动提交

- conn.setAutoCommit(true); //开启自动提交

- conn.commit();//提交事务

- conn.rollback();//回滚事务

####获取自增主键的值

- 球队球员练习：

- 创建表

create table team(id int primary key auto_increment,name varchar(10));

create table player(id int primary key auto_increment,name varchar(10),team_id int);

###获取元数据

- 数据库元数据：数据库相关的部分信息

//得到数据库的元数据

DatabaseMetaData dbmd = conn.getMetaData();

System.out.println("数据库版本："+dbmd.getDriverVersion());

System.out.println("用户名："+dbmd.getUserName());

System.out.println("数据库地址："+dbmd.getURL());

System.out.println("数据库厂商："+dbmd.getDatabaseProductName());

- 表的元数据：表相关的部分信息

ResultSet rs = stat.executeQuery("select * from emp");

//得到表相关的源数据

ResultSetMetaData rsmd = rs.getMetaData();

//得到表的字段数量

int count = rsmd.getColumnCount();

for (int i = 0; i < count; i++) {

System.out.println("字段名"+rsmd.getColumnName(i+1));

System.out.println("字段类型"+rsmd.getColumnTypeName(i+1));

}

IO流操作文件保存数据弊端:

业务代码繁琐开发效率低，执行效率低，一般只能保存字符串，一般只能保存小量数据。

数据库分类

1. 关系型数据库：经过数学理论验证可以将现实生活中存在的任何关系保存起来的数据库,以表为存储数据的单位 。

2. 非关系型数据库：解决一些特定的应用场景如：高并发、缓存等,非关系型数据库存储数据并非以表为单位,如Redis数据库通过key-value形式保存数据。

sql

Structured Query Language：结构化查询语言，用于程序员和数据库软件进行交互, sql语句执行在客户端(windows的命令行、Linux的终端或三方的可视化客户端) 或者通过java代码执行。

如何连接数据库软件：

1. windows系统 开始菜单--所有程序--MariaDB或MySQL--MySQL Client，然后直接输入密码。没有密码直接回车。

2. Linux系统 桌面右键打开终端 执行mysql -uroot -p    回车   输入密码     退出：exit;

sql分类

DDL	Data Definition Language 数据定义语言	create，drop，alter，truncate不支持事务
DML	Data Manipulation Language 数据操作语言	insert，update，delete，select（DQL）支持事务,select和事务没有关系
DQL	Data Query Language 数据查询语言	只包括select
TCL	Transaction Control Language 事务控制语言	begin、rollback、commit、savepoint s1、rollback to s1
DCL	Data Control Language 数据控制语言