MySQL学习笔记

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;