mysql的增删改查常用语法

SQL语句分类 按功能（定义、操纵、控制、查询）分类
DDL 数据定义语言，定义表、库、视图
DML 对数据表记录 增加、修改和删除操作
DCL 授权、事务控制、条件判断 
DQL (不是W3C组织 给出分类) 数据表记录查询 
也就是创数,删除,修改据库,创数,删除,修改表等(属于DDL语句)
增删改数据insert delete update(属于DML 语句)(truncate 删除)属于DDL 
数据表记录的查询 （属于DQL语句）


1、创建数据库 会为每个软件系统创建单独数据库：
语法 ： create database 数据库名称 ;  (创建数据库采用数据库服务器默认字符集 )
复杂写法 create database 数据库名称 character set 字符集 collate 比较规则 ; 
例如:
创建一个名称为mydb1的数据库。 create database mydb1;
创建一个使用utf8字符集的mydb2数据库。 create database mydb2 character set utf8;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。create database mydb3 character set utf8 collate utf8_bin;


补充：每次创建一个数据库在 数据存放目录中生成一个文件夹 ， 每个文件夹中存在 db.opt 存放默认字符集和校对规则 


2、查询数据库 
show databases; ----- 查看所有数据库 
show create database 数据库名; ------ 查看数据编码集


3、删除数据库
语法 ：drop database 数据库名称; 
例如:
查看当前数据库服务器中的所有数据库 show databases;
查看前面创建的mydb2数据库的定义信息 show create database mydb2;
删除前面创建的mydb1数据库 drop database mydb1;


4、修改数据库编码集 
语法：alter database 数据库名称 character set 字符集 collate 比较规则; 
例如:
修改mydb2字符集为gbk;  alter database mydb2 character set gbk;


切换当前使用数据库： use 数据库名称
查看当前正在使用数据库: select database();
注意：所有数据库相关操作语句都属于DDL 语句


*************************************************************************
数据表结构SQL语句 


1、数据表的创建 
语法：create table 表名(列名 类型(长度),列名 类型(长度)... );
一个数据表 可以存在很多列，每列具有类型和长度 
* 创建表时没有指定 字符集，将采用数据库默认字符集 
* 创建表之前 必须使用use db 语法指定操作数据库


创建表之前我们的先明确mysql中的数据类型.


MySQL 常用数据类型
java 中 String char  -----  mysql 中字符串型 char varchar 
* char是定长 varchar是变长 
例如：char(8) 保存 lisi，因为lisi 只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中 ，如果有 varchar(8) 自动根据存放内容改变长度


java 中 byte short int long float double ----- mysql 中数值类型 TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE


java 中 boolean ---- mysql 逻辑性 bit 存放一位数值 0 或者1 


java 中 Date ----- mysql 日期类型 date (只有日期) time(只有时间) datetime(日期时间都有) timestamp(日期时间都有 )
* datetime和timestamp 表现形式上完全相同，区别就在于timestamp 在数据库可以自定更新（当前时间）


java中 大数据类型 inputStream 二进制文件 Reader 文本文件 ------- mysql 大数据类型 blob(存放大二进制数据) text(存放大的文本文件)
* tinyblob tinytext 255字节 
  blob text 64KB 
  mediumblob mediumtext 16MB  
  longblob longtext 4GB




例如:在java中的数据类型,和mysql的一些数据类型的对比 
User {
   id   int    ------ mysql int                        
   name string  ------ mysql varchar
   password string  ----- mysql varchar
   birthday date    ----- mysql date 
}


例如:创建一个员工表:
create table employee (
   id int,
   name varchar(20),
   gender varchar(20),
   birthday date,
   entry_date date,
   job varchar(30),
   salary double,
   resume longtext
);
*** 创建数据表时，只有字符串类型必须写长度，而其他类型都有默认长度  


查看创建表的结构语法:
查看表结构 desc 表名; 


2、单表创建时约束
约束用来保证数据有效性和完整性 
主键约束 primary key : 信息记录某个字段可以唯一区分其他信息记录，这个字段就可以是主键 （唯一 非空） null?


唯一约束 unique : 该字段的值不允许重复    null
* 一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束 
非空约束 not null ：该字段的值不能为空


* 如果主键约束类型为 数值型 int bigint ，添加auto_increment 自动增长 


例:修改上面的那个员工表,给表添加主键,和唯一约束
   create table employee2 (
   id int primary key auto_increment,
   name varchar(20) unique not null,
   gender varchar(20) not null,
   birthday date not null,
   entry_date date not null,
   job varchar(30) not null,
   salary double not null,
   resume longtext
);


