01-01 菜鸟教程数据库初识

最新推荐文章于 2024-08-04 20:23:23 发布

csdn_immortal

最新推荐文章于 2024-08-04 20:23:23 发布

阅读量1.8k

点赞数

分类专栏： database

本文链接：https://blog.csdn.net/csdn_immortal/article/details/85163449

版权

database 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

菜鸟教程完整版

linux上安装mariadb的版本如下

/usr/share/doc/MariaDB-server-10.2.19

导入表mysql < websites.sql

MariaDB [my_db]> show tables;
+-----------------+
| Tables_in_my_db |
+-----------------+
| websites |
+-----------------+

mysql> use RUNOOB;
选择数据库或者用\u RUNOOB

mysql> set names utf8;
设置使用字符集

查看支持所有字符集
MariaDB [(none)]> show CHARACTER SET;
utf8mb4

SQL对大小写不敏感

某些数据库系统要求在每条 SQL 语句的末端使用分号。
分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句。

SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表

DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

MariaDB [my_db]> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+

MariaDB [my_db]> select name,url from websites;

大多数数据库软件系统都允许使用编程函数在结果集中进行导航，比如：Move-To-First-Record、Get-Record-Content、Move-To-Next-Record 等等。
类似这些编程函数不在本教程讲解之列。如需学习通过函数调用访问数据的知识，请访问我们的 ADO 教程或者 PHP 教程。

SQL SELECT DISTINCT 语句
在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。
DISTINCT 关键词用于返回唯一不同的值。

SELECT DISTINCT column_name,column_name FROM table_name;

MariaDB [my_db]> select distinct country from websites;

SQL WHERE 子句
WHERE 子句用于提取那些满足指定标准的记录。

SQL WHERE 语法
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

MariaDB [my_db]> select * from websites where country='CN';

文本字段 vs. 数值字段
SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。
在上个实例中 'CN' 文本字段使用了单引号。
如果是数值字段，请不要使用引号。

MariaDB [my_db]> select * from websites where id=1;
+----+--------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------+------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
+----+--------+------------------------+-------+---------+

WHERE 子句中的运算符
下面的运算符可以在 WHERE 子句中使用：
运算符   描述
=   等于
<>   不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>   大于
<   小于
>=   大于等于
<=   小于等于
BETWEEN   在某个范围内
LIKE   搜索某种模式
IN   指定针对某个列的多个可能值

Where 子句
搜索 empno 等于 7900 的数据：
Select * from emp where empno=7900;

Where +条件（筛选行）
条件：列，比较运算符，值
比较运算符包涵：= > < >= ,<=, !=,<> 表示（不等于）
Select * from emp where ename='SMITH';
例子中的 SMITH 用单引号引起来，表示是字符串，字符串要区分大小写。

逻辑运算
And:与同时满足两个条件的值。
Select * from emp where sal > 2000 and sal < 3000;
查询 EMP 表中 SAL 列中大于 2000 小于 3000 的值。

Or:或满足其中一个条件的值
Select * from emp where sal > 2000 or comm > 500;
查询 emp 表中 SAL 大于 2000 或 COMM 大于500的值。

Not:非满足不包含该条件的值。
select * from emp where not sal > 1500;
查询EMP表中 sal 小于等于 1500 的值。

逻辑运算的优先级：
() not and or

特殊条件
1.空值判断： is null
Select * from emp where comm is null;
查询 emp 表中 comm 列中的空值。

2.between and (在之间的值)
Select * from emp where sal between 1500 and 3000;
查询 emp 表中 SAL 列中大于 1500 的小于 3000 的值。
注意：大于等于 1500 且小于等于 3000， 1500 为下限，3000 为上限，下限在前，上限在后，查询的范围包涵有上下限的值。

3.In
Select * from emp where sal in (5000,3000,1500);
查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。

4.like
Like模糊查询
Select * from emp where ename like 'M%';
查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。
% 表示多个字值，_ 下划线表示一个字符；
M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。

