数据库查询语句罗列

#1 操作文件夹（库）
   增
      create database db1 charset utf8;   --指定格式为utf8

   查
      show databases;        --查看所有的数据库
      show create database db1; --查看已经建好的数据库信息
   改
      alter database db1 charset gbk; --修改数据库的字符编码
   删
      drop database db1;
   其他
       use database_name;  ---使用某个数据库，相当于进入这个数据库(文件夹）
        select database();  ---显示当前所在的数据库

#2 操作文件（表）

   切换到文件夹下：use db1

   增
      create table t1(id int,name char(10))engine=innodb;--增加一个表，后面追加了他的数据引擎
      create table t2(id int,name char(10))engine=innodb default charset utf8;--增加一个表，后面追加了他的数据引擎和
   查
      show tables;--查看表中的所有表
      show create table t1; --查看已有的表的类型和desc功能差不多，就是表先形式不好。

      desc t1;---查看表结构
   改
      alter table t1 add age int; -----------------给已有的表中添加字段（重点）
          列：alter table ti add 添加的名 类型;
      alter table t1 modify name char(12); --------修改字段的类型。
      rename table 原表名 to 新表名   -------------修改表名
       ---------------------------------------------------------------
      2.修改字段 (以t2表为例)
        alter table t2 modify salary int not null;   # 更改salary字段的数据类型，并加not null的约束条件。
        alter table t2 change salary salaries int;   # 更改salary字段的名字为salaries,注意 会改变约束条件，需要的话，需要添加；
                                                   其实相当于删除了salary字段，重新创建了一个salaries字段。

   删
      drop table t1; --直接删除一个表。

#3 操作文件的一行行内容（记录）
   增
      insert into db1.t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
      insert into db1.t1(name) values('egon1'),('egon2'),('egon3');
   查
      select * from t1;
      select name from t1、、、、、、;
      select name,id from t1;
   改
      update t1 set name='SB' where id=4;
+     update t1 set name='SB' where name='alex';
   删
      delete from t1 where id=4;


      #对于清空表记录有两种方式，但是推荐后者
      delete from t1;
      truncate t1; #当数据量比较大的情况下，使用这种方式，删除速度快



   #自增id
   create table t5(id int primary key auto_increment,name char(10));
   create table t4(id int not null unique,name char(10));

insert into t5(name) values
('egon5'),
('egon6'),
('egon7'),
('egon8'),
('egon9'),
('egon10'),
('egon11'),
('egon12'),
('egon13');

#拷贝表结构
create table t7 select * from t5 where 1=2;
alter table t7 modify id int primary key auto_increment;

insert into t7(name) values
('egon1'),
('egon2'),
('egon3'),
('egon4'),
('egon5'),
('egon6'),
('egon7'),
('egon8'),
('egon9'),
('egon10'),
('egon11'),
('egon12'),
('egon13');


delete from t7 where  id=1; #删记录
update t7 set name=''; #修改字段对应的值


-------------------------------------------------------------------------------
 select sex, concat('(平均工资:',max(salary),')') 男员工的平均薪资 from em group by sex;
 concat('(平均工资:',max(salary),')')

 
2-权限问题
#创建用户
create user 'xbb'@'localhost' identified by '123';


#insert,delete,update,select
#级别1：对所有库，下的所有表，下的所有字段
grant select on *.* to 'lin1'@'localhost' identified by '123';

#级别2：对db1库，下的所有表，下的所有字段
grant select,drop on db1.* to 'lin2'@'localhost' identified by '123';


#级别3：对表db1.t1，下的所有字段
grant select on db1.t1 to 'lin3'@'localhost' identified by '123';

#级别4：对表db1.t1，下的id,name字段
grant select (id,name) on db1.t1 to 'lin4'@'localhost' identified by '123';
grant select (id,name),update (name) on db1.t1 to 'lin5'@'localhost' identified by '123';

#修改完权限后，要记得刷新权限
flush privileges;

3-约束
-偏移量------步长------单列唯一-----多列唯一

1 not null 与default

create table student2(
id int primary key auto_increment,
name char(5),
sex enum('male','female') not null default 'female'
);

insert into student2(name) values('alex');


create table student3(
id int primary key auto_increment,
name char(5),
age int not null default 30
);

insert into student3(name) values('alex');


2 unique
#单列唯一
create table teacher(
id int not null unique,
name char(10)
);
insert into teacher values(1,'egon');
insert into teacher values(1,'alex');

#多列唯一
#255.255.255.255
create table services(
id int primary key auto_increment,
name char(10),
host char(15),
port int,
constraint host_port unique(host,port)
);

insert into services values('ftp','192.168.20.17',8080);
insert into services values('httpd','192.168.20.17',8081);




#auto_increment_offset:偏移量
create table dep(
id int primary key auto_increment,
name char(10)
);
insert into dep(name) values('IT'),('HR'),('SALE'),('Boss');

create table dep1(
id int primary key auto_increment,
name char(10)
)auto_increment=10;
insert into dep1(name) values('IT'),('HR'),('SALE'),('Boss');


#auto_increment_increment:步长
create table dep2(
id int primary key auto_increment,
name char(10)
);
set session auto_increment_increment=2; #会话级，只对当前会话有效
set global auto_increment_increment=2; #全局，对所有的会话都有效
insert into dep1(name) values('IT'),('HR'),('SALE'),('Boss');


#auto_increment_offset:偏移量+auto_increment_increment:步长
注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
set session auto_increment_offset=2;
set session auto_increment_increment=3;
show variables like '%auto_in%';

create table dep3(
id int primary key auto_increment,
name char(10)
);
insert into dep3(name) values('IT'),('HR'),('SALE'),('Boss');


4-键表与表之间的关系

1 多对一：左边表的多条记录对应右面表的唯一一条记录
create table dep(
id int primary key auto_increment, #被关联的字段必须保证是唯一的
name varchar(20),
comment varchar(50)
);


create table emp(
id int primary key auto_increment,
name varchar(20),
dep_id int, #关联的字段，一定要保证是可以重复的
constraint fk_depid_id foreign key(dep_id)  references dep(id)
on update cascade
on delete cascade
);

建立多对一的关系需要注意
1 先建立被关联的表，被关联的字段必须保证是唯一的
2 再创建关联的表，关联的字段，一定要保证是可以重复的
ps：关联的字段一定是来自于表关联的表对应字段的值


2 一对一的关系：
1. 先建立被关联的表，被关联的表示唯一的
create table user(
uid int primary key auto_increment,
name varchar(15)
);
insert into user(name) values
('egon1'),
('egon2'),
('egon3'),
('egon4'),
('egon5'),
('egon6');



create table admin(
id int primary key auto_increment,
user_id int unique,
password varchar(20),
foreign key(user_id) references user(uid)
on update cascade
on delete cascade
);


insert into admin(user_id,password) values
(3,'alex3714'),
(5,'alex371asdf4')
;

5-多表查询

1 简单查询
select * from employee;
select name,salary from employee;

2 where条件
select name,salary from employee where salary > 10000;
select name,salary from employee where salary > 10000 and salary < 20000;
select name,salary from employee where salary between 10000 and 20000;
select name,salary from employee where salary not between 10000 and 20000;

select name,salary from employee where salary = 10000 or salary = 20000 or salary = 30000;
select name,salary from employee where salary in (10000,20000,30000);


select * from employee where salary = 10000 or age = 18 or sex='male';

select * from employee where post_comment is Null;
select * from employee where post_comment = Null;
select * from employee where post_comment is not Null;

 select * from employee where name like '%n%';

select * from employee where name like 'e__n';