浅谈mysql的基本语句
管理数据库语句
1.创建数据库
CREATE DATABSE datebase_name
MariaDB [(none)]> CREATE DATABASE testdb1;
Query OK, 1 row affected (0.00 sec)
# 查看数据库创建过程
MariaDB [(none)]> SHOW CREATE DATABASE testdb1;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
- 数据创建命令反应到系统就是建立了一个文件夹
2.修改数据库
- 语法
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
- 示例：
MariaDB [(none)]> ALTER DATABASE testdb1 CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW CREATE DATABASE testdb1;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
# 本质是修改系统文件
[root@Centos7 ~]# cat /data/mysql/testdb1/db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
3.查看数据库列表
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t1 |
| testdb1 |
+--------------------+
5 rows in set (0.00 sec)
4.删除数据库
DROP DATABASE datebase_name;
MariaDB [(none)]> DROP DATABASE t1;
Query OK, 0 rows affected (0.00 sec)
查看数据库中的表
- 查看支持的engine类型
MariaDB [testdb1]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
- 查看数据库中的表
MariaDB [mysql]> SHOW TABLES FROM testdb1;
+-------------------+
| Tables_in_testdb1 |
+-------------------+
| studentinfo |
+-------------------+
1 row in set (0.00 sec
MariaDB [testdb1]> USE testdb1;
Database changed
1 row in set (0.00 sec)
MariaDB [testdb1]> SHOW TABLES;
+-------------------+
| Tables_in_testdb1 |
+-------------------+
| studentinfo |
+-------------------+
# 二者的意思是一样的
- 查看表的创建命令
MariaDB [testdb1]> SHOW CREATE TABLE studentinfo\G;
*************************** 1. row ***************************
Table: studentinfo
Create Table: CREATE TABLE `studentinfo` (
`SID` int(11) NOT NULL AUTO_INCREMENT,
`SName` varchar(10) NOT NULL,
`Class` varchar(10) DEFAULT NULL,
`Birth` date DEFAULT NULL,
`Sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
- 查看数据库中表的状态
# 查看某一个表的状态
MariaDB [testdb1]> SHOW TABLE STATUS LIKE 'teacherinfo'\G;
*************************** 1. row ***************************
Name: teacherinfo
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2019-11-20 22:16:58
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR: No query specified
# 查看某一个数据库下面所有表的状态
MariaDB [testdb1]> SHOW TABLE STATUS FROM testdb1\G;
*************************** 1. row ***************************
Name: studentinfo
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 6
Create_time: 2019-11-20 21:37:06
Update_time: 2019-11-20 21:53:30
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: teacherinfo
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2019-11-20 22:16:58
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
- 查看表结构
DESC table_name;
SHOW COLUMNS FROM table_name;
MariaDB [testdb1]> DESC testdb1.teacherinfo;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| teacherID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| TName | varchar(10) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
MariaDB [testdb1]> SHOW COLUMNS FROM testdb1.teacherinfo;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| teacherID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| TName | varchar(10) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
管理数据库表语句
- SQL (结构化查询语言)是用于执行查询的语法。
- SQL语句分类
DDL: Data Defination Language 数据定义语言CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言INSERT，DELETE，UPDATE
DCL：Data Control Language 数据控制语言GRANT，REVOKE，COMMIT，ROLLBACK
DQL：Data Query Language 数据查询语言SELECT
创建数据库中的表
- SQL CREATE TABLE 语法
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
MariaDB [testdb1]> CREATE TABLE studentinfo (
-> studentID int PRIMARY KEY,
-> SName varchar(10),
-> Class varchar(10),
-> Birth date,
-> Sex varchar (10)
-> );
Query OK, 0 rows affected (0.02 sec)
# 查询创建表的信息
MariaDB [testdb1]> SHOW CREATE TABLE studentinfo\G;
*************************** 1. row ***************************
Table: studentinfo
Create Table: CREATE TABLE `studentinfo` (
`studentID` int(11) NOT NULL,
`SName` varchar(10) DEFAULT NULL,
`Class` varchar(10) DEFAULT NULL,
`Birth` date DEFAULT NULL,
`Sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
# 表中每一列的数字类型
MariaDB [testdb1]> DESC studentinfo;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentID | int(11) | NO | PRI | NULL | |
| SName | varchar(10) | YES | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| Birth | date | YES | | NULL | |
| Sex | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 修改SName的数字类型
MariaDB [testdb1]> ALTER TABLE studentinfo MODIFY SName varchar(10) NOT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看修改后SName的数字类型
MariaDB [testdb1]> DESC studentinfo;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentID | int(11) | NO | PRI | NULL | |
| SName | varchar(10) | NO | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| Birth | date | YES | | NULL | |
| Sex | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 修改studentID列名并定义自动递增数据类型
MariaDB [testdb1]> ALTER TABLE studentinfo CHANGE studentID SID int AUTO_INCREMENT;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb1]> DESC studentinfo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| SID | int(11) | NO | PRI | NULL | auto_increment |
| SName | varchar(10) | NO | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| Birth | date | YES | | NULL | |
| Sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
给数据库表中插入记录
INSERT语句：
- 功能：一次插入一行或多行数据
- 语法：
INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE #如果重复更新之
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
- 简化语法
INSERT INTO 表名称 VALUES (值1, 值2,....) - 我们也可以指定所要插入数据的列：
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
MariaDB [testdb1]> INSERT INTO studentinfo values ( 1,'Jack','101','2010-09-30','men');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> INSERT INTO studentinfo values(3,'Alidan','102','2010-02-01','women');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> INSERT INTO studentinfo values(4,'limengmeng','102','1990-01-01','women');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> INSERT INTO studentinfo values ( 2,'Tony','101','2009-12-14','men');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> INSERT INTO studentinfo (SName,Birth,Sex)VALUES('Liudehua','1995-05-05','men');
Query OK, 1 row affected (0.00 sec)
# 结果如下
MariaDB [testdb1]> SELECT * FROM studentinfo;
+-----+------------+-------+------------+-------+
| SID | SName | Class | Birth | Sex |
+-----+------------+-------+------------+-------+
| 1 | Jack | 101 | 2010-09-30 | men |
| 2 | Tony | 101 | 2009-12-14 | men |
| 3 | Alidan | 102 | 2010-02-01 | women |
| 4 | limengmeng | 102 | 1990-01-01 | women |
| 5 | Liudehua | NULL | 1995-05-05 | men |
+-----+------------+-------+------------+-------+
5 rows in set (0.00 sec)
修改数据库中表的记录
Update 语句用于修改表中的数据。
- 语法
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
- 注意：一定要有限制条件，否则将修改所有行的指定字段
- 可利用mysql 选项避免此错误
mysql -U | --safe-updates| --i-am-a-dummy - 简洁写法
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
MariaDB [testdb1]> UPDATE studentinfo SET SName='Linchaoying' WHERE SName='Alidan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb1]> SELECT * FROM studentinfo;
+-----+-------------+-------+------------+-------+
| SID | SName | Class | Birth | Sex |
+-----+-------------+-------+------------+-------+
| 1 | Jack | 101 | 2010-09-30 | men |
| 2 | Tony | 101 | 2009-12-14 | men |
| 3 | Linchaoying | 102 | 2010-02-01 | women |
| 4 | limengmeng | 102 | 1990-01-01 | women |
| 5 | Liudehua | NULL | 1995-05-05 | men |
+-----+-------------+-------+------------+-------+
5 rows in set (0.00 sec)
删除表中的记录
DELETE 语句用于删除表中的行。
- 语法：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数
- 简化语法
DELETE FROM 表名称 WHERE 列名称 = 值 - 注意：一定要有限制条件，否则将清空表中的所有数据
MariaDB [testdb1]> DELETE FROM studentinfo WHERE SName='Jack';
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> SELECT * FROM studentinfo;
+-----+-------------+-------+------------+-------+
| SID | SName | Class | Birth | Sex |
+-----+-------------+-------+------------+-------+
| 2 | Linpingzhi | 101 | 2009-12-14 | men |
| 3 | Linchaoying | 102 | 2010-02-01 | women |
| 4 | limengmeng | 102 | 1990-01-01 | women |
| 5 | Liudehua | NULL | 1995-05-05 | men |
+-----+-------------+------
- 如果想清空表，保留表结构，也可以使用下面语句
DELETE FROM table_name;
TRUNCATE TABLE tbl_name;
# 先克隆两份表出来
MariaDB [testdb1]> CREATE TABLE ceshi1 SELECT * FROM studentinfo;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [testdb1]> CREATE TABLE ceshi2 SELECT * FROM studentinfo;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 使用DELETE 清除表ceshi1 查看表结构
MariaDB [testdb1]> DELETE FROM ceshi1;
Query OK, 4 rows affected (0.01 sec)
MariaDB [testdb1]> SELECT * FROM ceshi1;
Empty set (0.00 sec)
MariaDB [testdb1]> DESC ceshi1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| SID | int(11) | NO | | 0 | |
| SName | varchar(20) | YES | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| Birth | date | YES | | NULL | |
| Sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 使用TRUNCATE 清除表ceshi2 查看表结构
MariaDB [testdb1]> DELETE FROM ceshi2;
Query OK, 4 rows affected (0.01 sec)
MariaDB [testdb1]> SELECT * FROM ceshi2;
Empty set (0.00 sec)
MariaDB [testdb1]> DESC ceshi2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| SID | int(11) | NO | | 0 | |
| SName | varchar(20) | YES | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| Birth | date | YES | | NULL | |
| Sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
查询表的记录
SELECT 语句用于从表中选取数据，结果被存储在一个结果表中（称为结果集）。
- 语法
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
- 简洁语法
SELECT * FROM table_name;
星号（*）是选取所有列的快捷方式。
SELECT columns_name FROM table_name;
注意：SQL语句对大小写不敏感SELECT=select
MariaDB [testdb1]> SELECT * FROM studentinfo;
+-----+-------------+-------+------------+-------+
| SID | SName | Class | Birth | Sex |
+-----+-------------+-------+------------+-------+
| 2 | Linpingzhi | 101 | 2009-12-14 | men |
| 3 | Linchaoying | 102 | 2010-02-01 | women |
| 4 | limengmeng | 102 | 1990-01-01 | women |
| 5 | Liudehua | NULL | 1995-05-05 | men |
+-----+-------------+-------+------------+-------+
4 rows in set (0.00 sec)
MariaDB [testdb1]> SELECT SName,Class FROM studentinfo;
+-------------+-------+
| SName | Class |
+-------------+-------+
| Linpingzhi | 101 |
| Linchaoying | 102 |
| limengmeng | 102 |
| Liudehua | NULL |
+-------------+-------+
4 rows in set (0.00 sec
ing | 102 | 2010-02-01 | women |
| 4 | limengmeng | 102 | 1990-01-01 | women |
| 5 | Liudehua | NULL | 1995-05-05 | men |
+-----+-------------+-------+------------+-------+
4 rows in set (0.00 sec)
MariaDB [testdb1]> SELECT SName,Class FROM studentinfo;
+-------------+-------+
| SName | Class |
+-------------+-------+
| Linpingzhi | 101 |
| Linchaoying | 102 |
| limengmeng | 102 |
| Liudehua | NULL |
+-------------+-------+
4 rows in set (0.00 sec
4210
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
