MySQL 安装和使用经验

最新推荐文章于 2025-11-25 12:10:59 发布

原创最新推荐文章于 2025-11-25 12:10:59 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #database #table #null #shell #join

-> MySQL 专栏收录该内容

2 篇文章

订阅专栏

博客涉及MySQL数据库相关内容，包含表、空值、连接操作等方面，还提及了shell相关知识，这些都是信息技术领域数据库操作中的重要部分。

A.1. 安装

Windows

bin/mysqld-nt --install               # Install MySQL as a service
NET START mysql
NET STOP mysql
bin/mysqld-nt --remove                # remove MySQL as a service

Unix

# groupadd mysql
# useradd -g mysql mysql
# 源代码安装模式
	# tar zxvf mysql-xxx.tar.gz
	# cd mysql-xxx/
	# ./configure --prefix=/usr/local/mysql --localstatedir=/var/db/mysql
	# make
	# make install
# 二进制安装模式
	# cd /usr/local
	# gunzip < /path/to/mysql-xxx.tar.gz | tar xvf -
	# ln -s mysql-xxx mysql
# cd /usr/local/mysql
# scripts/mysql_install_db
# chown -R root:mysql  /usr/local/mysql
# chown -R mysql PATH_TO_MYSQL_DB

# cp surrport_files/mysql.server  /etc/rc.d/init.d/mysqld
# chmod a+x /etc/rc.d/init.d/mysqld
# ln -s /etc/rc.d/init.d/mysqld  /etc/rc.d/rc3.d/S98mysql
# /etc/rc.d/init.d/mysqld start
# 则启动进程：
	/usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf 
	--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 
	--user=mysql --pid-file=/usr/local/mysql/data/ltcvs.pid --skip-locking

Since MySQL Version 3.22, read default startup options for the server and for clients from option files. Location: /etc/my.cnf, DATADIR/my.cnf, or ~/.my.cnf on Unix, windows-system-directory/my.ini,c:/my.cnf or C:/mysql/data/my.cnf on Windows.

注意 my.cnf 中关于 socket 的设置，如果设置和php识别的不一致，会导致以 unix socket访问数据库失败。建议采用TCP/IP方式建立连接：即在建立连接时，host填写IP地址，如果是本机，使用127.0.0.1（TCP/IP），而不使用localhost（Unix Socket）。

A.3. 用法

A.3.1. 常用语法

测试

shell> telnet server_host 3306

shell&gt; BINDIR/mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
+-----------+

shell&gt; BINDIR/mysqlshow mysql
Database: mysql
+--------------+
|    Tables    |
+--------------+
| columns_priv |
| db           |
| func         |
| host         |
| tables_priv  |
| user         |
+--------------+

shell&gt; BINDIR/mysql -e "select host,db,user from db" mysql
+------+--------+------+
| host | db     | user |
+------+--------+------+
| %    | test   |      |
| %    | test_% |      |
+------+--------+------+

SQL基础

shell> mysql --help
shell> mysql -h <hostname> -u <username> -p[password] [database] [< batch-file]
shell> mysql < batch-file > mysql.out
shell> telnet server_host 3306

mysql> SeLeCt VERSON(), current_DATE;
mysql> SELECT SIN(PI()/4), (4+1)*5;
mysql> SELECT VERSION(); SELECT NOW();
mysql> SHOW DATABASES;
mysql> USE test
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;
mysql> CREATE DATABASE menagerie;
mysql> USE menagerie
shell> mysql -h host -u user -p menagerie
mysql> SHOW TABLES;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

mysql> SHOW TABLES;
mysql> DESCRIBE pet;
mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
mysql> SELECT * FROM pet;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
mysql> SELECT name, birth, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(birth))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet;

mysql> SELECT * FROM pet WHERE name LIKE "b%";
mysql> SELECT * FROM pet WHERE name LIKE "%w%";
-- To find names containing exactly five characters, use the `_' pattern character: --
mysql> SELECT * FROM pet WHERE name LIKE "_____";
mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";
mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = "dog" OR species = "cat"
    -> GROUP BY species, sex;
mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND type = "litter";
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1, pet AS p2
    -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));
CREATE TABLE persons (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirts (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
    PRIMARY KEY (id)
);


mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

SELECT MAX(article) AS article FROM shop;

How mysql handles sub-query
-- In ANSI SQL this is easily done with a sub-query: 
-- SELECT article, dealer, price
-- FROM   shop
-- WHERE  price=(SELECT MAX(price) FROM shop);

-- In MySQL (which does not yet have sub-selects), just do it in two steps: 
-- Get the maximum price value from the table with a SELECT statement. 
-- Using this value compile the actual query: 
SELECT article, dealer, price
FROM   shop
WHERE  price=19.95

-- Another solution is to sort all rows descending by price and only get the first row using the MySQL specific LIMIT clause: 
SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1;

-- In ANSI SQL, I'd do it with a sub-query like this: 

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

-- In MySQL it's best do it in several steps: 

-- Get the list of (article,maxprice). 
-- For each article get the corresponding rows that have the stored maximum price. 
-- This can easily be done with a temporary table: 

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;


shell> mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
--opt : Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables. 
	Should give you the fastest possible dump for reading into a MySQL server. 
--no-data 
	Don't write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!

A.3.2. SQL JOIN

参见：How to Join Multiple SQL Tables - Part 2: Types of Joins, SQL JOIN..., Free SQL Tutorial, SQL queries: join types.

Cartesian join, Inner join, Outer join(Left join, Right join), Self join.

A.3.3. Others

Ansi Compatible: use single quote('), note double quote(").

-- the following are the same.
insert into jxtest (ttt) values('js''s test');
insert into jxtest (ttt) values('js/'s test');

Case Sensitivity in Names

Database, Table, Alias on Table are case sensitive.

If lower_case_table_names is 1 MySQL will convert all table names to lower case on storage and lookup. Note that if you change this option, you need to first convert your old table names to lower case before starting mysqld.

A.4. Security

Don't run the MySQL daemon as the Unix root user.
Invest in a firewall

Check whether unnecessary host can access database using command "shell> telnet server_host 3306".

Password protect your database account

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
           WHERE user='root';
mysql> FLUSH PRIVILEGES;
or shell> mysqladmin -u root password new_password;

DON'T EVER GIVE ANYONE (EXCEPT THE MySQL ROOT USER) ACCESS TO THE user TABLE IN THE mysql DATABASE!

The GRANT and REVOKE commands are used for controlling access to MySQL. Do not grant any more privileges than necessary.

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO user1@localhost
           IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO user1@"%"
           IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Do not keep any plain-text passwords in your database.

Instead use MD5() or another one-way hashing function.
Do not trust any data entered by your users.

A hacker can enters something like ``; DROP DATABASE mysql;'' to destory your database.

Check user input data.

PHP: use the addslashes() function to quote user import.