SQL¶

SQL语法¶

数据操作语言 (DML)
数据定义语言 (DDL)

DML¶

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

DDL¶

常用的DDL语句

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

创建数据库¶

CREATE DATABASE sql_test

创建表¶

指定主键

CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

查看建表语句¶

show create table Persons;

mysql> show create table Persons\G
*************************** 1. row ***************************
       Table: Persons
Create Table: CREATE TABLE `Persons` (
  `Id_P` int(11) NOT NULL AUTO_INCREMENT,
  `LastName` varchar(255) DEFAULT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id_P`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ID自增¶

CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

查看表结构¶

mysql¶

mysql> desc Persons;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| Id_P      | int(11)      | NO   | PRI | NULL    | auto_increment |
| LastName  | varchar(255) | YES  |     | NULL    |                |
| FirstName | varchar(255) | YES  |     | NULL    |                |
| Address   | varchar(255) | YES  |     | NULL    |                |
| City      | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

select * from information_schema.columns where table_schema = 'sql_test' and table_name = 'Persons' ;

INSERT INTO¶

语法

INSERT INTO 表名称 VALUES (值1, 值2,....)

或者指定需要插入数据的列

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

插入数据

INSERT INTO Persons (LastName,FirstName,Address,City) VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

查看结果

mysql> select * from Persons;
+------+----------+-----------+----------------+---------+
| Id_P | LastName | FirstName | Address        | City    |
+------+----------+-----------+----------------+---------+
|    1 | Gates    | Bill      | Xuanwumen 10   | Beijing |
|    2 | Wilson   | NULL      | Champs-Elysees | NULL    |
+------+----------+-----------+----------------+---------+
2 rows in set (0.00 sec)

SELECT¶

SELECT 列名称 FROM 表名称
SELECT * FROM 表名称

mysql> SELECT LastName,FirstName FROM Persons;
+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Gates    | Bill      |
| Wilson   | NULL      |
+----------+-----------+
2 rows in set (0.00 sec)

用SQL命令查看Mysql数据库大小¶

1、进入information_schema 数据库（存放了其他的数据库的信息）
use information_schema;

2、查询所有数据的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

3、查看指定数据库的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';