数据库基本操作

1.数据库

(DDL create/show/alter/drop)

创建数据库

语法：
create database 表名 character set 字符集 collate 校验规则;
示例： 创建一个名字为person，并且设置该数据库字符集为utf8，校验规则为utf8_bin的数据库。
create database person character set utf8 collate utf8_bin;

查看数据库

// 查看数据库创建信息
show create database person;
// 查看数据库列表
show databases;
// 修改数据库
alter database person character set utf8;
// 使用创建的数据库
use person;
// 查看正在使用的数据库
select database();

删除数据库

drop database person;

2. 表

创建表

语法：
create table 表名(
    列名1 列名类型 约束,
    列名2 列名类型 约束,
)
注：约束就是约束字段，值有主键约束(primary key)、唯一约束(unique)、非空约束(not null)

示例：
create table student(
    id int primary key,
    name varchar(12),
    sex int,
    age int
);

查看表

show tables;
// 查看表的定义
show create table student;
// 查看表的结构
desc student;

修改表

添加列（add）、修改列（modify）、修改列名（change）、删除列（drop）、修改表名（rename）、修改表字符集

//添加列（add)
alter table 表名 add 列名 列类型 列约束;
alter table student add grade int not null;

// 修改列（modify）
alter table student modify sex varchar(2);

//修改列名（change）
alter table student change sex gender varchar(2);

//删除列（drop）
alter table student drop grade;

//修改表名（rename）
rename table student to teacher;

//修改表字符集
alter table student character set gbk;

//删除表
drop table student;

3.sql完成对表中数据CRUD的操作

（DML>>>>>> insert、update、delete）

插入数据

语法： insert into 表名(列1,列2,列3) values(值1,值2,值3);
示例： insert into student(id,name,age,gender) values(1,'张三',12,1);
简单写法： insert into student values(2,'李四',15,1);
注： 简单写法适用于全部列都插入情况，如果只插入部分列，表名后面列名是不能省略的。

// 批量插入,values 后面值集合用逗号隔开
insert into student values(3,'李小花',15,2),(4,'李五',16,1),(5,'李哈哈',25,1);

删除数据

语法： delete from 表名 where条件
示例： delete from student where id=5;
       delete from student; //这会将表中数据全部清除；
       
注： delete与truncate有什么区别？
delete DML   是删除表中数据
truncate DDL  是先删除表，再新建表

更新数据

语法： update 表名 set 列1=值1,列2=值2 where条件
示例： update student set name='修改李四名',gender=2 where id=3;
       update student set name='修改李四名',gender=2; // 不加条件会修改表中所有条数据

查询数据

select [distinct] [*] [列1,列2] from 表名 where条件  //distinct 为去除重复数据

//查看所有数据
select * from student;

//只查看表中name列
select name from student; 

//别名查询---表别名，用在多表查询
select s.name from student as s;

//别名查询---列别名(可以省略as关键字)
select name as 名字 from student;

//查询去掉重复的值,去掉age中重复的值
select distinct age from student;

//运算查询，只是在运算结果上做了运算处理，并没有改表表的结构和表的值
select *,age+1 as 一年后年龄 from student;

//条件查询
select * from student where age > 15;
注： where后面关系运算符有：<> 不等于(标准)、!= 不等于(非标准)、 > 、>= 、<、<=、 =
     where后面逻辑运算符有：and、or、not
     like模糊查询： _ 代表一个字符，% 代表多个字符
     in在某个范围中查询
例：查询不是15岁的学生
    select * from student where age <> 15;
    查询12到15岁的学生
    select * from student where age >12 and age < 15;
    select * from student where age between 12 and 15;
    查询小于13岁或者大于16岁的学生
    select * from student where age < 13 or age > 16;
    查询学生名字中带有李的学生
    select * from student where name like '李%';
    查询学生中年龄为12岁、15岁、18岁的学生
    select * from student where age in (12,15,18);
    
// 排序查询 order by(asc升序、desc降序，默认生序)
例：不写排序默认生序，按照年龄从小到大排序查询
    select * from student order by age; 
    按照年龄降序查询
    select * from student order by age desc;
    学生名中有‘李’字，然后按照升序查询
    select * from student where name like '李%' order by age asc;
    
//聚合函数
sum()求和、avg()求平均、count()统计数量、max()最大值、min()最小值
例：求学生年龄总和
    select sum(age) from student;
    获取所有学生总数
    select count(*) from student;
    查出所有学生中年龄大于平均年龄的学生
    select * from student where age > (select avg(age) from student);
注：where 后不能够接聚合函数，出现在分组关键字之前
    
// 分组
group by
例：查询按照性别分组后，男女的数量总和
   select gender, count(*) from student group by gender;
   查询按照性别分组，分组统计每组学生的平均年龄，并且平均年龄 > 14的;
   select gender, avg(age) from student group by age having avg(age) > 14;
