数据库:Database,简称DB。按照一定格式存储数据的一些文件的组合。实际上就是存储了具有特定格式的数据。
数据库管理系统:DataBaseManagement,简称DBMS。可以对数据库进行增删改查,常见的有MySQl , MS ,DB2 , sybase……
基本操作命令:
-
mysql -u root -p 登录
-
net start mysql 启动mysql
-
net stop myssql 停止mysql
-
show databases 显示所有数据库
-
create name 创建数据库
-
use name 使用数据库
-
source path 导入数据
-
show tables 展示所有表格
-
select scope from tablename 查询数据
-
desc tablename 展示某表格结构
-
\c 终止
-
source D:/BaiduNetdiskDownload/jdbc_data.sql
sql语句分类
1. DQL:数据查询语言,select……
2. DML:数据操作语言,增删改查
3. DDL :数据定义语言,主要是操作表的结构
4. TCL :事务控制语言
5. DCL :数据控制语言
select
格式:
select……from……where……group by……having……order by……limit……
执行顺序:
from-> where->group by->having->select->order by->limit
-
select * from emp
-
select deptno,dname as deptname from dept
-
select deptno,dname 'dept name' from dept
-
select ename,sal*12 '年薪' from emp;
-
select ename,empno from emp where sal<>800;
-
select * from emp where sal>=2500 and sal<=3000;
-
select * from emp where sal between 2500 and 3000;
-
必须左小右大
-
select * from emp where comm is null;
-
select * from emp where comm is not null;
-
null 代表空不能,用“=”比较
-
select * from emp where job='CLERK' or job ='SALESMAN';
-
select *from emp where sal>=2500 and deptno=10 or deptno=20;
-
相当于
-
select *from emp where (sal>=2500 and deptno=10) or deptno=20;
-
select empno,ename ,job from emp where job in('CLERK','MANAGER');
-
select empno,ename ,job from emp where job not in('CLERK','MANAGER');
-
select * from emp where ename like '%o%';
-
select * from emp where ename like '_o%';
-
select * from emp where ename like '%\_%';
-
select *from emp order by sal;
-
select *from emp order by sal asc;
-
select *from emp order by sal desc;
-
select * from emp order by sal desc ,ename;
-
sal 在前起主导
-
select * from emp order by 2;
-
select * from emp where sal between 1250 and 3000 order by sal desc;
-
固定格式 先 where再 order
单行处理函数
-
lower(); 转换小写
-
upper(); 转换大写
-
substr(); 去空格
-
length(); 取子串
-
trim(); 将字符串转换为空格
-
str_to_data(); 将字符串转化为日期
-
data_fromat(); 格式化日期
-
format(); 设置千分位
-
round(); 四舍五入
-
rand(); 生成随机数
-
ifnull(); 将null转化为一个具体的数
-
concat(); 拼接字符串
-
new() 获取当前时间
-
case …… when …… then …… when …… then …… else …… end;
-
select
-
-> ename,
-
-> job,
-
-> sal as oldsal,
-
-> (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end)as newsal
-
-> from
-
-> emp;
-
~~~
代码:
-
select lower(ename) from emp;
-
select substr(ename,1,2) from emp where substr(ename,1,1)='A';
分组处理函数
-
count(); 计数
-
sum(); 求和
-
avg(); 求平均
-
max(); 最大
-
min(); 最小
注意
- count、sum会自动忽略null
- count(*)统计表的行数
- 分组函数不能够直接使用在where中
分组查询
select……from……group by……
执行顺序
from -> where -> group by -> select -> order by
代码
-
select job ,sum(sal) sum
-
-> from emp
-
-> group by job;
注意
在一条`select`语句中,如果有`group by`语句的话,`select`后面只能跟:参加分组的字段,以及分组函数,其他一律不能跟
可以使用`having`对于数据进行进一步筛选,同时`having`不能单独使用
distinct:去除重复值
-
select distinct job from emp;
-
distinct 只能出现在字段最前端,若出现在多个字段前表示联合去重
连接查询
根据表连接方式,分内连接和外连接
-
select ename,dname from emp,dept where emp.deptno=dept.deptno;
-
select ename,dname from emp e join dept d on e.deptno=dep.deptno;
-
select e.ename,d.dname
-
-> from emp e
-
-> join dept d
-
-> on e.deptno=d.deptno;
-
select e.ename,e.sal,s.grade
-
-> from emp e
-
-> join salgrade s
-
-> on e.sal between s.losal and s.hisal;
-
mysql> select e.ename , f.ename
-
-> from emp e
-
-> join emp f
-
-> on e.mgr=f.empno;
-
select
-
e.ename, f.ename as 'BOSS',d.dname ,s.grade
-
from
-
emp e
-
join dept d
-
on e.deptno=d.deptno
-
join salgrade s
-
on e.sal between s.losal and s.hisal
-
left join emp f
-
on e.mgr=f.empno;
-
~~~
-
mysql> select
-
-> t.*,s.grade
-
-> from
-
-> (select job,avg(sal) as avgsal from emp group by job) t
-
-> join
-
-> salgrade s
-
-> on
-
-> t.avgsal between s.losal and s.hisal;
-
select
-
e.ename,e.deptno,(select dname from dept where e.deptno=deptno) as dname
-
from
-
emp e;
-
select 中的子查询只能一次返回一个结果,否则会报错
-
select ename ,job from emp where job ='MANAGER'
-
union
-
select ename ,job from emp where job ='SALESMAN'
-
对于表连接来说union效率更高
-
在使用union时,要求两个结果集的列数相同
-
建议结果集的列和列的数的类型相同,对于不同环境下要求不同
limit
-
select ename ,sal from emp order by sal desc limit 0,5;
-
limit n,m
-
显示从n开始的m条数据
select与from后嵌套 子查询的区别
-
####找出每个部门的平均的薪资等级,要求显示部门名称,部门编号,还有平均等级。
-
##from 后面嵌套子查询:
-
select
-
d.deptno ,d.dname, b.avggrade
-
from
-
dept d
-
left join
-
(select
-
e.deptno ,avg(s.grade) as avggrade
-
from
-
emp e
-
join
-
salgrade s
-
on
-
e.sal between s.losal and s.hisal group by e.deptno) b
-
on
-
d.deptno=b.deptno;
-
#select 后面嵌套子查询:
-
select
-
d.dname ,d.deptno,
-
(select
-
h.avggrade
-
from
-
(select
-
e.deptno ,avg(s.grade) as avggrade
-
from
-
emp e
-
join
-
salgrade s
-
on
-
e.sal between s.losal and s.hisal
-
group by
-
e.deptno) h
-
where
-
h.deptno=d.deptno) as avggrade
-
from
-
dept d;
create
create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型……)
数据类型
- varchar:可变长度字符串,会动态分配空间,最长255
- char:定长字符串,固定长度分配空间,最长255
- int:整型,最长11
- bigint:长整型
- float:单精度浮点型
- double:双精度浮点型
- date:短日期类型,只包含年月日,默认格式`%y-%m-%d`
- datetime:长日期类型,包含年月日时分秒,默认格式 `%Y-%m-%d %h:%i:%s`
- clob:字符大对象,最多可以存储4g的字符串
- blob:二进制大对象
create delect语句
-
create table t_student(
-
no int,
-
name varchar(32),
-
sex char(1),
-
age int(3),
-
email varchar(255)
-
),(……)……;
-
或者
-
create table tablename as (select……)
-
drop table t_student;
-
drop table if exists t_student;
-
insert into t_student (no,name,sex,age,email) values (1,'张三','女',20,'zhangsan@');
-
insert into t_use(id,name,birth) values(1,'张三',str_to_date('01-10-1990','%d-%m-%Y'));
-
insert into t_use(id,name,birth) values(2,'李四','1998-02-04');
-
select date_format(birth,'%m\%d') from t_use;
update delete
-
update t_use set birth=now where id=1;
-
delete t_use where id=1;
-
没有where默认全部删除
-
delete的删除不会清除实际的数据,不会释放空间,可以使用 rollback 恢复
-
truncate 会彻底删除,效率高
-
#删除表格
-
drop table tablename
约束constraint
约束是指在表的字段上加上一些约束,来保证表中数据的完整性,有效性。约束包括:`not null`,`unique`,`primary key`,`foreign key`
-
create table t_student(
-
no int unique,#唯一性约束,不能重复但能都为null
-
name varchar(32) not null,#非空性约束
-
sex char(1),
-
age int(3),
-
email varchar(255)
-
);
-
#联合主键
-
create table t_student(
-
no int ,#唯一性约束,不能重复但能都为null
-
name varchar(32) not null,#非空性约束
-
sex char(1),
-
age int(3),
-
email varchar(255),
-
primary key(no,name)
-
);
-
#主键可以用自增生成 Auto_increment 从一开始
-
create table t_student(
-
no int ,#唯一性约束,不能重复但能都为null
-
name varchar(32) not null,#非空性约束
-
sex char(1),
-
age int(3),
-
email varchar(255),
-
id int primary Auto_increment
-
);
外键约束(FOREIGN KEY)
定义一个外键时,需要遵守下列规则:
- 父表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则父表与子表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性
- 必须为父表定义主键。
- 主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
- 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
- 外键中列的数目必须和父表的主键中列的数目相同。
- 外键中列的数据类型必须和父表主键中对应列的数据类型相同
代码
-
mysql> create table t_class(
-
-> classno int primary key,
-
-> classname varchar(255)
-
-> );
-
mysql> create table t_student(
-
-> no int primary key,
-
-> name varchar(255),
-
-> cno int,
-
-> foreign key(cno) references t_class(classno)
-
-> );
存储引擎
-
show create table t_student;
-
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-
| Table | Create Table |
-
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-
| t_student | CREATE TABLE `t_student` (
-
`no` int(11) NOT NULL,
-
`name` varchar(255) DEFAULT NULL,
-
`cno` int(11) DEFAULT NULL,
-
PRIMARY KEY (`no`),
-
KEY `cno` (`cno`),
-
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
-
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-
mysql> show engines;
-
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
-
| Engine | Support | Comment | Transactions | XA | Savepoints |
-
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
-
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
-
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
-
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
-
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
-
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
-
| CSV | YES | CSV storage engine | NO | NO | NO |
-
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
-
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
-
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
-
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
事务
-
start transaction;
-
commit
-
rollback;