MySQL数据库管理

一、查看数据库结构

1.查看数据库信息

2.查看数据库中的表信息

3显示数据表的结构

二、SQL语句概述

1SQL语言简介
●Structured Query Language的缩写，即结构化查询语言
●关系型数据库的标准语言
●用于维护管理数据库
包括数据查询、数据更新、访问控制、对象管理等功能
2SQL分类
●DDL：数据定义语言
●DML：数据操纵语言
●DQL：数据查询语言
●DCL：数据控制语言

三、MySQL基础命令

mysql> select * from user;    查看表的数据信息
mysql> create database school;     创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> create table student (id int(9) primary key not null,name char(10) not null,socre decimal(5,2) default '0');创建表
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(9)       | NO   | PRI | NULL    |       |
| name  | char(10)     | NO   |     | NULL    |       |
| socre | decimal(5,2) | YES  |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> drop table comany.boss;    删除表
Query OK, 0 rows affected (0.01 sec)
mysql> drop database comany;     删除库
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student(id,name,socre)values(170420101,'zhangqing',91);往表中插入数据
mysql> select * from student;  查看添加信息
+-----------+-----------+-------+
| id        | name      | socre |
+-----------+-----------+-------+
| 170420101 | zhangqing | 91.00 |
| 170420102 | yangwu    | 88.00 |
+-----------+-----------+-------+
2 rows in set (0.00 sec)
mysql> create table teacher as select * from student where name='zhangqing';提取数据库信息到另一个数据库
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from teacher;
+-----------+-----------+-------+
| id        | name      | socre |
+-----------+-----------+-------+
| 170420101 | zhangqing | 91.00 |
+-----------+-----------+-------+
1 row in set (0.00 sec)
mysql> update teacher set socre=100 where id=110;修改更新数据库表中的数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+-----------+-----------+--------+
| id        | name      | socre  |
+-----------+-----------+--------+
| 110 | zhangqing | 100.00 |
+-----------+-----------+--------+
1 row in set (0.00 sec)
mysql> create temporary table tmp(id int (5) not null primary key auto_increment,创建临时表
    -> name char(10) not null,
    -> socre double(5) default '0'
    -> )engine=innodb default charset=utf8;
mysql> desc tmp;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(5)       | NO   | PRI | NULL    | auto_increment |
| name  | char(10)     | NO   |     | NULL    |                |
| socre | decimal(5,2) | YES  |     | 0.00    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> create table test1 as select * from teacher; 克隆
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
| test             |
| test1            |
+------------------+
4 rows in set (0.00 sec)

mysql> desc test1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(9)       | NO   |     | NULL    |       |
| name  | char(10)     | NO   |     | NULL    |       |
| socre | decimal(5,2) | YES  |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)