MySQL学习笔记

  写于    更新于  笔记

期末了，梳理一遍MySQL，防止挂科。

数据库的管理

数据库的创建

例：创建一个名称为mysqltest的数据库

1
create database if not exists mysqltest;

例：创建教务管理数据库teaching，并指定字符集为gb2312，校对原则为gb2312_chinese_ci。

1
2
3
create database teaching 
default character set gb2312
default collate gb2312_chinese_ci;

打开并设置数据库

例：打开前面创建的teaching数据库，设置其为当前默认的数据库

1
use teaching;

修改数据库参数

例：将前面创建的teaching的默认字符集设置为UTF8，校对规则为utf8_unicode_ci

1
2
3
alter database teaching
default character set utf8
default collate utf8_unicode_ci;

查看字符集与校对规则

1
2
show variables like ‘char%’;
show variables like ‘collation_%;

显示数据库结构

1
show create database teaching;

删除数据库

例：删除前面创建的teaching数据库

1
drop database teaching;

数据表的管理

数据表的创建

例：创建学生成绩管理系统数据库中的学生表：

1
2
3
4
5
6
7
create table student (
    studentno char(8),
    name varchar(10),
    sex enum('男','女'),
    birthday date,
    classno char(7)
);

数据表的修改

添加列（字段）

1
2
3
alter table 表名
add 新列名 数据类型
[约束条件] [ first | after 已存在列名];

例：在student表中添加一个电话字段（tel），将其放到sex字段之后：

1
2
alter table student 
add tel char(11) after sex;

修改列

例：修改student表中的tel字段的列名为Email，数据类型为varchar(30);

1
alter table student change tel Email varchar(30);

删除列

例：删除student表中的Email列;

1
alter table student drop Email;

修改表名称

例：将student表重命名为stu:

1
alter table student rename to stu;

修改列的数据类型和位置

例：修改student表中的Email字段的数据类型为varchar(20)，将其移动到birthday字段之后;

1
2
alter table student 
modify Email varchar(20) after birthday;

删除表

例：删除stu表;

1
drop table stu;

插入数据

例：给教师表添加两条记录(‘T01’,‘张三’和‘T02’,‘李四’)

1
2
3
4
insert into teacher(teacherno,name) values ('T01','张三');
insert into teacher values('T02','李四');
- 或
insert into teacher values(‘T01’,‘张三’),('T02','李四');

例：利用set子句向teacher表插入数据

1
insert into teacher set name='王五', teacherno='T03';

修改数据

例：给将学号为61130702的学生姓名修改为孙乐然

1
update student set name='孙乐然' where studentno='61130702';

删除数据

例：将成绩表中C02成绩最低的1个学生的信息删除

1
2
delete  from score where courseno='C02'
order by finalscore limit 1;

例：清除成绩表中的所有信息

1
delete from score;

