myaql的使用

myaql的使用

一、mysql的安装

1、查看linux中是否有mysql文件

rpm -qa|grep mysql

2、如果有mysql数据包，将其删除，可用以下命令任选其一

（1）yun remove 服务名称 删除Linux中的数据包；

（2）yum remove mysql * 删除Linux中所有的数据库包；

（3）yum erase 数据库包 删除Linux中的数据；

（4）rpm -e --nodeps 数据包 删除Linux中的数据库；

3、Linux中mysql数据包删除后将进行mysql安装

（1）安装数据库客户端：yum install mysql

（2）安装数据库服务端：yum install mysql-service

4、安装后启动数据库

Service mysqld start

5、设置登录密码：

mysqladmin -uroot password 密码

6、进入数据库操作界面

mysql -u root -p

二、数据库与xshell连接应注意事项

1、关闭防火墙

service iptables stop

2、数据库服务是否启动：

service mysqld start

3、连接的IP是否正确

4、账号和密码是否正确

5、是否设置权限并刷新

grant all privileges on *.* to root@"%" identified by "123456";授权所有权限

flush privileges刷新权限

三、mysql基本语句

第一步：新建一个库 create database 库名；

第二步：使用库use 库名（Linux界面）或者点击选择navicate中的一个库

第三步：create table 表名(字段1 字符类型1(字符长度1) 字段2 字符类型2(字符长度));创建表）

第四步：insert into 插入数据

create database 仓库名 创建新的仓库（末尾加分号）

show databases 显示所有的数据库

drop database 库名 删除数据库

use 库名 使用库

create table 表名(字段1 字符类型1(字符长度1) 字段2 字符类型2(字符长度));创建表

案例：create table aa(id int(10) name char(20));

show tables 显示库内所有的表

（一）建表语句

1、create table 表名(字段名1 字符类型1(字符长度),字段名2 字符类型2(字符长度),字段名3、字符类型3(字符长度))default charset=utf8（默认格式）;

2、default charset=utf8 ;默认编码格式，当插入中文数值显示？？？解决

3、案例：create  table hz10(id  int(10),name char(20),age int(10))default charset=utf8 ;

4、desc 表名 查看表结构；

（二）建表约束

表中的限制条件

作用：表在的时候加入约束的目的是为了保证表中的记录完整和有效。

1、非空约束 （not   null） 非空，用于保证字段的值不能为空，

2、唯一约束 （unique） 保证字段值具有唯一性（不能重复），可以为空，唯一约束可以有多个

3、主键约束（primary key） 保证字段值具有唯一性（不能重复），不能为空 ，一个表中只有一个主键

4、外键约束 （forrign key） 两个表之间的主键建立关系

5、默认 ：（default）当字段不填数据，默认数据

6、自增长 ：auto _increment 当添加一条数据，自动增长+1

drop   table   表名   删除表 ；

例如：DROP   table   aa  ;

表字段：

添加字段：add

格式：alter  table   表名  add 字段名 字符类型（字符长度);

alter  table   hz10  add  fs int(10);

修改字段：change

格式：alter  table   表名   change  原字段名    新字段名   字符类型(字符长度);

alter  table   hz10  change  fs  tz int(10);

删除字段：drop

格式：alter  table  表名   drop 字段名 ;

alter  table   hz10 drop tz ;

重命名：rename

格式：alter  table  原表名 rename 新表名;

alter  table   hz10 rename hzdcs10 ;

调换字段顺序：modify      after      在......后面

格式：alter  table   表名 MODIFY  移动的字段名   字段类型(字符长度)  after   字段名 ;

alter  table   hzdcs10 MODIFY  age int(10)  after id ;

将字段添加到首行：

格式：alter  table 表名 add  字段名 字符类型(字符长度)      first ;

alter  table   hzdcs10 add  no  int(10) first ;

将字段添加到指定字段的后面：

格式：alter  table   表名   add  新字段名    字符类型(字符长度  ) AFTER    原表名 ;

alter  table   hzdcs10 add  tz  int(10) AFTER age ;

(三)表中插入数据

表中插入数据：
1、表中插入数据：insert  into   表名    values  （插入值1，插入值2）   注入：插入的值，只有数值不需要加引号，字符要加上引号
如：INSERT  into hz10 VALUES  (1,"文琪",18);
2、表中插入数据：insert  into   表名 （id）   values  （插入值1）   

