EchoDemo's Blogs

MySQL基础（一）

发表于 | 分类于 | | 阅读次数 ℃
字数统计 2,906 | 阅读时长 13

MySQL是最流行的关系型数据库管理系统(关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。)，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。

1、MySQL的特点

(1)MySQL是开源的，所以你不需要支付额外的费用。

(2)MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

(3)MySQL使用标准的SQL数据语言形式。

(4)MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

(5)MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

2、MySQL的管理

(1)为MySQL数据库中的user表添加新用户

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

注意需要执行FLUSH PRIVILEGES语句。 这个命令执行后会重新载入授权表。如果你不使用该命令，你就无法使用新创建的用户来连接MySQL服务器，除非你重启MySQL服务器。

你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 ‘Y’ 即可，用户权限列表如下：

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv

另外一种添加用户的方法为通过SQL的GRANT命令，以下命令会给指定数据库TUTORIALS添加用户zara，密码为zara123 。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

*MySQL的SQL语句以分号 (;) 作为结束标识。

(2)管理MySQL的命令

a、USE 数据库名;选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

mysql> use RUNOOB;
Database changed

b、SHOW DATABASES;列出MySQL数据库管理系统当前的数据库列表。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RUNOOB             |
| cdcol              |
| mysql              |
| onethink           |
| performance_schema |
| phpmyadmin         |
| test               |
| wecenter           |
| wordpress          |
+--------------------+
10 rows in set (0.02 sec)

c、SHOW TABLES;显示指定数据库的所有表，使用该命令前需要使用USE命令来选择要操作的数据库。

mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)

d、SHOW COLUMNS FROM 数据表;显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id       | int(11)      | NO   | PRI | NULL    |       |
| runoob_title    | varchar(255) | YES  |     | NULL    |       |
| runoob_author   | varchar(255) | YES  |     | NULL    |       |
| submission_date | date         | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

e、SHOW INDEX FROM 数据表;显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

f、SHOW TABLE STATUS LIKE [FROM db_name] [LIKE ‘pattern’] \G;该命令将输出Mysql数据库管理系统的性能及统计信息。

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

3、数据库的基本操作语句

(1)创建数据表

CREATE TABLE table_name (column_name column_type);

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL 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;
Query OK, 0 rows affected (0.16 sec)
mysql>

*如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。

*AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

*PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

*ENGINE 设置存储引擎，CHARSET 设置编码。

(2)删除数据表

DROP TABLE table_name ;

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> DROP TABLE runoob_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

查看一下：

mysql> show tables;
Empty set (0.01 sec)

(3)插入数据

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1,value2,...valueN );

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>

*如果数据是字符型，必须使用单引号或者双引号，如："value"。

*使用箭头标记->不是SQL语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写SQL语句，SQL语句的命令结束符为分号 ;。

(4)查询数据

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

select * from runoob_tbl;

SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';

*查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

*SELECT 命令可以读取一条或者多条记录。你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

*你可以使用 WHERE 语句来包含任何条件。例如使用 AND 或者 OR 指定一个或多个条件。WHERE子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。WHERE子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

*你可以使用 LIMIT 属性来设定返回的记录数。

*你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

(5)更新数据表

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause];

mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)

mysql> SELECT * from runoob_tbl WHERE runoob_id=3;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3         | 学习 C++   | RUNOOB.COM    | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)

*你可以同时更新一个或多个字段。

*你可以在 WHERE 子句中指定任何条件。

*你可以在一个单独表中同时更新数据。

(6)删除MySQL数据表中的记录

DELETE FROM table_name [WHERE Clause];

mysql> use RUNOOB;
Database changed
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.23 sec)

*如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。

*你可以在 WHERE 子句中指定任何条件

*您可以在单个表中一次性删除记录。

(7)删除，添加或修改表字段

删除：ALTER TABLE alter_tbl  DROP columnname;删除指定字段

添加：ALTER TABLE alter_tbl ADD new_column_name new_typename AFTER columnname;指定添加位置

修改： ALTER TABLE alter_tbl MODIFY columnname new_typename;修改字段类型

      ALTER TABLE alter_tbl CHANGE oldcolumnname newcolumnname newtypename;修改字段及类型

      ALTER TABLE alter_tbl RENAME TO alter_tb2;修改表名

(8)UNION操作符

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

使用 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 修饰符对结果没啥影响。

*UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值。

(9)排序

SELECT field1, field2,...fieldN table_name1, table_name2...ORDER BY field1, [field2...] [ASC [DESC]];

SELECT * from runoob_tbl ORDER BY submission_date ASC;
SELECT * from runoob_tbl ORDER BY submission_date DESC;

*你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。

*你可以设定多个字段来排序。

*你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。

*你可以添加 WHERE...LIKE 子句来设置条件。

(10)GROUP BY语法

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

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;将数据表按名字进行分组，并统计每个人有多少条记录

(11)LIKE子句

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

在 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录：

mysql> SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

*你可以在 WHERE 子句中指定任何条件。

*你可以在 WHERE 子句中使用LIKE子句。

*你可以使用LIKE子句代替等号 =。

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

*你可以使用 AND 或者 OR 指定一个或多个条件。

*你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

(12)连接的使用

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

a、内连接示例

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER 
JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
等价于
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, 
tcount_tbl b WHERE a.runoob_author = b.runoob_author;

b、左连接示例

读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT 
JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

c、右连接示例

读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT 
JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

(13)NULL值处理

IS NULL: 当列的值是 NULL,此运算符返回 true。

IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。

<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

*关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

*在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。

(14)索引

CREATE INDEX indexName ON mytable(username(length)); 创建索引

ALTER table tableName ADD INDEX indexName(columnName); 修改表结构(添加索引)

创建表的时候直接指定索引

CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX [indexName] (username(length))
);

DROP INDEX [indexName] ON mytable; 删除索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)); 创建唯一索引

ALTER table mytable ADD UNIQUE [indexName] (username(length)); 修改表结构

创建表的时候直接指定唯一索引

CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE [indexName] (username(length))
);
🐶 您的支持将鼓励我继续创作 🐶
-------------本文结束感谢您的阅读-------------