3、数据表结构修改
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,在上面员工表的基本上增加一个image列。---- alter table employee add image varchar(100) ;
2,修改job列，使其长度为60。 ----alter table employee modify job varchar(60) not null;
3,删除gender列。 ----alter table employee drop gender ;
4,表名改为user。 ----rename table employee to user;
5,修改表的字符集为utf8 ---- alter table user character set utf8;
6,列名name修改为username  ----- alter table user change name username varchar(20) unique not null;


查看当前数据库内所有表：show tables 
查看当前数据表字符集 ： show create table user;


4、数据表删除 
语法：drop table 表名; 


5、查看数据表结构
desc 表名; 查看表结构
show tables ; 查看当前库内所有表名
show create table 表名; 查看建表语句和字符集 


注意：所有数据表结构操作语句都是 DDL 


***********************************************************************************
数据表中数据记录的增删改查操作 


1、向数据表插入记录 
语法1：
insert into 表名(列名,列名,列名...) values(值,值,值...);  为数据表的每列进行赋值 


注意事项
1) 插入值 类型必须和 列类型匹配
2) 值长度不能超过 列定义长度
3) 值的顺序和 列顺序对应 
4) 字符串和日期型值 必须写 单引号
5) 插入空值 可以写 null 


向上面新建employee表，插入1个员工信息
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume) 
values(1,'zhangsan','male','1990-10-10','2010-01-01','sales',4000,'good boy !');


语法2: 
省略所有列名，但是后面值必须要和表中所有列进行匹配，按照表中列顺序 
insert into employee values(2,'lisi','male','1988-10-01','2008-08-17','hr',3500,'good hr !');s


语法3：
省略可以为空，有默认值部分列名，后面值要和前面列进行匹配
insert into employee(id,name,job,salary) values(3,'wangwu','boss',20000);s


插入一条中文记录
insert into employee(id,name,job,salary) values(4,'小明','清洁员',1500);
出错了：
ERROR 1366 (HY000): Incorrect string value: '\xC3\xF7' for column 'name' at row 1 ; 
错误原因：mysql client 采用默认字符集编码 gbk 


查看系统所有字符集 ： show variables like 'character%';


解决办法：修改客户端字符集为gbk 
MYSQL中共有6个地方字符集 ：client connetion result 和客户端相关 、database server system 和服务器端相关 


第一种：
当前窗口临时修改 set names gbk ;
* 只对当前窗口有效，关闭后就会失效


第二种：
配置mysql/my.ini 文件
[mysql] 客户端配置
[mysqld] 服务器端配置 


修改客户端字符集 [mysql] 后字符集  default-character-set=gbk


2、数据记录更改操作
语法: update 表名 set 列名=值,列名=值.... where条件语句; 
* 如果没有where条件语句，默认修改所有行数据 
例:
将所有员工薪水修改为5000元。 ----- update employee set salary = 5000;
将姓名为’zhangsan’的员工薪水修改为3000元。 -------  update employee set salary = 3000 where name='zhangsan' ;
将姓名为’lisi’的员工薪水修改为4000元,job改为ccc。 ------- update employee set salary=4000, job='ccc' where name='lisi';
将wangwu的薪水在原有基础上增加1000元。 ------------ update employee set salary = salary+1000 where name ='wangwu';


3、数据记录的删除操作
语法：delete from 表名 where条件语句 ;
* 如果没有where语句，将删除表中 所有记录


如果要删除表中所有数据记录，使用 truncate table 表名; 等价于 delete from 表名; 


试题：如果使用delete删除表中所有记录和使用truncate table删除表中所有记录 有何不同 ？
truncate 删除数据，过程先将整个表删除，再重新创建 
delete 删除数据，逐行删除记录 
* truncate 效率要好于 delete 
truncate 属于DDL ，delete 属于DML ======== 事务管理只能对DML 有效 ，被事务管理SQL语句可以回滚到SQL执行前状态 




注意： insert ，update ，delete 属于DML 语句




4、数据表记录的查询 （DQL语句）


语法一 ： select [distinct] * | 列名,列名... from 表名; 
select * from 表名; 查询该表中所有列信息
select 列名,列名... from 表名;  查询表中指定列的信息 
distinct 用于排重


例:
create table exam(
   id int primary key auto_increment,
   name varchar(20) not null,
   chinese double,
   math double,
   english double
); 


insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);


查询表中所有学生的信息。 ---------  select * from exam; 
查询表中所有学生的姓名和对应的英语成绩。 ----- select name,english from exam;
过滤表中重复数据 (查询英语成绩，排除完全相同重复数据) ---- select distinct english from exam; 


语法二：select 表达式(列名执行运算) from 表名;
select 列名 as 别名 from 表名; 


例:
 在所有学生分数上加10分特长分。 ---- select name,chinese+10,math+10,english+10 from exam;
 统计每个学生的总分。------- select name,chinese+math+english from exam;
 使用别名表示学生分数。----- select name,chinese+math+english as 总分 from exam;


