数据库基本操作

关系型数据库： 以二维表存储数据

数据表操作

  • 数据表的创建
  1. -- unsigned: 无符号
  2. -- primary key： 主键
  3. -- auto_increment： 自动递增
  5. create table demo (
  6.     id int unsigned primary key auto_increment,
  7.     name varchar(10),
  8.     age int unsigned
  9. )
  • 数据表的删除
  2. -- 删除掉 demo 表
  3. drop table demo;
  5. -- 如果数据库中存在demo表，就把它从数据库中drop掉。
  6. drop table if exists demo;
  8. -- 使用场景
  9. drop table if exists demo;
  10. create table demo (
  11.     id int unsigned primary key auto_increment,
  12.     name varchar(10),
  13.     age int unsigned
  14. )

数据操作

  • 增
  1. -- 单条添加
  2. insert into demo values(null, "鲁班", 20);
  4. -- 多条添加
  5. insert into demo(name, age) values("鲁班大师", 50),("凯", 32),("安琪拉", 20), ... ...
  • 删
  1. -- 删除表中所有数据
  2. delete from demo;
  4. -- 按条件删除数据
  5. delete from demo where name ="鲁班大师";  ==> 删除 name 是 鲁班大师的整条数据
  • 改
  1. update demo set name = "小乔", age = 20 where id = 10;
  • 查
  2. select * from demo;
  3. select name, age from demo;
  5. -- 条件查询
  6. select name from demo where id = 1;
  8. -- where 支持多种运算符
  9.    - 比较运算符： =, >, <, >=, <=, !=
  10.    - 逻辑运算符： and(且), or(或), not(非)
  11.    - 模糊查询： like ==> %, _ 
  12.      + where name like '孙%'
  13.      + where name like '孙_'
  14.      + where name like '%孙%'
  15.      + where name like '_ _'
  16.    - 范围查询 in('男', 女), between 18 and 20
  17.    - 空判断： null , isnull
  20. -- 设置别名
  21. select name as 姓名, age as 年龄 from demo;
  23. -- 数据表设置别名
  24. select D.name, D.age from demo as D;
  • 去掉字段中重复的数据
  1. select distinct sex from student;
  3. -- 去掉多个字段中重复的数据
  4. select distinct sex, class from student;
  • 排序
  1. select * from student order by age;
  2. -- 升序： order by age asc (默认)
  3. -- 降序： order by age desc
  5. -- 多次排序
  6. select * from student order by age,id desc 先年龄正序，id降序
  8. -- 中文排序
  9. select * from student order by convert(name using gbk)

聚合函数

  • count 统计
  1. select count(*) from student;  ==> 只要有值就统计
  2.        count(name)
  • max 最大值
  1. select max(age) from student;
  • min 最小值
  1. select min(age) from student;
  • sum 求和
  1. select sum(age) from student;
  • avg 平均值
  1. select avg(age) from student;

分组

  • group by 根据某一字段排序，可以去重
  1. -- 每个班级的平均，最大年龄
  2. select class, avg(age), max(age) from student group by class;

连接查询

  • 等值查询
  1. select * from student as stu, score as sc where stu.sid = sc.sid;
  • 内查询
  1. select * from student as stu
  2. inner join score as sc on stu.sid = sc.sid;
  • 多表连接(两两之间产生条件)
  1. select * from student as stu
  2. inner join score as sc on stu.sid = sc.sid
  3. inner join course as co on sc.cid = co.cid;
  • 自关联（同一个表查询多次，自己产生关联，表必须起别名）
  1. select * from areas as sheng
  2. inner join areas as shi on sheng.pid = shi.pid
  • 左连接（jion 前边的表）
  1. select * from student as stu
  2. left join score as sc on stu.sid = sc.sid;
  • 右连接
  1. select * from student as stu
  2. right join score as sc on stu.sid = sc.sid;
  • 子查询
  1. -- 查询大于平均年龄的学生
  2. select * from student whrer age > (select avg(age) from student);
  • 数据分表
  1. create table newStudent (
  2.     id int unsigned primary key auto_increment,
  3.     name varchar(10),
  4.     age int unsigned
  5. )
  6. -- 查询的数据插入到另一个表中（查询出来的列必须对应表中的字段名，否则会新建）
  7. insert into newStudent(id, name, age )  select id,name,age from student;
  9. -- 创建并直接插入（查询出来的列必须对应表中的字段名，否则会新建）
  10. create table newStudent (
  11.     id int unsigned primary key auto_increment,
  12.     Sname varchar(10)
  13. ) select name as Sname  from student;

索引（作用于某个字段）

加索引后会使写入、修改、删除变慢，每一次增加数据平衡树都会重新排列，也会增加表的体积，占用磁盘存储空间。

  • 查看索引
  1.     show index from 表名;
  • 创建索引（创建索引后，表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是「平衡树」结构）
  1. -- 建表时创建索引 key (age)、primary key、unique
  2. create table newStudent (
  3.     id int unsigned primary key auto_increment,
  4.     name varchar(10) unique
  5.     age int unsigned,
  6.     key (age)
  7. )
  9. -- 已经存在的表创建索引
  10. create index 索引名称 on 表名(字段名(长度))
  11. create index i_index on newStudent(name(10))