mysql学习(一)-常用操作

常用操作

查看mysql状态

• ps查看mysql进程：ps -ef|grep mysql，是否有mysqld_safe 和mysqld进程

  $ ps -ef|grep mysqld
  root 4052 29869 0 10:25 pts/0 00:00:00 grep mysqld
  root 30905 1 0 10:14 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
  mysql 31231 30905 1 10:14 pts/0 00:00:09 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log- 　　　　                 error=/usr/local/mysql/data/localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/tmp/mysqld.sock --port=3306 
  

• 如果已添加到系统服务中，可以使用：service mysqld status

  $ service mysqld status
  SUCCESS! MySQL running (31231)
  

启动msql服务

• /etc/init.d/mysqld start，其简化命令为：service mysqld start

停止mysql服务

• /etc/init.d/mysqld stop，其简化命令为：service mysqld stop

重启mysql服务

• /etc/init.d/mysqld restart，其简化命令为：service msqld restart

shell操作

• 使用shell登录mysql：mysql -u root -p

  $ mysql -u root -p  # 使用root用户登录mysql；-u,user登录用户，-p，password密码
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g. 
  Your MySQL connection id is 207 
  Server version: 5.1.72-2 (Debian) 
  
  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 
  
  Oracle is a registered trademark of Oracle Corporation and/or its 
  affiliates. Other names may be trademarks of their respective 
  owners. 
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  
  

• 列出所有数据库：show dotabases

  mysql> show databases; 
  +--------------------+ 
  | Database           | 
  +--------------------+ 
  | information_schema | 
  | a1                 | 
  | cloud              | 
  | mysql              | 
  | phpmyadmin         | 
  | playsms            | 
  | sisso              | 
  | test               | 
  | ukolovnik          | 
  | wordpress          | 
  +--------------------+ 
  10 rows in set (0.14 sec)
  

• 切换数据库：use database_name

  mysql> use cloud; # 切换至cloud数据库
  Reading table information for completion of table and column names 
  You can turn off this feature to get a quicker startup with -A 
  
  Database changed 
  mysql>
  

• 列出某个数据库内所有表：show tables

  mysql> show tables; 	# 当前数据库为cloud，列出cloud的所有表
  +----------------------------+ 
  | Tables_in_cloud            | 
  +----------------------------+ 
  | oc_appconfig               | 
  | oc_calendar_calendars      | 
  | oc_calendar_objects        | 
  | oc_calendar_repeat         | 
  | oc_calendar_share_calendar | 
  | oc_calendar_share_event    | 
  | oc_contacts_addressbooks   | 
  | oc_contacts_cards          | 
  | oc_fscache                 | 
  | oc_gallery_sharing         | 
  +----------------------------+ 
  10 rows in set (0.00 sec)
  

• 获取表内所有 Field（字段）对象的名称和类型：describe table_name

  mysql> describe oc_users; 
  +----------+--------------+------+-----+---------+-------+ 
  | Field    | Type         | Null | Key | Default | Extra | 
  +----------+--------------+------+-----+---------+-------+ 
  | uid      | varchar(64)  | NO   | PRI |         |       | 
  | password | varchar(255) | NO   |     |         |       | 
  +----------+--------------+------+-----+---------+-------+ 
  2 rows in set (0.00 sec)
  

• 删除表：drop table table_name

  mysql> drop table lookup; 
  
  Query OK, 0 rows affected (0.00 sec)
  

• 删除数据库：drop database database_name

  mysql> drop database a1; 
  
  Query OK, 11 rows affected (0.07 sec)