MySQL—概念，用户的创建，主键，外键，数据类型，表格创建

MySQL

 

DBMS，MySQL的概念，数据库分类，以前MySQL的部署中的一些概念

#DBMS：数据库管理系统，用于管理数据库的大型软件。mysql就是dbms的一种
#Mysql：是用于管理文件的一个软件
        #服务端软件
            #socket服务端
            #本地文件操作
            #解析指令（sql语句）
        #客户端软件（各种各样）
            #socket客户端
            #发送指令
            #解析指令（sql语句）

#除了mysql，还有其他类似软件：
#语法大体上都是sql语句
        #关系型数据库：sqllite，db2，Oracle，access，sql server，MySQL
                #有比较多约束，比如字段类型啊，表和表，字段和字段间依赖呀
        #非关系型数据库：MongoDB，redis
                #没这么多约束


#MySQL安装：
    # bin目录下 mysql是客户端，mysqld是服务端
    #初始化： 初始化数据库mysqld --initialize --console。前者没密码
            # 5.7版本需要初始化data目录 ： mysqld --initialize -insecure
    #如果不添加环境变量：每次启动都要先告诉系统所在bin目录，所以需要添加环境变量
            # cmd1  启动服务端：E:\wupeiqi\mysql - 5.7.16 - winx64\mysql - 5.7.16 - winx64\bin\mysqld
            # cmd2  客户端连接：E:\wupeiqi\mysql - 5.7.16 - winx64\mysql - 5.7.16 - winx64\bin\mysql - u root - p

    #windows服务：每一个window上都有很多windows服务，默默在后台跑着 （如果不行试一下用绝对路径）
        #所以可以把 mysqld这个服务端制作为，windows服务，就可以不用多开一个cmd了。首先cd到 bin目录，输入以下命令
                # 命令为 mysqld install 或者 mysqld --install。server.msc里就有了个mysql
                #移除服务：mysqld --remove
        #再通过命令 ： net start mysql  就可以启动了，也可以从系统服务端里找到，右键启动
        #停止服务： net stop mysql

#总结：解压压缩包---MySQL路径具体到bin添加到环境变量---打开cmd---切换到bin目录---初始化【命令】---制作windows服务【mysqld install】---启动【net start mysql】--【mysql -u root -p】


#一些指令：
    #show databases;
    #create database 【db名】;

#sql语句：
    #DDL: 定义语言  比如 create  drop  alter
    #DML: 操作语言  比如 insert  update  delete  select
    #DCL：控制语言  比如  grant revoke
# 注释
#     单行注释： --
#     多行注释： /* ....*/
#建议 命令大写  表名，库名都小写

#windows下区分大小写的设置：
#在[mysqld] 设置lower_case_table_names = 0


# 在Linux下：
# 1、数据库名与表名是严格区分大小写的；
# 2、表的别名是严格区分大小写的；
# 3、列名与列的别名在所有的情况下均是忽略大小写的；
# 4、变量名也是严格区分大小写的；

#规范是建议大写，但是主要看公司规范

建议命令大写，表名库名小写

    #那在终端要怎么备份呢？
        #用 mysqldump
        #备份：数据表结构和数据
            #打开cmd 输入命令： mysqldump -u root db1 > db1.sql -p 回车输入密码即可
            #保存位置在 cmd行输入时候的路径，文件名为 db1.sql
        #只备份：数据表结构：
            #mysqldump -u root -d db1 > db1.sql -p  #多了个  -d
    #那备份的怎么导入呢？
        #首先创建一个数据库 create database db_name；
        #命令： mysqldump -uroot -p密码 db_name < 文件路径

 

 

用户的创建，删除，授权，取消权限，密码修改

'''
mysql -h localhost(或者是想连接的服务器的 IP)  -u （主机上有的账号） -p
'''
#http://www.cnblogs.com/wupeiqi/articles/5713315.html  数据库操作
#一些指令：
    #show databases;
    #create database 【db名】defalut charset utf8;
            # CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
    #drop database 【db_name】
        #数据库没有修改一说
    #show tables;
    # drop table table_name;
    #select user from user

