mysql总结_数据库链接_单表_多表查询

前言：
数据库的原理：客户端查询、修改、删除、添加操作，通过sql语句对数据库服务器进行操作，然后将数据展示在客户端
数据库的分类：关系型数据库：（安全、保持数据一致性、实现对复杂数据查询）
                    和非关系型数据库（效率高、容易扩展、使用更加灵活）
mysql优点：1.开源、免费；2.体积小维护成本低；3.支持多系统；4.支持多语言；5.支持多引擎


一、数据库链接

   1、安装mysql数据库
         yum install mydql
    2、安装mysql数据库服务器
         yum install mydql-server
    3、查询数据库
         rpm -gal | grep mysql
     4、打开/关闭数据库mysql
          service mysqld start /stop
     5、查看数据库状态
          service mysqld status
      6、设置登录密码
           mysqladmin -u root password "123456"
      7、授权、刷新
           grant all privileges on *.*  to root@"%" identified by "123456"
           grant-授权   all privileges -所有授权  第一个*表示所有数据库   。表示所有表  第二个*表示授权
            root表示针对root用户  %表示所有的远程主机    identified by 设置密码

             flush privileges 刷新授权
       8、进入mysql操作界面
           mysql -u root -p（用root用户进入mysql数据库）

注意：无法链接的原因
       1、防火墙是否关闭 service iptables stop /status(查询防火墙状态)
        2、数据库是否启动
       3、链接ip地址是否正确
       4、账号密码是否正确
        5、是否设置权限、刷新
二、基本语句

显示所有数据库 show databases ;
创建数据库 -create database 数据库名；
删除数据库-drop database 数据库名；
使用数据库-use 数据库名；

数据库中建表-create table 表名（字段1 字符类型1（字符长度1）。。。）
显示所有表-show tables ;
删除表-frop table 表名；
插入表数据-insert into 表名（字段1 字符类型1（）。。。。）
添加字段-alter table 表名 add 字段名 字符类型（长度）
修改字段-alter table 表名 change 原字段名 新字段名 字符类型（长度）
删除字段-alter table 表名 drop 字段名
重命名-alter table 表名 rename 新表明
调整字段顺序-alter table 表名 modify 移动的字段名 字段类型（长度） after 字段名；
将字段添加到首行-alter table 表名 add 字段名 字符类型（长度）first；
将字段添加到指定字段后面-alter table 表名 add 新字段名 字符类型（长度） after 原表名
防止乱码，建表时给一个默认编码格式-default charset=utf8

查看表结构-desc 表名；
查看表内容-select * from 表名；

表格中的闲置条件：常见的约束
1.非空约束（not null ）
2.唯一约束（unique） 保证字段值具有唯一性，可以为空
3.主键约束（primary key ）保证字段值具有唯一性，不能为空
4.外键约束（forrign）
5.默认（default）
6.自增长 auto_increment

数据的类型：数值类型int
字符类型：char varchar
浮点类型：folat
时间类型date
string类型：set


三、查询语句

查询表格中的所有数据--select * from 表名
查询表中的具体字段--select 字段名 from 表名
--------------------------------------------------
where 加条件查询
（1）判断条件
select  * from   emp   where  dept2=101 ;
select  * from   emp   where  dept2 !=101
select  * from   emp   where  dept2 <>101 ;
select  * from   emp   where  dept2 >102 ;
select  * from   emp   where  dept2 <102 ;
select  * from   emp   where  dept2 <=102 ;
select  * from   emp   where  dept2 >=102 ;
（2）and（同时满足）
select  * from   emp   where  dept2 >=102  and age=57 ;
（3）or（满足任意一个）
select  * from   emp   where  dept2 =102  or age=55 ;
（4）between 。。。and。。（在一个范围内，注意包含两端数据）
select  * from   emp   where  age BETWEEN 24 and  55 ;（包含24和55两个数）
（5）in （匹配包含的数据）
select  * from   emp    where  dept2 in (101,102,110);（匹配表中包含101,102,110的数据）
（6）is null 或 is not null
select  * from   emp    where  age  is not null ;
-----------------------------------------------------------
排序
对表中数据排序-select * from 表名 order by 条件 asc（升序，一般默认升序可以省略不写）
                        -select * from 表名 order by 条件 desc （降序）
二次排序--select  * from   emp   order  by  dept2 desc , incoming asc ;（先用条件deot2进行降序，再用incomming进行升序）


----------------------------------------------------------------
模糊匹配查询
%：表示会匹配0个字符或者多个字符
_:表示一个字符
select  * from   emp  where  dept2 like "1%" #匹配1开头的部门编号数据
select  * from   emp  where  dept2 like "%2%" #匹配的部门包含2编号数据
select  * from   emp  where  dept2 like "%2" #匹配结尾包含2编号数据
select  * from   emp  where  dept2 like "__2" #匹配固定的字符2编号数据


----------------------------------------------------------------
限制查询limit
limit _,_; 第一个值表示索引，第二个值表示步长 注意如果limit后面只有一个数表示默认索引为0


