一、数据库相关
1.创建数据库：
mysql> create database test default character set utf8 collate utf8_general_ci;Query OK, 1 row affected (0.00 sec)
2.查询数据：
查询所有数据库：
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || taotao || test || xmall |+--------------------+6 rows in set (0.00 sec)
查询当前正在使用的数据库：
mysql> select database();+------------+| database() |+------------+| taotao |+------------+1 row in set (0.00 sec)
3.更改数据库信息：
1.更改名字：
已经废弃rename.
1.若库中所有表为MyISAM引擎，则可改库对应的文件夹的名字 （现如今这种可能性几乎没有，而且该方法需要停机，不太实际） 关闭MySQL Server：
sudo /etc/init.d/mysql stop改目录名：
cd /data/mysql; mv old_db new_db;开启MySQL Server
sudo /etc/init.d/mysql start2.重命名旧库下边的所有表
create databases new_db; rename table old_db.table1 to new_db.table1,old_db.table2 to new_db.table2,...; drop database old_db;3.建新库，使用mysqldump工具导出老库数据并导入新库
mysqldump -uuser -ppass -hhost -Pport--single-transaction old_db >old_db.sql mysql -uuser -ppass -hhost -Pport -e "create database new_db" mysql -uuser -ppass -hhost -Pport new_db < old_db.sql mysql -uuser -ppass -hhost -Pport -e "drop database old_db"其中方法2中表比较多的情况下，一个个写这些名称未免太痛苦了，可用shell脚本或者存储过程来完成
#!/bin/bash mysqlconn=" mysql -uuser -ppass -hhost -Pport ” old_db=”old_db ” new_db=”new_db ” $mysqlconn -e “CREATE DATABASE $new_db” params=$($mysqlconn -N -e “SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=’$old_db’”) for name in $params; do $mysqlconn -e “RENAME TABLE $old_db.$name to $new_db.$name”; done; $mysqlconn -e “DROP DATABASE $old_db”更改字符集：mysql> alter database test default character set utf8;Query OK, 1 row affected (0.00 sec)
4.删除数据库mysql> drop database test;Query OK, 0 rows affected (0.00 sec)
5.查看数据库字符编码
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)
6.查看数据库端口
mysql> show variables like 'port';+---------------+-------+| Variable_name | Value |+---------------+-------+| port | 3306 |+---------------+-------+1 row in set (0.00 sec)
二。数据库表操作
1.创建数据表
mysql> create table tb_user( -> id tinyint(20) not null auto_increment primary key, -> username varchar(20) not null)engine=innodb auto_increment=7 default charset=utf8;Query OK, 0 rows affected (0.00 sec)
2.清空表内容
mysql> truncate table tb_user;Query OK, 0 rows affected (0.02 sec)
3.修改表名字
mysql> rename table tb_user to t_user;Query OK, 0 rows affected (0.01 sec)
4.查询所有表
mysql> show tables;+----------------+| Tables_in_test |+----------------+| t_user |+----------------+1 row in set (0.00 sec)
5.删除表
mysql> drop table t_user;Query OK, 0 rows affected (0.01 sec)
6.查询表信息
mysql> desc t_user;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| id | tinyint(10) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | UNI | NULL | |+----------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)
7.增加表信息
mysql> alter table t_user add password varchar(20) not null;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
8.修改列信息
mysql> alter table t_user modify password varchar(15);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
9.修改列名字
mysql> alter table t_user change password pwd varchar(10) not null;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_user; +----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| id | tinyint(10) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | UNI | NULL | || pwd | varchar(10) | NO | | NULL | |+----------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)
10. 删除列名
mysql> alter table t_user drop pwd;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_user;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| id | tinyint(10) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | UNI | NULL | |+----------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)
转载于:https://www.cnblogs.com/smallTiger123/p/9457076.html
