MYSQL命令总结

数据库的增删改

数据库操作

列出所有数据库		> show databases;
创建数据库			> create database 数据库名 charset=utf8;
删除数据库			> drop database 数据库名;
切换数据库			> use 数据库名;
查看当前选择的数据库	> select database();

表操作

列出当前数据库所有表	> show tables;
创建表				> create table 表名(列及类型);
	如:
		create table students(
			id int auto_increment primary key not null,
			sname varchar(10) not null,
			gender bit default 1,
			birthday datetime
		);
查看表:				> desc 表名;
修改表:				> alter table 表名 add|change|drop 列名 类型;
删除表:				> drop 表名;
更改表名:			> rename table 原表名 to 新表名;
查看表的创建语句:	> show create table 表名

数据操作

查询数据:				> select * from 表名;
增加数据:		
	全列插入:			> insert into 表名 values(...);
			如:	insert into students value(0, '陈好', 1, '1998-07-24') ;
				# id处可以插入一个错误的值来占位
	缺省插入:			> insert into 表名(列1,...) values(值1,...);
	同时插入多条数据:	> insert into 表名 values(...),(...)(...);
						> insert into 表名(列1,...) values(值1,...),(值1,...)(值1,...);
修改数据:				> update 表名 set 列1=值1,... where 条件;
删除数据:				> delete from 表名 where 条件;

数据备份与恢复

数据备份

1. 进入超级管理员: # sudo -s
2. 进入mysql库目录: # cd /var/lib/mysql
3. 运行mysqldump命令: # mysqldump -uroot -p 数据库名 > ~/Desktop/备份文件.sql;

数据恢复

1. 连接mysql,创建数据库
2. 退出连接,执行如下命令: # mysql -uroot -p 数据库名 < ~/Desktop/备份文件.sql;
   备份文件.sql中的数据内容是一系列sql命令语句, 功能是更新数据库中的表,但并没有创建数据库的语句.因此恢复数据前要手动创建同名数据库

数据的查询

查询的基本语法

	查询的基本语法 		> select 列名1,列名2... from 表名;
		select后面写表中的列名,如果是*表示选择所有列
	消除重复行:			> select distinct 列名 from 表名;
	可以用as为列起别名:		> select id as name from students;

查询条件:where

带条件的查询			> select * from 表名 where 条件;
	比较运算符: =, <, >, <=, >=, !=, <>
	逻辑运算符: and, or, not

模糊查询

模糊查询like			> select * from 表名 where 列名 like 样式;
	%	表示任意多个任意字符
	_	表示一个任意字符
		例子: 查询姓黄的学生:	> select * from students where sname like '黄%'; 

范围查询

范围约束:
	in 						表示在不连续的范围进行查询
	between ... and ...		表示在连续的范围进行查询
		例子: 查询年龄
		> select * from students where age in (18, 25);			# 只查询18和25岁的学生
		> select * from students where age between 18 and 25;	# 查询18到25岁的学生

判断空

空判断: is null 或 is not null
	注意: null 与 "" 是不同的
		例子: 查询学生的生日
		> select * from students where birthday is null;		# 查询没注册生日的学生

聚合: 快速得到统计结果,看不到具体查询结果

查询数量:		count(*) 	计算总行数, 括号中写*与列名,结果是相同的
	例子: 查询学生总数:	> select count(*) from students
求最大值:		max(列名)	返回'列名'字段的最大值
求最小值:		min(列名)	返回'列名'字段的最小值
求和:			sum(列名)	返回'列名'字段的之和
求平均值:		avg(列名)	返回'列名'字段的平均值
	例子: 查询未删除学生的最小id
	> select min(id) from students where isDelete=0;			# 返回最小id 
	> select * from students where id=(select min(id) from students where isDelete=0);	# 返回id最小的值

分组: group by

按照字段分组,此字段相同的数据会被放到同一组中进行聚合(统计)
	> select 列1,列2,聚合... 
	> from 表名 
	> group by 列1,列2,列3...;
例子:查询男女生总数:	
	> select gender as 性别, count(*)
	> from students
	> group by gender;
例子:查询各城市人数:	
	> select hometown as 家乡, count(*) as 人数
	> from students
	> group by hometown;

分组后的筛选: having 条件