不带比较运算符的 WHERE 子句：
WHERE 子句并不一定带比较运算符，当不带运算符时，会执行一个隐式转换。当 0 时转化为 false，1 转化为 true。
例如：
SELECT studentNO FROM student WHERE 0
则会返回一个空集，因为每一行记录 WHERE 都返回 false。
SELECT studentNO FROM student WHERE 1
返回 student 表所有行中 studentNO 列的值。因为每一行记录 WHERE 都返回 true。

SQL ORDER BY 关键字
ORDER BY 关键字用于对结果集进行排序。

SQL ORDER BY 关键字
ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

SQL ORDER BY 语法
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

MariaDB [my_db]> select * from websites order by alexa;
MariaDB [my_db]> select * from websites order by alexa asc;

MariaDB [my_db]> select * from websites order by alexa desc;

MariaDB [my_db]> select * from websites order by country,alexa;

ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序；如上述教程最后一个例子：
    1）、先将country值这一列排序，同为CN的排前面，同属USA的排后面；
    2）、然后在同属CN的这些多行数据中，再根据alexa值的大小排列。
    3）、ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC。

ORDER BY 多列的时候，eg:
   order by A,B 这个时候都是默认按升序排列
   order by A desc,B 这个时候 A 降序，B 升序排列
   order by A ,B desc 这个时候 A 升序，B 降序排列
   即 desc 或者 asc 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序

SQL INSERT INTO 语句
INSERT INTO 语句用于向表中插入新记录。

SQL INSERT INTO 语法
INSERT INTO 语句可以有两种编写形式。
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO table_name
VALUES (value1,value2,value3,...);
第二种形式需要指定列名及被插入的值：
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

MariaDB [my_db]> INSERT INTO websites (name, url, alexa, country) VALUES ('百度','https://www.baidu.com/','4','CN');

在指定的列插入数据
我们也可以在指定的列插入数据。
下面的 SQL 语句将插入一个新行，但是只在 "name"、"url" 和 "country" 列插入数据（id 字段会自动更新）：

MariaDB [my_db]> insert into websites (name,url,country) values ('stackoverflow','http://stackoverflow.com/','IND');

没有指定要插入数据的列名的形式需要把列出插入行的每一列数据
INSERT INTO table_name
VALUES (value1,value2,value3,...);

insert into select 和select into from 的区别
insert into scorebak select * from socre where neza='neza' --插入一行,要求表scorebak 必须存在
select * into scorebak from score where neza='neza' --也是插入一行,要求表scorebak 不存在

SQL UPDATE 语句
UPDATE 语句用于更新表中已存在的记录。

SQL UPDATE 语法
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

MariaDB [my_db]> update websites set alexa='5000',country='USA' where name='菜鸟教程';

在更新记录时要格外小心！在上面的实例中，如果我们省略了 WHERE 子句，如下所示：
UPDATE Websites SET alexa='5000', country='USA'
执行以上代码会将 Websites 表中所有数据的 alexa 改为 5000，country 改为 USA。

在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在update 语句后携带 where 条件，否则就会报错。
set sql_safe_updates=1; 表示开启该参数

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

SQL DELETE 语法
DELETE FROM table_name WHERE some_column=some_value;

WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

MariaDB [my_db]> delete from websites where name='百度' and country='CN';

删除所有数据
您可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变：

DELETE FROM table_name;
或
DELETE * FROM table_name;

SQL关于删除的三个语句：DROP、TRUNCATE、 DELETE 的区别。
DROP:
DROP test;
删除表test，并释放空间，将test删除的一干二净。

TRUNCATE:
TRUNCATE test;
删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在。

DELETE:
1、删除指定数据
删除表test中年龄等于30的且国家为US的数据
DELETE FROM test WHERE age=30 AND country='US';
2、删除整个表
仅删除表test内的所有内容，保留表的定义，不释放空间。
DELETE FROM test 或者 DELETE FROM test;
DELETE * FROM test 或者 DELETE * FROM test;

