文章目录
管理mysql的命令
-
mysql> use 数据库名;
选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
-
mysql> SHOW DATABASES;
列出 MySQL 数据库管理系统的数据库列表。
-
mysql> SHOW TABLES;
显示指定数据库的所有表,使用该命令前需要使用 use 命令来选择要操作的数据库。
-
mysql> SHOW COLUMNS FROM;
显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
-
mysql> SHOW INDEX FROM;
显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
-
mysql> SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G;
该命令将输出Mysql数据库管理系统的性能及统计信息。
MYSQL连接
-
mysql> mysql -u root -p;登录(接着输入登录密码)
mysql> mysql -u root -p密码; 直接登录
-
mysql> exit 退出
mysql创建数据库
-
mysql> create DATABASE 数据库名;
-
mysql> mysqladmin -u root -p create 数据库名;
mysql删除数据库
-
mysql> drop database 数据库名;
-
mysql> mysqladmin -u root -p drop 数据库名;
mysql数据类型
- 数值类型:严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。
- 日期/时间:DATETIME、DATE、TIMESTAMP、TIME和YEAR。
- 字符串类型:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
创建数据表
CREATE TABLE table_name (column_name column_type);
演示:
- 表名
- 表字段名
- 定义每个表字段
mysql> create DATABASE DATA1;
Query OK, 1 row affected (0.01 sec)
mysql> use DATA1;
Database changed
mysql> CREATE TABLE data_tbl(
-> data_id INT NOT NULL AUTO_INCREMENT,
-> data_title VARCHAR(100) NOT NULL,
-> data_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY (data_id)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)
删除数据表
mysql> DROP TABLE table_name ;
插入数据
mysql> INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );;
演示:
mysql> use DATA1;
Database changed
mysql> INSERT INTO data_tbl (data_title,data_author,submission_date)
VALUES
("学习 mysql","星期四",NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO data_tbl (data_title,data_author,submission_date)
VALUES
("学习 java","星期五",NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from data_tbl;
+---------+--------------+-------------+-----------------+
| data_id | data_title | data_author | submission_date |
+---------+--------------+-------------+-----------------+
| 1 | 学习 mysql | 星期四 | 2023-04-14 |
| 2 | 学习 java | 星期五 | 2023-04-14 |
+---------+--------------+-------------+-----------------+
2 rows in set (0.00 sec)
NOW() 是一个 MySQL 函数,该函数返回日期和时间。
查询数据
mysql> SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M];
WHERE 子句
mysql> SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....;
演示:
mysql> SELECT * from data_tbl WHERE BINARY data_author="星期四";
+---------+--------------+-------------+-----------------+
| data_id | data_title | data_author | submission_date |
+---------+--------------+-------------+-----------------+
| 1 | 学习 mysql | 星期四 | 2023-04-14 |
+---------+--------------+-------------+-----------------+
1 row in set (0.01 sec)
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
UPDATE 语句
mysql> UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];
演示:
mysql> UPDATE data_tbl SET data_title="学习 javaweb"WHERE data_id=2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * from data_tbl WHERE data_id=2;
+---------+----------------+-------------+-----------------+
| data_id | data_title | data_author | submission_date |
+---------+----------------+-------------+-----------------+
| 2 | 学习 javaweb | 星期五 | 2023-04-14 |
+---------+----------------+-------------+-----------------+
1 row in set (0.00 sec)
DELETE 语句
mysql> DELETE FROM table_name [WHERE Clause];
LIKE 子句
mysql> SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue';
LIKE 子句中使用百分号 **%**字符来表示任意字符,类似于UNIX或正则表达式中的星号 *****。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
UNION
mysql> SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
- DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
- ALL: 可选,返回所有结果集,包含重复数据。
排序
mysql> SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]];
使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
演示:
mysql> SELECT * from data_tbl ORDER BY submission_date DESC;
+---------+----------------+-------------+-----------------+
| data_id | data_title | data_author | submission_date |
+---------+----------------+-------------+-----------------+
| 3 | 学习 jdbc | 星期六 | 2023-04-15 |
| 1 | 学习 mysql | 星期四 | 2023-04-14 |
| 2 | 学习 javaweb | 星期五 | 2023-04-14 |
+---------+----------------+-------------+-----------------+
3 rows in set (0.00 sec)
GROUP BY 语句分组
mysql> SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
演示:
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * from employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | signin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
mysql> SELECT name,COUNT(*)FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
mysql> SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | signin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
mysql> select coalesce(a,b,c);
如果a=null,则选择b;如果b=null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。
mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.00 sec)
连接的使用
演示:
mysql> use DATA1;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程 | 10 |
| RUNOOB.COM | 20 |
| Google | 22 |
+---------------+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 5 | 学习 C | FK | 2017-04-05 |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.00 sec)
-
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
mysql> 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-----------+---------------+--------------+ 4 rows in set (0.02 sec)
等同于WHERE子句
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
-
**LEFT JOIN(左连接):**获取左表所有记录,即使右表没有对应匹配的记录。
mysql> 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +-----------+---------------+--------------+ 5 rows in set (0.01 sec)
-
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
mysql> 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-----------+---------------+--------------+ 5 rows in set (0.00 sec)
NULL值处理
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL:** 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符(不同于 = 运算符),当比较的的两个值相等或者都为 NULL 时返回 true。
演示:
mysql> SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| 菜鸟教程 | NULL |
| Google | NULL |
| FK | 20 |
+---------------+--------------+
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程 | NULL |
| Google | NULL |
+---------------+--------------+
2 rows in set (0.01 sec)
mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB | 20 |
| FK | 20 |
+---------------+--------------+
2 rows in set (0.01 sec)
正则表达式
MySQL中使用 REGEXP 操作符来进行正则表达式匹配
演示:
mysql> SELECT runoob_author FROM runoob_tbl WHERE runoob_author REGEX
P 'com$';
+---------------+
| runoob_author |
+---------------+
| RUNOOB.COM |
| RUNOOB.COM |
+---------------+
2 rows in set (0.02 sec)
事务
一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)
-
用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
-
直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
演示:
mysql> CREATE TABLE data_tbl2(id int(5))engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from data_tbl2;
Empty set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into data_tbl2 value(5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into data_tbl2 value(6);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from data_tbl2;
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.00 sec)
LTER 命令
- 使用 ALTER 命令及 DROP 子句来删除以上创建表的 i某字段
- ADD 子句来向数据表中添加列
- 需要指定新增字段的位置,可以使用MySQL提供的关键字 FIRST (设定位第一列), AFTER 字段名(设定位于某个字段之后)
- 需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句
- ALTER 来修改字段的默认值
- 修改数据表类型,可以使用 ALTER 命令及 TYPE 子句来完成
- 如果需要修改数据表的名称,可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
演示:
mysql> create table testalter_tbl2
-> (
-> i INT,
-> c CHAR(1)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> ALTER TABLE testalter_tbl2 DROP i;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE testalter_tbl2 ADD i INT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM testalter_tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
索引
-
普通索引
-
创建索引
mysql> CREATE INDEX indexName ON table_name (column_name);
-
修改表结构
mysql> ALTER table tableName ADD INDEX indexName(columnName);
-
创建表的时候直接指定
mysql> CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
-
删除索引
mysql> DROP INDEX [indexName] ON mytable;
-
-
唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
-
创建索引
mysql> CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;
-
修改表结构
mysql> ALTER table mytable ADD UNIQUE [indexName] (username(length));
-
创建表的时候直接指定
mysql> CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
-
-
使用ALTER命令添加和删除索引
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
-
使用ALTER命令添加和删除主键
临时表
临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
-
创建临时表
mysq> CREATE TEMPORARY TABLE SalesSummary;
-
删除临时表:默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
mysql> DROP TABLE SalesSummary;
复制表
- 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等。
- 复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令 将完全的复制数据表结构。
- 如果你想复制表的内容,你就可以使用 INSERT INTO … SELECT 语句来实现
元数据
序列使用
-
MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义序列。
-
使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。
-
如果你删除了数据表中的多条记录,并希望对剩下数据的AUTO_INCREMENT列进行重新排列,那么你可以通过删除自增的列,然后重新添加来实现
-
设置序列的开始值
-
在创建时指定
-
创建后指定
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
-
处理重复数据
-
防止表中出现次方数据
在 MySQL 数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。 而 REPLACE INTO 如果存在相同的记录,则先删除掉。再插入新记录。
-
统计重复数据
mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions > 1;
-
过滤重复数据
需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
-
删除重复数据
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
也可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录
mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);
注入
导出数据
-
SELECT…INTO OUTFILE
-
mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本,其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 导出数据需要使用 –tab 选项来指定导出文件指定的目录,该目标必须是可写的。
演示:
[rillia@localhost ~]$ mysqldump -u root -p DATA1 data_tbl > dump.txt
Enter password:
导入数据
-
mysql命令导入
mysql> mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql);
-
source命令导入
mysql> create database abc; # 创建数据库 mysql> use abc; # 使用已创建的数据库 mysql> set names utf8; # 设置编码 mysql> source /home/abc/abc.sql # 导入备份数据库
-
使用LOAD DATA导入数据
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
-
使用mysqlimport导入数据
[rillia@localhost ~]$ mysqlimport -u root -p --local mytbl dump.txt password *****
函数
- 字符串函数
- 数字函数
- 日期函数
- 高级函数
运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符