创建数据库
create database dbName
创建数据库并显示数据库全部基本信息
show create database dbName
删除数据库
drop database dbName
查询所有数据库，显示数据库名
show databases
切换数据库
use database
查看当前数据库
select database()
切换字符集为gbk
alter database dbName character set gbk
创建数据表
create table student
id int;
name varchar(20);
birthday date
);
查看当前数据库所有表
show tables
查看表基本信息
show create table student
查看表字段信息
desc student
修改表名
alter table student rename stu
修改字段名
alter table student change name sname varchar(20)
修改字段数据类型
alter table student modify name int
增加字段
alter table student add age int
删除字段
alter table student drop age
删除数据表
drop table student
主键约束 primary key
外键约束 foreign key
非空约束 not null
唯一性约束 unique
默认值 default
方法1
create table student(
id int primary key,
name varchar(20)
);
方法2
create table student(
id int,
name varchar(20),
primary key(id)
);
create table student(
id int primary key not null,
name varchar(20) not null
);
create table student(
id int,
name varchar(20),
age int default 20
);
主表
create table student(
sid int primary key,
sname varchar(20)
);
从表
create table class(
cid int primary key,
student_id int
);
设置外键约束 c_s_id为外键名
alter table class add constraint c_s_id foreign key(cid) references student(sid)
删除外键约束
alter table class drop foreign key cid
创建一个示例表
create table student(
id int primary key not null,
name varchar(2o) not null,
age int
);
插入数据
insert into student(id,name,age) values(1,'Tom',20)
同时插入多条数据
insert into student(id,name) values(1,'Tom'),(2,'Jack')
更新指定数据
update student set age=20 where name='Tom'
更新所有数据
update student set age=20
删除指定数据
delete from student where name='Tom'
删除所有数据
delete from student
=====查询操作=====
查询所有字段
select * from student
查询指定字段
select id,name from student
查询并过滤重复数据
select distinct age from student
查询并添加运算符
select age+10 from student
查询总数据量
select count(*) from student
max、min查询
select max(age) from student
select min(age) from student
计算指定列的数值和, 非数值对象则结果为0
select sum(age) from student
计算指定列的平均值
select avg(age) from student
关系运算符
等于= 不等于<> 不等于!= 大于> 大于等于>= 小于< 小于等于<=
使用where查询
select * from student where age=20
使用in查询
select * from student where age in (20,21,22)
使用between……and查询，闭区间
select * from student where age between 19 and 21
使用not between……and查询，开区间
select * from student where age not between 19 and 21
使用or查询
select * from student where age>5 or name='Tom'
使用like查询
select * from student where name like 'Tom' 名字为'Tom'
select * from student where name like 'T%' 名字以'T'开头
select * from student where name like '%m' 名字以'm'结尾
select * from student where name like 'T_m' 名字为三个字母且第一个、第三个字母分别为'T'和'm'
使用limit查询
select * from student limit 3 查询前3条数据
使用group by查询
TODO
使用order by查询
select * from student order by id asc,name desc,age asc 升序asc 降序desc
为表取别名
select * from student as stu
为字段取别名
select name as sname from student
创建两个示例表
表1
create table student(
id int primary key not null,
name varchar(2o) not null,
class_id int
);
表2
create table class(
cid int,
cname varchar(20)
);
查找数据
select * from student where class_id=(
select cid from class where cname='kotlin'
)
删除关联数据
先删除关联数据
delete from student where class_id=(
select cid from class where cname='kotlin'
)
再删除本体
delete from class where cname='kotlin'
内连接查询
select student.id,class.cid from student inner join class on student.class_id=class.cid
外连接查询
左外连接，返回左表所有数据和右表指定数据
select student.id,class.cid from student left join class on student.class_id=class.cid
右外连接，返回左表指定数据和右表所有数据
select student.id,class.cid from student right join class on student.class_id=class.cid
select -> from -> where -> group by -> having -> order by -> limit
from -> where -> group by -> having -> select -> order by ->limit