select  * from   emp   limit  2,3 ;表示显示一个表中从第三行开始的三行数据
select  * from   emp   limit  2 ; 表示显示一个表中的前两行数据


--------------------------------------------------------------------
分组查询
group by 条件 having 条件
SELECT  dept2,max(age) from   emp  group  by  dept2 ;表示在emp表中以dept2为条件分组查询最大值
SELECT  dept2 ,max(age)  as  s from   emp  group  by  dept2 HAVING  s>50 ;表示在emp表中以dept2为条件分组查询最大值，并且要s大于50


----------------------------------------------------------------------
通过mysql函数查询（也叫聚合函数）
最大值--max     SELECT  dept2,max(age) from   emp；表中年龄最大值
最小值--min      SELECT min(age) from   emp；表中年龄最小值
平均值--avg       SELECT avg(age) from   emp；
统计数--count    SELECT count(age) from   emp；
求和--sum          SELECT sum(age) from   emp；
去重--distinct     SELECT  distinct(dept2) from   emp；


-----------------------------------------------------------------------
删数据
delete；truncate；drop
删除数据速度 drop>truncate >drop
注意：1、drop是删除表和数据
             2、truncate 删除无法恢复
             3、delete 删除可以恢复
delete --delete  from  表名  where  条件


-------------------------------------------------------------
表取别名
SELECT  s.dept2,s.name from   emp as s ;  （把emp表设置成临时表s）


--------------------------------------------------------------
备份表结构
create table  新表明 like 旧表名
备份数据
insert into 新表（有表结构）select * from 备份表的原表


备份部分数据
INSERT  into  表名(s字段名1,字段名2)  select  sid ,dept2  from  emp ;
备份表结构和数据
create  table 表名 as(select  * from   备份原表 )


--------------------------------------------------------------------------------------

linux备份数据库：

备份用:>

格式：mysqldump -u  root -p 原数据库>数据库脚本.后缀名

eg：mysqldump -u root -p mysql>/home/mysqlbf.sql;

还原数据库：
1.建立一个新的空库
2.在linux中还原
3.mysql -u root -p 新建空库<数据库备份好的数据脚本
eg：mysql -u root -p aa<mysqlbf.sql;


--------------------------------------------------------------
三、多表
多表的四中链接：
1、内连接（基本内连接，隐藏内连接）
2、左链接
3、右链接
4、全连接

（1）内连接
       1.基本内连接：
         格式： select * from 表1 inner join 表2 on 表1.关联字段=表2.关联字段
       2.隐藏内连接
         格式：select  * from 表1 ，表2 where 表1.关联字段1=表2.关联字段2

（2）左连接
         以坐表为主（显示完整的左表），右表右关联的数据就显示，没有数据就以null显示
       格式：select * from 表1 left join 表2 on 表1.关联字段1=表2。关联字段2；
（3）右连接
       以右表为主（显示完整的右表），左表右关联的数据就显示，没有数据就以null显示
      格式：select * from 表1 right join 表2 on 表1.关联字段1=表2.关联字段2；

（4） 左表独有
       左表中独有的数据显示（左独有，以右表字段为空查询）
     格式：select * from 表1 left join 表2 on 表1.关联字段1=表2.关联字段2 where 右表字段 is null

（5）右独有
      右表中独有的数据显示（右表独有，以左表字段为空查询）
       格式：select * from 表1 right join 表2 on 表1.关联字段1=表2.关联字段2 where 左表字段 is null

（6）左表独有+右表独有
       格式：左表独有 + union + 右表独有

（7）全连接
       格式：左独有 + 右独有+ 内连接
                  左连接+右独有
                 左独有+ 右连接

四、子查询

子查询分类：标量子查询；列子查询；行子查询；表子查询
（1) 标量子查询：（返回一个值）
            吧一个sql执行语句（返回一个值）作为另一sql执行语句的一个条件 ，一般出现在where后

     注意： 表量子查询允许使用符号：=，！=，<>,<,>
（2）列子查询（返回一个列）
          返回一列值   
     注意：通常在where条件后使用，in或not in ，不能使用=，<,>等比较运算符
（3）行子查询（返回一行）
            返回一行值
（4）表子查询（返回一个表）













五、练习题

建表：

#部门表

create table dept(

    deptno int primary key,

    dname varchar(14), -- 部门名称

    loc varchar(13)-- 部门地址

)default charset=utf8 ;

insert into dept values (10,'会计部','纽约');

insert into dept values (20,'技术部','达拉斯');

insert into dept values (30,'销售部','芝加哥');

insert into dept values (40,'运营部','波士顿');

select * from dept

#员工表

drop table emp;

create table emp(

    empno int  primary key, -- 员工编号

    ename varchar(10), -- 员工姓名

    job varchar(9), -- 员工工作

    mgr int, -- 员工直属领导编号

    hiredate date, -- 入职时间

    sal double, -- 工资

    comm double, -- 奖金

    deptno int,  -- 所在部门

    foreign key (deptno) references dept(deptno)) default  charset=utf8;  -- 关联dept表  