truncate table 命令将快速删除数据表中的所有记录，但保留数据表结构。这种快速删除与 delete from 数据表的删除全部数据表记录不一样，delete 命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而 truncate 命令删除的数据是不可以恢复的。

相同点
truncate 和不带 where 子句的 delete, 以及 drop 都会删除表内的数据。
不同点:
1. truncate 和 delete 只删除数据不删除表的结构(定义) ，drop 语句将删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index); 依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。
2.delete 语句是 dml, 这个操作会放到 rollback segement 中, 事务提交之后才生效; 如果有相应的 trigger, 执行的时候将被触发。 truncate, drop 是 ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚。操作不触发 trigger。
3.delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动。显然 drop 语句将表所占用的空间全部释放。 truncate 语句缺省情况下见空间释放到 minextents 个 extent, 除非使用 reuse storage; truncate会将高水线复位(回到最开始)。
4.速度：一般来说: drop > truncate > delete 。
5.安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候。否则哭都来不及。
使用上, 想删除部分数据行用 delete, 注意带上 where 子句。回滚段要足够大。
想删除表, 当然用 drop。
想保留表而将所有数据删除。如果和事务无关, 用 truncate 即可。如果和事务有关, 或者想触发 trigger, 还是用 delete。
如果是整理表内部的碎片, 可以用 truncate 跟上 reuse stroage, 再重新导入/插入数据。

SQL SELECT TOP, LIMIT, ROWNUM 子句

SQL SELECT TOP 子句
SELECT TOP 子句用于规定要返回的记录的数目。
SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。
注意:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

SQL Server / MS Access 语法
SELECT TOP number|percent column_name(s) FROM table_name;

MySQL 语法
SELECT column_name(s) FROM table_name LIMIT number;
实例
SELECT * FROM Persons LIMIT 5;

Oracle 语法
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
实例
SELECT * FROM Persons WHERE ROWNUM <=5;

MySQL SELECT LIMIT 实例
MariaDB [my_db]> select * from websites limit 2;
+----+--------+-------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------+-------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
+----+--------+-------------------------+-------+---------+

SQL SELECT TOP PERCENT 实例
在 Microsoft SQL Server 中还可以使用百分比作为参数。
下面的 SQL 语句从 websites 表中选取前面百分之 50 的记录：
实例
以下操作在 Microsoft SQL Server 数据库中可执行。
SELECT TOP 50 PERCENT * FROM Websites;

SQL LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SQL LIKE 语法
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

MariaDB [my_db]> select * from websites where name like 'G%';
+----+--------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------+------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
+----+--------+------------------------+-------+---------+

MariaDB [my_db]> select * from websites where name like '%k';
+----+----------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+----------+---------------------------+-------+---------+
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+----------+---------------------------+-------+---------+

not like 指定模式取反
MariaDB [my_db]> select * from websites where name not like '%k';

'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
‘_a_’ //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的

学到这里，想起一个问题，如果里面包括 _ % 怎么转义，查下，可以通过 escape ：
select * from username where 用户名 like '段_%' -- 会查出来段煜段鑫

select * from username where 用户名 like '段\_%' escape '\' -- 通过 \转义,只能查出来段_煜

SQL 通配符
通配符可用于替代字符串中的任何其他字符。

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。
SQL 通配符用于搜索表中的数据。
在 SQL 中，可使用以下通配符：

通配符       描述
%           替代 0 个或多个字符
_           替代一个字符
[charlist]   字符列中的任何单一字符

[^charlist]
或
[!charlist] 不在字符列中的任何单一字

MariaDB [my_db]> select * from websites where url like 'http%';
MariaDB [my_db]> select * from websites where name like '_oogle';

使用 SQL [charlist] 通配符
MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

MariaDB [my_db]> select * from websites where name regexp '[GFs]';
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+

不以A-H开头的name
MariaDB [my_db]> select * from websites where name regexp '^[^A-H]';

首先说下LIKE命令都涉及到的通配符：
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[!charlist] 不在字符列中的任何单一字符
其中搭配以上通配符可以让LIKE命令实现多种技巧：

