MySQL入门知识点

数据库慨念

数据是事实或观察的结果，它是对客观事物的逻辑归纳，是信息的表现形式和载体，可以是符号、文字、数字、语音、图像、视频等。

1、创建数据库
	create database mydb;
	create database `test` Character Set utf8 Collate utf8_general_ci;

2、查看数据库
	show databases;

3、使用mydb数据库
	use mydb;

4、创建表
	create table myinfo(
		id int(10) not null auto_increment primary key,
		name varchar(20),
		sex int(2),
		phone int(11)
		);

5、查看数据库里面的表
	show tables; 

6、删除数据库
	drop database mydb;

7、添加字段sex，类型为VARCHAR(1) 
	ALTER TABLE myinfo ADD sex VARCHAR(1);

8、修改字段sex的类型为tinyint 
	ALTER TABLE myinfo MODIFY sex tinyint;

9、删除字段sex 
	ALTER TABLE myinfo DROP COLUMN sex;

10、删除myinfo表 
	DROP TABLE myinfo; 

11、添加单个数据
	insert into myinfo(name,sex,phone) values("zhangsan",1,13765287861);

12、添加多个数据
	insert into myinfo(name,sex,phone) values("zhangqin",1,13765287863)
											,("zhangrui",1,13765287862);

13、修改数据
	update myinfo SET name="lisi", sex=2 where id=1;(如果不带where就修改全部数据)

14、删除数据
	delete from myinfo where id = 1;(如果不带where则删除数据表中的所有记录)
		
15、约束
	唯一性约束：unique
		#在创建表时添加唯一性约束
			create table person(
			id int not null auto_increment primary key comment '主键id',
			name varchar(30) comment '姓名',
			id_number varchar(18) unique comment '身份证号'
			);
	外键约束：foreign key(stu_no) references stu(stu_no)
		#学生表（主表）
			create table stu(
			 stu_no int not null primary key comment '学号',
			 stu_name varchar(30) comment '姓名'
			);
		#成绩表（从表）
			create table sc(
			 id int not null auto_increment primary key comment '主键id',
			 stu_no int not null comment '学号',
			 course varchar(30) comment '课程',
			 grade int comment '成绩',
			 foreign key(stu_no) references stu(stu_no)
			);
		# 注意：在插入数据时，必须先向主表插入，再向从表插入。删除数据时正好相反。
		
16、查询表
	#全部信息
		select * from myinfo;
	#根据需要的数据查询
		select name,phone from myinfo;
	#带条件的查询(and表示前后都要有，or选择其中一个)
		select name,phone from myinfo where id=4 and name = "zhangrui";
		select name,phone from myinfo where id=1 or name = "zhangrui";
17、模糊查询
	#like(匹配姓名带张的)
		select * from myinfo where name like '%zhang%';
	#in(相当于多个or)
		select * from myinfo where id in(2,3,4);
		
18、数据库常用函数
	#count()查询数据库的总条数
		select count(*) from myinfo;
	#sum()统计
		select sum(工资) from myinfo;
	#avg()平均数
		select avg(工资) from myinfo;
	#所属用户
		select user();
	#min()/max() 最小值和最大值
		select min(工资)/max(工资) from myinfo;
	#now()当前时间
		select now();
	#date_format 序列化时间格式
		select name, date_format(birthday, '%Y/%m/%d') from myinfo;
	#case where用法
		select id,name,case sex 
			when 1 then '男' 
			else '女' 
			end as sex,phone 
			from myinfo;

19、数据库的排序
	#order by
		#默认升序排：
			select * from employee order by salary;
		#设置升序排
			select * from employee order by salary asc;
		#设置降序排：
			select * from employee order by salary desc;
		#多条件的排序
			select * from employee order by sex,salary asc;
			select * from employee order by sex,salary desc;
	#limit
		#显示5条数据
			select * from employee limit 5;
		#显示分页数据(从哪里开始-到哪里结束)
			select * from employee limit 0,5;
20、group by和having的使用
	#测试数据：
		create table employee(
			id int not null auto_increment primary key,
			name varchar(30) comment '姓名',
			sex varchar(1) comment '性别',
			salary int comment '薪资（元）',
			dept varchar(30) comment '部门'
		);
		insert into employee(name, sex, salary, dept) values('张三', '男', 5500, '部门A');
		insert into employee(name, sex, salary, dept) values('李洁', '女', 4500, '部门C');
		insert into employee(name, sex, salary, dept) values('李小梅', '女', 4200, '部门A');
		insert into employee(name, sex, salary, dept) values('欧阳辉', '男', 7500, '部门C');
		insert into employee(name, sex, salary, dept) values('李芳', '女', 8500, '部门A');
		insert into employee(name, sex, salary, dept) values('张江', '男', 6800, '部门A');
		insert into employee(name, sex, salary, dept) values('李四', '男', 12000, '部门B');
		insert into employee(name, sex, salary, dept) values('王五', '男', 3500, '部门B');
		insert into employee(name, sex, salary, dept) values('马小龙', '男', 6000, '部门A');
		insert into employee(name, sex, salary, dept) values('龙五', '男', 8000, '部门B');
		insert into employee(name, sex, salary, dept) values('冯小芳', '女', 10000, '部门C');
		insert into employee(name, sex, salary, dept) values('马小花', '女', 4000, '部门B');
		insert into employee(name, sex, salary, dept) values('张勇', '男', 8800, '部门A');
	#group by
		#查看公司男员工和女员工的数量
			select sex,count(*) from employee group by sex;
		#查看公司每个部门人数
			select dept,count(*) from employee group by dept;
		#查看公司每个部门人数的总工资
			select dept,sum(salary) from employee group by dept;
		#查看公司每个部门的最高工资
			select dept,max(salary) from employee group by dept;
		#查看公司每个部门的最低工资
			select dept,min(salary) from employee group by dept;
	#having
		#查看公司人数小于5的部门
			select dept,count(*) 
				from employee 
				group by dept 
				having count(*)<5;
		#查看公司工资过万的部门
			select dept,max(salary) 
				from employee 
				group by dept 
				having max(salary)>=10000;
			
