编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255)
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州');
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州');
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');
 
SELECT * FROM student;

create database a;
use a;
create table q (
 id int,
  z varchar(255),
  x varchar(255),
  c varchar(255)
  );
  insert into q values (1,'野人','神农架','湖北');
  insert into q values (2,'田鸡','畜生','鸡圈');
  select *from q;
  update q set c='h' where c='湖北';
  select *from q;
  delete from q where id=1;
  select * from q;
  desc q;
 select z,x from q;


 CREATE database b;
use b;

CREATE TABLE Y(
NAME CHAR(3) ,
ID INT ,
XB CHAR(1),
cj int 
);
desc Y;

insert into Y values('赵三',1,'男',80);
insert into Y (name,id,cj) values('李四',2,50);
insert into Y(name,id,cj) values('王五',3,68);
insert into Y set NAME = '赵六' , id = 4;
insert into Y values('张三',7,'男',70);
insert into Y values('张三',9,'男',40);
insert into Y values('张三',65,'男',80);

select * from Y;

insert into Y values('李华',5,'女',87);
select * from Y;

update Y set xb = '男' where name = '李华';
update Y set cj = cj+10;
select * from Y;

delete FROM Y where id= 3;
select * FROM Y;

desc Y;

select name ,cj FROM Y;

select name as 姓名,cj as '成 绩' FROM Y;

desc Y;

select name as 姓名,cj as 成绩 , case when cj>=90 then '优秀' 
when cj >=80 then '良好' else '差' end as 成绩 FROM Y;

select name as 姓名,cj as 成绩 , case when cj>=90 then '优秀' 
when cj >=80 then '良好' when cj >=70 then '及格'
else '差' end as 成绩评定 FROM Y;

select name as 姓名 ,cj+id as '总成绩' FROM Y;

select distinct name as 姓名 from Y;

select count(*) as 总人数 FROM Y;

select count(cj) as 有成绩的人 FROM Y;

select sum(cj) as 总成绩 FROM Y;

select avg(cj) as 平均成绩 FROM Y;

select max(cj) as 最大值, min(cj) as 最小值 FROM Y; 

select variance(cj) as 方差 FROM Y;

select stddev(cj) as 标准差 FROM Y;

select std(cj) as 差 FROM Y ;

select group_concat(name,cj) FROM Y where cj=90;

CREATE TABLE y(
x int
);

insert into y values(6);/*110*/

insert into y values(1);/*001*/

select bin(bit_and(x)) FROM y;/*只有有零就为零 000*/
select bin(bit_or(x)) FROM y;/*只要有一就为一 111*/
select bin(bit_xor(x)) FROM y;/*相同为零，不同为一 111*/