#创建用户:
    #create user 'username'@'ip' identified by 'password'
    #create user 'gkx'@'192.168.0.1' identified by 'gkx123'
    #create user 'gkx2'@'192.168.0.%' identified by 'gkx123'
    #create user 'gkx2'@'%' identified by 'gkx123'
    #用户授权：
        #grant select,insert,update on db1.* to 'gkx'@'%'
        #grant all privileges on *.* to 'gkx'@'%'
    #取消权限：
        #revoke all privileges from db1.* to 'gkx'@'%'

#修改密码:
    #ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'
    # 修改密码另一种方式：
            # use mysql；
            # ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
            # FLUSH PRIVILEGES;

# 删除用户
#     drop user '用户名'@'IP地址';
# 修改用户
#     rename user '用户名'@'IP地址'  to '新用户名'@'IP地址';
#         修改用户名，可以通过操作user数据库，但是不建议这么做
#         mysql> use mysql;  选择数据库
#         Database changed
#         mysql> update user set user="dns" where user="root";    将用户名为root的改为dns
# 修改密码
#     set password for '用户名'@'IP地址' = Password('新密码') #8.04后开始不能用了


#但是实际生产过程中，这些都不用我们创建，DBA（database administrator）来操作，我们拿到用户名及密码即可

# show databases;
# use 数据库名称;
# show tables;
#select * from 表名;  or  select field1,field2 from talbename

 

外键的定义，创建，删除，约束条件，作用，以及外键的变种

# select now(); 显示时间
# 定义：如果一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键。
        #如果父表的主键是复合主键，那么子表也需要指定两列对应     constraint fk_fkname foreign key (id1,id2) references table_name(pid1,pid2)
# 父表：外表，主键被指向的表， 子表：设置外键的那张表
# 　　外键的默认作用有两点：
# 　　1.对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败。
# 　　2.对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败。
#外键设置： 取名： 用表名就不会重复了   fk_table1_table2
    #1.节省空间
    #2.约束字段取值
    #命令： constraint foreign_key_name  foreign key (想绑定的本表ID) references table_name(外表id);
    #******在创建好的表后 alter table my_tab1 add [constraint 外键名] foreign key(外键字段名) references mytab2(主键字段名);
            # (3) 查看外键:
            # SHOW CREATE TABLE ***;可以查看到新建的表的代码以及其存储引擎.也就可以看到外键的设置.
            # 删除外键:
            # alter table drop foreign key '外键名'.
            # 注意:
            # 只有在定义外键时,用constraint 外键名 foreign key .... 方便进行外键的删除.
            # 若不定义,则可以:
            # 先输入:alter table drop foreign key -->会提示出错.此时出错信息中,会显示foreign key的系统默认外键名.--->用它去删除外键.
            #对于非InnoDB表，FOREIGN KEY子句会被忽略掉。

        #···创建多个外键: 约束规则默认是 restrict
            #··所以要想操作外表，必须把子表修改好了
# create table score(sid int not null auto_increment primary key,
#   number int,
#   student_id int,
#   corse_id int,
#   constraint fk_sid_student foreign key (student_id) references student(sid),
#   constraint fk_cid_course foreign key (corse_id) references course(cid)
# [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] 可选
# [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]  可选
# )engine=innodb default charset=utf8;
'''
# 比如
# create table score(sid int not null auto_increment primary key,
#   number int,
#   student_id int,
#   corse_id int,
#   constraint fk_sid_student foreign key (student_id) references student(sid),
#   constraint fk_cid_course foreign key (corse_id) references course(cid) on delete cascade on update cascade
# )engine=innodb default charset=utf8;
'''

# 关键字
# CASCADE  删除包含与已删除键值有参照关系的所有记录
# SET NULL 修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)
# RESTRICT 拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
# NO ACTION  InnoDB拒绝删除或者更新父表。

# 外键的定制作用----三种约束模式：
# restrict：严格模式(默认), 父表不能删除或更新一个被子表引用的记录。
# cascade：级联模式, 父表操作后，子表关联的数据也跟着一起操作。
# set null：置空模式,前提外键字段允许为NLL,  父表操作后，子表对应的字段被置空。
'''
# 使用外键的前提：
# 1. 表储存引擎必须是innodb，否则创建的外键无约束效果。
# 2. 外键的列类型必须与父表的主键类型完全一致。
# 3. 外键的名字不能重复。
# 4. 已经存在数据的字段被设为外键时，必须保证字段中的数据与父表的主键数据对应起来。
'''

