【MySQL】基础篇

1 MySQL 简介

1 简介

2 什么是数据库

3 RDBMS 属于

4 MySQL 数据库的特点

2 MySQL 安装

1 Linux Windows Mac 上安装

1、检查

# rpm -qa | grep mysql

rpm -e mysql // 普通删除模式
rpm -e --nodeps mysql // 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

2、安装MySQL

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

3、权限设置 初始化 启动 查看状态

chown mysql:mysql -R /var/lib/mysql

mysqld --initialize

systemctl start mysqld


systemctl status mysqld

注意：如果我们是第一次启动 mysql 服务，mysql 服务器首先会进行初始化的配置。

MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

yum install mariadb-server mariadb

mariadb数据库的相关命令是：
复制代码

systemctl start mariadb #启动MariaDB
systemctl stop mariadb #停止MariaDB
systemctl restart mariadb #重启MariaDB
systemctl enable mariadb #设置开机启动

2 验证安装

• mysqladmin --version

3 使用

• mysql

  show databases;

  show tables;

  USE DBname;

• mysqladmin -u root password “mypassword” 创建用户名密码

• mysql -u username -p password -h host

  mysql -h 主机名 -u 用户名 -p

  -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;

  -u : 登录的用户名

  -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

3 MySQL 管理

1 启动及关闭MySQL 服务器

2 MySQL 用户设置

3 /etc/my.cnf 文件配置

4 管理MySQL 的命令

4 MySQL 连接

1 使用MySQL 二进制方式连接

mysql -u root -p
Enter password:******

exit

2 使用脚本连接

5 数据库

1 MySQL 创建数据库

• 1 使用mysqladmin 创建数据库

  mysqladmin -u root -p create NOWCODER

  Enter password:******

• 2 使用脚本创建

2 选择数据库

• 1 命令窗口

  use user;

• 2 脚本

3 删除数据库

• 1 drop 命令删除数据库

  drop database <数据库名>;

  drop database user;

• 2 使用mysqladmin 删除数据库

  [root@host]# mysqladmin -u root -p drop user
  Enter password:******

• 3 使用脚本删除

6 数据类型

1 数值类型

2 时间 和 日期 类型

3 字符串类型

7 数据表

1 创建数据表

