1,创建数据库,create datebase 数据库名,在这里kenan建立一个数据库名为myfirstdb
- create database myfirstdb;
- show databases;
- use myfirstdb;
- drop database myfirstdb;
数据库的增删改查
- use myfirstdb;
- create table user(
- id int auto_increment primary key,
- username varchar(50) not null,
- password varchar(50) not null,
- createtime datetime,
- )
- mysql> desc user;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | username | varchar(50) | NO | | NULL | |
- | password | varchar(50) | NO | | NULL | |
- | createtime | datetime | YES | | NULL | |
- +------------+-------------+------+-----+---------+----------------+
- 4 rows in set (0.01 sec)
- mysql> show columns from user;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | username | varchar(50) | NO | | NULL | |
- | password | varchar(50) | NO | | NULL | |
- | createtime | datetime | YES | | NULL | |
- +------------+-------------+------+-----+---------+----------------+
- 4 rows in set (0.01 sec)
- mysql> alter table user add age int;
- Query OK, 0 rows affected (0.25 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> alter table user modify username varchar(30);
- Query OK, 0 rows affected (0.19 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc user;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | username | varchar(30) | YES | | NULL | |
- | password | varchar(50) | NO | | NULL | |
- | createtime | datetime | YES | | NULL | |
- | age | int(11) | YES | | NULL | |
- +------------+-------------+------+-----+---------+----------------+
- 5 rows in set (0.01 sec)
- mysql> drop table user;
- Query OK, 0 rows affected (0.03 sec)
- mysql> insert into user values(null,'kenan','kenan',now());
- Query OK, 1 row affected (0.13 sec)
- mysql> select * from user;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- +----+----------+----------+---------------------+
- 1 row in set (0.00 sec)
- mysql> select username,password from user;
- +----------+----------+
- | username | password |
- +----------+----------+
- | kenan | kenan |
- +----------+----------+
- 1 row in set (0.00 sec)
- mysql> select * from user where id > 1;
- mysql> select * from user where username = 'kenan';
- mysql> select * from user where username = 'kenan' and id = 1;
- mysql> select * from user;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from user group by username;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- +----+----------+----------+---------------------+
- 2 rows in set (0.04 sec)
- mysql> select username,avg(id) from user group by username;
- +----------+---------+
- | username | avg(id) |
- +----------+---------+
- | kenan | 2.0000 |
- | lele | 2.0000 |
- +----------+---------+
- 2 rows in set (0.04 sec)
- mysql> select username,sum(id) from user group by username;
- +----------+---------+
- | username | sum(id) |
- +----------+---------+
- | kenan | 4 |
- | lele | 2 |
- +----------+---------+
- 2 rows in set (0.03 sec)
- mysql> select * from user;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from user order by username;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from user order by id;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from user order by id desc
- -> ;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from user order by username,id;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select distinct username from user;
- +----------+
- | username |
- +----------+
- | kenan |
- | lele |
- +----------+
- 2 rows in set (0.00 sec)
- mysql> select * from user where username like 'ke%';
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- +----+----------+----------+---------------------+
- 2 rows in set (0.00 sec)
- mysql> select * from user where username like '_e%';
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- +----+----------+----------+---------------------+
- 3 rows in set (0.01 sec)
- mysql> select * from user limit 1;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-30 21:46:31 |
- +----+----------+----------+---------------------+
- 1 row in set (0.00 sec)
- mysql> select * from user limit 1,2;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 2 | lele | lele | 2012-10-31 15:01:20 |
- | 3 | kenan | lele | 2012-10-31 15:06:46 |
- +----+----------+----------+---------------------+
- 2 rows in set (0.00 sec)
- mysql> update user set createtime = now() where username = 'kenan';
- Query OK, 2 rows affected (0.05 sec)
- Rows matched: 2 Changed: 2 Warnings: 0
- mysql> delete from user where username = 'lele';
- Query OK, 1 row affected (0.06 sec)