MySQL数据库基础指令

1.连接mysql

语法：

mysql -u 用户名 -p密码
mysql -u root -p123

mysql -u 用户名 -p密码 -h 数据库服务器的地址（ip地址） -D  数据库名

安装mysql后，默认有一个管理员 root ,密码是安装是设置的密码

退出： exit

2.数据库

数据库软件可以为我们管理多个库。 数据库实质就是磁盘上的物理文件。

安装后有几个默认的数据库， 自带的数据库不要随意删除，和修改，尽量操作自己新建的数据库。

2.1 查看数据库

show databases  :  查看可用的数据库
use 数据库名     ：   选择要使用的数据库
select database(): 查看当前正在使用的数据库

mysql库是系统库，包含了mysql相关的系统信息，不要修改。

2.2创建库

语法：

create database  数据库名 charset utf8;

create database if not exists 数据库名 charset utf8;  -- 不存在，才创建。
-- 示例：
create database if not exists wbs20041 charset utf8;

2.3删除库

语法：

drop database 数据库名； --  很厉害  不要随便用
drop database if exists 数据库；
-- 示例
drop database wbs20041;

3.表

数据库中存储的东西以表为单位，每个库中可以放多个表

表中用来存放数据， 需要定义数据的 名称（列名/字段名） 类型

3.1 数据库中的类型

 整数： int    smallint    bigint

 小数：float   double

 字符串：  varchar -------  (char   String)

                 需要指定字符长度  ：  varchar(30)

日期类型： date  

比尔类型：bit  

其他：   clob  存储文本大数据

              blob  存储二进制数据

3.2创建表

语法：

create table 表名
(  
   列名  数据类型  特征，
   列名  数据类型  特征，
   ...
   列名  数据类型  特征
   
);

注意: 列名不要写中文   
     特征暂时没有  
     多个列之间用逗号分隔， 所有符号都必须是英文状态下的

-- 示例
create table userinfo(
	id   int,
    username varchar(30),
    password varchar(40),
    age  int
);


show tables; -- 查看数据看中的表
desc 表名； -- 查看表的结构（列名  类型  特征）

-- student 表
-- stuid   name  age sex  address height birthday 
create table student(
	stuid int,
    name  varchar(30),
    age int,
    sex varchar(5),
    address varchar(40),
    height float,
    birthday date
)

3.3 更新表

• 添加列
  alter table 表名 add 列名 数据类型；
  – 示例
  alter table student add weight float;

• 修改列的类型
  alter table 表名 modify 列名 数据类型；
  – 示例
  alter table student modify weight double;

• 修改列名
  alter table 表名 change 原列名 新列名 数据类型；
  – 示例
  alter table student change weight tizhong double;

• 删除列
  alter table 表名 drop 列名；
  – 示例
  alter table student drop tizhong;

• 修改表名
  alter table 原表名 rename 新表名；

  rename table 原表名 to 新表名；
  
  -- 示例
  alter table student rename stu;
  

3.4删除表

语法

drop table 表名；
drop table if exists 表名；

4.约束

constraint 对表中的数据进行限制，保证数据的有效性。

4.1约束分类

五种约束：

• 主键约束 primary key
  用来唯一标识一条数据，本身不能为空，表中设为主键的列可以没有实际含义，建议一个表设置一个主键（id）

• 唯一约束 unique
  不允许重复

• 检查约束 check
  判断数据是否符合指定的条件
  mysql中检查约束不起作用， 后期可以通过sql编程来解决

• 非空约束 not null
  不允许为null,但可以为空字符串

• 外键约束（foreign key）
  用来关联两个表
  班级表
  班级编号（主键） 班级名称 班级人数 开班时间
  1 java
  2 php

  学生表

  学号（主键） 姓名 年龄 性别 班级编号 （外键列）

  1001 tom 12 女 1

  1002 jack 13 女 1

  学生表中的班级编号 不能随意填写， 有约束（班级表总存在的），需要引用班级表中的数据

  此时，班级编号设置为外键，关联两张表

4.2 添加约束

4.2.1 创建表的同时创建约束

-- 主表
create table class(
	cid int primary key auto_increment,-- 主键约束  自动增加 
    cname varchar(30) not null  -- 非空约束
)；

-- 子表
create table student(
	stuid  int  primary key auto_increment,
     name  varchar(30)  not null,
     sex	varchar(6),
     phone varchar(30) unique,  -- 唯一约束
    age    int   check(age between 1 and 100),  -- 检查约束
    classid int,  -- 外键
    foreign key (classid)  references class(cid)  
    -- foreign key (外键名) references  要引用的表名（具有要引用的列名）

);

