MySQL基本用法

---

1. MySQL的程序组成

---

mysql程序分客户端和服务器端两种：

• 客户端

  • mysql：CLI交互式客户端程序
  • mysql_secure_installation：安全初始化
  • mysqldump：mysql备份工具
  • mysqladmin：执行管理操作，可以用来检查服务器的配置和当前状态、创建和删除数据库等

• 服务器端

  • mysqld：通过开启此程序，开启mysql

mysql工具用法：

语法：mysql [OPTIONS] [databases]

常用的OPTIONS：
-uUSERNAME    指定用户名，默认为root
-hHOST        指定服务器主机，默认为localhost
-pPASSWORD    指定用户的密码
-P#           指定数据监听的端口，#需用实际的端口号代替
-V            查看当前使用的mysql版本
-e            不登录mysql执行sql语句后退出

登录mysql：mysql -uroot -p123456 -h127.0.0.1

服务器监听的两种socket地址：

• ip socket 默认监听在tcp3306端口，支持远程通信
• unix sock 监听在sock文件(/tmp/mysql.sock,/var/lib/mysql/mysql.sock)上，仅支持本地通信，server地址只能为localhost，127.0.01

2. MySQL数据库操作

---

2.1 DLL操作

DDL操作包括定义数据库、表、列。create 创建，show 查看，drop 删除

2.1.1 数据库操作

• 创建数据库

mysql> create database if not exists abc;
Query OK, 1 row affected (0.00 sec)

• 查看MySQL服务器中有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.09 sec)

• 查看某个数据库的定义信息

mysql> show create database sys;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| sys      | CREATE DATABASE `sys` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

• 查看建库语句

mysql> show create databases abc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases abc' at line 1
mysql> show create database abc;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| abc      | CREATE DATABASE `abc` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.01 sec)

• 删除数据库

mysql> drop database if exists abc;
Query OK, 0 rows affected (0.05 sec)

• 切换数据库

mysql> use sys;
Database changed

2.1.2 表操作

• 创建表

格式：create table 表名(字段名 类型 修饰符，字段名 类型 修饰符)

例：
mysql> use abc
Database changed
mysql> create table ggg(id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.00 sec)

• 查看当前数据库有哪些表

mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| ggg           |
+---------------+
1 row in set (0.00 sec)

• 设定主键约束和自动递增创建表

mysql> create table abc(id int primary key auto_increment not null,name varchar(50) not null,age tinyint);
Query OK, 0 rows affected (0.03 sec)
//设定了主键约束的列，列中的字段只能出现一次。且必须提供数据，不能为空
//int为整数类型，primary key为主键，auto_increment为自动递增，not null表示不能为空，varchar为字符串类型，tinyint整数类型，值为0-255

mysql> desc abc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

• 查看表结构

mysql> desc ggg;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 查看建表语句

mysql> show create table ggg;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| ggg   | CREATE TABLE `ggg` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• 删除表

mysql> drop table if exists ggg;
Query OK, 0 rows affected (0.01 sec)

• 删除列

语法：alter TABLE 表名 DROP 列名;

mysql> alter table ggg drop age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ggg;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 修改表名

mysql> rename table ggg to heihei;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| heihei        |
+---------------+
1 row in set (0.00 sec)

• 修改列名

语法：alter TABLE 表名 CHANGE 列名 新列名 列类型;

mysql> alter table heihei change id num int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc heihei;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

• 修改表的字符集

语法：alter TABLE 表名 CHARACTER SET 字符集

mysql> alter table abc character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE abc;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc   | CREATE TABLE `abc` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• 添加列

语法：alter table 表名 add 列名 列类型;

mysql> alter table heihei add age tinyint;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc heihei;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)