就业班day_09_mysql

关系型数据库管理系统 (存放数据和数据之间的关系  一对一 一对多 多对多 er实体图) er？     RDBMS = Manager + Databases     Database =  Tables     Table = 表结构 + 表记录

mysql安装和卸载    见文档       1 mysql安装有二个目录，一个是程序安装目录，一个是data存放目录。data目录存放在c:\programdata目录下，卸载时该目录不会删除，需要手动删除。      2 net stop mysql  net start mysql   停用和启用mysql服务          3 命令行登录 mysql -u root -p   -h  表示服务器IP 不写表示本机  -P  表示端口          4. my.ini，MySQL最为重要的配置文件                  > 配置MySQL的端口：默认为3306，没有必要去修改它；                  > 配置字符编码：                        * [client]下配置客户端编码：default-character-set=gbk                           可以用来解决命令行乱码问题，命令行默认是gbk编码，用连接工具不存在该问题。                        * [mysqld]下配置服务器编码：character-set-server=utf8                  > 配置二进制数据大小上限：                        * 在[mysqld]下配置：max_allowed_packet=8M                 补充：                  show variables '%character%'; 查看字符编码                   character_set_client、character_set_results、character_set_connection、character_set_server 、character_set_data                  SQL 结构化查询语言 structured  query  language （SQL标准 + 方言）        DDL  数据定义语言                  DML  数据操作语言                 DQL  数据查询语言                 DCL   数据控制语言数据库                  create database shopping;                  drop database shopping;                 show databases;                 alter database shopping character set utf8;                 注意 在mysql中的utf8字符集中没有-。                 查询当前数据库 select database();                 使用某个数据库  use  shopping;         

2 数据类型
    java类型          mysql类型
    byte                tinyint
    short               smallint
    int                   int
    long                bigint        表示整数 常用int
    float                float
    double             double      表示小数，常用double(5,2)，该写法表示有三位整数，2位小数，即最大值为999.99
    decimal            在表示钱方面使用该类型，因为不会出现精度缺失问题；
    char                 char(255)  varchar(65535)
    boolean            ？
    String               char varchar
    Date                 date  time  datetime timestamp
    Blob                  blob
    Clob                  text
     注意
    char varchar  可变不可变  char 长度不够补空格  varchar 长度不够不补 自适应。
    datetime timestamp 区别  datatime 没插入时  补null   timestamp 没插入时 补系统时间
   
3 完整性约束
   primary key  
       添加主键
       alter table stu add primary key(sid);
       删除主键
       alter table stu drop primary key;
       主键自增长 auto_increment  这是mysql的方言   添加和删除自增长 用change
   unique
   not null
   foreign key
       添加外键
       alter table stu add FOREIGN KEY(u_id) REFERENCES t_user(uid);
       删除外键
       alter table user drop foreign key FK_user;

4  表
    创建表
         create table user (
            id int primary key auto_increment,
            name varchar(20) unique,
            age int not null
         );
    删除表
        drop table user;
    修改表
         添加字段
         alter table user add birthday date not null;
         修改字段类型和长度
         alter table user modify age int(20);
         修改字段名称
         alter table user change age age1 int(15) ;
         修改表名
         rename table user to user_bak;
    查询表
        show tables;
        show create table student;
        desc student;

插入记录                  insert into user values(1,'lisi',10);                  insert into user( name,age) values('zhangshan',20);                  注意  字符串和日期 用单引号删除记录                  delete from user  where id =1;                  注意 delele 和 truncate 的区别                  delete 属于dml语句 有日志记录 可以恢复                  truncate 属于ddl语句 没有日志记录 不可以恢复  相当于drop table ，然后 createdtable 。更新记录                update user set age = 30 where id =1 ;        注意 删除和更新操作实际应用中都会带条件。创建用户         create user user1@localhost identified by '111'; 表示创建user1密码111只能在localhost上登录         create user user1@'%'  identified by '111'; 表示创建user1密码111，在所有IP上都能登录         给用户授权         grant create,alter,drop,insert,update,delete,select on mydb1.*  to user1@localhost;          表示给user1授予mydb1库上的(create,alter,drop,insert,update,delete,select)权限         grant all on mydb1.* to user1@localhost; 表示给User1授予mydb1库上的所有权限         撤销授权         revoke create,alter,drop on mydb1.* from user1@localhost;         查看用户权限         show grants for user1@localhost;         删除用户         drop user user1@localhost;         修改用户密码  注意：需要先切换到mysql自带的数据库中（mysql）； 需要root身份登录         update user set password=password('111') where userid= 'user1';         flush privileges;         注意：user1@localhost  用户名和ip是绑定的。    create,alter,drop...on mydb1.*   权限和数据库是绑定的。表与表之间的关系                a 一对一：例如公民表和身份证表。设计方案有二种：                        a 在身份证表中添加外键，指向公民表的主键，并且给外键添加唯一约束；                        b 给身份证表中的主键添加外键约束，即身份证表的主键也是外键。该方案不可取，因为身份证表的主键被赋予了业务属性。主键一般采用非业务字段。                        注意：另外也可以将二张表设计成一张表。                b 一对多：例如部门表和员工表。设计方案：                    在多的一方(员工表)添加外键，指向部门表的主键。        c 多对多：例如学生表和课程表。设计方案：                   创建中间表。给出两个外键，一个指向学生表的主键，一个指向课程表的主键。                另外实际开发中，一般不创建真实外键，而是添加关联字段，通过程序来保证数据的完整性。                注意，外键约束指的是不能外键字段的内容不能超出主键的内容范围，但是不包含NULL 同时主键列如果要删除的话，必须保证外键没有指引它的引用。               

