/*
多行文本注释
数据类型
char(n) 长度为n的字符 用于存储定长的字符串
n=5,但是只存储了2个字符,但是仍然占有5个字符空间
varchar(n) 最大长度为n的字符 用于存储可变长度的字符串
n=5,只存储了2个字符,只占2个空间
date 日期
datetime 时间
int
float
double(建议使用)
text(用于存储长文本内容)被删掉
*/
/*
数据(结构)定义语言DDL
*/
#对数据库进进行操作
CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8
CREATE DATABASE IF NOT EXISTS masqla CHARSET utf8
DROP DATABASE IF EXISTS masqla -- 删除数据库
ALTER DATABASE schooldb CHARSET gbk -- 改表放入编码
/*
约束: 在数据库中,必须有一列用来做主键,表示唯一记录
主键不能为空,不能重复,一张表里只能有一列做为主键
PRIMARY KEY 添加主键约束 不能为空,不能重复
AUTO_INCREMENT 设置自动增加
NOT NULL 不能为空约束
UNIQUE 唯一约束 可以在一张表多列添加
CHECK(height<3) 检查约束
外键约束 多表关联时用
*/
#对表进行操作
CREATE TABLE student(
num INT(10) PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
NAME VARCHAR(5) NOT NULL COMMENT '姓名',
gender CHAR(1) DEFAULT '男' COMMENT '性别',
birthday DATE;
height DOUBLE(3,2) CHECK(height<3),
phone CHAR(11) NOT NULL UNIQUE,
address VARCHAR(30),
reg_time DATETIME
)
SELECT *FROM student;
-- 删除表
DROP TABLE IF EXISTS student
DROP TABLE IF EXISTS stu
-- 修改表名
RENAME TABLE stu TO student
-- 复制表结构,指定表为模板,创建一个新表,只有结构,没有数据
CREATE TABLE stu LIKE student
/*
DML语句
数据操纵语言DML
*/
-- insert插入语句
INSERT INTO stu(num,NAME,height,phone) VALUE(1,'张三',2,5);
INSERT INTO student SET NAME='李四',gender='男',phone='22',reg_time = NOW;
INSERT INTO student(NAME,gender,height,phone,address,reg_time)
VALUES('王五','男',2,'2222222','陕西汉中',NOW()),
('王五','男',2,'2222','陕西汉中',NOW()),
('王五','男',2,'222','陕西汉中',NOW());
INSERT INTO stu(NAME,gender,phone) SELECT NAME,gender,phone FROM student
-- 删除指定数据
delect from 表名(自增不归零,属于DML操作)
truncate table 表名 (自增归零,属于DDL操作,使得表结构重置)
-- 修改表内容
update 表名 set 列名 = 更改内容,列名 = 更改内容,列名 = 更改内容
/*
select 结果 from 表名 where order by limit group by
*/
-- 查询指定的列 建议的
SELECT num,NAME,gender FROM student
-- * 表示所有的列
SELECT * FROM student
-- 算术运算 +只能作为算术运算,不能拼接字符串
SELECT height+1 FROM student
SELECT CONCAT(NAME,":",gender) FROM student
-- DISTINCT 去除重复数据(所有的列都相同,才是重复数据)
SELECT DISTINCT gender,birthday FROM student
/*
单行函数: 对每行数据进行处理,有多少条数据,操作完还是多少条
分组函数: 把多行数据,处理后变为一个,总数,求和,最大值,最小值,平均值
length():获取参数值的字节个数
char_length()获取参数值的字符个数
concat(str1,str2,.....):拼接字符串
upper()/lower():将字符串变成大写/小写
substring(str,pos,length):截取字符串 位置从1开始
instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
replace(str,old,new):替换,替换所有的子串
*/
SELECT LENGTH(NAME) FROM student
SELECT num,NAME,CHAR_LENGTH(NAME)AS name_length FROM student
SELECT CONCAT(num,":",NAME)AS NAME FROM student
SELECT UPPER(NAME) FROM student
SELECT SUBSTRING(address,1,3) FROM student
SELECT INSTR(address,"西") FROM student
SELECT TRIM(NAME) FROM student -- 去除name列里边的空格
SELECT TRIM('陕' FROM address) FROM student
SELECT LPAD(address,10,"ab") FROM student -- yongab填充 加上原字符共10位
SELECT REPLACE(address,"陕","山") FROM student
/*
逻辑处理
*/
SELECT
num,
NAME,
(CASE WHEN state=0 THEN '未注册' ELSE '已注册' END) AS state
FROM student
SELECT num,NAME,IFNULL(address,'暂未登记') FROM student
SELECT num,NAME,IF(state=0,'未注册','已注册') FROM student
/*
round(数值):四舍五入
ceil(数值):向上取整,返回>=该参数的最小整数
floor(数值):向下取整,返回<=该参数的最大整数
truncate(数值,保留小数的位数):截断,小数点后截断到几位
mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
rand():获取随机数,返回0-1之间的小数
*/
SELECT ROUND(height) FROM student
SELECT CEIL(height) FROM student
SELECT FLOOR(height) FROM student
SELECT TRUNCATE(height,1) FROM student
SELECT MOD(10,3) FROM student
SELECT RAND() FROM student
/*
日期函数
*/
SELECT NOW() FROM student
SELECT CURDATE() FROM student //2002-12-14
SELECT CURTIME() FROM student
SELECT YEAR(birthday) FROM student
SELECT MONTH(birthday) FROM student
SELECT STR_TO_DATE("2002-3-4","%Y-%m-%d") FROM student
SELECT DATE_FORMAT(birthday,"%Y年%m月%d日") FROM student
SELECT DATEDIFF(CURDATE(),birthday) FROM student
-- 统计数量
## SELECT COUNT(*) FROM student
SELECT MAX(height) FROM student
SELECT MIN(height) FROM student
-- sum 求和 avg求平均值 只能对数值类型操作
SELECT SUM(height) FROM student
注意 : sum(if(;列名=条件,1,0)) 由此来判断符合条件的个数
SELECT AVG(height) FROM student
/*
select 结果列 from 表 where 条件
*/
SELECT * FROM student
SELECT * FROM student WHERE gender = '男'
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '男'
SELECT * FROM student WHERE gender = '男' AND height >1.70;
SELECT * FROM student WHERE gender = '男' OR height >1.70;
SELECT * FROM student WHERE gender = '男' OR height >1.70;
SELECT * FROM student WHERE height = 1.75 OR height = 1.78;
SELECT * FROM student WHERE height IN(1.75,1.78,1.98);
SELECT * FROM student WHERE height NOT IN(1.75,1.78,1.98);
SELECT * FROM student WHERE address IS NULL
SELECT * FROM student WHERE address IS NOT NULL
SELECT * FROM student WHERE height >=1.70 AND height<=1.90
SELECT * FROM student WHERE height BETWEEN 1.70 AND 1.98
SELECT * FROM student WHERE NAME LIKE '张%' --- 开头是张的
SELECT * FROM student WHERE NAME LIKE '%三%'--- 中间是三的
SELECT * FROM student WHERE NAME LIKE '张__' --- 张某某 一个_一个字
--union 合并查询结果
union --- 合并两次查询结果 (会将重复的去掉)
SELECT * FROM student WHERE NAME LIKE '%三%'
SELECT * FROM student WHERE NAME LIKE '张__'
union all --- 合并两次查询结果 (会重复,两次结果加一起 )
SELECT * FROM student WHERE NAME LIKE '%三
-- order by 排序 asc/desc
select * from student order by reg_time --默认升序
select * from student order by reg_time dsce--降序
select * from student where 条件 order by reg_time --默认升序
--多列排序
select * from student order by reg_time desc,birthday asc --x先reg_time排序,重复后再排
-- 有条件的排序 先条件过滤 而后排序
SELECT * FROM student WHERE num>1 ORDER BY reg_time DESC
--limit 分页
select * from student limit 0,3
select * from student order by reg_time desc,birthday asc
-- group by 分组的列 用于统计 统计男生女生各自的人数 结果就两条记录
-- 分组 把相同的数据,划分到一个组中处理
SELECT gender,COUNT(*) FROM student GROUP BY gender
SELECT IF(state=0,'未注册','已注册'),COUNT(*) FROM student GROUP BY state
-- 男生和女生谁的人数大于3
-- 分组求和展示指定组的内容
方式1: select * from (select gender,COUNT(*) AS a from student GROUP BY gender)as t where t.a>3
方式2: SELECT gender,COUNT(*)AS a FROM student GROUP BY gender HAVING a>3
-- 给指定表名添加一列
alter table 表名 add 列名 列名数据类型
-- 添加外键 约束
alter table 表名 add constraint fk_主表_从表_外键列名 foreign key(外键) reference 主表(主键)
添加外键约束
sql查询规范
联表查询
DQL 自连接
分页limit 与 排序 order by
ASC是将结果从最低值排序到最高值,而DESC则是将结果集从最高排序到最低值。
子查询
常用函数
聚合函数和分组过滤
MD5加密
事务 要么同时成功,要么同时失败
添加索引 : 帮助sql高效获取数据
用户权限操作
三大范式
DJBC
查询用statement.executeQuery(sql)
增删改用 statement.executeUpdate(sql)