,mysql基础知识

student

primary key	key	key	外键foreign key

CREATE DATABASE BBA;
show databases;
drop DATABASE BBA;
use BBA;
CREATE TABLE `student`(
    `studnet_id` INT PRIMARY KEY,
    `NAME` VARCHAR(20) NOT NULL  UNIQUE,
    `MAJOR` VARCHAR(20) DEFAULT 'WSSB'
    );
DESCRIBE `student`;
ALTER TABLE `student` ADD gpa DECIMAL(3,2);
ALTER TABLE `student` DROP COLUMN gpa ;

INSERT INTO `student` VALUES(3,'S','b');
SElECT * FROM `student`;
SElECT `NAME` FROM `student`;

SELECT *
FROM `student`
WHERE `major` in ('b');
UPDATE     `student`

SET `NAME`='798',`major`='555'
where `studnet_id`='1';

delete from `student`
where `studnet_id`='1';

DROP TABLE `student`;

INT          整数
DECIMAL(3,2) 有小数点的数
VARCHAR(10) 字串
BLOB 图片
DATE    日期
TIMESTAMP    记录时间

drop table `employee`;
create table `employee`(
    `emp_id` INT PRIMARY KEY,
    `NAME` VARCHAR(20),
    `birth_date` DATE,
    `sex` varchar(1),
    `salary` int,
    `branch_id`int,
    `sup_id` int
    );
    create table `branch`(
    `branch_id` INT PRIMARY KEY,
    `branch_name` VARCHAR(20),
    `manager_id` int,
    foreign key(`manager_id`) references `employee`(`emp_id`)on delete set null
    );
    
alter table`employee`
add foreign key(`branch_id`)
references`branch`(`branch_id`)
on delete set null;

    
alter table`employee`
add foreign key(`sup_id`)
references`employee`(`emp_id`)
on delete set null;

create table `client`(
    `client_id` INT PRIMARY KEY,
    `client_name` VARCHAR(20),
    `phone` varchar(20)
    );
    
    create table `work_with`(
    `emp_id` INT ,
    `client_id` INT ,
    `total_sales_id` INT ,
    PRIMARY KEY(`emp_id`,`client_id`),
    foreign key(`emp_id`) references `employee`(`emp_id`)on delete cascade,
    foreign key( `client_id`) references `client`( `client_id`)on delete cascade
    );
    insert into `employee` values(206,'xiaohuang','1998-10-08','F',5000,1,null);
     insert into `employee` values(209,'xiaohuang','1998-10-08','r',5000,2,null);
      insert into `employee` values(207,'xiaohuang','1998-10-08','q',5000,1,null);
       insert into `employee` values(201,'xiaohuang','1998-10-08','u',5000,3,null);
        insert into `employee` values(205,'xiaohuang','1998-10-08','y',5000,1,null);
         insert into `employee` values(203,'xiaohuang','1998-10-08','s',5000,1,null);
    insert into `branch` values(1,'yanfa',null);
    insert into `branch` values(2,'xingzheng',null);
    insert into `branch` values(3,'zixun',null);
    
    
 update `branch`
 set`manager_id`=209
 where `branch_id`=3;
    
    
    insert into `client` values(400,'zixun','542566');
     insert into `client` values(401,'zixun','542266');
      insert into `client` values(402,'zixun','542166');
       insert into `client` values(403,'zixun','542566');
        
insert into `work_with` values(206,'400','10000');
insert into `work_with` values(207,'401','10200');
insert into `work_with` values(209,'402','10060');
insert into `work_with` values(206,'403','15000');
insert into `work_with` values(209,'404','10040');

select * from `employee`;

select * from `client`;

select * from `employee` ORDER BY `sex` DESC;

select * from `employee` order by `salary` desc;

select distinct`name` from `employee`;

select sum(`salary`) from `employee`;

select max(`salary`) from `employee`;
select min(`salary`) from `employee`;

select *
 from `client`
where `phone` like '%6';

select `name`
 from `employee`
union
select `client_name`
 from `client`
 union
 select `branch_name`
 from `branch`;
 
select `salary`
 from `employee`
 union
  select `total_sales_id`
 from `work_with`;
 
 insert into `branch` values(4,'toulan',null);
 
select *
 from `employee`
 join `branch`
 on `emp_id` = `manager_id`; 
 
 
 select `name`
 from `employee`
 where `emp_id`=(
    select `manager_id`
 from `branch`
 where `branch_name`='yanfa'
 );
 
select `emp_id`
from `employee`
 where `emp_id`in(
    select `emp_id`
 from `work_with`
 where `total_sales_id`>4000
 );
 
 delete from `employee`
 where`emp_id` =207;
 
  select *
  from`branch`;
  
    select *
  from`work_with`;
 
select *
 from  `employee`
where `birth_date` like '_____10%';
select count(*) from `employee` where `birth_date`>'1970-01-01' and `sex` = 'f';

limit 3;