数据库常用sql

纯属个人常用总结。待续。。。

将A表中某个字段的值赋值个B表的某个字段

A、B两张表通过字段ID关联，现通过A表中ID查B表等于该ID的B.XM的值，查到以后赋给A.KZXM字段

实现方式一：
update A set A.KZXM = (select B.xm from B where A.ID=B.ID )
以上处理方式，当
select B.xm from B where A.ID=B.ID
这句返回结果是多条记录时会报错：ORA-01427：单行子查询返回多个行，这种情况下，在以上sql中添加一个条件即可解决，添加完以后的sql
如下
实现方式二：
update A set A.KZXM = (select B.xm from B where A.ID=B.ID and rownum< 2 )

环境变量导入sql路径
C:\Program Files\MySQL\MySQL Server 5.7\bin

备份数据库
mysqldump -u root -p myschool> myschool20171209.sql

还原数据库
首先创建数据库 create database school;
使用数据库 use shool;
source d:\backup\myschool_20171209.sql;

在dos窗口查看数据库数据按照中文格式显示
set names gbk;

备份数据库中列的数据
select *from score Into outfile ‘d:\student.txt’;

文件导入到数据库中的表列
load data infile ‘d:\student.txt’ into table score;

DML数据操作语言
DDL数据定义语言
DQL数据查询语言
DCL数据控制语言

设置中文字符集
alter table 表名 charset utf-8;

修改表名
alter table 旧表名 rename 新表名

rename table 表名 to 新表名;
作用：修改表名
例如：
#5, 为分类表sort 改名成 category
RENAME TABLE sort TO category;

添加新字段
ALTER TABLE 表名 ADD 字段名 数据类型 [属性];
ALTER TABLE 表名 ADD 字段名 VARCHAR(50) NOT NULL COMMENT ‘宠物昵称’;

alter table 表名 modify 列名 类型(长度) 约束;
作用：修改表修改列的类型长度及约束.
例如：
#2, 为分类表的分类名称字段进行修改，类型varchar(50) 添加约束 not null
ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL;

alter table 表名 change 旧列名 新列名 类型(长度) 约束;
作用：修改表修改列名.
例如：
#3, 为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)
ALTER TABLE sort CHANGE sname snamename VARCHAR(30);

alter table 表名 drop 列名;
作用：修改表删除列.
例如：
#4, 删除分类表中snamename这列
ALTER TABLE sort DROP snamename;

添加主键约束
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段);
例：将grade表中的gradeId设置为主键：
ALTER TABLE grade ADD CONSTARINT pk_grade PRIMARY KEY grade（gradeId）;

添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段);
ALTER TABLE pet ADD CONSTRAINT fk_pet_master FOREIGN KEY (Master_Id) REFERENCES master(id);

无符号 unsigned
默认值 default
唯一 unique key 常用于身份证号

创建表格

CREATE TABLE IF NOT EXISTS student (
studentNo INT(4) NOT NULL COMMENT ‘学号’ PRIMARY KEY ,
loginPwd VARCHAR(20) NOT NULL COMMENT ‘密码’,
studentName VARCHAR(50) NOT NULL COMMENT ‘学生姓名’,
sex CHAR(2) DEFAULT ‘男’ NOT NULL COMMENT ‘性别’,
gradId INT(4) UNSIGNED COMMENT ‘联系电话’,
phone VARCHAR(50) COMMENT ‘联系电话’,
address VARCHAR(255) DEFAULT ‘地址详情’ COMMENT’地址’ ,
bornDate DATETIME COMMENT’出生日期’,
email VARCHAR(50) COMMENT ‘邮件账号’,
identityCard VARCHAR(18) UNIQUE KEY COMMENT’身份账号’
);

CREATE TABLE master(
id INT(10) AUTO_INCREMENT COMMENT ‘序号’ ,
name VARCHAR(20) COMMENT ‘姓名’,
password VARCHAR(20) COMMENT ‘密码’,
money INT(10) COMMENT’元宝数’,
PRIMARY KEY(id)
);

添加默认值
default ‘男’
default 10

默认为当前时间是 datetime DEFAULT NOW()

删除表
drop table 表名

查看系统所支持的引擎
show engines;

插入单行数据
INSERT INTO 表名 [(字段名列表)] VALUEES (值列表);
INSERT INTO student(loginPwd,studentName,gradId,phone,bornDate)VALUES(‘1jjjjjj666693’,‘张斌’,2,‘15895542574’,‘1994-5-8’);

插入多行数据
INSERT INTO 新表（字段名列表）VALUES(值列表1),(值列表2),…,(值列表n);
INSERT INTO subject（subjectName,classHour,gradeID）VALUES(Logic Java,220,1),(HTML,160,1),(Java OOP,230,2);

