对MySQL数据操作的汇总

数据库操作语句

1、查询当前所有的数据库： show databases；
2、查看当前数据库的创建方式：查看数据库的编码表： show create database 库名;
3、创建数据库的语法： create database 数据库名；
4、创建数据库的语法： create database 数据库名 character set 编码表名；（注意：如果创建数据库指定的编码表是utf8,记住不能写utf-8.。utf-8 mysql不认识）
5、删除数据库的语法： drop database 数据库名;
6、修改数据库的编码集： alter database 数据库名称 character set 字符集;
7、切换数据库： use 数据库名；
8、查询当前正在使用的数据库： select database();

对数据表结构的增删改查

1、创建数据库表的语法：
create table 表名(
列名 类型(长度), ----某一列,存放的数据是什么类型.长度表示允许列名存储数据的最大长度.
列名 类型(长度),
…
列名 类型(长度) —最后一个列不需要逗号。
);

注意：多个列之间使用逗号隔开，最后一个列不需要输入逗号。创建表的时候，一定切换到某一个具体的数据库

2、查看该数据库的所有的表： show tables；
3、查看表的列的信息（查看表结构）： desc 表名；
4、快速创建相同结构的表： create table 新表名 like 旧表名；
5、查看该数据库的所有的表： show tables；
6、查看建表语句以及字符集： show create table 表名；
7、查看表的列的信息（查看表结构）： desc 表名； 或 show columns from 表名；

单表创建时可添加约束

1、主键约束： primary key
2、自增长： auto_increment
3、唯一约束： unique
4、非空约束： not null

示例：
create table 表名(
	id int primary key auto_increment,
	name varchar(20) unique,
	age int not null,
	dept_id int,
	foreign key (dept_id) references dept(id),
	…………
);

5、外键约束： foreign key( 当前表中的列名 ) references 被引用表名(被引用表的列名);

注意：一般在开发中，被引用表的列名都是被引用表中的主键。

数据表结构修改

例：alter table 表名 增/删/改 列名 类型(长度) 约束；

1、增加列语法： alter table 表名 add 列名 类型(长度) 约束;
2、修改现有列类型、长度和约束： alter table 表名 modify 列名 类型(长度) 约束;
3、修改现有列名称： alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4、删除现有列： alter table 表名 drop 列名 ;
5、修改表名： rename table 旧表名 to 新表名;
6、修改表的字符集： alter table 表名 character set 编码集;

数据表的删除

1、 drop table 表名；
2、查看mysql服务的所有客户端和服务端使用的字符集：show variables like ‘character%’;

数据表内容的增删改

蠕虫复制：

insert into 表名1 select * from 表名2；将表名2 中的数据复制到表名1 中
1、insert语句----向数据表插入数据
方式一：insert into 表名 (列名1，列名2，列名3…) values (值1，值2，值3…);
方式二：insert into 表名 values (值1，值2，值3…);
2、update语句----数据内容修改操作
update 表名 set 列名=值,列名=值… where条件语句;
3、delete语句----删除表中数据的语句
delete from 表名 where条件语句

注意：如果没有where，删除表中的所有的记录。

4、truncate 语句----删除数据
truncate table 表名：
先删除表,再创建表，就等于将数据全部删除了。

注意：truncate与delete的区别：

1.delete

DML语言
数据还可以找回来，恢复回来
可以有条件的删除。DELETE FROM 表名 WHERE 条件

2.truncate table

DDL语言
数据不可以恢复
先将整个表删除，再重新创建
删除速度比delete快。

注意：delete与drop的区别：

1.delete

把表中的所有数据全部删除，但是表还存在。还可以向表中继续添加数据。

2.drop

把表从数据库中删除。表已经不存在，不能再向表中添加数据。

运算符（即where后面的条件）

1、>(大于)  <(小于)  >=(大于等于)  <=(小于等于)  =(相等)  <>或者!= (不相等) 
2、 and 逻辑与 ，多个条件同时成立。
	or 逻辑或 ，多个条件任意一个成立。
	not 逻辑非 ，相反的意思。
	between  ...  and ... 在两者之间取值。
	in(值1,值2,值3) 在指定值中任意取。
	like '模糊查询部分' 	
	  举例：name like '张%' 所有姓张学员。 
	    name like '%张%' 只要有张就可以。
		name like '张_' 所有姓张名字为两个字学员。
		name like '_张_'  只有中间是张，前面一个字，后面一个字。
	is null 判断该列值是否为空。  
	is not null  不是空

数据表记录的查询

1、查询数据库中的某张表的所有数据： select * from 表名;
2、查询某张表中指定的列： select 列名,列名… from 表名;
3、按条件查询： select 列名,列名… from 表名 where 条件;
4、过滤重复数据： select distinct 列名 from 表名 where 条件;
5、对查询的结果进行排序： select * from 表名 order by 列名1 asc|desc，列名2（asc|desc），列名3（asc|desc） … ;

asc是升序排列，desc是降序排列。默认是asc升序。

6、对查询出来的列名起别名： select 列名 as 别名,列名 as 别名,列名 as 别名… from 表名 where 条件;

注意：在使用别名的时候，as 关键字可以省略

7、关于列为null的问题： if null(列名，默认值)

判断该列是否为null,如果为null，返回默认值，如果不为null，返回实际的值

SQL中的聚合函数-----聚合函数不能嵌套使用

count(数目) : 统计个数（行数）,统计有多少行，或者有多少条数据。
sum(和)函数：求和。
avg(均值)函数：求平均值。
max(最大值)：求最大值。
min(最小值)：求最小值。

1、count函数–统计记录数(统计行数)： select count(*)或者count(具体列名) from 表名;

