当前位置:Gxlcms > mysql > mysql---基本操作_MySQL

mysql---基本操作_MySQL

时间:2021-07-01 10:21:17 帮助过:23人阅读

早打算写些关于数据库操作方面的知识了,现在终于完成了第一篇,以下记录了关于mysql操作方面的基础知识

在window下,启动、停止mysql服务

启动mysql数据库
net start mysql

停止mysql数据库
net stop mysql

重新启动mysql数据库
net restart mysql


命令行形式,mysql基本命令的使用

1、命令的取消
\c

2、退出mysql窗口
exit;或quit;或ctrl+c

3、查看数据库版本号
select version();

4、显示当前存在的数据库
show databases;

5、选择test数据库
use test;

6、查看选择的数据库
select database();

7、设置数据库中文编码
set names utf8;

8、创建一个test数据库
create database test

9、创建一张mtest表
create table mtest(
id tinyint(2) unsigned not null auto_increment,
name varchar(20),
sex char(1),
email varchar(50),
birth datetime,
primary key (id)
);

10、向mtest表中插入一条数据
insert into mtest(`name`,`sex`,`email`,`birth`) values ('zhangsan','1','zhangsan@163.com',now());

11、查询mtest表中name为张三的信息
select * from mtest where name='zhangsan';

12、按照id号降序输出
select * from mtest order by id desc;

13、显示第11至20条数据信息
select * from mtest id limit 10,10;

一.首先配置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文件

二:

  1. C:\Windows\System32>mysql -hlocalhost -u用户名 -p密码 //输入 用户名和密码,登录数据库
  2. mysql> show databases;//查看数据库
  3. mysql> use 数据库名;//进入数据库
  4. mysql> show tables;//查看表
  5. 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为4
mysql> 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: 0
mysql
> 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: 0 mysql> 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)

查询最大的id
mysql> select max(id) from student;
+---------+
| max(id) |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

查询最小的id
mysql> 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 

                  

	 	
                    
                    
                    
                    
                    
                

人气教程排行