2019-02-26 MySQL基础操作指令

数据库排名：
Oracle(关系型)   
MySQL(关系型)   
SQLServer(关系型)   
PostgreSQL(关系型)      
MongoDB(非关系数据库)      
DB2(关系型)     
Redis(非关系数据库)    
ElasticSearch(非关系数据库)     

行（记录）
列(字段)

关系型数据库：想

SQL语句(结构化查询语言)    
DDL(数据定义语言)： create / drop / alter   
DML(数据操作语言 - Data Manipulation Language): insert / delete / update / select  
DCL(数据控制语言): grant / revoke      

-- 注意: SQL中大小写不敏感（大写和小写是一样的）; 每条SQL语句必须以分号结束

-- 一.DDL - 主要提供数据库和表的创建、删除和修改  
-- 0. 删除数据库: drop database 数据库名;
DROP DATABASE school;   -- 直接删除指定数据库
DROP DATABASE if EXISTS school;   -- 如果指定的数据库存在就删除数据库

-- 1.创建数据库: create database 数据库名;
create database school;  -- 直接创建指定数据库
CREATE database if not EXISTS school;  -- 当指定数据库不存在的时候才创建数据库
create database if not EXISTS school default charset utf8;   -- 创建数据库的时候设置字符集编码方式为utf8,让数据库支持中文数据的存储

-- 2.使用/切换数据库: use 数据库名;
use school;

-- 3.新建表: create table if not exists 表名(字段名1 类型1, 字段2 类型2,...);
-- 注意： a. 表名一般需要加前缀't'或者'tb'    b.字段用来确定表中要存储哪些数据，字段名随便命名但是不能是关键字  c.数据类型必须是MySQL支持的数据类型
-- 常用数据类型: int-整数, char(size)-定长字符串, varchar(size)-不定长字符串, text-字符串, bit-布尔, date-日期
CREATE TABLE if not EXISTS t_student(stuid int, stuname varchar(20), gender bit, birth date);  

-- 新建表并且添加约束: create table if not exists 表名(字段名1 类型1 约束1, 字段2 类型2 约束2,...);
-- 常见约束: not null - 不为空,  default  - 设置默认值,  unique - 值唯一,  primary key - 主键约束 
-- 主键约束:  主键的值可以确定列表中唯一一条记录(通过一个主键值可以找到表中的唯一一条记录)
-- 注意: auto_increment只针对主键有效,并且主键的类型是整型;
CREATE TABLE if not EXISTS t_student
(
stuid int not null auto_increment, 
stuname varchar(20) not null, 
gender bit DEFAULT 1, 
birth date,
PRIMARY KEY(stuid)  -- 将字段stuid作为当前表的主键（设置主键可以间接约束这个字段的值是唯一的）
);
 

-- 4.删除表: DROP TABLE if EXISTS 表名;
DROP TABLE if EXISTS t_student;  
-- 清空表中的数据: TRUNCATE TABLE 表名;
TRUNCATE TABLE t_student;

-- 5.修改表
-- 5.1 添加列: alter TABLE 表名 add COLUMN 字段名 字段类型 约束;
alter TABLE t_student add COLUMN score FLOAT(8,2) DEFAULT 0;

-- 5.2 删除列: alter TABLE 表名 drop COLUMN 字段名;
alter TABLE t_student drop COLUMN gender;


-- 二、DML(数据操作语言) - 主要针对数据库中数据的增、删、改、查
-- 1.增(添加数据/记录)
-- 1.1插入数据/记录: insert into 表名 values(值1, 值2, 值3,....)  - 依次给指定表中的字段赋值
INSERT into t_student VALUES(100, '张三', 0, '2019-9-23');

-- 1.2插入数据/记录: insert into 表名(字段名1,字段名2,...) values(值1, 值2,...) -以指定的顺序给指定的字段赋值  
INSERT into t_student(stuname, birth) VALUES("小花", date(now()));   -- 一次插入一条记录

-- 一次插入多条记录
INSERT into t_student(stuname, birth) VALUES
("小花", date(now())),
('小明', '2018-9-8'),
('路飞', '1999-12-16'),
('佐助', '2000-10-12');

-- 值的问题: sql中是数字对应的值直接写，字符串需要使用引号引起来，bit类型的值只有0或者1, 时间可以用内容是满足时间格式字符串也可以是通过时间函数获取的值
-- 时间函数: now() - 当前时间  date(now()) - 当前日期   year(now()) - 当前年   month(now()) - 当前月 ....  

