MySQL学习笔记1安装

1. download mysql-5.5.41-linux2.6-i686.tar.gz

2. to execute pre steps

tar -xzvf mysql-5.5.41-linux2.6-i686.tar.gz -C /usr/local/

ln -s /usr/local/mysql  /usr/local/mysql-5.5.41-linux2.6-i686.tar

groupadd mysql

useradd -r -g mysql mysql

cd /usr/local/mysql

chown -R mysql .

chgrp -R mysql .

currently the data directory under mysql is as below:

[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]# ll data
total 4
drwxr-xr-x 2 root root 4096 Jan 28 23:52 test
[root@localhost mysql]# 

3. to install mysql

scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data &

***************************************************

mysql_install_db:

 initializes the mysql data directory and creates the system tables that it contains.

initializes the system tablespace and related data structures needed to manager InnoDB tables.

***************************************************

now the data directory looks like this

[root@localhost mysql]# ll data
total 12
drwx------ 2 mysql root  4096 Jan 28 23:59 mysql
drwx------ 2 mysql mysql 4096 Jan 28 23:59 performance_schema
drwxr-xr-x 2 mysql root  4096 Jan 28 23:52 test
[root@localhost mysql]# 

4. to start mysqld

bin/mysqld_safe --user=mysql &

5 . to verify server started

bin/mysqladmin -version

ps -ef|grep mysql

bin/mysqladmin variables

6. to secure the user root：

mysqladmin -u root password "root"

mysql -uroot -proot

select user, host, password from mysql.user;

7. get db and table info

show databases;

use mysql;

select database();

show tables;

desc user;

8. to execute sql

mysql -uroot -proot -e "select user, password,host from mysql.user"

[root@localhost mysql]# echo "show databases;select user,password,host from mysql.user;">/tmp/test.sql
[root@localhost mysql]# mysql -uroot -proot </tmp/test.sql

[root@localhost mysql]# mysql -uroot -proot

mysql> source /tmp/test.sql

****************corelated subquery****************

mysql> select article, dealer, price from shop s1

where price=(select max(s2.price) from shop s2

      where s1.article=s2.article); //this is corelated subquery which is inefficient, we can use left join to instead

************left join is efficient than corelated subquery****************888

select s1.article, dealer, s1.price

from shop s1

join (select article, max(price) as price 

 from shop group by article) as s2

on s1.article = s2.article and s1.price=s2.price;

select s1.article, s1.dealer, s1.price

from shop s1

left join shop s2 on s1.article=s2.article and s1.price < s2.price

where s2.article is null;

*********************using user defined variables*******************************88

select @min_price:=min(price),@max_price:=max(price) from shop;

select * from shop where price=@min_price or price=@max_price;

*查看支持的引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

*查看表的引擎
 mysql> show create table shop;

mysql> show table status from mysql where name='user';

*修改表的引擎

mysql> alter table shop engine=MyISAM;

*修改默认的引擎

/etc/my.cnf中添加如下

default-storage-engine=InnoDB/MyISAM

*添加用户

以root用户登录

mysql> create user 'scott'@'vmhostu' identified by 'scott';//创建用户
Query OK, 0 rows affected (0.07 sec)

mysql> grant select on mysql.* to scott@vmhostu;//赋予用户mysql上的权限
Query OK, 0 rows affected (0.00 sec)

mysql> create database shop; //创建数据库

mysql>grant all on shop.* to scott@vmhost;//将数据库权限赋予用户

*查看当前连接信息

mysql>select user();//查看当前用户

mysql>select database();//查看当前数据库

mysql>show grants for scott@vmhost;//查看当前用户的权限

mysql>revoke create on *.* from scott@vmhostu;//收回权限