Mysql初学总结（2）

Mysql初学（2）
上次学完后，已经成功安装了MySQL服务区，接下来先查看一下Mysql的错误日志文件（error_log）：
在mysql数据库中，错误日志功能是默认开启的。默认情况下，错误日志存储在mysql数据库的数据目录中。
错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过log-error参数来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、在从服务器上启动服务器进程时产生的信息。
查看mysql错误日志的定义信息
mysql> show global variables like ‘%log_error%’;

其中
log_error定义为错误日志文件路径。
log_error_verbosity:
The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity。There are three possible values, as documented in the manual:
Verbosity Value Message Types Logged
1 Errors only
2 Errors and warnings
3 Errors, warnings, and notes (default)
更改错误日志位置可以使用log-error来设置形式如下
#vi /etc/my.cnf
log-error = /data/mysql/log/mysqld.log
查看mysql错误日志：可以使用tail或grep、cat等命令查看。
为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候
就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。
删除错误日志：
在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –u root –p flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。
在mysql5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的：方式如下：

接下来就是常规的增删改查

1. 查看数据库
   查看数据库有3种方式
   第一种直接进入数据库查看
   mysql> show databases;
   ±-------------------+
   | Database |
   ±-------------------+
   | information_schema |
   | mysql |
   | performance_schema |
   | sys |
   ±-------------------+
   4 rows in set (0.00 sec)
   1：information_schema这个数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型，访问权限等。 [ˈskimə]
   2：performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表，
   3：mysql库是系统库，里面保存有账户信息，权限信息等。
   4：mysql5.7增加了sys 系统数据库，sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息,元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。
   第二种 以行的方式显示
   mysql> show databases \G #以行的方式显示
   *************************** 1. row ***************************
   Database: information_schema
   *************************** 2. row ***************************
   Database: mysql
   *************************** 3. row ***************************
   Database: performance_schema
   *************************** 4. row ***************************
   Database: sys
   4 rows in set (0.00 sec)
   第三种 在shell中查看
   mysql -e后面直接跟sql语句，这种方式一般是在shell脚本中用到
   [root@cong11 ~]# mysql -e ‘show databases’ -uroot -p123456
   mysql: [Warning] Using a password on the command line interface can be insecure.
   ±-------------------+
   | Database |
   ±-------------------+
   | information_schema |
   | mysql |
   | performance_schema |
   | sys |
   ±-------------------+
2. 创建数据库
   语法
   create database 数据库名;
   创建数据库注意事项
3. 在文件系统中，MySQL的数据存储区以目录方式表示MySQL数据库。因此，上面命令中的数据库名字必须与操作系统的约束的目录名字一致。例如不允许文件和目录名中有,/,:,*,?,”,<,>,|这些特殊符号，在MySQL数据库名字中这些字母会被自动删除。
   [root@cong11 ~]# ls /data/mysql/data/
   auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema sys
4. 数据库的名字不能超过64个字符，包含特殊字符的名字或者是全部由数字或保留字组成的名字必须用反引号``括起来。
5. 数据库不能重名
   创建一个数据库
   mysql> create database HA;
   Query OK, 1 row affected (0.00 sec)
   mysql> create database HA-test;
   Query OK, 1 row affected (0.00 sec)
   查看数据库
   mysql> show databases;

查看数据库目录
我们可以去数据目录下查看新创建的数据库目录
[root@cong11 ~]# ls /data/mysql/data/

注：建议数据名不要包含特殊符号或是纯数字的。
选择要操作的数据库
我们需要使用哪个数据库，就用use进行选择，后面的操作默认都是在被选择的数据库中进行操作。
mysql> use HA-test;
Database changed
查看自己在所处的位置
mysql> select database();

在命令行选择默认的数据库
我们也可以在命令行直接选择我们需要进入的数据库
[root@cong11 ~]# mysql -uroot -p123456 HA-test
mysql> select now(),user(),database();

删除数据库