INSERT  into hz10(id) VALUES；

三、MySQL 查询语句（单表查询）
单表查询

（1）查询一个表中的所有数据
格式：selec * from 表名  
案例：select * from hz10；
注意：* 表示所有，每个语句后面加上分号

（2）查询具体的某个字段
格式：select 字段名1，字段名2，字段名3 from 表名；
案例：select name ,age from hz13

（3）查询表中具体的字段取别名（别名是为了在查看数据时方便）
例子：select name as "姓名"，age “年龄” hz13;（as可以省略不写，as表示取别名）
（4）查询指定内容，用where+条件
格式select * from 表名 where 字段名=101
select * from emp where dept2=101


where 条件使用注意：
比较运算符
(1)where+条件（=、！=、> 、<、>=、<= 、<>）
案例
（2）and(与)同时满足所有条件
比如条件1条件2都要满足
案例
（3）or(或)当有多个条件时，满足其中任意一个即可显示，条件1和条件2同时存在，只要满足条件1 就显示，只要满足条件2就显示
（4）between...and..在...范围之间（包含了本身）
select * from emp
（5）in（在一组数据中匹配）
格式：select * from 表名 where 表字段名 in（102，103，105）
（6）is null为空is not null
格式：select * from 表名 where 字段名 is null


多行注释：CTRL+/
取消多行注释：CTRL+shift+/
单行注释：直接按#号

四、排序：
对表中数据进行排序：order by   asc升序（可省略）  desc (降序)
升序：
格式：select * from 表名 order by 字段名 asc;
案例1：select * from emp order by age asc;
案例2：select * from emp order by age

降序
格式：select * from 表名 order by 字段名 desc;
案例：select * from emp order by age desc;


二次排序
select * from emp order by dept2 desc,incoming asc;
select * from emp order by 字段1 desc,incoming 字段2;

模糊匹配查询 like
%:表示匹配0个字符或多个字符
—：表示一个字符
select * from emp where dept like "1%"查询1开头的
select * from emp where dept like "%2%"匹配中间含有2 的
select * from emp where dept like "%2"匹配结尾包含2的数据
select * from emp where dept like "_2"匹配固定的字符2编号数据


limit 限制查询后面接两个值，第一个值表示下标（索引），第二个值是步长(行数)
备注：一个表中索引是从0开始的
案例1 select * from emp limit 0,3
案例1 select * from emp limit 3表示直接查询三行
格式：select * from表名 limit 索引,步长；

分组查询：group by一般不会单独使用，通常与函数组合使用
group by后查询出来的结果，在需要的条件下可以接having
select max(age) as s from emp group by dept2 having s >50;
having +条件和where+条件一样，场景不一样，一般group by 后接having
group by 和having组合
select max(age) from emp group by dept2;查询最大年龄
备注：函数求出以后，需要设置成别名，设置别名的字段进行语句的操作。

select max(age) from emp;
select name ,max(age) from emp group by(默认结果)
改为：elect  dept2 name ,max(age) from emp group by(默认结果)
mysql语句
max 最大值
案例：select 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(dept) from emp;


update....set  改
update emp set  name="成权" where sid=1789;
update 表名 set  修改字段="字段新值" where 条件；


删
delete 删除
truncate 快速删除表内数据
drop 删除
删除速度drop>truncate>delete
注意：1、drop是删除表和数据
2、truncate删除不可恢复
3、delete 删除可以恢复

delete
(1)删除表中所有数据
格式：delete from 表名
案例：delete from hz13
（2）删除表中指定数据
delete from 表名 where 条件
delete from 、emp where dept=103
(4)truncate
truncate 表名

表取别名
格式：表别名设置
select s.dept2  s.name from emp as s;

备份表结构：
create table hz11 like emp;

备份数据
insert into 新表 有表结构 select* from emp;
insert into 新表 hz11 select* from emp;

备份部分数据insert into 新表 hz110（sid,dept2） select sid dept2 from emp;

备份表结构和数据
create tabla hzdcs110 as（select * from emp）
再查询一下select

