Java035
修改表
1)使用 DDL 语句修改表名
alter table 旧表名 rename 新表名 ;
示例:
需求:将 employees 表名修改为 emp。
alter table employees rename emp;
2)使用 DDL 修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型 ;
示例:
需求:将 emp 表中的 last_name 修改为 name
alter table emp change column last_name name varchar(30);
3)使用 DDL 语句修改列类型
ALTER TABLE 表名 MODIFY 列名 新类型 ;
示例:
需求:将 emp 表中的 name 的长度指定为 40
alter table emp modify name varchar(40);
4)使用 DDL 语句添加列
ALTER TABLE 表名 ADD COLUMN 新列名 类型
示例:
需求:在 emp 表中添加一个新的列为 commission_pct
alter table emp add column commission_pct float(4,2);
5)使用 DDL 语句删除列
ALTERTABLE 表名 DROPCOLUMN 列名;
示例:删除 emp 表中的 commission_pct
alter table emp drop column commission_pct;
MySQL 中的约束
非空约束(not null)
唯一性约束(unique)
主键约束(primary key) PK
外键约束(foreign key) FK
创建表时添加约束
查询表中的约束信息
SHOW KEYS FROM 表名 ;
主键约束:create table 表名 (列名 类型 primary key);
非空约束:create table 表名 (列名 类型 not null);
唯一约束:create table 表名 (列名 类型 unique);
外键约束:create table 表名 (列名 类型,constraint 约束名 foreign key(列名) references 参照表(列名));
示例:
需求:创建 departments 表包含 department_id 该列为主键且自动增长,department_name 列不允许重复,location_id 列不允含有空值。
create table departments(department_id int primary key auto_increment,dep
artment_name varchar(30) unique,location_id int not null);
示例:
需求:创建 employees 表包含 employees_id 该列为主键且自动增长,last_name 列不允许含有空值,email 列不允许有重复不允许含有空值,dept_id 为外键参照 departments 表的主键。
create table employees(employees_id int primary key auto_increment,last_n ame varchar(30) not null,
email varchar(40) not null unique,dept_id int,constrain t emp_fk foreign key(dept_id) references departments(department_id));
修改表实现约束的添加与删除
1)添加主键约束:alter table 表名 add primary key(列名);
删除主键约束:Alter table 表名 drop primary key约束名;
注意:删除主键时,如果主键列具备自动增长能力,需要先去掉自动增长,然后在删除主键。
2)添加非空约束: alter table 表名 modify 列名 类型 not null;
删除非空约束:alter table 表名 modify 列名 类型 null;
3)添加唯一约束 :alter table 表名 add constraint 约束名 unique(列名);
删除唯一约束:alter table 表名 drop key 约束名;
4)添加外键约束: alter table 表名 add constraint 约束名 foreign key(类名) references 参照表(列名);
删除外键:alter table 表名 drop foreign key 约束名;
删除外键索引(索引名与约束名相同):Alter table 表名 drop index 约束名;
MySQL 中的 DML 操作
1)新增语句
insert into 表名[(列名1,列名2....)] values(值1,值2...);
示例:
insert into dept values(1,'开发部',10);
insert into dept values('测试部',2);-- 错误, 如果不指定列名,则values后的值的类型和个数及顺序必须与数据表定义的向匹配insert into dept values(default,'测试部',2);-- default代表默认值,主键自增insert into dept(dname,dnum) values('市场部',20);
一次性插入多条数据:
insert into 表名[(列名1,列名2....)] values(值1,值2...),(值21,值22...);
示例:
insert into dept(dname,dnum) values('财务部',2),('人事部',3),('讨账部',10);
修改语句:
update 表名 set 列名1=值1,列名2=值2.. [where 子语句];
示例:
update dept set dnum=dnum+1 ; -- 如果没有where子语句则修改所有的记录update dept set dnum=dnum+1 where did=1;
删除语句:
delete from 表名 [where 子语句];
delete from dept where did=6;-- 删除did为6的记录delete from dept ;-- 删除所有的记录
默认值处理
在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指定该列的值,那么 MySQL 会将默认值添加到该列中。
示例:
1)创建表时指定列的默认值
需求:创建 emp3 表,该表包含 emp_id 主键且自动增长,包含 name,包含 address 该列默认值为”未知”。
create table emp3(emp_id int primary key auto_increment,name varchar(30),
address varchar(50) default 'Unknown');
2)修改表添加列的默认值
需求:修改 emp3 表,添加 job_id 该列默认值为 0。
alter table emp3 add column job_id int default 0;
删除数据(delete)
使用 delete 子句
delete from 表名 where 条件;
示例:
需求:删除 emp3 表中 emp_id 为 1 的雇员信息。
delete from emp3 where emp_id = 1
使用 truncate 清空表
truncate table 表名 ;
示例:
需求:删除 emp3 表中的所有数据
truncate table emp3;
delete 与 truncate的区别:
1)truncate 是整体删除(速度较快), delete 是逐条删除(速度较慢);
2)truncate 不写服务器 log,delete 写服务器 log,也就是 truncate 效率比 delete 高的原因;
3)truncate 是会重置自增值,相当于自增列会被置为初始值,又重新从 1 开始记录,而不是接着原来的值。而 delete 删除以后,自增值仍然会继续累加。
MySQL中自动增长
1)MySQL中自动增长使用的关键字 auto_increment
2)自动增长类型的要求: 该列必须具有主键约束或者唯一性约束;该列必须为整数类型。
MySQL 中的事务处理
1)在 MySQL 中,默认情况下,事务是自动提交的,也就是说,只要执行一条 DML 语句就开启了事物,并且提交了事务。
2)关闭 MySQL 的事务自动提交使用的关键字:start transation 。
MySQL 查询数据
1)MySQL 的列选择
SELECT * | 投影列 FROM 表名 ;
示例:
需求:查询 departments 表中的所有数据
select * from departments;
2)MySQL 的行选择
SELECT * | 投影列 FROM 表名 WHERE 选择条件 ;
示例:
需求:查询 departments 表中部门 ID 为 4 的部门名称与工作地点 ID。
select department_name,location_id from departments where department_id =4;
SELECT 语句中的算术表达式
+ :加法运算
- :减法运算
* :乘法运算
/ :除法运算,返回商
% :求余运算,返回余数
MySQL 中的列别名 关键字 as
SELECT 列名 AS 列别名 FROM 表名 WHERE 条件 ;
需求:查询 employees 表将雇员 laser_name 列名改为 name。
select last_name as name from employees;
MySQL 中去除重复 关键字distinct
需求:查询 employees 表,显示唯一的部门 ID。
select distinct dept_id from employees;
MySQL 中的比较条件
比较运算符
• 等于=
• 大于>
• 大于等于>=
• 小于<
• 小于等于<=
• 不等于<>
模糊查询
模糊查询的关键字:Like
常用的通配符:% 和_(百分号和下划线)。 %表示任意多个任意字符; _表示一个任意字符 。
范围查询
between...and :是指两值之间,表示区间。
in :表示在一个非连续的范围内(是指在括号里现有的值中)。
空值判断
判断空 is null
判断非空 is not null
示例:
需求:找出 emloyees 表中那些没有佣金的雇员
select * from employees where commission_pct is null;
示例2:
需求:找出 employees 表中那些有佣金的雇员
select * from employees where commission_pct is not null;
使用 orader by 排序
排序: order by 默认升序。
asc: 升序排序,默认
desc: 降序排序
示例:
需求:查询 employees 表中的所有雇员,薪水按升序排序。
select * from employees order by salary
示例2
需求:查询 employees 表中的所有雇员,雇员名字按降序排序。
select * from employees order by last_name desc
大小写控制函数
LOWER(str) 转换大小写混合的字符串为小写字符串
UPPER(str) 转换大小写混合的字符串为大写字符串。
字符处理
CONCAT(str1,str2,...) 将 str1、str2 等字符串连接起来 。
SUBSTR(str,pos,len) 从 str 的第 pos 位(范围:1~str.length)开始,截取长度为 len 的字符串。
LENGTH(str) 获取 str 的长度 。
INSTR(str,substr) 获取 substr 在 str 中的位置 。
LPAD(str,len,padstr)/RPAD(str,len,padstr) 。
TRIM(str) 从 str 中删除开头和结尾的空格(不会处理字符串中间含有的空格) 。
LTRIM(str) 从 str 中删除左侧开头的空格 。
RTRIM(str) 从 str 中删除右侧结尾的空格 。
REPLACE(str,from_str,to_str) 将 str 中的 from_str 替换为 to_str(会替换掉所有符合from_str 的字符串)。
数字函数
ROUND(arg1,arg2):四舍五入指定小数的值。
ROUND(arg1):四舍五入保留整数。
TRUNC(arg1,arg2):截断指定小数的值,不做四舍五入处理。
MOD(arg1,arg2):取余。
日期函数
SYSDATE() 或者 NOW() 返回当前系统时间,格式为 YYYY-MM-DD hh-mm-ss
CURDATE() 返回系统当前日期,不返回时间
CURTIME() 返回当前系统中的时间,不返回日期
DAYOFMONTH(date) 计算日期 d 是本月的第几天
DAYOFWEEK(date) 日期 d 今天是星期几,1 星期日,2 星期一,以此类推
DAYOFYEAR(date) 返回指定年份的天数
DAYNAME(date) 返回 date 日期是星期几
LAST_DAY(date) 返回 date 日期当月的最后一天
常用的转换函数
1)date_format(date,format) 将日期格式化成字符串。
2)str_to_date(str,format) 将字符串转换为日期。
多表连接查询(重要)
1.确定需要查询的哪些列? 2.确定需要查询的列分布在哪些表中? 3.确定表之间的关联关系(主外键的关系)
等值连接
select * from emp;
select * from dept;
-- 查询员工编号,姓名,薪水和部门名称select eid,ename,sal,dname from emp,dept where emp.did=dept.did
-- 查询工资大于5000的员工编号,姓名,薪水和部门名称select eid,ename,sal,dname from emp,dept where emp.did=dept.did and sal>5000
非等值连接
--查询所有雇员的名称,薪水及对应薪水级别。select ename,sal,level from emp, sal_level where sal between lowest_sal and highest_sal;
自连接(表自身连接自身)
alter table emp add column manager_id int;
update emp set manager_id=1 where eid in (2,3,4);-- 修改员工编号为2,3,4的员工manager_id为1-- 查询每个有上级经理的雇员的经理的名字以及雇员的名字。select e1.ename 员工姓名 , e2.ename 经理名称 from emp e1,emp e2 where e1.manager_id=e2.eid
外连接(OUTER JOIN)
外连接接(左外连接,右外连接,全外连接(MySQL不支持 full join),交叉连接)
1)左外连接(left join)
left join 以left join左侧的表为主,左侧表的数据全部展现,右侧表只展现与左侧表相关联的数据。
select * from emp;
select * from dept;
insert into emp(ename,birthday,did,sal,manager_id) values('tianqi','1999-1-1',NULL,3000,2)
-- 查询员工的编号,姓名,薪水及其部门名称select eid,ename,sal,dname from emp left join dept on(emp.did=dept.did)
右外连接: right join 以right join右侧的表为主,右侧表的数据全部展现,左侧表只展现与右侧表相关联的数据。
select eid,ename,sal,dname from emp right join dept on(emp.did=dept.did)
UNION:可以将两个查询结果集合并,返回的行都是唯一的,如同对整个结果集合使用了 DISTINCT。相当于Oracle中的全外连接
(select eid,ename,sal,dname from emp left join dept on(emp.did=dept.did))
UNION
(select eid,ename,sal,dname from emp right join dept on(emp.did=dept.did))
UNION ALL:只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据, 那么返回的结果集就会包含重复的数据了。
(select eid,ename,sal,dname from emp left join dept on(emp.did=dept.did))
UNION ALL
(select eid,ename,sal,dname from emp right join dept on(emp.did=dept.did))
MySQL 数据分组
MySQL中创建数据分组 使用group by关键字
MySQL中约束分组结果 使用having关键字