数据库MySQL的基本操作

MySQL的基本操作
查看数据库 show databases; 会显示当前服务器上所有的数据库
创建库 create database test; 创建了一个 test 的数据库, show databases; 进行查看
删除库 drop database test 删除 test 的数据库 show databases; 进行查看
选择库 use test; 选择test 的数据库
查看当前的 数据库 select database();
查看表 show tables; 查看当前数据库下所有的数据表 若为空 显示Empty set (0.00 sec)
创建表 create table user(username varchar(20), password char(20))
查看表结构 desc user;
查看创建方式

查看库 	show create database user;
查看表	show create table user;

删除表 drop table user;

• 指定字符集：
  • 创建时指定：create table xxx()engine=Innodb default chatrset=utf8;
  • 修改配置文件：
    • win：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
    • linux：/etc/mysql/mysql.conf.d/mysqld.conf
    • 字符集：character-set-server=utf8
    • 存储引擎：default-storage-engine=INNODB
  • 重启服务配置才能生效
• 修改字段：
  • alter table user modify username varchar(30);，不能修改字段名
  • alter table user change email em varchar(32);，可以修改字段名
• 添加字段：
  • 默认在最后添加：alter table user add age int(3);
  • 在指定字段后添加：alter table user add email varchar(60) after password;
  • 在开头添加字段：alter table user add id int(11) first;
• 删除字段：alter table user drop age;
• 修改字段位置及名称：add/modify/change都可以
  • alter tablr user modify em varchar(32) first;
• 修改表名：alter table user rename new_user;

数据库的操作:
关于索引的问题

• 索引
  • 说明：简单理解就是一本书最前面的目录，可以提高读取效率，但也不是说越多越好。
  • 分类：
    • 普通索引(index)：最基本的索引
    • 唯一索引(unique)：修改的字段不能重复
    • 主键索引(primary key)：是一种特殊的唯一索引，一张表中最多只能有一个字段设置
    • 全文索引(fulltext)：对全局数据进行添加索引
  • 示例：

alter table user add index(em);				# 给em字段添加普通索引
alter table user add unique(username);		# 给username字段添加唯一索引
alter table user add primary key(id);			# 给id字段添加主键索引
alter table user drop index em;				# 删除em字段的普通索引

• 创建表示直接指定：

create table user(
	id int auto_increment,
	name varchar(20),
	primary key(id),
	unique(name)
)engine=innodb default charset=utf8;

数据操作语言(DML)

在数据的大多数操作中 使用的都是增删改查操作

准备一张测试的表

mysql> create table star(
    -> id int auto_increment,
    -> name varchar(20) not null,
    -> money float not null,
    -> province varchar(20) default null,
    -> age tinyint unsigned not null,
    -> sex tinyint not null,
    -> primary key(id)
    -> )engine=innodb default charset=utf8;

• 插入数据：
  • 方式1：不指定字段，添加数据时需要写完整所有的字段

      insert into star values(1, '刘亦菲', 20000000, '武汉', 28, 1);

可以一次性插入多条数据，一条数据需要使用一个()包括起来。

• 方式2：指定字段，只需要传递指定字段的值
• insert into star(name, money, age, sex, province) values('赵丽颖', 8000000, 31, 1, '河北');
  插入数据的顺序与前面指定的字段名要一致。
• 注意：插入数据时可以不传值的字段
  • 自增的字段
  • 有默认值的
  • 可以为空的
• 说明：插入数据时，第二种方式使用的比较多。
• 查询数据：select * from star;
• 修改数据：update star set age=31, money=10000000 where id=1;
  • 警告：修改操作一定不要忘了指定条件，否则后果自负。
• 删除数据：delete from star where id=2;
  • 警告：删除操作一定不要忘了指定条件，否则后果自负。
• 创建库,选择库,进行添加数据

数据查询语言(DQL)