将查询结果插入到新表
CREATE TABLE 新表（SELECT 字段1，字段2，…FROM 原表）;
CREATE TABLE phoneList(SELECT studentName,phone FROM student);

删除表里的数据信息
delete [from] 表名 [where<删除条件>];
delete from student where studentName =‘张斌’;

truncate table student;
delete 一条一条删除，不清空auto_increment记录数。
truncate 直接将表删除，不能恢复数据即数据不能被回滚，重新建表，auto_increment将置为零，从新开始。删除效率比delete高，
有主外键关联的，需要先解除关系后才可以truncate，然后在增加主外键关联关系。
实际工作中，不建议使用truncate 删除数据

修改表数据的信息
UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件];
update student set sex= ‘女’ where studentName =‘黄小平’;
UPDATE sort SET sname =‘汽车美容’ , sprice= 25035 ,sdesc =‘汽车内容配件上涨’ WHERE sid=6;

查询部分的行以及附带条件语句
select loginPwd ,StudentName , sex, address, email from student where sex =‘男’;

查询行换为自己熟悉的别名
select studentName AS 学生姓名 from student;

查询行信息条件为空值 IS NULL IS NOT NULL
select loginPwd ,StudentName , sex, address from student where identityCard IS NULL;

在查询中使用常量列
select loginPwd ,StudentName , sex, address, email AS 邮箱地址 ,‘楚州中学’ AS 学校名称 from student where identityCard IS NULL;

对学员成绩进行取整
select CEIL(studentResult) from student;

创建表的信息时直接复制了另一个表的信息
CREATE TABLE IF NOT EXISTS student_grade(
SELECT studentName,sex ,bornDate ,phone FROM student
);

查询时条件并且用&&
select studentName ,address from student where gradID =2 AND sex =‘女’;
select studentName ,address from student where gradID =2&&sex =‘女’;

日期大于1990年的学生姓名student表中使用到YEAR函数

select studentName from student where YEAR(bornDate)>1990;

order by 是升序排序

asc 是ascend 升序的意思
DESC 是descend 降序意思

查询去重 distinct
SELECT DISTINCT 列名称 FROM 表名称

查询出金额在2000-5000之间的账务信息
SELECT * FROM zhangwu WHERE money >=2000 AND money <=5000;
或
SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 5000;

查询结果升序搜索结果中只显示4条 limit 4
select studentName , address from student where studentNo >10000 order by studentNo limit 4;

select <字段名列表>
FROM <表名或试图>
[where<查询条件>]
[group by <分组的字段名>]
[having]
[order by <排序的列名>[asc或desc]]
[limit[位置偏移量，]行数];

位置偏移量： （当前页码 - 1）* 行数；

查询结果升序搜索结果中只显示4条从第2条开始(注意下标从0开始) limit 2 ,4

实现子查询
select studentNo , loginPwd , studentName,sex ,gradId, phone,address, bornDate from student where bornDate>( select bornDate from student where studentName=‘李斯文’ );

两个表的关联信息
SELECT price, sid , hTID , sys_user.uName FROM hos_house,sys_user WHERE hos_house.UID= sys_user.UID;

建立组合索引
CREATE INDEX index_name_gradId ON student (studentName,gradId)

删除索引

DROP INDEX index_name_gradId ON student

查看索引
SHOW INDEX FROM student

删除表的信息
delete from zhangwu; 表中di类还是自增

但是使用了
begin;
truncate table zhangwu;
commit;

常用函数
1.聚合函数
AVG() 返回某字段的平均值 SELECT AVG (studentResult)FROM result;
COUNT() 返回某字段的行数
MAX() 返回某字段的最大值
MIN()返回某字段的最小值
SUM() 返回某字段的和 SELECT SUM (studentResult)FROM result;

2.字符串函数
CONCAT() 字符串的拼接
INSERT()
LOWER()
UPPER()
SUBSTRING()

3.时间日期函数
CURDATE() 获取当前日期 SELECT CURDATE(); 返回：2016-08-08
CURTIME() 获取当前时间
NOW() 获取当前日期和时间
WEEK(date) 返回日期date为一年的的第几周
YEAR(date) 返回年份
HOUR(time) 返回小时
MINUTE(time) 返回分钟
DATEDIFF(date1,date2) 返回date1和date2之间的天数
ADDDATE(date,n) 计算日期date加上n天后的日期

4.数学函数
CEIL(x) 返回大于或等于数值x的最小整数
FLOOR(x)返回小于或等于数值x的最大整数
RAND() 返回0~1间的随机数