[原创]MYSQL的简单入门

MYSQL简单入门:

查询库名称：show databases;
information_schema
mysql
test
2:创建库
create database 库名 DEFAULT CHARACTER SET utf8;//修改编码
3:删除库
drop database 库名
4:进入当前库
use 库名;

#表管理
1：创建表
create table 表名(列名 类型，列名 类型);
数据类型
整型
-TINYINT 年龄常用
-INT {常用}
-BIGINT
小数
-DOUBLE
-DECIMAL 货币常用
字符串类型
char(10) 固定长度字符串 如果字符不足10位，那么后面自动补充空格
varchar(10) 可变长度字符串{常用} 如果字符不足10位，那么长度自动缩短到字符的长度
日期类型
datetime 日期和时间-范围大{常用}
timestamp 日期和时间
date 日期
time 时间

示例：创建表

use test;
create table t_stu(
id int,
name varchar(25),
age tinyint,
rdate datetime
); 

查询当前库中的表 show tables;
查询创建表指令

show create table t_stu;

:2：更新表
#添加一列

alter table t_emp add rdate datetime;
alter table t_emp add age int;

#删除一列

alter table t_emp drop age;

#修改列的属性

alter table t_emp MODIFY age tinyint;

#修改列的名称

alter table t_emp CHANGE age age1 tinyint;

表操作
#添加
(1):按顺序将所有列添加数据

insert into t_emp values(1,'tom',100000,now(),19);

(2):不按顺序只添加部分数据

insert into t_emp(id,name,salary)values(2,'Jerry',9000.00);

(3):mysql特有的插入方法,一次添加多行

insert into t_emp values(3,'tom3',10000,now(),19),(4,'tom4',100000,now(),20);

#建议使用第二种方式
#更新

update t_emp set salary=salary*1.1,age1=age1+1;

#删除
#删除之前一定确认(留备份)
#删除记录之前给条件(全删)

delete from t_emp where salary<10000;

:3：删除表
delete from t_emp where 条件

 

练习1：
1:创建两张表 emp 雇员表
dept 部门表
2:添加记录
emp
empno ename rdate sal comm deptno job
001 tom 2000-10-9 1000 null 10 经理
002 jerry 2000-11-12 2000 null 30 工程师
003 kk 2000-1-1 3000 null 10 工程师
004 cc 2000-10-12 800 null 20 工程师
005 qq 1999-10-03 1000 null 10 工程师
dept

dept
deptno dname loc
10 财务部 洛杉矶
20 工程部 纽约
30 销售部 德州

#创建emp表

create table t_emp(
empno int,
ename varchar(25),
rdate datetime,
sal decimal(10,2),
comm decimal(10,2),
deptno int,
job varchar(20)
);

#创建dept

create table t_dept(
deptno int,
dname varchar(20),
loc varchar(20)
);

#对emp表插入数据

insert into t_emp(empno,ename,rdate,sal,comm,deptno,job) values (001,'tom1','2000-10-9',1000.00,null,10,'经理');
insert into t_emp(empno,ename,rdate,sal,comm,deptno,job) values (002,'jerry','2000-11-12',2000.00,500.00,30,'工程师');
insert into t_emp(empno,ename,rdate,sal,comm,deptno,job) values (003,'tom2','2000-10-3',3000.00,null,10,'工程师');
insert into t_emp(empno,ename,rdate,sal,comm,deptno,job) values (004,'tom3','2000-10-1',800.00,1000.00,20,'工程师');
insert into t_emp(empno,ename,rdate,sal,comm,deptno,job) values (005,'tom4','1999-10-12',1000.00,300.00,10,'工程师');

#对dept表插入数据

insert into t_dept(deptno,dname,loc)values(10,'财务部','洛杉矶');
insert into t_dept(deptno,dname,loc)values(20,'工程部','纽约');
insert into t_dept(deptno,dname,loc)values(30,'销售部','德州');

#查询
#*所有列

select * from emp;

#优化方案

select empno,ename,sal,comm,deptno,job from t_emp;

#条件
where sal>2000;

#and 并且 or 或者 not 不

练习二:
1:工资大于1000所有员工信息

select * from t_emp where sal>1000;

2:职务等于工程师所员工信息

select * from t_emp job='工程师';

3:入职时间1999-10-12~2000-10-3 范围入职员工

select * from t_emp where rdate between '1999-10-12' and '2000-10-3';

4:没有奖金的员工

select * from t_emp where isnull(comm);

4.1:有奖金的员工

select * from t_emp where not isnull(comm);

5:查询部门编号10所有员工信息

select * from t_emp where deptno=10;

#解决乱码

set names utf8;
source d:db.sql;

