MySQL Commands

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges; 

or 

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db's.

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

华为海思:大小海思的双轮驱动战略分析 大海思的定位清晰明确,即专注于为华为内部提供高性能、高集成度的芯片解决方案,以支持华为终端、服务器等业务的快速发展。小海思的产品线广泛,涵盖了广域物联、短距物联、手机穿戴、显示交互、智慧视觉、智慧媒体、MCU、模拟以及光模块等多个领域。通过提供多样化的芯片解决方案,小海思旨在满足外部客户的不同需求,拓展华为海思在芯片市场的份额和影响力。大海思的产品布局主要围绕华为的内部需求展开。在智能手机应用处理器方面,大海思的麒麟系列芯片已经成为华为手机的标志性产品,以其出色的性能和能效比赢得了市场的广泛认可。 阅读详情

相关推荐

BES平台集成第三方音频算法避坑指南:以声加ENC为例,聊聊文件放置、内存分配与授权那些事

本文详细解析了在BES平台上集成声加ENC降噪算法的全流程,包括技术选型、系统架构设计、内存管理及量产授权等关键环节。特别针对BES平台的双核架构和内存限制,提供了文件放置、内存分配与授权验证的实用解决方案,帮助开发者高效实现音频算法集成。

weixin_33720956的博客 515

mysql命令(command)

MySQL命令(command)

wgchen 4235

从ISCXTor2016到CICIDS2017:手把手教你下载和使用UNB的网络安全数据集

本文详细介绍了UNB大学发布的ISCXTor2016和CICIDS2017等网络安全数据集的下载与使用方法,特别适合加密流量识别和网络行为分析研究。文章提供了多种高效获取路径、文件结构解析及实战分析示例,帮助研究者快速上手这些真实场景捕获的精细标注数据集。

weixin_42524945的博客 641

MYSQL command

mysql命令[转] 来自:http://www.cnblogs.com/zhangzhu/archive/2013/07/04/3172486.html 1、连接到本机上的MYSQL。 首先打开DOS窗口,然后进入目录mysql\bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密...

qq_40938267的博客 4045

深度学习的卷积神经网络的煤矸石识别

在本博文中,我们提出了一种基于深度学习的煤矸石检测与识别系统,该系统采用YOLOv8算法对煤矸石进行检测和识别,实现对图片中的煤矸石进行准确识别。1.项目需求对煤矸石进行检测与识别,系统将识别出图片中的克服小块煤矸石漏检的缺陷。 2.项目实现 通过调研,本项目最终使用yolov8模型作为目标检测的模型, YOLOv8 旨在快速、准确且易使用,使其成为广泛的物体检测、图像分割和图像分类任务的极佳选择。 1.数据集:格式为jpg的3091张煤矸石图片2.YOLOv8环境配置yolo检测网络包括24个卷积层

2201_75355700的博客 792

GD32F103RCT6/GD32F303RCT6(6.2)USART串口实战:从寄存器配置到DMA高效传输

本文详细介绍了GD32F103RCT6和GD32F303RCT6的USART串口通信实战,从寄存器配置到DMA高效传输的全过程。通过具体的代码示例和优化建议,帮助开发者快速掌握串口通信的关键技术,提升嵌入式开发效率。特别强调了DMA在高速数据传输中的优势和应用技巧。

weixin_33725239的博客 309

MySqlCommand创建

MySqlCommand创建

againknow的博客 445

告别过热焦虑:用Ansys Icepak给你的电子设备做个“水冷体检”(附冷板案例全流程)

本文详细介绍了如何利用Ansys Icepak进行电子设备水冷系统的热仿真分析,帮助工程师解决设备过热问题。通过冷板案例全流程演示,展示了从几何处理、材料设定到求解器设置和后处理的关键步骤,提升散热设计效率与准确性。

weixin_29299807的博客 442

c#学习(二)--c#中MySqlCommand用法

c#学习(二)--c#中MySqlCommand用法

ChenJin_2的博客 1438

避坑指南:ANSYS流固耦合计算中System Coupling数据传递设置与常见报错解决

本文深入解析ANSYS Workbench中System Coupling模块在双向流固耦合(FSI)计算中的应用,详细讲解数据传递机制、典型报错解决方案及高阶调试技巧。通过实际案例展示如何优化耦合步长、处理大变形问题,并提升并行计算效率,帮助工程师有效规避常见技术陷阱。

weixin_29213525的博客 731

清华源pip install出现ERROR: Could not find a version that satisfies the requirement的解决方案

本文主要介绍了清华源pip install出现ERROR: Could not find a version that satisfies the requirement的解决方案,希望能对学习python的同学们有所帮助。 文章目录 1. 问题描述 2. 解决方案

weixin_43178406的博客 8万+

数据库mysql

一.mysql的安装 1. 从网上安装 sudo apt-get install mysql-server。装完已经自动配置好环境变量,可以直接使用mysql的命令。     注:建议将/etc/apt/source.list中的cn改成us,美国的服务器比中国的快很多。 二.mysql的启动 允许root远程登陆:     1)本机登陆mysql:mys

让代码飞~~~~~ 467

网络安全系列-四十三:使用Suricata分析恶意流量pcap文件

