#SQL
1.SQL: **Structured Query Language 结构化查询语言**
2.SQL分类
1).DDL Data Definition Language 数据定义语言
2).DML Data Manipulation Language 数据操作语言
3).DQL Data Query Language 数据查询语言
4).DCL Data Control Language 数据控制语言
## DDL:操作数据库、表
####操作数据库：CURD
1. C(Create): 创建
* Create datebase db1;
* show datebases;
* Create datebase if not exist db2;
* Create database if not exist db3 character set utf-8;
2. R(Retrieve): 查询
* show create database db3;
3. U(Update): 修改
* alter datebase db1 character set gbk;
4. D(Delete): 删除
* drop database if exists db1;
5. 使用数据库
* select database db1;
* use db1;
####操作表：CURD
1. 创建
语法
create table person(
id int,
score double(5,2),
birthday date,
insert_time timestamp,
name varchar(30)
);
create table per like person;
2. 查询
* show tables;
* desc person;
3. 修改
* alter table person rename to student;
* alter table person character set gbk;
* alter table person add gender varchar(5);
* alter table person change gender sex varchar(10);
* alter table person modify sex varchar(2);
* alter table person drop sex;
4. 删除：
* drop table person;
* drop table if exists person;
**客户端图形化工具：SQLYog**
## DML 增删改表中数据
1. 添加数据
* insert into person(列名) values(数值);
* insert into person values(.....);
2. 删除数据：
* delete from person where id=1;
* 删除所有
- delete from person;
- truncate table person;
3. 修改数据
* update person set name="Damon",score=100 where id = 1;
## DQL 查询表中的数据
1. 基础查询
- select * from person;
- select name, age from person;
- select name, math, english, math+english as total from student;
- select distinct address from student;
- select name, math, english, math+ifnull(english, 0) as total from student;
2. 条件查询
- select * from student where age>=20;
- select * from student where age=20;
- select * from student where age between 20 and 30;
- select * from student where age>=20 && age<=30;
- select * from student where age>=20 and age<=30;
- select * from student where age=20 or age=25 or age = 30;
- select * from student where age in (20,25,30);
# select * from student where english=null; -- 错误的
- select * from student where entlish is null;
- select * from student where name like "马%";
- select * from student where name like "_化%";
- select * from student where name like "___";
- select * from student where name like "%德%";
3. 排序查询
* select * from student order by math;
* select * from student order by math, english;
- select * from student order by math ASC;
4. 聚合查询
- select count(id) from student;
- select max(english) from student;
- select min(math) from student;
- select sum(math) from student;
- select avg(math) from student;
5. 分组查询
- select sex, avg(math) from student group by sex;
- select sex, avg(math) from student where math>70 group by sex;
- select sex, avg(math),count(id) from where math>70 group by sex having count(id)>2;
6. 分页查询
- select * from student limit 0,3;
- select * from student limit 3,3;
## 约束
> **概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。**
1. 非空约束 not null
- create table student(
id int,
name varchar(10) not null
- );
* alter table student modify name varchar(10) not null;
* alter table student modify name varchar(10);
2. 唯一约束 unique
- create table student(
id int,
phone_number varchar(11) unique
);
- alter table student phone_number varchar(20) unique;
- alter table student drop index phone_number;
3. 主键约束 primary key
* create table student(
id int primaty key,
name varchar(10)
);
* alter table student modify id int primary key;
* alter table student drop primary key;
4. 自动增长 auto_increment
* create table student(
id int primary auto_increment,
name varchar(20)
);
* alter table student modify id int;
* alter table student modify id int auto_increment;
5. 外键约束
* create table student(
....
dep_id
constraint fk foreign key dep_id references department(id)
);
* alter table student add contraint fk foreign key dep_id references department(id);
* alter table student drop foreign key fk;
* 级联更新和删除
- on update cascade
- on delete cascade
##数据库的备份与还原
* 备份： mysqldump -uroot -proot db1 "d:\\aaa";
* 还原：
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
|
|
收藏
淘帖
踩
