MySQL基础知识

本文为自己对MySQL数据库基础知识的一些笔记整理。

MySQL:关系型数据库

1.选择数据库３点：
    １）．是否开源，２）．是否跨平台，３）．看公司类型
开源并且也跨平台的数据库：
    MySQL,MariDB,MongoDB
2.关系型数据库的核心内容是关系,即二维表
3. 启动和连接MySQL服务
   1).启动(路径根据个人实际路径有所不同)
      a.查看MySQL服务的状态
        sudo /etc/init.d/mysql status
      b.停止MySQL服务的状态
        sudo /etc/init.d/mysql stop
      b.启动MySQL服务的状态
        sudo /etc/init.d/mysql start
      b.重启MySQL服务的状态
        sudo /etc/init.d/mysql restart
   2).连接
   mysql -u用户名 -p密码
    示例:
     mysql -uroot -p123456
     mysql -u root -p123456(-u后可以打空格,-p后打空格则连贯失败,还要输入密码)
４．使用\c终止命令
５．查看当前已有的库：show databases;
    创建库：create database 库名;
    查看表：先切换库：use 库名;然后show tables;
    删除库：drop database 库名;
    创建表：create table 表名(字段名　数据类型，字段名　数据类型...);
    删除表：drop table 表名;
    查看表结构：desc 表名;
    在表中插入记录：insert into 表名(字段１，字段２...)　values(值１，值２...);
    查看表中记录：select * from 表名;(查看包含所有字段的记录);
                select 字段名１，字段名２... from 表名;(查看包含部分字段名的记录);
6.MySQL中的数值类型有：数值类型，字符类型，枚举类型，日期时间类型．
　　１）．char和varchar的区别?各自的特点        
       cahr:定长,浪费存储空间,性能高,常用于生产环境中
       varchar:变长,相对于varchar来说节省存储空间,但是性能比较低
　　２）．枚举类型：
    1>.单选enum:字段名　enum(值１，值２，．．．)
    2>.多选set:字段名　set(值１，值２...)
  3).日期时间类型：
    　a. year:年 YYYY
     b. date:日期 YYYYMMDD
     c. time:时间 HHMMSS
     e. datetime:日期时间 YYYYMMDDHHMMSS
７．什么是ER模型,请举例说明 ：            
　　　　即实体关系模型:例如学生选课系统,为了减少数据库中数据的冗余,设计三张表,学生信息表记录学生信息,课程表用来记        录课程信息,教师表用来记录授课老师信息.
８．设置支持中文：
　　　１）．创建库时设置库支持中文：create database 库名 default charset=utf8;
　　　２）创建表时设置表支持中文：create table 表名(字段名 数据类型)default charset = utf8;
９．表字段增删改查：
    增：alter table 表名　add 字段名　数据类型;
    删: alter table 表名　drop 字段名;
    改：alter table 表名 change 旧字段名 新字段名 类型;
    查：desc 表名;
    修改表名：alter table 表名 rename 新表名;
    修改字段的数据类型：alter table 表名 modify 字段名 新的数据类型;
10.表记录增删改查：
    增：insert into 表名(字段１，字段２...)　values(值１，值２...);
    删：delete from 表名 where 条件子句;
    改：update 表名　set 字段名１＝新值１，字段名２＝新值２　where 条件子句;
    查：select * from 表名　where 条件子句;
        查部分：delect 字段名１，字段名２．．．　from  表名;
11.聚合函数
  1).avg(字段名):求字段的平均值
  2).sum(字段名):求字段的和
  3).max(字段名):求字段的最大值
  4).min(字段名):求字段的最小值
  5).count(字段名):统计该字段的记录的个数
12.default 默认约束
    not null 非空约束
    primary key 主键约束　　PRI 
    unique key 唯一约束   UNI
    foreign key 外键约束
    auto_increment　自增长属性
１３．添加外键：
    １）.创建表时添加：foreign key(参考字段名)  references 被参考表名(被参考字段名)　［on 　delete 级联动作(cascade)　on update 级联动作(cascade)］;
    2).在已有表中添加外键约束:
     alter table 从表名 add foreign key(从表参照字段) references 主表名(参照字段,主表)　[on delete cascade on update cascade];
    注意:在已有表中添加外键时,会受到原有数据的限制
    删除外键：
     alter table 表名 drop foreign key 外键名; 
