SQL 学习

1. 安装

1.1 安装检查

命令：sudo service mysql start
结果：

• 未安装：mysql: unrecognized service

1.2 安装

• 安装 MySQL 服务端、核心程序
  sudo apt-get install mysql-server
• #安装 MySQL 客户端
  sudo apt-get install mysql-client
• 验证安装
  sudo netstat -tap | grep mysql
• 使用
  sudo service mysql start
  mysql -u root
• 查看数据库
  show databases；
• 连接数据库
  use <数据库名>
• 查看表
  show tables;
• 退出
  exit / quit

2 数据库基本

2.1 创建数据库

CREATE DATABASE mysql_shiyan；
mysqladmin -u root -p create mysql_shiyan；

2.2 删除数据库

drop database <数据库名>;
mysqladmin -u root -p drop <数据库名>;

2.3 选择数据库

use <数据库名字>：

2.4 创建数据表

CREATE TABLE table_name (column_name column_type);
CREATE TABLE employee (id int(10),name char(20),phone int(12));

2.5 插入数据

INSERT INTO employee(id,name,phone) VALUES(01,‘Tom’,110110110);

INSERT INTO employee VALUES(02,‘Jack’,119119119);

INSERT INTO employee(id,name) VALUES(03,‘Rose’);

2.6 导入数据

2.6.1 mysql 命令导入

mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)
例：mysql -uroot -p123456 < runoob.sql

2.6.2 source 命令导入

例：mysql> source /home/abc/abc.sql # 导入备份数据库
注：source 命令导入数据库需要先登录到数库终端

2.6.3 使用 LOAD DATA 导入数据

例：mysql> LOAD DATA LOCAL INFILE ‘dump.txt’ INTO TABLE mytbl;

2.6.4 使用 mysqlimport 导入数据

例：mysqlimport -u root -p --local mytbl dump.txt
mysqlimport的常用选项介绍

2.7 列的修改

• 增加一列

  ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
  或：
  ALTER TABLE 表名字 ADD 列名字 数据类型 约束;
  

• 删除一列

  ALTER TABLE 表名字 DROP COLUMN 列名字;
  或：
  ALTER TABLE 表名字 DROP 列名字;
  

• 重命名一列

  ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
  

• 改变数据类型

  ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
  

2.8 表的内容修改

• 修改某一个值

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
例：UPDATE employee SET age=21,salary=3000 WHERE name='Tom';

• 删除一行记录

DELETE FROM 表名字 WHERE 条件;
例：DELETE FROM employee WHERE name='Tom';

3 数据类型

3.1 数值类型

3.2 日期和时间类型

3.3 字符串类型

4 SQL约束

约束类型	主键	默认值	唯一	外键	非空
关键字	PRIMARY KEY	PRIMARY KEY	PRIMARY KEY	PRIMARY KEY	PRIMARY KEY

4. 1主键（唯一性）

基本形式	单一主键	复合主键	默认值

4.2 唯一约束

create table test(
    id int unsigned auto_increment,
    name varchar(50) not null,
    age int unsigned,
    unique(name),
    primary key(id)
);

注： 唯一约束 (UNIQUE) 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

4.3 外键约束

4.3.1 创建表时直接创建外键约束

create table books(
    bookid number(10) not null primary key,
    bookName varchar2(20) not null,
    price number(10,2),
    categoryId int,
    CONSTRAINT FK_categoryId foreign key(categoryId) not null references Category(id)  --外键约束
);

注：必须先创建参照表，才能在创建外键约束，即必须现有表Category，再有book

4.3.2 先创建表，表创建成功后，单独添加外键约束

create table books(
    bookid number(10) not null primary key,
    bookName varchar2(20) not null,
    price number(10,2),
    categoryId number(10) not null
);

ALTER TABLE  books ADD CONSTRAINT FK_Book_categoryid FOREIGN KEY(categoryId ) REFERENCES Category(id);

4.3.3 删除外键

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

4.4 非空约束

注：非空约束 (NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。

5 SELECL 语句

基本语法：SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;
WHERE限制条件

• 数学符号： (=,<,>,>=,<=)

  • SELECT name,age FROM employee WHERE age>25;

• 逻辑关系：[条件一 OR 条件二]] 和 [条件一 AND 条件二]

  • SELECT name,age FROM employee WHERE age<25 OR age>30;

• 边界包含：BETWEEN

  • SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30;

• 在与不在：IN 和 NOT IN

  • SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

• LIKE 通配符

  • _ 代表一个未指定字符，% 代表不定个未指定字符
  • SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
  • SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

• 结果排序

  • ORDER BY 排序，默认升序；键词 ASC 和 DESC 可指定升序或降序排序
  • SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

• SQL 内置函数

  函数名	COUNT	SUM	AVG	MAX	MIN
  作用	计数	求和	取平均	最大	最小

  SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
  注：AS 重命名

• 子查询
  HAVING 关键字可以的作用和 WHERE 是一样的，都是说明接下来要进行条件筛选操作。区别在于 HAVING 用于对分组后的数据进行筛选；

  SELECT of_dpt,COUNT(proj_name) AS count_project 
  FROM project GROUP BY of_dpt HAVING of_dpt IN 
  (SELECT in_dpt FROM employee WHERE name='Tom');
  

• 连接查询
  连接 (join) 操作。 连接的基本思想是把两个或多个表当作一个新的表来操作；

  SELECT id,name,people_num 
  FROM employee,department 
  WHERE employee.in_dpt = department.dpt_name 
  ORDER BY id;
  

  等同于

  SELECT id,name,people_num
  FROM employee JOIN department
  ON employee.in_dpt = department.dpt_name
  ORDER BY id;
  

  注： GROUP BY 后的唯一性

6 索引

索引：可以加快查询速度

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);

查看索引：
SHOW INDEX FROM 表名字;

7 视图

视图为结果虚拟表

• 创建视图

  CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
  

• 修改视图

  alter view 视图名 as select 语句
  

• 删除视图

  drop view 视图名[,视图名…];
  

• 重命名视图

  Rename table 视图名 to 新视图名;
  

• 查看视图

  Show tables；
  

8 数据操作

• 数据导入

  LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;
  

  注：导入导出大量数据都属于敏感操作，根据 mysql 的安全策略，导入导出的文件都必须在指定的路径下进行，在 mysql 终端中查看路径变量：

  mysql -uroot
  mysql> show variables like '%secure%';
  +--------------------------+-----------------------+
  | Variable_name            | Value                 |
  +--------------------------+-----------------------+
  | require_secure_transport | OFF                   |
  | secure_auth              | ON                    |
  | secure_file_priv         | /var/lib/mysql-files/ |
  +--------------------------+-----------------------+
  3 rows in set (0.00 sec)
  

• 数据导出

  SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
  

• 数据备份

  mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
  mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表