MySQL学习记录1（基础）（MySQL in a nutshell）_MySQL

记得当时腾讯实习生一面的时候被面试官问会不会用数据库，我回答会，但是只会比较基础。。。。最后还被问这么多东西掌握得不好（幸好没用“不会”），为什么不找时间补补。。。。。被狠狠地鄙视。。。

唉，自己的确做的不好。所以把数据库这一块也补补。

虽然之前课程有学SQLServer，但是很多时候都只是应付一下考试，不过有对数据库的初步认识。打算通过《MySQL in a nutshell》来学习MySQL。

这篇文章主要是记录基础用法。。。。。

MySQL的安装：

在Debian里我直接通过aptitude install mysql 安装。。。。。好像没有难度。

MySQL的配置文件：

在Debian里，MySQL的配置文件为/etc/mysql/my.cnf （内容如下：）

lancelot@debian:~/Code/Mysql$ cat /etc/mysql/my.cnf ## The MySQL database server configuration file.## You can copy this to one of:# - "/etc/mysql/my.cnf" to set global options,# - "~/.my.cnf" to set user-specific options.# # One can use all long options that the program supports.# Run program with --help to get a list of available options and with# --print-defaults to see which it would actually understand and use.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html# This will be passed to all mysql clients# It has been reported that passwords should be enclosed with ticks/quotes# escpecially if they contain "#" chars...# Remember to edit /etc/mysql/debian.cnf when changing the socket location.[client]port        = 3306socket        = /var/run/mysqld/mysqld.sock# Here is entries for some specific programs# The following values assume you have at least 32M ram# This was formally known as [safe_mysqld]. Both versions are currently parsed.[mysqld_safe]socket        = /var/run/mysqld/mysqld.socknice        = 0[mysqld]## * Basic Settings#user        = mysqlpid-file    = /var/run/mysqld/mysqld.pidsocket        = /var/run/mysqld/mysqld.sockport        = 3306basedir        = /usrdatadir        = /var/lib/mysqltmpdir        = /tmplc-messages-dir    = /usr/share/mysqlskip-external-locking## Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.bind-address        = 127.0.0.1## * Fine Tuning#key_buffer        = 16Mmax_allowed_packet    = 16Mthread_stack        = 192Kthread_cache_size       = 8# This replaces the startup script and checks MyISAM tables if needed# the first time they are touchedmyisam-recover         = BACKUP#max_connections        = 100#table_cache            = 64#thread_concurrency     = 10## * Query Cache Configuration#query_cache_limit    = 1Mquery_cache_size        = 16M## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file        = /var/log/mysql/mysql.log#general_log             = 1## Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.## Here you can see queries with especially long duration#log_slow_queries    = /var/log/mysql/mysql-slow.log#long_query_time = 2#log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about#       other settings you may need to change.#server-id        = 1#log_bin            = /var/log/mysql/mysql-bin.logexpire_logs_days    = 10max_binlog_size         = 100M#binlog_do_db        = include_database_name#binlog_ignore_db    = include_database_name## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!## * Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI "tinyca".## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem[mysqldump]quickquote-namesmax_allowed_packet    = 16M[mysql]#no-auto-rehash    # faster start of mysql but no tab completition[isamchk]key_buffer        = 16M## * IMPORTANT: Additional settings that can override those from this file!#   The files must end with '.cnf', otherwise they'll be ignored.#!includedir /etc/mysql/conf.d/

该配置文件是可以进行修改的。。。。。

首先来检查一下MySQL的守护进程是否运行：

lancelot@debian:~/Code/Mysql$ ps -e | grep mysqld 3003 ?        00:00:00 mysqld_safe 3374 ?        00:00:31 mysqld

登录：

lancelot@debian:~/Code/Mysql$ mysql -u root -p

输入密码后就登录成功！登录后的界面如下：

Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 44Server version: 5.5.35-0+wheezy1 (Debian)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql>

查看存在的用户：

mysql> SELECT User, Host FROM mysql.user;+------------------+---------------------------+| User             | Host                      |+------------------+---------------------------+| rick             | %.wiley.com               || root             | 127.0.0.1                 || rick             | 192.168.0.0/255.255.255.0 || root             | ::1                       ||                  | debian                    || root             | debian                    ||                  | localhost                 || debian-sys-maint | localhost                 || rick             | localhost                 || root             | localhost                 || wordpress        | localhost                 |+------------------+---------------------------+11 rows in set (0.00 sec)

修改用户密码：

