SQL常用语句

-- 创建表格
-- create table workers(
-- 	workerId int  not null  auto_increment,
-- 	workerName  varchar(10) not null,
-- 	 slalry  int not null,
-- 	 sex varchar(10) not null,
-- 	 primary key (workerId)

-- )engine=InnoDB  DEFAULT CHARSET=utf8mb4;
-- 删除表格
-- drop table student
   插入信息
-- insert into workers (workerName,slalry ,sex) values('王',30,'male');
-- INSERT INTO workers (workerName,slalry ,sex) VALUES('张',30,'flmale');
-- INSERT INTO workers (workerName,slalry ,sex) VALUES('高',40,'flmale');
-- INSERT INTO workers (workerName,slalry ,sex) VALUES('名',66,'male');
-- INSERT INTO workers (workerName,slalry ,sex) VALUES('大',99,'male');
    查询男性的人名
-- select workerName from workers where sex='male';
    更改小明的slalry
-- update workers set slalry=101 where workerName = '小明';
    删除第一个人
-- delete from workers where workerId = 1;
    嵌套链接
-- select * from workers A where sex in (select sex from workers where slalry <50 and A.`workerName`='高'); 
    求和函数
-- select sum(slalry) from workers  where sex = 'male';

     as的使用
-- select count(workerId) as num  from workers  F  where sex     in  (SELECT sex from workers where slalry >50 and  F.`sex` = 'male' ); 
     新添加字段
-- alter table workers add address varchar(10);
     删除已有字段
-- alter table workers drop address;
      更改字段的属性
--  alter table workers modify sex char(20);
    if语句
-- select if(sex = 'male' ,'男','女') as  xingbie  from  workers where slalry >35;
     
-- alter table workers add OrderDate datetime
-  获取现在的时间
- SELECT NOW() AS '现在时间'
  右连接
 SELECT workers.`workerId` ,workers.`sex`,books.`bookId`,books.`bookname`
 FROM workers  RIGHT JOIN books ON workers.`workerId` = books.`bookId`;
  左连接
 SELECT workers.`workerId` ,workers.`sex`,books.`bookId`,books.`bookname`
 FROM workers  LEFT JOIN books ON workers.`workerId` = books.`bookId`;
  join on
 SELECT workers.`workerId` ,workers.`sex`,books.`bookId`,books.`bookname`
 FROM workers INNER JOIN books ON workers.`workerId` = books.`bookId`;