2 单表查询
    格式如下：
      select
      from
      where
      group by
      having
      order by
      limit
      注意 limit a, b  其中a从0开始 b表示取多少行数据。
      第一页  0-9      limit 0,10    limit (page-1)*10, 10
      第二页 10-19   limit 10,10
      第三页 20-29  limit 20,10
      注意：
        a 模糊查询 like  有二个占位符  _  表示任意一个字符  % 表示任意多个字符
        b IFNULL(comm, 0)  任意字段类型与null做算数运算都返回null ， 不能转化为数字的字段做算数运算被当作0处理。
        c x=null   任意字段=null返回的都是false 需要通过is null 来判断。
        d sql中赋值和判断都用=  没有==。
        e  mysql中连接二个字符串 用concat(a,b,c,d)可以有多个 oracle中用||
        f  count()  统计的是非NULL的行数
        
3 多表查询
        a 连接查询
            内连接    取左右二边都符合条件的记录,常用写法如下：
            SELECT * FROM CLASS A , STUDENT S WHERE A.CID = S.CNO;
            外连接
                    左外连接    left join  左边的表取全部记录右边的补NULL(相当于内连接+左边没匹配上的记录)  常用写法如下：
                    SELECT * FROM CLASS A LEFT JOIN STUDENT S ON A.CID = S.CNO;
                    右外连接    right join  右边的表 取全部记录  左边的补NULL  右外连接一般就转化为左外连接来写。
                    SELECT * FROM CLASS A RIGHT JOIN STUDENT S ON A.CID = S.CNO;
         b 子查询
            带in的子查询
            带exists的子查询
            select * from calss where exists (select * from student where sid>1) 表示后面的查询有记录，则执行前面的select * from calss
            带any的子查询
                > any()   大于最小值
                < any()   小于最大值
            带all的子查询
                > all()     大于最大值
                < all()     小于最小值
            子查询出现的位置：
                where后作为条件存在
                from后作为表存在(多行多列)
            子查询的不同条件应用场景
                单行单列：SELECT * FROM 表1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2  WHERE 条件)
                多行单列：SELECT * FROM 表1  WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 WHERE 条件)
                单行多列：SELECT * FROM 表1  WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2  WHERE 条件)
                多行多列：SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件
        c 合并结果集
              要求被合并的表中，列的类型和列数相同
              UNION，去除重复行
              UNION ALL，不去除重复行

事务                一组逻辑操作要么都成功，要么都失败。经典案例：银行转账                事务的特性：ACID                 原子性( 一组逻辑操作要么都成功，要么都失败)；一致性()；隔离性(事物的并发，一个事物的操作不受其它事务的影响)；持久性(事务结束，结果会保存到硬盘中)；                mysql事务操作：                        start transaction; 开启事务                        commit; 提交事务                        rollback  回滚事务                并发事物会导致的安全问题：                        a 脏读              表示一个事务读到了另外一个事务未提交的数据                        b 不可重复读    表示在同一个事务中，二次读取时读取内容不一致，读到了另外一个事务提交的update操作之后的数据。                        c  幻读             表示在同一个事务中，二次读取时读取内容不一致，读到了另外一个事务提交的insert操作之后的数据。                4种隔离级别                   a read uncommitted   会出现脏读 不可重复读 幻读 问题                   b read committed       可以避免脏读  会出现不可重复读 幻读问题       oracle默认采用的隔离级别                   c repeatable read        可以避免脏读和不和重复读  会出现幻读问题     mysql默认采用的隔离级别                   d seriaziable                可以避免脏读 不可重复读 幻读 问题                 mysql中设置隔离级别和查询隔离级别                        SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别                        SELECT @@tx_isolation;               

