mysql概述及基本文法(1)

一.mysql概述

一個dbms中可以有多個資料庫，一個資料庫中可以有多張資料表表，我們的資料實際就是存儲在資料表中

dbms 資料庫管理系統

database 資料庫

table 資料表

field 字段，也就是資料表的列

record 記錄，資料表中每一行就是一個記錄

二.mysql語句

1.語句分類

DDL(data definition language) 資料定義語言，就是建立/修改/查詢/删除資料庫，建立/修改/查詢/删除資料表之類的操作，create,alter drop,show..

DML(data manipulation language) 資料操縱語言，就是添加/修改/查詢/删除資料的語句,insert update select delete..

DCL(data control language) 資料控制語言，控制資料庫，資料表，使用者等的通路權限

2.mysql中求幫助

mysql> help；
mysql> ？

mysql> ? contents

You asked for help about help category: "Contents"

For more information, type \'help <item>\', where <item> is one of the following

categories:

Account Management

Administration

Compound Statements

Data Definition

Data Manipulation

Data Types

Functions

Functions and Modifiers for Use with GROUP BY

Geographic Features

Help Metadata

Language Structure

Plugins

Procedures

Storage Engines

Table Maintenance

Transactions

User-Defined Functions

Utility

mysql> ? Data Definition

You asked for help about help category: "Data Definition"

For more information, type \'help <item>\', where <item> is one of the following

topics:

ALTER DATABASE

ALTER EVENT

ALTER FUNCTION

ALTER LOGFILE GROUP

ALTER PROCEDURE

ALTER SERVER

ALTER TABLE

ALTER TABLESPACE

ALTER VIEW

CONSTRAINT

CREATE DATABASE

CREATE EVENT

CREATE FUNCTION

CREATE INDEX

CREATE LOGFILE GROUP

CREATE PROCEDURE

CREATE SERVER

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE VIEW

DROP DATABASE

DROP EVENT

DROP FUNCTION

DROP INDEX

DROP PROCEDURE

DROP SERVER

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP VIEW

RENAME TABLE

TRUNCATE TABLE

mysql> ? CREATE DATABASE

Name: \'CREATE DATABASE\'

Description:

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this

statement, you need the CREATE privilege for the database. CREATE

SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html

mysql>

三.資料庫的操作

1.建立資料庫

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

也可以建立時指定字元集

mysql> create database db_shop default charset=utf8;
Query OK, 1 row affected (0.00 sec)

2.檢視資料庫

檢視所有的資料庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bank               |
| db_bank            |
| db_itheima         |
| db_shop            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

檢視指定的資料庫

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

3.修改資料庫

mysql5之後就不能修改資料庫名稱了，需要先删除再重新建立

可以修改資料庫的字元集

mysql> alter database db_shop default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

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

4.删除資料庫

mysql> drop database db_shop;
Query OK, 0 rows affected (0.00 sec)