Python-数据库 基本SQL语句

1. 数据库是什么
   2. MySQL安装
   3. 用户授权
   4.
      数据库操作
         -
      数据表
         - 数据类型
         - 是否可以为空
         - 自增
         - 主键
         - 外键
         - 唯一索引

      数据行
         增
         删
         改
         查
            排序： order by desc/asc
            分组：group by
            条件：where
            连表：
               left join
               right join
               inner join
            临时表：
            通配符
            分页：limit
            组合：
               union
      视图（虚拟）
      触发器
      函数 select xx(f)
      存储过程
         - 游标
         - 事务
         - 结果集+ “返回值”
      pymysql
         - 连接 connect(...)
         - 操作（游标）
            - 增删改 -> commit
            - 查     -> fetchone,fetchall
            - SQL注入
            - 调用存储过程：
               callproc('p1',参数)
               select @_存储过程名称_0
         - 关闭游标
         - 关闭连接



===============================
#我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），
    于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，
    但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，
    总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。

1.什么是数据库 *****
    存放数据的仓库

    数据库中包含所有内容:
    字段(列)  一行中的某个数据
    记录  一行数据
    表   文件
    库  文件夹
    DBMS  数据库管理系统  socket软件
    数据库服务器 计算机
    一个库可以包含多个表  一个表可以包含多条记录  一条记录中有多个字段

    数据库是什么,本质上就是一套C/S架构得socket软件

  已学习的文件操作的缺陷
        1.IO操作 效率问题
        2.多用户竞争数据
        3.网络访问
        4.用户的验证
2.常见的数据库
    关系型 数据之间存在某种关联关系
            主要存储在硬盘中  容量大 速度慢
        oracle
            目前最好关系型数据库,体现在用户管理,分布式
            商业用途收费
        mysql
            免费开源,功能支持没有oracle强 但是足以满足中小企业使用
            先后被sun 和 oracle 收购
            mysql创始人wedenius担心 mysql会闭源 于是另起灶炉 搞了marriaDB
            mariaDB和mysql 使用方法一模一样
        sqlServer
            属于微软生态链,需要和windows配合使用
        DB2
            IBM开发的大型关系型数据库 ,收费的,通常与硬件捆绑销售
        maria

    非关系型 数据之间不存在关联关系
            容量小 速度快 以key value形式存储
        mongoDB
        redis
        memcache

2.数据库的相关概念 ******
    age = 18
    一个变量 无法描述清楚数据
    1.称之为一个字段

    age = 18,name = Jack,genger = man
    一堆变量在一起,可以描述清楚数据
    2.称之为一条记录 对应着文件中的一行

    文件需要分门别类 每个文件存储不同的数
    3.一个文件称之为表

    文件越来越多 得分文件夹
    4.一个文件称之为库

    数据库中包含所有内容:
    字段(列)  一行中的某个数据
    记录  一行数据
    表   文件
    库  文件夹
    DBMS  数据库管理系统  socket软件
    数据库服务器 计算机
    一个库可以包含多个表  一个表可以包含多条记录  一条记录中有多个字段

    数据库是什么,本质上就是一套C/S架构得socket软件



3.安装数据库与配置使用环境 ***
    1.代码界面 傻瓜式安装  记住密码
    2.绿色解压版
        1.  5.7以后的版本需要先初始化 mysqld --initialize-insecure
        2.启动需要先进入安装目录  比较麻烦 需要添加到环境变量
            打开环境变量 将mysql安装目录下的bin加入path变量
        3.将mysqld 加入到windows的系统服务启动 services.mcs

