（三十五）数据库

一、什么是数据库

数据库管理系统（英语：Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。

二、简单使用

1. 启动/关闭服务

     net start mysql(这个是启动mysqld，在mysqld  --install  <可以自己设置名字>)。

     net stop mysql 关闭服务。

1. 直接使用mysql -uroot -p 命令打开，然后会让用户输入密码。接着就会进入了，然后就可以开始建库，建表，输入数据，修改数据，最后删表，删库走人。
2. 修改密码

（1）在cmd窗口下,不需要连接到mysql里面,也就在外部执行的指令mysqladmin -u root -p旧密码 password 新密码 -----这种方式在没有配置my.ini文件前使用，如果一旦配置了，就不生效了。

（2）set password for root@localhost = password(‘123’) -----进入mysql后才可以修改。

（3）use mysql;  update user set password=password(‘123’) where user = ‘root’;-------直接修改mysql数据库，修改用户的密码。

（4）忘记root密码修改密码（windows）：

　　　　 1. 关闭正在运行的MySQL服务，net stop mysql（这个mysql是你添加的mysqld到系统服务时的服务名）。 
　　　　2. 打开DOS窗口，转到mysql\bin目录。 
　　　　3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证，因为之所以mysql启动之后，客户端连接的时候需要登陆认证，输入密码什么的，是因为mysql服务端启动的时候，加载了自己内部的一些权限相关信息的授权表、权限认证表什么的，这样就要求客户端必须有认证，如果启动的时没有加载这些表和设置，那么我们客户端再进行登陆的时候，就不需要认证了，那么就可以登陆上了，登陆之后，我们到mysql这个存有所有用户信息的表中去修改root用户或者别的用户的密码了，还是比较6的，但是这样搞需要关闭服务端，在实际工作中想关闭mysql服务？？你觉得可能吗？？记住这个问题，我后面给大家解决。 

　　　注意一个问题，如果我们直接使用的上面这个指令，也即是mysqld --skip-grant-tables，也就是直接通过mysqld启动的mysql服务的话，我们就不能通过net stop mysql的方式来关闭mysql服务了。但是可以通过别的方式来关闭，我在安装mysql的那篇博客里面写到了，杀进程的方式，知道你肯定忘记了，再给你写一下（win10）：tasklist |findstr mysqld找到这个mysqld服务的端口号，然后taskkill /F /PID 端口号来杀死这个mysql服务的进行，以后就可以使用net start/stop mysql的方式来启动和关闭了。

                        

 

               4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。 
　　　　5. 输入mysql回车，如果成功，将出现MySQL提示符 >。 
　　　　6. 连接权限数据库： use mysql; 。 
　　　　6. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。 
　　　　7. 刷新权限（必须步骤）：flush privileges;　。凡是涉及到密码修改或者后面我们会学到的权限修改，修改完之后全部要再执行一下这一句。 
　　　　8. 退出 quit。 
　　　　9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

 

（5）在初始文件中设置账号密码

 

                                                                                                           

在这个根目录下找my.ini，如果没有就自己创建一个，文件名是my，文件的后缀名是ini。在里面配置一句话：

[mysql]

user=root #root是用户名

password=000  #这里的000就是密码

#配置客户端连接的时候，指定一下用户名和密码，那么我们在进行mysql客户端连接的时候，直接输入mysql然后回车就可以了，并且用户是我们指定的root用户。

 

三、设置字符集

 

     在我们创建数据库和表时，mysql会为我们默认配置一个字符集----latin1。但是我们把汉字存入数据库后，就会乱码。为了解决这个问题，下面设置一下编码问题。

 

1. 在插入数据之前，先执行一条指令：“set  names  latin1；”，临时修改客户端的字符集，但是当关闭DOS窗口，再次进来添加汉字时，还是会乱码。
2. 在配置文件里面修改客户端和服务端参数，可以实现set names latin1；的效果，并且永久生效。

 

     这句话放在[mysql]下 default-character-set=utf8。

 

再添加上下面三行，为服务器设置编码格式：

 

[mysqld]

character_set_server=utf8

collation-server=utf8_general_ci

 

 #就是一个校对规则，一般默认都是这个，如果不是就改成这个就可以了，所以直接写上就行了，这个规则后面我们会讲的~~~

 

添加下面两行目的是为了，来客户端了解时，编码设置为utf-8:

 

[client]

default-character-set=utf8

上面内容最好放到[mysqld]上面，否则可能会报出下面的错误：

mysql: unknown variable 'sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'

 

来张图卡一下：

 

                    

 

上面的文件配置完毕后，需要重启一下服务，然后再次进来时，就不需要输入mysql -uroot -p ，直接输入mysql就可以进入了。

 

四、基本数据类型

 

    数值类型（整数类型，浮点类型），字符类型，日期时间类型，枚举类型，集合类型

 

1. 数据类型

                                                                              

2.日期类型

     YEAR

            YYYY（范围：1901/2155）2018

 

        DATE

            YYYY-MM-DD（范围：1000-01-01/9999-12-31）例：2018-01-01

 

        TIME

            HH:MM:SS（范围：'-838:59:59'/'838:59:59'）例：12:09:32

 

        DATETIME

            YYYY-MM-DD HH:MM:SS（范围：1000-01-01 00:00:00/9999-12-31 23:59:59    Y）例： 2018-01-01 12:09:32

 

        TIMESTAMP

            YYYYMMDD HHMMSS（范围：1970-01-01 00:00:00/2037 年某时）

3.字符串类型

Char 和varchar，前者是不可变长的，定义为多少，在内存中就占多少；varchar是可变长度的，但是为了读取准确，会在每个字符串前面添加一个这个字符串长度的记录，因此也占了一位。

