MySQL数据库基础语法

1，库的操作语法

数据库的DDL和DML
DDL – >data definition language //数据库定义语言（对表结构和表字段进行操作）
create
alter table
DROP
DML – > data Manipulation language //数据库操作语言（对表数据进行操作）
INSERT INTO
DELETE
select
UPDATE

delete、只删除数据，不删除自增索引的数据
trucate、删除数据，也删除自增数据，相当于删除表，再建了一张相同的表
drop，直接把表删了

count(1)、count(*)、count(字段) 的区别？？
count(1)和count(*)表示统计的是数据库中所有符合条件的行数，再使用上没有什么区别
count(column) 统计的是所有符合条件且该字段不为空的数据条数，因为要进行null的判断，再效率上相对上面两个慢一点

where 和 having的区别？？
where 是 对select 出来的数据进行筛选
having 也是对数据进行筛选，但是having 必须伴随group By 进行使用相当于对分好的组进行筛选
where 是放在group by 前面 ,所有having 算的上是对where 筛选后的数据进行分组后再次筛选

union 和 union all的区别？？
union 将两个表的数据强硬的进行连接，连接的条件有连接的字段数相同，不会连接相同的数据
而union all 会将重复的数据也进行连接，没有去重功能，

查看数据库中存在那些数据库，记得后面有个 s 复数，表明查看的是多个数据库

show databases;

查看当前使用的数据库, 后面是括号，没有s复数

select database();

新建一个数据库

create database student;

删除一个数据库

drop database student;

使用指定的数据库

use student;

Linux操作下备份SQL数据库中的数据库
将userTemp数据库备份成一个SQL文件在根目录下面。位置可以自己选择

mysqldump -uroot -p123456 userTemp > /userTemp.sql

使用SQL文件恢复数据库,
需要注意的是，必须是一个存在的数据库，还有备份SQL文件的路径

mysql -uroot -p123456 userTemp < /userTemp.sql

2，修改表 语法

查看当前数据库存在的所有表，记得 table 后面有个 s

show tables;

查看指定表的构建语句

show create table name;

创建一张学生表

create table student(id int);

删除指定的表

drop table student;

删除表的数据

delete from student;   #删除所有数据，但是自增的数据还未删除
truncate studnet;      #删除表的所有数据，而且自增也会被删。原理是，先删除表在重建了一个同样结构的表

重命名表名字，关键字 rename

alter table student rename stu;

复制表结构 关键字 like

create table stu like student;

复制表的数据

#指定几个字段的数据进行复制
insert into stu(name,sex) select name sex from student;
#全部数据进行备份
insert into stu select * from student;

2.1，表的约束

not null #字段的非空约束，规定该字段不能为空
primary key #主键约束，表面该字段值唯一，不能存在相同的值
default #默认值约束，当没有值时，自动给一个默认值
auto_increment #自增长约束，不给值时，自动在最大值上加 1 做为值
foreign key #外键约束，表和表之间的约束

外键约束,主要是为了保持表之间的数据的一致性
foreign key
创建主表
create table a(id int primary key auto_increment,name varchar(20));

创建一个子表，子表的主键为主表主键的外键约束
create table b(id2 int primary key auto_increment,age int,constraint dd foreign key(id2) references a(id))engine=innodb;
在字段上创建一个外键，外键依赖的字段要有主键约束才能修改成功
alter table b add foreign key(id2) references a(id);

当外键约束创建好后：
1，主表的约束字段没有的值,子表的字段是无法插入数据的，子表的数据来源于主表
2，如果想要删除主表中的数据,必须先把子表中的相关数据全部删除,清除掉他们之间的约束，才能删除主表的数据

3，修改字段

修改表的字段名和属性，关键字 alter change

alter table student change id id int primary key auto_increment;

或者 关键字 modify

alter table student modify id int primary key auto_increment;

在表中新增一个字段，关键字 add 并且column可加可不加

alter table student add name varchar(20) not null defatult '消亡' comment '姓名';

alter table student add column name varchar(20) not null defatult '消亡' comment '姓名';

在表中新增一个字段放在首列

alter table student add id int first;

在表中新增一个字段放在指定字段后面

alter table student add grade int after age;

在表中新增多个字段，两种写法，但是删除字段只有一种写法，默认放在尾列

alter table student add(a int,b int);
#或者
alter table student add c int, add d int;

将字段移动到某个字段后面，
需要注意的是必须要改动需要变动字段的类型才能modify成功

alter table student modify a int after b;

删除一个，或多个字段

alter table student drop a,drop b;

4，增删改查

insert 语法

插入一条数据

#按字段插入数据
insert into student(id,name) values(1,'xiaotian');
#插入一整条数据 ，可以不指定字段名，所以必须全部字段都要有数据，自增也需要有
insert into student values(10，'girl',18,0,0555);

插入多条数据，必须带上需要插入数据的字段，自增可以不用带上

  insert into student(name,age,sex,phone)
  values
  ('aa',3,0,333),
  ('bb',4,1,444),
  ('cc',5,1,555);

delete 语法

#删除一条数据
delete from student where id = 1;
#删除多条数据,使用where条件是关键
delete from student where id < 6;

update 语法

#修改一条数据
update student set name = 'yy' where id = 1;
#也可以使用where条件修改多条数据，但意义不大
update student set name = 'yy' where id < 5;

select 语法

基本用法

select * from student;