注意： 先创建主表，再创建子表
      先删除子表，再删除主表

4.2.2 表创建后单独创建约束

alter  table 表名  add  constraint  约束名 约束的类型  约束内容
约束名：自定义的一个名字

alter  table student  add  constraint pk_stuid primary key (stuid);
alter  table student  add  constraint ck_age  check(age between 1 and 100);
alter  table student  add  constraint uq_phone  unique (phone);
alter  table student  add  constraint fk_classid foreign key (classid) references  class(cid)

4.2.3 删除约束

• 删除主键 alter table 表名 drop primary key
• 删除外键 alter table 表名 drop foreign key 约束名
  （show create table 表名 表的具体信息， 可以看到约束名）
• 删除唯一约束 alter table 表名 drop index 约束名
• 删除非空 alter table 表名 modify 列名 类型 null

4.2.4 注意事项

• 建议一个表一个主键，可以有多个外键
• 先创建主表，再创建子表
• 先删除子表，再删除主表
• 外键和引用的列 数据类型一致，被引用的列是唯一的

5.名词说明

SQL 语句： 结构化查询语言，用来对数据库进行查询 更新 和管理的一种语言

包含了三个部分

• DML : 数据操作语言
  用于对数据库中存储的数据进行增删改查 ： insert delete update select
• DDL: 数据定义语言
  用于定义数据的结构： create alter drop
• DCL: 数据控制语言
  用于定义数据库的权限： grant revoke

三.DML(增删改查 )

DML对数据的 增删改 — 更新

                      查  ------   查询

1.insert

语法：

-- 语法一
insert into 表名 （列名1，列名2，，，）  values (值1，值2，，，，)；
select * from 表名；  --  查询表中的所有信息
-- 语法二
insert into 表名 （列名1，列名2，，，）  values (值1，值2，，，，)  ,  (值1，值2，，，，)
-- 示例
insert into userinfo (id,username,password) values (1,'tom','123');
insert into userinfo (id,username,password,age) values (3,'rose','1243',12),
(4,'jack','1213',13),
(5,'jim','1236',14);

注意：

• varchar和date 类型的数据 需要用单引号将值引起来 ‘1990-01-01’

• 列与值要一一对应(个数 顺序 类型)
  dept 部门表

  列名 类型 含义
  deptno int 整数 部门编号
  dname varchar 字符串 部门名称
  loc varchar 字符串 部门位置

  emp 员工表

  列名 类型 含义
  empno int 整数 员工编号
  ename varchar 字符串 员工姓名
  job varchar 字符串 职位、工作
  mgr int 整数 经理或上司的编号
  hiredate date 日期 入职日期
  sal double 小数 工资、薪水
  comm int 整数 奖金
  deptno int 整数 部门编号

  salgrade 工资等级表

  列名 类型 含义
  grade int 整数 编号
  losal int 整数 工资下限
  hisal int 整数 工资上限

2.delete

语法：

delete from 表名； -- 删除整个表的数据   慎用！！！
delete from 表名  where 条件； -- 根据条件删除某些数据

-- 示例
-- 删除  名叫 jones的员工信息
   delete from emp where ename='jones';
-- 删除 工作为秘书 的员工信息
   delete from emp where job='秘书';
-- 删除 销售部（deptno 为30）工资高于5000的员工
   delete from emp where deptno=30 and sal>5000;

3.update

语法：

update 表名 set 列名1=值1，列名2=值2，，，， where 条件；

-- 示例
-- 修改王五的地址
 update student set address='南极' where name='王五'；
-- 将销售部修改为市场部
update dept set dname='市场部' where dname='销售部'；
-- 将smith的 工资修改为888  奖金为666  职位为 经理

update emp set sal=888,comm=666,job='' where ename='smith';

4.select

4.1基本查询

语法：

-- 查询所有
select * from 表名；
-- 指定列查询
select 列名1，列名2，，，， from 表名；
-- 别名查询
select 列名 别名， 列名2  别名，，， from 表名；

-- 示例
-- 查询 所有员工的姓名、
select ename from emp;
-- 查询 姓名  工资 工作 
select ename 姓名，sal 工资，job 工作 from emp;
-- 姓名 工资  入职时间

• 可以使用四则运算 + - * /
  – 查询员工的姓名 和年薪
  select ename 姓名,(sal+comm)*12 年薪 from emp; – 问题
  – ifnull(x,y) 如果x为null 则用y来替代 if(comm,0)
  select ename 姓名,(sal+ifnull(comm,0))*12 年薪 from emp;
  注意： 在mysql中 null 与任何值进行运算，结果都为null(空)
