MySQL基础知识

一、Mysql之表操作：

一、数据库
	//1.创建数据库
	create database [if not exists] db_name [character set xxx];
	
二、表
	//1.创建表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name varchar(25),
	gender boolean,
	age int(11),
	depart varchar(20),
	salary double(7,2)
);
	//2.查看表信息
    desc tab_name 查看表结构
    show tables 查看当前数据库中的所有的表
    show create table tab_name    查看当前数据库表建表语句 
	
	//3.修改表结构
   (1)增加一列
      alter table tab_name add [column] 列名 类型;
   (2)修改一列类型
      alter table tab_name modify 列名 类型;
   (3)修改列名
      alter table tab_name change [column] 列名 新列名 类型;
   (4)删除一列
      alter table tab_name drop [column] 列名;
   (5)修改表名
      rename table 表名 to 新表名;
   (6)修该表所用的字符集    
      alter table student character set utf8;


三、表记录
    //1.增加一条记录insert
INSERT INTO emp (id,name,gender,age,depart,salary)
	values (1,"alex",0,18,"技术部",8000);
INSERT INTO emp values("ray",0,18,"技术部",8000);
INSERT INTO emp set name="张三",salary=3000;


	//2.修改表记录
UPDATE emp SET salary=salary+1000,depart="经理" where name="ray";


	//3.删除表操作
DELETE FROM emp where name="张三";
	//删除表中所有记录,清空记录的操作。
delete from emp;
    //使用truncate删除表，是把整个表摧毁，然后再创建一个新表(这种用法在于数据很多的时候，不需要一条一条删除)。
truncate table emp;


	//4.查找
CREATE TABLE ExamResult(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	JS DOUBLE,
	Django DOUBLE,
	Flask DOUBLE
);
    //查询表中所有学生的姓名和对应的英语成绩。
    select name,JS from ExamResult;
	//过滤表中重复数据。
    select distinct name from ExamResult;
	//在所有学生分数上加10分特长分显示。(数据库不增加)
    select name,JS+10,Django+10,Flask+10 from ExamResult;
	//取别名查。
	select name as "姓名" from ExamResult;
//过滤查询where字句中可以使用：
    //比较运算符：    > < >= <= <>  
    between 10 and 20 值在10到20之间  
    in(10,20,3)值是10或20或30
    like '张pattern' 
    pattern可以是%或者_，
    如果是%则表示任意多字符，张*
	//查找缺考JS的学生的姓名
    select name from ExamResult where JS is null;
	
	
	//5.Order by排序，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
	// Asc 升序、Desc 降序，其中asc为默认值
	select * from ExamResult order by JS asc;
	//按总成绩排
	select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0)) as "总成绩" from ExamResult order by "总成绩";
	
	
	//6.聚合函数count(列名)\sum(列名)\AVG(列名)\Max(列名)\Min(列名)
	//查找多少学生
	select count(*) from ExamResult;
	//统计数学成绩大于70的学生有多少个？
    select count(JS) from ExamResult where JS>70;
	//统计一个班级JS成绩平均分
    select sum(JS)/count(*) from ExamResult;
	//求一个班级JS平均分？先查出所有的JS分，然后用avg包上。
	select avg(ifnull(JS,0)) from ExamResult;
	//求班级最高分和最低分（数值范围在统计中特别有用）
    select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0))) from ExamResult;
    select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0))) from ExamResult;
null加上什么数都为null，(ifnull(JS,0))的意思是如果是null转化为0
	
	
	7.group by分组，where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
	//按名字分组，查每组JS的和
	select name,sum(JS) from examresult group by name;
	//按名字分组，查每组JS的总和大于100的
	select name,sum(JS) from examresult group by name having sum(JS) > 100;
	
	8.限制查询记录
	select * from examresult limit 2;
	select * from examresult limit 1,3;	(查询索引1到3)
	
	9.正则
	select * from examresult where name regexp '^al';
	select * from examresult where name regexp 'lv$';
	

　　

二、多表查询之外键查询

create table lesson.classcharger(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	is_marriged tinyint
);
create table student1(
	id int primary key auto_increment,
	name varchar(20),
	charger_id int,
	foreign key (charger_id) references classcharger(id)
)ENGINE=INNODB;
外键和主键的类型要一致。

insert into classcharger (name,age,is_marriged) values ('张三',28,0),
('李四',30,0),('小红',32,0),('小明',40,0);
insert into student1 (name,charger_id) values ('ray1',2),
('ray2',4),('ray3',1),('ray4',3),('ray5',1),('ray6',2);

//（1）增加外键：
    可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。
									  取别名
	alter table student add constraint FK_st 
									foreign key(charger_id) 
									references classcharger(id);
   （2）删除外键:
	alter table student drop foreign key FK_st;(别名)
	
	
//INNODB支持的ON语句，约束删除父表记录所带来子表的影响。
	//on delete cascade子表的内容也跟着父表删除
	alter table ss add constraint fk_cid_cc
									foreign key(charger_id)
									references cc(id) on delete cascade;
	//on delete set null子表的内容设置为null
	alter table ss add constraint fk_cid_cc
									foreign key(charger_id)
									references cc(id) on delete set null;
	　　

 

三、多表查询之连接查询

create table employee (id int primary key,
                name varchar(20),
                tb_id int);
create table dpm (id int primary key,department varchar(20) );

insert into employee values (1,'刘备',1);
insert into employee values (2,'关羽',2);
insert into employee values (3,'张飞',3);
				
insert into dpm values(1,'财务部');
insert into dpm values(2,'人事部');
insert into dpm values(3,'科技部');
        

1.笛卡尔积查询：两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录，其中很多错误数据
    select * from employee ,dpm;

2.内连接
	select employee.name,dpm.department from employee,dpm where employee.tb_id=dpm.id;
	select * from employee inner join dpm on employee.tb_id = dpm.id;
3.外连接：左外、右外
	左外连接：显示左表全部信息
	select employee.name,dpm.department from employee LEFT JOIN dpm on employee.tb_id=dpm.id;
	右外连接：显示右表全部信息
	select employee.name,dpm.department from employee RIGHT JOIN dpm on employee.tb_id=dpm.id;

　　

四、多表查询之子查询

--子查询是将一个查询语句嵌套在另一个查询语句中。
--内层查询语句的查询结果可以为外层查询语句提供查询条件。
--子查询中包含：IN,NOT IN,ANY,ALL,EXISTS,NOT EXISTS
--还有比较运算符：=,!=,>,<等

1.查询employee表，当id必须在部门表中出现过。
	select * from employee where id IN (select id from department);
	
2.用EXISTS(返回true或false)
	select * from employee where exists (select id from department where id=2);
	

　　

五、索引

--索引提高搜索时间，提高用户体验
--primary key和unique就是索引，唯一性。
--index｜key普通索引可重复

1.普通索引index｜key
	create table emp(
		id int primary key,
		name varchar(20),
		index index_name (name)
	--  key [索引名] (字段名[(长度)])
 	)



--添加索引
	create INDEX 索引名 ON 表名 (字段名[(长度)]);
	create INDEX index_name on employee (name);
	alter table 表名 ADD INDEX 索引名 (字段名[(长度)]);
	alter table employee add INDEX index_name (name)
	
--删除索引
	drop index 索引名 on 表名
	drop index index_name on employee;
	
	
--模拟添加数据
create table t1_suoyin(id int,name varchar(20));

delimiter $			
create procedure autoinsert()
BEGIN
declare i int default 1;
while(i<8000)do
insert into t1_suoyin values (i,'yuan');
set i=i+1;
end while;
END$

delimite ;    --改回来
call autoinsert();	--执行