4.绿色版修改默认密码
    修改默认密码需要先登录成功
    执行update语句直接修改 user表中的密码
    ?查询系统用户的基础信息desc mysql.user;
    5.7
    update mysql.user set authentication_string = password("admin") where user = "root";
    5.6
    update mysql.user set password = password("admin") where user = "root";


    修改密码方式2 不需要登录 需要知道旧密码
    mysqladmin -uroot -p旧密码 password "123"

    破解密码,当忘记密码时  无法登录 也就不能值执行update语句
    思路:
    1.启动服务器时 控制它不要加载授权表
    2.无密码登录服务器
    3.执行update mysql.user set authentication_string = password("root") where user = "root"
    4.重启服务器后更改密码才会生效

用到的系统指令:
mysqld 直接运行服务器程序
net start mysql 启动mysql服务器
net stop mysql
mysqld --skip-grant-tables 跳过授权表  用于重设密码
tasklist | findstr mysqld     taskkill /f /pid 结束服务器程序
mysql 运行客户端程序 -u用户名 -p密码 -h主机地址 -P端口号
mysqld --install  将mysqld注册当windows服务中  在服务中叫MySQL
    windows就是绑定了一个exe程序
sc delete mysql  删除windows服务
exit 退出客户端



5.简单的sql语句 *****
   针对库的相关操作
       增
            create database 库名称
       删
            drop database 库名称
       改
            alter database 库名称 要改的属性名称
                alter database db1 DEFAULT CHARACTER SET utf8;
                /alter database db1 CHARSET utf8;
            注意 在mysql中 utf-8 不能带- 写成utf8
       查
            show databases查看所有数据库
            show create databases 库名称 查看建库的语句
    命名规范:
        1.不能使用纯数字
        2.可以是数字 字母 下滑线的组合
        3.可以下滑线开头
        4.不能是关键字 如create
        大致和python相同 ,不区分 大小写

   表相关的操作
    增
        建表时要明确数据库
        use db1;
        create table 表名称(字段名 类型(长度),....)
            create table dog(nikename char(10),gender char(1),age int)
        #创建时同时指定数据库
        create table 库名称.表名称(字段名 类型(长度),....)
    删
        drop table 表名;
    改
        alter table 表名称 (DCMA)drop|change|modify|add
            alter table drop 字段名称
                alter table dog drop color;
            alter table change 旧字段名 新字段名 新类型
                alter table dog change gender sex  char(2);
            alter table modify 字段名 新类型
                alter table dog modify color char(5);
            alter table add 新字段名称 新类型
                alter table dog add color char(10);
        重命名表
            rename table 旧表名称 to 新表名称
                rename table dog to dogtable;
        修改表的属性
            alter table 表名 属性名 值;
                alter table dogtable DEFAULT CHARSET gbk;
    查
        show tables;查看所有表
        show create table 表名;查建表语句
        desc 表名称;  查看表结构


   记录相关操作
        增
            insert into 表名 values(值1,值2.....)
        删
            delete from 表名 where 字段名称 = 值
            没有条件的话删除全部数据
        改
            update 表名 set 字段名 = 新的值  where 字段名 = 值
            没有条件的话修改全部
        查
            select *from 表名;  *表示通配符 查看所有字段
            select 字段名称1,字段名2..  from 表名;


复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引）
    mysql> create table new_service select * from service;

只复制表结构
    mysql> select * from service where 1=2;        //条件为假，查不到任何记录
    Empty set (0.00 sec)
    mysql> create table new1_service select * from service where 1=2;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    mysql> create table t4 like employees;


6.修改默认编码
    配置文件放在安装路径根目录中 就是和bin同级  名称必须叫my.ini
    #客户端的配置
    [client]
    #mysql这个客户端的配置
    [mysql]
    user = root
    password = root
    default-character-set = utf8
    #服务器端的配置
    [mysqld]
    character-set-server = utf8

7.数据库的引擎 ***

8. 5.6 与5.7的区别
    1. 5.7需要初始化
    2. 5.6游客模式  没有密码可以登录 但是无法操作数据
        只能看到 information_schema mysql
    3. 5.7中 data数据存放目录 是由初始化时决定
        带界面的 在C:ProgramData中
        不带界面的就在安装目录中
       5.6就在安装目录中
    4.密码存储字段名 在5.6中时password  5.7中叫authentication_string

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。
SQL语言分为3种类型：
    #1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
    #2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
    #3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