在Linux操作备份(>)
备份数据 mysqldump -u root -p hz10 >hz10bf.sql
备份后，要将文件到项目里面，还原数据
还原数据库
1、先建立空库create database 库名
2、在Linux中还原（>）
3、mysqlyuserser -u root -p 库名 <hz10bf.sql

五、MySQL 查询语句（多表查询）
1、什么是多表关联查询？
定义：查询数据来源于多张表。

主要内容
1、内连接：分为普通内连接和隐藏内连接
2、左连接（左外链接）
3、右链接（右外链接）
4、全连接（全外连接）

1、笛卡尔积查询（了解）
根据两张表相乘得到的结果：比如：左边有4条数据，右边有6条数据，查询出来就是4*6=24条数据，这种查询数据包含大量的错误结果，通常不会使用这种查询。
格式：SELECT   * from  表1,表二;
案例：SELECT   * from  dept,emp ;

2、内连接（普通内连接，隐藏内连接）
查询两个表共有的关联数据。
2.1普通内连接：
格式：select   *  from  表1    inner join 表2 on 表1.关联字段1=表2.关联字段2
案例1：select   *  from  dept  inner join emp on dept.dept1=emp.dept2 ;
案例2：select   name,dept_name  from  dept  inner join emp on dept.dept1=emp.dept2  where sid=1789 ;


2.2隐藏内连接
格式：select   *  from  表1，表2   where   表1.关联字段1=表2.关联字段2
select   *  from  dept,emp where   dept.dept1=emp.dept2 ;

3、左连接
以左表为主（显示完整的左表），游标关联的数据就显示，没有的数据就以null显示
格式：
select   *  from  表1  left  join  表2 on       表1.关联字段1=表2.关联字段2 ;
案例：
select   *  from  dept  left  join emp on dept.dept1=emp.dept2   ;

4、右连接
右表是以右表数据为主（显示整个右表）左表有关联的数据就显示，没有就以null值显示。
格式：select   *  from  表1  right   join  表2 on       表1.关联字段1=表2.关联字段2 ;
select*  from  dept  right join emp on dept.dept1=emp.dept2   ;

5、左表独有数据
左表中独有的数据显示，（方法，左独有，以右表字段为空查询）
格式：select   *  from  表1  left  join  表2 on       表1.关联字段1=表2.关联字段2   where     右表字段  is  null  
select   *  from  dept  left  join emp on dept.dept1=emp.dept2   where   name is  null;

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

6、左表独有数据+右表独有数据：
union  拼接
左独有  union  右独有
select   *  from  dept  left  join emp on dept.dept1=emp.dept2   where   name is  null
union
select   *  from  dept  right  join emp on dept.dept1=emp.dept2   where  dept1  is  null;

7、全连接
方法一：左独有+右独有+内连接
select   *  from  dept  left  join emp on dept.dept1=emp.dept2   where   name is  null
union
select   *  from  dept  right  join emp on dept.dept1=emp.dept2   where  dept1  is  null
UNION
select   *  from  dept    inner  join  emp on dept.dept1=emp.dept2   ;


方法二：左连接+右独有
select   *  from  dept  left  join emp on dept.dept1=emp.dept2  UNION
select   *  from  dept  right  join emp on dept.dept1=emp.dept2   where  dept1  is  null ;

方法三：右连接+左独有
select   *  from  dept  right join emp on dept.dept1=emp.dept2   UNION
select   *  from  dept  left  join emp on dept.dept1=emp.dept2  where   name is  null ;

总结：
普通内连接：
select  *  from  表1  inner  join    表2  on   表1 的关联字段=表2的关联字段 ；
隐藏内连接：
select  *  from  表1 ，  表2  where    表1 的关联字段=表2的关联字段 ；
左连接
select  *  from  表1  left   join    表2  on   表1 的关联字段=表2的关联字段 ；
右连接：
select  *  from  表1  right  join    表2  on   表1 的关联字段=表2的关联字段 ；

左独有数据：
select  *  from  表1  left   join    表2  on   表1 的关联字段=表2的关联字段   where  右表字段为空；

右独有数据：
select  *  from  表1  right   join    表2  on   表1 的关联字段=表2的关联字段   where   左表字段为空；


六、mysql查询语句（三表查询）




student表和课程表course无关联

课程表course与选修课程sc表中关联字段 c_no