表示要显示哪些组
例子:统计人数大于20的城市	
	> select hometown, count(*) as 人数
	> from students
	> group by hometown
	> having 人数>20;

排序: order by

asc指定升序,desc指定降序
	> select * from 表名 	
	> order by 列1 asc|desc, 列2 asc|desc,... ;
例子: 查询未删除男生,按id降序
	> select * from students where gender=1 and isDelete=0
	> order by id desc;

分页: limit

可用limit指定页首和页尾
	> select * from 表名
	> limit start,count;
从start开始,获取count条数据 
start是索引,从0开始,与我们定义的id无关
例子: 分页(每页显示m条数据,当前显示第n页)
	> select * from students
	> where isDelete=0
	> limit (n-1)*m,m;

查询语句总结

完整的select语句:
	> select distinct * 
	> from 表名
	> where ...
	> group by ... having ...
	> order by ...
	> limit start,count;

关系

外键: 约束关联字段的值,保证数据的有效性

插入外键: > foreign key(列名) references 外部表名(列名)
例子: 创建学生成绩表
	> create table scores(
	>	id int primary key auto_increment not null,
	>	stuid int,
	>	subid int,
	>	score decimal(5, 2),
	>	foreign key(stuid) references students(id),
	>	foreign key(subject) references subjects(id), 
	> );

外键的级联操作

在删除students表时的数据时, 若这个id值在scores已作为外键被引用,则会抛出异常
可以在引用外键的表中定义级联操作
级联的操作类型包括:
	restrict(限制)		默认值,抛出异常
	casccade(级联)		若主表记录被删除,其他表中数据也被删除
	set null			将外键设置为空
	no action			什么也不做

连接查询: 从有关系的多个表中查询相关数据

inner join语法:
	> select 表1.列1, 表2.列2, 表3.列3 ...
	> from 主表名
	> inner join 表1 on 表1与表2的关系
	> inner join 表2 on 表2与表3的关系

不需要有一个主表, 只要在所有被引用的表之间两两建立关系即可
默认只会显示在左右两表中都匹配的结果
可以选择 left join 或者 right join 将左表或右表的所有条目完全显示出来,匹配不上的显示为空
例子: 按 姓名|科目|成绩 打印成绩单
	> select students.name, subject.title, scores.score
	> from scores
	> inner join students on scores.stuid=students.id
	> inner join subjects on scores.subid=subjects.id;
例子: 查询每个男生的姓名,平均分
	> select students.name, avg(scores.score)
	> from scores
	> inner join students on scores.stuid=students.id;
	> where students.gender=1
	> group by scores.stuid

自关联: 将表中字段引用同一张表中其他字段,在一张表内保存几张表内的数据和关系

自关联表建立:物理上是一张表,逻辑上是三张表

例如:将全国各省市区信息存在三张表中过于浪费,可以只存在一张表中
	将省市区对象抽象成area有三个字段: id, atitle, pid(存储上级区划的id,省的pid=null)
	表中字段pid引用本表中的字段id
	> create table areas(
	>	aid int primary key auto_increment
	>	atitle varchar(20)
	>	pid int
	>	foreign key(pid) references areas(id)
	> );

自关联查询

对自关联的表,逻辑上对两张表进行关联查询时,只需要将本表视作两张表,连接查询即可
例子: 查询全国所有 省-市组合
	> select provinces.atitle,citys.atitle from areas as provinces	# 这里将areas视作省表
	> inner join areas as citys on provinces.id=citys.pid			# 这里将areas视作市表
	> where provinces.pid is null		# 区对应于市 正如 市对应于省,因此筛除掉结果中的 市-区组合

视图: 对查询的封装

为多次使用同一查询语句,创建视图
# 定义视图:
	> create view v_视图名 as
	>	select语句
	视图名常以v_开头,与表加以区分

事务: 对增删改操作的封装

事务是将一系列的SQL语句作为一个整体执行,若有一条语句执行失败,则整个事务相当于未执行
	使用事务的场景:	转账等操作
事务语句:
	开启: begin;
	提交: commit;
	回滚: rollback;
用法:
	在sql命令行中输入begin;开始一个事务
	输入sql语句
	输入commit;提交 或 输入rollback;回滚