mysql学习笔记(增删改查、事务、约束、用户权限)
一、增删改查
1、数据库操作
(1) 登陆数据库服务器
#启动数据库
net start mysql
#关闭数据库
net stop mysql
#登陆数据库服务器
mysql -uroot -p
(2) 查询数据库服务器中所有的数据库
show databases;
(3)选中一个数据库进行操作
use mysql;
(4)列出当前数据库中的表格
show tables;
(5)退出数据库服务器
exit;
(6)创建数据库?
mysql-> create database test; //创建了一个名为test的数据库
(7)删除数据库
drop database 数据库名;
2、数据表操作
(1)创建数据表
# 创建了一个名为pet的数据表,包含字段 name,owner,sepcies,sex,birth,death。
CREATE TABLE pet(
name varchar(20),
owner varchar(20),
species varchar(20),
sex char(1),
birth date,
death date);
(2)查看表结构
#describe 表名
describe pet;
(3)数据表修改
# alter table 表名 操作;
alter table student add age int;
#向现有表中添加列(向学生表中增加age列)
alter table student add age int;
#修改表中的列(修改学生表中,name长度为20)
alter table student modify name vachar(20);
#删除表中的列(删除学生表中的addr列)
alter table student drop addr;
#修改列名(修改学生表中name为snmae)
alter table student change name sname varchar(20);
(4)数据表删除
# drop table 表名
drop table student;
3、数据(记录)操作
(1)查询数据表记录(select)
# select 指定要查询的列 from 指定要查询的表
select * from pet;
(2)添加数据(insert)
# insert into 表名(列1,列2,列3...) values(值1,值2,值3);
#向宠物表中插入值
INSERT INTO pet VALUES('旺财','周星驰','狗','1998-01-01');
(3)删除数据(delete)
# delete from <表名> where <数据名>='';
#例:删除pet表中name为puffball的记录。
delete from pet where name='Puffball';
(4)修改数据(update)
# update <表名 > set <要更改的数据名>='' where <数据名> ='';
#例:将owner是‘周星驰’的数据,name改为‘旺旺财’
update pet set name='旺旺财' where owner='周星驰';
4、数据查询
(1) 基本查询
#(1)语法:select 列名 from 表名
select * from student
#(2)对列中数据进行运算(+ - * /)
#查询student中的sid、sname、score乘2列。
select sid,sname,score*2 from student;
#(3)列的别名
# 列 as 列名
# 查询student表中的sid,sname,age,取别名为学号,姓名,年龄。
select sid as '学号',
sname as '姓名',
age as '年龄'
from student
#(4)查询结果去重
#distinct 列名
#查询学生表中姓名不重复的所有记录
select distinct sname from student;
(2)排序查询
#语法: select 列名 from 表名 order by 排序列 排序规则
# asc 升序 desc 降序 (默认升序)
#(1)单列排序
#查询student 表中所有数据,按照sid降序排序
select * from student order by sid desc
#(2)多列排序(先按照第一列排序,第一列相等的情况下按照后面列的排序规则排序。)
#查询student 表中所有数据,先按照age升序排序,age相同的情况下按照sid降序排序。
select * from student order by sid asc,age desc;
(3)条件查询
#语法:select 列名 from 表名 where 条件
#(1)等值判断(=)
#查询student 表中sid为1001的学生。
select * from student where sid='1001';
#(2)逻辑判断(and、or、not)
#查询年龄是16并且成绩大于60的记录。
select * from student where age=16 and score>60;
#(3)不等值判断(> < >= <= != <>)
#查询成绩大于60小于100的记录
select *from student where score>60 and socre <100;
#(4)区间判断(between and)【闭区间】
#查询成绩在60-100的记录(包含60和100)
select * from student where score between 60 and 100;
#(5)null 值判断(is null ,is not null)
#列名 is null
#查询地址为null的记录
select * from studen where addr is null;
#(6)枚举查询(in (值1,值2,值3))
#查询地址在南窑头,鱼化寨,小寨的学生记录。
select * from student where addr in('南窑头','鱼化寨','小寨')
#(7)模糊查询
# like _(单个任意字符) like %(任意长度的任意字符)
#查询名字中以'张'开头的学生记录
select * from student where name like '张%'
#查询姓名中包含'天'的学生记录
select * from student where name like '%张%';
#查询姓名以'王'开头,并且长度为3的学生记录。
select * from student where name like '王__';
#(8)分支结构查询
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果4
end
#查询学生记录,根据分支信息对应生成一个值
select sid,sname,age,score,
case
when score>=90 and score <=100 then '优秀'
when score<90 and score>=80 then'良'
when score<80 and score>=60 then '及格'
when score<60 and score>0 then '不及格'
else '不合法数据'
end as '评价'
from student;
(4)时间查询
时间函数 | 描述 |
---|---|
sysdate() | 获取系统时间(日、月、年、时、分、秒) |
curdate() | 获取当前日期 |
curtime() | 获取当前日期 |
week(date) | 获取指定日期为一年中的第几周 |
year(date) | 获取指定日期的年份 |
hour(time) | 获取指定时间的小时 |
minute(time) | 获取时间的分钟值 |
datediff(date1,date2) | 获取date1和date2之间相隔的天数 |
adddate(date,n) | 计算date加上n天后的日期 |
#语法:select 时间函数([参数列表])
#获取当前的时间
select sysdate();
#查询当前时间
select now();
#获取当前日期
select curdate();
#获取当前时间
select curtime();
(5)字符串查询
字符串函数 | 说明 |
---|---|
concat(str1,str2,str…) | 将多个字符串连接 |
insert(str,pos,len,newStr) | 将str中指定pos位置开始len长度的内容替换为newStr |
lower(str) | 将指定的字符串转换为小写 |
upper(str) | 将指定字符串转换为大写 |
substring(str,num,len) | 将str字符串指定num位置开始截取len个内容 |
#语法 select 字符串函数([参数列表])
#拼接内容
select concat('My','S','QL');
#字符串替换
select insert('这是一个数据库',3,2,'Mysql'); #结果为这是MySql数据库。
#指定内容转换为小写
select lower('MYSQL'); #mysql
(6)聚合函数
聚合函数 | 说明 |
---|---|
sum() | 求所有行中单列结果的总和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
count() | 求总行数 |
#语法: select 聚合函数(列名) from 表名;
#统计所有学生成绩总和
select sum(score) from student;
#统计所有学生的平均成绩
select avg(score) from student;
#总行数
select count(*)from student;
(7)分组查询
#语法: select 列名 from 表名 where 条件 group by 分组依据(列);
#group by 分组依据,必须填在where之后生效。
#查询每门课程的平均成绩。
select avg(score) from score group by subject;
#查询每个班级的总人数
select count(sid) from student group by class;
(8)分组过滤查询
#语法:select 列名 from 表名 where 条件 group by 分组列 having 过滤规则;
#having 过滤规则 过滤规则定义对分组后的数据进行过滤。
#查询java、python、php每门课程的最高成绩
select Max(score),class from score group by class having class in('java','python','php');
(9)限定查询(分页查询)
# select 列名 from 表名 limit 起始行,查询行数
#查询前5条记录
select * from student limit 0,5;
(10)查询总结
# sql语句编写顺序
# select 列名 from 表名 where 条件 group by 分组 having 过滤条件 order by 排序列(asc||desc) limit 起始行,查询行数;
1.form:指定数据来源表
2.where:对查询数据做第一次过滤
3.group by:分组
4.having:对分组后的数据第二次过滤
5.select:查询各字段的值
6.order by:排序
7.limit:限定查询结果
(11)子查询(条件判断)
#语句 select 列名 form 表名 where 条件 (子查询结果)
(12)子查询(枚举查询条件)
#语句:select 列名 form 表名 where 列名 in(子查询结果)
(13)子查询(作为一张表)
#语句:select 列名 form (子查询结果集) where 条件
(14)合并查询
# select * from 表名1 union select *from 表名2(合并两张表结果去重)
# select * from 表名1 union all select *from 表名2(合并两张表保留重复内容)
(15)表连接查询
# select 列名 from 表1 连接方式 表2 on 连接条件
#(1)内连接(inner join on)
# 查询所有部门的员工信息(不包括null)
select *from t_employee inner join t_jobs on t_employee.job_id=t_jobs.job_id;
#(2)左连接(left join on)
#(3)右连接(right join on)
二、约束
1、主键约束
primary key 主键约束,唯一且不可重复不能为null。
2、自增约束
auto_increment 自增约束,一般搭配主键约束使用,字段从1开始自动增长。
3、唯一约束
unique 唯一约束 ,不可重复,值唯一,可以为null。
4、非空约束
not null 非空约束,值不能为null。
5、默认值约束
default 值 为列赋予默认值,当新增数据不指定值市,书写default,以指定的默认值进行填充。
6、外键约束
# 语法: constraint 引用名 foreign key (列名) references 被引用表名(列名)
#详解:foreign key 引用外部表的某个字段的值,新增数据时,约束此列的值必须是引用表中存在的值。
三、事务
1、概念
事务是一个原子操作,是一个最小执行单元。可以由一个或多个sql语句组成,在同一个事务当中,所有的sql语句都执行成功时,整个事务成功,当有一个sql语句执行失败,整个事务都执行失败。
2、开启事务
#开启事务
start transactton; || setAutoCommit=0; #禁止自动提交
3、特性
A(Atomicity)
原子性:表示事务是最小单元不可分割。
I(Isolation)
隔离性:事物之间具有隔离性,不同事务之间互不影响。
C(Consistency)
一致性:同一事务中的sql语句只能一起成功或者一起失败。
D(Durability)
持久性:事务一旦提交,就不可返回。
四、用户权限
1、创建用户
#创建用户
create user '用户名' identified by 密码
#创建用户bbsm
create user bbsn identified by admin
2、授权
#授权
grant all on 数据库.表 to '用户名';
#授予bbsn在java2002的所有权限
grant all on java2002.* to 'bbsn';
3、撤销权限
#撤销用户权限
revoke all on 数据库.表 from '用户名'
4、删除用户
#删除用户
drop user 用户名
四、查表练习
5、统计—count
例:查询‘95031’班的学生人数。
select count(*) from student where class='95013';
6、查询最大值
例:查询score表中的最高分的学生号和课程号。(子查询或者排序)
select sno,cno from score where degree=
(select max(degree) from score);
7、查询每门课的平均成绩(avg)
select avg(degree) from score where cno='3-105';
select avg(degree) from score where cno='9-101';
如何在一个sql语句中查询呢?
select cno,avg(degree) from score group by cno;
8、查询score表中至少有两名学生选修的并且以3开头的课程平均分数。
select cno,avg(degree) ,count(*)
from score group by cno having count(cno>=2)
and cno like '3%';
9、查询分数大于70,小于90的sno列。
select sno ,degree from score
where degree>70 and degree<90;
10、多表查询—查询所有学生的sname cno dgree(不在同一个表)
select snmae,cno,degree from student,score
where student.sno=score.sno;
11、已知生日查询年龄
年龄=当前年份-出生年份
查询当前年份: select year(now));
查询出生年份:select year(sbirthday) from student;
所以查询年龄就是:(这里去别名“年龄”as ‘年龄’)
select sname,year(now())-year(sbirthday) as '年龄' from student;