mysql columnname2_MySQL系列--2.常用的命令

1 、创建数据库

#语法：

CREATE DATABASE dbName;

#创建数据库rms

create database rms;

2、切换数据库

#选择数据库

USE dbName;

#选择数据库rms;

use rms;

3、 查看所有的数据库名称

SHOW DATABASES;

4、创建表

#语法：

#columnName：字段名称

#columnType：字段类型

CREATE TABLE tableName(columnName,columnTypei);

#创建表customers

CREATE TABLE `customers` (

`customerNumber` int(11) NOT NULL,

`customerName` varchar(50) NOT NULL,

`contactLastName` varchar(50) NOT NULL,

`contactFirstName` varchar(50) NOT NULL,

`phone` varchar(50) NOT NULL

) ;

5、查看数据库中所有的表

SHOW TABLES;

6、查看表结构

#语法：

DESC tableName;

#查看customes的结构

DESC customers;

mysql> DESC customers;

+------------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------------+-------------+------+-----+---------+-------+

| customerNumber | int(11) | NO | | NULL | |

| customerName | varchar(50) | NO | | NULL | |

| contactLastName | varchar(50) | NO | | NULL | |

| contactFirstName | varchar(50) | NO | | NULL | |

| phone | varchar(50) | NO | | NULL | |

+------------------+-------------+------+-----+---------+-------+

5 rows in set (0.03 sec)

7、插入数据

#语法：

INSERT INTO tableName(columnName1,columnName2,columnName N)values(value1,value2,valueN);

#columeName也可以不写

INSERT INTO tableName values(value1,value2,valueN);

INSERT INTO customers(customerNumber,customerName,contactLastName,contactFirstName,phone)values(001,"Stephen Wang",'Stephen','Wang','15687965432');

INSERT INTO customers(customerNumber,customerName,contactLastName,contactFirstName,phone)values(003,"Lucy Liu",'Lucy','Liu','15687965432');

8、查看表中的数据

#语法：

SELECT * FROM tableName;

mysql> select * from customers; +----------------+--------------+-----------------+------------------+-------------+

| customerNumber | customerName | contactLastName | contactFirstName | phone |

+----------------+--------------+-----------------+------------------+-------------+

| 2 | Vicent Wang | Vicent | Wang | 15687965438 |

| 1 | Stephen Wang | Stephen | Wang | 15687965432 |

| 3 | Lucy Liu | Lucy | Liu | 15687965432 |

+----------------+--------------+-----------------+------------------+-------------+

3 rows in set (0.00 sec)

#查看表中的某些字段,语法：

SELECT columnName1,columnName2 from tableName;

mysql> select customerNumber,customerName from customers;

+----------------+--------------+

| customerNumber | customerName |

+----------------+--------------+

| 2 | Vicent Wang |

| 1 | Stephen Wang |

| 3 | Lucy Liu |

+----------------+--------------+

3 rows in set (0.00 sec)

#where条件

#查看客户编号为1的记录

mysql> select customerNumber,customerName from customers where customerNumber=1;

+----------------+--------------+

| customerNumber | customerName |

+----------------+--------------+

| 1 | Stephen Wang |

+----------------+--------------+

1 row in set (0.00 sec)

9、 更新数据

#语法：

UPDATE tableName SET columeName=value where conditions;

#将客户编号为1的客户手机号修改为15997654325

mysql> update customers set phone='15997654325' where customerNumber=1;

Query OK, 1 row affected (0.62 sec)

Rows matched: 1 Changed: 1 Warnings: 0

10、模糊查询

#语法：

select * from tableName where columnName like ''condition [and /or] [columeName = value];

#查询表里last name为Wang的客户

mysql> select * from customers where contactFirstName like '%wang';

+----------------+--------------+-----------------+------------------+-------------+

| customerNumber | customerName | contactLastName | contactFirstName | phone |

+----------------+--------------+-----------------+------------------+-------------+

| 2 | Vicent Wang | Vicent | Wang | 15687965438 |

| 1 | Stephen Wang | Stephen | Wang | 15997654325 |

+----------------+--------------+-----------------+------------------+-------------+

2 rows in set (0.08 sec)

11、排序与分组

排序语法：

select * from tableName order by columnName asc / desc;

#按照客户编号递增排序

mysql> select * from customers order by customerNumber asc;

+----------------+--------------+-----------------+------------------+-------------+

| customerNumber | customerName | contactLastName | contactFirstName | phone |

+----------------+--------------+-----------------+------------------+-------------+

| 1 | Stephen Wang | Stephen | Wang | 15997654325 |

| 2 | Vicent Wang | Vicent | Wang | 15687965438 |

| 3 | Lucy Liu | Lucy | Liu | 15687965432 |

+----------------+--------------+-----------------+------------------+-------------+

3 rows in set (0.00 sec)

#分组语法

#按照contactFirstName分组并统计客户个数

mysql> select contactFirstName,count(*) from customers group by contactFirstName;

+------------------+----------+

| contactFirstName | count(*) |

+------------------+----------+

| Liu | 1 |

| Wang | 2 |

+------------------+----------+

2 rows in set (0.03 sec)

12、修改字段名称

#新增字段语法：

alter table tableName add columnName;

#修改字段语法：

alter table tableName modify columnName;

#删除字段语法：

alter table tableName drop columnName;

#customers添加一个状态字段，类型为char(20)

mysql> alter table customers add staus char(20);

Query OK, 0 rows affected (0.78 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from customers;

+----------------+--------------+-----------------+------------------+-------------+-------+

| customerNumber | customerName | contactLastName | contactFirstName | phone | staus |

+----------------+--------------+-----------------+------------------+-------------+-------+

| 2 | Vicent Wang | Vicent | Wang | 15687965438 | NULL |

| 1 | Stephen Wang | Stephen | Wang | 15997654325 | NULL |

| 3 | Lucy Liu | Lucy | Liu | 15687965432 | NULL |

+----------------+--------------+-----------------+------------------+-------------+-------+

3 rows in set (0.00 sec)

13.、删除

#删除表中的记录,语法：

delete from tableName where conditions;

mysql> delete from customers where customerNumber=1;

Query OK, 1 row affected (0.06 sec

#删除表数据和结构，语法：

drop table tableName;

mysql> drop table customers;

Query OK, 0 rows affected (0.16 sec)

#删除数据库,语法：

drop database dbName;

mysql> drop database rms;

Query OK, 0 rows affected (0.13 sec)