mysql数据库语句

在使用命令行执行操作时，插入数据库表的内容有中文会有乱码报错，先执行 set names gbk; 更改编码解码方式**【注意，只更改当前数据库或表】**

进入mysql:

mysql -u 账户名 -p 
输入密码：密码

退出mysql:

quit;

使用数据库：

use 数据库名;

数据库和表的增删改查：

• 创建：create database/table 数据库名或表名;

• 查看：show databases/tables show create database/table 数据库名或表名;

• 修改：alert database/table 数据库名或表名;

• 删除：drop database/table 数据库名或表名;

表数据的增删改查：

注意：字符和日期型数据应包含在单引号中

1、增：

• insert into 表名(列名1，列名2,…) values(数值1，数值2，…);【根据需要指定列名插入数值】

• insert into 表名 values(数值1，数值2，…) ;【插入所有字段】

• insert into 表名 values(数值1，数值2，…）,（数值1，数值2，…);

2、改：

• update 表名 set 列名=数值, 列名=数值, …;【修改所有字段】

• update 表名 set 列名=数值, 列名=数值, … where 指定修改字段的条件;

例子：

在原有数值基础上加减乘除数值：

update employee set salary=salary+1000;

3、删：

• delete from 表名;【删除所有字段】

• delete from 表名 where 指定删除的字段;

• truncate table 表名;【摧毁整张表后重新创建一个名字相同的表，delete from 表名 是一条条记录去删除】

4、查：

注意：查询默认升序

[distinct] 能过滤重复数据

• select [distinct] */查找的列 from 表名 order by 列名 asc/desc;【查找表的所有字段】

• selct [distinct] */查找的列 from 表名 where 指定查询字段的条件 order by 列名 asc/desc;

• select [distinct] */查找的列 [别名] from 表名; == select [distinct] */查找的列 as [别名] from 表名 order by 列名 asc/desc;

比较运算符：>, <, >=, <=,

between 数值 and 数值【两个数值之间】

in(数值1，数值2，数值3，…)【查找指定数值的字段】

列名 like ‘n%’【查找前缀为n的字段,%为任意长度，_为一个长度】,

数值 > n-1 and 数值 < n 【查找数值在n-1到n之间的字段】

聚合函数：

count, sum ,ifnull, avg，max/min 【ifnull表示如果数值为null，用其他值替代】

比较运算符例子：

在原有数值基础上加减乘除数值：

select name, chinese+math+english from exam;

查询英语分数在80-100之间的同学：

select name 姓名 , english 英语 from exam where english between 80 and 100;

查询数学分数为76，77，78的同学：

select name 姓名 , math 数学 from exam where math in(76,77,78);

查询姓张的同学：

select name 姓名 from exam where name like '张%';

查询英语分数大于80分小于100分的同学：

select name 姓名 , english 英语 from exam where english > 80 and english < 100;

聚合函数例子：

查询数学100分以上的同学人数：

select count(ifnull(math, 0)) from exam where math > 100;

查询班级语文总分：

select sum(ifnull(chinese, 0)) from exam;

查询班级英语平均分：

select avg(ifnull(english, 0)) from exam;

查询班级英语最高分/最低分：

select max(ifnull(english, 0)) from exam;/select min(ifnull(english, 0)) from exam;

备份恢复数据库

备份:

在cmd窗口下

mysqldump -u root -p dbName>c:/1.sql

恢复:

方式1:
在cmd窗口下

mysql -u root -p dbName<c:/1.sql

方式2:
在mysql命令下

 source c:/1.sql

要注意恢复数据只能恢复数据本身,数据库没法恢复,需要先自己创建出数据后才能进行恢复.

1 数据库

1.1 创建数据库

create database [if not exists] db_name [character set xxx] [collate xxx]

创建一个名称为mydb1的数据库

create database mydb1;

创建一个使用utf8字符集的mydb2数据库

create database mydb2 character set utf8;

创建一个使用utf8字符集，并带校对规则的mydb3数据库

create database mydb3 character set utf8 collate utf8_bin ;

1.2 查看数据库

查看所有数据库

show databases;

查看数据库的创建方式

show create database db_name;

1.3 修改数据库

alter database db_name [character set xxx] [collate xxxx]

1.4 删除数据库

drop database [if exists] db_name;

1.5 使用数据库

切换数据库

use db_name;

查看当前使用的数据库

select database();

2 表

2.1 创建表

