正文

text我简单的mysql命令行cheatsheet(代码片段)

author  author  2022-12-03  265

关键词：

MySQL
===============

Getting started: 
- http://www.sqlteaching.com/
- https://www.codecademy.com/courses/learn-sql

Related tutorials:
- [MySQL-CLI](https://www.youtube.com/playlist?list=PLfdtiltiRHWEw4-kRrh1ZZy_3OcQxTn7P)
- [Analyzing Business Metrics](https://www.codecademy.com/learn/sql-analyzing-business-metrics)
- [SQL joins infografic](https://lh4.googleusercontent.com/-RdjzcoAwBYg/UxTXWGJHgoI/AAAAAAAACrs/Gqbu6zyksgo/w852-h670/sql-joins.jpg)

Tools:
- [DataGrip](https://www.jetbrains.com/datagrip/)
- [Sequel Pro](http://www.sequelpro.com/)


Commands
-----------

Access monitor: `mysql -u [username] -p;` (will prompt for password)

Show all databases: `show databases;`

Access database: `mysql -u [username] -p [database]` (will prompt for password)

Create new database: `create database [database];`

Select database: `use [database];`

Determine what database is in use: `select database();`

Show all tables: `show tables;`

Show table structure: `describe [table];`

List all indexes on a table: `show index from [table];`

Create new table with columns: `CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);`

Adding a column: `ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);`

Adding a column with an unique, auto-incrementing ID: `ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;`

Inserting a record: `INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');`

MySQL function for datetime input: `NOW()`

Selecting records: `SELECT * FROM [table];`

Explain records: `EXPLAIN SELECT * FROM [table];`

Selecting parts of records: `SELECT [column], [another-column] FROM [table];`

Counting records: `SELECT COUNT([column]) FROM [table];`

Counting and selecting grouped records: `SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];`

Selecting specific records: `SELECT * FROM [table] WHERE [column] = [value];` (Selectors: `<`, `>`, `!=`; combine multiple selectors with `AND`, `OR`)

Select records containing `[value]`: `SELECT * FROM [table] WHERE [column] LIKE '%[value]%';`

Select records starting with `[value]`: `SELECT * FROM [table] WHERE [column] LIKE '[value]%';`

Select records starting with `val` and ending with `ue`: `SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';`

Select a range: `SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];`

Select with custom order and only limit: `SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];` (Order: `DESC`, `ASC`)

Updating records: `UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];`

Deleting records: `DELETE FROM [table] WHERE [column] = [value];`

Delete *all records* from a table (without dropping the table itself): `DELETE FROM [table];`
(This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: `truncate table [table];`

Removing table columns: `ALTER TABLE [table] DROP COLUMN [column];`

Deleting tables: `DROP TABLE [table];`

Deleting databases: `DROP DATABASE [database];`

Custom column output names: `SELECT [column] AS [custom-column] FROM [table];`

Export a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysqldump -u [username] -p [database] > db_backup.sql`

Use `--lock-tables=false` option for locked tables (more info [here](http://stackoverflow.com/a/104628/1815847)).

Import a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysql -u [username] -p -h localhost [database] < db_backup.sql`

Logout: `exit;`


Aggregate functions
-----------

Select but without duplicates: `SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00`

Calculate total number of records: `SELECT SUM([column]) FROM [table];`

Count total number of `[column]` and group by `[category-column]`: `SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];`

Get largest value in `[column]`: `SELECT MAX([column]) FROM [table];`

Get smallest value: `SELECT MIN([column]) FROM [table];`

Get average value: `SELECT AVG([column]) FROM [table];`

Get rounded average value and group by `[category-column]`: `SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];`


Multiple tables
-----------

Select from multiple tables: `SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];`

Combine rows from different tables: `SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];`

Combine rows from different tables but do not require the join condition: `SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column];` (The left table is the first table that appears in the statement.)

Rename column or table using an _alias_: `SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];`


Users functions
-----------

List all users: `SELECT User,Host FROM mysql.user;`

Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`

Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`


Find out the IP Address of the Mysql Host
-----------
`SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))

alias mysql=/Applications/MAMP/Library/bin/mysql

[mysqld]
max_allowed_packet=64M

mySQL 命令行替代方案 - 远程工具

...道任何替代方案或我可以使用哪些工具让我的生活变得更简单？谢谢【问题讨论】：查看详情

最简单的命令行钉钉机器人发群信息(代码片段)

红色文字内容替换成自己的token，就可以通过命令行发布自己的钉钉群通知了 curl-H"Content-Type:application/json"-d‘"msgtype":"text","text":"content":"我就是我,是不一样的烟火"‘https://oapi.dingtalk.com/robot/send?access_token=XXXXXXX 更查看详情

进入mysql命令行之后，怎么退出

参考技术A进入mysql命令行之后可以按键ctrl+c来退出mysql命令客户端；也可以在命令行输入quit按回车键来退出命令行。扩展资料mysql命令行常用命令使用方法介绍：1、连接和断开服务器shell>mysql-hhost-uuser-pEnterpassword:********host和use... 查看详情

我无法从 Windows 中的命令行启动 MySQL 服务器

】我无法从Windows中的命令行启动MySQL服务器【英文标题】：IcannotstarttheMySQLServerfromcommandlineinwindows【发布时间】：2015-01-0603:46:22【问题描述】：每当我编写命令“C:\\ProgramFiles\\MySQL\\MySQLServer5.0\\bin\\mysqld”时，命令提示符都会返回... 查看详情

MySQL 工作台中的交互式命令行

】MySQL工作台中的交互式命令行【英文标题】：InteractivecommandlinefromwithinMySQLworkbench【发布时间】：2014-08-2912:31:24【问题描述】：我一直在MySQL工作台的SQL查询面板中编写和运行查询。但是，有时我需要运行一个我不想保存的一次... 查看详情

使用 MySQL 命令行客户端

】使用MySQL命令行客户端【英文标题】：UsingMySQLCommandLineClient【发布时间】：2011-09-1904:08:42【问题描述】：我是在MySQL中创建数据库的新手。我已经在MySQLWorkbench中创建了表。现在我想检查我创建的数据库。我该怎么做？我应该在... 查看详情

从命令行下载 MySQL 转储

】从命令行下载MySQL转储【英文标题】：DownloadingMySQLdumpfromcommandline【发布时间】：2012-11-0904:43:30【问题描述】：我将离开Linode，因为我没有必要的Linux系统管理员技能；在完成向更适合新手的服务的过渡之前，我需要下载MySQL数... 查看详情

如何使用 MySQL 中的命令行获取用户帐户列表？

】如何使用MySQL中的命令行获取用户帐户列表？【英文标题】：HowcanIgetalistofuseraccountsusingthecommandlineinMySQL?【发布时间】：2010-11-1105:42:37【问题描述】：我正在使用MySQL命令行实用程序，并且可以浏览数据库。现在我需要查看用户... 查看详情

如何使用 MySQL 中的命令行获取用户帐户列表？

基于行中存在的两个元素插入 MySQL

...发布时间】：2011-02-1714:51:46【问题描述】：我想这是一个简单的问题，但我认为我遇到了语法问题。我有一个表，其中有一个事件ID作为主键(eventID)，一个设备ID是触发事件的设备ID，以及事件触发时间的时间戳。我正在通过C#和... 查看详情

从命令行获取 mysql 列

】从命令行获取mysql列【英文标题】：gettingmysqlcolumnfromthecommandline【发布时间】：2014-03-3112:37:13【问题描述】：我正在从cmd访问mysql，并且我想从特定表中获取所有行，但我找不到执行此操作的确切命令。例子：SHOWDATABSES#showsallav... 查看详情

无法让 sublime text 3 命令行工具工作

】无法让sublimetext3命令行工具工作【英文标题】：cannotgetsublimetext3commandlinetoolstowork【发布时间】：2017-02-1218:18:00【问题描述】：我使用了sublimetext2命令行工具。当我下载SublimeText3时，我无法使用命令行工具。我在这里尝试了所... 查看详情

从脚本在命令行上运行 MySQL 查询 [重复]

】从脚本在命令行上运行MySQL查询[重复]【英文标题】：RunMySQLqueryoncommandlinefromascript[duplicate]【发布时间】：2017-01-1423:09:59【问题描述】：如何使用mysql客户端通过ubuntu命令行从脚本运行mysql查询？我知道我可以使用以下命令：mysq... 查看详情

如何在 MySQL 的命令行中显示变量的值？

】如何在MySQL的命令行中显示变量的值？【英文标题】：HowtodisplaythevalueofavariableatthecommandlineinMySQL?【发布时间】：2013-09-2105:28:44【问题描述】：我尝试了以下-我在命令提示符下创建了一个变量如下-mysql>set@myId=1;QueryOK,0rowsaffecte... 查看详情

正文

text我简单的mysql命令行cheatsheet(代码片段)