• distinct 去除重复列
  – 查询所有的职位
  select distinct job from emp;

4.2限定查询

语法：

select 列名1，列名2，列名3，，，
from 表名
where 条件；

4.2.1 比较运算符

>  <   >=   <=  =  !=

-- 查询 工资大于 1500的员工信息
select * from emp where sal >1500;
-- 查询编号不是7369的员工信息
select * from emp where empno!=7369;
-- 查询姓名为 smith的员工 编号  姓名  工资 入职时间
select empno,ename,sal,hiredate from emp where ename='smith';

4.2.2 null 或者not null

-- 查询没有奖金的员工信息
select * from emp where comm is null or comm= 0;

注意： 判断是否为null 使用is , 不能使用 =

4.2.3 and or

-- 查询 工资大于1000 并且有奖金的员工 姓名  工资  奖金
select ename,sal,comm from emp where sal >1000 and comm is not null;
-- 查询从事销售工作，或者工资大于等于2000的员工信息

4.2.4 between and

-- 查询 工资 大于1500 小于3000 的信息
select * from emp where sal between 1500 and 3000;
注意：包含临界值

-- 查询1981 年入职的员工  姓名  入职时间  部门编号
select ename,hiredate,deptno from emp where hiredate between '1981-01-01' and '1981-12-31';

注意： 日期用单引号引起来

4.2.5 in 或者 not in

-- 查询编号为 7369,7566,7788 的员工信息  
select * from emp where empno in (7369,7566,7788);

-- 查询 smith allen jack 的入职时间   工资

4.2.6 like

like 后接匹配的条件，需要用‘ ’ 引起来

用于进行模糊查询，需要和通配符一起使用

通配符：

• % 可以匹配任意长度的字符 张%

• _ 只能匹配单个字符 张__

  – 查询姓名以d 开头的员工信息
  select * from emp where ename like ‘d%’
  – 查询 员工姓名中包含 a的员工信息；
  select * from emp where ename like ‘%a%’;
  – 查询 从事销售工作，并且名字长度为4个字符的员工信息
  select * from emp where job=‘销售员’ and ename like ‘____’;

4.3排序

4.3.1 基本用法

语法：

select ......
from 表名
where 条件
order by  排序列1 asc|desc, 排序列2 asc|desc

   asc:升序   desc 降序，  默认 按照升序

-- 查询员工信息 ， 工资的由低到高
select * from emp order by sal;
-- 查询部门编号10 的员工，按工资由高到底 排序，如果工资相同 ，则按入职时间的从早到晚排序
select * from emp where deptno=10 order by sal desc,hiredate asc;

-- 查询员工的编号  姓名  年薪  按年薪的由高到低排序
select empno,ename,(sal+ifnull(comm,0))*12  from emp order by (sal+ifnull(comm,0))*12 desc;

4.3.2limit

用来限制查询返回的记录数

-- 查询工资前三名的员工信息
select * from emp order by sal desc limit 3;
limit :后面接一个整数  表示查询几行（返回几条数据）

select * from 表名 limit 参数1，参数2；

参数 1： 起始行的索引   从0开始
参数 2 ： 用来指定返回的行数

首页   上一页  下一页  尾页  跳转_页   1/5
1       --     ++     ？             

用户给的数据为：当前页数 3

总的行数               每一页显示的行数       共计页数
  21 （数据库）             5（自定义）        5
  
总的行数 % 一页的显示行数 == 0 ？ 总的行数 /一页的显示行数 ： 总的行数 /一页的显示行数 +1

显示第3页的数据，应该显示   11 ---15   

select * from emp limit 10 , 5
select * from emp limit 一页显示的行数*（当前页数-1） , 一页显示的行数
参数1： 是一个动态数据，根据用户输入的当前页数，算出具体值

4.4聚合函数和分组查询

4.4.1 聚合函数

聚合函数 ， 称为统计函数

常用函数：

• count() 求总的行数
  count(*): 求表中总的行数 count(主键列名) count(0)
  count(列名)： 求有值的列的 行数

• max() 最大值

• min（）最小值

• sum( ) 和

• avg() 平均值

  – emp表中的 总人数
  select count(*) from emp;

  – 有奖金的人数
  select count(comm) from emp;

  – 查询部门30的总人数
  select count(*) from emp where deptno=30;

  – 工资最高的员工的工资
  select max(sal) from emp;

  – 查询部门30的最高工资，最低工资，工资总和，平均工资；
  select max(sal),min(sal),sum(sum),avg(sal) from emp where deptno = 30;

4.4.2分组查询

语法

select .....
from 表名
where 分组前的条件
group by 分组列
having  分组后的条件
order by 排序列