# 创建外键语法：
# [CONSTRAINT[symbol]]
# FOREIGN KEY [index_name](index_col_name, ...)
# REFERENCES
# tbl_name(index_col_name, ...)
# [ON DELETE reference_option]
# [ON UPDATE reference_option]
#
# reference_option:
# RESTRICT | CASCADE | SETNULL | NOACTION

在多对多中，如果子表外键多列，那么父表主键也要多列

# 定义：如果一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键。
        #如果父表的主键是复合主键，那么字表也需要指定两列对应     constraint fk_fkname foreign key (id1,id2) references table_name(pid1,pid2)
# 父表：外表，主键被指向的表， 子表：设置外键的那张表

#普通外键：
    #父表对子表  是1对多

#唯一索引外键：
    #父表对子表  是1对1
    #博客表对用户表，一个用户只有一个博客
        #比如公司用户中有个系统
            #用户表
            #管理表
                #用户表中只有少数几个管理岗才能登陆系统
                #所以就在管理表中，设置一个 唯一索引外键 ，把管理者的用户id 放到管理表中
                #图在有道词典181006

#多对多
    # 不要求联合唯一
            #图在有道词典181006  相亲用户表
    #要求联合唯一
            # 用户和主机也是多对多，用户主机关系表中可以 user和hostid可以联合唯一。
            #有道笔记20181006 mysql

#如果外键要两列，那么父表的主键也要两列

 

主键的概念，创建，删除，多列主键

# 表中的每一行都应该具有可以唯一标识自己的一列(或一组列)。而这个承担标识作用的列称为主键。
# 　　如果没有主键，数据的管理将会十分混乱。比如会存在多条一模一样的记录，删除和修改特定行十分困难。

# primary key：  加速查找 （保证数据的唯一性），一张表只能有一个主键
                #但是一个主键可以为一列，或者两列


#主键还可以这么写  ： 为了能更准确的表示数据唯一性，有时候需要用到复合主键
# create table score(sid int not null auto_increment ,
#   number int,
#   student_id int,
#   corse_id int,
#  primary key (sid,student_id) #表示由这两列设置为一个主键
                                #且记得主键不能为空
#   constraint fk_sid_student foreign key (student_id) references student(sid),
#   constraint fk_cid_course foreign key (corse_id) references course(cid)
# )engine=innodb default charset=utf8;


#   删除主键：
    # alter table t1 drop primary key;
    # 删除后创建
          # alter table t1 add primary key(id1);

 

数据类型，分成三大类数字类型，字符串，时间类型

#分成三大类

#数字类型:
    #bit 二进制
    #tinyint
    # int
    # bigint
    #float 数值越多越不准确
    #double 数值越多越不准确
    #decimal 小数推荐用这个,但是 decimal(m,d) 这里的m是指包括小数在内的总位数

# 字符串:
    #char  char(10) 不管字段值多少个,存储必定是10个位置,但是速度快,因为在搜索的时候,固定只要每10个,10个位置找就行了,不用顾虑字段值多大
    #varchar  varchar(10) 比如输入root,就只占用4个空间,但是速度慢
         # char(20) 和 varchar(20) 都只能最多存储20个字符。超过20个字符会自动截掉
         #sql优化手段,创建数据表字段的时候,把定长的放前面用char,类似地址要放最后,采用varchar
    #Varchar(m) 最大字节65535 可变长度　　m取0-65535 只保存需要字符数，另加一个字节来声明长度
    #Char(m)   最大字节数255  定长类型　　m可取0-255 它的右边填充空格以达到指定长度，当检索到char值时，尾部的空格被删除掉
    #text
    #mediumtext
    #longtext
    #如果比longtext还大,就不要用数据库存了,写成文件的格式,然后把路径存到数据库
    #对于上传文件,要把文件存硬盘,只把路径存数据库
        # 有4种text类型：tinytext、text、mediumtext和longtext。这些对应4种blob类型，有相同的最大长度和存储需求。
        # blob 列被视为二进制字符串(字节字符串)。
        # text列被视为非二进制字符串(字符字符串)。
        # blob列没有字符集，并且排序和比较基于列值字节的数值值。
        # text列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。

#时间类型:
    #datatime