1. 命令
   mysql> drop database HA-test;
   Query OK, 0 rows affected (0.01 sec)
   或者 移动数据库目录
   上面删除数据库的方法，不安全，删除完了，无法恢复，在工作中我们可以移动数据库目录到别的地方，防止删除数据库了想要恢复。
   [root@cong11 ~]# mkdir /db_backup
   [root@cong11 ~]# mv /data/mysql/data/HA /db_backup
   [root@cong11 ~]# mysql -uroot -p123456
   mysql> show databases; #发现HA数据库已经不见了

2. 使用IF EXISTS 子句以避免删除不存在的数据库时出现的MySQL错误信息 exists [ɪɡˈzɪst]
   mysql> drop database if exists HA-test; #如果存在则删除
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   也可以在创建数据库时使用
   mysql> create database if not exists HA; #if not exists 如果不存在则创建
   Query OK, 1 row affected (0.00 sec)

3. 关于表的操作

   1. 创建表create：
      语法：create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);
      mysql> use HA;
      Database changed
      mysql> create table student(id int(20),name char(40),age int);
      Query OK, 0 rows affected (0.02 sec)

   2. 查看表相关信息
      mysql> use HA;
      Database changed
      mysql> show tables;

   3. 查看表结构
      使用desc 命令来查看表的结构
      mysql> desc student;

      注：还可以用以下命令查看表结构，会一种就可以。
      mysql> explain mysql.user;
      mysql> show columns from mysql.user;
      mysql> show fields from mysql.user;
      mysql> show columns from mysql.user like ‘%user’;

4. 查看创建表执行了哪些命令：
   mysql> show create table student \G

