数据库

数据库概念

mysql navicat

sql语句 查询

mysql高级

1、概念

1.1、数据库的作用

	用来存储数据，方便查询

1.2、关系型数据库

	核心是用二维表存储数据

	行，记录，代表的是一条事物的信息
	列，字段，代表一条事物的某一个属性

	表可以存很多行
	数据库可以存很多表

1.3、SQL

	结构化查询语言，操作关系型数据库

	不区分大小写，select Select

1.4、MySQL
	
	瑞典公司开发，被卖给Sun，Sun又被卖给Oracle

	开源 免费 支持多平台

2、MySQL安装

服务端：不能带中文路径

	启动  停止  重启


客户端：

	命令行客户端

	navicat

3、navicat使用

连接服务端

	输入ip、端口、用户名、密码

数据库操作 

	创建  字符集用过utf8

	编辑  不能改名称

	删除


数据表

	创建

	设计表 

		添加字段

		修改字段

		删除字段

	重命名

	删除

数据操作

	添加 

	删除

	修改

	查询

4、数据类型与约束

int ： 有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义

varchar ：varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符

decimal ：decimal(5,2)表示共存5位数，小数占2位，整数占3位


主键：字段值必须唯一，且不能为null，唯一标识某一条数据，一般字段名叫id，int类型、无符号、自动递增

5、sql语言

表

	创建表

		create table 表名(
		字段名 类型 约束,
		字段名 类型 约束
		...
		)

		-- 注释 ctrl + /
		-- 取消注释 ctrl + shift + /

		例：创建学生表，字段要求如下：
		
		姓名(长度为10)

			create table students(
			name varchar(10)
			)

		例：创建学生表，字段要求如下：
		
		姓名(长度为10)， 年龄

			create table students2(
			name varchar(10),
			age int unsigned
			)


		例：创建学生表，字段要求如下：
		
		姓名(长度为10)， 年龄，身高(保留小数点2位)
		
			create table students3(
			id int unsigned primary key auto_increment,
			name varchar(10),
			age int unsigned,
			height decimal(5,2)
			)

	删除表

		如果表不存在，会报错
		drop table students

		如果表存在，才删除，不存在也不会报错
		drop table if exists students3;

		create table students3(
		id int unsigned primary key auto_increment,
		name varchar(10),
		age int unsigned,
		height decimal(5,2)
		)

数据操作

	插入数据

		insert into 表名 values(...)
		例：插入一个学生，设置所有字段的信息

			insert into students3 values(0,'亚瑟',16,160.5)
			insert into students3 values(default,'亚瑟2',16,160.5)
			insert into students3 values(null,'亚瑟',16,160.5)

		给部分字段设置数据
			
			insert into students3(name) values('鲁班')

			insert into students3(name,age) values('鲁班2',30) 

		执行多条语句插入多条数据

			insert into students3 values(null,'亚瑟4',16,160.5);
			insert into students3 values(null,'亚瑟5',16,160.5);
			insert into students3 values(null,'亚瑟6',16,160.5)

		执行一条语句插入多条数据
			
			insert into students3(name) values('鲁班'),('鲁班2'),('鲁班3'),('鲁班4')

	更新数据

		格式：update 表名 set 列1=值1,列2=值2... where 条件
		例：修改id为5的学生数据，姓名改为 狄仁杰，年龄改为 20

			update students3 set age=20 where id=5

			update students3 set name='狄仁杰',age=22,height=123.246 where age=20

	删除数据

		格式：delete from 表名 where 条件
		例：删除id为6的学生数据

		delete from hero2 

		delete from students3 where id=12

	逻辑删除

		1、给表添加一个字段，名字叫isdelete int类型 ，0代表未删除，1代表删除

		2、把表中所有的数据isdelete字段改为0

		3、想删除数据时，使用update语句，把某条数据的isdelete字段改为1

			update students3 set isdelete=1 where id=5

		4、在查询数据时，只查询isdelete为0的数据

			select * from students3 where isdelete=0