-- 2.删（删除数据/记录） 
-- delete from 表名;    - 删除指定表中所有记录
DELETE FROM t_student;

-- delete from 表名 where 条件语句;    - 删除满足条件的记录  
-- SQL中的条件语句: =(判断是否相等), <>(不等于,和python中的!=功能一样), >, <, >=, <=
DELETE FROM t_student WHERE stuid=100;     -- 删除t_student表中stuid的值等于100的记录  
DELETE FROM t_student WHERE stuname='小花';  -- 删除t_student表中stuname的值等于'小花'的记录
DELETE FROM t_student WHERE stuid<108;   -- 删除t_student表中stuid的值小于'小花'的记录  

-- 3.改(修改数据/记录) 
-- update 表名 set 字段1=新值1, 字段2=新值2,...;      - 将指定表中所有行的指定列/字段的值赋值为新值
UPDATE t_student set birth='1999-10-1', gender=1;

-- update 表名 set 字段1=新值1, 字段2=新值2,... where 条件语句;  -将表中满足条件的行中指定字段的值赋值为新值  
UPDATE t_student set gender=0 WHERE stuname='小花';

-- 通配符%: 表示任意个数的任意字符（包括0个）
UPDATE t_student set birth='2000-01-01' WHERE stuname LIKE '小%';    -- 修改stuname是以'小'开头的行对应的birth的值
UPDATE t_student set birth='2111-01-01' WHERE stuname LIKE '%小%';  
-- 统配符_: 表示一个任意字符
UPDATE t_student set birth='2444-01-01' WHERE stuname LIKE '小_';    --  修改stuname只有两个字符，并且第一个字符是‘小’对应的行的birth的值
-- 注意: 通配符只针对字符串有效!  


-- 4.查(获取数据)
-- 4.1直接查询
-- select * from 表名;    - 获取指定表中所有行和所有的列(所有数据)
SELECT * FROM t_student;

-- select 字段名1,字段名2,... from 表名;   - 获取指定表中所有行指定的列 
SELECT stuname,stuid FROM t_student;   

-- select * from 表名 where 条件;    - 获取指定表中所有满足条件的行所有列的数据  
SELECT * FROM t_student WHERE stuid>115;    

-- 4.2列重命名
-- select 字段1 as 新字段1, 字段2 as 新字段2,... from 表名;
-- 注意： 这儿的as可以省略
SELECT stuid as '学号', stuname, gender as '性别' FROM t_student;    -- 对查询结果中的stuid和gender字段进行重命名

-- 4.3对查询结果重新赋值(一般针对布尔数据)
-- select if(字段名,值1,值2) from 表名;    -查询指定字段，并且判断字段对应的值是0还是1，如果是1结果为值1，否则为值2
-- 注意: 这儿的if的用法是MySQL专有的
-- MySQL写法: if(字段, 新值1, 新值2)
SELECT stuname,if(gender,'男','女') as '性别' FROM t_student;    
-- 通用写法:  case 字段 when 值 then 新值1 else 新值2 end
SELECT case gender WHEN 1 THEN '男' ELSE '女' END as '性别' FROM t_student;

-- 4.4对列进行合并
-- select concat(字段1,字段2,...) from 表名;
SELECT CONCAT(stuname,stuid) as 'name_id' FROM t_student;
SELECT CONCAT(stuname,':',stuid) as 'name_id' FROM t_student;
-- 注意: 数字和字符串数据可以合并，bit类型的数据不可以合并
-- SELECT CONCAT(stuname,':',gender) as 'name_id' FROM t_student;   

-- 4.5模糊查询 - 查询的时候时候通过like条件来指定查询对象
-- sql中支持逻辑运算符and(逻辑与运算)和or(逻辑或运算)，not(逻辑非)
SELECT * FROM t_student WHERE stuname like '%飞%' or not stuid < 110;  

-- 4.6排序(先按之前的任何语法进行查询在排序)
-- select * from 表名 order by 字段;      - 对查询结果按照指定字段的值进行升序排序 
-- select * from 表名 order by 字段 asc;      - 对查询结果按照指定字段的值进行升序排序 
-- select * from 表名 order by 字段 desc;      - 对查询结果按照指定字段的值进行降序排序
SELECT * FROM t_student ORDER BY gender;    -- 按性别升序排序
SELECT * FROM t_student ORDER BY stuid ASC;  -- 按学号升序排序
SELECT * FROM t_student ORDER BY stuid DESC;  -- 按学号降序排序