------------------
set names gbk;
删除表中所有的数据
重新插入

6:排序

select empno,ename,sal from t_emp
where sal>1000 order by sal desc;//desc 降序 asc 升序

7:分组 统计按照所有的部门分组求工资总和

select sum(sal) from t_emp;
select empno,sum(sal) from t_emp group by
deptno;

select deptno,avg(sal) from t_emp
group by deptno;

#查询 按职务分组 显示职务名称
#工资总和

select job,sum(sal) from t_emp group by job;

#查询:按职务分组，显示职务名称
#该职人数，count()
#人数大于1显示

select job ,count(job) from t_emp group by job
having count(job)>1;

#强调
#max() min() count() sum()
1:如果条件中出现分组函数 将where换成having
2:在select语句中出现的字段 如果有分组语句
字段一定要加分组函数
或者在group by 中出现

8:子查询
#查询与tom2员工相同的职务

select job from t_emp where ename='tmo2'

#所有员工的信息

select empno,ename,job,sal
from t_emp where job=(select job from t_emp where ename='jerry');

9;多表查询
#查询员工的编号,员工姓名,职务,部门名称

select e.empno,e.ename,e.job,d.dname
from t_emp e,t_dept d
where e.deptno=d.deptno;

练习:
1:查询员工信息 条件工资900-1000之间

select * from t_emp where sal between 900 and 1000;

2:查询员工姓名与工资，按工资排序

select ename,sal from t_emp order by sal;

3:查询员工信息，条件 部门编号与tom1相同
所有员工

select * from t_emp where deptno=(select deptno from t_emp where ename='tom1');

4:按员工部门编号分组，查询部门平均工资
大于1000的部门编号

select deptno,avg(sal) from t_emp group by deptno
having avg(sal)>1000;

5:查询,员工姓名,员工工资 ,部门位置

select e.ename,e.sal,d.loc
from t_emp e,t_dept d
where e.deptno=d.deptno;

#索引
索引:数据库一个对象
作用:提高查询速度
缺点:控制繁琐
#原理:
1:索引独立对象单独保存{有序}索引表
2:查询方式{类二分查找法}查询速度极快

1:创建索引
#手工创建:

CREATE INDEX idx_empno
on t_emp(empno);

#idx_empno:索引名称
#emp:表名
#empno:列名

#唯一索引(这一列名所有数据是唯一不能重复)
#效率比较高

CREATE UNIQUE INDEX idx_empno
on t_emp(empno);

#组合索引

CREATE UNIQUE idx_empno_ename
ON t_emp(empno,ename);

#部分索引

CREATE INDEX idx_ename
on t_emp(ename(5));

#自动创建:

c:查询当前表中所有索引的名称与类型

show index from t_emp \G; 

2:删除索引

DROP INDEX idx_empno ON t_emp;
DROP INDEX idx_ename ON t_emp;
DROP INDEX idx_empno_ename ON t_emp;

练习:
1:dept表的deptno 列添加唯一索引

CREATE UNIQUE INDEX idx_deptno
ON t_dept(deptno);

2:dept表的dname 列添加前缀索引(5)

CREATE INDEX idx_dname ON t_dept(dname(5));

3:查询dept表中所有索引

show index from t_dept \G;

3:使用索引(分析索引生效)
3.1:哪些列需要加索引
a:经常作为条件的列加索引

select empno,ename,sal,job
from emp
where empno = 1;

b:列:唯一性太差不用加索引
性别:
c:频繁更新的列不用加索引
logincount
3.2:查询时应该如何写
a:添加索引的列放在条件中

select empno,ename,job
from emp
where empno = 12;

b:组合索引注意(最左前缀原则)

create index idx_no_name_sal on emp(empno,ename,sal);
select empno,ename,sal,job,rdate
from emp
where
empno = 1 and
ename = 'tom' and
sal < 1000;

3.3:如何分析索引是否生效

explain select empno,ename,sal
from emp \G;

explain select empno,ename,sal
from emp
where empno = 1 \G;

#本次查询可能用到索引
possible_keys: idx_no_name_sal
#本次查询用到索引
key: idx_no_name_sal
#查询索引用到索引长度
key_len: 5
#查询一行
rows: 1

#约束
:数据为中一种强制性手段
:保存数据正确性完整性
1:主键 {强制要求当前列不能为空，不能重复}
a:创建表时指定
b:后在加

alter table emp add PRIMARY KEY(empno);

#强调:添加主键mysql会自动为当前列
# 添加索引
#固定套路
#没有特殊原因:一张表加主键 id {记录编号}
#主键列没有任何实际含义

2:不为空
#当前列不能为空

alter table emp modify ename not null;