SQL 中，通配符与 SQL LIKE 操作符一起使用。
不过，MySQL 、SQLite 只支持 % 和 _ 通配符，不支持 [^charlist] 或 [!charlist] 通配符（ MS Access 支持，微软 office 对通配符一直支持良好，但微软有时候的通配符不支持 %，而是 *，具体看对应软件说明）。通配符和正则不是一回事。
MySQL 和 SQLite 会把 like '[xxx]yyy' 的中括号当成普通字符，而不是通配符。
比如：
select * from persons WHERE City LIKE '[b]eijing'
将查出 city 为 [B]eijing 的行，而不是 city 为 beijing 的行。
MySQL 中要完成 [^charlist] 或 [!charlist] 通配符的查询效果，需要通过正则表达式来完成。
select * from persons WHERE City REGEXP '[b]eijing' SQLite不支持Regexp正则方法。

SQL IN 操作符
IN 操作符
IN 操作符允许您在 WHERE 子句中规定多个值。

SQL IN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

MariaDB [my_db]> select * from websites where name in ('Google','菜鸟教程');
+----+--------------+------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 5000 | USA |
+----+--------------+------------------------+-------+---------+

IN 与 = 的异同
相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义
不同点：IN可以规定多个值，等于规定一个值

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

=
SELECT column_name(s)
FROM table_name
WHERE column_name=value1;

in 与 = 的转换
select * from Websites where name in ('Google','菜鸟教程');
可以转换成 = 的表达：
select * from Websites where name='Google' or name='菜鸟教程';

SQL BETWEEN 操作符

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期

SQL BETWEEN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

MariaDB [my_db]> select * from websites where alexa between 1 and 20;
+----+----------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+----------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+----------+---------------------------+-------+---------+

NOT BETWEEN 操作符实例

MariaDB [my_db]> select * from websites where alexa not between 1 and 20;

带有 IN 的 BETWEEN 操作符实例
下面的 SQL 语句选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站
MariaDB [my_db]> select * from websites where (alexa between 1 and 20) and country not in ('USA','IND');
+----+--------+-------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------+-------------------------+-------+---------+
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
+----+--------+-------------------------+-------+---------+

带有文本值的 BETWEEN 操作符实例
下面的 SQL 语句选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站：
MariaDB [my_db]> select * from websites where name between 'A' AND 'H';
MariaDB [my_db]> select * from websites where name not between 'A' AND 'H';

带有日期值的 BETWEEN 操作符实例
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。
因此，请检查您的数据库是如何处理 BETWEEN 操作符！

SQL 别名
通过使用 SQL，可以为表名称或列名称指定别名。
基本上，创建别名是为了让列名称的可读性更强。

列的 SQL 别名语法
SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法
SELECT column_name(s)
FROM table_name AS alias_name;

MariaDB [my_db]> select name as n,country as c from websites limit 2;
+--------+-----+
| n | c |
+--------+-----+
| Google | USA |
| 淘宝 | CN |
+--------+-----+

列的别名实例
在下面的 SQL 语句中，我们把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：
MariaDB [my_db]> select name,concat(url,',',alexa,',',country) as site_info from websites limit 1;
+--------+------------------------------+
| name | site_info |
+--------+------------------------------+
| Google | https://www.google.cm/,1,USA |
+--------+------------------------------+

表的别名实例
MariaDB [my_db]> select w.name,w.url,a.count,a.date from websites as w,access_log a where a.site_id=w.id and w.name='菜鸟教程';
+--------------+------------------------+-------+------------+
| name | url | count | date |
+--------------+------------------------+-------+------------+
| 菜鸟教程 | http://www.runoob.com/ | 100 | 2016-05-13 |
| 菜鸟教程 | http://www.runoob.com/ | 220 | 2016-05-15 |
| 菜鸟教程 | http://www.runoob.com/ | 201 | 2016-05-17 |
+--------------+------------------------+-------+------------+

在下面的情况下，使用别名很有用：
在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起