-- 排序的时候可以通过在order by的后边加多个字段，进行联排。排序的时候前面的字段的优先级高些
SELECT * FROM t_student ORDER BY gender ASC, stuid DESC;   -- 先按性别进行升序排序，然后再按学号降序排序。  

-- 4.7限制 
-- select * from 表名 limit N;    -- 获取查询结果的前N条记录
SELECT * FROM t_student LIMIT 3;   -- 获取查询结果的前3条记录

-- select * from 表名 limit M offset N;   -- 对查询结果跳过前N条数据，取M条数据出来  
SELECT * FROM t_student LIMIT 3 offset 4;  -- 跳过前4数据，然后取3条数据
-- =================================sql基础补充===============================
-- 1.条件语句的写法
-- 在SQL中可以通过 `where 条件语句`来对操作对象进行筛选 - 筛选  
-- a. 比较运算符： =, <>, >, <, >=, <=
-- 注意: 判断一个字段的值是否为空不用使用=和<>, 而是使用`is null` 和 `is not null`
SELECT addr FROM t_student;
SELECT stuname FROM t_student WHERE addr is NULL;    -- 判断是否为NULL（空）
SELECT stuname FROM t_student WHERE addr='';     -- 判断是否是空串
-- b. 逻辑运算符: and, or, not  
-- c. where 字段名  between 值1 and 值2   --  筛选指定的字段的值在值1和值2之间
SELECT stuname,birth FROM t_student WHERE birth BETWEEN '1990-1-1' AND '1999-12-31';  
-- d. where 字段名 in 集合   -- 筛选出字段值是集合中的元素;(集合是使用括号括起来里面多个值)
SELECT * FROM t_student WHERE stuname in ('小花', '小明', '路飞');  
-- e. like操作 

-- 2.数据类型 
-- varchar(size): 不定长字符串，size决定的是最大长度
-- char(size): 定长字符 
-- text: 不限长度(最大是255个字符)
-- int/tinyint(-128~127)
-- FLOAT(size,d)/DOUBLE(size,d)  - 这儿的size和d的值都有约束效果
-- bit: 只有0和1两个值
-- date/datetime/time: 值可以是时间函数的结果，也可以时间字符串；计算或者是比较的时候内部是按时间处理的
UPDATE t_student SET tel='1367819230';
ALTER TABLE t_student ADD COLUMN intro VARCHAR(10);
SELECT * FROM t_student;  

-- 3.去重 
SELECT DISTINCT addr FROM t_student;  


-- ========================添加约束=========================
-- 1.创建表的时候添加约束
CREATE TABLE if not EXISTS t_college
(
collid int, 
collname VARCHAR(20) NOT NULL,   -- 创建表的时候添加约束
website VARCHAR(1024), 
intro VARCHAR(200),
PRIMARY KEY(collid)
);

-- 2.通过添加约束索引的方式添加约束 
-- a.添加约束
-- alter table 表名 add constraint 索引名 约束 (字段名);
-- 说明: 索引名 - 自己随便命名，用来指向当前添加的约束； 约束 - 需要添加的约束(支持唯一约束、主键约束和外键约束)

ALTER TABLE t_college ADD CONSTRAINT con_website UNIQUE (website);  
-- 给t_college表中的website添加unique约束，约束索引为con_website

-- b.删除约束
-- alter table 表名 drop index 约束索引;
ALTER TABLE t_college DROP index con_website;


-- ====================外键和E.R图================
-- 1.什么是外键：表中的某个字段的值是根据其他表中主键的值来确定的。那么这个字段就是外键  
-- 1.1 多对一的外键的添加： 将外键添加到多的一方对应的表中  
--     一对一的外键的添加： 将外键随便添加到哪一方，同时添加值唯一约束  
--     多对多的外键的添加： 关系型数据库中，两张表没法实现多多的关系，需要一个中间表。(中间表有两个外键分别参照多多的两个表的主键)
-- 1.2 怎么添加外键： 

-- a.添加外键对应的字段 
alter TABLE tb_student add COLUMN collid int;

-- b.给设计好的外键对应的字段添加外键约束
-- alter table 表1 add constraint 索引名 foreign key (字段1) references 表2 (字段2); 
--   - 将表1中的字段1设置为外键，并且让这个外键的值参照表2中的字段2
ALTER TABLE tb_student ADD CONSTRAINT fk_collid_stu 
FOREIGN KEY (collid) 
REFERENCES  tb_college (collid);