查询

	查询所有字段

		select * from 表名

		select * from students

	查询指定字段

		select name,hometown from students

	给字段取别名

		select name as 姓名,hometown as 家乡 from students
	
	给表取别名

		select stu.name,stu.hometown from students as stu

	去重，重复的记录只显示一条，当显示的一整行记录跟另一整行记录完全一样时，认为是重复的记录

		select distinct sex from students

		select distinct sex,class  from students


比较运算


	例1：查询小乔的年龄

		select age from students where name='小乔'

	例2：查询20岁以下的学生

		select * from students where age<=20

	例3：查询家乡不在北京的学生

		select * from students where hometown<>'北京'

逻辑运算

	例1：查询年龄小于20的女同学

		select * from students where age<20 and sex='女'

	例2：查询女学生或'1班'的学生

		select * from students where sex='女' or class='1班'

	例3：查询非天津的学生

		select * from students where not hometown='天津'

模糊查询

	例1：查询姓孙的学生

		select * from students where name like '孙%'
	
	例2：查询姓孙且名字是一个字的学生

		select * from students where name like '孙__'

	例3：查询叫乔的学生

		select * from students where name like '%乔'

	例4：查询姓名含白的学生

		select * from students where name like '%白%'

范围查询


	例1：查询家乡是北京或上海或广东的学生

		select * from students where hometown='北京' or hometown='上海' or hometown='广东'

		select * from students where hometown in ('北京','上海','广东')

	例2：查询年龄为18至20的学生

		select * from students where age in (18,19,20)

		select * from students where age between 18 and 20   小值在前，大值在后

		select * from students where age>=20 and age<=18

空判断


	例1：查询没有填写身份证的学生

		select * from students where  card is null
	
	例2：查询填写了身份证的学生
	
		select * from students where  card is not null

	'' 代表插入的数据为空字符串

	insert into students values('013','凯','男','北京',20,'1班','')

	null 代表插入的值为空
	insert into students values('014','凯2','男','北京',20,'1班',null)

	插入数据时，如果一些字段没指定数据，默认为null
	insert into students(studentNo,name) values('015','凯3')

排序


	例1：查询所有学生信息，按年龄从小到大排序

		select * from students order by age

	例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序

		select * from students order by age desc,studentNo 

	对中文字段进行排序
	select * from students order by convert(name using gbk)

聚合函数

	例1：查询学生总数

		count(*)代表如果一行记录中任何一个字段有值，就会统计在内
		select count(*) from students

		count(card)代表统计某一个列的个数，不会把null值统计在内
		select count(card) from students

	例2：查询女生的最大年龄

		select max(age) from students where sex='女'

	例3：查询1班的最小年龄

		select min(age) from students where class='1班'

	例4：查询北京学生的年龄总和

		select sum(age) from students where hometown='北京'

	例5：查询女生的平均年龄

		select avg(age) from students where sex='女'


分组


	例1：查询各种性别的人数

		select sex,count(*) from students group by sex

	例2：查询各种年龄的人数

		select age,count(*) from students group by age


	例1：查询男生总人数    having必须放在group by 后面

		select count(*) from students where sex='男'

		select sex,count(*) from students group by sex having sex='男'



	查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄

		select class,avg(age),max(age),min(age) from students where class!='1班' group by class

		select class,avg(age),max(age),min(age) from students group by class having class!='1班'


	按照多个字段分组，多个字段值一模一样时，认为是一组的

		select class,sex,count(*) from students group by class,sex

获取部分行

		limit start,count  mysql中的第一条记录的位置是0

	例1：查询前3行学生信息  

		select * from students limit 1,2

		limit 2 就等于 limit 0,2

	分页

		每页显示4条


		1页
		
			select * from students limit 0,4  (n-1)*4

		2页
			
			select * from students limit 4,4  

		3页

			select * from students limit 8,4

连接查询

	等值连接

		例1：查询学生信息及学生的成绩

			生成临时表，然后在临时表中使用关联的字段进行过滤

			select stu.name,sc.score from students as stu,scores as sc
			where stu.studentNo=sc.studentNo

			内连接，不生成临时表，连接时先判断条件，只有符合条件才会连接再放到结果

			select * from students as stu
			inner join scores as sc on stu.studentNo=sc.studentNo

		例2：查询课程信息及课程的成绩

			select * from scores,courses
			where scores.courseNo=courses.courseNo

			select * from courses
			inner join scores on scores.courseNo=courses.courseNo


		例3：查询学生信息及学生的课程对应的成绩


			select
				*
			from
				students,
				scores,
				courses
			where
			students.studentno = scores.studentno
			and scores.courseno = courses.courseno 

			select
				students. name,
				courses. name as coursename,
				scores.score
			from
				students
			inner join scores on students.studentno = scores.studentno
			inner join courses on scores.courseno = courses.courseno
			
		例7：查询男生中最高成绩，要求显示姓名、课程名、成绩

			select 
				students.sex,students.name,scores.score,courses.name as cName 
			from 
				courses
			inner join scores on courses.courseNo=scores.courseNo
			inner join students on students.studentNo=scores.studentno
			where students.sex='男'
			order by scores.score desc
			limit 1


	左连接


		查询所有学生的成绩，包括没有成绩的学生
			
			join 前面的表为左表，join 后面的表为右表
			
			select * from students
			left join scores on students.studentNo=scores.studentno

		例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
			
			select * from students
			left join scores on students.studentNo=scores.studentno
			left join courses on courses.courseNo=scores.courseNo

	右连接

		insert into courses values 
		(0, '语文'),
		(0, '数学');


		例1：查询所有课程的成绩，包括没有成绩的课程
			
			select * from scores
			right join courses on scores.courseNo=courses.courseNo
	
		例2：查询所有课程的成绩，包括没有成绩的课程,包括学生信息

			select * from scores
			right join courses on scores.courseNo=courses.courseNo
			left join students on students.studentNo=scores.studentNo

			select students.*,scores.*,courses.* from courses
			left join scores on scores.courseNo=courses.courseNo
			left join students on students.studentNo=scores.studentno

	自关联

		例1：查询一共有多少个省

			select count(*) from areas where pid is null

		例1：查询河南省的所有城市


			from后面跟的是数据源，可以对一个数据源查询多次

			select * from areas as sheng,areas as shi
			where sheng.aid=shi.pid and sheng.atitle='河南省'


		例2：查询郑州市的所有区县

			select * from areas as shi,areas as qu
			where shi.aid=qu.pid and shi.atitle='郑州市'

		例3：查询河南省的所有区县
			
			select * from areas as sheng,areas as shi,areas as qu
			where sheng.aid=shi.pid and qu.pid=shi.aid and sheng.atitle='河南省'

			先连接省和市，再连接区
			select * from areas as sheng
			inner join areas as shi on sheng.aid=shi.pid
			inner join areas as qu on shi.aid=qu.pid

			先连接市和区，再连接省
			select * from areas as shi
			inner join areas as qu on shi.aid=qu.pid
			inner join areas as sheng on shi.pid=sheng.aid

			查询河南省所有的市和区
			select * from areas as sheng
			inner join areas as shi on sheng.aid=shi.pid
			left join areas as qu on shi.aid=qu.pid 
			where sheng.atitle='河南省'

	子查询

	标量子查询：子查询返回的结果是一行一列
		查询大于平均年龄的学生

			select avg(age) from students

			-- 21.5833

			select * from students where age>21.5833

			select * from students where age>(select avg(age) from students)

		例2：查询王昭君的成绩，要求显示成绩

			select studentNo from students where name='王昭君'

			select score from scores where studentNo=(select studentNo from students where name='王昭君')

	列子查询：子查询返回的结果是一列多行
		例3：查询18岁的学生的成绩，要求显示成绩

			select studentNo from students where age=18

			select * from scores where studentNo in(select studentNo from students where age=18)

	行子查询：子查询返回的结果是一行多列
		例4：查询男生中年龄最大的学生信息

			select * from students where sex='男' and age=(select max(age) from students where sex='男')

			select * from students where sex='男' and age=30


			select * from students where (sex,age)=('男',30)

			select * from students 
			where (sex,age)=(select sex,age from students where sex='男' order by age desc limit 1)


	表子查询：子查询返回的结果给另一个查询当作表来使用

		例5：查询数据库和系统测试的课程成绩

			select * from scores
			inner join courses on scores.courseNo=courses.courseNo
			where courses.name in ('数据库','系统测试')

			select * from scores
			inner join 
			(select * from courses where name in  ('数据库','系统测试')) as c
			on scores.courseNo=c.courseNo

查询演练

	求所有电脑产品的平均价格,并且保留两位小数

		select round(avg(price),2) from goods
		
	查询所有价格大于平均价格的商品，并且按价格降序排序

		select * from goods where price>(select round(avg(price),2) from goods) order by price desc

	查询类型为'超极本'的商品价格
	
		select price from goods where cate='超级本'
	
	查询价格大于或等于"超级本"价格的商品，并且按价格降序排列
		
		select * from goods where price >=any(select price from goods where cate='超级本') order by price desc


	创建类型表

		create table goods_cates(
		cate_id int unsigned primary key auto_increment,
		cate_name varchar(10)
		)

		select distinct cate from goods
		select cate from goods group by cate

		插入数据，查询出来的字段需要和想插入的字段保持顺序一致
		insert into goods_cates(cate_name) select distinct cate from goods

	创建品牌表,并且插入数据，查询出来的字段名需要和想插入的字段名一模一样

		create table goods_brands(
		brand_id int unsigned primary key auto_increment,
		brand_name varchar(10)
		) 
		select distinct brand_name from goods


	更新商品表的类型

		select * from goods
		inner join goods_cates on goods.cate=goods_cates.cate_name


		update goods
		inner join goods_cates on goods.cate=goods_cates.cate_name
		set goods.cate=goods_cates.cate_id

	更新商品表的品牌

		update goods
		inner join goods_brands on goods.brand_name=goods_brands.brand_name
		set goods.brand_name=goods_brands.brand_id

	查询商品信息，包括品牌、类型
	
		select goods.id,goods.name,goods_cates.cate_name,goods_brands.brand_name from goods
		inner join goods_cates on goods.cate_id=goods_cates.cate_id
		inner join goods_brands on goods.brand_id=goods_brands.brand_id

命令行客户端

	连接服务端
	mysql -uroot -p  回车后，输入密码


	显示所有仓库
	show databases;

	打开某个仓库
	use ceshi;

	显示当前仓库所有的表
	show tables;

	显示表结构
	desc students;

	中文乱码
	set charset gbk;

函数

	随机从一个表中取一条数据
	select * from goods order by rand() limit 1


视图

	创建视图

		create view v_students as
		select name as 姓名,sex 性别,age 年龄 from students

	查询视图

		select * from v_students

	删除视图

		drop view v_students

事务

	转账

		大乔 500
		小乔 200

		update  set account=500-100 where name='大乔'

		update  set account=200+100 where name='小乔'


		提交，所有步骤都执行成功，才提交

			begin;   开启事务

			update students set age=age-10 where name='大乔';

			update students set age=age+10 where name='小乔';

			commit;   commit后，数据才变化到表中

		回滚，任何一步失败，回到以前的状态

			begin;

			update students set age=age-10 where name='大乔';

			update students setb age=age+10 where name='小乔';

			rollback;   rollback后，所有的操作全部失败，数据回到原始状态

索引

	虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件


外键

	从表的一列，被主表的一列约束，从表被约束的一列的值，必须在主表主表中那一列值的范围内

	外键约束,会极大的降低表更新的效率


修改密码

	在mysql仓库下执行

		更新密码
		update user set password=password('111')

		刷新权限
		flush privileges