mysql增删改查

基础操作

mysql [–h 服务器名] –u登录名 [–P 端口号] -p; 登录mysql
set names gbk; 设置编码为gbk
show charset; 显示当前使用编码
show databases; 显示所有数据库
show tables; 显示所有数据表
show create table 表名; 显示表的创建语句
desc/describe 表名; 显示表的结构
mysql>set password for root@localhost = password('123'); 修改root密码为123

数据库及表的操作

增：

创建数据库：create database mpc [charset utf8];（设置数据库编码）

创建数据表：create table [if not exists] 表名（字段1，字段2…[索引1，索引2…，约束1，约束2…]）[表选项1，表选项2…];

字段设定形式：
字段名 类型 [字段属性1，字段属性2…]
类型：int ，tinyint, float, double, char, varchar, text, datetime, date, time, timestamp…
字段属性：
auto_increment:整数自增（一定是主键）
primary key:设定主键，且是唯一的，一个表里面只能设置一个，不能为空（null）
unique key:设定唯一索引，一个数据表里面可以设置多个唯一索引，这个字段中可以多个值为空（null）
not null:不能为空
default:设定默认值
comment ‘字段说明文字’:字段的说明；

create table if not exists createtest(
id int auto_increment primary key,
name varchar(40) not null unique key,
password varchar(40) not null,
age tinyint default 18,
email varchar(40) comment '电子邮件'
);

索引：对数据库内部的某个表所有数据进行预先的某种排序；
普通索引：key(字段名);
唯一索引：unique key(字段名);
主键索引: primary key(字段名);
联合主键：primary key(多个字段名用逗号隔开);
全文索引：fulltext（字段名）；
外键索引：foreign key（字段名） references 表名(字段名)；（一个表的字段是另一个表的某个字段（一定得是主键），如果输入的值不再外面表的字段里，则不能录入）

create table tab1_key(
id int auto_increment,
name varchar(20) not null,
emai varchar(50) not null,
age int not null default 18,
grades1 int,
primary key(id),
unique key(name),
foreign key(grades1) references tab2_key(grades2)
);
create table tab2_key(
stu_id int not null,
teacher varchar(20) not null,
floor int not null,
grades2 int auto_increment primary key
);

约束：要求数据满足什么条件的一种规定
唯一约束：unique key(字段名);
主键约束: primary key(字段名);
外键约束：foreign key（字段名） references 表名(字段名)；（一个表的字段是另一个表的某个字段（一定得是主键），如果输入的值不再外面表的字段里，则不能录入）
非空约束：not null；设定一个字段时不能为空值
默认约束：default XX值，设定一个字段的默认值为xx;
检查约束：check（某种判断语句）

create table tab_check(
age tinyint,
check (age>0 and age<100)
);-- 但是mysql并不能生效，语法能够通过

表选项：创建一个表的时候，对该表的整体设定：
charset=要使用的字符编码；
engine=要使用的存储引擎（也叫表类型）；（默认是InnoDB）

关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。这篇博文将总结和分析各个引擎的特点，以及适用场合，并不会纠结于更深层次的东西。我的学习方法是先学会用，懂得怎么用，再去知道到底是如何能用的。下面就对MySQL支持的存储引擎进行简单的介绍。

auto_increment=设定当前表的自增长字段的初始值，默认是1
comment=‘该表的一些说明文字’create table 表2 like 表1;

按照一个表的结构创建一个新表：created table 表1 like 表2;

在已有的表结构中插入相似表结构表中的所有数据：insert into 表1 select * from 表2;

删：

删除数据库：drop database [if exists] 数据库名;（if exists如果不存在不会报错）

删除数据表：drop table [if exists] 表名;（if exists如果不存在不会报错）

改：

添加字段：alter table 表名 add 新字段名 字段类型 [字段属性列表]；
修改字段：alter table 表名 change 旧字段名 新字段名 新字段类型 [新字段属性列表]；
修改字段（只改属性）：alter table 表名 modify 字段名 新字段类型 [新字段属性列表];
修改表名：alter table 表名 rename [to] 新表名;
删除字段：alter table 表名 drop 字段名;
添加普通索引：alter table 表名 add key (字段名1[,字段名2，字段名3…]);
删除普通索引：alter table 表名 drop key 字段名;
添加唯一索引（约束）：alter table 表名 add unique key(字段名1[,字段名2，字段名3…]);
添加主键索引（约束）：alter table 表名 add primary key(字段名1[,字段名2，字段名3…]);
删除主键索引（约束）：alter table 表名 drop primary key;#每一个表最多一个主键
添加外键索引（约束）：alter table 表名1 add foreign key (字段名1[,字段名2，字段名3…]) references 表名2 (字段名1[,字段名2，字段名3…]);
删除外键索引（约束）：alter table 表名1 drop foreign key 字段名;
添加字段默认值（约束）：alter table 表名 alter 字段名 set default 默认值;
删除字段默认值（约束）：alter table 表名 alter 字段名 drop default;

数据的操作

增：

