偶然的机会需要接触MySQL,一番较量下来，倒也顺利，利用周未整理了一下，摘录一些内容如下：

[@more@]

如何安装和配置？
在mysql网站上注册用户wworacle@gmail.com/oracle
下载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表中增加了用户'ww'@'%'
（注：mysql> create user 'ww'@'localhost' identified by 'ww';
结果是在mysql.user表中增加了用户'ww'@'localhost'）

如何删除一个用户？
mysql> drop user 'ww'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'ww'; 结果是删除了用户'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

ITPub博客

MySQL入门笔记2

最新文章