#固定套路
#所有列都加 not null
#所有not null 后添加默认值(防止mysql BUG)

alter table emp modify ename varchar(25) not null default '';
alter table emp modify sal decimal(10,2) not null default 0;

3:唯一性 {当前列不能重复}

alter table emp add UNIQUE (ename);

4:外键

alter table dept add PRIMARY KEY(deptno);

#添加外键

alter table emp add FOREIGN KEY(deptno)
REFERENCES dept(deptno);

5:检查(mysql不支持) enum()替换

alter table emp add sex enum('F','M') not null default '

#表设计

#事务 一般在出现并发数据访问时采用(就是一条数据同时被多个用户操作)
概念:多条不可分割的sql语句
作用:保存数据完整性一致性
相关概念:
1:事务ACID特性
1:原子性 A 不可分割(要成功都成功,如果一个失败全失败)
2:一致性 C (将数据库从一种状态转变为下一种状态){前后一致}
3:隔离性 I {一个事务在未提交之前其他事务不可见}
4:持久性 D {事务一旦提交，结果是永久的}
2:事务隔离级别{级别高}
脏读:一个事务读取到另一个事务未提交的数据
不可重复读:同一个事务内,二次读同一条数据，结果不一致
幻读:很少出现
mysql以上三种情况都不会出现

#如何使用事件
START TRANSACTION 开始事务(begin)
commit; 提交事务
rollback; 回滚事务

 

练习:
1:创建表

create table t_bank(
id int,
name varchar(20),
m decimal(10,2)
)ENGINE=innodb;
insert into t_bank values(1,'wqh',7200);
insert into t_bank values(2,'tom',1000);

2:开始事务/更新

wqh: START TRANSACTION
update t_bank set m=0 where id=2;
update t_bank set m=m+100 where id=1;
commit;

1:开启事务
2:
3:提交事务
#表引擎
mysql 数据库特性
作用:不同的业务要求可以选择不同的表引擎
哪些表引擎:
myisam:
1:表查询的多 更新的少
2:表数据量小
3:表不需要事务和外键支持
4:便于管理(copy/备份)
#现在很少使用了
innodb
1:功能强(事务，外键支持)
2:查询速度快
3:管理稍复杂

#建议:都使用innodb
如何使用表引擎:
1:创建表指定表引擎

2:创建表后修改(建议不用)

create table t_bank(
id int,
name varchar(20),
m decimal(10,2)
)ENGINE=innodb;

//修改mysql的配置文件
my.ini中的编码 utf8
修改完后 到服务中去关闭 在打开服务

SQL注入
SQL注入:网络攻击手段
statement 安全漏洞
PreparedStatement: 几乎没有

"SELECT count(*) FROM t_user
WHERE name='"+name+"' and pwd = '"+pwd+"';
String name = "' or 1=1 #";

select count(*) from t_user
where name='' or 1=1 #

用户名:tom jerry james tuodou

drop table;
delete from t_user;
delete from mysql.user;

连接池:阿里driud{效率极快,安全性}

=======================================
分页查询
问题:什么是分页，为什么使用分页?
分页就是将数据以多页去展示，使用分页可以提高客户的感受。

分页分类:
1.物理分页
只从数据库中查询出当前页的数据。
优点:不占用很多内存
缺点:效率比较低
2.逻辑分页
从数据库中将所有记录查询出业，存储到内存中，要想展示当前页
数据，直接从内存中获取。
优点:效率高
缺点:占用内存比较高

在java开发领域，我们使用的比较多的是物理分页。
物理分页的实现:

1.直接使用jdbc完成
使用滚动结果集. 优点:跨数据库。缺点:性能低。
2.使用数据库本身提供的分页操作.
会使用每一个数据库特定的分页函数，优点:性能高 缺点:不能跨数据库。

mysql:limit
sqlservlet:top
oracle:rownum

介绍limit使用.
select * from 表 limit m,n;
m:代表的是从第几条开始 注意:它是从0开始记录.
n:代表查询几条记录.

示例:分页音，每页显示6条，要查询第2页的数据.

select * from 表 limit (页码-1)*每页条数,每页条数;

------------------------------------------------------------------------------
分页分析:
1.页码 默认第一页
2.每页条数 人为定义
3.总条数 select count(*) from 表
4.总页数 总页数=总条数%每页条数==0?总条数/每页条数:总条数/每页条数+1
总页数=Math.ceil(总条数*1.0/每页条数);

5.当前页的数据 List<?>----->select * from 表 limit (页码-1)*每页条数,每页条数;

 

 作者:little飞 出处:http://www.cnblogs.com/little-fly/ 欢迎转载 也请保留这段声明 谢谢!