-- c. 删除外键约束  
-- alter table 表名 drop foreign key 外键索引名;    - 可以删除外键约束，但是外键索引还存在；需要额外的把索引删掉
-- 注意: 删除外键约束的时候直接删除约束的索引无效，必须先将约束删掉，然后再删除索引.
ALTER TABLE tb_student DROP FOREIGN KEY fk_collid_stu;
ALTER TABLE tb_student DROP INDEX fk_collid_stu; 

-- d. 多对多的外键约束
-- 添加学生的外键约束 
ALTER TABLE tb_score ADD CONSTRAINT fk_stuid_score 
FOREIGN KEY (stuid) 
REFERENCES tb_student (stuid);

-- 添加课程的外键约束
ALTER TABLE tb_score ADD CONSTRAINT fk_couid_score 
FOREIGN KEY (couid) 
REFERENCES tb_course (couid);


-- ==================查询的高级操作=================  
-- 1.聚合：max()/min()/sum()/avg()/count()
-- SELECT 聚合函数(字段) FROM  表名WHERE 条件;   -- 按条件多表查询指定字段数据，然后将查询结果做相应的聚合运算。聚合运算的结果是最后结果 
SELECT mark FROM tb_score;    -- 获取tb_score表中的所有分数值
SELECT max(mark) as maxmark FROM tb_score;      -- 获取tb_score表中所有分数的最大值
SELECT min(mark) FROM tb_score; 
SELECT sum(mark) FROM tb_score; 
SELECT avg(mark) FROM tb_score WHERE scoreid>=123;   -- 计算平均值的时候如果参与运算的对象的值为NULL,那么这个数据不会参与计算
SELECT count(mark) FROM tb_score WHERE mark>=90;


-- 2.分组  
-- SELECT 字段操作  FROM 表名 WHERE 条件 GROUP BY(字段2);    
--         将指定表中满足条件的记录按照字段2的进行分组(值是一样的在一个组里面), 然后再讲每个分组作为整体按照指定字段进行指定聚合操作
-- 求每个学生的平均成绩
SELECT stuid,avg(mark) FROM tb_score WHERE scoreid <= 115 GROUP BY(stuid) ;
-- 注意:a.字段操作的位置除了分组字段不用聚合，其他字段都必须聚合   b.分组的时候where要放到分组前对需要分组的数据进行筛选

-- having: 分组的时候，在分组后用having代替where来对分组后的数据进行筛选
-- 获取平均分数大于60分的学生的id
SELECT stuid,avg(mark) FROM tb_score GROUP BY(stuid) HAVING avg(mark)>60;

-- 3.子查询: 将一个查询操作的结果作为另外一个查询的数据源
-- 在tb_score表中获取成绩是大于90分的学生的id
SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL;   

-- 获取成绩大于90分的学生的名字
SELECT stuname FROM tb_student WHERE stuid in 
(SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL);  


SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is  not NULL;
-- 将一个查询的结果作为查询对象提供给另外一个查询。但是第一个查询结果需要重命名
SELECT mark FROM (SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is  not NULL) as t2;

-- ========================连接查询===============================
-- 1.连接查询：同时查询多个表中的数据
-- select * from 表名1,表名2... where 连接条件;
-- 连接查询如果不加连接条件，结果是笛卡尔积：(a,b,c)(1,2,3)——>(a1,a2,a3,b1,b2,b3,c1,c2,c3)

-- 获取老师姓名和其对应的学院名
select teaname,collname from tb_college,tb_teaher where tb_college.collid=tb_student.collid;

-- 获取学生姓名和其指定课程的分数
select stuname,couname,mark from tb_student,tb_course,tb_score where tb_student.stuid=tb_score.stuid and tb_course.couid=tb_score.couid;

-- 注意：如果连接查询的时候既有连接条件又有筛选条件，我们要把筛选条件写在连接条件后面
-- 查询平均成绩大于80分的学生姓名对应的学生的平均成绩
select stuname,avg_mark from tb_student,(select stuid,avg(mark) as avg_mark from tb_score group by(stuid)) as temp_t where tb_student.stuid = temp_t.stuid and avg_mark>80;

