当前位置:Gxlcms > 数据库问题 > MySQL(1)

MySQL(1)

时间:2021-07-01 10:21:17 帮助过:25人阅读

MySQL

1. 数据库简单操作

经常使用PHP脚本来操作数据库

  • 从cmd连接mysql服务器
mysql -u root -p
  • use 命令选择数据库
use RUNOOB;
  • create 命令创建数据库
CREATE DATABASE 数据库名;
create DATABASE RUNOOB;

使用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin 命令来创建数据库。
mysqladmin -u root -p create RUNOOB
  • drop 命令删除数据库
drop database <数据库名>;
mysql> drop database RUNOOB;

mysqladmin -u root -p drop RUNOOB

2. MySQL 数据类型

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

详见:https://www.runoob.com/mysql/mysql-data-types.html

  • 数值类型:MySQL支持所有标准SQL数值数据类型。

    这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

  • 日期和时间类型:DATETIME、DATE、TIMESTAMP、TIME和YEAR。

    每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

  • 字符串类型:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

技术图片


3. MySQL 创建、删除数据表

创建MySQL数据表需要以下信息:

  • 表名
  • 表字段名
  • 定义每个表字段

以下为创建MySQL数据表的SQL通用语法:

CREATE TABLE table_name (column_name column_type);

将在 RUNOOB 数据库中创建数据表runoob_tbl:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析:

  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
  • AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
  • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
  • ENGINE 设置存储引擎,CHARSET 设置编码。

