暂时用

最新推荐文章于 2024-09-23 18:22:38 发布

芬达爱吃糖

最新推荐文章于 2024-09-23 18:22:38 发布

阅读量195

点赞数

分类专栏：二级教程

本文链接：https://blog.csdn.net/zy1206_c/article/details/103988815

版权

二级教程专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章目录

表格的约束（用于新建表格）

【主键】
【外键约束】外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。
比如，现在有用户表和文章表，给文章表中添加一个指向用户 id 的外键，表示这篇文章所属的用户 id，外键将确保这个外键指向的记录是存在的，如果你尝试删除一个用户，而这个用户还有文章存在于数据库中，那么操作将无法完成并报错。因为你删除了该用户过后，他发布的文章都没有所属用户了，而这样的情况是不被允许的。同理，你在创建一篇文章的时候也不能为它指定一个不存在的用户 id。
一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

CREATE DATABASE mysql_shiyan;

use mysql_shiyan;

CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,
  people_num INT(10) DEFAULT '10',
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
 );

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY,
  name    CHAR(20),
  age     INT(10),
  salary  INT(10) NOT NULL,
  phone   INT(12) NOT NULL,
  in_dpt  CHAR(20) NOT NULL,
  UNIQUE  (phone),
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) #设置外键，employee中的in_dpt关联department中的dpt_name
 );
 
CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01',
  of_dpt     CHAR(20) REFERENCES department(dpt_name),
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
 );

# 创建 shop 数据表，散列数据：物品编号、经销商、价格
# ZEROFILL 表示如果一个 INT 类型的数不足 4 位就在前面补零且不允许出现负数
# DOUBLE(16, 2) 表示该数值至多占 16 位，包括小数点儿，其中小数位数至多两位
mysql> CREATE TABLE shop (
    -> article INT(4) ZEROFILL DEFAULT '0',
    -> dealer CHAR(20) DEFAULT '',
    -> price DOUBLE(16, 2) DEFAULT '0.00',
    -> PRIMARY KEY (article, dealer)
    -> );

# 创建个人数据表，包含两列：ID 和名字
mysql> CREATE TABLE person (
    -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> name CHAR(60) NOT NULL,
    -> PRIMARY KEY (id)
    -> );

# 创建关于衬衫的数据表，包括四列：ID 、样式、颜色、所有者
# owner 外键关联个人数据表的 ID 列
mysql> CREATE TABLE shirt (
    -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    -> color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    -> owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    -> PRIMARY KEY (id)
    -> );

# 向 person 表插入一条数据
mysql> INSERT INTO person VALUES (NULL, 'Antonio Paz');

# 如果刚刚向一张表中插入一条数据，且该表的 ID 字段是自增字段
# 那么 LAST_INSERT_ID 函数的值就是刚插入的那一条数据的 ID 字段值
mysql> SELECT @last := LAST_INSERT_ID();

# 向 shirt 表中插入多条数据
mysql> INSERT INTO shirt VALUES
    -> (NULL, 'polo', 'blue', @last),
    -> (NULL, 'dress', 'white', @last),
    -> (NULL, 't-shirt', 'blue', @last);

mysql> INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

mysql> SELECT @last := LAST_INSERT_ID();

mysql> INSERT INTO shirt VALUES
    -> (NULL, 'dress', 'orange', @last),
    -> (NULL, 'polo', 'red', @last),
    -> (NULL, 'dress', 'blue', @last),
    -> (NULL, 't-shirt', 'white', @last);

表格数据列属性（用于新建表格）

【NOT NULL】听名字就能理解，被非空约束的列，在插入值时必须非空。
【auto_increment】数据列自动递增的唯一编号来标识记录。常与NOT NULL搭配
mysql> CREATE TABLE student( -> sid int NOT NULL AUTO_INCREMENT, -> sname varchar(20) NOT NULL, -> gender varchar(10) NOT NULL, -> PRIMARY KEY(sid) -> );

AUTO_INCREMENT 默认从 1 开始递增，如果你想自定义开始的数字，可以在创建表的时候指定，如:
CREATE TABLE animals (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
)AUTO_INCREMENT = 10001;
如果表格已经建好了,可以通过alter table修改
mysql> ALTER TABLE tbl_name AUTO_INCREMENT = 100001;

【ZEROFILL 】补零且不允许出现负数
【DEFAULT】

表格的查询

一般查询select from