-- 查询每个部门的平均工资
 select deptno 部门编号， avg(sal) 平均工资 from emp group by deptno ;
-- 查询 部门编号， 每个部门的员工人数 
 select deptno 部门编号， count(*) 员工数量 from emp group by deptno ;
-- 查询平均工资大于2000的部门编号 和 该部门的平均工资
select deptno 部门编号， avg(sal) 平均工资 from emp  group by deptno having avg(sal) > 2000;
--  查询每个部门中秘书的平均工资
select avg(sal) from emp where job='秘书' group by deptno;

SQL 语句的执行顺序

from
join
on				明确数据源，数据从哪张表取出来
where             对数据筛选
group by		 对数据分组
avg,sum....  
having
select           明确具体查询的数据
distinct
order by
limit             对数据结果处理

写的顺序：  select   from  where  group by  having  order by  limit
执行属性：  from    where   group by  having select  order by limit

四.多表查询

1. 简介

同时从多张表中查询出数据，多张表之间一定联系

2.基本用法

2.1语法

• 查询两张表数据
  select * from 表1，表2；
  select * from emp,dept; – 重复数据（笛卡尔积）

• 通过两张表中关联的列（字段）进行比较，去笛卡尔积
  select … from 表1，表2
  where 条件 表1.列名 = 表2.列名 – （ 用外键关联）

  -- 员工 编号  姓名  工资  部门名称  部门位置
  select empno,ename,sal,dname,loc from emp,dept
  where emp.deptno = dept.deptno and sal >3000;
  
  select empno,ename,sal,dname,loc from emp e,dept d
  where e.deptno = d.deptno and sal >3000;
  
  
  -- 1、     查询所有学生的Sname、Cno和Degree列。
   select sname,cno,degree from student,score where  student.sno=score.sno;
  
  -- 2、     查询所有学生的Sno、Cname和Degree列
  select sno,cname,degree from score,course where score.cno = course.cno; 
  
  -- 3、     查询所有学生的Sname、Cname和Degree列
  select sname,cname,degree from score,course,student  where
  student.sno = score.sno and course.cno = score.cno;
  

3.连接查询

3.1 内连接

语法：

select ... 
from  表1  inner join 表2 on 多表的关联关系

-- 查询所有学生的 sno  cname degree;

3.2 外连接

分类：

• 左外连接 left join … on 以左边的表为主表，无论怎样都会显示主表的数据
• 右外连接 right join… on 以右边的表为主表，无论怎样都会显示主表的数据

五.子查询

1.简介

一个查询中嵌套另一个完整的查询，称为子查询

• 子查询放在小括号中
• 子查询可以出现任意位置 select from where

2.用法

2.1语法

select （子查询）
from    （子查询）
where （子查询）
....

2.2 示例

-- 查询工资比 7566 高的员工信息

-- 子查询与运输符一起使用，子查询的结果只能返回一个结果

-- 查询工资比7654 高，同时又与 7900从事相同工作的员工信息

-- 查询工资最低的员工姓名

-- 查询工资高于公司 平均工资的员工信息

六.常用函数

1.字符串函数

• concat(s1,s2,s3….) 拼接字符串
  select concat(‘aa’,‘bb’,‘cc’)
  select concat(‘aa’,‘bb’,‘cc’) from dual;
  select CONCAT(‘编号为’,empno,‘的员工姓名为’,ename) from emp;
  注：dual表是MySQL提供的一张虚拟表，主要是为了满足select…from…语法习惯，一般测试时使用，无实际意义
