1,创建数据库,create datebase 数据库名,在这里kenan建立一个数据库名为myfirstdb
create database myfirstdb;
2,查看所有的数据库
show databases;
3,选择数据库,只有先选择数据库,才能在选择的数据库中进行数据库操作
use myfirstdb;
4,删除数据库
drop database myfirstdb;
数据库的增删改查
这里要先使用 use来选择要操作的数据库
1,建立数据库表
use myfirstdb; create table user( id int auto_increment primary key, username varchar(50) not null, password varchar(50) not null, createtime datetime, )
这里创建了一个名为user 的表,id为int型的,auto_increatement表明这个是自增字段,primary key表明这个是主键,username varchar(50)表明userame是可变的字符长,not null表明该字段不能够为空,createtime datetime 字段cratetime是日期时间类型的。
2, 查看表结构
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)
在这里展示了两种查看表结构的方式
3,修改表结构
添加列
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
注意关键词 modify
修改后的表结构
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)
4,删除表
mysql> drop table user; Query OK, 0 rows affected (0.03 sec)
5,插入数据
mysql> insert into user values(null,'kenan','kenan',now()); Query OK, 1 row affected (0.13 sec)
这里使用了日期函数,表示当前时间和日期,字符串要用单引号引起来
6,查询数据
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)
第一句表示查询所有的字段从user表里边,在这里*表示所有,第二句表示单独查询username和password字段
6,带有where字句的条件查询
mysql> select * from user where id > 1; mysql> select * from user where username = 'kenan'; mysql> select * from user where username = 'kenan' and id = 1;
这里分别进行了三个带有where字句的子查询,分别对id,和username进行限制,用and 连接两个限制条件
7,group by 对查询结果进行分组
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)
这里展示的group by的用法,首先第一句是按照username分组,所以 username相同的数据会认为是一组 只显示第一条数据,第二个sql语句和第三个sql语句用了两个分组函数avg(),sum()函数,这两个函数 呢,是跟group by 配合使用的,avg()是用来求这一组数据的平均值,而sum()是用来求这一组数据的 和。
8,order by 对结果进行排序
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)
在这里第一个sql语句,查出所有的user表中的数据,默认是按照你插入时的数据来排序,就是id递增 第二句是 按照username排序,这个会比较username的首字母 order by id 是按照id递增排序 order by id desc 是按照id递减排序,这里大家明白 desc的作用了吧 order by username,id是添加了两个排序条件,首先按照username排序,username相同的话,会按照id排序 在排序中如果遇到NULL值,把NULL值按照最小的值来处理
9,distinct的用法
mysql> select distinct username from user; +----------+ | username | +----------+ | kenan | | lele | +----------+ 2 rows in set (0.00 sec)
distinct的是去重,然后把不重复的查询出来
10,like模糊查询
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)
在这里用了like模糊查询 第一个sql语句,是查询出来 uername 以 ke 开头的 行 第二个sql语句,是查询出来 username 的第二个字母是e的行 在这里%表示0或多个字符,_代表一个字符
11,LIMIT 限定结果行数,用来进行分页查询
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)
limit的两种用法 第一种limit 1这个是显示查询出来的数据的第一条 当让1换成x,就是x条 第二种limit 1,2这里limit带有的两个参数 首先我们把查询出来的数据进行编号,从0号开始(当然这个编号是不存在的,就是说表内不存在这个字段),它不是id 这个编号是便于我们理解人为的编号 然后第一个参数代表的含义呢,就是要显示的数据的编号 第二个参数表示的就是从刚刚编号开始(包含选中的编号)显示多少条数据 分页用法,要显示第x页的数据,每页显示y条 首先计算第x也的数据的开始编号是(x-1)*y 所以这个sql语句应该这样写 select * from user limit (x-1)*y,y;
12,修改记录
mysql> update user set createtime = now() where username = 'kenan'; Query OK, 2 rows affected (0.05 sec) Rows matched: 2 Changed: 2 Warnings: 0
这里把user表中的所有username叫做kenan的记录更新createtime字段,更新为当前时间
13,删除记录
mysql> delete from user where username = 'lele'; Query OK, 1 row affected (0.06 sec)
这里删除user表中所有username叫做lele的记录
原文链接:http://soukenan.blog.51cto.com/5130995/1045340