mysql入门(安装+查询语句)

mysql安装

在centos7下有root权限安装，主要参考这里.

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server
#设置权限
chown mysql:mysql -R /var/lib/mysql
#初始化MySQL
mysqld --initialize
#启动MySQL
systemctl start mysqld
#查看MySQL运行状态
systemctl status mysqld

简单验证安装是否完成：

>mysql > SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

修改root密码：

mysql> mysqladmin -u root password "XXXXX";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqladmin -u root password "XXXXX"' at line 1

1.停止mysql数据库：systemctl stop mysqld

2.用以下命令启动MySQL，以不检查权限的方式启动：
mysqld --user=root --skip-grant-tables &

解决方案详见

修改root密码：

UPDATE mysql.user SET Password=PASSWORD('new passwd') where USER='root';

登陆mysql:

mysql -u root -p

mysql 查询语句

• 项目一

mysql> CREATE TABLE IF NOT EXISTS email (
    -> ID INT NOT NULL PRIMARY KEY,
    -> Email VARCHAR(255)
    -> );
mysql> INSERT INTO email VALUES('1','a@b.com');
mysql> INSERT INTO email VALUES('2','c@d.com');
mysql> INSERT INTO email VALUES('3','a@b.com');
mysql> SELECT * FROM email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
|  3 | a@b.com |
+----+---------+
3 rows in set (0.00 sec)
mysql> SELECT Email FROM email GROUP BY Email Having Count(*)>1;
+---------+
| Email   |
+---------+
| a@b.com |
+---------+
1 row in set (0.00 sec)

筛选重复内容方法详见

• 项目二

mysql> CREATE TABLE IF NOT EXISTS World (
    -> name VARCHAR(50) NOT NULL,
    -> continent VARCHAR(50) NOT NULL,
    -> area INT NOT NULL,
    -> population INT NOT NULL,
    -> gdp INT NOT NULL
    -> );
mysql> INSERT INTO World
    ->   VALUES('Afghanistan','Asia',652230,25500100,20343000);
mysql> INSERT INTO World
    ->   VALUES('Albania','Europe',28748,2831741,12960000);
mysql> INSERT INTO World
    ->   VALUES('Algeria','Africa',2381741,37100000,188681000);
mysql> INSERT INTO World
    ->   VALUES('Andorra','Europe',468,78115,3712000);
mysql> INSERT INTO World
    ->   VALUES('Angola','Africa',1246700,20609294,100990000);
mysql> SELECT * FROM World;
+-------------+-----------+---------+------------+-----------+
| name        | continent | area    | population | gdp       |
+-------------+-----------+---------+------------+-----------+
| Afghanistan | Asia      |  652230 |   25500100 |  20343000 |
| Albania     | Europe    |   28748 |    2831741 |  12960000 |
| Algeria     | Africa    | 2381741 |   37100000 | 188681000 |
| Andorra     | Europe    |     468 |      78115 |   3712000 |
| Angola      | Africa    | 1246700 |   20609294 | 100990000 |
+-------------+-----------+---------+------------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT name,population,area FROM World WHERE area>30000000 OR population>25000000 AND gdp>20000000;
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan |   25500100 |  652230 |
| Algeria     |   37100000 | 2381741 |
+-------------+------------+---------+
2 rows in set (0.00 sec)

多重筛选方法详见. AND优先级高于OR，但是可以用（）来提高优先级。