• lower(s) 将字符串变为小写select lower(‘Hello’) from dual
• upper(s) 将字符中变为大写select upper(‘Hello’) from dual
• length(s) 获取字符串的长度select length(‘hello’) from dual
• reverse(s) 将字符串反转select reverse(‘hello’) from dual
• trim(s) 去除字符串两边的空格select trim(’ hello ') from dual，还有ltrim()和rtrim()，去除左边或右边的空格
• replace(s,s1,s2) 将字符串s中的s1替换为s2
  select replace(‘hello world’,‘o’,‘xx’) from dual
• repeat(s,n) 将字符串s重复n次后返回 select repeat(‘hello’,3) from dual
• lpad(s,len,s1) 在字符串s的左边使用s1进行填充，直至长度为len
  select lpad(‘hello’,8,‘x’) from dual
• rpad(s,len,s1) 在字符串s的右边使用s1进行填充，直至长度为lenselect rpad(‘hello’,8,‘x’) from dual
• substr(s,i,len) 从第i个位置开始对字符串s进行截取，截取len个
  select substr(‘hello’,2,3) from dual

2.数值函数

• ceil(n) 返回大于n的最小整数 select ceil(10.1) from dual
• floor(n) 返回小于n的最大整数 select floor(10.1) from dual
• round(n,y) 将n保留y位小数，四舍五入 select round(3.1415,3) from dual
• truncate(n,y) 将n保留y位小数，不四舍五入 select truncate(3.1415,3) from dual
• rand() 返回0到1的随机数 select rand() from dual

3.日期和时间函数

• now() 返回当前日期时间 select now() from dual
• curdate() 返回当前日期 select curdate() from dual
• curtime() 返回当前时间 select curtime（） from dual
• year(date) 返回日期中的年 select year(‘2018-2-14’) from dual
• month(date) 返回日期中的月 select month(‘2018-2-14’) from dual
• day(date) 返回日期中的日 select day(‘2018-2-14’) from dual
• timestampdiff(interval,datetime1,datetime2) 返回两个日期时间之间相隔的整数，
  单位由interval定义
  interval可取值：year、month、day、hour、minute、second
  select timestampdiff(day,‘1993-9-23’,‘2018-11-22’) from dual
• date_format(date,pattern) 格式化日期
  select date_format(now(),’%Y年%m月%d日 %H:%i:%s’) from dual
  格式化参数：
  %Y 表示四位数字的年
  %m 表示两位数字的月
  %d 表示两位数字的日
  %H 表示两位数字的小时，24小时制
  %i 表示两位数字的分钟
  %s 表示两位数字的秒数

4.流程控制函数

• if(f,v1,v2) 如果f为真，则返回v1，否则返回 v2 select if(5>2,‘yes’,‘no’) from dual
• ifnull(v1,v2) 如果v1不为null，则返回v1，否则返回v2 select ifnull(null,‘0’) from dual
• case when f1 then v1 when f2 then v2….else v end 如果f1为真，则返回v1；如果f2为真，则返回v2…否则返回v
  select case when 5>2 then ‘yes’ end from dual;
  select case when 5<2 then ‘yes’ else ‘no’ end from dual;
  select case when 5<2 then 'one ’
  when 6>4 then ‘two’
  else ‘three’ end
  from dual;

七.事务

1.简介

transaction 事务， 用来保证数据的操作完整性

一个业务中由多个小的业务构成，要么都成功，要么都失败

事务的特性：（ACID）

• 原子性（Atomicity）： 不可再分
• 一致性（Consistentcy）:要保证数据前后的一致性
• 隔离性（isolation）: 两个事务的操作互不干扰
• 持久性（durability）:一旦事务提交，不可回滚

2.事务

mysql默认的是自动提交事务，将一条sql语句都作为一个独立的事务操作

查询提交模式： show variables like ‘autocommit’

设置手动提交： set autocommit = off

提交：commit

回滚： rollback

八.用户和权限管理

1.创建用户并授权

语法：

grant 权限列表  on 库名.表名   to  用户@地址  identified by '密码'；

只有管理员才具有创建用户的权限

test库是安装时默认创建的，默认情况下所有的用户对该数据库具有最大权限

2.查询权限

show grants; -- 查看自己的权限
show grants for 用户名@地址； -- 查看其他用户权限

3.撤销权限

revoke 权限列表 on 库名.表名   from  用户@地址

4.删除用户

drop user 用户名@localhost；

use mysql;
delete from user where User='用户名'

练习：

-- 基本查询

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
   
2、 查询教师所有的单位即不重复的Depart列。

3、 查询Score表的所有记录。

4、 查询Score表中成绩在60到80之间的所有记录。

5、 查询Score表中成绩为85，86或88的记录。

6、 查询Student表中“95031”班或性别为“女”的同学记录。

7.查询分数大于70，小于90的Sno列。

8.查询95033班和95031班全体学生的记录。

9.查询Student表中不姓“王”的同学记录。



-- 排序

1.查询Score表中的最高分的学生学号和课程号。

2.以Cno升序、Degree降序查询Score表的所有记录

-- 聚合函数
1、 查询“95031”班的学生人数。
   
2.查询Student表中最大和最小的Sbirthday日期值。
 
-- 分组
1、 查询每门课的平均成绩。

2、查询至少有2名男生的班号。

3、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。





-- 子查询
1.查询所有任课教师的Tname和Depart.

2查询选修了“3-105”课程的学生中 成绩高于“109”号同学成绩的 学生信息

3查询“95033”班学生的平均分




-- 多表
1、     查询所有学生的Sname、Cno和Degree列。

2、     查询所有学生的Sno、Cname和Degree列

3、     查询所有学生的Sname、Cname和Degree列