#其他类型:
    #枚举 enum
    #集合 set
    # 集合类型
    # A SET column can have a maximum of 64 distinct members.
    # 示例：
    # CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
    # INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
    # SET 的值,里面只能填 abcd这四个值的任意组合

#总览
# 【1】bit[(M)]
#     二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
#
# 【2】tinyint[(m)] [unsigned] [zerofill]
# 小整数，数据类型用于保存一些范围的整数数值范围：
# 有符号： -128 ～ 127.
# 无符号：0 ～ 255
# 特别的： MySQL中无布尔值，使用tinyint(1)构造。
#
# 【3】int[(m)][unsigned][zerofill]
# 整数，数据类型用于保存一些范围的整数数值范围：
# 有符号：-2147483648 ～ 2147483647
# 无符号：0 ～ 4294967295
# 特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
#
# 【4】bigint[(m)][unsigned][zerofill]
# 大整数，数据类型用于保存一些范围的整数数值范围：
# 有符号：-9223372036854775808 ～ 9223372036854775807
# 无符号：0  ～  18446744073709551615
#
# 【5】decimal[(m[,d])] [unsigned] [zerofill]
#  准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
# 特别的：对于精确数值计算时需要用此类型decaimal能够存储精确值的原因在于其内部按照字符串存储。
#
# 【6】FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
# 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
# 有符号：-3.402823466E+38 to -1.175494351E-38，0，1.175494351E-38 to 3.402823466E+38
# 无符号： 0，1.175494351E-38 to 3.402823466E+38
#  **** 数值越大，越不准确 ****
#
# 【7】DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
# 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
# 有符号：-1.7976931348623157E+308 to -2.2250738585072014E-308，0，2.2250738585072014E-308 to 1.7976931348623157E+308
# 无符号：0，2.2250738585072014E-308 to 1.7976931348623157E+308
# **** 数值越大，越不准确 ****
#
#
# 8】char (m)
# char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
# PS: 即使数据小于m长度，也会占用m长度
#
# 【9】varchar(m)
# varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
# 注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
#
# 【10】text
# text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
#
# 【11】mediumtext
# A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
#
# 【12】longtext
# A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
#
#
# 【13】enum
# 枚举类型，
# An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
# 示例：
# CREATE TABLE shirts (
# name VARCHAR(40),
# size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
# );
# INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
#
# 【14】set
# 集合类型
# A SET column can have a maximum of 64 distinct members.
# 示例：
# CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
# INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
#
# 【15】DATE
# YYYY-MM-DD（1000-01-01/9999-12-31）
#
# 【16】TIME
# HH:MM:SS（'-838:59:59'/'838:59:59'）
#
# 【17】YEAR
# YYYY（1901/2155）
#
# 【18】DATETIME
# YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
#
# 【19】TIMESTAMP
# YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

 

表格的创建，表格的约束：字段名，数据类型，是否为null，默认值，是否自增，主键，以及表格的增删改查，字段的操作。

查看表格结构的两个语句 show create table table table_name \G;   desc table_name;

#由于我们在 my.ini中已经配置了默认信息,所以 default信息可以不用写
# engine=innodb default charset=utf8
        #mysql中的引擎:
            #innodb:事务功能和行级锁,比如原子性操作,表示一条操作如果有一部分失败了,那么整条操作会回滚
            #myisam: 全局索引,存储速度快

#create database 【db名】defalut charset utf8;
#创建表
    #create table table_name(字段1 类型,字段2 类型...) default charset=utf8;

        #!!字段后可以跟 类型,是否null(默认不为空),默认值,auto_increment,primary key
    #create table table_name( 字段1 类型  null,
                            # 字段2 类型  not null,
                            # 字段3 类型 not null default=1,
                            # 字段3 类型 not null auto_increment primary key...
                            # )engine=innodb default charset=utf8;
    # auto_increment 表示：自增   #与primarykey绑定使用,一张表只能有一个自增  （id int auto_increment primary key,）
    # primary key：  表示 约束(不能重复且不能为空); 加速查找 （保证数据的唯一性），一张表只能有一个主键
    #删除表: drop table table_name;
        #查看创建表的语句 show create table table_name;

    #修改字段类型：
        # alter table t1 change id1 id1 int;