注：having 后能够接聚合函数，出现在分组关键字后面

// sql 编写顺序：select...from...where...group by...having...order by
        执行顺序：from...where...group by...having...select...order by 

连表查询

准备两张表

mysql> select * from profession;

+------+------------+
| id   | profession |
+------+------------+
|  200 | 教师        |
|  201 | 学生        |
|  202 | 程序员      |
+------+------------+

mysql> select * from game_person;

+------+--------+--------+--------+
| id   | name   | sex    | dep_id |
+------+--------+--------+--------+
|    1 | 鲁班   | male   |    200 |
|    2 | 小乔   | female |    200 |
|    3 | 李白   | male   |    201 |
+------+--------+--------+--------+

// 交叉连接 不适用任何匹配条件,生成笛卡尔积
select * from 表-, 表二;

例子：
select * from profession, game_person;

+------+------------+------+--------+--------+--------+
| id   | profession | id   | name   | sex    | dep_id |
+------+------------+------+--------+--------+--------+
|  200 | 教师       |    1 | 鲁班   | male   |    200 |
|  201 | 学生       |    1 | 鲁班   | male   |    200 |
|  202 | 程序员     |    1 | 鲁班   | male   |    200 |
|  200 | 教师       |    2 | 小乔   | female |    200 |
|  201 | 学生       |    2 | 小乔   | female |    200 |
|  202 | 程序员     |    2 | 小乔   | female |    200 |
|  200 | 教师       |    3 | 李白   | male   |    201 |
|  201 | 学生       |    3 | 李白   | male   |    201 |
|  202 | 程序员     |    3 | 李白   | male   |    201 |
+------+------------+------+--------+--------+--------+

// 内链接 (常用) 只连接匹配的行
select * from staff inner join department on 条件(表1.字段=表2.字段);

例子：
select * from game_person inner join profession on profession.id=game_person. dep_id;

+------+--------+--------+--------+------+------------+
| id   | name   | sex    | dep_id | id   | profession |
+------+--------+--------+--------+------+------------+
|    1 | 鲁班   | male   |    200 |  200 | 教师       |
|    2 | 小乔   | female |    200 |  200 | 教师       |
|    3 | 李白   | male   |    201 |  201 | 学生       |
+------+--------+--------+--------+------+------------+

// 左外连接(常用) 优先显示左表全部记录 left join
// 右外连接(常用) 优先显示左表全部记录 right join
select * from staff left join department on 条件(表1.字段=表2.字段)
select * from staff right join department on 条件(表1.字段=表2.字段)

例子：
select * from game_person left join profession on profession.id=game_person. dep_id;

+------+--------+--------+--------+------+------------+
| id   | name   | sex    | dep_id | id   | profession |
+------+--------+--------+--------+------+------------+
|    1 | 鲁班   | male   |    200 |  200 | 教师       |
|    2 | 小乔   | female |    200 |  200 | 教师       |
|    3 | 李白   | male   |    201 |  201 | 学生       |
+------+--------+--------+--------+------+------------+

例子：
select * from game_person right join profession on profession.id=game_person. dep_id;

+------+--------+--------+--------+------+------------+
| id   | name   | sex    | dep_id | id   | profession |
+------+--------+--------+--------+------+------------+
|    1 | 鲁班   | male   |    200 |  200 | 教师       |
|    2 | 小乔   | female |    200 |  200 | 教师       |
|    3 | 李白   | male   |    201 |  201 | 学生       |
| NULL | NULL   | NULL   |   NULL |  202 | 程序员     |
+------+--------+--------+--------+------+------------+

// 全外连接 显示左右两个表全部记录

select * from 表1  left join 表2 on 条件((表1.字段=表2.字段))
union
select * from 表1  right join 表2 on 条件((表1.字段=表2.字段));

例子：
select * from game_person left join profession on profession.id=game_person. dep_id
union
select * from game_person right join profession on profession.id=game_person. dep_id;

+------+--------+--------+--------+------+------------+
| id   | name   | sex    | dep_id | id   | profession |
+------+--------+--------+--------+------+------------+
|    1 | 鲁班   | male   |    200 |  200 | 教师       |
|    2 | 小乔   | female |    200 |  200 | 教师       |
|    3 | 李白   | male   |    201 |  201 | 学生       |
| NULL | NULL   | NULL   |   NULL |  202 | 程序员     |
+------+--------+--------+--------+------+------------+

注意：
#全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full join
#强调：mysql可以使用此种方式间接实现全外连接
#注意 union与union all的区别：union会去掉相同的纪录

本文作者原创，仅供学习交流使用，转载需注明出处。