-- 2.内连接
-- select * from 表1 inner join 表2 on 表2的连接条件 inner join 表3 on 表3的连接条件 ...;
-- 注意：中间表写在最前面（存在关联其他表外键的表）
-- 查询学生姓名对应的学科名的分数
select stuname,couname,mark from tb_score inner join tb_student on tb_student.stuid=tb_score.stuid inner join tb_course on tb_course.couid=tb_score.couid where mark<60;
-- 内连接把满足条件的记录取出来
-- 3.外连接
-- 外连接分为左外连接、右外连接和全连接，但是在mysql中只支持左外连接和右外连接
-- 表1(左表) left/right/inner jion 表2(右表)
-- 左外连接：将左表中所有对应字段的所有数据取出，然后再查出对应的右表中对应字段的值，如果右表对应的值不存在结果为null
-- 右外连接：将右表中所有对应字段的所有数据取出，然后再查出对应的左表中对应字段的值，如果左表对应的值不存在结果为null
-- 查所有学生名对应的成绩
select stuname,mark from tb_score inner join tb_student on tb_student.stuid=tb_score.stuid;
select stuname,mark from tb_score left join tb_student on tb_student.stuid=tb_score.stuid;

-- ========================字段索引==============================
-- explain: 获取执行

-- 字段的索引就相当于目录，作用是为了能够快速的对这个字段进行查找
-- 添加索引的好处是可以大大的提高查询效率； 缺点：1.会消耗额外的存储空间 2.会让添加和删除的效率降低。
-- 建议：1. 索引不能滥用  2.如果项目中针对某个字段的查询很频繁，建议加个对应的索引
explain select * from tb_student where stuid=1;
explain select * from tb_student where stuname='张三';

-- 添加索引
-- create index 索引名称 on 表名 (字段名);  - 给指定表中的指定字段添加索引
create index index_stuname on tb_student (stuname);  -- 给名字加索引
create index index_stuname1 on tb_student (stuname(1));   -- 按姓加索引

-- 删除索引
alter table tb_student drop index index_stuname;


-- =========================DCL========================
-- 1.创建用户
-- create user 用户名@登录地址
-- 登录地址: (限制用户能够登录mysql的主机地址)，ip地址(指定地址)，localhost(数据库本机)，%(任何位置)
create user 'zhangsan'@'%' identified by 'aaaaaa'; 
-- 删除用户: drop user 用户名;
drop user 用户名;

-- 2.授权
-- grant 权限类型 on 数据库.对象 to 用户名;
grant select on *.* to 'zhangshan';
grant update on *.* to 'zhangshan';
grant all privileges on *.* to 'zhangshan';   -- 添加所有权限
grant all privileges on *.* to 'zhangshan' with grant option; -- 添加所有权限，并且能够将自己的权限再授权给其他用户

-- 3.召回授权
-- revoke 权限类型 on 数据库.对象 from 用户名;
revoke delete on school.* from 'zhangshan';

-- 4.事务
-- 完成一个任务需要执行多条sql，但是要求这多个操作当中只要有一个操作失败，整个任务就失败，数据全部还原，所有的操作都成功整个任务才成功的时候就使用事务。

-- 开启事务环境
begin;
update tb_student set stuname = '一一' where stuname='12';
delete from tb_student where stuid=110;

-- 提交事务(只有begin到commit之间的所有的sql都执行成功，才会执行commit；否则执行rollback)
commit;

-- 事务回滚(放弃begin到commit之间执行成功的所有sql语句的结果)
rollback;

-- 8.pyMySQL
1.建立连接
    host - 要连接的数据库所在的主机地址(远程连接用ip地址，本机使用'localhost')
    user - 用户名
    password - 密码
    database - 数据库
    port - 端口
    charset - 编码方式
    con_obj = pymysql.connect(host='47.101.0.09',
                              user='root',
                              password='aaaaa',
                              database='school',
                              port=3306,
                              charset='utf8',
                              autocommit=True
                              )
    print(con_obj)

    2.获得游标对象 - 提供数据库操作的上下文
    注意：游标提供的上下文是事务环境
    with con_obj.cursor() as cursor:
        在这后面操作数据库
        3.操作数据库，执行sql语句：连接对象.execute(sql语句)
        返回执行结果，如果是增删改操作，结果是否成功：1-成功，0-失败
        result = cursor.execute("update tb_teacher set teaage=25;")
        print(result)

        事务提交
        con_obj.commit()

    关闭连接：连接对象.close()
    con_obj.close()