windows下MySQL安装

http://dev.mysql.com/downloads/mysql/

下载windows版本，

 

点击download；

我选择放在D:\BigData目录下，进行解压，进入根目录D:\BigData\SQLServer\mysql-8.0.15-winx64，并配置环境变量;

在根目录下创建data文件夹，并在根目录下创建my.ini文件，输入以下内容

找到CMD（C:\Windows\System32），以管理员身份打开，并cd 到mysql的根目录D:\BigData\SQLServer\mysql-8.0.15-winx64，再进入bin目录，执行以下三个命令：

1.mysqld install（安装mysql）

2.mysqld --initialize-insecure自动生成无密码的root用户

3.net start mysql（启动sql服务）

 

 

 

 

 

SQL常用语法：

1.开启mysql

mysql -hlocalhost -uroot -p123456

2.关闭mysql

1. quit
2. exit
3. \q

 

创建表指定引擎

mysql> CREATE TABLE engine_demo_table( i int ) ENGINE = MyISAM;

修改表的引擎

mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB;

再次查看此表的结构，发现引擎已经变化

mysql> SHOW CREATE TABLE engine_demo_table\G

 

 

查看某一列中不重复的元素

SELECT DISTINCT country FROM Websites;

 

 

SELECT * FROM Websites WHERE country='CN';

 

 

SELECT * FROM Websites WHERE country='USA' OR country='CN';

 

 

 

SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA');

 

 

 

 

SELECT * FROM Websites ORDER BY alexa;

 

SELECT * FROM Websites ORDER BY alexa DESC;

 

 

多列排序

SELECT * FROM Websites ORDER BY country,alexa;

 

 

 

order by alexa,country order by country desc,alexa order by country,alexa desc

 

 

 

 

insert into scorebak select * from socre where neza='neza' --插入一行,要求表scorebak 必须存在(复制一行一摸一样的) select * into scorebak from score where neza='neza' --也是插入一行,要求表scorebak 不存在

 

 

 

update

UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';

如果不加where 则会把表中所有alexa和country更新

可设置必须加where，否则报错

set sql_safe_updates=1;

 

 

 

 

 

 

SELECT * FROM Websites WHERE name LIKE 'G%';

 

SELECT * FROM Websites WHERE name LIKE '%oo%';

SELECT * FROM Websites WHERE name NOT LIKE '%oo%';

 

 

 

 

 

SELECT * FROM Websites WHERE url LIKE '%oo%';

 

 

 

SELECT * FROM Websites WHERE name LIKE '_oogle';

 

 

 

 

SELECT * FROM Websites WHERE name REGEXP '^[GFs]'; 以GFs开头的

SELECT * FROM Websites WHERE name regexp '^[GFs]'; 不以GFs开头的

regexp可以换成rlike

 

 

 

 

 

 

SELECT * FROM Websites WHERE name REGEXP '^[A-H]'; 选取以A-H开头的

SELECT * FROM Websites WHERE name REGEXP '^[^A-H]'; 选取不以A-H开头的

 

 

 

 

 

 

 

 

 

 

SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程'); //选出名字为Google和cainiao的所有网站

 

 

 

 

 

between not between

 

 

 

SELECT * FROM Websites

WHERE (alexa BETWEEN 1 AND 20)

AND country NOT IN ('USA', 'IND');

下面的 SQL 语句选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

 

 

 

 

SELECT * FROM Websites

WHERE name BETWEEN 'A' AND 'H';

WHERE name NOT BETWEEN 'A' AND 'H';

 

 

 

 

 

SELECT * FROM access_log

WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

 

 

 

别名：

SELECT name AS n, country AS c

FROM Websites;

 

多列集合到一起

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info

FROM Websites;

 

 

 

 

 

SELECT w.name, w.url, a.count, a.date

FROM Websites AS w, access_log AS a

WHERE a.site_id=w.id and w.name="cainiao";

 

 

 

 

SELECT Websites.id, Websites.name, access_log.count, access_log.date

FROM Websites

INNER JOIN access_log

ON Websites.id=access_log.site_id;

 

 

 

 

 

 

 

SELECT Websites.name, access_log.count, access_log.date

FROM Websites

INNER JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY access_log.count;

 

 

 

 

left join

SELECT Websites.name, access_log.count, access_log.date

FROM Websites

LEFT JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY access_log.count DESC;

 

 

 

 

right——join

SELECT Websites.name, access_log.count, access_log.date

FROM access_log

RIGHT JOIN Websites

ON access_log.site_id=Websites.id

ORDER BY access_log.count DESC;

 

 

 

 

 

union——去重（所有值只出现一次）

SELECT country FROM Websites

UNION

SELECT country FROM apps

ORDER BY country;

 

 

union-all——不去重

SELECT country FROM Websites

UNION ALL

SELECT country FROM apps

ORDER BY country;

 

 

 

 

 

带有where的union-all

SELECT country, name FROM Websites

WHERE country='CN'

UNION ALL

SELECT country, app_name FROM apps

WHERE country='CN'

ORDER BY country;

 

 

 

 

 

 

SQL的 select into

 

 

 

INSERT INTO Websites (name, country)

SELECT app_name, country FROM apps;

 

 

 

INSERT INTO Websites (name, country)

SELECT app_name, country FROM apps

WHERE id=1;

 

 

 

 

 

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

 

 

 

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

 

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

)

 

 

 

 

 

ALTER TABLE 时的 SQL UNIQUE 约束

当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE Persons

ADD UNIQUE (P_Id)

 

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

 

 

 

撤销 UNIQUE 约束

如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE Persons

DROP INDEX uc_PersonID

 

 

 

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (P_Id)

)

 

 

 

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

)

 

 

 

 

增加PRIMARY KEY

ALTER TABLE Persons

ADD PRIMARY KEY (P_Id)

 

ALTER TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

 

 

 

撤销PRIMARY KEY

ALTER TABLE Persons

DROP PRIMARY KEY