mysql> SET PASSWORD FOR 'rick'@'localhost'=PASSWORD('615857');Query OK, 0 rows affected (0.00 sec)

添加用户：

mysql> GRANT SELECT ON *.* TO 'alan'@'localhost' IDENTIFIED BY '615857';Query OK, 0 rows affected (0.00 sec)mysql> SELECT User, Host FROM mysql.user;+------------------+---------------------------+| User             | Host                      |+------------------+---------------------------+| rick             | %.wiley.com               || root             | 127.0.0.1                 || rick             | 192.168.0.0/255.255.255.0 || root             | ::1                       ||                  | debian                    || root             | debian                    ||                  | localhost                 || alan             | localhost                 || debian-sys-maint | localhost                 || rick             | localhost                 || root             | localhost                 || wordpress        | localhost                 |+------------------+---------------------------+12 rows in set (0.00 sec)

添加数据库并查看当前拥有的数据库：

mysql> CREATE DATABASE book;Query OK, 1 row affected (0.01 sec)mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || book               || bookstore          || mysql              || performance_schema || rick               || student            || test               || wordpress          |+--------------------+9 rows in set (0.00 sec)

使用数据库并创建表：

mysql> USE book;Database changedmysql> CREATE TABLE books (    -> book_id INT,    -> title VARCHAR(50),    -> author VARCHAR(50));Query OK, 0 rows affected (0.20 sec)

创建一个记录书本的表，其中包含书本的id（整型），书名（50个字符长的字符串），作者（50个字符长的字符串）。

查看表的信息：

mysql> DESCRIBE books;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| book_id | int(11)     | YES  |     | NULL    |       || title   | varchar(50) | YES  |     | NULL    |       || author  | varchar(50) | YES  |     | NULL    |       |+---------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

其中Key为主键，Extra为额外属性。

接下来因为觉得很多语句如果直接在mysql命令行里直接敲出错的时候又要重新敲一遍不太方便，所以将语句放在一个文件，在mysql执行该文件。

修改表的信息：

sql文件：

ALTER TABLE booksCHANGE COLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,CHANGE COLUMN author author_id INT,ADD COLUMN description TEXT,ADD COLUMN genre ENUM('novel','poetry','drama'),ADD COLUMN publisher_id INT,ADD COLUMN pub_year VARCHAR(4),ADD COLUMN isbn VARCHAR(20);

执行文件后，表的变化：

mysql> /. 1.sqlQuery OK, 0 rows affected (0.31 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESCRIBE books;+--------------+--------------------------------+------+-----+---------+----------------+| Field        | Type                           | Null | Key | Default | Extra          |+--------------+--------------------------------+------+-----+---------+----------------+| book_id      | int(11)                        | NO   | PRI | NULL    | auto_increment || title        | varchar(50)                    | YES  |     | NULL    |                || author_id    | int(11)                        | YES  |     | NULL    |                || description  | text                           | YES  |     | NULL    |                || genre        | enum('novel','poetry','drama') | YES  |     | NULL    |                || publisher_id | int(11)                        | YES  |     | NULL    |                || pub_year     | varchar(4)                     | YES  |     | NULL    |                || isbn         | varchar(20)                    | YES  |     | NULL    |                |+--------------+--------------------------------+------+-----+---------+----------------+8 rows in set (0.00 sec)

将book_id设置为主键并且自动增加，修改作者的名字变成作者ID，添加书本的描述，添加书本的类型，添加出版社ID，添加出版年份，添加书本isbn

添加作者表：

sql文件：

CREATE TABLE authors(author_id INT AUTO_INCREMENT PRIMARY KEY,author_last VARCHAR(50),author_first VARCHAR(50),country VARCHAR(50));

执行后结果：

mysql> /. 2.sqlQuery OK, 0 rows affected (0.13 sec)mysql> DESCRIBE authors;+--------------+-------------+------+-----+---------+----------------+| Field        | Type        | Null | Key | Default | Extra          |+--------------+-------------+------+-----+---------+----------------+| author_id    | int(11)     | NO   | PRI | NULL    | auto_increment || author_last  | varchar(50) | YES  |     | NULL    |                || author_first | varchar(50) | YES  |     | NULL    |                || country      | varchar(50) | YES  |     | NULL    |                |+--------------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

显示当前的所有表：

mysql> SHOW TABLES;+----------------+| Tables_in_book |+----------------+| authors        || books          |+----------------+2 rows in set (0.00 sec)