MySQL学习笔记

2017-06-30  本文已影响0人  鬼宇书生

目录

MySQL简介

  1. 数据库：保存表和其他相关SQL结果的容器
  2. 列是存储在表中的一块数据。行是一组能够描述某个事物的流的集合。行和列构成了表
  3. 数据库中所有的表应该能以某种方式相互关联

基本输入查询

  1. 启动与连接服务器
sudo service mysql start
mysql -u root
  1. 要求服务器告诉MySQL的版本号和当前日期。
SELECT VERSION(), CURRENT_DATE;
  1. 不想执行正在输入过程中的一个命令，输入\c取消它
SELECT  USER()  \c
  1. 找出服务器上当前存在什么数据库
SHOW DATABASES;

创建并使用数据库

  1. 创建数据库 gregs_list
CREATE DATABASE gregs_list;
  1. 使用刚才的数据库
USE gregs_list;
  1. 创建一个表
 CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
                     species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
  1. 各种数据类型
  1. 验证表是按你期望的方式创建
DESC pet;
  1. 展示数据库中的所有表
SHOW TABLES;
  1. 删除创建的表
DROP TABLE pet;
  1. 添加数据：INSERT
INSERT INTO pet
            VALUES ('zhangyu','Diane','hamster','f','1994-03-',NULL);

INSERT 语句的三种形式

  1. SELECT语句查看表中检索所有记录
 SELECT*FROM my_contacts;
  1. 用一个UPDATE语句就可以修正错误记录
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
  1. NULL:代表未定义的值
    可以把列改为不接受NULL，但一定要提供NOT NULL列的值，否则会出现错误信息
CREATE TABLE my_contacts
 （last_name VARCHAR (30) NOT null）;
  1. 使用DEFAULT 填补空白

如果某些列通常有某个特定值，可把特定值指派为DEFAULT默认值
跟在DEFAULT关键字后的值会在每次新增记录时自动插入表中。
默认值的类型必须和列类型相同

CREATE TABLE doughnut_list
( doughnut_name VARCHAR(10) NOT NULL,
doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00);

SELECT语句

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

选择特殊行

  1. WHERE :提供搜索的特定条件
SELECT * FROM my_contacts
 WHERE first_name = 'zhang';

* : 返回表中的所有列

  1. 单引号与不需要单引号
    数据类型中，VARCHAR, CHAR,BLOB,DATA,TIME需要单引号。数字类的类型，DEC和INT不需要引号

  2. 当单引号为特殊字符，需加反斜线或在单引号前再加单引号

INSERT INTO my_contacts
(location)
VALUES
('Grover\'s Mill');

INSERT INTO my_contacts
(location)
VALUES
('Grover''s Mill');
  1. 只显示特定数据
    用列名代替* 
SELECT drink_name, main, second FROM easy_drinks WHERE main ='soda';
  1. AND 同时处理两项查询
SELECT drink_name FROM easy_drinks
 WHERE
 main = 'soda'
 AND
 amount1 = 1.5
  1. OR:返回符合条件之一的数据
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'
  1. 使用UNION将两个表里面的关键字一起使用进行搜索
SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
    UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';
  1. 比较运算符
SELECT drink_name
 FROM drink_info
 WHERE 
 drink_name >= 'L'
 AND
drink_name < 'M';
  1. 用 IS NULL 找到 NULL
    唯一直接选择NULL 的方法是利用关键字IS NULL
SELECT drink_name
 FROM drink_info
 WHERE
  calories IS NULL;
  1. 选取一个范围
SELECT drink_name FROM drink_info
WHERE
calories >= 30
AND
calories <= 60;

SELECT drink_name FROM drink_info
WHERE
calories BETWEEN 30 AND 60;
  1. 查找是否在多个筛选条件 IN 
SELECT date_name FROM 
 black_book 
 WHERE
 rating IN('innovative', 'fabulous', 
 'delightful', 'pretty good');

SELECT date_name from black_book
 WHERE NOT date_name LIKE  'A%'
AND NOT date_name LIKE 'B%';
  1. LIKE 查找部分文本字符串并返回所有符合匹配条件的行