-- alter table emp add foreign key (deptno) references dept(deptno);

insert into emp values(7369,'smith','职员',7566,"1980-12-17",800,null,20);

insert into emp values(7499,'allen','销售员',7698,'1981-02-20',1600,300,30);

insert into emp values(7521,'ward','销售员',7698,'1981-02-22',1250,500,30);

insert into emp values(7566,'jones','经理',7839,'1981-04-02',2975,null,20);

insert into emp values(7654,'martin','销售员',7698,'1981-09-28',1250,1400,30);

insert into emp values(7698,'blake','经理',7839,'1981-05-01',2850,null,30);

insert into emp values(7782,'clark','经理',7839,'1981-06-09',2450,null,10);

insert into emp values(7788,'scott','职员',7566,'1987-07-03',3000,2000,20);

insert into emp values(7839,'king','董事长',null,'1981-11-17',5000,null,10);

insert into emp values(7844,'turners','销售员',7698,'1981-09-08',1500,50,30);

insert into emp values(7876,'adams','职员',7566,'1987-07-13',1100,null,20);

insert into emp values(7900,'james','职员',7698,'1981-12-03',1250,null,30);

insert into emp values(7902,'ford','销售员',7566,'1981-12-03',3000,null,20);

insert into emp values(7934,'miller','职员',7782,'1981-01-23',1300,null,10);

#工资等级表

create table salgrade(

    grade int,-- 等级

    losal double, -- 最低工资

    hisal double ) default charset=utf8; -- 最高工资

insert into salgrade values (1,500,1000);

insert into salgrade values (2,1001,1500);

insert into salgrade values (3,1501,2000);

insert into salgrade values (4,2001,3000);

insert into salgrade values (5,3001,9999);

------------

单表题目：

#1、查找部门30中员工的详细信息。

select ename,job,sal from emp where deptno=30

#2、找出从事职员工作的员工的编号、姓名、部门号。（clerk  职员，办事员）

select empno,ename,deptno from emp where job="职员";

#3、检索出奖金多于基本工资的员工信息。

select ename,sal from emp where comm>sal

#4、检索出奖金多于基本工资60%的员工信息。

select ename,sal from emp where comm>(sal*0.6)

#5、找出10部门的经理、20部门的职员 的员工信息。（manager经理；管理

select ename from emp where (deptno=10 and job="经理")or(deptno=10 and job="职员");

#6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。（sal 工资）

select ename from emp where (deptno=10 and job="经理")or(deptno=10 and job="职员")or(job!="职员" and job!="经理"and sal>2000)

#7、找出获得奖金的员工的工作。（comm奖金）

select job from emp where comm is not null

#8、找出奖金少于100或者没有获得奖金的员工的信息。

select ename from emp where comm <100 or comm is null

#9、找出姓名以a、b、s开始的员工信息.

select ename from emp where ename like "a%" or ename like "b%" or ename like "s%"

#10、找到名字长度为6个字符的员工信息。

select ename from emp where ename like "______";

#11、名字中不包含r字符的员工信息。

select ename from emp where ename not like "%r%";

#12、查找员工的详细信息并按姓名排序。

select ename,job,deptno from emp order by ename ;

#13、返回员工的信息并按工作降序工资升序排列。(升序的规则)  

select ename,job from emp order by job desc,sal

#14、计算员工的日薪(按30天)。

select ename,(sal/30)a from emp

#15、找出姓名中包含a的员工信息。

select ename from emp where ename like "%a%"


------------
多表题目

多表查询答案

#1、返回拥有员工的部门名、部门号。

select dname,empno from dept,emp where dept.deptno=emp.deptno

#2、工资水平多于smith的员工信息。

select ename,job from dept,emp where dept.deptno=emp.deptno and sal>(select sal from emp where ename="smith")

#3、返回员工和所属经理的姓名。

select ename from emp job=""

#4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

#5、返回员工姓名及其所在的部门名称。

select ename,dname from dept,emp where dept.deptno=emp.deptno

#6、返回从事clerk工作的员工姓名和所在部门名称。(CLERK销售)

select ename,dname from dept,emp where dept.deptno=emp.deptno and job="销售员"

#7、返回部门号及其本部门的最低工资。

#8、返回销售部(sales)所有员工的姓名。

#9、返回工资水平多于平均工资的员工。

#10、返回与SCOTT从事相同工作的员工。(职员)

#11、返回与30部门员工工资水平相同的员工姓名与工资。

#12、返回工资高于30部门所有员工工资水平的员工信息。

#13、返回部门号、部门名、部门所在位置及其每个部门的工员总数。

#14、返回员工的姓名、所在部门名及其工资。

#15、返回员工的详细信息。(包括部门名)

#16、返回员工工作及其从事此工作的最低工资。

#17、计算出员工的年薪，并且以年薪排序。

#18、返回工资处于第四级别的员工的姓名。

#19、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

#20.工资等级多于smith的员工信息。