程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫

mysql操作

編輯:MySQL綜合教程

mysql操作


連接服務器:
shell>mysql -u 用戶名 -p
Enter password:
或者
shell>mysql -u 用戶名 -p 密碼

斷開連接
mysql> QUIT

多行語句以分號“;”表結束,一條長的語句可以寫在不同的行,只要在最後加分號即可。若不想執行輸入的 語句則可以使用‘\c’
不區分大小寫查看當前已存在的數據庫
mysql> show databases ;

創建新數據庫
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

注:abc為自己創建的數據庫名創建表:
格式:
mysql> 
create table 表名(
       字段1 數據類型 ,
       字段2 數據類型 ,
       ......
       字段n 數據類型 ,
);


注:創建表之前,需要先選擇數據庫,如
mysql> use test1;
結果:Database changed      否則顯示:1046 - No database selected

下面的范例都是使用如下表:
表1:
學生表student
Sno學號,sname姓名,difdate出生日期, grade年級
表2:
課程表 course
cno課程號,名字cname
表3:
成績單score
sno學號 cno課程號 score成績
mysql> 
create table student(
       sno varchar(20) not null default '',
       sname varchar(30),
       difdate date,
       grade varchar(10)    
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table course(
       cno varchar(20) not null default '',
       cname varchar(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table score(
       sno varchar(20) not null default '',
       cno varchar(20) ,
       score double
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
結果:
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected



為了避免中文亂碼需以下兩步:a.創建數據庫時設置編碼格式utf-8
mysql> CREATE DATABASE  test1
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
b.然後創建表時設置編碼格式
create table student(
       sno varchar(20) not null default,
       sname varchar(30),
       difdate date,
       grade varchar(10)    
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
向表中添加一個或多個記錄
注:mysql中的插入日期格式為‘YYYY-MM-DD’,而oracle中 格式為‘DD-M月-YYYY’;字符、漢字、日期需要用單引號;
格式:
1.插入部分字段
mysql>insert into 表名 (字段1,字段2……字段n)
values(字段1值,字段2值,……)
2.插入全部的字段,可以省略字段名,如:
mysql>insert into 表名 
values(字段1值,字段2值,……)

范例:插入值
*表1
mysql> insert into student values('1','李麗','1993-02-03','2007');
insert into student values('2','王明','1993-04-02','2008');
insert into student values('3','李雲','1994-03-12','2009');
insert into student values('4','李四','1992-02-13','2007');
insert into student values('5','王五','1992-04-13','2010');
insert into student values('6','趙六','1992-03-28','2009');
insert into student values('7','張三','1992-08-03','2008');

*表2
mysql>insert into course(cno,cname) values('1','語文');
insert into course(cno,cname) values('2','大學英語');
insert into course(cno,cname) values('3','數學');
insert into course(cno,cname) values('4','物理');
insert into course(cno,cname) values('5','生物');

*表3
mysql>insert into score (sno,cno,score) values('1','1',69);
insert into score (sno,cno,score) values('1','2',40);
insert into score (sno,cno,score) values('1','3',67);
insert into score (sno,cno,score) values('1','4',79);
insert into score (sno,cno,score) values('1','5',89);
insert into score (sno,cno,score) values('2','1',97);
insert into score (sno,cno,score) values('2','2',69);
insert into score (sno,cno,score) values('2','3',39);
insert into score (sno,cno,score) values('2','4',90);
insert into score (sno,cno,score) values('2','5',99);
insert into score (sno,cno,score) values('3','1',99);
insert into score (sno,cno,score) values('3','2',69);
insert into score (sno,cno,score) values('3','3',39);
insert into score (sno,cno,score) values('3','4',69);
insert into score (sno,cno,score) values('3','5',99);
insert into score (sno,cno,score) values('4','1',99);
insert into score (sno,cno,score) values('4','2',69);
insert into score (sno,cno,score) values('4','3',39);
insert into score (sno,cno,score) values('4','4',69);
insert into score (sno,cno,score) values('4','5',99);
insert into score (sno,cno,score) values('5','1',79);
insert into score (sno,cno,score) values('5','2',70);
insert into score (sno,cno,score) values('5','3',39);
insert into score (sno,cno,score) values('5','4',69);
insert into score (sno,cno,score) values('5','5',99);
insert into score (sno,cno,score) values('6','1',99);
insert into score (sno,cno,score) values('6','2',69);
insert into score (sno,cno,score) values('6','3',39);
insert into score (sno,cno,score) values('6','4',69);
insert into score (sno,cno,score) values('6','5',80);
insert into score (sno,cno,score) values('7','1',39);
insert into score (sno,cno,score) values('7','2',69);
insert into score (sno,cno,score) values('7','3',89);
insert into score (sno,cno,score) values('7','4',64);
insert into score (sno,cno,score) values('7','5',59);
單表查詢
范例:查詢每個表的內容
*表1
mysql> select * from student ;
結果:
+-----+-------+------------+-------+
| sno | sname | difdate    | grade |
+-----+-------+------------+-------+
| 1   | 李麗  | 1993-02-03 | 2007  |
| 2   | 王明  | 1993-04-02 | 2008  |
| 3   | 李雲  | 1994-03-12 | 2009  |
| 4   | 李四  | 1992-02-13 | 2007  |
| 5   | 王五  | 1992-04-13 | 2010  |
| 6   | 趙六  | 1992-03-28 | 2009  |
| 7   | 張三  | 1992-08-03 | 2008  |
+-----+-------+------------+-------+
7 rows in set
*表2
mysql> select * from course;
結果:
+-----+----------+
| cno | cname    |
+-----+----------+
| 1   | 語文     |
| 2   | 大學英語 |
| 3   | 數學     |
| 4   | 物理     |
| 5   | 生物     |
+-----+----------+
5 rows in set

*表3
mysql> select * from score ;
+-----+-----+-------+
| sno | cno | score |
+-----+-----+-------+
| 1   | 1   |    69 |
| 1   | 2   |    40 |
| 1   | 3   |    67 |
| 1   | 4   |    79 |
| 1   | 5   |    89 |
| 2   | 1   |    97 |
| 2   | 2   |    69 |
| 2   | 3   |    39 |
| 2   | 4   |    90 |
| 2   | 5   |    99 |
| 3   | 1   |    99 |
| 3   | 2   |    69 |
| 3   | 3   |    39 |
| 3   | 4   |    69 |
| 3   | 5   |    99 |
| 4   | 1   |    99 |
| 4   | 2   |    69 |
| 4   | 3   |    39 |
| 4   | 4   |    69 |
| 4   | 5   |    99 |
| 5   | 1   |    79 |
| 5   | 2   |    70 |
| 5   | 3   |    39 |
| 5   | 4   |    69 |
| 5   | 5   |    99 |
| 6   | 1   |    99 |
| 6   | 2   |    69 |
| 6   | 3   |    39 |
| 6   | 4   |    69 |
| 6   | 5   |    80 |
| 7   | 1   |    39 |
| 7   | 2   |    69 |
| 7   | 3   |    89 |
| 7   | 4   |    64 |
| 7   | 5   |    59 |
+-----+-----+-------+
35 rows in set
格式:
1:個別字段查詢
select 字段1,字段2 ...... from 表名 

2:顯示表中的全部內容
select * from 表名

3:限定條件查詢
select 字段1,字段2 ......
from 表名   
where 條件
范例:查詢2007級姓李的學生信息
mysql> select  * from student where sname like '李%' and grade=2007;
結果:
+-----+-------+------------+-------+
| sno | sname | difdate    | grade |
+-----+-------+------------+-------+
| 1   | 李麗  | 0000-00-00 | 2007  |
| 4   | 李四  | 0000-00-00 | 2007  |
+-----+-------+------------+-------+
2 rows in set

4:分組查詢
select 字段1,字段2 ...... 
from 表名 
[where 條件] 
group by 字段 
此語句表示按照這個指定的字段進行分組,一般和sql的合計函數一起使用,如sum(),count(),avg(),min(),max(),Var 和 VarP 函數等
范例:統計每個年級的人數 mysql> select  grade,count(sno) from student group by grade; 結果: +-------+------------+ | grade | count(sno) | +-------+------------+ | 2007  |          2 | | 2008  |          2 | | 2009  |          2 | | 2010  |          1 | +-------+------------+ 4 rows in set 
5:限定分組後的字段顯示條件查詢
select字段1,字段2 ......
from 表名
[where 條件]
group by 分組字段
[having 條件]
注意:HAVING 是可選的。HAVING 與 WHERE 類似,可用來決定選擇哪個記錄。在使用 GROUP BY對這些記錄分組後,HAVING 會決定應顯示的記錄

6:按指定順序查詢asc,desc
select字段1,字段2 ...... 
from 表名
[where 條件]
[group by 分組字段
having  條件]
order by 字段a  [asc|desc],字段b [asc|desc],......
表示按照字段a升序排序或降序排序;若字段a相同,則按照字段b升序排序或降序排序;若字段b相同,則按照字段 ....升序排序或降序排序;......


多表查詢
1:內鏈接 inner join
范例:查詢2007級的各學生的平均成績,沒有成績的為0;
       a)先作笛卡爾積
mysql> select * from student s,score sc where s.sno=sc.sno;
+-----+-------+------------+-------+-----+-----+-------+
| sno | sname | difdate    | grade | sno | cno | score |
+-----+-------+------------+-------+-----+-----+-------+
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 1   |    69 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 2   |    40 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 3   |    67 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 4   |    79 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 5   |    89 |
| 2   | 王明  | 1993-04-02 | 2008  | 2   | 1   |    97 |
| 2   | 王明  | 1993-04-02 | 2008  | 2   | 2   |    69 |
| 2   | 王明  | 1993-04-02 | 2008  | 2   | 3   |    39 |
| 2   | 王明  | 1993-04-02 | 2008  | 2   | 4   |    90 |
| 2   | 王明  | 1993-04-02 | 2008  | 2   | 5   |    99 |
| 3   | 李雲  | 1994-03-12 | 2009  | 3   | 1   |    99 |
| 3   | 李雲  | 1994-03-12 | 2009  | 3   | 2   |    69 |
| 3   | 李雲  | 1994-03-12 | 2009  | 3   | 3   |    39 |
| 3   | 李雲  | 1994-03-12 | 2009  | 3   | 4   |    69 |
| 3   | 李雲  | 1994-03-12 | 2009  | 3   | 5   |    99 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 1   |    99 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 2   |    69 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 3   |    39 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 4   |    69 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 5   |    99 |
| 5   | 王五  | 1992-04-13 | 2010  | 5   | 1   |    79 |
| 5   | 王五  | 1992-04-13 | 2010  | 5   | 2   |    70 |
| 5   | 王五  | 1992-04-13 | 2010  | 5   | 3   |    39 |
| 5   | 王五  | 1992-04-13 | 2010  | 5   | 4   |    69 |
| 5   | 王五  | 1992-04-13 | 2010  | 5   | 5   |    99 |
| 6   | 趙六  | 1992-03-28 | 2009  | 6   | 1   |    99 |
| 6   | 趙六  | 1992-03-28 | 2009  | 6   | 2   |    69 |
| 6   | 趙六  | 1992-03-28 | 2009  | 6   | 3   |    39 |
| 6   | 趙六  | 1992-03-28 | 2009  | 6   | 4   |    69 |
| 6   | 趙六  | 1992-03-28 | 2009  | 6   | 5   |    80 |
| 7   | 張三  | 1992-08-03 | 2008  | 7   | 1   |    39 |
| 7   | 張三  | 1992-08-03 | 2008  | 7   | 2   |    69 |
| 7   | 張三  | 1992-08-03 | 2008  | 7   | 3   |    89 |
| 7   | 張三  | 1992-08-03 | 2008  | 7   | 4   |    64 |
| 7   | 張三  | 1992-08-03 | 2008  | 7   | 5   |    59 |
+-----+-------+------------+-------+-----+-----+-------+
35 rows in set
       b)再添加條件
mysql>select * from student s,score sc where s.sno=sc.sno and s.grade='2007' ;
結果:
+-----+-------+------------+-------+-----+-----+-------+
| sno | sname | difdate    | grade | sno | cno | score |
+-----+-------+------------+-------+-----+-----+-------+
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 1   |    69 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 2   |    40 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 3   |    67 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 4   |    79 |
| 1   | 李麗  | 1993-02-03 | 2007  | 1   | 5   |    89 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 1   |    99 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 2   |    69 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 3   |    39 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 4   |    69 |
| 4   | 李四  | 1992-02-13 | 2007  | 4   | 5   |    99 |
+-----+-------+------------+-------+-----+-----+-------+
10 rows in set
      c)有重復,所以要進行分組
mysql> select s.sno ,s.sname , avg(sc.score) 
from student s,score sc 
where s.sno=sc.sno and s.grade='2007' 
group by s.sno,s.sname;
結果:
+-----+-------+---------------+
| sno | sname | avg(sc.score) |
+-----+-------+---------------+
| 1   | 李麗  |          68.8 |
| 4   | 李四  |            75 |
+-----+-------+---------------+
2 rows in set


2:外連接 outer join
      a)左連接 left join
      b)右連接 right join
      c)