找出以“b”开头的名字的动物信息
找出以“fy”结尾的名字
找出包含“w”的名字

mysql> SELECT * FROM pet WHERE name LIKE 'b%';
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';

SELECT first_name FROM my_contacts WHERE first_name LIKE '_im';

查询结果例如：Kim

  1. 如果你不想看到表中的所有行，就需要指明你感兴趣的列名称，并用逗号将列名分开。例如，如果你想要知道你的动物什么时候出生的，选择name和birth列
SELECT name, birth FROM pet;
  1. 取出重复数据，增加关键字DISTINCT检索出每条唯一的输出记录
 SELECT DISTINCT owner FROM pet;
  1. 使用一个WHERE子句同时进行行选择与列选择。例如，要想查询狗和猫的出生日期，使用以下查询：
SELECT name, species, birth FROM pet
     WHERE species = 'dog' OR species = 'cat';

排序

  1. 对表pet的生日按日期排序ORDER BY
SELECT name, birth FROM pet ORDER BY birth;
  1. BINARY强制执行区分大小写
SELECT col_name, birth FROM pet ORDER BY BINARY col_name
  1. 默认排序是升序，也就是最小的值排在第一。要想以降序排序，在你正在排序的列名旁边增加DESC（降序 ）关键字
SELECT name, birth FROM pet ORDER BY birth DESC;
  1. 可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。

例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询

SELECT name, species, birth FROM pet
 -> ORDER BY species, birth DESC;

内置函数

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

  1. 计算表中有多少行COUNT(*) 
SELECT COUNT(*) FROM pet;
  1. 你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你也可以使用COUNT(*)函数,使用GROUP BY对每个owner的所有记录分组
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
  1. 分类数量

查看每种动物的数量

查看每种性别的动物数量
按种类和性别组合分类的动物数量

SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
  1. 计算出salary的最大、最小值
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
  1. 找出最高或最低的商品
SELECT min_price:=MIN(price),max_price:=MAX(price) FROM shop;
 SELECT * FROM shop WHERE price= min_price OR price= max_price;

日期计算

  1. 使用函数TIMESTAMPDIFF()计算当前日期的年和出生日期之间的差也可以按照直接使用语句(YEAR(CURDATE())-YEAR(birth))计算,其中函数CURDATE()是计算当前的日期。如果当前日期的日历年比出生日期早，则减去一年。
 SELECT name, birth, CURDATE(),
     TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
     FROM pet;

 SELECT name, birth, CURDATE(),
     (YEAR(CURDATE())-YEAR(birth))
     - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age  
    FROM pet;

YEAR()提取日期的年部分
RIGHT()提取日期最右面5个字符的MM-DD(月份和日期)部分
MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1
整个表达式看起来有些难懂，使用age来使输出的列标记更有意义

  1. 如果以某个顺序排列行，那么会使得浏览结果变得更加轻松。添加ORDER BY name子句则能够实现按照名字进行排序输出
SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
     AS age
     FROM pet ORDER BY name;

子查询

  1. 处理多个表才能获得想要的信息

想要知道名为 "Tom" 的员工所在部门做了几个工程。员工信息储存在 employee 表中，但工程信息储存在project 表中

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
  1. event表中有生产日期，但是为了计算年龄，你需要出生日期，存储在pet表中。查询需要两个表
  • FROM子句连接两个表，因为查询需要从两个表中提取信息
  • 当从多个表组合(联结)信息时，你需要指定其中一个表中的列明以期匹配其它表的列名。这很简单，因为它们都有一个name列，查询可以通过使用WHERE子句基于name值来匹配两个表中的记录
  • 因为name列都存在两个表中，因此当引用该列时，一定要指定是哪个表，把表名附在列名前即可以实现
