07_mysql常用sql语句

一、数据库相关

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 start

2.重命名旧库下边的所有表

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)