mysql

-- sql:Structured Query Language --结构化查询语言 -不区分大小写
-- 1.DDL : Data Definition Language -数据定义语言 --CREATE/DROP/ALTER
-- 2.DML:Data Manipulation Language -数据操作语言 -- -- INSERT/SELECT/DELETE/UPDATE
-- 3. DCL: Data Control Language -- 数据控制语言 --- grant/REVOKE/COMMIT/ROLLBACK
-- drop DATABASE IF EXISTS school; 如果存在名为school 的数据库就删除
-- 一行是一条记录，一列是一个字段

-- 设置字符集为utf8
CREATE database school DEFAULT CHARSET utf8;

-- 切换到school数据库
use school;

-- 删除表
DROP TABLE if EXISTS tb_student

-- 创建学生表 tb_student
CREATE TABLE tb_student
(
stuid int NOT NULL COMMENT '学号',
stuname VARCHAR(20) NOT NULL COMMENT '姓名',
stusex enum('男','女') DEFAULT '男' COMMENT '性别',
stubirth date COMMENT '生日',
stuaddr VARCHAR(255) COMMENT '家庭住址',
PRIMARY KEY (stuid)
);

-- 修改学生表
ALTER TABLE tb_student add column stutel char(11) COMMENT '联系电话';
-- 删除一列
ALTER TABLE tb_student DROP COLUMN stutel;
-- 修改tb_student表的表结构修改一个列
ALTER TABLE tb_student CHANGE COLUMN stuname stuname VARCHAR(20) NOT NULL;

use school;
CREATE TABLE tb_course(
cid int NOT NULL COMMENT '课程编号',
cname VARCHAR(31) not NULL COMMENT '课程名称',
credit TINYINT not NULL COMMENT '学分',
cdate TIMESTAMP DEFAULT now() COMMENT '开课日期',
PRIMARY KEY (cid)
);

select time(now());

INSERT into tb_student VALUE(1001,'报错','男','1980-1-1','四川成都');

INSERT INTO tb_student (stuid,stuname,stubirth) VALUES (1002,'王大锤','1990-2-2');

INSERT INTO tb_student (stuid,stuname,stubirth)
VALUES (1003,'远方','1990-2-2'),
(1004,'张三','1990-2-3'),
(1005,'李四','1990-2-4'),
(1006,'王五','1990-2-5');

INSERT INTO tb_student ()
VALUES (1008,'远方','男','1990-2-2','添加'),
(1009,'张三','女','1990-2-3','天涯'),
(1010,'李四','女','1990-2-3','还叫'),
(1011,'李四','女','1990-2-6','哈哈'),
(1012,'李四','男','1990-2-7','美国'),
(1013,'王五','女','1990-2-8','每周');

CREATE DATABASE td_course;
drop DATABASE tb_course;

-- 删除记录
DELETE FROM tb_student WHERE stuid=1011;
DELETE FROM tb_student WHERE stuid>1009;
DELETE FROM tb_student WHERE stuid BETWEEN 1006 and 1008;

-- 删全表
TRUNCATE table tb_student;

-- 更新
UPDATE tb_student set stubirth='2018-2-1' WHERE stuname='张三';
update tb_student set stubirth='1999-9-9', stuaddr='大山' where stuid=1001;

insert into tb_course
(cid, cname, credit)
values
(1111, 'Python程序设计', 3),
(2222, 'Web前端开发', 2),
(3333, 'Linux系统入门', 2),
(4444, '数据库理论和实践', 3),
(5555, '企业应用架构', 2);

UPDATE tb_course set credit=4 where cid in(2222,3333);
UPDATE tb_course set credit=4 where cid=2222 or cid=3333;

SELECT * from tb_student;
SELECT * from tb_course;

-- 投影
SELECT stuid,stuname,stusex FROM tb_student;

-- 别名 as可以省略
SELECT stuid as 学号,stuname as 姓名 FROM tb_student;

-- 筛选
SELECT stuid,stuname,stusex FROM tb_student WHERE stusex='女';

SELECT stuid,stuname,stusex,stubirth FROM tb_student WHERE stubirth BETWEEN '2000-1-1' and '2020-1-1';

-- 判断空值不能用=或<>,需要使用is 或 is not;
SELECT * FROM tb_student WHERE stuaddr is NOT null;

-- 模糊，姓张
SELECT *FROM tb_student where stuname LIKE '张%';
-- 精确到一个字符
SELECT *FROM tb_student where stuname LIKE '张_';
SELECT *FROM tb_student where stuname LIKE '%哥';
-- 查询带张zi的
SELECT *FROM tb_student where stuname LIKE '%张%';

-- 查询结果排序
SELECT * FROM tb_student ORDER BY stuid DESC;
SELECT * FROM tb_student ORDER BY stuname ASC;

SELECT * FROM tb_student ORDER BY stusex DESC,stuid desc;

-- 查看学号最大的三个学号
SELECT * FROM tb_student ORDER BY stuid DESC LIMIT 3;
SELECT * FROM tb_student ORDER BY stuid DESC LIMIT 0，3;
-- 跳过五条看三条 分页查询
SELECT * FROM tb_student ORDER BY stuid DESC LIMIT 3 OFFSET 5;
SELECT * FROM tb_student ORDER BY stuid DESC LIMIT 5，3;

-- 查名字
SELECT distinct SUBSTR(stuname,1,1) FROM tb_student;
SELECT SUBSTR(stuname,2,LENGTH(stuname)) FROM tb_student;

-- 聚合函数查询
SELECT max(credit) from tb_course;
SELECT max(stubirth) from tb_student;
SELECT min(stubirth) from tb_student;

SELECT sum(credit) from tb_course;
SELECT avg(credit) from tb_course;

SELECT count(stuid) FROM tb_student;
SELECT count(cid) FROM tb_course;

-- 分组查询
SELECT stusex,count(stuid) from tb_student GROUP BY stusex;

-- 创建用户
CREATE user 'hellokitty'@'localhost' IDENTIFIED by '123456';
CREATE user 'hellokitty'@'%' identified by '123456';

-- 授权操作
-- 授权一个超级管理员
grant all privileges on . to 'hellokitty'@'%' with grant option;
-- 召回权限
REVOKE all PRIVILEGES on . FROM 'hellokitty'@'%';

grant select on school.* to 'hellokitty'@'%';

-- 一对一 （人和身份证、用户和购物车）
-- 一对多 （人和银行卡，用户和订单）
-- 多对多 （学生与课程、订单和商品）

-- 外键:外来的主键（在其他表示主键）

-- 右表驱动，小表放右边
-- 事务（transaction）：多个操作不可分割要么全成功要么全失败

--开启事务环境
begin；/start transaction

---

---

--提交事务
commit;

--回滚事务
rollback;

事务的ACID特性：
原子性（Atomicity），:操作不可分割
一致性(Consistency)，：事务前后数据状态要一致
隔离性(Isolation)，：多个并发事务不能看到彼此的中间状态
持久性(Duration)，：事务完成后数据要持久化

关系型数据库如何保证数据的完整性：
1.实体完整性 --每一条记录都是独一无二的没有冗余数据 -主键/唯一索引 （unique，primary）
2.参照完整性/引用完整性 --外键
3.领域完整性 -- 没有无效的数据--数据类型/非空约束/默认值约束/ 检查约束（mysql不支持）

软件和硬件是等效的
时间和空间是矛盾体

表的索引相当于一本书的目录，他可以加速查询，但是索引会让增删改变得更慢，
因为增删改数据时索引也需要更新，所以索引要建立在经常查询的字段上