MySQL详解(4)----------基本操作
来源:互联网 发布:怎样注册2个淘宝账号 编辑:程序博客网 时间:2024/06/03 22:40
一.首先配置jdk环境变量:
1.配置jdk的变量名,jdk的目录
2.配置jdk的path(路径\bin;)和mysql的path路径,指定bin目录(cmd下任意目录下可以访问mysql)
3.指定ClassPath
或者看以下配置:
1.环境变量的配置
我的电脑---属性-----高级------环境变量。
方法1 :固定配置:
A.
新建java_home: 找jdk目录。 例如新建 D:\Program Files\Java\jdk( 配置java_home以后就不怕换别的盘符了。直接修改java_home,不用修改path了)
B.
编辑path: 找bin路径。 在路径后面添加bin的路径 %java_home%\bin或者在没有配置java_home的情况下直接例如D:\Program Files\Java\jdk\bin(查找顺序,先在本目录查找,然后再在path路径查找。)
C.
CLASSpath 新建 确定找生成的.class目录。进入.java的目录后,如果在classpath中设置了.classpath的路径,就可以在dos环境下任意的目录下找到.class文件
二:
- C:\Windows\System32>mysql -hlocalhost -u用户名 -p密码 //输入 用户名和密码,登录数据库
- mysql> show databases;//查看数据库
- mysql> use 数据库名;//进入数据库
- mysql> show tables;//查看表
- mysql> desc 表;//查看表结构
三.
创建数据库:
create database 数据库名;
mysql> create database sql_db;mysql> use sql_db;
删除数据库:
drop database 数据库名;
在数据中(标准语句)
创建表:
create table 货物信息 (名称 类型 null|not null 主键或者唯一键,……);//包含了创建表时就建立约束语法。
mysql> create table student (id int not null auto_increment primary key,name varchar(10),cid int);mysql> create table class (id int not null auto_increment primary key,name varchar(10));
删除表:
drop table 表名;
修改表 :
alter table 表名
增加列: add column 列名 数据类型(长度)null or not null 主键or唯一键;ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
mysql> alter table student add column age int ;
删除列: drop column 列名;DROP [COLUMN] col_name
mysql> alter table student drop column age;
更改列: change column 原列名 新列名 数据类型(长度); CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
modify column 列名 数据类型(长度);MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
column_definition:
col_name data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition]
mysql> desc class;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | 0 | || name | varchar(10) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)如果想去除自动增长,则 先删除主键,再修改。mysql> alter table class drop primary key;mysql> desc class;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | | 0 | || name | varchar(10) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> alter table class modify column id int null;
如果没有在原始没有给列添加为主键,可以 添加和删除约束的方法。
alter table 表名 add primary key(id);或者更改列 mysql> alter table 表 change id id int primary key;
例子:
mysql> alter table 表名 add primary key(id);
删除主键:
alter table 表名 drop primary key();
例子:
mysql> alter table emp drop primary key;
四.
表数据中的增删改查
1.向表中添加数据:(全部插入)insert into 表名 valus('','',……,'');
(部分插入)insert into 表名(列x,列y,……,列z) valus('','',……,'');
student中 (自动增长,赋予0和null 并不影响自动增长性。):
mysql> insert into student values(0,'s1',1);mysql> insert into student values(0,'s2',2);mysql> insert into student values(0,'s3',null);mysql> insert into student values(0,'s3',3);//将在2中被删除mysql> insert into student values(0,'s4',3);mysql> insert into student(id,name) values(0,'s5');
class中:
mysql> insert into class(id,name) values(1,'c1');mysql> insert into class(id,name) values(null,'c2'); //指定id为4mysql> insert into class(id,name) values(2,'c3');mysql> select * from class;+----+------+| id | name |+----+------+| 1 | c1 || 2 | c2 || 4 | c3 |+----+------+
2.向表中删除行数据:delect from 表名;(清空表)
delect from 表名 where [检索条件表达式]
mysql> delete from student where id=4;
3.更改表中的数据:update 表名 set 列名='表达式' [from 另一表名] where [检索条件表达式];
mysql> update student set name='s11' where id=1;
4.查询表中的数据:select */表字段1,表字段2,……表字段n from 表名 【where 查询条件】;
mysql> select * from student;mysql> select * from student where id=1;mysql> select id,name from student;mysql> select id,name from student where id in(1,3,4);//in表示集合。
特殊查询:
//增加一列mysql> alter table student add column createDate date;
//查询是否增加mysql> select * from student;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 1 | s11 | 1 | NULL || 2 | s2 | 2 | NULL || 3 | s3 | NULL | NULL || 5 | s4 | 3 | NULL || 6 | s5 | NULL | NULL |+----+------+------+------------+5 rows in set (0.00 sec)//插入date类型格式的日期。mysql> update student set createDate='2012-11-16' where id=3;//date时间类型 yyyy-mm-dd 格式 。createDate='2012-9-8',必须这么写。Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from student;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 1 | s11 | 1 | NULL || 2 | s2 | 2 | NULL || 3 | s3 | NULL | 2012-11-16 || 5 | s4 | 3 | NULL || 6 | s5 | NULL | NULL |+----+------+------+------------+5 rows in set (0.00 sec)//插入当前日期mysql> update student set createDate=now() where id=5;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from student;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 1 | s11 | 1 | NULL || 2 | s2 | 2 | NULL || 3 | s3 | NULL | 2012-11-16 || 5 | s4 | 3 | 2012-11-17 || 6 | s5 | NULL | NULL |+----+------+------+------------+5 rows in set (0.00 sec)统计查询(sum.avg,count)
mysql> select count(*) from student where id>3;+----------+| count(*) |+----------+| 2 |+----------+1 row in set (0.03 sec)mysql> select count(name) from student where id>3;+-------------+| count(name) |+-------------+| 2 |+-------------+1 row in set (0.00 sec)mysql> select sum(id) from student;+---------+| sum(id) |+---------+| 17 |+---------+1 row in set (0.03 sec)
查询最大的idmysql> select max(id) from student;+---------+| max(id) |+---------+| 6 |+---------+1 row in set (0.00 sec)查询最小的idmysql> select min(id) from student;+---------+| min(id) |+---------+| 1 |+---------+1 row in set (0.07 sec)
分页查询(mysql专用):
page表示要查询的页码(1开始),pagesize表示一页显示多少条记录begin=(page-1)*pagesize;表示查询页开始的行号。 注意:行记录是从0开始
mysql> select * from 表 limit begin,pagesize;mysql> select * from student limit 0,3;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 1 | s11 | 1 | NULL || 2 | s2 | 2 | NULL || 3 | s3 | NULL | 2012-11-16 |+----+------+------+------------+3 rows in set (0.03 sec)mysql> select * from student limit 3,3;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 5 | s4 | 3 | 2012-11-17 || 6 | s5 | NULL | NULL |+----+------+------+------------+
查询 id<3的,再以分页显示mysql> select * from student where id<2 limit 0,3;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 1 | s11 | 1 | NULL |+----+------+------+------------+1 row in set (0.00 sec)//降序mysql> select * from student order by id desc;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 6 | s5 | NULL | NULL || 5 | s4 | 3 | 2012-11-17 || 3 | s3 | NULL | 2012-11-16 || 2 | s2 | 2 | NULL || 1 | s11 | 1 | NULL |+----+------+------+------------+5 rows in set (0.00 sec)升序(默认)mysql> select * from student order by id asc;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 1 | s11 | 1 | NULL || 2 | s2 | 2 | NULL || 3 | s3 | NULL | 2012-11-16 || 5 | s4 | 3 | 2012-11-17 || 6 | s5 | NULL | NULL |+----+------+------+------------+5 rows in set (0.00 sec)查询id>2的,降序排列,分页查询mysql> select * from student where id >2 order by id desc limit 0,2;+----+------+------+------------+| id | name | cid | createDate |+----+------+------+------------+| 6 | s5 | NULL | NULL || 5 | s4 | 3 | 2012-11-17 |+----+------+------+------------+2 rows in set (0.00 sec)
链接查询(针对2个表):
3.关联查询:
mysql> select * from 表1,表2 where 表1.列1=表2.列2
mysql> select * from student s,class c where s.cid=c.id;+----+------+------+------------+------+------+| id | name | cid | createDate | id | name |+----+------+------+------------+------+------+| 1 | s11 | 1 | NULL | 1 | c1 || 2 | s2 | 2 | NULL | 2 | c2 |+----+------+------+------------+------+------+
外键约束:
步骤:
1.建立2个表并在建立时建立主键约束(student 的cid字段作为外键关联另一个表class的id,id必须为主键)。
2.建立外键约束。
alter table 表1 add constraint fk_表1_表2 foreign key(列1)references 表2(列2);
mysql> alter table student add constraint fk_student_depart foreign key(cid)references class(id);
外键名为fk_emp_depart,student表的cid列为外键,关联到了class表的id列。删除class表要先解除关系。删除stdent不需要去除外键。
4.连接查询:
select * from 表1
【内连接inner】
左外/右外【外连接outer】
join 表2 on 表1.列1=表2.列2;(查询出,表1中的列1关联表2中的列2的内容)
mysql> select * from student s inner join class c on s.cid=c.id; ; 内连接(显示2者关联字段的字段,和关联查询相同,inner可省略)
mysql> select * from student s inner join class c on s.cid=c.id;+----+------+------+------------+------+------+| id | name | cid | createDate | id | name |+----+------+------+------------+------+------+| 1 | s11 | 1 | NULL | 1 | c1 || 2 | s2 | 2 | NULL | 2 | c2 |+----+------+------+------------+------+------+2 rows in set (0.00 sec)
mysql> select * from employe e left (outer) join depart d on e.did=d.id; 左链接(以左边的标准显示)
mysql> select * from student s left join class c on s.cid=c.id;+----+------+------+------------+------+------+| id | name | cid | createDate | id | name |+----+------+------+------------+------+------+| 1 | s11 | 1 | NULL | 1 | c1 || 2 | s2 | 2 | NULL | 2 | c2 || 3 | s3 | NULL | 2012-11-16 | NULL | NULL || 5 | s4 | 3 | 2012-11-17 | NULL | NULL || 6 | s5 | NULL | NULL | NULL | NULL |+----+------+------+------------+------+------+
mysql> select * from employe e right (outer)join depart d on e.did=d.id;右链接(以右边的标准显示)
mysql> select * from student s right join class c on s.cid=c.id;+------+------+------+------------+------+------+| id | name | cid | createDate | id | name |+------+------+------+------------+------+------+| 1 | s11 | 1 | NULL | 1 | c1 || 2 | s2 | 2 | NULL | 2 | c2 || NULL | NULL | NULL | NULL | 4 | c3 |+------+------+------+------------+------+------+3 rows in set (0.00 sec)
数据库的备份和恢复:
备份:
mysqldump -uroot -p密码 数据库名称 > 路径
Dos下输入cmd
C:\Documents and Settings\Administrator>
mysqldump -uroot -p123456 jxc > d://jxc.sql
恢复:
如果没有在环境变量中指定路径就要进入mysql的bin文件内
mysql -u root -p密码 空的数据库 <d://jxc.sql
mysql -uroot -p123456 jxc <d://jxc.sql
本文转自http://www.cnblogs.com/RanNing/archive/2012/11/17/2870484.html
- MySQL详解(4)----------基本操作
- 数据库基本概念&MySQL基本操作详解
- MySQL的基本操作
- Mysql 基本操作
- mysql基本操作
- Mysql的基本操作
- Mysql 基本命令操作
- mysql基本操作
- MySQL的基本操作
- MySQL常用操作基本
- MySQL基本操作
- MySQL基本操作
- MySql数据库基本操作
- MySQL 安装 (基本操作)
- MySQL基本操作(控制台)
- mysql 基本操作函数
- MySQL的基本操作
- MySql 基本操作
- redis集群配置
- 利用新版ShareSDK进行手动分享(自定义分享界面)
- Oracle中的去重
- Java使用iText生成word文 表格、图片、表格里插图片、页眉、页脚、图片页脚、这一次更全面
- error:jump to case label [-fpermissive]
- MySQL详解(4)----------基本操作
- Apache&PHP+Xdebug 安装配置
- Oracle中的sqlldr
- tmux的使用方法和个性化配置
- Oracle中分区汇总
- xStream完美转换XML、JSON
- uva--165(邮资问题,dp)
- Behavioral模式之Visitor模式
- 每天游戏设计原理笔记(一)