TIPS:

  • 创建 MySql 的表时,表名和字段名外面的符号 ` 不是单引号,而是英文输入法状态下的反单引号,也就是键盘左上角 esc 按键下面的那一个 ~ 按键,坑惨了。

  • 除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。

    在数据库里是严格区分的,任何数跟 NULL 进行运算都是 NULL, 判断值是否等于 NULL,不能简单用 =,而要用 IS NULL关键字。

    含有空值的列很难进行查询优化,而且对表索引时不会存储 NULL 值的,所以如果索引的字段可以为 NULL,索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。

  • ENGINE = innodb,即存储引擎是 innodb。innodb 是 MySQL 上第一个提供外键约束的数据存储引擎,除了提供事务处理外,InnoDB 还支持行锁,提供和 Oracle 一样的一致性的不加锁读取,能增加并发读的用户数量并提高性能,不会增加锁的数量。InnoDB 的设计目标是处理大容量数据时最大化性能,它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。

    InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 有它自己的缓冲池,能缓冲数据和索引,InnoDB 还把数据和索引存放在表空间里面,可能包含好几个文件,这和 MyISAM 表完全不同,在 MyISAM 中,表被存放在单独的文件中,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB。


MySQL中删除数据表是非常容易操作的,但是你在进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。

以下为删除MySQL数据表的通用语法:

DROP TABLE table_name ;

TIPS:MySQL 数据库删除数据的三种方式

技术图片

  • delete from table where

    直接删除表中的某一行数据,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。所以 delete 相比较 truncate 更加占用资源,数据空间不释放,因为需回滚。对 table 和 view 都能操作。

  • truncate table

    一次性地从表中删除所有的数据(释放存储表数据所用的数据页来删除数据)并不把单独的删除操作记录记入日志保存(只在事务日志中记录 页的释放),因此也不能回滚,不能恢复数据,在删除的过程中不会激活与表有关的删除触发器,占用资源更加少,速度更快。数据空间会释放,这个表和索引所占用的空间会恢复到初始大小。只能操作没有关联视图的 table。

    truncate table 不能用于参与了索引视图的表。

  • drop table

    删除的是整个表,包括表的结构,数据,定义。永久抹去,空间释放。对 table 和 view 都能操作。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器,对于外键(foreignkey )约束引用的表,不能使用 truncate table,而应使用不带 where 子句的 delete 语句。


4. MySQL 插入、查询、更新、删除数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
                       
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());

PS:

  • 在以上实例中,我们并没有提供 runoob_id 的数据,因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数,该函数返回日期和时间。
  • 实测:使用 delete 删除数据后,新插入的数据主键 runoob_id 不会从1开始,而使用 truncate 清除表中数据后,新插入的数据主键 runoob_id 重新从1开始

MySQL 数据库使用 SELECT 语句来查询数据。

以下为在MySQL数据库中查询数据通用的 SELECT 语法:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][OFFSET M]

select * : 返回所有记录
limit N : 返回 N 条记录
offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   
  
//为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:    
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   
  
//如果只给定一个参数,它表示返回最大的记录行数目:    
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行   
  
//换句话说,LIMIT n 等价于 LIMIT 0,n。
  • 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
  • SELECT 命令可以读取一条或者多条记录。
  • 你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
  • 你可以使用 WHERE 语句来包含任何条件。
  • 你可以使用 LIMIT 属性来设定返回的记录数。
  • 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
  • 关于分页查询:https://www.runoob.com/mysql/mysql-select-query.html 下方笔记

如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
 
SELECT * from runoob_tbl WHERE runoob_author=‘菜鸟教程‘;
 
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
SELECT * from runoob_tbl WHERE BINARY runoob_author=‘runoob.com‘;
SELECT * from runoob_tbl WHERE BINARY runoob_author=‘RUNOOB.COM‘;
  • 如果我们想在 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。
  • 使用主键来作为 WHERE 子句的条件查询是非常快速的。
  • 如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。

如果我们需要修改或更新 MySQL 中的数据,我们可以使用 UPDATE 命令来操作。

UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
  • 你可以同时更新一个或多个字段。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。


可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法:

DELETE FROM table_name [WHERE Clause]
  • 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
  • 你可以在 WHERE 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。


WHERE 子句中可以使用等号 = 来设定获取数据的条件,如 "runoob_author = ‘RUNOOB.COM‘"。

但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录,这时我们就需要在 WHERE 子句中使用 LIKE 子句

SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *****。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = ‘somevalue‘

从 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录:
SELECT * from runoob_tbl  WHERE runoob_author LIKE ‘%COM‘;
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在 WHERE 子句中使用LIKE子句。
  • 你可以使用LIKE子句代替等号 =
  • LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
  • 你可以使用 AND 或者 OR 指定一个或多个条件。
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

MySQL UNION 操作符语法格式:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]

从 "Websites" 和 "apps" 表中选取所有不同的country(只有不同的值):
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
注释:UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家,每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值!

使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country(也有重复的值):
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据(也有重复的值):
SELECT country, name FROM Websites
WHERE country=‘CN‘
UNION ALL
SELECT country, app_name FROM apps
WHERE country=‘CN‘
ORDER BY country;

参数:

  • expression1, expression2, ... expression_n: 要检索的列。
  • tables: 要检索的数据表。
  • WHERE conditions: 可选, 检索条件。
  • DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
  • ALL: 可选,返回所有结果集,包含重复数据。

如果我们需要对读取的数据进行排序,我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

使用 ORDER BY 子句来读取MySQL 数据表 runoob_tbl 中的数据:
尝试以下实例,结果将按升序及降序排列。
SELECT * from runoob_tbl ORDER BY submission_date ASC;
SELECT * from runoob_tbl ORDER BY submission_date DESC;
  • 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
  • 你可以添加 WHERE...LIKE 子句来设置条件。

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            1 |
| 小明 |            3 |
| 小王 |            2 |
| NULL   |          6 |
+--------+--------------+

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:

select coalesce(a,b,c);

参数说明:如果a == null,则选择b;如果b == null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

以下实例中如果名字为空我们使用总数代替:

SELECT coalesce(name, ‘总数‘), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, ‘总数‘) | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+

MySQL(1)

标签:集中   创建数据库   oracle   个人   表达式   count   自动   select   结果   

人气教程排行