表约束

  1. 非空约束

    • null：字段值可以为空

    • not null：字段值不允许为空

      例：创建教师表，设置教师编号为非空约束

      1
      2
      3
      4
      create table teacher(
      teacherno char(3) not null,
      name varchar(10)
      );

      例：修改教师表，设置姓名列为非空约束

      1
      alter table teacher modify name varchar(10) not null

      例：修改教师表，去除姓名列的非空约束

      1
      alter table teacher modify name varchar(10);

  2. 主键约束

    • 主键列的数据唯一，不允许为空，主键唯一标识表中一条记录。
    • 一张表中最多一个主键约束。

    • 主键可以是一个字段，也可以是多个字段。

      例：创建教师表，设置教师编号为主键约束

      1
      2
      3
      4
      create table teacher(
      	teacherno char(3) primary key,
          name varchar(10)
      );

      例：修改教师表，去除主键约束

      1
      alter table teacher drop primary key;

      例：修改教师表，设置教师编号列为主键约束

      1
      alter table teacher modify teacherno char(3) primary key;

  3. 唯一约束

    • 该列唯一，允许为空

    • 一个数据表只能创建一个主键，但可以有多个唯一约束

      例：创建教师表，设置教师编号为唯一约束

      1
      2
      3
      4
      create table teacher(
      	teacherno char(3) unique,
          name varchar(10)
      );

      例：修改教师表，去除唯一约束

      1
      alter table teacher drop index teacherno;

      例：修改教师表，设置教师编号列为唯一约束

      1
      alter table teacher modify teacherno char(3) unique;

  4. 默认约束

    • 指定某列的默认值

      例：创建学生表student，设置性别的默认约束为男

      1
      2
      3
      4
      5
      6
      7
      create table student(
      	studentno char(8),
          name varcahr(10),
          sex enum('男', '女') default '男',
          brithday date,
          classNo char(7)
      );

      例：删除学生表的默认约束

      1
      alter table student modify sex enum('男'， '女');

      例：对学生表student的sex列，修改默认约束为男

      1
      alter table student modify sex enum('男', '女') default '男';

  5. 外键约束

    • 建立子表与父表的关联，保证数据一致性，父表中信息更新，子表中对应的信息相应改变。
    • 原则：必须依赖于已存在的父表的主键

    • 外键可以为空值

      例：创建班级表，表中的teacherno字段为外键（主表是teacher表）

      1
      2
      3
      4
      5
      6
      7
      create table class (
          classno char(7) primary key,
          name varchar(20),
          teacherno char(3),
          foreign key(teacherno)
          references teacher(teacherno)
      );

      例：删除班级表teacherno字段上的外键约束

      1
      alter table class drop foreign key teacherno;

      例：在班级表的teacherno字段上添加外键约束

      1
      alter table class add foreign key (teacherno) references teacher(teacherno);

  6. 检查约束

    • 限定列的数据值范围

      例：创建成绩表，设置成绩字段的取值范围是0～100

      1
      2
      3
      4
      5
      6
      create table score(
      	courseno char(3),
          studentno char(8),
          finalscore tinyint default 0
          check(finalscore >= 0 && score <= 100)
      );

      例：删除成绩表上的检查约束

      1
      alter table score drop check score_chk_1;

      例：在成绩表的成绩字段添加检查约束（>=0）

      1
      alter table score add check(finalscore >= 0);

简单查询

distinct 消除重复行

  • distinct子句可以消除查询结果中的重复行

例：查找所有参加考试的同学的学号，重复的行只出现一次

1
select distinct stuno from tbl_score;

where in 判断过滤

  • where设置了查询条件
  • in关键字可以判断某个字段的值是否在集合中
  • 查询不在集合中使用not in
  • 多个判断条件使用and连接

例：查询学号为60115101,60115102,60115103,60115104四位同学的成绩

1
2
select * from tbl_score where stuno in
(60115101,60115102,60115103,60115104)

between and 指定查询范围

  • 排除范围使用not between

例：查询成绩在80—90之间的学号、课程号、成绩

1
select * from tbl_score where score between 80 and 90;

like 通配符查询

  • like关键字结合通配符，通过字符串比较来选择符合条件的行
  • “%” 表示0个或多个字符
  • “_”表示匹配一个字符

is null 查询空值

  • 查询非空值用 is not null

例：查找成绩表中没有成绩（即成绩为空）的行

1
select * from tbl_score where score is null;

聚合函数查询

count()

  • count(列名)函数返回非null行的行数
  • count(*)返回包含null的所有行

sum()

  • 求出某个字段的和

avg()

  • 求出某个字段的平均值

round()

  • 四舍五入
  • 第一个参数是原来的值
  • 第二个参数是小数位数

例：查询每位同学的总分和平均分（四舍五入到1位小数）

1
2
3
4
select stuno, sum(score) as 
total_score, 
round(avg(score), 1)
from tbl_score group by stuno;

max() min()

  • 返回最大值和最小值

查询中的子句

order by

  • 对查询结果进行排序
  • 升序：asc，降序：desc
  • 默认升序，null值排最前面，降序则null值排最后

例：查询成绩表中C01课程的成绩，并按成绩降序排列

1
select * from tbl_score where courseno='C01' order by score desc;