【应用】用于简单的列查询和表格所有内容查询
【例子1】SELECT name,age FROM table_name;#查询name一列的所有信息
【例子2】SELECT DISTINCT name FROM table_name;#查询name且去掉重复的名字
【例子3】SELECT * FROM table_name;#查询表格的所有内容

内置函数
【应用】再查询过程中进行函数运算
【例子】SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

函数名：	COUNT()	SUM()	AVG()	MAX()	MIN()	YEAR()	MONTH()	DAYOFMONTH()	RIGHT()	CURDATE	TIMESTAMPDIFF()
作用：	计数	求和	求平均值	最大值	最小值	提取日期的年数	提取日期的月	提取日期的天	提取日期的月与日	计算当前日期	计算年龄
适用类型	任何数据	数字类数据	数字类数据	数值、字符串和日期	数值、字符串和日期	日期	日期	日期	日期	空的括号即可	`SELECT name,birth,CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;`

【例子1】寻找下一个月生日的动物的名单
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

DATE_ADD

或mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MOD

select from 与limit结合

mysql> SELECT article, dealer, price
    -> FROM shop
    -> ORDER BY price DESC
    -> LIMIT 1;

select distinct from

# 以年和月分组，选择年、月、和分组后各组的数量
# 从哪里选择呢？从去掉重复数据的列表里选择
# 第二行括号内的 SELECT DISTINCT 负责去重，注意不要忘记将去重的结果设置别名
mysql> SELECT year, month, COUNT(*) AS days
    -> FROM (SELECT DISTINCT year, month, day FROM t1) AS tmp
    -> GROUP BY year, month;

select from与ORDER BY结合进行排序

用 ORDER BY 对数据按照某一列进行排序。
【注意】默认情况下，ORDER BY 的结果是升序排列，而使用关键词 ASC 和 DESC 可指定升序或降序排序。
【例子1】SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
【例子2】SELECT * FROM table_name ORDER BY name,salary DESC;#name按升序排，salary 按降序排

select from 与 group by 结合

用 group by 进行分组查询，通常与count()、sum()连用。
【格式】select 列名或列函数 from table_name group by 列名
【例子1】SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt#查询各部门有几个项目
【例子2】SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region#显示每个地区的总人口数和总面积

select from与where结合

【格式】SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

用数学符号 (=,<,>,>=,<=,!=) 表示限制条件
【例子】SELECT name,age,phone FROM employee WHERE name='Mary';

p.s. NULL 要用SELECT name,age,phone FROM employee WHERE name IS NOT NULL;#NULL 是特殊值，不能用普通比较符来比较;只能是 IS NULL 或 IS NOT NULL

用OR 和 AND表示多个限制条件：
用 IN 和 NOT IN 筛选“在”或“不在”某个范围内的结果。
【例子】SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
用 LIKE 实现模糊查询。和 LIKE 联用的通常还有通配符，代表未知字符。
【例子】SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

SQL中的通配符	含义
_	一个未指定字符
%	不定个未指定字符

select from 与 join on 结合，连接两个表查询

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

#FROM employee JOIN department 将表格department与表格employee结合起来
ON employee.in_dpt = department.dpt_name 表格连接的条件
以上也可以表述为SELECT id,name,people_num FROM employee,department WHERE employee.in_dpt = department.dpt_name ORDER BY id;

子查询（两个select）

【定义】子查询指的就是在一个查询之中嵌套了其他的若干查询
【应用】既可以应用于仅涉及一个表中的数据，又可以应用于处理多个表才能获得所需的信息。
【例子】select * from project where proj_num=(select max(proj_num) from project);
【例子1】SELECT name,salary FROM employee WHERE salary>(SELECT AVG(salary) FROM employee)#查询大于公司平均工资的员工姓名
【例子2】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');#查询名为 “Tom” 的员工所在部门做了几个工程。

where 于 having 的区别：
WHERE 子句作用于表和视图
HAVING 子句作用于组
WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算），而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚合函数；

找出价格最高或者最低的物品：

mysql> select* from shop where price in (select max(price),min(price) from shop );
%%%
ERROR 1241 (21000): Operand should contain 1 column(s)
^^^
mysql> select* from shop where price = (select max(price) from shop )or price=(select min(price) from shop);#可以成功运行
^^^

为了1. 类也可以运行引进了临时变量