student表和选修课程sc表 中关联字段 stu_no



三表内连接合并：

方法一：三表的隐藏内连接
格式：
SELECT * FROM 表1 as 别名, 表2 as 别名, 表3 sc as 别名 where 表1.关联字段1=表3.关联字段3 and 表2.关联字段2= 表3.关联字段3

案例：SELECT * FROM student as a,course as b,sc as c where a.stu_no=c.stu_no and b.c_no=c.c_no

方法二：三表普通内连接
SELECT * FROM 表1 as 别名 inner join 表3 as 别名 on 表1.关联字段1=表3.关联字段3 inner join 表2 as 别名 on 表2.关联字段2= 表3.关联字段3

案例SELECT * FROM student as a inner join sc as c on a.stu_no=c.stu_no INNER JOIN course as b on b.c_no=c.c_no

方法三：三表左连接
格式：
SELECT * FROM 表1 as 别名 left join 表3 as 别名 on 表1.关联字段1=表3.关联字段3 left join 表2 as 别名 on 表2.关联字段2= 表3.关联字段3
案例：SELECT * FROM student as a left join sc as c on a.stu_no=c.stu_no LEFT JOIN course as b on b.c_no=c.c_no

方法四：三表右连接

格式：SELECT * FROM 表1 as 别名 right join 表3 as 别名 on 表1.关联字段1=表3.关联字段3 right join 表3 as 别名 on 表2.关联字段2= 表3.关联字段3

案例：SELECT * FROM student as a right join sc as c on a.stu_no=c.stu_no right JOIN course as b on b.c_no=c.c_no

方法五：先合两个表在和合一个表的方法
SELECT * FROM (SELECT c_no FROM student as a,sc as c where a.stu_no=c.stu_no ) s INNER JOIN course as b where s.c_no=b.c_no

1、写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
方法一：引用隐藏内连接三表 and 加条件
select a.stu_no,a.stu_name from student a,course b,sc c where a.stu_no=c.stu_no and b.c_no=c.c_no and c_name=‘计算机原理’;

方法二：引用左连接 三表 where 加条件
select s.stu_no,s.stu_name from student s left join sc on s.stu_no=sc.stu_no left join  course c on c.c_no=sc.c_no where c.c_name=‘计算机原理’

方法三：先根据课程表找出课程编号，根据课程编号求出选修表中的学生号，根据学生号在求出学生表的学生号和姓名
select stu_no,stu_name from student where stu_no in(select stu_no from sc where sc.c_no=(select c_no from course c where c.c_name=‘计算机原理’))

方法四：先合三表，取别名，在加条件 筛选出结果
SELECT s.stu_no,s.stu_name FROM (SELECT a.stu_no,a.stu_name,b.c_name FROM student as a,course as b,sc as c WHERE a.stu_no=c.stu_no AND b.c_no=c.c_no) as s WHERE s.c_name=‘计算机原理’;

方法五：先合两表求出，求出学生号，在合并学生表
select a.stu_no,a.stu_name from (select b.c_name,c.stu_no from course b,sc c where b.c_no=c.c_no and b.c_name=“计算机原理”) d inner join student a on d.stu_no=a.stu_no ;


2、写一个SQL语句，查询“小明”同学选修的课程名称
条件：a.stu_name='小明' 结果：b.c_name
方法一：select b.c_name from student as a,course as b,sc as c where  a.stu_no=c.stu_no and b.c_no=c.c_no and a.stu_name='小明'；
方法二：select b.c_name from（select c_no,stu_name from student a inner join sc as c on a.stu_no=c.stu_no）f  inner join course as b on f.c_no=b.c_no  where f.stu_name=‘小明’；
方法三：select b.c_name from (select a.stu_name ,c.c_no from student a,sc c where a.stu_no=c.stu_no and a.stu_name=“小明”) d inner join course b on d.c_no=b.c_no;


3、写一个SQL语句，查询选修了5门课程的学生学号和姓名
方法一：
select stu_no,stu_name from student a where stu_no in(select stu_no from sc group by stu_no having count(c_no)=5)
方法二：
select a.stu_no,a.stu_name from student a,sc where a.stu_no=sc.stu_no group by sc.stu_no having count(sc.stu_no)=5;