***  在对列起别名时，as可以省略 select name,chinese+math+english as 总分 from exam; ------ select name,chinese+math+english 总分 from exam;
select name,math from exam; 查询name和math两列的值
select name math from exam; 查询name列值，起别名math




语法三: select 列名 from 表名 where条件语句


查询姓名为关羽的学生成绩 -------select * from exam where name='关羽'; 
查询英语成绩大于90分的同学 ----- select * from exam where english > 90;
查询总分大于200分的所有同学 ----- select * from exam where chinese+math+english > 200;


运算符 
1) 相等= 不等 <>
2) between  ...and... 在两者之间取值 between 70 and 80 等价于 >=70 <=80  ----- 注意前面那个数要比后面那个数要小 
3) in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90 
4) like '模糊查询pattern' 进行模糊查询 ，表达式有两个占位符 % 任意字符串 _ 任意单个字符 例如： name like '张%' 所有姓张学员  
name like '张_' 所有姓张名字为两个字学员 
5) is null 判断该列值为空
6) and 逻辑与 or 逻辑或 not 逻辑非 


查询英语分数在 90－100之间的同学。 -------- select * from exam where english>=90 and english <= 100;  select * from exam where english between 90 and 100;
查询数学分数为65,75,85的同学。 ---- select * from exam where math in(65,75,85); 
查询所有姓赵的学生成绩。---- select * from exam where name like '赵%';
查询英语分>80，语文分>80的同学。 ---- select * from exam where english > 80 and chinese > 80;


insert into exam values(null,'刘备',null,55,38);
查询语文没有成绩学员 select * from exam where chinese is null;
查询语文有成绩学员 select * from exam where chinese is not null;




语法四： select * from 表名 order by 列名 asc|desc ;  ---- asc升序 desc降序 
对数学成绩排序后输出。----------- select * from exam order by math; 默认asc升序 
对总分排序按从高到低（降序）的顺序输出  ------------ select * from exam order by math+chinese+english desc;
对学生成绩按照英语进行降序排序，英语相同学员按照数学降序 ------------- select * from exam order by english desc,math desc;


聚集函数 指SQL语句中内置函数 ---------- 分组函数（用于统计）
1) count 统计查询结果记录条数 select count(*)|count(列名) from 表名; 
统计一个班级共有多少学生？ ------------ select count(*) from exam; 
统计英语成绩大于90的学生有多少个？ ------- select count(*) from exam where english > 90;
统计总分大于220的人数有多少？ --------select count(*) from exam where chinese+math+english > 220;


2) sum 统计某一列数据的和  select sum(列名) from 表名; 


统计一个班级数学总成绩？ ----- select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩 ---- select sum(chinese),sum(math),sum(english) from exam;
统计一个班级语文、英语、数学的成绩总和 select sum(chinese+math+english) from exam;  select sum(chinese)+sum(math)+sum(english) from exam;
**** 刘备语文null ，null进行所有运算 结果都是null 
select sum(chinese)+sum(math)+sum(english) from exam; 含有刘备英语和数学成绩
select sum(chinese+math+english) from exam;  不含刘备英语和数学成绩 
* 使用ifnull函数处理 null情况 
select sum(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;  含有刘备英语和数学成绩


统计一个班级语文成绩平均分  ------ select sum(chinese)/count(*) from exam; 


3) avg 统计某一列平均值 select avg(列名) from 表名;
求一个班级数学平均分？ ---- select avg(math) from exam;
求一个班级总分平均分？---- select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 


4) max 统计一列最大值 min 统计一列最小值 
求班级最高分和最低分（数值范围在统计中特别有用）select max(chinese+math+english) ,min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 


语法五：select 分组函数 from exam group by 列名; 按照某列进行分组统计 
分组操作，就是具有相同数据记录分到一组中，便于统计 


create table orders(
id int,
product varchar(20),
price float
);


insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
例如：对订单表中商品归类后，显示每一类商品的总价 ---- 需要按照商品名称进行分组
select product,sum(price) from orders group by product;


在group by 语句后面 添加having 条件语句 ---- 对分组查询结果进行过滤 
练习：查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price) > 100;


试题：where 和 having 条件语句的区别 ？ 
where 是在分组前进行条件过滤，having 是在分组后进行条件过滤 
使用where地方都可以用 having替换 ， 但是having可以使用分组函数，而where后不可以用分组函数 




***小结 select 语句 ：
 S-F-W-G-H-O 组合 select ... from ... where ... group by... having... order by ... ; 
顺序不能改变 
解析顺序 ： from  - where -  group by - having - select - order by 
Select name from exam where name=’’ group by having order by ;