CREATE TABLE IF NOT EXISTS `tbl`(
  `id` INT UNSIGNED AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `author` VARCHAR(40) NOT NULL,
  `date` DATE,
  PRIMARY KEY ( `nowcoder_id` ),
 key(``)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 删除数据表

3 更新数据库表

4 查询表结构

show tables;

8 数据操作

1 插入数据

1、可以看Insert 的详细语法

2 修改数据

1、Update 语句用于修改表中的数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

2、例子

UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’

UPDATE Person SET Address = ‘Zhongshan 23’, City = ‘Nanjing’
WHERE LastName = ‘Wilson’

3 删除数据

1、DELETE 语句用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值

2、可以看Delete 的详细语法

9 用户管理

1 创建用户

2 删除用户

3 用户权限

10 基础语法

1 SQL 对大小写不明感

2 SQL DML 和 DDL

• 数据操作语言 (DML)

  1、SQL (结构化查询语言)更新指令构成了 SQL 的 DML 部分

  SELECT - 从数据库表中获取数据

  UPDATE - 更新数据库表中的数据

  DELETE - 从数据库表中删除数据

  INSERT INTO - 向数据库表中插入数据

• 数据定义语言 (DDL)

  1、SQL 的数据定义语言 (DDL) 部分创建或删除表格。也可以定义索引（键），规定表之间的链接，以及施加表间的约束

  CREATE DATABASE - 创建新数据库

  ALTER DATABASE - 修改数据库

  CREATE TABLE - 创建新表

  ALTER TABLE - 变更（改变）数据库表

  DROP TABLE - 删除表

  CREATE INDEX - 创建索引（搜索键）

  DROP INDEX - 删除索引

3 基础语法

• select

  1 、SELECT 语句用于从表中选取数据

  2、SELECT 列名称 FROM 表名称

  3、SELECT * FROM 表名称

• distinct

  1、关键词 DISTINCT 用于返回唯一不同的值。

  2、SELECT DISTINCT 列名称 FROM 表名称

• where

  有条件地从表中选取数据

  SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

  SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。
  这是正确的：

  SELECT * FROM Persons WHERE FirstName=‘Bush’

  这是错误的：

  SELECT * FROM Persons WHERE FirstName=Bush

• and & or

  1、AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤

  AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

  如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

  如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

  2、例子

  SELECT * FROM Persons WHERE firstname=‘Thomas’ OR lastname=‘Carter’

  SELECT * FROM Persons WHERE FirstName=‘Thomas’ AND LastName=‘Carter’

• Order By

  1、ORDER BY 语句用于对结果集进行排序

  ORDER BY 语句用于根据指定的列对结果集进行排序。

  ORDER BY 语句默认按照升序对记录进行排序。

  降序排序使用 DESC 关键字。

  SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

  SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

• insert into

  1、INSERT INTO 语句用于向表格中插入新的行。

  插入所以值，少一个会保存

  INSERT INTO 表名称 VALUES (值1, 值2,…)

  插入指定列数据

  INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

  2、例子

  INSERT INTO Persons VALUES (‘Gates’, ‘Bill’, ‘Xuanwumen 10’, ‘Beijing’)

  INSERT INTO Persons (LastName, Address) VALUES (‘Wilson’, ‘Champs-Elysees’)

• update

  1、Update 语句用于修改表中的数据。

  UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

  2、例子

  UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’

UPDATE Person SET Address = ‘Zhongshan 23’, City = ‘Nanjing’ WHERE LastName = ‘Wilson’

• delete

  1、DELETE 语句用于删除表中的行。

  DELETE FROM 表名称 WHERE 列名称 = 值

  2、可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

  DELETE FROM table_name

  DELETE * FROM table_name

  3、例子

  DELETE FROM Person WHERE LastName = ‘Wilson’

11 复杂操作

TOP 排名 & Aliases(别名)

1、TOP 子句用于规定要返回的记录的数目

SELECT column_name(s)
FROM table_name
LIMIT number


SELECT *
FROM Persons
LIMIT 5

2、通过使用 SQL，可以为列名称和表名称指定别名（Alias）。

表的别名

SELECT column_name(s)
FROM table_name
AS alias_name

列的别名

SELECT column_name AS alias_name
FROM table_name

Like 与 通配符

1、LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

2、例子

SELECT * FROM Persons
WHERE City LIKE 'N%'

3、通配符
在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。

% 替代一个或者多个字符

_ 仅代替一个字符

4、例子

SELECT * FROM Persons
WHERE City LIKE '%lond%'

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

Between & in

1、 in
IN 操作符允许我们在 WHERE 子句中规定多个值

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

2、例子

SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

3、Between
BETWEEN 操作符在 WHERE 子句中使用，操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

4、例子

SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'

Join & Union

1、SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据

JOIN: 如果表中有至少一个匹配，则返回行

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

2、例子

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

3、 INNER JOIN 与 JOIN 是相同的。

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

4、 LEFT JOIN 称为 LEFT OUTER JOIN。

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

5、RIGHT JOIN 称为 RIGHT OUTER JOIN。

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

6、 FULL JOIN 称为 FULL OUTER JOIN

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

7、UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

UNION ALL 命令和 UNION （列出不同的）命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

Select Into & Create DB & Create Table

1、SQL SELECT INTO 语句可用于创建表的备份复件。

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

把所有的列插入新表：

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

把希望的列插入新表：

SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

2、例子

SELECT LastName,Firstname
INTO Persons_backup
FROM Persons
WHERE City='Beijing'

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

3、Create DATABASES

CREATE DATABASE database_name

CREATE DATABASE my_db

4、Create Table 用于创建数据库中的表

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Constraints(约束)

1、约束用于限制加入表的数据的类型。

2、可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

3、几种长用约束
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

• Not Null

  1、NOT NULL 约束强制列不接受 NULL 值。

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
  )

如果不向字段添加值，就无法插入新记录或者更新记录。 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值

• Unique

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

  2、每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

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

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  UNIQUE (Id_P)
  )

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
  )

4、创建