21、group_concat的使用（在后面显示数据）		
		#查看公司每个部门人数并且查看姓名
			select dept,count(*),group_concat(name) from employee group by dept;
		#查看公司每个部门人数并且查看姓名（用分号隔开）
			select dept,count(*),group_concat(name separator ';') from employee group by dept;
			
22、distinct的使用(去重)
		#去重员工性别
			select distinct sex from employee;
			
23、内连接：
		#准备两张表
			create table student(
				stu_no varchar(20) not null primary key comment '学号',
				name varchar(30) comment '姓名',
				address varchar(150) comment '地址'
			);

			insert into student(stu_no, name, address) values('2016001', '张三', '贵州贵阳');
			insert into student(stu_no, name, address) values('2016002', '李芳', '陕西兴平');
			insert into student(stu_no, name, address) values('2016003', '张晓燕', '江西南昌');

			create table score(
				id int not null auto_increment primary key,
				course varchar(50) comment '科目',
				stu_no varchar(20) comment '学号',
				score int comment '分数',
				foreign key(stu_no) references student(stu_no)
			);

			insert into score(course, stu_no, score) values('计算机', '2016001', 99);
			insert into score(course, stu_no, score) values('离散数学', '2016001', 85);
			insert into score(course, stu_no, score) values('计算机', '2016002', 78);
		#join用法(表连接（JOIN）是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获
取数据)
			语法：select A.c1, B.c2 
					from A
					join B on (A.c3 = B.c3)

			#内连接查询表里面学生的成绩（学号，姓名，课程，成绩）
				select A.stu_no,A.name,B.course,B.score 
					from student A 
					join score B on(A.stu_no=B.stu_no);
			#常用where表示(等价于join)
				select A.stu_no,A.name,B.course,B.score 
					from student A,score B 
					where(A.stu_no=B.stu_no);
			#左连接查询表里面学生的成绩（学号，姓名，课程，成绩）
				select A.stu_no,A.name,B.course,B.score 
					from student A 
					left join score B on(A.stu_no=B.stu_no);
					
24、自连接:(有层次结构的表)
		#准备测试数据：
			create table area(
				id int not null auto_increment primary key comment '区域id',
				pid int not null comment '父id(0-省份)',
				name varchar(30) comment '区域名称'
			);

			insert into area(id, pid, name) values(1, 0, '贵州省');
			insert into area(id, pid, name) values(2, 1, '贵阳');
			insert into area(id, pid, name) values(3, 1, '遵义');
			insert into area(id, pid, name) values(4, 0, '广东省');
			insert into area(id, pid, name) values(5, 4, '广州');
			insert into area(id, pid, name) values(6, 4, '深圳');
		#查询市属于哪个省份
			select A.id,A.name,B.name as proName
				from area A,area B
				where A.pid=B.id and A.pid<>0;
				
25、子查询EXISTS和IN的使用
	#in用法
		#查询所有选修课程的学生
			select A.*
				from student A
				where A.stu_no in(select B.stu_no from score B);
		#查询所有选修课程的学生并且选择了计算机的学生
			select A.*
				from student A
				where A.stu_no in(select B.stu_no from score B where B.course="计算机");
	#exists用法
		#查询所有选修课程的学生
			select A.*
				from student A
				where exists(select * from score B where A.stu_no=B.stu_no);
		#查询所有未选修课程的学生
			select A.*
				from student A
				where not exists(select * from score B where A.stu_no=B.stu_no);

26、用户管理
		#创建用户
			create user 'zhangyong'@'localhost' identified by '521521';
		#删除用户
			drop user 'zhangyong'@'localhost';
		#修改密码
			alter user 'zhangyong'@'localhost' identified by '新密码';
		#授权
			grant all privileges on databaseName.tableName to 'zhangyong'@'localhost';
		#撤销授权
			revoke all privileges on databaseName.tableName from 'zhangyong'@'localhost';
		#刷新权限
			flush privileges;
		#查看权限
			show grants for 'zhangyong'@'localhost';
			
27、禁止root远程登录
		#把root设置为只能本机登录（默认只能本机登录）
			UPDATE user SET Host="localhost" where user="root";
		#查看表里所有用户的权限
			SELECT user,Host from user;
			
28、忘记root密码处理
		#关闭MySQL服务-->重启MySQL时关闭权限验证-->修改root密码-->启动mysql
			#关闭MySQL服务
				net stop MySQL57
			#关闭权限验证
				mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 5.7\my.ini" --console --skip-granttables --shared-memory
			#连接数据库
				mysql
			#刷新权限
				FLUSH PRIVILEGES;
			#修改root用户的密码
				alter user 'root'@'localhost' IDENTIFIED BY '521521';