创建数据库
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;