#插入
    #insert into table_name(字段1，字段2..) values (列值1，列值2...),(列值1，列值2...) 可插入多行
    #insert into table_name values (列值1，列值2...)要按顺序每列都要
    #INSERT INTO uses SET name = '姚明', age = 25; (不好用,还是用上面的)

#查看
    #select * from table_name where 字段 = '字段值';

#清空表:
    # delete from table_name where 字段 = '字段值';
    #delete from table_name; 删除表全部值  #让id从删除的项开始自增
    #truncate table table_name;            #id会从最原始开始i自增

#更新:
    #update table_name  set 字段 = '新字段值' where 字段 = '字段值';

'''
#修改表的列属性
# alter table 表名 change 原列名 新列名 类型；   --修改表的列属性名
# alter table 表名 modify 列名 类型 ；          --修改表的类类型
# alter table 表名 drop 列名；                    --删除表的某一列
# alter table 表名 add  列名 类型；               --添加某一列
# alter table 表名 rename 新表名；                 --修改表名
'''

# Mysql增加主键或者更改表的列为主键的sql语句

# 添加表字段
    # alter table table1 add transactor varchar(10) not Null;
    # alter table table1 add id int unsigned not Null auto_increment primary key

# 修改某个表的字段类型及指定为空或非空
    # alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
    # alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

# 修改某个表的字段名称及指定为空或非空
    # alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空

# 删除某一字段
    # ALTER TABLE mytable DROP 字段 名;
#
# 添加唯一键
    # ALTER TABLE `test2` ADD UNIQUE ( `userid`)

# 修改主键
    # ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )
#
# 增加索引
    # ALTER TABLE `test2` ADD INDEX ( `id` )

#修改字段类型，并设置主键
    # ALTER TABLE `category ` MODIFY COLUMN `id`  int(11) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`id`);


#建表后创建唯一索引:
#         create unique index UK_student_name on student (name);
# 建表后添加约束
#         alter table student add constraint uk_student_name unique (name);

#1. select now();  打印当前时间

#2. desc table_name; 查看字段的属性
#3. show create table table_name ; 查看表的创建代码
#3. show create table table_name \G ; 竖着看，查看表的创建代码

# desc table_name; 查看列的属性

#自增列的起始值修改：
# show create table table_name \G ; 竖着看，查看表的创建代码
    # 可以看到auto_increment = 数值
    #这个就是自增数列此时下一个要自增的id数
    #修改自增列的值：
            #aler table table_name auto_increment = 你想要的数值;
    #取消自增列:
            #alter table table_name change id id int;

#自增列的步长：
    # 1. mysql ： 基于会话级别，一次mysql登陆使用，就是一次会话
        #show session variables like 'auto_inc%'; 查看步长
        #set session auto_increment_increment=2;  #设置步长
        #set session auto_increment_offset=10;    #设置起始值

            #基于全局级别的，应用于所有会话所有表，【尽量别使用】
            #shwo global  variables like 'auto_inc%';
            #set global auto_increment_increment=2;
            #set global auto_increment_offset=10;

    #2.sql server: 基于表级别可以在表最后
        #)engine=inndb auto_increment=4,步长=5 default charset=utf8;

自增列步长，起始值的修改

#有道笔记20181006 mysql
#唯一索引  ： 看到索引马上想到是 加速查找
                #唯一 ： 约束，不能重复
                #约束功能： 主键，外键

#create table t1( id int auto_increment primary key,
                 # num int,
                 # name char(10),
                 #unique 索引名 (num)  #单列唯一索引
                 #index 索引名 (num)  #创建普通索引
                 #unique 索引名 (num,name) #联合唯一索引： 不能由两行一模一样的 num和name
#

#建表后创建唯一索引:
#         create unique index UK_student_name on student (name);
# 建表后添加约束
#         alter table student add constraint uk_student_name unique (name);
        #要先建联合索引，乖乖删除原来的索引，再操作吧
#删除索引：
        # ALTER TABLE t1 DROP INDEX uq_name;

# ERROR !!! :needed in a foreign key constraint问题
        #要先乖乖删除外键，才能继续操作索引


#唯一索引和主键的区别：
        #唯一索引 可以为空
        #主键 不能为空

 

武老师博客目录：http://www.cnblogs.com/wupeiqi/articles/5729934.html