create table tab_name(
field1 type,
field2 type,
...
fieldn type
)[character set xxx][collate xxx];

java和mysql的数据类型比较

java	mysql	描述
String	char(n) varchar(n)	char是固定字节长度，varchar不固定字节长度；varchar(40)表示最大字节为40，一个中文3个字节（utf-8）；n的最大取值：255(mysql 5.0支持的最大长度） 65535(mysql 5.1开始支持的最大长度）
byte	tinyint
short	smallint
int	int
long	bigint
float	float
double	double
boolean	bit 0/1
Date	Date、Time、DateTime、timestamp
FileInputStream、FileReader	Blob Text

创建一个员工表employee

create table employee(
id int primary key auto_increment ,
name varchar(20),
gender bit default 1,
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text 
);

约束:

约束关键字	描述
primary key	主键
unique	唯一约束
not null	不能为空
auto_increment	主键字段必须是数字类型

名字不能相同

name varchar(20) unique 

salary double not null

外键约束

2.2 查看表信息

查看表结构

desc tab_name 

查看当前数据库中的所有的表

show tables show 

查看当前数据库表建表语句

create table tab_name	 

2.3 修改表结构

(1)增加一列

alter table tab_name add [column] 列名 类型;

(2)修改一列类型

alter table tab_name modify 列名 类型;

(3)修改列名

alter table tab_name change [column] 列名 新列名 类型;

(4)删除一列

alter table tab_name drop [column] 列名;

(5)修改表名

rename table 表名 to 新表名;

(6)修该表所用的字符集

alter table student character set utf8;

2.4 删除表

drop table tab_name;

3 表记录

3.1 增加一条记录insert

insert into tab_name (field1,filed2,.......) values (value1,value2,.......);

• 插入的数据应与字段的数据类型相同。

• 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

• 在values中列出的数据位置必须与被加入的列的排列位置相对应。

• 字符和日期型数据应包含在单引号中’zhang’ ‘2013-04-20’

• 插入空值：不指定某列的值或insert into table value(null)，则该列取空值。

• 如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表insert into tab_name values(value1,value2,…);

使用insert语句向表中插入三个员工的信息。

insert into emp (name,birthday,entry_date,job,salary) values ('张飞','1990-09-09','2000-01-01','打手',999);
insert into emp (name,birthday,entry_date,job,salary) values ('关羽','1989-08-08','2000-01-01','财神',9999);
insert into emp (name,birthday,entry_date,job,salary) values ('赵云','1991-07-07','2000-01-02','保安',888);
insert into emp values (7,'黄忠',1,'1970-05-05','2001-01-01','战神',1000,null);

3.2 修改表记录

update tab_name set field1=value1,field2=value2,......[where 语句]

• UPDATE语法可以用新值更新原有表行中的各列。

• SET子句指示要修改哪些列和要给予哪些值。

• WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

将所有员工薪水修改为5000元。

update emp set salary=5000;

将姓名为’zs’的员工薪水修改为3000元。

update emp set salary=3000 where name='zs';

将姓名为’ls’的员工薪水修改为4000元,job改为ccc。

update emp set salary=4000,job='ccc' where name='zs';

将wu的薪水在原有基础上增加1000元。

update emp set salar=salary+4000 where name='wu';

3.3 删除表操作

delete from tab_name [where ....]

• 如果不跟where语句则删除整张表中的数据

• delete只能用来删除一行记录，不能值删除一行记录中的某一列值（这是update操作）。

• delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

• TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在事务中恢复。

删除表中名称为’zs’的记录。

delete from emp where name='黄忠';

删除表中所有记录。

delete from emp;

使用truncate删除表中记录。

truncate table emp;

3.4 select操作

3.4.1 select查询

// 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列列表明确指定要查找的列，distinct用来剔除重复行
select [distinct] *|field1，field2，......   from tab_name 

查询表中所有学生的信息。

select * from exam;

查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

过滤表中重复数据。

select distinct english from exam;

3.4.2 select表达式

select 也可以使用表达式，并且可以使用 as 别名

在所有学生分数上加10分特长分显示。

select name,english+10,chinese+10,math+10 from exam;

统计每个学生的总分。

select name,english+chinese+math from exam;

使用别名表示学生总分。

select name,english+chinese+math as 总成绩 from exam;
select name,english+chinese+math 总成绩 from exam;
select name english from exam;

3.4.3 where

使用where子句，进行过滤查询。

查询姓名为XXX的学生成绩

select * from exam where name='张飞';

查询英语成绩大于90分的同学

select name,english from exam where english>90;

查询总分大于200分的所有同学

select name,math+english+chinese as 总成绩 from exam where math+english+chinese>200 ;

where字句中可以使用：

• 比较运算符： > < >= <= <>

between 10 and 20 值在10到20之间
  
in(10,20,3)值是10或20或30

// pattern可以是%或者_，如果是%则表示任意多字符
like '张pattern' 

张三丰 张飞 张abcd ，如果是_则表示一个字符张_，张飞符合

• 逻辑运算符

在多个条件直接可以使用逻辑运算符 and or not

查询英语分数在 80－100之间的同学。

select name ,english from exam where english between 80 and 100;

查询数学分数为75,76,77的同学。

select name ,math from exam where math in (75,76,77);

查询所有姓张的学生成绩。

select * from exam where name like '张%';

查询数学分>70，语文分>80的同学。

select name from exam where math>70 and chinese >80;

查找缺考数学的学生的姓名

select name from exam where math is null;

3.4.4 order by

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。

Asc 升序、Desc 降序，其中asc为默认值

ORDER BY 子句应位于SELECT语句的结尾。

对数学成绩排序后输出。

select * from exam order by math;

对总分排序按从高到低的顺序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam order by 总成绩 desc;

对姓张的学生成绩排序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 总成绩 from exam where name like '张%' order by 总成绩 desc;

3.4.5 聚合函数

先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。

格式：

count(列名)

统计一个班级共有多少学生？先查出所有的学生，再用count包上

select count(*) from exam;

统计数学成绩大于70的学生有多少个？

select count(math) from exam where math>70;

统计总分大于250的人数有多少？

select count(name) from exam where (ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))>250;

格式：

sum(列名)

统计一个班级数学总成绩？先查出所有的数学成绩，再用sum包上

select sum(math) from exam;

统计一个班级语文、英语、数学各科的总成绩

select sum(math),sum(english),sum(chinese) from exam;

统计一个班级语文、英语、数学的成绩总和

select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 总成绩 from exam; 

统计一个班级语文成绩平均分

select sum(chinese)/count(*) from exam ;

注意：sum仅对数值起作用，否则会报错。

格式：

AVG(列名)

求一个班级数学平均分？先查出所有的数学分，然后用avg包上。

select avg(ifnull(math,0)) from exam;

求一个班级总分平均分

select avg((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam ;

格式：

Max()、Min()

求班级最高分和最低分（数值范围在统计中特别有用）

select Max((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;
select Min((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

3.4.6 group by

group by 字句，其后可以接多个列名，也可以跟having子句对group by 的结果进行筛选。

对订单表中商品归类后，显示每一类商品的总价

select product，sum(price) from orders group by product;

查询购买了几类商品，并且每类总价大于100的商品

select product，sum(price) from orders group by product having sum(price)>100;

having 和 where 的差别：

where语句用在分组之前的筛选，having用在分组之后的筛选，having中可以用合计函数，where中就不行。使用where的地方可以用having替代。

查询商品列表中除了橘子以外的商品，每一类商品的总价格大于500元的商品的名字

select product,sum(price) from orders where product<>'桔子'group by product having sum(price)>500;

3.4.7 mysql条件关键字执行顺序

mysql在执行sql语句时的执行顺序：from where select group by having order by

select math+english+chinese as 总成绩 from exam where 总成绩 >250; ---- 不成功
select math+english+chinese as 总成绩 from exam having 总成绩 >250; --- 成功
select math+english+chinese as 总成绩 from exam group by 总成绩 having 总成绩 >250; ----成功
select  math+english+chinese as 总成绩 from exam order by 总成绩;----成功
select * from exam as 成绩 where 成绩.math>85; ---- 成功

4 约束

4.1 创建表时指定约束

create table tb(
id int primary key auto_increment,
name varchar(20) unique not null,
ref_id int,
foreign key(ref_id) references tb2(id)
);

create table tb2(
id int primary key auto_increment
);

4.2 外键约束

4.2.1 增加外键

可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。

• RESTRICT : 只要本表格里面有指向主表的数据， 在主表里面就无法删除相关记录。

• CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。

alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];

4.2.2 删除外键

alter table 表名 drop foreign key 外键（区分大小写，外键名可以desc 表名查看）;

4.3 主键约束

4.3.1 增加主键（自动增长，只有主键可以自动增长）

alter table tb add primary key(id) [auto_increment];

4.3.2 删除主键

alter table 表名 drop primary key

4.3.3 增加自动增长

alter table employee modify id int auto_increment;

4.3.4 删除自动增长

alter table tb modify id int;		

5 多表设计

• 一对一（311教室和20130405班级，两方都是一）：在任意一方保存另一方的主键

• 一对多、多对一（班级和学生，其中班级为1，学生为多）：在多的一方保存一的一方的主键

• 多对多（教师和学生，两方都是多）：使用中间表，保存对应关系

6 多表查询

create table tb (id int primary key,name varchar(20) );
create table ta (
id int primary key,
name varchar(20),
tb_id int);

insert into tb values(1,'财务部');
insert into tb values(2,'人事部');
insert into tb values(3,'科技部');

insert into ta values (1,'刘备',1);
insert into ta values (2,'关羽',2);
insert into ta values (3,'张飞',3);

mysql> select * from ta;
+----+------+-------+
| id | name | tb_id |
+----+------+-------+
|  1 | aaa  |     1 |
|  2 | bbb  |     2 |
|  3 | bbb  |     4 |
+----+------+-------+

mysql> select * from tb;
+----+------+
| id | name |
+----+------+
|  1 | xxx  |
|  2 | yyy  |
|  3 | yyy  |
+----+------+

笛卡尔积查询

两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录，其中很多错误数据

select * from ta ,tb;

mysql> select * from ta ,tb;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
|  1 | aaa  |     1 |  1 | xxx  |
|  2 | bbb  |     2 |  1 | xxx  |
|  3 | bbb  |     4 |  1 | xxx  |
|  1 | aaa  |     1 |  2 | yyy  |
|  2 | bbb  |     2 |  2 | yyy  |
|  3 | bbb  |     4 |  2 | yyy  |
|  1 | aaa  |     1 |  3 | yyy  |
|  2 | bbb  |     2 |  3 | yyy  |
|  3 | bbb  |     4 |  3 | yyy  |
+----+------+-------+----+------+

内连接

查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

select * from ta ,tb where ta.tb_id = tb.id;
select * from ta inner join tb on ta.tb_id = tb.id;

mysql> select * from ta inner join tb on ta.tb_id = tb.id;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
|  1 | aaa  |     1 |  1 | xxx  |
|  2 | bbb  |     2 |  2 | yyy  |
+----+------+-------+----+------+

外连接

• 左外连接

在内连接的基础上增加左边有右边没有的结果

select * from ta left join tb on ta.tb_id = tb.id;

mysql> select * from ta left join tb on ta.tb_id = tb.id;
+----+------+-------+------+------+
| id | name | tb_id | id   | name |
+----+------+-------+------+------+
|  1 | aaa  |     1 |    1 | xxx  |
|  2 | bbb  |     2 |    2 | yyy  |
|  3 | bbb  |     4 | NULL | NULL |
+----+------+-------+------+------+

• 右外连接

在内连接的基础上增加右边有左边没有的结果

select * from ta right join tb on ta.tb_id = tb.id;

mysql> select * from ta right join tb on ta.tb_id = tb.id;
+------+------+-------+----+------+
| id   | name | tb_id | id | name |
+------+------+-------+----+------+
|    1 | aaa  |     1 |  1 | xxx  |
|    2 | bbb  |     2 |  2 | yyy  |
| NULL | NULL |  NULL |  3 | yyy  |
+------+------+-------+----+------+

• 全外连接

在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

select * from ta full join tb on ta.tb_id = tb.id; --mysql不支持全外连接
select * from ta left join tb on ta.tb_id = tb.id 
union
select * from ta right join tb on ta.tb_id = tb.id;

mysql> select * from ta left join tb on ta.tb_id = tb.id
  -> union
  -> select * from ta right join tb on ta.tb_id = tb.id; --mysql可以使用此种方式间接实现全外连接
+------+------+-------+------+------+
| id   | name | tb_id | id   | name |
+------+------+-------+------+------+
|    1 | aaa  |     1 |    1 | xxx  |
|    2 | bbb  |     2 |    2 | yyy  |
|    3 | bbb  |     4 | NULL | NULL |
| NULL | NULL |  NULL |    3 | yyy  |
+------+------+-------+------+------+