5. 指定默认存储引擎和字符集
   新建一个表，指定默认的存储引擎为InnoDB,编码为utf8
   mysql> create table student2(id int(20),name char(40),age int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   Query OK, 0 rows affected (0.02 sec)
   mysql> show create table student2 \G

6. 删除表
   mysql> drop table student2;
   Query OK, 0 rows affected (0.01 sec)

7. 禁止预读表信息
   没有禁止前转换数据库会有提示信息
   mysql> use mysql;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with –A
   解决这个问题可以在登陆mysql的时候添加参数-A
   [root@cong11 ~]# mysql -uroot -p123456 -A
   mysql> use mysql;
   Database changed #发现没有提示信息了

8. 修改表名称 alter
   语法：alter table 表名 rename 新表名;
   修改HA库中student表名为students
   mysql> alter table student rename students;
   Query OK, 0 rows affected (0.02 sec)
   mysql> show tables;

9. 修改表中的字段类型
   语法：alter table 表名 modify 要修改的字段名 要修改的类型;
   查看students表结构
   mysql> desc students;

10. 修改字段id 的int（20）字段类型为int（10）
    mysql> alter table students modify id int(10);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    查看修改完的students表结构
    mysql> desc students;

11. 修改表中的字段类型和字段名称
    语法：alter table 表名 change 原字段名 新字段名 新字段类型;
    mysql> alter table students change name stname char(20);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> desc students;

    注：CHANGE 和MODIFY的区别：
    CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）

12. 在表中添加字段：
    语法：alter table 表名 add字段名 字段类型；
    enum #枚举类型，比如性别，只能在男女选择，是男非女，是女非男
    mysql> alter table students add sex enum(‘M’,‘W’);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> desc students;

13. 在表中指定位置添加字段
    1. 在第一列添加一个字段
    mysql> alter table students add uid int(10) first;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> desc students;

    2. 在age后面添加一个address字段
       mysql> alter table students add address char(40) after age;
       Query OK, 0 rows affected (0.01 sec)
       Records: 0 Duplicates: 0 Warnings: 0

14. 删除表中字段
    语法：alter table 表名 drop 字段名 ;
    mysql> alter table students drop address;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> desc students;
    发现表中address字段不见了

15. 关于表中记录的操作

16. 插入<记录>INSERT
    INSERT INTO 语句用于向表中插入新的行。
    语法：insert into 表名values (字段值1,字段值2, 字段值3);
    插入记录时values所指定的值要和表中字段的个数、顺序以及类型要一一对应。
    先删除students表，再创建
    mysql> drop tables students;
    Query OK, 0 rows affected (0.01 sec)
    mysql> create table students(id int(20),name char(40),age int);
    Query OK, 0 rows affected (0.01 sec)
    mysql> insert into students values(1,‘zhangs’,21);
    Query OK, 1 row affected (0.01 sec)

17. 同时插入多条记录
    mysql> insert into students values(2,‘lis’,24),(3,‘wange’,26);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2 Duplicates: 0 Warnings: 0

18. 分开插入表记录
    我们也可以指定所要插入数据的字段：
    语法：
    INSERT INTO table_name (字段1, 字段2,…) VALUES (字段值1, 字段值2,…)
    例如：向students表中的id,name字段插入数据
    mysql> insert into students (id,name)values(4,‘hangl’);
    Query OK, 1 row affected (0.00 sec)

19. 查询表中记录select
    SELECT 语句用于从数据库表中读取数据。
    语法：
    select * from 表名； # *号表示表中所有的字段

20. 查询student表中所有记录
    mysql> select * from students;

21. 当表中记录比较多时可以使用\G查看
    mysql> select * from student\G

22. 只查询表中某个字段或某些字段的内容
    mysql> select name from students;

    mysql> select id,name from students;

23. 查看别的数据库的表或者不在本数据库上进行查看
    语法：SELECT 字段 FROM 数据库名.表名;
    效果等同于先使用use数据库，然后再看看表内容
    mysql> select * from HA.students;

24. 删除表中的记录
    DELETE语句用于删除表中的记录。
    语法:
    DELETE FROM table_name WHERE some_column=some_value;
    请注意 SQL DELETE 语句中的 WHERE 子句！
    WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

    1. 删除students表中id为3的行
       mysql> delete from students where id=3;
       Query OK, 1 row affected (0.00 sec)
       mysql> select * from students; #发现表中id为3的记录不见了
       ±-----±-------±-----+
       | id | name | age |
       ±-----±-------±-----+
       | 1 | zhangs | 21 |
       | 2 | lis | 24 |
       | 4 | hangl | NULL |
       ±-----±-------±-----+
       3 rows in set (0.00 sec)
    2. 删除age为空的行
       mysql> delete from students where age is null;
       Query OK, 1 row affected (0.00 sec)
       mysql> select * from students;
       ±-----±-------±-----+
       | id | name | age |
       ±-----±-------±-----+
       | 1 | zhangs | 21 |
       | 2 | lis | 24 |
       ±-----±-------±-----+
       2 rows in set (0.00 sec)

25. 更新记录
    update 语句用于修改表中的数据。
    语法：
    UPDATE table_name
    SET column1=value1,column2=value2,…
    WHERE some_column=some_value;
    请注意 SQL UPDATE 语句中的 WHERE 子句！
    WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

    1. 把表中id为2的记录age更新为25
       mysql> update students set age=25 where id=2;
       Query OK, 1 row affected (0.01 sec)
       Rows matched: 1 Changed: 1 Warnings: 0
       mysql> select * from students;
       ±-----±-------±-----+
       | id | name | age |
       ±-----±-------±-----+
       | 1 | zhangs | 21 |
       | 2 | lis | 25 |
       ±-----±-------±-----+
       2 rows in set (0.01 sec)

    2. 把表中所有的id都更新为2
       mysql> update students set id=2;
       Query OK, 1 row affected (0.01 sec)
       Rows matched: 2 Changed: 1 Warnings: 0
       mysql> select * from students;

    3. 同时更新多个字段的值，请使用逗号隔开
       mysql> update students set id=1,name=‘zhangsan’ where age=21;
       Query OK, 1 row affected (0.01 sec)
       Rows matched: 1 Changed: 1 Warnings: 0
       mysql> select * from students;