3:交叉連接
mysql日期和字符相互轉換方法 date_format(date,'%Y-%m-%d') 對應於oracle中的to_char(,'yyyy-mm--dd'); str_to_date(date,'%Y-%m-%d') 相當於oracle中的to_date(,);
單表更新
范例:將數學替換成高等數學
mysql> update course set cname='高等數學' where cname='數學';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
查看結果:


mysql> select * from course;
+-----+----------+
| cno | cname    |
+-----+----------+
| 1   | 語文     |
| 2   | 大學英語 |
| 3   | 高等數學 |
| 4   | 物理     |
| 5   | 生物     |
+-----+----------+
5 rows in set


update更新 多表
1.使用inner join .....on更新
范例:給2007級學生的數學成績加5分
mysql> update score sc inner join course c inner join student s on s.sno=sc.sno and c.cno=sc.cno set score=5+sc.score 
where  s.grade='2007'and c.cname='數學';
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
deleted多表刪除
1.使用inner join .....on刪除
范例:刪除姓名是張三的大學語文成績
mysql> delete sc.* from score sc inner join student s inner join course c on s.sno=sc.sno and sc.cno=c.cno where s.sname='張三' and c.cname='語文';
Query OK, 1 row affected

修改表結構alter
1.增加主鍵
格式:alter table 表名 add constraint 主鍵名 primary key(字段);
范例:設置student表的主鍵sno
mysql> alter table student  add constraint pk_sno primary key(sno);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
2.增加外鍵
格式:alter table 表名 add constraint 外鍵名 foreign key(字段) references 關聯表的名稱(關聯表的主鍵)
范例:設置score表的外鍵
mysql> alter table score add constraint fk_sno foreign key(sno) references student(sno);

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved