数据库笔记

**

1、基本概念

1.1 数据库是什么

	数据库是用来存储数据，给软件显示

1.2 RDBMS

	关系型数据库管理系统

	核心：用表存储数据

		表头有多个字段名，代表每一列数据的含义
		表中存的是一行一行的数据
		每一行数据有多个字段值
		表包含多行数据
		一个数据库中可以存多个表

1.3 SQL

	结构化查询语言

	关系型数据库系统操作数据的语言

	oracle,mysql,sqlite

	不区分大小写，selcet SELECT SeLCet

1.4 MySql

	mysql 瑞典一个公司，被卖给sun，sun又被卖给oracle

	开源 免费 支持多平台

2、Mysql安装和使用

2.1 Linux Centos

	服务端使用

		打开Terminal命令行

		查看mysql进程是否开启

			ps ajx | grep mysql -- 查看mysqld是否存在

		root用户执行下面命令

		启动mysql服务

			service mysqld start

		停止mysql服务

			service mysqld stop

		重启mysql服务

			service mysqld restart

	客户端使用

		navicat

			把navicat112_mysql_cs_x64.tar.gz拷贝到centos中，解压
			打开start_navicat程序，点击两次取消
			如果试用期到期了，删除用户目录下的

				rm -r /home/admin/.navicat64/

		命令行客户端

			打开Terminal命令行，输入

				mysql -u root --回车

			退出命令行客户端 ctrl + d

2.2 Windows中

		Mysql服务端，安装：安装路径不要有中文


		客户端

			命令行客户端：开始菜单-->mysql-->mysql server 5.1-->>mysql commond line client
			输入MySQL安装的密码

			navicat工具：激活时必须以管理员身份运行navicat_chs.exe

2.3 Mysql客户端和服务端搭配场景

有一个图，在文件夹下面拉进来

	windows中客户端连接windows中服务端

		1、确保mysql服务端是开启的

		2、打开navicat，输入密码，点击测试连接，测试成功后，点击确定

	windows中客户端连接centos中服务端

		1、检查windows和centos的网络是否畅通
			在centos中获取ip，打开terminal，输入ifconfg，拷贝ip
			在windows中cmd命令行，输入 ping ip地址，如: ping 192.168.133.159

		2、centos中设置mysql服务端允许其他电脑连接服务端
			打开terminal
			mysql -u root

			use mysql;

			update user set host='%' where host='::1';

			flush privileges;
		3、打开navicat，输入centos中mysql服务端的ip，点击测试连接，测试成功后，点击确定

3、navicat操作

1、连接mysql服务端

2、打开连接

3、连接下面显示mysql服务端中所有的数据库仓库

4、数据库的操作

		打开数据库

		数据库的创建

		数据库的修改

		数据库的删除

5、数据表的操作

		创建表

		修改表

			字段 添加、删除、插入、修改

			重命名

		删除表

6、数据的操作

		打开表

		数据的增加、修改、删除

4、数据类型与约束

数据类型

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


	小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，整数占3位，如果插入的小数点后面超出长度，会自动的四舍五入
	字符串：varchar，范围（0~65533），如varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符
	日期时间: datetime，范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），如'2020-01-01 12:29:59'


	查看任何一个数据类型的使用
	1、打开mysql命令行客户端
	2、help 数据类型名称，如 help tinyint

约束

	主键(primary key)：代表一条记录的唯一标识，此自动的值必须唯一不能重复，不能为空;
		自动递增：从1开始，一直累加，值不会取以前的值
		表清空：删除所有数据，不删表结构，自动递增的值继续累加
		表截断：删除所有数据，不删表结构，自动递增的值从1开始

		应用时：主键+自动递增+无符号

	非空(not null)：此字段不允许填写空值

	惟一(unique)：此字段的值不允许重复，通过sql语法演示

	默认值(default)：当不填写此值时会使用默认值，如果填写时以填写为准

	外键(foreign key)：维护两个表之间的关联关系，后面多表时讲


navicat 备份与恢复

	备份：右键点击数据库-->存储sql文件
	恢复：右键点击数据库-->运行sql文件

5、sql语言

5.1 表操作

	创建表

		-- 创建学生表，字段要求如下：注释 ctrl + /
		-- 姓名(长度为10) 

		create table students(
		name varchar(10)
		)

		-- 创建学生表，字段要求如下：取消注释 ctrl + shift + /
		-- 
		-- 姓名(长度为10)， 年龄

		create table sutdents1(
		name varchar(10),
		age tinyint unsigned
		)

		-- 创建学生表，字段要求如下：
		-- 
		-- 姓名(长度为10)， 年龄，身高(保留小数点2位)

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

	删除表

		-- 删除学生表

		-- drop table students

		-- drop table if exists sutdents1

	删除并创建新表

		drop table if exists students;

		create table students(
		name varchar(10),
		age int
		)

5.2、增删改查

	查询
		查询姓名和年龄
		-- select name,age from stu 
		查询所有列的数据
		-- select * from stu
		查询性别为男的数据
		select * from stu where sex='男'

	插入

		-- 插入一个学生，设置所有字段的信息，值的顺序与表中字段的顺序对应

		-- insert into students values('亚瑟',20)

		-- 插入一个学生，只设置姓名，值的顺序与给出的字段顺序对应

		-- insert into students(name) values('鲁班')

		-- insert into students(age) values(30)

		insert into students(age,name) values(30,'亚瑟2')

		当表中有auto_increment的字段时，添加数据时使用0或者 default 或者 null 来占位

		insert into students values(0,'老夫子3',20);

	插入多条数据

		-- insert into students values(0,'老夫子3',20);
		-- insert into students values(0,'老夫子4',20);
		-- insert into students values(0,'老夫子5',20);


		-- insert into students values (0,'老夫3',20),(0,'老夫4',20),(0,'老夫5',20)

		-- insert into students(id,name) values (0,'老夫3'),(0,'老夫4'),(0,'老夫5')

	修改数据

		-- 更新数据 设置某一个学生的年龄加3岁

		update students set age=age+3 where name='亚瑟3'

	删除数据

		-- 删除数据

		delete from students where name='亚瑟3'

	逻辑删除

		1、-- 添加字段，标识数据是否被删除 is_delete
		
		--  默认设置为0，代表数据没有被删除

		2、-- update students set is_delete=0

		3、-- 删除一条数据，只是修改了这条数据的is_delete 改为1

		-- update students set is_delete=1 where name='老夫子6'

		4、-- 查询所有学生时，不显示删除的学生

		select * from students where is_delete=0

6、sql查询

给字段起别名

-- select name as 姓名,age as 年龄,hometown as 家乡 from students where name='王昭君'

-- select name 姓名,age 年龄,hometown 家乡 from students where name='王昭君'

给表起别名
-- select s.name,s.age from students as s

消除重复数据
-- select distinct age,class from students

select distinct * 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 in ('北京','上海','广东')
		-- 
		-- select * from students where hometown not in ('北京','上海','广东')

		-- 例2：查询年龄为18至20的学生  between 20 and 18 小值在前

		-- select * from students where age between 18 and 20

	判空

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

		-- select * from students where card is null

			非空

		-- select * from students where card is not null

			判断身份证为空字符

		-- select * from students where card=''

	排序

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

		-- select * from students order by age asc
		-- 
		-- 降序
		-- select * from students order by age desc

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

		select * from students order by age desc,studentNo

	聚合函数

		-- 例1：查询学生总数 

		-- select count(*) as 学生总数 from students

		-- select count(name) from students

		-- count(card) 不统计为null数据
		-- select count(card) from students

		-- 例2：查询女生的最小年龄

		-- select min(age) from students where sex='女'

		-- 例3：查询1班的最大年龄

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

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

		-- select sum(age) from students

		-- 例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：查询男生总人数

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

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

	表 创建 create 
	    删除 drop   

	增 insert 
	删 delete 
	改 update 
	查 select 

	数据类型 能看懂
	int(1) 长度没有意思
	varchar(5) 5个字符
	decimal(5,2) 3个整数，2个小数，添加数据超过长度，自动四舍五入
	datetime
	命令行客户端，使用，查看某一个数据类型的使用帮助
	help tinyint

	备份与恢复 会操作

	查询

	select 列1、列2... from 表名 where

	group by 分组

	order by 排序

	having 分组后过滤

分页
查询学号是’007’的学生的身份证号
select * from students where studentNo=‘007’

查询’1班’以外的学生信息
select * from students where class!=‘1班’

查询年龄大于20的学生的姓名和性别
select studentNo,sex from students where age>20

查询河南或河北的学生
select * from students where hometown=‘河南’ or hometown=‘河北’

2、查询’1班’的’上海’的学生
select * from students where class=‘1班’ and hometown=‘上海’

查询非20岁的学生
select * from students where not age=20

查询姓名为两个字的学生
select * from students where name like ‘__’

查询姓百且年龄大于20的学生
select * from students where name like ‘百%’ and age>20

3、查询学号以1结尾的学生
select * from students where studentNo like ‘%1’

查询年龄在18或19或22的女生
select * from students where age between 18 and 22

查询年龄在20到25以外的学生
select * from students where not age between 20 and 25

查询所有学生信息，按班级从小到大排序，班级相同时，再按学号再按学号从小到大排序
select * from students order by class,studentNo

查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from students

一班共有多少个学生
select count(*) from students where class=‘1班’

查询3班年龄小于18岁的同学有几个
select count(*) from students where age<18

查询各个班级学生的平均年龄、最大年龄、最小年龄
select class,max(age),min(age),avg(age) from students group by class

查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
select class,max(age),min(age),avg(age) from students group by class having class!=‘1班’

查询第4到第6行学生信息
select * from students limit 3,3

每页显示5条数据，显示每一页的数据
select * from students limit 0,5

查询练习题
1、查询学生"百里守约"的基本信息
Select * from stu where name=’百里守约’

2、查询学生"百里守约"或”百里玄策”的基本信息
Select * from stu where name=’百里守约’ or name=’百里玄策’

3、查询姓"张"学生的姓名，年龄，班级
Select name,age,class from stu where name like ’张%’

4、查询姓名中含有"约"字的学生的基本信息
Select * from stu where name like ’%约%’

5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号
Select name,age,class,id from stu where name like ’孙_ _’

6、查询姓"百"或者姓”孙”的学生的基本信息
Select * from stu where name like ’百%’ or name like ’孙%’

7、查询姓"百"并且家乡是"山西"的学生信息
Select * from stu where name like ’百%’ and hometown=’山西’

8、查询家乡是"北京"、”新疆”、”山东”或者"上海"的学生的信息
Select * from stu where hometown in (“北京”,”新疆”,”山东”,“上海”)

9、查询姓"孙"，但是家乡不是"河北"的学生信息
Select * from stu where name like ’孙%’ and hometown !=’河北’

10、查询家乡不是"北京"、“新疆”、“山东”、"上海"的学生的信息
Select * from stu where hometown not in (“北京”,“新疆”,“山东”,“上海”)

11、查询全部学生信息，并按照“性别”排序
Select * from stu order by sex

12、查询现有学生都来自于哪些不同的省份
Select hometown from stu group by hometown

13、查询所有男生，并按年龄升序排序
Select * from stu where sex=’男’ order by age

14、统计共有多少个学生
Select count(*) from stu

15、统计年龄大于20岁的学生有多少个
Select count(*) from stu where age>20

16、统计男生的平均年龄
Select avg(age) from stu where sex=’男’

17、查询1班学生中的最大年龄是多少
Select max(age) from stu where class=’1班’

18、统计2班男女生各有多少人
Select count() from stu where class=’2’ and group by sex
select class,count() from students group by class

19、统计每个班级中每种性别的学生人数，并按照班级升序排序
Select count() from stu where group by sex asc
select class,sex,count() from students group by class,sex order by class

20、查询年龄最小的学生的全部信息
Select * from stu where min(age)
select * from students order by age limit 1

7、数据库设计

ER模型