注意:此界面的语句都是在 mysql 命令行界面输入的!!
在 命令提示符 输入:mysql -u root -p,然后再输入 密码,进入 mysql 命令行界面:
C:\Users\Administrator>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.5.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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.
mysql>
一、数据库管理语句
1、查询所有数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | -- mysql元数据,基础数据
| mysql | -- mysql配置数据库,其中包含用户信息。(用户名和密码,权限管理)
| performance_schema | -- mysql数据库软件的运行数据,日志信息,性能数据
| test | -- 测试数据库。空的
+--------------------+
4 rows in set (0.00 sec)
2、创建数据库:
mysql> create database day15; -- 创建数据库
Query OK, 1 row affected (0.10 sec)
mysql> create database day15 -- 指定默认字符集创建数据库
-> default character set utf8;
Query OK, 1 row affected (0.00 sec)
3、查看数据库的默认字符集:
mysql> show create database day15;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| day15 | CREATE DATABASE `day15` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
4、删除数据库:
mysql> drop database day15;
Query OK, 0 rows affected (0.01 sec)
5、修改数据库的字符集:
mysql> alter database day15 default character set gbk;
Query OK, 1 row affected (0.00 sec)
6、选择数据库:
mysql> use day15;
Database changed
二、表管理语句
1、创建表:
mysql> create table student(
-> sid int,
-> sname varchar(20),
-> sage int
-> );
Query OK, 0 rows affected (0.01 sec)
2、查看所有表:
mysql> show tables;
+-----------------+
| Tables_in_day15 |
+-----------------+
| student |
+-----------------+
1 row in set (0.00 sec)
3、查看表结构:
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sage | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
4、删除表:
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)
5、修改表:
1)、添加字段(sgender)
mysql> alter table student add column sgender varchar(2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
2)、删除字段(sgender)
mysql> alter table student drop column sgender;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
3)、修改字段类型(varchar(20) -> varchar(100))
mysql> alter table student modify column sname varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
4)、修改字段名称(sname -> name)
mysql> alter table student change column sname name varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
5)、修改表名称
mysql> alter table student rename to teacher;
Query OK, 0 rows affected (0.01 sec)