【总结】数据库基础

一.sql分类及基本语法

1.sql分类

(1)DDL:数据库定义语言,用于定义表,列,索引等数据库对象. create,drop,alter等
(2)DML:数据库操纵语言,增删改查数据库的记录insert,delete,update,select等
(3)DCL:数据库控制语言,定义了数据库的表,字段,用户的访问权限和安全级别 grant,revoke等

2.DDL语句

(1)创建数据库:create database test1;
(2)删除数据库:drop database test1;
(3)创建表:create table user(name varchar(10),age int(2));
(4)删除表:drop table user;
(5)修改表:
alter table user modify name varchar(20);
alter table user add column sex varchar(10);
alter table user drop column age;
Alter table user change age age1 int(4);
Alter table user rename user1;

3.DML语句

(1)插入记录insert into user(name,age)values(‘zhangsan’,18);
(2)删除记录:delete from user where name=’张三’
(3)更新记录:update user set age=1 where name=’ 张三’
(4)查询记录:select * from user;
(5)查询不重复的记录j:select distinct name from user;
(6)排序查询:select from user order by age desc;
(7)限制排序:select * from user order by age limit 1,30;
(8)聚合:select name,age from user group by age;
(9)聚合后条件:select name,age from user group by age having age > 18;
(having和where的区别:having是聚合后条件,where是聚合前条件)

3.DML语句

(1)插入记录insert into user(name,age)values(‘zhangsan’,18);
(2)删除记录:delete from user where name=’张三’
(3)更新记录:update user set age=1 where name=’ 张三’
(4)查询记录:select * from user;
(5)查询不重复的记录j:select distinct name from user;
(6)排序查询:select from user order by age desc;
(7)限制排序:select * from user order by age limit 1,30;
(8)聚合:select name,age from user group by age;
(9)聚合后条件:select name,age from user group by age having age > 18;
(having和where的区别:having是聚合后条件,where是聚合前条件)

1.group by详解

(1)单字段分组(去重)

select  grade  from  student   group   by   grade       查出学生等级的种类(按照等级划分,去除重复的)

(2)多字段分组(一般配合聚合函数使用)
常用的聚合函数:count() , sum() , avg() , max() , min()

select  name , sum(salary)    from   student    group  by   name , grade      按照名字和等级划分,查看相同名字下的工资总和

2.内连接 外连接

(1)内连接:返回两个表的交集部分

select * from a_table a inner join b_table bon a.a_id = b.b_id;

(2)左外连接:左表的全部数据和右表符合条件的数据

select * from a_table a left join b_table bon a.a_id = b.b_id;

(3)右外连接:右表中全部数据和左表中符合条件的数据

select * from a_table a right outer join b_table b on a.a_id = b.b_id;

(4)全外连接:相当于左外连接+右外连接。mysql不支持全外连接,可以使用union连接左外连接和右外连接

3.union union all

select * from t1 union select * from t2
select * from t1 union all select * from t2

union和union all的区别:union是将union all查询出来的结果进行了一次distinct,去除重复后的结果

3.DCL语句

(1)授权:创建一个数据库用户 z1,具有对 sakila 数据库中所有表的 SELECT/INSERT 权限:
grant select,insert on sakila.* to ‘z1‘@‘localhost‘ identified by ‘123‘;

(2)收回权限:由于权限变更,需要将 z1 的权限变更,收回 INSERT,只能对数据进行 SELECT 操作:
revoke insert on sakila.* from ‘z1‘@‘localhost‘;

二.索引

1.什么是索引

1.系统根据某种算法,将已有的数据(和未来新增的数据)单独建立一个文件,文件能够实现快速的匹配数据,并能够快速的找到对应表中的记录
2.每种存储引擎(innodb,myidsam等)对每个表至少支持16个索引,myisam和innodb默认创建的都是BTREE索引,memory存储引擎默认使用hash索引

2.索引分类

(1)主键索引:设定为主键后数据库会自动建立索引
(2)唯一索引:索引列的值必须唯一,但允许有空值

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

(3)单值索引:即一个索引只包含单个列,一个表可以有多个单列索引

CREATE INDEX idx_customer_name ON customer(customer_name);

(4)复合索引:即一个索引包含多个列。当表的行数远大于索引列的数目时可以使用复合索引

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

3.哪些情况需要创建索引

(1)主键自动建立唯一索引
(2)频繁作为查询条件的字段应创建索引(where后面的语句)

4.哪些情况不需要创建索引

(1)表记录太少
(2)经常增删改的表(重建索引)

三.存储引擎

1.myisam和innodb

(1)innodb是事务优先,它加的是行锁。适合高并发操作 。myisam是性能优先,加的是表锁
(2)innodb支持事务,外键。myisam不支持
(3)5.5之前默认myisam,5.5之后默认innodb