小结
1.数据库是什么?
    mysql 一种DBMS
    是一个CS的socket软件

    数据库服务器
    DBMS
    文件夹 库
    文件  表
    一个完整数据  记录
    单个数据      字段

数据库的分类
    关系型  数据之间存在某种关联 存在硬盘上
    非关系型  数据之间存在某种关联  以key value形式存储  存在内存中

    为什么用mysql
    免费开源 能够支持中小企业使用

    安装两种方式
    1.界面
    2.解压安装

    环境变量
    系统服务
    修改默认秘密
    破解密码
    修改编码(配置文件)
    简单Crete Read Update Delete


语法

SQL语句数据行操作补充
			create table tb12(
				id int auto_increment primary key,
				name varchar(32),
				age int
			)engine=innodb default charset=utf8;
	
		增
			insert into tb11(name,age) values('alex',12);
			
			insert into tb11(name,age) values('alex',12),('root',18);
			
			insert into tb12(name,age) select name,age from tb11;
		删
			delete from tb12;
			delete from tb12 where id !=2 
			delete from tb12 where id =2 
			delete from tb12 where id > 2 
			delete from tb12 where id >=2 
			delete from tb12 where id >=2 or name='alex'
		
		改
			update tb12 set name='alex' where id>12 and name='xx'
			update tb12 set name='alex',age=19 where id>12 and name='xx'
		查
			
			select * from tb12;
			
			select id,name from tb12;
			
			select id,name from tb12 where id > 10 or name ='xxx';
			
			select id,name as cname from tb12 where id > 10 or name ='xxx';
			
			select name,age,11 from tb12;
			
			其他：
				select * from tb12 where id != 1
				select * from tb12 where id in (1,5,12);
				select * from tb12 where id not in (1,5,12);
				select * from tb12 where id in (select id from tb11)
				select * from tb12 where id between 5 and 12;
	
			
				通配符：
				
				select * from tb12 where name like "a%"
				select * from tb12 where name like "a_"
	
			
				分页：
				
					select * from tb12 limit 10;
					
					select * from tb12 limit 0,10;
					select * from tb12 limit 10,10;
					select * from tb12 limit 20,10;
					
					select * from tb12 limit 10 offset 20;
					从第20行开始读取，读取10行；
		
					结合Python分页：
					# page = input('请输入要查看的页码')
					# page = int(page)
					# (page-1) * 10
					# select * from tb12 limit 0,10; 1 
					# select * from tb12 limit 10,10;2
				
				
				排序：
					select * from tb12 order by id desc; 大到小
					select * from tb12 order by id asc;  小到大
					 select * from tb12 order by age desc,id desc;
					 
					取后10条数据
					select * from tb12 order by id desc limit 10;
			
				分组：
				
					select count(id),max(id),part_id from userinfo5 group by part_id;
					
					count
					max
					min
					sum
					avg
					
					**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****
					select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
					
					select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
			
					
				连表操作：
				
					select * from userinfo5,department5
					
					select * from userinfo5,department5 where userinfo5.part_id = department5.id
					

					select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
					select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
					# userinfo5左边全部显示
					
					
					# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
					# department5右边全部显示
				
				
				
					select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
					将出现null时一行隐藏
					
					
				
				
				
				
					select * from 
						department5 
					left join userinfo5 on userinfo5.part_id = department5.id
					left join userinfo6 on userinfo5.part_id = department5.id
				
				
					select 
						score.sid,
						student.sid 
						from 
					score

						left join student on score.student_id = student.sid

						left join course on score.course_id = course.cid

						left join class on student.class_id = class.cid

						left join teacher on course.teacher_id=teacher.tid
					
			
			
			
			select count(id) from userinfo5;