偶然的機會需要接觸MySQL,一番較量下來，倒也順利，利用周未整理了一下，摘錄一些內容如下：

[@more@]

如何安裝和配置？
在mysql網站上註冊使用者
下載5.0.37-community版本的非安裝包，我是為了省事，才下載的非安裝版
並解壓到c:mysql

1 修改c:windowsmy.ini,指明basedir和datadir
# set basedir to your installation path
basedir=c:/mysql
# set datadir to the location of your data directory
# datadir=E:/mydata/data 如果把資料庫放到非basedir/data目錄下需要設定此引數，我準備把稅局的庫拷到預設目錄下，所以沒有改
default-character-set=gbk 後來加的，打算解決亂碼問題，沒有看出有什麼作用

2 C:mysqlbin>mysqld --console
3 新開一個cmd 關閉服務
C:mysqlbin>mysqladmin -u root shutdown
4 加入windows的服務
C:mysqlbin>mysqld --install
Service successfully installed.

5 你還可以將MySQL bin目錄的路徑加到Windows系統PATH環境變數中

資料庫安裝完後，預設有兩個資料庫information_schemamysqltest
資料庫的使用者root，預設無口令

如何查詢現在有幾個資料庫？
C:>mysqlshow -u root
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| shuiwuju |
| test |
+--------------------+
連線伺服器後也可以查詢：
mysql> show databases;
The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.
mysql儲存伺服器使用者許可權所用，test供使用者練習用

如何連線MYSQL伺服器？
C:>mysql -h localhost -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 13
Server version: 5.0.37-community MySQL Community Edition (G

Type 'help;' or 'h' for help. Type 'c' to clear the buffe

如何連線資料庫？
mysql> connect shuiwuju
Connection id: 14
Current database: shuiwuju

mysql> select version();
+------------------+
| version() |
+------------------+
| 5.0.37-community |
+------------------+
1 row in set (0.00 sec)

mysql>

如何發出查詢？
不連線資料庫，也可以查詢以下內容：
version(),user(),current_date(),now(),(4+3)*2等等，語法如下：
select version(),now();
select verion(),
now()
;

select 'a
b'
;

In MySQL, you can write strings surrounded by either ‘'’ or ‘"’ characters (for example, 'hello' or "goodbye"), and mysql lets you enter strings that span multiple lines. When you see a '> or "> prompt,

如何切換資料庫？
mysql> use test
Database changed
mysql> use shuiwuju
Database changed
use命令與quit exit 一樣不需要分號結束，要分號也可以執行

如何建立使用者？
c:>mysql -h localhost -u root
mysql> create user ww identified by 'ww';
Query OK, 0 rows affected (0.01 sec)
口令上要加引號,結果是在mysql.user表中增加了使用者
（注：mysql> create user identified by 'ww';
結果是在mysql.user表中增加了使用者）

如何刪除一個使用者？
mysql> drop user ;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'ww'; 結果是刪除了使用者
Query OK, 0 rows affected (0.00 sec)

如何給使用者授權訪問某個資料庫？
grant all on menagerie.* to ww;

如何建立menagerie資料庫?
以root使用者，建立ww使用者，並賦權給ww;然後以ww使用者登入，建立menagerie庫
create user ww identified by 'ww';
grant all on menagerie.* to ww;
mysql -u ww -p
create database menagerie;
use menagerie

如何在進入伺服器時直接連線某個資料庫？
c:> mysql -h localhost -u ww -p menagerie

如何建立表？如何顯示一個表的表結構？
mysql> create table pet (
-> name varchar(20),
-> owner varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date
-> );
Query OK, 0 rows affected (0.47 sec)

mysql> update pet set birth='1979-08-31',death='1995-07-29' where name='Bowser';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
英文原文：
To load the text file pet.txt into the pet table, use this command:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
Note that if you created the file on Windows with an editor that uses rn as a line terminator, you should use:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY 'rn';
(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY 'r'.)

如何清空一個表？
mysql> truncate table pet;
Query OK, 0 rows affected (0.00 sec)

查詢語句example:
mysql> select * from pet where birth>='1998-1-1';
mysql> select distinct owner from pet;
mysql> select name,species,birth from pet
-> order by species,birth desc;

select name,birth,now(),
(year(curdate())-year(birth))-(right(curdate(),5)from pet;

如何在where中使用萬用字元？
一是使用like 關鍵字，搭配% _
mysql> select * from pet where name like 'f%';
+---------+---------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+---------+---------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
+---------+---------+---------+------+------------+-------+
二是使用regexp關鍵字，搭配^[a-z]等正規表示式
mysql> select * from pet where name regexp '^[a-c]';
一些正規表示式的規則:
Some characteristics of extended regular expressions are:
‘.’ matches any single character.
A character class ‘[...]’ matches any character within the brackets. For example, ‘[abc]’ matches ‘a’, ‘b’, or ‘c’. To name a range of characters, use a dash. ‘[a-z]’ matches any letter, whereas ‘[0-9]’ matches any digit.
‘*’ matches zero or more instances of the thing preceding it. For example, ‘x*’ matches any number of ‘x’ characters, ‘[0-9]*’ matches any number of digits, and ‘.*’ matches any number of anything.
A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
To anchor a pattern so that it must match the beginning or end of the value being tested, use ‘^’ at the beginning or ‘$’ at the end of the pattern.

mysql> select * from pet where name regexp '^.....$'; 顯示名字只有5個字元的記錄

mysql> select p1.name,p1.sex,p2.name,p2.sex,p1.species
-> from pet as p1 inner join pet as p2
-> on p1.species=p2.species and p1.sex='f' and p2.sex='m';
+---------+------+---------+------+---------+
| name | sex | name | sex | species |
+---------+------+---------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
| Fluffy | f | ?? | m | cat |
+---------+------+---------+------+---------+
4 rows in set (0.00 sec)

如何檢視當前連線的是哪個資料庫？
mysql> select database();
+------------+
| database() |
+------------+
| menagerie |
+------------+
1 row in set (0.00 sec)

連線資料庫後，如何檢視有幾個表？
show tables;

如何執行MYSQL的批處理檔案？
shell> mysql < batch-file
C:> mysql -e "source batch-file"
shell> mysql -h host -u user -p < batch-file
Enter password: ********

shell> mysql < batch-file | more
You can catch the output in a file for further processing:
shell> mysql < batch-file > mysql.out

You can also use scripts from the mysql prompt by using the source or . command:
mysql> source filename;
mysql> . filename

MySQL入門筆記2

相關文章