mysql学习笔记（三）—— 数据表操作（详细）

数据库的基础操作

mysql采用的同样是sql语法，mysql数据表的常用操作已经总结在这篇文章

创建新的数据库

创建数据库

create database 数据库名 （字符集）
mysql> create database test1  character set utf8 collate utf8_bin;

查看数据库的字符集

show create database 数据库名
mysql> show create database test1
    -> ;
+----------+---------------------------------------------------------------------------------+
| Database | Create Database                                                                 |
+----------+---------------------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看所有的数据库

show databases;

删除数据库

drop database 数据库名

使用数据库

use database 数据库名字;

查看数据库里的所有表

show tables;

数据表的基础操作

创建表

mysql> create table classes
    -> (
    -> id int(11) primary key,
    -> name varchar(22),
    -> age int(11),
    -> salary float
    -> );

查看表结构

mysql> desc classes;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

表中添加新字段

## alter table 表名 add 列名 类型
mysql> alter table classes add location varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc classes;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

字段重命名

##  alert table 表名 change 原名 新名 类型及约束
mysql> alter table classes change location LOCATION varchar(50);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc classes;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
| LOCATION | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

删除字段

alter table 表名 drop 列名

mysql> alter table test3 drop id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

drop table 表名

更新数据
语法：

UPDATE table_name SET column1=value1,column2=value2,...WHERE some_column=some_value; 

mysql> update classes set name='yu',salary=7200 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from classes;
+----+-------+------+--------+----------+
| id | name  | age  | salary | LOCATION |
+----+-------+------+--------+----------+
|  1 | zhou  |   26 |  12000 | aaa      |
|  2 | wu    |   22 |   9200 | bbb      |
|  3 | zheng |   20 |   8500 | ccc      |
|  4 | li    |   18 |  13850 | ddd      |
|  5 | yu    |   20 |   7200 | NULL     |
|  6 | cao   | NULL |   NULL | NULL     |
+----+-------+------+--------+----------+
6 rows in set (0.00 sec)

插入数据

全列插入：值的顺序与表结构字段的顺序完全一一对应

insert into 表名 values (...)
mysql> insert into class2 values(001,"zhao",1,488);

部分列插入：值的顺序与给出的列顺序对应

insert into 表名 (列1,列2..) values(值1,值2..)
mysql>  insert into class(id,name) values(3,"sun");

全列多行插入

insert into 表名 values(...),(...)...;
mysql> insert into class values(4,"cao",20,"aaa",8500),(5,"wei",18,"bbb",)7000),(6,"yang",21,"ccc",11000);

部分列多行插入

insert into 表名(列1,...) values(值1,...),(值1,...)...;
mysql> insert into class(id,name) values(10,"wang"),(11,"wu"),(12,"liu");

主键约束

主键约束要求主键列的数据唯一，并且不允许为空

语法： 字段名 数据类型 PRIMARY KEY [默认值]
-> id INT(11) PRIMARY KEY,
或
在定义完所有列之后指定主键
-> PRIMARY KEY(id)

多字段联合主键

## PRIMARY KEY [字段1，字段2，....]
mysql> create table test2
    -> (
    -> name varchar(25),
    -> de int(11),
    -> salary float,
    -> primary key(name,de)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc test2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | NO   | PRI | NULL    |       |
| de     | int(11)     | NO   | PRI | NULL    |       |
| salary | float