形式1、insert [into] 表名[(字段名1,字段名2,...)] values(值表达式1,值表达式2,...),(...),...;

　　最常用的插入语句，可以一次性插入多行数据，用逗号隔开；其中的值表达式可以是一个“直接值”或“函数调用结果”；如果对应字段是字符或时间类型，值应该使用单引号；

形式2、replace [into]表名[(字段名1,字段名2,...)] values(值表达式1,值表达式2,...),(...),...;

　　用法和形式1相同，唯一区别是如果插入语句的主键或唯一键相同，则会直接替换已经存在的数据；

形式3、insert [into] 表名[(字段名1,字段名2,...)] select 字段名1,字段名2,...from 其他表名;

　　将select语句查询的结果数据都插入到指定的表中，需要注意select语句查询出来的对应字段；

形式4、insert [into] 表名 set 字段名1=值表达式1,字段名2=值表达式2,...;

形式5、load data infile ‘文件名（绝对路径）’ into table 表名;

　　load data:适用于载入如下图所示的“结构整齐的文本数据”（要注意有的编辑器会给文档头部添加BOM字符来占位，以致于不能够载入）

删：

delete from 表名 [where 条件] [order by 排序字段] [limit 限定行数];

　　删除数据依然是以“行”为单位进行，通常删除数据都应该带上where条件否则就会删除所有数据（极度危险），order by 和limit 是为了筛选一些特定的数据，在实际应用中用得较少；

改：

update 表名 set 字段1=值1,字段2=值2,...[where 条件] [order by 排序字段] [limit 限定行数];

　　通常update语句也都带上where条件，否则就是对所有数据进行修改（极度危险），order by 和limit 是为了筛选一些特定的数据，在实际应用中用得较少；

查：

select [all | distinct] 字段或表达式列表 [from子句] [where 子句]
[group by 子句] [having 子句] [order by 子句] [limit 子句]

字段：来源于表，表达式：类似于8+3，now()等

 

all默认值：显示所有数据；
distinct：显示所有非重复的数据；
as：另起别名。select id as 序号 from tab1；

where 类似于其他语言的if条件判断语句，有true 和false 表示永真和永假

 　　where子句是对from子句中的“数据源”中的数据进行筛选的条件设定，筛选的机制是“一行一行”进行判断，其作用和其他语言的 if 语句相似；where子句通常都需要使用各种运算符：算术运算符（+,-,*,/,%），比较运算符（>,<,>=,<=,[=,<>]国际标准的等于和不等于，==,!=），逻辑运算符（and,or,not），其他运算符（is,in,beween and,like）；

　　is运算符是对空值和布尔值的判断，常见4种使用情况：xx is null（判断某个字段是“null”值），xx is not null（判断某个字段不是“null”值），xx is true（判断某个字段为“真”），xx is false（判断某个字段为“假”[false,0,0.0,'',null]）

　　in运算符用于表示字段属于某些值中的一个，而这些值通常是零散而无规律的，xx In (值1，值2，值3...);

　　between and运算符用于判断某个字段的数据值是否在某个给定的范围，xx between 值1 and 值2;相当于xx>=值1 and xx<=值2;

　　like运算符用于对字符串进行模糊查找，xx like ‘特征值’; 特征值有2个特殊的符号，%（百分号）表示任何个数的任意字符，_（下划线）表示一个任意字符；如果特征值中包含两个特殊符号则用转义字符（\%和\_），‘%x%’指定字段包含x的数据行，'x%'指定字段以x开头的数据行，‘%x’指定字段以x结尾的数据行，‘x_’指定字段以x开头并只有两个字符的数据行，‘_x’指定字段以x结尾并只有两个字符的数据行，‘%\%%’指定字段中包含%的数据行

select * from test where id>2 and sex!='man'; id大于2且sex不等于man；
select * from test where id>2 or sex='man'; id大于2或者sex等于man；
select * from test where not(sex='man'); sex不等于man；

 

group by子句是用于对已经查询出的数据进行分组

　　group by 字段1[desc|asc],字段2[desc|asc],...; 分组的结果可以指定排序方式默认是asc（正序），通常分组使用一个字段，2个以上的字段很少

　　分组过后的数据就剩下一个一个组了，所以尽量不输出所有字段，有的字段不能针对一个组（比如带有姓名，年龄，性别字段的数据表以性别字段分组过后，姓名和年龄字段就没有意义了），所以分组过后一般有用的信息就是总量[count(*)得出该组共有多少数据]，和原来数据中的数据类型字段的聚合信息[max(age)得出最大年龄，min(age)得出最小年龄，avg(age)得出平均年龄，sum(age)得出所有人年龄的总和]（原则是只有被用来分组的字段和聚合字段）。

　　select 班级,count(*) as 人数, max(体育成绩) as 体育成绩最高分,min(体育成绩) as 体育成绩最低分,avg(体育成绩) as 体育成绩平均分,sum(体育成绩) as 所有人体育成绩总和 from test group by 班级;

having 子句的作用和where完全一样，但是是对已有的分组数据进行筛选，所以having子句一定在group by子句使用后才能使用

