mysql clomn_MySQL学习笔记

目录

MySQL简介

基本输入查询

创建并使用数据库

SELECT语句

选择特殊行

条件

通配符

选择特殊列

排序

内置函数

日期计算

子查询

连接查询

修改和删除

删除

ALTER 改写历史

对表的内容修改

索引

视图

导入与导出数据

备份数据

恢复

SQL 约束

主键

默认约束

唯一约束值

外键约束

非空约束

MySQL简介

数据库：保存表和其他相关SQL结果的容器

列是存储在表中的一块数据。行是一组能够描述某个事物的流的集合。行和列构成了表

数据库中所有的表应该能以某种方式相互关联

基本输入查询

启动与连接服务器

sudo service mysql start

mysql -u root

要求服务器告诉MySQL的版本号和当前日期。

SELECT VERSION(), CURRENT_DATE;

不想执行正在输入过程中的一个命令，输入\c取消它

SELECT USER() \c

找出服务器上当前存在什么数据库

SHOW DATABASES;

创建并使用数据库

创建数据库 gregs_list

CREATE DATABASE gregs_list;

使用刚才的数据库

USE gregs_list;

创建一个表

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

CRATE TABLE 创建表

括号里输入表列和列名称

VARCHAR：保存以文本格式存储的信息

(20)：表示这段文字的长度最多只有6个字符

各种数据类型

CHAR / CHARACTER

INT / INTEGER

DEC, DECIMAL

BLOB

DATE

TIMESTAMP / DATETIME

VARCHAR

验证表是按你期望的方式创建

DESC pet;

展示数据库中的所有表

SHOW TABLES;

删除创建的表

DROP TABLE pet;

添加数据：INSERT

INSERT INTO pet

VALUES ('zhangyu','Diane','hamster','f','1994-03-',NULL);

INSERT 语句的三种形式

改变列顺序：可改变列名和数据值的顺序

省略列名：数据值必须有，且与列顺序完全相同

省略部分列

SELECT语句查看表中检索所有记录

SELECT*FROM my_contacts;

用一个UPDATE语句就可以修正错误记录

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

NULL:代表未定义的值

可以把列改为不接受NULL，但一定要提供NOT NULL列的值，否则会出现错误信息

CREATE TABLE my_contacts

(last_name VARCHAR (30) NOT null);

使用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 限制条件;

选择特殊行

WHERE :提供搜索的特定条件

SELECT * FROM my_contacts

WHERE first_name = 'zhang';

* : 返回表中的所有列

单引号与不需要单引号

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

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

INSERT INTO my_contacts

(location)

VALUES

('Grover\'s Mill');

INSERT INTO my_contacts

(location)

VALUES

('Grover''s Mill');

只显示特定数据

用列名代替*

SELECT drink_name, main, second FROM easy_drinks WHERE main ='soda';

AND 同时处理两项查询

SELECT drink_name FROM easy_drinks

WHERE

main = 'soda'

AND

amount1 = 1.5

OR:返回符合条件之一的数据

SELECT field1_index, field2_index FROM test_table

WHERE field1_index = '1' OR field2_index = '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';

比较运算符

=

<> / !=

(>=)

<=

可用于数据数据或文本数据的比较

SELECT drink_name

FROM drink_info

WHERE

drink_name >= 'L'

AND

drink_name < 'M';

用 IS NULL 找到 NULL

唯一直接选择NULL 的方法是利用关键字IS NULL

SELECT drink_name

FROM drink_info

WHERE

calories IS NULL;

选取一个范围

AND

SELECT drink_name FROM drink_info

WHERE

calories >= 30

AND

calories <= 60;

BETWEEN

SELECT drink_name FROM drink_info

WHERE

calories BETWEEN 30 AND 60;

查找是否在多个筛选条件 IN

SELECT date_name FROM

black_book

WHERE

rating IN('innovative', 'fabulous',

'delightful', 'pretty good');

或者不符合特征的结果NOT IN

