MySQL基本概念以及简单操作

<?php
//对数据库的简单操作方法

//显示存在所有的数据库
mysql> show databases;    
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lamp183            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


//删除数据库lamp183
mysql> drop database lamp183;
Query OK, 1 row affected (0.01 sec)


//删除数据库lamp183
mysql> drop database lamp183;
Query OK, 0 rows affected (0.00 sec)


//创建数据库lmap175
mysql> create database lamp175;
Query OK, 1 row affected (0.00 sec)


//查看当前的数据库[没有被使用的数据库即使被创建也不会被查询到]
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)


//使用lamp175数据库
mysql> use lamp175;
Database changed


//当前数据库显示出lamp175,说明此数据库为当前操作的库
mysql> select database();
+------------+
| database() |
+------------+
| lamp175    |
+------------+
1 row in set (0.00 sec)


//查看lamp175的建库语句 /G可以纵向显示
mysql> show create database lamp175\G;
*************************** 1. row ***************************
       Database: lamp175
Create Database: CREATE DATABASE `lamp175` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

ERROR: 
No query specified


//查看lamp175建库语句
mysql> show create database lamp175;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| lamp175  | CREATE DATABASE `lamp175` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> create database lamp183;
Query OK, 1 row affected (0.00 sec)

mysql> use lamp183;
Database changed


//在lamp183数据库中创建stu数据表
mysql> create table stu(    
    -> id int,
    -> name varchar(32),
    -> sex varchar(2),
    -> age int,
    -> job varchar(32)
    -> );
Query OK, 0 rows affected (0.05 sec)


mysql> desc stu;    //查看stu数据表的表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| sex   | varchar(2)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| job   | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


//查看stu的建表语句[可以查看到该表的编码类型,存储类型]
mysql> show create table stu;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `job` varchar(32) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



//查看stu表的建表语句[竖起来查看]
mysql> show create table stu\G;    
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `job` varchar(32) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified


//查看当前数据库中所有的表
mysql> show tables;
+-------------------+
| Tables_in_lamp183 |
+-------------------+
| stu               |
+-------------------+
1 row in set (0.00 sec)


//删除stu数据表
mysql> drop table stu;
Query OK, 0 rows affected (0.00 sec)


//创建stu数据表
mysql> create table stu(
    -> id int,
    -> name varchar(32),
    -> sex varchar(2),
    -> age int,
    -> job varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)


//查看stu数据表的表结构
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| sex   | varchar(2)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| job   | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


//查看stu数据表的所有数据
mysql> select * from stu;
Empty set (0.00 sec)


//在stu数据表中插入数据
mysql> insert into stu set id=9,name=‘haiyang‘,sex=‘w‘,age=38,job=‘toupai‘;
Query OK, 1 row affected (0.00 sec)


//查看stu数据表中所有数据
mysql> select * from stu;
+------+----------+------+------+---------+
| id   | name     | sex  | age  | job     |
+------+----------+------+------+---------+
|    1 | zhangsan | w    |   18 | laobao  |
|    2 | lisi     | m    | NULL | NULL    |
|    3 | wangwu   | w    | NULL | NULL    |
|    4 | maliu    | m    | NULL | NULL    |
|    5 | zhaoqi   | w    | NULL | NULL    |
|    6 | wangba   | m    |   20 | baoan   |
|    7 | haibo    | w    |   28 | guigong |
|    8 | hongze   | m    |   17 | student |
|    9 | haiyang  | w    |   38 | toupai  |
+------+----------+------+------+---------+
9 rows in set (0.00 sec)
//退出MySQL数据库
mysql> \q

MySQL基本概念以及简单操作

标签：key   大小   apache   基本   sts   erro   myisam   ctr   数据库管理