１４．嵌套查询：sql查询语句 where 条件(sql查询语句);
１５．多表查询（**笛卡尔集合）
 两种方式:
  1.select 字段名列表 from 表名列表1,表名2;  -->笛卡尔集
   示例:
    1)select * from t1,t2;
      select t1.username,t1.uid,t2.username from t1,t2;
  2.select 字段名列表 from 表名列表 where 条件;
   示例:
     1).找到t1表和t2表中相同的用户名,全部显示信息
      select * from t1,t2 where t1.username=t2.username;
     2).找到t1和t2表中相同的uid号,把两张表的uid和username都显示出来
      select t1.uid,t1.username,t2.uid,t2.username from t1,t2 where  t1.uid=t2.uid;
１６.连接查询
  先创建两张表
    create table t3 select username,uid from userinfo limit 2;
    create table t4 select username,uid from userinfo limit 3;
  1.左连接
    select 字段名 from 表1 left join 表2 on 条件;(以左边的表1为主)
  示例:
    把t3和t4表中username相同的记录显示出来,以t3为主
    select * from t3 left join t4 on t3.username=t4.username;
    比较insert into t3 values("MYSQL",888),("Python",666);后再执行上句
  2.右连接
    select 字段名 from 表1 right join 表2 on 条件;(以右边的表2为主)
１７．pymysql的使用：
    import pymysql
    try:
        # 1.创建数据库连接
        db = pymysql.connect("主机地址", "root", "密码")
        # 2.创建游标对象(用db对象的cursor方法创建游标)
        cursor = db.cursor()
        # 3.使用游标的方法和sql语句操控MySQL数据库
        cursor.execute("create database python;")
        cursor.execute("use python;")
        cursor.execute("create table t1(id int primary key,name char(20),age tinyint unsigned,sex enum('boy','girl'));")
        # 4.提交到数据库commit
        db.commit()
        # 5.关闭游标
        cursor.close()
        # 6.关闭数据库连接
        db.close()
    except Exception as e:
        raise e
        print(e)
１８．数据库和表的导入、导出
　　1).导出整个的数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名
　　　　mysqldump -u root -p db_test >/home/bak/dbtest.sql
　　2).导出指定数据库中的一个表：mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
　　　　mysqldump -u root -p db_test tbl_user >/home/bak/test_user.sql
　　3).只导出一个结构（struct）：加上-d参数
　　　　mysqldump -u root -p -d –add-drop-table db_test >/home/bak/btest_struct.sql
　　4).只导出一个数据（data）：加上-t参数
　　　　mysqldump -u root -p -t –add-drop-table db_test >/home/bak/btest_struct.sql
    ⑴.只导出表结构 -d
　　  mysqldump -h 192.168.1.11 -u root -p 123456 -d fault-character-set=utf8   test >sql.sql
  ⑵.只导出数据 -t
　　  mysqldump -h 192.168.1.11 -u root -p 123456 -t –default-character-set=utf8 test >sql.sql
注：①.-d 没有数据(不导出数据，只导出数据结构和表结构)
　　　　 -t 不导出结构，只要数据
　　　 　–add-drop-table 在每个create语句之前增加一个drop table
　　　 　–default-character-set 设置字符集
　　　②.这里的导出文件没有加路径，就是相对路径，当前的目录下的文件。
　　　③.密码是显示输入的，跟在-p后面。
　　5).导入数据库：source 命令
　　　　[root@dream ~] mysql -u root -p #登陆
　　　　mysql>use test;
　　　　mysql>source /home/bak/db_test.sql
１９．数据备份与恢复(在Linux终端中操作)
 　1.数据备份
   mysqldump -u用户名 -p源库名 > 路径/(xxx.sql)  
    ()里的文件名可变,txt等格式也可以,最好用.sql
  　2)备份所有的库
    源库名的表达方式
      --all-databases   备份所有库
      单个库名           备份单个库
      -B 库1 库2        备份多个库
      库名 表名          备份指定库的指定表
  2.数据恢复(在Linux终端中操作)
    语法:
      mysql -uroot -p 目标库名 < 路径/xxx.sql
    示例:
     1).恢复db3库
      mysql -uroot -p db3 < /home/tarena/maydata/db3.sql
     2).恢复所有的库
     mysql -uroot -p < /home/tarena/maydata/allku.sql
     3)从所有库的备份当中恢复一个库db2
     mysql -uroot -p db2 --one-database < /home/tarena/maydata/allku.sql
     4)恢复某个库中的表:
     mysql -uroot -p db3 < 路径kubiao.sql
    注意:
      在数据恢复时如果要恢复的库不存在,则先要创建一个空库。