SELECT date_name from black_book

WHERE NOT date_name LIKE 'A%'

AND NOT date_name LIKE 'B%';

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

如果你不想看到表中的所有行，就需要指明你感兴趣的列名称，并用逗号将列名分开。例如，如果你想要知道你的动物什么时候出生的，选择name和birth列

SELECT name, birth FROM pet;

取出重复数据，增加关键字DISTINCT检索出每条唯一的输出记录

SELECT DISTINCT owner FROM pet;

使用一个WHERE子句同时进行行选择与列选择。例如，要想查询狗和猫的出生日期，使用以下查询：

SELECT name, species, birth FROM pet

WHERE species = 'dog' OR species = 'cat';

排序

对表pet的生日按日期排序ORDER BY

SELECT name, birth FROM pet ORDER BY birth;

BINARY强制执行区分大小写

SELECT col_name, birth FROM pet ORDER BY BINARY col_name

默认排序是升序，也就是最小的值排在第一。要想以降序排序，在你正在排序的列名旁边增加DESC(降序 )关键字

SELECT name, birth FROM pet ORDER BY birth DESC;

可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。

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

SELECT name, species, birth FROM pet

-> ORDER BY species, birth DESC;

内置函数

|函数名|COUNT|SUM|AVG|MAX|MIN|

|作用|计数|求和|求平均值|最大值|最小值|

计算表中有多少行COUNT(*)

SELECT COUNT(*) FROM pet;

你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你也可以使用COUNT(*)函数,使用GROUP BY对每个owner的所有记录分组

SELECT owner, COUNT(*) FROM pet GROUP BY owner;

分类数量

查看每种动物的数量

查看每种性别的动物数量

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

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;

计算出salary的最大、最小值

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

找出最高或最低的商品

SELECT min_price:=MIN(price),max_price:=MAX(price) FROM shop;

SELECT * FROM shop WHERE price= min_price OR price= max_price;

日期计算

使用函数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)

FROM pet;

YEAR()提取日期的年部分

RIGHT()提取日期最右面5个字符的MM-DD(月份和日期)部分

MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1

整个表达式看起来有些难懂，使用age来使输出的列标记更有意义

如果以某个顺序排列行，那么会使得浏览结果变得更加轻松。添加ORDER BY name子句则能够实现按照名字进行排序输出

SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)

AS age

FROM pet ORDER BY name;

子查询

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

想要知道名为 "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');

event表中有生产日期，但是为了计算年龄，你需要出生日期，存储在pet表中。查询需要两个表

FROM子句连接两个表，因为查询需要从两个表中提取信息

当从多个表组合(联结)信息时，你需要指定其中一个表中的列明以期匹配其它表的列名。这很简单，因为它们都有一个name列，查询可以通过使用WHERE子句基于name值来匹配两个表中的记录

因为name列都存在两个表中，因此当引用该列时，一定要指定是哪个表，把表名附在列名前即可以实现

SELECT pet.name,

(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)

remark

FROM pet, event

WHERE pet.name = event.name AND event.type = 'litter';

连接查询

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

连接的基本思想是把两个或多个表当作一个新的表来操作，如下

SELECT id,name,people_num

FROM employee,department

WHERE employee.in_dpt = department.dpt_name

ORDER BY id;

另一个连接语句格式是使用 JOIN ON 语法，刚才的语句等同于：

SELECT id,name,people_num

FROM employee JOIN department

ON employee.in_dpt = department.dpt_name

ORDER BY id;

如果你想要将一个表的记录与该表的其它记录进行比较，可以将该表联结到自身。

例如，为了在你的宠物之中选择繁殖中的配偶，你可以用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';

修改和删除

删除

删除数据库 DROP

DROP DATABASE test_01;

创建新记录并删除旧记录

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';

删除一张表

DDROP TABLE 表名字;

删除一列：

ALTER TABLE 表名字 DROP COLUMN 列名字;

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

ALTER 改写历史

添加一列

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

或：

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