ALTER TABLE Persons
  ADD UNIQUE (Id_P)
  
  ALTER TABLE Persons
  ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

5、撤销Unique 约束
ALTER TABLE Persons
DROP INDEX uc_PersonID

• Primary Key

  1、PRIMARY KEY 约束唯一标识数据库表中的每条记录。

  主键必须包含唯一的值。

  主键列不能包含 NULL 值。

  每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  PRIMARY KEY (Id_P)
  )
  
  CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
  )

2、添加

ALTER TABLE Persons
  ADD PRIMARY KEY (Id_P)
  
  复合主键
  ALTER TABLE Persons
  ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

3、撤销主键约束

ALTER TABLE Persons
  DROP PRIMARY KEY

• Foreign Key

  1、一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

CREATE TABLE Orders
  (
  Id_O int NOT NULL,
  OrderNo int NOT NULL,
  Id_P int,
  PRIMARY KEY (Id_O),
  FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
  )

2、添加外键

ALTER TABLE Orders
  ADD FOREIGN KEY (Id_P)
  REFERENCES Persons(Id_P)
  
  ALTER TABLE Orders
  ADD CONSTRAINT fk_PerOrders
  FOREIGN KEY (Id_P)
  REFERENCES Persons(Id_P)

3、撤销外键

ALTER TABLE Orders
  DROP FOREIGN KEY fk_PerOrders

• Check

  1、CHECK 约束用于限制列中的值的范围。

  如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

  如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CHECK (Id_P>0)
  )

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
  )

2、添加约束

ALTER TABLE Persons
  ADD CHECK (Id_P>0)
  
  ALTER TABLE Persons
  ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

3、撤销约束

ALTER TABLE Persons
  DROP CHECK chk_Person

• DEFAULT

  1、DEFAULT 约束用于向列中插入默认值。

  如果没有规定其他的值，那么会将默认值添加到所有的新记录。

CREATE TABLE Persons
  (
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255) DEFAULT 'Sandnes'
  )

2、设置列的默认值

ALTER TABLE Persons
  ALTER City SET DEFAULT 'SANDNES'

3、撤销默认约束

ALTER TABLE Persons
  ALTER City DROP DEFAULT

Create Index

1、在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据

2、更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

创建普通索引

CREATE INDEX index_name
ON table_name (column_name)

创建唯一索引

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

3、例子

CREATE INDEX PersonIndex
ON Person (LastName) 

CREATE INDEX PersonIndex
ON Person (LastName DESC)

Drop

1、使用 DROP 语句，可以轻松地删除索引、表和数据库

DROP INDEX table_name.index_name

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：
DROP TABLE 表名称

DROP DATABASE 语句用于删除数据库：
DROP DATABASE 数据库名称

Alter

1、ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name 
DROP COLUMN column_name

这个有点问题

ALTER TABLE table_name
ALTER COLUMN column_name datatype

ALTER TABLE Person
DROP COLUMN Birthday

Nulls & isnull()

1、Null

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

注释：无法比较 NULL 和 0；它们是不等价的。

2、例子

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

3、ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

计算时希望 NULL 值为 0

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products


SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

View & Increment & Date

1、Auto-increment 会在新记录插入表中时生成一个唯一的数字。

自动地创建主键字段的值。

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

2、什么是视图？
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

3、创建

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition


SELECT * FROM [Current Product List]

4、撤销视图

SQL DROP VIEW Syntax
DROP VIEW view_name

5、更新视图

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

向 "Current Product List" 视图添加 "Category" 列
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

6、DATE
DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
YEAR - 格式 YYYY 或 YY

函数 描述
NOW() 返回当前的日期和时间

CURDATE()返回当前的日期

CURTIME()返回当前的时间

DATE() 提取日期或日期/时间表达式的日期部分

EXTRACT() 返回日期/时间按的单独部分

DATE_ADD()给日期添加指定的时间间隔

DATE_SUB()从日期减去指定的时间间隔

DATEDIFF()返回两个日期之间的天数

DATE_FORMAT()用不同的格式显示日期/时间

12 函数

13 正则表达式

14 运算符

15 事物

16 临时表 元数据

17 SQL 注入

18 数据的备份还原

19 索引