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

浏览数：24 / 时间：2015年06月12日

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

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

虽然之前课程有学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        = 3306
socket        = /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.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-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        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_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.log
expire_logs_days    = 10
max_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]
quick
quote-names
max_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/

View Code

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

首先来检查一下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 44
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.

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 changed
mysql> 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 books
CHANGE 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.sql
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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.sql
Query 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)