如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词

ALTER TABLE employee ADD weight INT(4) DEFAULE 120 AFTER age;

ADD COLUMN 添加列

AFTER 列 表示新增的列被放置在 列1 的后面,

DEFAULT 120 ：新增的一列weight的默认数值都为值为120

如果想放在第一列的位置，则使用FIRST 关键词

修改表的常用命令

CHANGE ：可同时改变现有列的名称和数据类型

MODIFY：修改现有列的数据类型和位置

ADD：在当前表中添加一列

DROP：从表中删除某列

DESCRIBE 查看表的构成

更换列名

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

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

改变列名，并标注为主键

ALTER TABLE project_list

CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,

ADD PRIMARY KEY (proj_id);

AUTO_INCREMENT自动填入递增的整数

ADD......——使用新命令的列作为主键

重命名一张表,三种形式：

RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

ALTER TABLE projekts

RENAME TO project_list;

只改变数据类型， 关键字：MODIFY

ALTER TABLE project_list

MODIFY COLUMN proj_desc VARCHAR(120);

project_list 要修改的列名

VARCHAR(120)新数据类型， 保证新类型不会造成旧数据被截断。

删除一列：

ALTER TABLE 表名字 DROP COLUMN 列名字;

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

ALTER TABLE project_list

DROP COLUMN start_date;

使用FIRST, LAST, BEFORE column_name, AFTER column_name, SECOND,THIRD, FOURTH等关键字，可调整列的顺序

改变数据类型

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

将现有列的内容填入新列

UPDATE my_contacts

SET state = RIGHT(location, 2);

(state, 储存州数据的新列

RIGHT........提取location列的最后两个字符)

对表的内容修改

修改表中某个值

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';

删除一行记录

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

DELETE FROM 表名字 WHERE 条件;

索引

什么是索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容

当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。

而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度

对一张表中的某个列建立索引，有以下两种语句格式

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 表名字; 查看刚才新建的索引

视图

什么是视图

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

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

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；

视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；

在使用视图的时候，可以把它当作一张表。

创建视图的语句格式为

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;

导入与导出数据

导入操作

可以把一个文件里的数据保存进一张表。

linux系统

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

windows 系统

LOAD DATA INFILE '文件路径' INTO TABLE pet LINES

TERMINATED BY '\r\n';

导出数据

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中

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

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

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

备份数据

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

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

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

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

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

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

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

mysqldump -u root mysql_shiyan > bak.sql;

恢复

方法1

把 MySQL-06.sql 文件中保存的mysql_shiyan 数据库恢复。

source /tmp/SQL6/MySQL-06.sql

方法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 约束

约束

什么是约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

约束类型

约束类型：

主键

默认值

唯一值

外键

非空

关键字

PRIMARY KEY

DEFAULT

UNIQUE

FOREIGN KEY

NOT NULL

主键

什么是主键

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

主键的规则

主键不可以为NULL

插入新记录时必须制定主键值

主键必须简洁

主键值不可以被修改

为现有的表添加主键

ALTER TABLE 并添加PRIMARY KEY

ALTER TABLE my_contacts

ADD COLUMN contact_id INT NOT

AUTO_INCREMENT FIRST,

ADD PRIMARY KEY (contact_id);

默认约束

什么是默认约束

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

规则

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) 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的

规则

当 INSERT 语句新插入的数据和已有数据重复的时候，如果有 UNIQUE约束，则 INSERT 失败

INSERT INTO employee VALUES(01,'Tom',25,3000,110110,'dpt1');

INSERT INTO employee VALUES(02,'Jack',30,3500,110110,'dpt2');

第二行插入会显示错误

外键约束

什么是外键约束

外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。

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

规则

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

#将 dpt3 改为 dpt2(department 表中有 dpt2)，则插入成功

INSERT INTO employee VALUES(02,'Jack',30,3500,114114,'dpt3');

非空约束

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

参考资料：

实验楼-MySQL 基础课程

《MySQL必知必会》