就业班day_09_mysql

关系型数据库管理系统 (存放数据和数据之间的关系  一对一 一对多 多对多 er实体图) er？     RDBMS = Manager + Databases     Database =  Tables     Table = 表结构 + 表记录

mysql安装和卸载    见文档       1 mysql安装有二个目录，一个是程序安装目录，一个是data存放目录。data目录存放在c:\programdata目录下，卸载时该目录不会删除，需要手动删除。      2 net stop mysql  net start mysql   停用和启用mysql服务          3 命令行登录 mysql -u root -p   -h  表示服务器IP 不写表示本机  -P  表示端口          4. my.ini，MySQL最为重要的配置文件                  > 配置MySQL的端口：默认为3306，没有必要去修改它；                  > 配置字符编码：                        * [client]下配置客户端编码：default-character-set=gbk                           可以用来解决命令行乱码问题，命令行默认是gbk编码，用连接工具不存在该问题。                        * [mysqld]下配置服务器编码：character-set-server=utf8                  > 配置二进制数据大小上限：                        * 在[mysqld]下配置：max_allowed_packet=8M                 补充：                  show variables '%character%'; 查看字符编码                   character_set_client、character_set_results、character_set_connection、character_set_server 、character_set_data                  SQL 结构化查询语言 structured  query  language （SQL标准 + 方言）        DDL  数据定义语言                  DML  数据操作语言                 DQL  数据查询语言                 DCL   数据控制语言数据库                  create database shopping;                  drop database shopping;                 show databases;                 alter database shopping character set utf8;                 注意 在mysql中的utf8字符集中没有-。                 查询当前数据库 select database();                 使用某个数据库  use  shopping;         

2 数据类型
    java类型          mysql类型
    byte                tinyint
    short               smallint
    int                   int
    long                bigint        表示整数 常用int
    float                float
    double             double      表示小数，常用double(5,2)，该写法表示有三位整数，2位小数，即最大值为999.99
    decimal            在表示钱方面使用该类型，因为不会出现精度缺失问题；
    char                 char(255)  varchar(65535)
    boolean            ？
    String               char varchar
    Date                 date  time  datetime timestamp
    Blob                  blob
    Clob                  text
     注意
    char varchar  可变不可变  char 长度不够补空格  varchar 长度不够不补 自适应。
    datetime timestamp 区别  datatime 没插入时  补null   timestamp 没插入时 补系统时间
   
3 完整性约束
   primary key  
       添加主键
       alter table stu add primary key(sid);
       删除主键
       alter table stu drop primary key;
       主键自增长 auto_increment  这是mysql的方言   添加和删除自增长 用change
   unique
   not null
   foreign key
       添加外键
       alter table stu add FOREIGN KEY(u_id) REFERENCES t_user(uid);
       删除外键
       alter table user drop foreign key FK_user;

4  表
    创建表
         create table user (
            id int primary key auto_increment,
            name varchar(20) unique,
            age int not null
         );
    删除表
        drop table user;
    修改表
         添加字段
         alter table user add birthday date not null;
         修改字段类型和长度
         alter table user modify age int(20);
         修改字段名称
         alter table user change age age1 int(15) ;
         修改表名
         rename table user to user_bak;
    查询表
        show tables;
        show create table student;
        desc student;

插入记录                  insert into user values(1,'lisi',10);                  insert into user( name,age) values('zhangshan',20);                  注意  字符串和日期 用单引号删除记录                  delete from user  where id =1;                  注意 delele 和 truncate 的区别                  delete 属于dml语句 有日志记录 可以恢复                  truncate 属于ddl语句 没有日志记录 不可以恢复  相当于drop table ，然后 createdtable 。更新记录                update user set age = 30 where id =1 ;        注意 删除和更新操作实际应用中都会带条件。创建用户         create user user1@localhost identified by '111'; 表示创建user1密码111只能在localhost上登录         create user user1@'%'  identified by '111'; 表示创建user1密码111，在所有IP上都能登录         给用户授权         grant create,alter,drop,insert,update,delete,select on mydb1.*  to user1@localhost;          表示给user1授予mydb1库上的(create,alter,drop,insert,update,delete,select)权限         grant all on mydb1.* to user1@localhost; 表示给User1授予mydb1库上的所有权限         撤销授权         revoke create,alter,drop on mydb1.* from user1@localhost;         查看用户权限         show grants for user1@localhost;         删除用户         drop user user1@localhost;         修改用户密码  注意：需要先切换到mysql自带的数据库中（mysql）； 需要root身份登录         update user set password=password('111') where userid= 'user1';         flush privileges;         注意：user1@localhost  用户名和ip是绑定的。    create,alter,drop...on mydb1.*   权限和数据库是绑定的。表与表之间的关系                a 一对一：例如公民表和身份证表。设计方案有二种：                        a 在身份证表中添加外键，指向公民表的主键，并且给外键添加唯一约束；                        b 给身份证表中的主键添加外键约束，即身份证表的主键也是外键。该方案不可取，因为身份证表的主键被赋予了业务属性。主键一般采用非业务字段。                        注意：另外也可以将二张表设计成一张表。                b 一对多：例如部门表和员工表。设计方案：                    在多的一方(员工表)添加外键，指向部门表的主键。        c 多对多：例如学生表和课程表。设计方案：                   创建中间表。给出两个外键，一个指向学生表的主键，一个指向课程表的主键。                另外实际开发中，一般不创建真实外键，而是添加关联字段，通过程序来保证数据的完整性。                注意，外键约束指的是不能外键字段的内容不能超出主键的内容范围，但是不包含NULL 同时主键列如果要删除的话，必须保证外键没有指引它的引用。               

