mysql操作数据库表_MYSQL 操作数据库、表等操作

数据库基本操作

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会去掉相同的纪录

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