　　select 班级id,count(*) as 人数, max(体育成绩) as 体育成绩最高分,min(体育成绩) as 体育成绩最低分,avg(体育成绩) as 体育成绩平均分,sum(体育成绩) as 所有人体育成绩总和 from test group by 班级id having 体育成绩平均分>60;

order by 子句是将前面已经取得的数据以设定的字段进行排序后输出（desc倒序，asc顺序，默认是asc）

　　order by 字段1 [asc|desc], 字段2 [asc|desc], ...; 多个字段的排序都是建立在上一个排序的基础上如果还有“相等值”再继续排序；

　　select * from test order by age;

　　select * from test order by age, weight;

limit 子句用于将已经得到的数据取出指定的几行出来

　　limit 起始行号, 取出的行数; 起始行号从0开始计时，起始行号可以省略，省略后默认从第0行开始取出多上行；

　　select * from test where order by age desc limit 0,1;

在select语句的形式上很多的子句都可以省略，但是如果出现他们的顺序不能被打乱，where子句依赖from子句，having子句依赖group by子句，select 的字段依赖于from子句，有依赖关系的不能在所依赖的对象之前出现也不能凭空出现；用from语句查询出数据，使用where语句将查询的数据进行按需筛选，使用group by语句将筛选过的数据进行分组，使用having子句将分组的数据进行筛选，使用order by语句将筛选后的数据进行排序，用limite x,y语句将排序后的特定行输出。

　　select classid,avg(age) as age from test where sex='man' group by classid order by desc limit 0,1;

连接查询：

　　表1 [连接形式] join 表2 [on 连接条件] [[连接形式] join 表3 [on 连接条件]...];

　　交叉连接：表1 jion 表2;表示交叉连接（形式有3种）；

select * from test1,test2;
select * from test1 join test2;
select * from test1 cross join test2;

 

　　内连接：表1 [inner] join 表2 on 连接条件;
连接条件通常是两个表有共同字段，然后将两个表的共同字段等起来，有点相似于两个表的外键关系

select * from test1 join test2 on test1.id=test2.id;

　　外连接：左外连接、右外连接和全外连接（full [outer] join）mysql不支持全外连接；左右外连接就是将两个表的内链接结果再加上某个表数据多余（左右表，左表多余叫左连接，右表多余叫右连接）的那部分。
　　表1（左表） left [outer] join 表2（右表） on 连接条件;
　　表1（左表） right [outer] join 表2（右表） on 连接条件;

     

联合查询（union）：将两个具有相同字段数量的查询语句的结果以“上下堆叠”的方式合并成一个查询结果，所以两个select语句的查询结果的字段数必须是一致的，通常也应该让字段类型具有一致性，也可以进行更多的联合：select 语句1 union [all/distinct] select 语句2;默认自动消除重复行，order by和limit语句是对联合后的语句进行筛选：select 语句1 union [all/distinct] select 语句2 order by子句 limit 子句;

select id,d1,d2 from test0 union select id,c1,c2 from test2;

 有了union，所以可以使用左右外连接然后在联合就可以完成mysql的全外连接了。

any、all、some、exists子查询：

　　some和any有相同的意思，是任何一个的意思，当满足任意一个即可以查询出来，而all则需要满足所有的值才行，而且some、all都是针对列子查询，即只能针对一组值。

select * from test2 where id > any(select id from test1);
select * from test2 where id > all(select id from test1);

 

 　　exists子查询为 where exists(子查询);该子查询如果有数据行，则exists的结果是true，否则就是false，所以往往出现全部取出或者都不取出而失去了他的实际意义，所以该子查询往往不是独立的子查询，而会和主查询中的数据表建立某种关系（比如连接关系），建立的方式是隐式的在代码上并没有体现关系，但却在内部有连接的实现（在子查询的where条件语句中使用了主查询的字段）。

select * from test1 where exists(select * form test2 where type like '%x%' and id=test1.id);

表示查询test1中的数据类别带有‘x'字样的数据（而数据类别存在test2中，而test中并没有type字段，两个表可以通过id关联）
如果一个查询需求既可以用连接查询也可以使用子查询，则推荐使用连接查询效率更高

视图

create view 视图名 as select语句;
　　视图相当于其他编程语言中的函数，把select语句查询出来的所有数据封装起来作为一个视图，而在使用视图是当成一个数据表使用就好，删除视图也和删除数据表相似;

create view viewtest as select id,name,age,email from test where id between 50 and 100;
select * from view test where id>80;
drop view [if exists] viewtest;

数据备份

导出的时候注意是切换到根目录以后
1.导出整个数据库
　　mysqldump -u 用户名 -p 数据库名 > 导出的文件名
　　mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sq
2.导出一个表
　　mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
　　mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
　　mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
注：　　-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
4.导入数据库
　　mysql –h 服务器名 –u登录名 –p 数据库名<文件名
5.导入数据表
　　常用source 命令
　　进入MySQL数据库控制台，
mysql -u root -p
mysql>use 数据库
　　然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:wcnc_db.sql