1、建立数据库
语法:create database 数据库名;
实例:
mysql> create database shujuku;
Query OK, 1 row affected (0.10 sec)
2、进入数据库
语法:use 数据库名;
实例:
mysql> use shujuku;
Database changed
3、新建表
语法:create table 表名(字段,...);
实例:
mysql> CREATE TABLE class1 (
-> id SMALLINT UNSIGNED AUTO_INCREMENT ,
-> name VARCHAR(30) NOT NULL UNIQUE KEY ,
-> school VARCHAR(30) DEFAULT 'jilin_university',
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.18 sec)
4、查看表结构
语法: desc 表名;
实例:
mysql> desc class1;
+--------+----------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+------------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | UNI | NULL | |
| school | varchar(30) | YES | | jilin_university | |
+--------+----------------------+------+-----+------------------+----------------+
3 rows in set (0.05 sec)
5、插入
语法:insert into 表名 set 字段名=‘字段值’,...;
实例:
mysql> insert into class1 set name='baiyang',school='nanhugaozhong';
Query OK, 1 row affected (0.00 sec)
mysql> insert into class1 set name='yueyue';
Query OK, 1 row affected (0.00 sec)
6、修改
语法:update 表名 set 字段=‘修改值’ where 字段=‘原值’,...;
实例:
mysql> update class1 set name='lala' where name='yueyue';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class1;
+----+---------+------------------+
| id | name | school |
+----+---------+------------------+
| 1 | baiyang | nanhugaozhong |
| 2 | lala | jilin_university |
+----+---------+------------------+
2 rows in set (0.00 sec)
7、删除
语法:delete from 表名 where 条件;
实例:
mysql> delete from class1 where school='jilin_university';
Query OK, 1 row affected (0.00 sec)
mysql> select * from class1;
+----+---------+---------------+
| id | name | school |
+----+---------+---------------+
| 1 | baiyang | nanhugaozhong |
+----+---------+---------------+
1 row in set (0.00 sec)
语法:drop table 表名;
实例:
mysql> drop table class1;
Query OK, 0 rows affected (0.01 sec)
语法:drop database 数据库名;
实例:
mysql> drop database shujuku;
Query OK, 0 rows affected (0.36 sec)
8、查询
语法:select * from 表名; 简单查询
实例:
mysql> select * from class1;
+----+---------+------------------+
| id | name | school |
+----+---------+------------------+
| 1 | baiyang | nanhugaozhong |
| 2 | yueyue | jilin_university |
+----+---------+------------------+
2 rows in set (0.00 sec)
语法:select * from 表名 where 条件; 条件查询
实例:
mysql> select * from class1 where id>1;
+----+--------+------------------+
| id | name | school |
+----+--------+------------------+
| 2 | yueyue | jilin_university |
+----+--------+------------------+
1 row in set (0.06 sec)
语法:select * from 表名 group by 字段 desc; 分组查询
实例:
mysql> select * from class1 group by school desc;
+----+---------+------------------+
| id | name | school |
+----+---------+------------------+
| 1 | baiyang | nanhugaozhong |
| 2 | yueyue | jilin_university |
+----+---------+------------------+
2 rows in set (0.06 sec)
语法:select * from 表名 order by 字段 desc; 排序查询,升序asc,降序desc
实例:
mysql> select * from class1 order by id desc;
+----+---------+------------------+
| id | name | school |
+----+---------+------------------+
| 2 | yueyue | jilin_university |
| 1 | baiyang | nanhugaozhong |
+----+---------+------------------+
2 rows in set (0.00 sec)