条件 **where** 所接的可能性
#不等式
select * from student where id <= 10;
#判断为空 is null /  **判断不为空 is not null**
select * from student where name age is null;
select * from student where name is not null;
#并且 and 
select * from student where id = 10 and age < 19;
#或者 or 
select * from student where id > 10 or age <19;
#两者之间 between  / 不等式+and
select * from student where age between 10 and 100;
select * from student where age > 10 and age < 100;
#不在两者之间
select * from student where age not between 10 and age;
#集合中 in   / 不在结合中 not in
select * from student where age in(10,19,20);
select * from student where age not in(10,19,20);
#模糊查找 like
select * from student where name like '%a%';
#分页查找  limit
#前面为数据的下标（下标从零开始计算类似数组，第一条数据的下标为零）,后面是需要显示的条数
select * from student limit 5,10;
#排序 默认升序asc， 降序为desc(查看表结构也是这个名字)
select * from student order by age desc;

聚合函数
#统计数据条数 count()
select count(id) from student;
#计算总和 sum()
select sum(age) from student;
#最大max()最小min()
select max(age) from student;
#平均数 avg()
select avg(age) from student;
#取重 distinct
select distinct(name) from student;

分组 group by
(分组一般和聚合函数一起使用)(当分组时使用了聚合函数,那么select后面只能加用来分组的字段,在通过分组时可以使用多个字段来进行分组，相当于比较多个字段相同的数据)
select sex,count(id) from studnet group by sex;#查询表中各性别有多少人

一个stu表有4个字段id，class，name，age用sql语句查找出class，name，age都相同的id 
select group_concat(id),name,age,class from stu group by name,age,class having count(id) > 1;

分组完进行筛选 having 可以对已经分组完的数据进行筛选
#跟group by 组合使用的筛选的关键字,分组查询完在进行条件判断
select sex,count(id) from studnet group by sex having count(id) > 10;

####注意点####、
1，where 不能放在GROUP BY 后面
2，having是跟GROUP BY连在一起用的，放在GROUP BY后面，
3，order by 放在where后面放在group by 前面

多表查询
基本查询
select * from a,b where a.id = b.id;

内联 inner join 
2个相拼接的字段同时存在的数据才会进行连接,没有同时存在的数据不会展示出来
select * from a inner join b on a.id = b.id;

左联、右联
以左边/右边的表为主表,显式全部数据,右边的表有相同数据的则连接一起展示,没用的数据则会以null补齐
select * from a left join b on a.id = b.id;
select * from a right join b on a.id = b.id;

硬连接 union
把前面没有的数据从后面的表中全部查出来接到后面，两个表的字段数必须相同
select * from a union select * from b;

嵌套查询
将一条select语句查询出来的临时表当作一个新表进行查询，或是将select出来的数据当作一个集合或值用来作条件语句

临时表当新表
临时表
在临时表中进行查询select ab.age from(select * from a,b where a.id = b.id) as ab where ab.name = 'zhang';
多个临时表连接，所有数据可以直接从各临时表上取
```sql
select name,dept_name,incoming from (select max(incoming) tem,dept2 from emp group 
by dept2) a,(select * from emp,dept where emp.dept2 = dept.dept1) b where a.tem = b.incoming and a.dept2 = b.dept2 order by incoming desc;

将select 出来的单个值当作条件
值 select id from a where a.name = ‘zhang’;
把值当作条件select name from b where id = (select id from a where a.name = ‘zhang’);

将select出来的数据当作集合 in/ not in
集合select id from a where a < 10;
把集合当作条件 select * from b where id in(select id from a where a < 10);

索引

索引在数据很多多的时候对筛选数据有着优化的作用

显示表存在的索引
show index from stu;
创建普通索引，可以在不同的字段上创建同名索引
create index a on stu(name,age);
删除索引
alter table stu drop index a;
创建唯一索引，唯一索引不能重复，但可以为空，所以有重复数据的字段不能添加索引
create unique index on stu(name);
主键约束也是主键索引

视图

由基本表构建的一个虚拟的表，但是该虚拟表与基本表又相互影响

创建视图，创建的视图会出现在表的列中，可以进行增删改查，且基本表的数据也会被改变，反之一样
create view a as(select name,age from stu);#把stu中查出来的两个字段作为a视图
查看视图,
show tables;
删除视图
drop view a;#删除名为a的视图

存储过程

构建存储过程类似与写一个函数，该函数主要针对数据的CRUD，写存储过程主要注意以下几点
1，存储过程可以带参数，记得加字段类型
2，有几个if 就要有几个 end if;
3,while 循环由end while结束
4，declare 声明变量时需要给字段类型，且default时数据来源于表中要打括号

写法

create procedure dd()
begin
	中间写操作逻辑
end

调用

call dd;

删除

drop procedure if exists dd;

放个题的例子
现在有一个user用户表,需要往user表中插入1000个登录用户
要求如下：
1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实际的行数、如若不存在则自动补齐剩余的数据
2、表名为user,存储过程名字随意取,表中字段有id 、user_name 、user_pwd、verify 格式如下(1,user1,123456,W4E38J),且id、用户名不能重复,verify验证码字段为随机生成6位数验证码

建表
create table user(id int primary key auto_increment, user_name varchar(20) unique,									user_pwd varchar(20), verify int(6))engine = innodb;

创建存储过程
drop procedure if exists ins;
create procedure ins()
begin
	declare i int default(select count(id) from user);
	declare j int default 0;
	if i >= 1000 then
		select count(id) from user;
	else
		while i < 1000 do
		    #随机生成验证码：set j=(select substring(md5(rand()),1,6)); 或者数字五位数密码
			select floor(rand()*(999999-100000)+100000) into j;
			set i = i + 1;
			insert into user(user_name,user_pwd,verify) 
			values(concat('user',i),123456,j);
		end while;
		select * from user;
	end if;
end

call ins;
truncate user;