SQL

最新推荐文章于 2024-11-26 16:35:41 发布

七小琦

最新推荐文章于 2024-11-26 16:35:41 发布

阅读量134

点赞数

分类专栏：编程语言

本文链接：https://blog.youkuaiyun.com/qq_41986166/article/details/103582160

版权

编程语言专栏收录该内容

1 篇文章

订阅专栏

展示所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wisdom             |
+--------------------+
5 rows in set (0.03 sec)

创建数据库

mysql> CREATE DATABASE sq;
Query OK, 1 row affected (0.01 sec)

drop 命令删除数据库

mysql> drop database sq;
Query OK, 0 rows affected (0.15 sec)

使用某个数据库

mysql> use wisdom;
Database changed

显示指定数据库所有的表

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.06 sec)

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

mysql> show columns from db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.08 sec)

显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

mysql> show index from db;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| db    |          0 | PRIMARY  |            1 | Host        | A         | NULL        | NULL     | NULL   |      | BTREE      |         |               |
| db    |          0 | PRIMARY  |            2 | Db          | A         | NULL        | NULL     | NULL   |      | BTREE      |         |               |
| db    |          0 | PRIMARY  |            3 | User        | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| db    |          1 | User     |            1 | User        | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.04 sec)

创建数据表

CREATE TABLE table_name (column_name column_type);

mysql> create table book(name char(20), author char(20));
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+--------------+
| Tables_in_sq |
+--------------+
| book         |
+--------------+
1 row in set (0.04 sec)

mysql> select * from book;
Empty set

插入内容到表：INSERT INTO xxx VALUES();

mysql> insert into book values('qixoqi','niuren');
Query OK, 1 row affected (0.06 sec)

mysql> insert into book values('qixoqi','niuren');
Query OK, 1 row affected (0.01 sec)

mysql> select * from book;
+--------+--------+
| name   | author |
+--------+--------+
| qixoqi | niuren |
| qixoqi | niuren |
+--------+--------+
2 rows in set (0.04 sec)

查询
SELECT * from 表名 WHERE 条件;

mysql> select * from book where author='niuren';
+--------+--------+
| name   | author |
+--------+--------+
| qixoqi | niuren |
| qixoqi | niuren |
+--------+--------+
2 rows in set (0.04 sec)

更新

mysql> update book set author='sasa' where name='qixoqi';
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from book;
+--------+--------+
| name   | author |
+--------+--------+
| qixoqi | sasa   |
| qixoqi | sasa   |
+--------+--------+
2 rows in set (0.05 sec)

like语句

mysql> select * from book where author like '%sa';
+--------+--------+
| name   | author |
+--------+--------+
| qixoqi | sasa   |
| qixoqi | sasa   |
+--------+--------+
2 rows in set (0.15 sec)

排序

mysql> select * from book;
+----------+--------+
| name     | author |
+----------+--------+
| qixoqi   | sasa   |
| qixoqi   | sasa   |
| qixiaoqi | nanren |
| wwwwwi   | sss    |
+----------+--------+
4 rows in set (0.03 sec)

mysql> select * from book order by author ASC;
+----------+--------+
| name     | author |
+----------+--------+
| qixiaoqi | nanren |
| qixoqi   | sasa   |
| qixoqi   | sasa   |
| wwwwwi   | sss    |
+----------+--------+
4 rows in set (0.03 sec)