11111

创建数据库

create database teachingdb;

创建表

use teachingdb;

create table student

(

SNO char(5) primary key,

SNAME varchar(20) NOT NULL,

SDEPT varchar(20) NOT NULL,

SCLASS char(2) NOT NULL,

SSEX char(1),

BIRTHDAY DATE,

TOTALCREDIT decimal(4,1)

);

添加字段

use teachingdb;

alter table student add nativeplace varchar(20);

删除字段

use teachingdb;

alter table student DROP nativeplace ;

修改字段

use teachingdb;

alter table student MODIFY ssex varchar(3);

用insert，，，，into插入

use teachingdb;

insert into student values("11111","马明","计算机","01","女","2000/01/02",null);

基本查询

USE Company;

select Name,Salary from tb_emp;

select * from tb_emp;

带in的查询

USE Company;

SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1);

带between and

USE Company;

SELECT Name,Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000;

带like

USE Company;

SELECT Name,Salary

FROM tb_emp

WHERE Name LIKE 'C%';

带null和distinct

USE Company;

SELECT * FROM tb_emp WHERE DeptId IS NULL;

SELECT DISTINCT Name FROM tb_emp;

带and与or

USE Company;

SELECT * FROM tb_emp WHERE DeptId=301 AND Salary>3000;

SELECT * FROM tb_emp WHERE DeptId=301 OR DeptId=303;

带order by

USE School;

select *

from tb_score

where class_id=1 order by score desc;

带group

USE School;

SELECT*FROM tb_class GROUP BY class_id;

带limit

USE School;

SELECT*FROM tb_score ORDER BY score DESC limit 1,4;

索引

use School;

#1.创建名为pk_student的主键索引

create table student(

stu_id int not null,

name varchar(25) not null,

age int not null,

sex char(2) not null,

classes int not null,

grade int not null,

constraint pk_student primary key(stu_id)

);

#2.创建名为idx_age的普通索引

create index idx_age on student(age);

#3.创建名为uniq_classes的唯一索引

create unique index uniq_classes on student(classes);

#4.创建名为idx_group的组合索引

create index idx_group on student(name,sex,grade);

视图

use School;

#1.创建单表视图

create or replace view stu_view

as

select math,chinese,math+chinese

from student;

#2.创建多表视图

create or replace view stu_classes as

select student.stu_id,name,classes from student,stu_info

where student.stu_id=stu_info.stu_id

带比较运算符的子查询

USE Company;

#1.查询大于所有平均年龄的员工姓名与年龄

select name,age from tb_emp

where age>

(select avg(age) from tb_emp);

关键字查询

USE Company;

#1.使用 ALL 关键字进行查询

select position,salary from tb_salary

where salary>all(select salary from tb_salary where position='Java');

#2.使用 ANY 关键字进行查询

select position,salary from tb_salary

where salary>any(select salary from tb_salary where position='Java');

#3.使用 IN 关键字进行查询

select position,salary from tb_salary

where salary in(select salary from tb_salary where position='Java');

带grant

use teachingdb;

GRANT ALL PRIVILEGES ON teachingdb2.* to 'user1'@'localhost'

带revoke回收

use teachingdb;

revoke select on teachingdb.* from user1@localhost;