SQl语句练习

CREATE DATABASE zy  CHARACTER SET utf8;
USE zy

CREATE TABLE teachar(
 id  INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(255),
 score INT,
 address VARCHAR(255),
 usermail VARCHAR(255)
)

INSERT INTO teachar VALUES
(NULL,'张三',88,'北京','111111111@qq.com'),
(NULL,'李四',98,'上海','111111112@qq.com'),
(NULL,'王五',78,'广州','111111113@qq.com'),
(NULL,'赵六',68,'深圳','111111114@qq.com'),
(NULL,'孙七',58,'北京','111111115@qq.com'),
(NULL,'小红',67,'杭州','111111116@qq.com');
SELECT * FROM teachar;
SELECT id,NAME,score FROM teachar WHERE score>70;
ALTER TABLE teachar MODIFY COLUMN NAME VARCHAR(50)
ALTER TABLE teachar ADD pingjia VARCHAR(20);
UPDATE teachar SET score=88 WHERE NAME ='张三'
SELECT * FROM teachar WHERE score>80
SELECT * FROM teachar WHERE id IN(1,5,7);
SELECT * FROM teachar WHERE id BETWEEN 5 AND 8;
SELECT * FROM teachar WHERE id >=5 AND id<=8;
SELECT * FROM teachar WHERE NAME='小红' AND score>60;
SELECT * FROM teachar WHERE NAME='小红' OR score>90;

CREATE TABLE emp(
 id INT PRIMARY KEY AUTO_INCREMENT,
 ename VARCHAR(255),
 job VARCHAR(50),
 mgr INT,
 hiredate DATE,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2),
 deptnop INT
);
SELECT * FROM emp;
SELECT* FROM emp WHERE ename='张三';
SELECT ENAME,JOB,SAL FROM emp WHERE ENAME LIKE'___'
SELECT *FROM emp WHERE id >=1004 AND id<=1008;
SELECT * FROM emp WHERE id BETWEEN 1004 AND 1008;
SELECT * FROM emp WHERE job='文员' AND ename='黄盖';
SELECT * FROM EMP WHERE hiredate>"2001-01-01";
SELECT * FROM emp  WHERE comm IS NULL;

SELECT id ,NAME ,address FROM teachar;
SELECT id ,NAME ,score FROM teachar;
ALTER TABLE teachar MODIFY COLUMN usermail VARCHAR(50)
ALTER TABLE teachar ADD pingjia VARCHAR(20);
UPDATE teachar SET score=92 WHERE NAME ='张三';
SELECT *FROM teachar WHERE score>80
SELECT * FROM teachar WHERE id IN(1,5,7)
SELECT * FROM teachar WHERE id betweem 4 AND 9; 

select * from student2 where name='小红' and score>60;

select * from student2 where name='小红' or score>90;

select * from student2 where score is null;

select id,name,score from student2 where name!='张三';

select * from student2 order by address,score desc;

select distinct address from student2;