SELECT pet.name,
  (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
  remark
  FROM pet, event
  WHERE pet.name = event.name AND event.type = 'litter';

连接查询

在处理多个表时，子查询只有在结果来自一个表时才有用。但如果需要显示两个表或多个表中的数据，这时就必须使用连接 (join) 操作。

  1. 连接的基本思想是把两个或多个表当作一个新的表来操作，如下
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
  1. 另一个连接语句格式是使用 JOIN ON 语法，刚才的语句等同于：
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
  1. 如果你想要将一个表的记录与该表的其它记录进行比较，可以将该表联结到自身。

例如，为了在你的宠物之中选择繁殖中的配偶，你可以用pet表联结自身来进行相同种类的雄雌配对：
为表名指定别名p1和p2以便能引用它们的列并且使得每一个列的引用更直观

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
 FROM pet AS p1, pet AS p2
 WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

修改和删除

删除

  1. 删除数据库 DROP
DROP DATABASE test_01;
  1. 创建新记录并删除旧记录

SELECT——INSERT——DELETE，先用SELECT 挑选你必须移除的记录，确认记录无误，并没有误删其他记录，用INSERT添加新的信息，再删除旧的信息

SELECT * FROM clown_info
WHERE
activities = 'dancing';

INSERT INTO clown_info
    VALUES
    ('CLARABELLE', 'Belmont senior', 'F,pink hair', 'dancing');

DELETE FROM clown_info
    WHERE
   activities = 'yelling, dancing' AND name = 'Clarabelle';
  1. 删除一张表
DDROP TABLE 表名字;
  1. 删除一列：
ALTER TABLE 表名字 DROP COLUMN 列名字;
或： ALTER TABLE 表名字 DROP 列名字;

ALTER 改写历史

  1. 添加一列
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或：
 ALTER TABLE 表名字 ADD 列名字 数据类型 约束;
  1. 如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词
ALTER TABLE employee ADD weight INT(4) DEFAULE 120 AFTER age;

ADD COLUMN 添加列
AFTER 列 表示新增的列被放置在 列1 的后面,
DEFAULT 120 ：新增的一列weight的默认数值都为值为120
如果想放在第一列的位置，则使用FIRST 关键词

  1. 修改表的常用命令
    • CHANGE ：可同时改变现有列的名称和数据类型
  1. 更换列名

修改数据类型可能会导致数据丢失，所以要慎重使用

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
  1. 改变列名，并标注为主键
ALTER TABLE project_list
CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (proj_id);

AUTO_INCREMENT自动填入递增的整数
ADD......——使用新命令的列作为主键

  1. 重命名一张表,三种形式：
RENAME TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;

 ALTER TABLE projekts 
RENAME TO project_list;
  1. 只改变数据类型， 关键字：MODIFY
 ALTER TABLE project_list
   MODIFY COLUMN proj_desc VARCHAR(120);

project_list 要修改的列名
VARCHAR(120)新数据类型， 保证新类型不会造成旧数据被截断。

  1. 删除一列：
ALTER TABLE 表名字 DROP COLUMN 列名字;
或： ALTER TABLE 表名字 DROP 列名字;

ALTER TABLE project_list
  DROP COLUMN start_date;
  1. 使用FIRST, LAST, BEFORE column_name, AFTER column_name, SECOND,THIRD, FOURTH等关键字，可调整列的顺序
  2. 改变数据类型
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
  1. 将现有列的内容填入新列
UPDATE my_contacts
SET state = RIGHT(location, 2);

(state, 储存州数据的新列
RIGHT........提取location列的最后两个字符)

对表的内容修改

  1. 修改表中某个值
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

UPDATE doughnut_ratings
   SET
   type = 'glazed'
   WHERE type = 'plain glazed';

不加where子句，SET子句提到的表中的每行的每列都会被修改

UPDATE drink_info
  SET cost = cost + 1
 WHERE
  drink_name = 'Blue Moon'
  OR
 drink_name = 'Oh My Gosh'
 OR
  drink_name = 'Lime Fizz';
  1. 删除一行记录

删除表中的一行数据，也必须加上WHERE条件，否则整列的数据都会被删除

DELETE FROM 表名字 WHERE 条件;

索引

  1. 什么是索引
    索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容
    • 当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。
  1. 对一张表中的某个列建立索引，有以下两种语句格式
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引
CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引

索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。使用命令 SHOW INDEX FROM 表名字; 查看刚才新建的索引

视图

  1. 什么是视图
    视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据

注意理解视图是虚拟的表：

  1. 创建视图的语句格式为
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

现在我们创建一个简单的视图，名为 v_emp，包含v_name，v_age，v_phone三个列

CREATE VIEW v_emp(v_name, v_age,v_phone) AS SELECT name, age, phone FROM employee;

导入与导出数据

  1. 导入操作
    可以把一个文件里的数据保存进一张表。
LOAD DATA INFILE '文件路径' INTO TABLE 表名字;

LOAD DATA INFILE '文件路径' INTO TABLE pet LINES 
    TERMINATED BY '\r\n';
  1. 导出数据
    导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中
SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

把整个employee表的数据导出到 /tmp 目录下，导出文件命名为 out.txt 具体语句为

SELECT * INTO OUTFILE '/tmp/out.txt' FROM employee;

备份数据

  1. 数据库中的数据或许十分重要，出于安全性考虑，在数据库的使用中，应该注意使用备份功能。

备份与导出的区别：导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。

  1. mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT 等。

  2. 使用 mysqldump 备份的语句,退出msql, 在终端输入

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

备份整个数据库 mysql_shiyan，将备份文件命名为 bak.sql,先 Ctrl+Z 退出 MySQL 控制台，再打开终端

mysqldump -u root mysql_shiyan > bak.sql;

恢复

  1. 方法1
    把 MySQL-06.sql 文件中保存的mysql_shiyan 数据库恢复。
source /tmp/SQL6/MySQL-06.sql
  1. 方法2
    先使用命令新建一个空的数据库 test：
mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录
CREATE DATABASE test;  #新建一个名为test的数据库

再次 Ctrl+Z 退出MySQL，然后输入语句进行恢复，把刚才备份的 bak.sql 恢复到 test 数据库

mysql -u root test < bak.sql

输入命令查看 test 数据库的表，便可验证是否恢复成功

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录
use test               #连接数据库test
SHOW TABLES;           #查看test数据库的表

SQL 约束

约束

  1. 什么是约束
    约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
  2. 约束类型
约束类型： 主键 默认值 唯一值 外键 非空
关键字 PRIMARY KEY DEFAULT UNIQUE FOREIGN KEY NOT NULL

主键

  1. 什么是主键
    主键 是用于约束表中的一行，作为这一行的唯一标识符，让每一条记录成为唯一的，在一张表中通过主键就能准确定位到一行，用于独一无二地识别出每条记录。

  2. 主键的规则

  1. 为现有的表添加主键
    ALTER TABLE 并添加PRIMARY KEY
ALTER TABLE my_contacts
   ADD COLUMN contact_id INT NOT 
   AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (contact_id);

默认约束

  1. 什么是默认约束
    默认值约束 (DEFAULT) 规定，当有 DEFAULT 约束的列，插入数据为空时，将使用默认值。

  2. 规则
    DEFAULT 约束只会在使用 INSERT 语句时体现出来，INSERT语句中，如果被 DEFAULT 约束的位置没有值，那么这个位置将会被 DEFAULT 的值填充

# 正常插入数据
INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
#插入新的数据，people_num 为空，使用默认值
INSERT INTO department(dpt_name) VALUES('dpt2');

唯一约束值

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

  1. 规则
    当 INSERT 语句新插入的数据和已有数据重复的时候，如果有 UNIQUE约束，则 INSERT 失败
INSERT INTO employee VALUES(01,'Tom',25,3000,110110,'dpt1');
INSERT INTO employee VALUES(02,'Jack',30,3500,110110,'dpt2');

第二行插入会显示错误

外键约束

  1. 什么是外键约束
    外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。

    一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值

  2. 规则
    在 INSERT 时，如果被外键约束的值没有在参考列中有对应，比如以下命令，参考列 (department 表的 dpt_name) 中没有dpt3，则INSERT 失败

#将 dpt3 改为 dpt2（department 表中有 dpt2），则插入成功
INSERT INTO employee VALUES(02,'Jack',30,3500,114114,'dpt3');

非空约束

被非空约束的列，在插入值时必须非空

参考资料：
实验楼-MySQL 基础课程
《MySQL必知必会》

上一篇下一篇

猜你喜欢

热点阅读