group by

  • 将查询结果按指定字段进行分组
  • group by与group_concat()函数结合，可以将
    每个组中的所有字段值都显示出来
  • group_concat()的作用是链接字符串

例：使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录

1
select name, count(*) from employee_tbl group by name;

with rollup

  • 在分组统计的基础上再进行相同的总体统计
  • 要和group by一起使用，加在最后

having

  • 通常与group by子句结合使用
  • 指定在where子句的筛选后进一步筛选，即group by分组后的再筛选

例：查询选课有2门以上且课程成绩均高于80分的学生的学号及其总成绩（不计算80分以下的），结果按成绩降序排列

1
2
3
4
5
6
select stuno as '学号', sum(score) as '总分'
    from tbl_score
        where score >= 80
            group by stuno
                having count(*) >= 2
            order by sum(score) desc;

limit

  • 显示指定序号记录的结果
  • 第一条记录编号为0
  • 第一个参数为开始编号
  • 第二个参数为记录的个数

例：查询成绩表信息，按成绩降序排序，显示前3条记录

1
select * from score order by finalscore desc limit 3;

例：查询成绩表信息，按成绩降序排序，显示编号2开始的3条记录

1
select * from score order by finalscore desc limit 2,3;

内连接

  • 自然连接运算
  • 为了区分两个表中同名的列，需要对同名的列加上表名做为前缀。
  • 多表（两张以上）查询：先进行两表连接，结果再和第三张表进行连接

例：查询学生的学号、姓名、课程号和成绩。

1
2
3
select score.stuno, stuname, courseno, score
	from tbl_score score inner join tbl_student student
		on student.stuno=score.stuno;

左/右 外连接

  • 左连接

    将左表所有值对应右表查询，右表中没有的显示为null

例：查询所有学生的学号、姓名、班级号、班级名称。

1
2
3
select stuno, stuname, c.classno, classname 
    from tbl_student as s left join tbl_class as c
        on s.classno=c.classno;

  • 右连接

    将右表所有值对应右表查询，左表中没有的显示为null

1
2
3
select stuno, stuname, c.classno, classname 
	from tbl_student as s right join tbl_class as c
		on s.classno=c.classno;

合并多个查询结果

例：合并姓张和姓李的同学的查询结果

1
2
3
select * from tbl_student where stuname like '张% ' 
	union
select * from tbl_student where stuname like '李%';

exists()

  • 在where后用于判断是否存在
  • 返回True或False

例：查询是否存在班级号为‘P601119’的学生，如果存在输出学生信息

1
2
select * from tbl_student where exists(select *
	from tbl_student where classno='P601119');

视图

例：创建视图course_avg，统计各门课程平均成绩，并按课程名称降序排列

1
2
3
4
5
6
create view course_avg as 
	select tbl_course.coursename as 课程名, avg(score) as 平均成绩       
		from tbl_score inner join tbl_course            
   			on tbl_score.courseno= tbl_course.courseno 
           			group by tbl_course.courseno                   
 		 		order by tbl_course.coursename desc;

变量

  • 系统会话变量以两个@开头
  • 用户会话变量以一个@开头

用户会话变量

例：:创建用户会话变量@name，并赋值为“张三”

1
2
3
set @name=“张三”
- 或
select @name:=“张三”

局部变量

例：定义局部变量myvar，数据类型为int，默认值为100，并赋值60

1
2
declare myvar int default 100;
set myvar = 60;

自定义函数

例：创建一个名为func_course的函数，返回表course中指定课程号的课程名

1
2
3
4
5
6
7
delimiter &&
create function func_course(c_no char(3))
returns varchar(20)
begin	
    return(select coname from course where courseno=c_no);
end &&
delimiter ;

IF 条件控制

  • 函数中使用if

例：创建函数exam_if，判断传入的参数如果是10，是则输出1，否则判断是否是20，是则输出2，否则输出3

1
2
3
4
5
6
7
8
9
10
11
delimiter //
create function exam_if(x int)
returns int
begin
if x=10 then set x=1;
elseif x=20 then set x=2;
else set x=3;
end if;
return x;
end //
delimiter ;
  • 查询中使用if()