# SELECT 命令可以设置临时变量，注意变量名须以 @ 开头
# 要用 := 赋值
# 注意该变量只在当前 MySQL 交互环境下有效
mysql> SELECT @max_price := MAX(price), @min_price := MIN(price) FROM shop;
%%%
mysql> SELECT * FROM shop WHERE price IN (@max_price, @min_price);

多表查询

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

在这个查询中，我们为表名指定别名 p1 和 p2 以便能引用它们的列并且使得每一个列的引用更直观。

用 UNION 对两个关键字进行搜索

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'
还可以写成

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';
#UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

模糊查找

方法一

_	%
匹配任何单个字符	匹配任意数量（包括零）的字符

找到b开头的名字
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
要想找出以 “fy” 结尾的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
要想找出包含 “w” 的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
要想找出正好包含 5 个字符的名字:
mysql> SELECT * FROM pet WHERE name LIKE '_____';

方法二
由 MySQL 提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用 REGEXP 和 NOT REGEXP 操作符（或 RLIKE 和 NOT RLIKE ，它们是同义词）。

.	[…]	*
匹配任何单个的字符	匹配在方括号内的任何字符	匹配零个或多个在它前面的东西
	“[abc]” 匹配 “a” 或“b” 或 “c”； “[a-z]” 匹配任何字母； “[0-9]” 匹配任何数字	x* 匹配任何数量的 “x” 字符； [0-9]* 匹配的任何数量的数字； .* 匹配任何数量的任何东西

如果 REGEXP 模式与被测试值的任何地方匹配，模式就匹配(这不同于 LIKE 模式匹配，只有与整个值匹配，模式才匹配)。为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用 “^” 或在模式的结尾用 “$”。为了说明扩展正则表达式如何工作，下面使用 REGEXP 重写上面所示的 LIKE 查询：

为了找出以 “b” 开头的名字，使用 “^” 匹配名字的开始：
mysql> SELECT * FROM pet WHERE name REGEXP '^b';

如果你想强制使 REGEXP 比较区分大小写，使用 BINARY 关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写 ‘b’。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以 “fy” 结尾的名字，使用 “$ ” 匹配名字的结尾：
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
为了找出包含一个 “w” 的名字，使用以下查询：
mysql> SELECT * FROM pet WHERE name REGEXP 'w';

既然如果一个正则表达式出现在值的任何地方，他就会被模式匹配，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个 SQL 模式那样。

为了找出包含正好 5 个字符的名字，使用 “^” 和 “$ ” 匹配名字的开始和结尾，和 5 个 “.” 实例在两者之间：
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
你也可以使用 “{n}” 重复 n 次操作符，重写前面的查询：
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

索引

【本质】与表有关的结构，相当于目录
【应用】加快检索速度
【格式】alter table table_name add index index_name(列名）或create index index_name on table_name(列名)
【例子】

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

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

视图

【定义】视图是从一个或多个表格中导出的表格，是一种虚拟的表格

在使用视图时，可以当作表格用；
数据库中只存放了视图的定义，而试图的数据存贮在原来的表格中，使用视图查询数据时，从原表格调用数据；
一旦原表数据发生改变，视图数据同时改变；

【格式】create view view_name(新列名1,新列名2,…) as select 列名1,列名2,… from table_name
【例子】

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

导入

【作用】把一个文件里的数据保存进一张表。
【格式】load data infile '数据文件绝对路径和文件名' into table table_name;
【例子】

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)
mysql> load data infile '/var/lib/mysql-files/SQL6/in.txt' into table employee;

secure_file_priv 变量指定安全路径为 /var/lib/mysql-files/ ，要导入数据文件，需要将该文件移动到安全路径下。

导出

【作用】顾名思义
【格式】SELECT 列1,列2 INTO OUTFILE '文件路径与名称' FROM table_name;
【例子】

SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;

备份

【作用】顾名思义

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

【格式】mysqldump -u root database_name > 备份名;#备份整个数据库mysqldump -u root database_name table_name > 备份名;#备份表格

mysqldump 是一个备份工具，因此该命令是在终端中执行的，而不是在 mysql 交互环境下

【例子】

cd /home/shiyanlou/
mysqldump -u root mysql_shiyan > bak.sql;

恢复

【作用】将备份的数据恢复到数据库
【格式】有两种格式

在mysql命令下source /文件路径与文件名
现在mysql命令下新建一个空白数据库create database database_name;再到linux终端将备份恢复到空白数据库mysql -u root test < 备份名.sql

芬达爱吃糖

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录