本文基于[网络安全系列-三十六:使用Suricata IDS分析pcap文件]和 [网络安全系列-四十二: Suricata之rulesets的激活、更新及动态加载]的基础上,使用suricata针对包含恶意流量的pcap文件进行分析,触发事件告警,并对suricata输出的日志进行逐个分析,让你掌握针对恶意流量的分析步骤,及怎么查看suricata输出的日志。

penriver的博客 2890

mysql 中COMMANDS 支持的所有命令

typedef struct {   const char *name; /* User printable name of the function. */   char cmd_char; /* msql command character */   int (*func)(String *str,char *); /* Function to call to do the job. *

yuan_hs_hf的专栏 776

jdk-8u144-linux-x64.tar.gz.

该资源为搭建Linux系统Java环境用,资源的后缀为tar.gz格式的压缩包

老版QQ原生表情gif动态

这是老版的QQ表情,不过全是动态的的,Windows照片查看器是不会动的,可以右击用浏览器打开查看,再说一遍是老版的,只是分享给宁愿用老版也要动态的人。

commands out of sync mysql,MySQL-python: Commands out of sync

在给 MySQL 数据库访问层增加新功能时遇到了这样的错误:ProgrammingError: (2014, "Commands out of sync; you can't run this command now")之前零星地见到过几次,因为发生频度很低,就没有太在意,这次找了一下原因,MySQL 文档对 Commands out of sync 错误的描述是这样的:If you get Co...

weixin_39561431的博客 358

数学建模模型总结

这是本人在两年内准备参加数学建模竞赛搜集到的模型资料,已经经过了本人的筛选和整理,希望能够对准备参加数学建模竞赛的学弟学妹们提供一些帮助!

Mysql问题一则:Commands out of sync

最近在使用C++访问mysql(使用mysql提供的C API)时,偶尔会出现一个错误:Commands out of sync; you can’t run this command nowMysql文档中说明错误:Commands out of sync If you get “Commands out of sync; you can’t run this command now” in y

jiange_zh的博客 3万+

mysql 出现 Commands out of sync; you can't run this command now

存储过程中就只有一句普通的select * from ...; 第一次调用成功, 然后mysql_stmt_close().. 再调一次,结果mysql_stmt_prepare()这一句,就出现CR_COMMANDS_OUT_OF_SYNC错误。 郁闷啊。。。  ------Solutions------ 你的代码是什么?  ------Solutions------ void 

woshiyuanlei的专栏 2万+

mysql 出现 Commands out of sync you can't run this command no

mysql 出现 Commands out of sync you can't run this command no

qq_44949789的博客 671

mysql的CR_COMMANDS_OUT_OF_SYNC错误

mysql执行sql语句时有时会出现CR_COMMANDS_OUT_OF_SYNC错误(2014)提示语为commands out of sync; you cant run this command now网上对此错误的描述很少,而且没有给出解决方法。其实这是因为上一个sql执行返回了多个结果集,但没有把结果都取出来,就执行了新的sql语句,就会产生这个错误。应在sql语句

rickypc的专栏 3233

mysql commands_Mysql Commands

start service:mysqld --console;start client: mysql -uroot -proot;check server version: show variables like '%version%';check status: status or show veriables like '%char%';change charset:SET character...

weixin_33791488的博客 156

PHP MySQL报错Commands out of sync

Commands out of sync; you can’t run this command now Mysql文档中说明错误: Commands out of sync If you get “Commands out of sync; you can’t run this command now” in your client code, you are calling client functions in the wrong order. This can happen, for examp

huxyc的博客 460

mysql的2014错误解决,CR_COMMANDS_OUT_OF_SYNC错误

今天一个mysql 2014错误折磨了我好长时间。  网上狂搜一通,没有解决方法。在我的问题解决之后发现这个是函数使用造成的,不属于mysql的bug 找到一篇文章,很有帮助 命令不同步 如果遇到“命令不同步”错误,将无法在你的客户端代码中运行该命令,你正在以错误顺序调用客户端函数。 例如,如果你正使用mysql_use_result(),并打算在调用mysql_free_resu

liupengying的专栏 3661

mysql Commands out of sync

f you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order. This can happen, for example, if you are using mysql_use_r

xiuzhentianting的博客 672

mysql 2014 commands out of sync,请问mysql++有关问题,commands out of sync

请教mysql++问题,commands out of sync我用mysql++ api来操作mysql数据库,部分大致程序如下:Connection con;con.set_option(new MultiStatementsOption(true));if (!con.connect(mysqlpp::examples::db_name, cmdline.server(),cmdline.u...

weixin_29955195的博客 230

MySQL CR_COMMANDS_OUT_OF_SYNC

mysql_error: commands out of sync; you can't run this command now.    why:    after you used 'mysql_use_result', but didn't finish it with        'mysql_fetch_row' until NULL.    after you got mul

u013411808的博客 581

MySQL commands

This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use t

含糊不清为的博客 462
上一篇: How-to: Write an Asterisk Module, Part 3
下一篇: kamailio,asteisk,realtime 实现负载均衡(集群)完整配置笔记
whatsyourname
博客等级 码龄23年 13粉丝 326原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值