• 基础查询：select * from star;
• 指定字段查询：select name, money, province from star;
• 删除重复记录：select distinct province, sex from star;
  • 使用distinct指定的字段不能重复，指定多个字段时，字段组合不能重复
• 指定条件查询：

  条件                   	说明            
  >                    	大于            
  >=                   	大于等于          
  <                    	小于            
  <=                   	小于等于          
  =                    	等于            
  != 或 <>              	不等于           
  and                  	并且            
  or                   	或者            
  [not] between m and n	[不]在区间[m, n]区间
  [not] in ()          	[不]在指定集合中     
  [not] like 条件        	模糊匹配，%表示任意字符  

• 示例：

select * from star where age > 30;
select * from star where age > 30 and sex=1;
select * from star where age > 30 or province='河南';
select * from star where age [not] between 30 and 40;
select * from star where id [not] in(1,3,5);
select * from star where province like '%北%';

• 结果集排序：
  • 示例：

 select * from star order by age asc;	
 select * from star order by money desc;
 order by：指定排序字段
 asc：升序，默认方式
 desc：降序

• 多字段排序：当前面字段值一样时，按照后面指定的字段进行排序

select * from star order by sex asc, age desc;

• 限制结果集：

  - limit：用于限制结果集的数量
  

  • 示例：

  select * from star limit 5;						# 取前5条数据
  select * from star limit 5 offset 2;			# 偏移2条，取5条数据
  select * from star limit 2, 5; 					# 功能同上

• 分页查询：

每页10条数据,用pageSize，page是当前页面，请写出对于页码的查询条件
  第一页：limit 10
  第二页：limit 10, 10
  第三页：limit 20, 10
  page页：limit (page-1) * pageSize, pageSize

• 常用聚合函数：

  函数   		说明  
  count		统计个数
  sum  		求和  
  avg  		平均值 
  max  		最大值 
  min  		最小值 
  

  • 示例：

select count(*) as c from star;
select max(money) as max_money from star;
as可以给查询结构字段起别名

• 分组及过滤：
  • 示例：

select * from star group by sex;			# 只分组，结果集中每个组中只有一条数据
select count(*) as c, sex from star group by sex;	# 分组并统计信息
select count(*) as c, province from star group by province having c>1; 
# 分组后条件不能使用where，只能使用having!!!!

重点:多表联合查询!!

• 隐式内连接：没有出现join关键字
  • 示例：select username, name from user, goods where user.gid=goods.gid;
  • 说明：查看哪个用户购买了哪个商品
• 显式内连接：SQL语句中出现了join关键字
  • 示例：select username,name from user join goods on user.gid=goods.gid;
  • 说明：功能同上
  • join前可以添加inner或cross关键字，可以省略
• 左外连接：以左表为主，left [outer]
  • 示例：select username, name from user left [outer] join goods on user.gid=goods.gid;
  • 说明：显示左表所有数据，右表有对应就显示数据，没有对应显示NULL
• 右外连接：以有表位住，right [outer]
  • 示例：select username, name from user right[outer] join goods on user.gid=goods.gid;
  • 说明：显示有表所有数据，左表有对应就显示数据，没有对应显示NULL
• 子(嵌套)查询

select * from user where gid in (1,3,5);
select * from user where gid in (select gid from goods);

• 记录联合
  • 语法：select 语句1 union select 语句2
  • 说明：
    • union all：将两边查询结果直接拼在一起
    • union：是union all的去重结果
• 联合更新数据
  • 示例：update user u, goods g set u.gid=0, g.price=g.price-200 where u.gid=g.gid and u.id=7;
  • 说明：可以给操作的表起别名，还可以对字段的原有只进行运算

备份与恢复

• 备份：
  • 说明：就是将数据库中的数据(SQL语句)保存到一个文件中
  • 示例：mysqldump -uroot -p test > test.sql
• 恢复：
  • 说明：将保存SQL语句的文件解析，挨个执行其中的SQL语句
  • 示例：mysql -uroot -p test < test.sql