注意： 1.count(具体列名)：在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。按照列去统计有多少行数据。
2、 select count(列名) from 表名； 按具体列来进行统计行数。
select count(*) from 表名； 统计表中的行数。

2、sum函数： select sum(列名) ,sum(列名) ,…from 表名;

注意：sum 也可排除null，sum仅对数值类型的列起作用，否则会报错。

3、truncate截取函数： truncate(列名, 截取的小数位) 表示截取的意思，保留几位小数。
4、avg函数： select avg(列名) from 表名;
5、max，min函数： select max(列名),min(列名) from 表名;
6、round函数：round(数值, 小数的位数); 实现四舍五入的结果.

group by分组函数

select … from … group by 列名，列名

注意：在sql语句中的where 后面不允许添加聚合函数，可以使用having条件，表示分组之后的条件，在having后面可以书写聚合函数。
总结：having和group by 一起使用规则：group by 列名 having 条件

举例：select ... from ... where 条件1 ... gropu by ... having 条件2
执行顺序：条件1会先执行过滤，其次进行分组，最后条件2进行过滤
***********
查询的执行顺序：
1）from : 表名         
2）where： 条件过滤
3）group by: 分组
4）having ： 分组之后进行过滤。
5）select ：执行完毕之后，显示内容。
6）order by ： 根据查询的内容进行排序输出.

查询总结

from 表名
where 条件：
	  逻辑运算符：and or not
	  比较运算符：< > <= >= <> =
	  在....之间：between...and...
	  in()：任意的一个条件成立即可
	  is null/is not null
	  模糊匹配：like % _	  
group by 列：对列进行分组。
having 条件：
	  逻辑运算符：and or not
	  比较运算符：< > <= >= <> =
	  在....之间：between...and...
	  is null/is not null
	  模糊匹配：like % _
	  聚合函数（sum,avg,max,min,count）	  
order by desc(降序)/asc(升序，默认)

约束

添加外键约束：
alter table 从表名称 add foreign key (外键列的名称) references 主表名称(主键)

删除主键约束：
ALTER TABLE 表名 DROP PRIMARY KEY;

如果主键是自增会报错， 此属性只有在字段为主键时才能生效，所以我们先删除自增属性，然后再删除主键约束，操作成功。

删除自增属性：
ALTER TABLE 表名 CHANGE id id INT(10) UNSIGNED NOT NULL ;

添加自增属性：
ALTER TABLE 表名 CHANGE id id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
设置自增属性初始值：
ALTER TABLE 表名 … … AUTO_INCREMENT= 初始值
添加主键约束：
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

添加唯一约束：
ALTER TABLE 表名 ADD UNIQUE KEY (列名)
删除唯一约束：
ALTER TABLE 表名 DROP INDEX (列名)；

修改默认约束：
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
删除默认约束：
ALTER TABLE 表名 MODIFY 列名 列类型;

添加非空约束：
ALTER TABLE 表名 MODIFY 列名 列类型 NOT NULL;
删除非空约束：
ALTER TABLE 表名 MODIFY 列名 列类型;

删除外键约束：
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束的名称;
删除外键要先使用语句：
SHOW CREATE TABLE 表名;查看外键约束的名称，然后删除此外键约束。

多表查询

1.隐式内连接：select * from a,b where a.列名 = b.列名
2.显式内连接：select * from a inner join on a.列名 = b.列名

3.左外连接：select * from a left outer join b on a.id = b.id

left左侧的表为主
查询的内容，以左侧的表为主，如果左侧有数据，右侧没有对应的数据，仍然会把左侧数据进行显示，outer可以省略
注意：左表数据一定显示，右表数据只显示符合on条件的数据

4.右外连接：select * from a right outer join b on a.id = b.id

right之前的是左侧，right之后的是右侧。
如果右侧有数据，左侧没匹配到，把右侧的数据显示出来。outer可以省略
注意：右表数据一定显示，左表数据只显示符合on条件的数据

总结：

左外连接以关键字左侧数据为主。不管右侧的数据是否有对应，都把左侧的数据显示出来
右外连接以关键字右侧数据为主，不管左侧有没有数据对应。都把右侧的数据进行显示。

5.全外连接：(mysql 不支持全外连接。) 就是左外连接和右外连接之和，将左右两表中的数据全部显示出来
可以使用union来达到全外连接的查询效果。
union ：可以将左外连接查询和右外连接查询两条sql语句使用union合并起来进行查询，去掉重复的数据。
union all :不去掉重复进行合并,相当于查询一次左外连接，然后再查询一次右外连接，然后将两次的查询结果合并。
select * from a left outer join b on a.id = b.id
union all
select * from a right outer join b on a.id = b.id

子查询(将一个查询作为另一个查询的一部分)

1.in的用法: 例：where id in(1,2,3) ‘id’等于1,2,3中任意一个，相当于id=1 or id=2 or id=3.
2.all的用法：表示所有和union一起使用。
a >all(1,2,3,4) 相当于a >1 and a >2 and a>3 and a >4 或者相当于 a > max(1,2,3,4);
3.any的用法: 表示任何一个
a > any(1,3,5,6) 相当于 a>1 相当于 a > min(1,3,5,6);
a=any(1,3,5,6) 相当于 a in(1,3,5,6) 或者 a=1 or a=3 or a=5 or a=6
注意：any的后面需要跟语句，不能直接放值。
4.some的用法：表示任何一个和any的作用相同
5.as的用法：as:不仅可以用来做列的别名，还可以将查询结果通过as作为一张表来使用。
6.limit的用法：select * from 表名 LIMIT offset, row_count;
offset:起始记录数
row_count：查询记录数
注意 : 记录数是从 0 开始编号的.