数据库分类
关系型数据库 (SQL)
MySQL Oracle SqlServer DB2 SQLite 通过表和表之间 行和列之间的关系进行数据的存储 学员信息表 考勤表…
非关系型数据库(noSQL)
Redis mongDB 非关系型数据库 对象存储 通过对象的自身的属性来决定
数值
字符串
时间日期
DDL 语句创建数据库表
- 注意点 使用英文() 表的名称 和 字段 尽量使用`括起来`
- 字符串使用 单引号括起来
- PRIMARY KEY 主键
CREATE TABLE student( id INT UNSIGNED PRIMARY KEY, name VARCHAR(20) NOT NULL, sex CHAR(1) NOT NULL, bir DATE NOT NULL, tel CHAR(20) NOT NULL, remark VARCHAR(200) );
CREATE TABLE [IF NOT EXISTS]`表名`( `字段名` 列的类型[属性] [索引] [注释], `字段名` 列的类型[属性] [索引] [注释], ...... . `字段名` 列的类型[属性] [索引] [注释] )[表类型][字符集设置][注释]
SHOW CREATE DATABASE school -- 查看创建数据库的数据 SHOW CREATE TABLE student -- 查看student数据表的定义语句 DESC student -- 显示表的结构
DML语句数据操作
插入
语法: insert into 表名(字段一 字段二....) values("值1","值2","值3"........) INSERT INTO student VALUES(3,"HQH","男","2692-5-2","1303157","贺启衡"); 子查询 INSERT INTO student (name,ege,id) VALUES("HQH","10", (SELECT id FORM class WHERE name="1611") ); IGNORE INSERT IGNORE INTO student VALUES(3,"HQH","男","2692-5-2","1303157","贺启衡"); 插入不存在
主键自增
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
修改
update 修改谁 (条件) set 要修改字段名=新值
修改学员的名字
UPDATE `student` SET `name`='hqh' WHERE id =1;
不指定条件的情况下 会改动所有表
UPDATE `student` SET `name`='林宏程'
语法
UPDATE `表名` SET `字段`='新值' WHERE 条件; 条件 where 子句 运算符 id等于某个值 大于某个值 在某个区间内修改
UPDATE 表1,表2 SET 字段1=值1 WHERE
不重复 NOT NULL UNIQUE
CREATE TABLE student( name VARCHAR(20) NOT NULL, id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, sex ENUM("男","女") NOT NULL, tel CHAR(20) NOT NULL UNIQUE, FOREIGN KEY (id) REFERENCES CLASS (id) );
操作符
= 等于 5=6 false !=不等于 5=!6 true > 大于 < 小于 <= 小于等于 >= 大于等于 between X and Y 闭合区间 between 2 and 5 [2,5]
删除
delete命令
语法 delete from 表名 where 条件
删除全部数据
DELETE FROM "student"
删除数据
DELETE IGNORE FROM "student" WHERE id=1
WEHER
ORDEY BY //排序
LIMIT;
TRUNCATE TABLE 表名 一次一张表
DQL语句查询数据 data query language
创建学生表
CREATE TABLE student( name VARCHAR(20) NOT NULL, id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, sex ENUM("男","女") NOT NULL, tel CHAR(20) NOT NULL UNIQUE, FOREIGN KEY (id) REFERENCES CLASS (id) );
创建班级表
CREATE TABLE class( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10) NOT NULL UNIQUE, tel CHAR(20) NOT NULL UNIQUE );
创建老师表
CREATE TABLE teacher( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, sex CHAR(1) NOT NULL, tel CHAR(20) NOT NULL UNIQUE );
创建mes表 索引
CREATE TABLE mes(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type ENUM("公告","通知","处罚") NOT NULL,
content VARCHAR(200) NOT NULL,
creattime TIMESTAMP NOT NULL,
INDEX idx (type)
);
删除 DROP INDEX idx ON mes;
添加 SHOW INDEX FROM mes;
ALTER TABLE mes ADD INDEX IDX(type);
SELECT查询
查询所有字段
SELECT * FROM student
查询指定字段
SELECT `loginpwd`,`studentname` FROM student
起名 给结果起名字 as
SELECT `loginpwd` AS 学生学号,`studentname` AS 学生名字 FROM student AS s
-- 函数 concat(a,b)
SELECT CONCAT('姓名:',studentName)AS 新名字 FROM student
去重distinct
作用:去除select查询出来的结构中重复的数据 只显示一条
查询那些同学参加了考试 SELECT `studentno` FROM `result` 发现重复数据 SELECT DISTINCT `studentno` FROM `result` 数据库的列(表达式) SELECT VERSION() -- 查询系统版本 (函数) SELECT 100*3-1 AS 计算结果 -- 计算 (表达式) SELECT @@auto_increment_increment -- 查询自增的步长(变量)
LIMT 用法 分页 limit 0,10 limit 显示的第几个数据,每页显示几个数据
IFNULL 用法 判断是否 null
ORDER BY 用法 排序 /desc
where条件子句
作用:检索数据中 符合条件 的值
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
not ! not a !a 逻辑非
and && SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=95 AND `studentresult`<=100 SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=95 && `studentresult`<=100 SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult` BETWEEN 95 AND 100 ! not SELECT `studentno`,`studentresult` FROM `result` WHERE studentno!=1000; SELECT `studentno`,`studentresult` FROM `result` WHERE NOT studentno=1000;
模糊查询
is null a is null 如果操作符为null 结果为真
is not null a is not null 如果操作符为 not null 结果为真
between a between b and c 若a 在 b和c之间 结果为真
like(配合 %或_) a like b sql匹配 如果a能匹配到b 结果为真
in a in (a1,a2,a3) 假设a在a1 a2 其中 那么结果为真
查询 姓张的同学
like 结合%(代表0到任意个字符)_(一个字符)
SELECT `studentno`,`studentname` FROM`student`
WHERE studentname LIKE '张%'
SELECT `studentno`,`studentname` FROM`student`
WHERE studentname LIKE '张_'
查询 名字中有伟的同学
SELECT `studentno`,`studentname` FROM`student`
WHERE studentname LIKE '%伟%'
in
查询1000 1001 1003号学生
SELECT `studentno`,`studentname` FROM`student`
WHERE studentno IN (1000,1001,1002)
null not null
查询地址为空的学生
SELECT `studentno`,`studentname` FROM`student`
WHERE address IS NULL AND address=''
嵌套查询
where(这个值是计算出来的)
本质 在where 语句中嵌套一个子查询语句
where (select * from)
聚合函数
求最大 小 平均值
Sum: 数字 Max min :非空最大 小值 Avg :数字平均值 Count : count(*) 包含空值记录数 Count(列名) 包含非空记录数 SELECT COUNT(studentname) FROM student -- count(指定列) 会忽略所有的null值 SELECT COUNT(*) FROM student -- count(*) 不会忽略所有的null值 本质计算行数 SELECT COUNT(1) FROM result -- count(1) 不会忽略所有的null值 本质计算行数
常用函数
数学函数 SELECT SQRT(8) -- 开平方 SELECT PI() SELECT ABS(-8) -- 绝对值 SELECT CEIL(9.4) -- 向上取整 四舍五入 SELECT FLOOR(9.1) -- 向下取整 年龄 SELECT RAND() -- 返回0~1的随机数 SELECT SIGN(-10) -- 判断一个数的符号 负数返回-1 正数返回1 字符串函数 SELECT CHAR_LENGTH('字符串长度') -- 字符串长度 一个汉字三个字节 SELECT CONCAT('我','爱','你') -- 拼接字符串 SELECT INSERT('我喜欢编程',1,1,'你是不是') -- 插入替换 从某个位置开始替换某个长度 SELECT LOWER('AAAA') -- 转小写 SELECT UPPER('aaaa') -- 转大写 SELECT INSTR('222222122222','1') -- 返回第一次出现的字符索引 SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串 SELECT SUBSTR('坚持就能成功',3,6) -- 截取 开始位置到结束位置 SELECT REVERSE('两极反转') -- 反转字符 SELECT RPAD(SUBSTRING("贺启衡",1,1),LENGTH("贺启衡")/3,"*"); 时间和日期函数(常用) SELECT CURRENT_DATE() -- 获取当前日期 SELECT NOW() -- 获取现在时间 SELECT LOCALTIME() -- 本地时间 SELECT SYSDATE() -- 系统时间 SELECT YEAR(NOW()) SELECT MONTH(NOW()) SELECT DAY(NOW()) SELECT HOUR(NOW()) SELECT MINUTE(NOW()) SELECT SECOND(NOW()) SELECT DATE_FORMAT("2002-05-07","%W") SELECT DATE_ADD(NOW(),INTERVAL 15 DAY) 偏移 SELECT DATEDIFF("2014-2-8","2012-2-7") 差多少天 系统 SELECT SYSTEM_USER() SELECT USER() SELECT VERSION() 条件判断 SELECT IF(条件表达式,值一,值二); SET SAL=( CASE WHEN 表达式 THEN 值一 WHEN 表达式 THEN 值二 WHEN 表达式 THEN 值三 ELSE 值N AS XX END );
分组查询:
GROUP BY :分组
WITH ROLLUP :再次分组
GROUP_CONCAT(列名):以逗号聚合
执行顺序
HAVING语句
原因 :WHERE 先于 GROUP ,聚合函数 与具体内容比较 无法使用
(表连接:聚合函数 与字段比较)
表连接
----------------------联表查询------------------------ 查询参加了考试的同学(学号 姓名 科目编号 分数) /* 思路 1.分析需求 分析差的字段来自哪些表 2.确定使用哪种连接查询 确定交叉点(两个表中相同的字段) 判断的条件:学生表中的studentNO=成绩表 studentNO */ inner join SELECT s.studentNo,studentName,subjectno,studentresult FROM student AS s INNER JOIN result AS r WHERE s.studentno=r.studentno
左链接与右链接:
保留左边的 右边没有的为null
用on 外连接不会被筛掉 ;WHERE 不会
right join SELECT s.studentNo,studentName,subjectno,studentresult FROM student s RIGHT JOIN result r ON s.studentNo=r.studentNo LEFT JOIN SELECT s.studentNo,studentName,subjectno,studentresult FROM student s LEFT JOIN result r ON s.studentNo=r.studentNo
自连接
事务机制
事务原则 ACID原则
1.原子性(Atomicity) 要么都成功 要么都失败 2.一致性(Consistency) 事务前后的数据完整性要保持一致 3.隔离性(Isolation) 多个用户同时操作不会互相影响 4.持久性(Durability) 事务一旦提交则不可逆 被持久化到数据库中
执行事务
==================事务======================== mysql是自动开启事务的 SET autocommit=0 -- 关闭 SET autocommit=1 -- 开启(默认的) 手动处理事务 SET autocommit=0 -- 关闭自动提交
事务开启 START TRANSACTION ;-- 标记一个事务开始 后的sql都在同一个事务内 COMMIT;提交 持久化(成功) ROLLBACK ;回滚 回到原来的样子(失败!)
事务结束
SET autocommit=1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点 了解即可
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点
模拟场景
转账 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 买票 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 买东西商家改价 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;默认 SELECT FORM 必须结束前面的 很少使用 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
数据导出导入
导出成 sql 或文件
在想要导出的表或者库中 右键 选择备份或者导出 使用命令行导出 mysqldump 命令行使用 #mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名 导出一个表 mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql #mysqldump -h 主机 -u 用户名 -p 密码 数据库名 表名1 表名2 >物理磁盘位置/文件名 导出多个表 mysqldump -hlocalhost -uroot -p123456 school student result >D:a.sql #mysqldump -h 主机 -u 用户名 -p 密码 数据库名 >物理磁盘位置/文件名 导出一个数据库 mysqldump -hlocalhost -uroot -p123456 school >D:a.sql
导入
#登入的情况下 切换到指定的数据库 #source 备份文件 use xxx source >D:a.sql mysql -u用户名 -p密码 库名< 备份文件
Comments NOTHING