记得当时腾讯实习生一面的时候被面试官问会不会用数据库，我回答会，但是只会比较基础。。。。最后还被问这么多东西掌握得不好（幸好没用“不会”），为什么不找时间补补。。。。。被狠狠地鄙视。。。
唉，自己的确做的不好。所以把数据库这一块也补补。
虽然之前课程有学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)