MySQL数据库的增删改查

1、创建数据库

-- 创建数据库
-- 使用默认的校对规则就行
create database 数据库名 default character set utf8 collate utf8_general_ci;
-- 查询所有的数据库
show databases;
-- 删除数据库
drop database 数据库名

2、创建表

-- 创建表之前要先进入某个数据库里面
use 数据库名;
-- 创建表
create table 表名（
	列名 类型 是否为空,
	列名 类型 是否为空,
	列名 类型 是否为空   --最后一个没有逗号
）engine=innodb default charset=utf8;

1. 是否为空 
	null 可以为空 （默认可以为空）
	not null 不能为空

2. 设置默认值
	create table 表名（
	id int,
	name varchar(10),
	gender char(1) default '男'  --最后一个没有逗号
）engine=innodb default charset=utf8;

3. 自增字段，在插入时不需要设置此列，默认会自增，一张表里只能有一个自增字段，且必需要将这个字段设置为主键
create table 表名（
	id int not null auto_increment primary key,
	name varchar(10),
	gender char(1)   --最后一个没有逗号
）engine=innodb default charset=utf8;

4.自增字段可以设置步长
	-- 查看的步长
	show variables like 'auto_inc%'
	-- 设置会话级别的步长，起始值
	show session variables like 'auto_inc%';
    set session auto_increment_increment=2;
    set session auto_increment_offset=10;
	-- 设置全局级别的步长，起始值
    set global auto_increment_increment=2;
    set global auto_increment_offset=10;

5.设置主键
	(1)
	create table 表名（
		id int not null primary key,
		name varchar(10),
		gender char(1)   --最后一个没有逗号
	）engine=innodb default charset=utf8;
	(2)
	create table 表名（
		id int,
		name varchar(10),
		gender char(1),
		primary key (字段)
	）engine=innodb default charset=utf8;

6.设置外键
	create table t1（
		id int not null primary key,
		name varchar(10),
		gender char(1),
		primary key (字段)
	）engine=innodb default charset=utf8;
	
	create table t2（
		id int,
		name varchar(10),
		t2_t1_id int,
		constraint fk_id foreign key (t2_t1_id) references t1(id)
	）engine=innodb default charset=utf8;
	
7.添加唯一索引
	-- 给email添加唯一索引
	create table t3(
		id int,
		name varchar(10),
		email varchar(10),
		unique uq_em (email)
		);
		
	-- 给name和email添加唯一索引
	create table t3(
		id int,
		name varchar(10),
		email varchar(10),
		unique uq_ne (name, email)
		);
		

3、向表中添加数据

-- 往表中添加数据
-- 自增字段可以不用自行添加，他会自己一个个的进行添加
insert into 表名(name,gneder) values('lily','女'),('bob','男');
-- 可以将另一张表的数据插入到这张表，类型要相同
insert into 表名(name,gneder) select name,gender from 另一个表名

4、删除表/清空表

-- 删除表
	drop table 表名;
-- 清空表
	delete from 表名 [可以加条件语句]
	truncate table 表名;
-- delete 和 truncate 的区别 
-- 前者在清空表后再插入数据，自增字段会接着被清空前的继续增加，而后者正好相反。

5、更改表

-- 增加列
	alter table 表名 add column 列名 类型;
-- 删除列
	alter table 表名 drop column 列名
-- 更改列表中的值
	update 表名 set 列名 = 新值 [where 条件]
-- 修改列名
	alter table 表名 modify column 列名 类型
	alter table 表名 change column 旧列名 新列名 类型;
-- 更改表名
	alter table 表名 rename to 新的表名;
-- 添加、删除默认值
	alter table 表名 alter column 列名 [ set default <默认值> | drop default ]	
-- 添加主键
	alter table 表名 add primary key(列名);
-- 删除主键
	alter table 表名 drop primary key;
	alter table 表名  modify  列名 int, drop primary key;
-- 添加外键
	alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
-- 删除外键
	alter table 表名 drop foreign key 外键名称
-- 添加唯一索引
	alter table 表名 add unique 名称(字段名，可以填写多个)
-- 删除唯一索引
	alter table 表名 drop index 唯一键名称

6、查表

-- 查表中的数据（不要使用*进行查询速度慢，这里为了方便使用'*'）
	select * from 表名 [后面可接where条件语句]
-- 指定字段的查询（推荐）
	select sid,sname from student where sid > 1;
-- 给查询的字段重新命名
	select sid,sname as student_name from student where sid >= 1;
	select sid,sname as student_name from student where sid != 1;

-- 其他
-- in/not in
	select * from student where sid in [1,3,4];
	select * from student where sid not in [1,3,4];
	select * from student where sid in (select tid from teacher);

-- between A and B (在A和B的闭区间内)
	select * from student where sid between 1 and 3;

-- limit (限制)
-- limit 3 （查询前三条数据）
	select * from student limit 3;
-- limit 2,3 (2表示从第三个作为起始位置[从零开始数]开始查询，3表示查询的条数)
	select * from student limit 2,3;
-- limit 3 offset 2 (3表示查询几条数据，2表示从哪个位置开始[从零开始数])
	select * from student limit 3 offset 2;

-- order by (分组, 可以配合MySQL中的一些聚合函数（count，sum，maxd等等）使用，也可以用来去重)
-- 查看男女同学各有多少（对gender进行分组）
	select count(1) from student group by gender
-- 查看某一科成绩的最高分
	select max(number) from score group by course_id=1

-- order by (排序，desc 从大到小，asc从小到大）
	select * from score order by number desc;
-- 特别的：group by 必须在where之后，order by之前

-- like （通配符）
-- 查询姓李的同学有哪些
	select * from student where sname like '李%'
-- 查询名字以‘军’字结尾的同学有哪些
	select * from student where sname like '%军'
-- 查询姓李的同学并且姓名后面只能有一个字的有哪些
	select * from student where sname like '李_'

-- 连表
-- left join (左边表中的数据全部显示，右边没有对应的显示为None), right join(右表中的数据全部显示，左边没有对应的显示为None）, inner join （左边的全显示，显示None的自动省略掉，不显示）
select * from course left join score on cid = course_id;
select * from course right join score on cid = course_id;
select * from course inner join score on cid = course_id;
-- 后面都可以加条件

-- 组合（上下连表）
-- union（去重），union all(不去重）
select sid,sname from student union select tid,tname from teacher;

-- 临时表
select * from (select * from student where sid > 1) as A where A.sid  = 3;

-- 动态获取字段的值
-- (内层的查询只能返回一个值)
select sid,(select tname from teacher where tid=sid) from student;