例：从成绩表中查询学号为60121901的学生成绩信息,如成绩大于等于80分，显示“通过”，否则显示“未通过”

1
2
3
select *,if(finalscore>=80,'通过','未通过') as '是否通过'
	from score 
		where studentno='60121901';
  • 查询中使用ifnull()

例：从成绩表中查询学号为60241902的学生成绩信息,如成绩为null，显示“没有成绩”，否则显示成绩

1
select * ,ifnull(finalscore,'没有成绩') from score where studentno='60241902';

while循环

例：定义函数exam_while,求1到n的和

1
2
3
4
5
6
7
8
9
10
11
12
delimiter //
create function exam_while(n int) returns int
begin 
declare sum int default 0;
declare m int default 1;
while m<=n do
set sum =sum+m;
set m=m+1;
end while;
return sum;
end //
delimiter ;

储存过程

  • 无参数

例：创建存储过程proc_stu，从数据库school的student表中检索出所有2001年出生的学生的学号、姓名、出生日期等信息

1
2
3
4
5
6
delimiter //
create procedure proc_stu()
begin	
select * from student where year(birthday)='2001';
end //
delimiter ;
  • 有参数

例：创建存储过程stu_score的，统计指定同学的考试门数

1
2
3
4
5
6
7
delimiter //
create  procedure  stu_scores(in s_no char(8), out count_num int)
reads SQL data
begin
select  count(*)  into count_num  from  score where  studentno=s_no;
end //
delimiter ;
  • 调用：call stu_scores(‘18125121107’, @c_num );
  • 查看：select @c_num;

触发器

  • 在表数据修改后激活的指令

例：创建一个触发器，当更改表course中某门课的课程号时，同时将score表课程号全部更新

1
2
3
4
5
6
7
delimiter $$
create trigger cno_update after update on course for each row
begin	
	update score set courseno=new.courseno 	
	where courseno=old.courseno;
end $$
delimiter ;
  • update on 更新后
  • delete on 删除后
  • insert 插入后

例：在de_teacher表插入记录前和插入记录后，分别向before_insert和after_insert中插入一条记录

1
2
3
4
5
6
7
8
9
create  trigger  before_insert  before insert
	on  de_teacher for each row
	insert into  bef_after
	set teacherno =‘t11’, name =‘孙悟空’;

	create  trigger  after_insert  after  insert
	on  de_teacher  for each row
	insert into  bef_after
	set teacherno =‘t22’, name =‘猪八戒’;

用户管理

  • 添加

    例：添加两个新用户，用户名zzz,密码zzz001,用户名qqq，密码qqq002

    1
    2
    3
    create user 
      'zzz'@'localhost'identified by 'zzz001',
      'qqq'@'localhost'identified by 'qqq002';
    • 修改名称为rename

  • 修改密码

    例：修改用户kkk的密码为kkk008

    1
    mysqladmin -u kkk -p password "kkk008"

    例：更新密码

    1
    mysqladmin -u username -p password "newpassword"

  • 删除用户

    例：删除普通用户kkk

    1
    drop user kkk@localhost;

    或

    1
    2
    delete from mysql.user 
    	where host='localhost' and user='kkk';

权限管理

授予权限

例：创建一个新用户zzz,密码为zzz001，用户zzz对所有的数据有查询、插入权限，并授予grant权限

1
2
3
create user ‘zzz’@‘localhost’identified by 'zzz001'；
		grant select,insert on *.* to 'zzz'@'localhost'
		with grant option;

例：查询用户zzz的权限

1
show grants for 'zzz'@'localhost'

收回权限

例：收回用户zzz的insert权限

1
revoke insert on *.* from 'qqq'@'localhost';

例：收回用户zzz的所有权限

1
revoke all privileges,grant option from 'qqq'@'localhost';

报错处理

1418

1
set global log_bin_trust_function_creators=1;
 

喜欢这篇文章？请作者喝杯拿铁吧

评论

×