MYSQL数据库操作

删除表

mysql> drop table workerlist;
Query OK, 0 rows affected (0.29 sec)

创建表

CREATE TABLE workerlist(
_id int(11) primary key AUTO_INCREMENT,
email varchar(30),
password varchar(30),
name varchar(30),
phone varchar(30),
taddress varchar(150)
)DEFAULT CHARSET=utf8;

查看表结构

mysql> desc workerlist;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| _id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(30)  | YES  |     | NULL    |                |
| password | varchar(30)  | YES  |     | NULL    |                |
| name     | varchar(30)  | YES  |     | NULL    |                |
| phone    | varchar(30)  | YES  |     | NULL    |                |
| taddress | varchar(150) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)

mysql>

查看表编码

mysql> show create table workerlist;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                              |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| workerlist | CREATE TABLE `workerlist` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(30) DEFAULT NULL,
  `password` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `taddress` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

查看数据库编码

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> 

虚拟机MySQL进入命令

mysqladmin -u root password 'root'

云服务器MySQL进入命令

mysql -u root -p

修改数据库编码

查看数据库编码
    mysql> SHOW VARIABLES LIKE 'character%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | utf8                       |
    | character_set_connection | utf8                       |
    | character_set_database   | latin1                     |
    | character_set_filesystem | binary                     |
    | character_set_results    | utf8                       |
    | character_set_server     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)

    mysql> 
my.cnf设置编码
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8
修改后重启MySQL
    [root@space Desktop]# service mysqld restart

MYSQL5.5修改编码

在my.cnf中添加[client]
[client]
default-character-set=utf8

在[mysqld]下添加
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
登录MySQL使用mysql> SHOW VARIABLES LIKE 'character%';查看编码
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

查看某列各个类别的数量

select tclass,count(3) AS counts from orderlist group by tclass;

获取最新的一个自增ID

select max(_id) from workerlist;