看下表，说明了这两者的特点与区别：

                                                        

再来看看其他的字符串类型：

                                                   

两者的区别：1.读取速度char大于varchar，因为varchar读取时还得使用开始的长度；2.存储方式，char根据宽度开辟存储空间可以按照宽度直接取值，varchar会按照存入的长度进行开辟空间，为了识别存入的内容会在最开始的位置添加此段内容的长度。3.char在存储空间的宽度是不可变的，varchar是可变的。

4.枚举类型和集合类型

 

枚举类型（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');

 

　　

集合类型（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');

　　

5.严格模式

严格模式如果级别较低，有些情况下会导致明明错了，但是却并不会报错。例如：设置了，字段不为空，但是如果模式不严格，那么不会报错，int型的会默认给个0，其他的会默认给个NULL。

 

好了设置一下模式吧：

 

 方式一：先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';，例如：set session sql_mode='STRICT_TRANS_TABLES';改为严格模式

 

        此方法只在当前会话中生效，关闭当前会话就不生效了。

 

方式二：先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set global sql_mode = '修改后的值'。

 

        此方法在当前服务中生效，重新MySQL服务后失效

 

 

 

方法三：在mysql的安装目录下，或my.cnf文件(windows系统是my.ini文件)，新增 sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，

 

        添加my.cnf如下：

 

        [mysqld]

 

        sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

 

 

 

        然后重启mysql。

 

    此方法永久生效.当然生产环境上是禁止重启MySQL服务的，所以采用方式二加方式三来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了。

 

 

 

sql_mode常用值如下: 
    ONLY_FULL_GROUP_BY:
    对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
 
    NO_AUTO_VALUE_ON_ZERO:
    该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
 
    STRICT_TRANS_TABLES:
    在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
    NO_ZERO_IN_DATE:
    在严格模式下,不允许日期和月份为零
 
    NO_ZERO_DATE:
    设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
 
    ERROR_FOR_DIVISION_BY_ZERO:
    在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL
 
    NO_AUTO_CREATE_USER:
    禁止GRANT创建密码为空的用户
 
    NO_ENGINE_SUBSTITUTION:
    如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
 
    PIPES_AS_CONCAT:
    将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
 
    ANSI_QUOTES:
    启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

　　

五、数据库操作

1. 创建数据库

CREATE DATABASE 数据库名 <charset utf8,如果my.ini已经配置过，不需要。没有配置过，可以写，也可以不写。>

         2.数据库命名规则

　　　　　　可以由字母、数字、下划线、＠、＃、＄

　　　　　　区分大小写

　　　　　　唯一性

　　　　　　不能使用关键字如 create select等

　　　　　　不能单独使用数字

　　　　　　最长128位

         3.数据库相关操作

　  1） 查看数据库

show databases;

show create database 数据库名;

select database();

          2） 选择数据库

USE 数据库名

          3） 删除数据库

DROP DATABASE 数据库名;

          4） 修改数据库

alter database 数据库名 charset utf8;

 

六、表操作

 

1. 存储引擎即是表类型，mysql根据不同的表类型会有不同的处理机制。

  现在常用的引擎有：InnoDB,MyISAM等。现在，mysql默认使用的是InnoDB,这种引擎，这种引擎，使用的是行锁定，也就是如果有一个客户端使用某一行数据了，其他的客户端就不能使用。支持事物处理。

  MyISAM支持表锁定。

1. 新建表

  #语法：

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

#注意：

(1)在同一张表中，字段名是不能相同

(2)宽度和约束条件可选、非必须，宽度指的就是字段长度约束，例如：char(10)里面的10

(3) 字段名和类型是必须的

例如：

 

mysql> create table t1(  
    -> id int, 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> age int(3)
    -> );

　　

注意：每个字段后面都要使用逗号隔开，最后结尾时，要使用分号。逗号和分号都是英文状态下的。

3.插入数据到表中

插入数据的sql语句：

 

mysql> insert into t1 values
    -> (1,'zhangsan',18,'male'),
    -> (2,'lisi',81,'female')
    -> ;

 

　　

注意：插入的每条消息后都要使用逗号分隔，最后结尾时使用分号。逗号和分号也必须是英文状态下的。

4.查看表结构

（1）describe t1; #查看表结构，可简写为：desc 表名
会展示出来表中字段的信息和一切要求。
（2）show create table t1\G; #查看表详细结构，可加\G

　　

5.表的完整性约束

 

1）not null 与default
       create table tb1(
　　　　 nid int not null defalut 2,#nid设置为int型，不可为空（not null），default 默认值设置为2
　　　　 num int not null #num设置为int型，并且不可为空
　　	     )；
（2）unique----唯一
   1）设置唯一
    方法一：
create table department1(
id int,
name varchar(20) unique,#name字段必须是唯一的不可重复的
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)#name字段必须是唯一的不可重复的
);
2）联合唯一
  create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一);#两者必须同时与已存在的数据一样时才会报错

 

　　

七、修改表

语法：1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;
2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…], #注意这里可以通过逗号来分割，一下添加多个约束条件                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST; #添加这个字段的时候，把它放到第一个字段位置去。      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;#after是放到后的这个字段的后面去了，我们通过一个first和一个after就可以将新添加的字段放到表的任意字段位置了。
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;
4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];  #change比modify还多了个改名字的功能，这一句是只改了一个字段名      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];#这一句除了改了字段名，还改了数据类型、完整性约束等等的内容

　　

 

 

 

转载于:https://www.cnblogs.com/asia-yang/p/10356240.html