2 单表查询
    格式如下：
      select
      from
      where
      group by
      having
      order by
      limit
      注意 limit a, b  其中a从0开始 b表示取多少行数据。
      第一页  0-9      limit 0,10    limit (page-1)*10, 10
      第二页 10-19   limit 10,10
      第三页 20-29  limit 20,10
      注意：
        a 模糊查询 like  有二个占位符  _  表示任意一个字符  % 表示任意多个字符
        b IFNULL(comm, 0)  任意字段类型与null做算数运算都返回null ， 不能转化为数字的字段做算数运算被当作0处理。
        c x=null   任意字段=null返回的都是false 需要通过is null 来判断。
        d sql中赋值和判断都用=  没有==。
        e  mysql中连接二个字符串 用concat(a,b,c,d)可以有多个 oracle中用||
        f  count()  统计的是非NULL的行数
        
3 多表查询
        a 连接查询
            内连接    取左右二边都符合条件的记录,常用写法如下：
            SELECT * FROM CLASS A , STUDENT S WHERE A.CID = S.CNO;
            外连接
                    左外连接    left join  左边的表取全部记录右边的补NULL(相当于内连接+左边没匹配上的记录)  常用写法如下：
                    SELECT * FROM CLASS A LEFT JOIN STUDENT S ON A.CID = S.CNO;
                    右外连接    right join  右边的表 取全部记录  左边的补NULL  右外连接一般就转化为左外连接来写。
                    SELECT * FROM CLASS A RIGHT JOIN STUDENT S ON A.CID = S.CNO;
         b 子查询
            带in的子查询
            带exists的子查询
            select * from calss where exists (select * from student where sid>1) 表示后面的查询有记录，则执行前面的select * from calss
            带any的子查询
                > any()   大于最小值
                < any()   小于最大值
            带all的子查询
                > all()     大于最大值
                < all()     小于最小值
            子查询出现的位置：
                where后作为条件存在
                from后作为表存在(多行多列)
            子查询的不同条件应用场景
                单行单列：SELECT * FROM 表1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2  WHERE 条件)
                多行单列：SELECT * FROM 表1  WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 WHERE 条件)
                单行多列：SELECT * FROM 表1  WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2  WHERE 条件)
                多行多列：SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件
        c 合并结果集
              要求被合并的表中，列的类型和列数相同
              UNION，去除重复行
              UNION ALL，不去除重复行

事务                一组逻辑操作要么都成功，要么都失败。经典案例：银行转账                事务的特性：ACID                 原子性( 一组逻辑操作要么都成功，要么都失败)；一致性()；隔离性(事物的并发，一个事物的操作不受其它事务的影响)；持久性(事务结束，结果会保存到硬盘中)；                mysql事务操作：                        start transaction; 开启事务                        commit; 提交事务                        rollback  回滚事务                并发事物会导致的安全问题：                        a 脏读              表示一个事务读到了另外一个事务未提交的数据                        b 不可重复读    表示在同一个事务中，二次读取时读取内容不一致，读到了另外一个事务提交的update操作之后的数据。                        c  幻读             表示在同一个事务中，二次读取时读取内容不一致，读到了另外一个事务提交的insert操作之后的数据。                4种隔离级别                   a read uncommitted   会出现脏读 不可重复读 幻读 问题                   b read committed       可以避免脏读  会出现不可重复读 幻读问题       oracle默认采用的隔离级别                   c repeatable read        可以避免脏读和不和重复读  会出现幻读问题     mysql默认采用的隔离级别                   d seriaziable                可以避免脏读 不可重复读 幻读 问题                 mysql中设置隔离级别和查询隔离级别                        SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别                        SELECT @@tx_isolation;