對(duì)數(shù)據(jù)倉(cāng)庫(kù)的操作(CURD)：

 新增: 

create database db_test;

新增的時(shí)候設(shè)置編碼:

create database da_test_1 character set utf-8;

刪除：

drop database da_test_1;

修改：

修改數(shù)據(jù)倉(cāng)庫(kù)的字符編碼集:

 alter database db_test character set 'gbk';

查看：

獲取所有的數(shù)據(jù)倉(cāng)庫(kù)：

 show databases;

查看數(shù)據(jù)倉(cāng)庫(kù)的編碼：

show create database db_test;

選擇數(shù)據(jù)倉(cāng)庫(kù)：

use db_test;

查看當(dāng)前選擇的是哪個(gè)數(shù)據(jù)倉(cāng)庫(kù)：

select database();

對(duì)數(shù)據(jù)表的操作(CURD):

新增：

create table emp(

id int primary key auto_increment,

name varchar(50) not null,

workid varchar(32) unique not null,

salary double

);

查看：

查看表結(jié)構(gòu)：

desc emp;

查看該數(shù)據(jù)倉(cāng)庫(kù)中的所有表：

show tables;

查看表的字符編碼:

 show create table person;

修改:

增加一列字段：

alter table emp add telephone varchar(20);

修改現(xiàn)有列的 數(shù)據(jù)類型和約束：

alter table emp modify telephone varchar(30) not null;

修改現(xiàn)有列的名稱：

 alter table emp change telephone phone varchar(11);

刪除存在的列:

 alter table emp drop phone;

修改表的名字：

rename table emp to person; 

修改表的字符編碼:

alter table person character set utf8;

刪除：

drop table person;

對(duì)表中記錄的操作(CUD):

新增：

insert into (id,name,workid) values (null,'xiaoshitou','P001');

插入數(shù)據(jù)時(shí)的注意事項(xiàng)：

1、值與列一一對(duì)應(yīng)。并且有多少個(gè)列，需要寫多個(gè)值。如果某一個(gè)列沒有值?？梢允褂?/span>null。表示插入空。

2、值的數(shù)據(jù)類型，與列被定義的數(shù)據(jù)類型要相匹配。并且值得長(zhǎng)度。不能夠超多定義的列的長(zhǎng)度。

3、字符串類型：插入字符類型的數(shù)據(jù)，必須英文的單引號(hào)括起來。在mysql中。

4、date 時(shí)間類型：也必須用英文的單引號(hào)括起來，如 ‘yyyyMMdd’ ,’yyyy-MM-dd’,’yyyy/MM/dd’ 。

5、在插入數(shù)據(jù)的時(shí)候，如果某些列可以為null，或者是自動(dòng)增長(zhǎng)的列，或者有默認(rèn)值的，在插入的時(shí)候可以省略。

6、如果給表中的所有列插入數(shù)據(jù)，這時(shí)可以省略表名后面的列名，直接寫values

修改：

update emp set salary = 15000 where id = 1;

刪除：

 delete from emp where id=1;

刪除表中所有的記錄：

truncate table emp;

delete from emp; 和truncate table emp;的區(qū)別？

前者是一條一條記錄刪除，

后者是先刪除表，然后重新建一張表

對(duì)表中記錄的查詢操作(R):

準(zhǔn)備工作（創(chuàng)建表和添加數(shù)據(jù)）：

create database day02;use day02;create table student(
    id int primary key auto_increment,
    name varchar(32) not null,
    age int ,
    gender varchar(10) not null,
    score double not null,
    birthday date
);insert into student (id,name,age,gender,score,birthday) values(null,'zhangsan',23,'male',98.99,'1990-09-09');insert into student (id,name,age,gender,score,birthday) values(null,'lisi',23,'男',56.99,'1990-02-09');insert into student (id,name,age,gender,score,birthday) values(null,'王五',24,'女',75.99,'1988-01-01');insert into student (id,name,age,gender,score,birthday) values(null,'趙六',25,'男',80.99,'1980-11-12');insert into student (id,name,age,gender,score,birthday) values(null,'王思聰',null,'女',84,null);

數(shù)據(jù)表中記錄查詢：

查詢表中所有的記錄：

 select * from student;

查詢表中指定列的數(shù)據(jù):

 select name,age,score from student;

按條件來查詢：

查詢分?jǐn)?shù)大于80的所有學(xué)生信息: select * from student where score >80;

過濾重復(fù)數(shù)據(jù)：

獲取學(xué)生表中所有的年齡，不能重復(fù)

select distinct age from student;

對(duì)查詢的結(jié)果進(jìn)行排序：

用學(xué)生的age進(jìn)行降序排序

select * from student order by age desc;

先按照age進(jìn)行降序排列，年齡相同時(shí)，利用分?jǐn)?shù)升序排列：

select * from student order by age desc,score asc;

給列起別名：

可以用as，也可以不用

 select id as 'GongHao',name 'XingMing' from student;

聚合函數(shù)：

count函數(shù):

統(tǒng)計(jì)個(gè)數(shù)，統(tǒng)計(jì)一共有多少學(xué)生:

count的注意點(diǎn)：count在根據(jù)指定的列統(tǒng)計(jì)的時(shí)候，如果這一列中有null 不會(huì)被統(tǒng)計(jì)在其中。

sum函數(shù)：

求和方法：統(tǒng)計(jì)該班級(jí)所有學(xué)生的分?jǐn)?shù)：

select sum(score) from student;

sum的注意點(diǎn)：

1、如果使用sum 多列進(jìn)行求和的時(shí)候，如果某一列中的有null，這一列所在的行中的其他數(shù)據(jù)不會(huì)被加到總和。

2、可以使用mysql 數(shù)據(jù)庫(kù)提供的函數(shù) ifnull(列名,值)

3、在數(shù)據(jù)庫(kù)中定義double類型數(shù)據(jù)，是一個(gè)近似值，需要確定準(zhǔn)確的位數(shù)，這時(shí)可以把這一列設(shè)計(jì)成numeric類型。numeric(數(shù)據(jù)的總列數(shù),小數(shù)位數(shù))

avg 函數(shù)：

求平均值，求班級(jí)的平均年齡

select avg(age) from student;

max, min函數(shù)：

求最大值和最小值：求班級(jí)的最高分，最低分

select max(score) as '最高分',min(score) '最低分' from student;

group by 分組函數(shù)：

準(zhǔn)備工作：

create table student(
    name varchar(30),
    sex char(10),
    course varchar(30),
    score int);insert into student values('張三', '男', '語文', 93);insert into student values('張三', '男','數(shù)學(xué)', 96);insert into student values('張三', '男', '英語', 99);insert into student values('李四', '女', '語文', 90);insert into student values('李四', '女','數(shù)學(xué)', 85);insert into student values('王五', '男', '語文', 80);insert into student values('王五', '男','數(shù)學(xué)', 75);insert into student values('王五', '男', '英語', 70);

表中的數(shù)據(jù)為：

求：每個(gè)學(xué)生的總成績(jī)？

分析：要求每個(gè)人的總成績(jī)，首先需要按學(xué)生姓名來分組，然后對(duì)分組的成績(jī)求和，就可以完成。

求平均分大于80的學(xué)生？

select name,avg(score) from student group by name having avg(score) > 80;

select語句執(zhí)行的順序：

1、from ：確定數(shù)據(jù)源

2、Where ： 指定條件對(duì)記錄進(jìn)行篩選

3、Group by : 對(duì)記錄進(jìn)行分組

4、聚合函數(shù)：如sum，count，avg等...

5、Having ：對(duì)分組的數(shù)據(jù)，進(jìn)行篩選

6、計(jì)算所有的表達(dá)式

7、使用order by 對(duì)結(jié)果進(jìn)行排序

8、Select對(duì)數(shù)據(jù)的顯示

數(shù)據(jù)庫(kù)的備份和恢復(fù)：

備份：

mysqldump -u root -p mytestdb > e:t/est.sql

恢復(fù)：

注意：恢復(fù)的時(shí)候，首先應(yīng)該自己創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)

create database dbtest;

執(zhí)行:mysql -u root -p db_test < e:/test.sql

數(shù)據(jù)庫(kù)的多表設(shè)計(jì)：

多對(duì)多的關(guān)系:

一個(gè)A可以對(duì)應(yīng)多個(gè)B，一個(gè)B可以對(duì)應(yīng)多個(gè)A；比如說兩張表：學(xué)生和課程表，一個(gè)學(xué)生可以選擇多門課程；一門課程也可以被不同的學(xué)生選擇。

設(shè)計(jì)方式：

創(chuàng)建一個(gè)中間表C，增加兩列，引入A和B表中主鍵，作為外鍵。

外鍵的增加方式：

1、已經(jīng)創(chuàng)建好的表中，添加外鍵:

alter table C add foreign key(A_id) references A(id);

alter table C add foreign key(B_id) references B(id);

2、在定義表的時(shí)候增加外鍵

create table C(

A_id int,

B_id int,

foreign key(A_id) references A(id),

foreign key(B_id) references B(id)

);

一對(duì)多的關(guān)系：

一個(gè)A對(duì)應(yīng)多個(gè)B,而多個(gè)B只能對(duì)應(yīng)一個(gè)A。

設(shè)計(jì)方式：

在多的一張表中增加一列，引入一表中主鍵作為外鍵。

一對(duì)一的關(guān)系：

一個(gè)A只能對(duì)應(yīng)一個(gè)B，一個(gè)B也只能對(duì)應(yīng)一個(gè)A。

設(shè)計(jì)方式：

在任意一張表增加一列，引入另一張表的主鍵作為自己的外鍵。

表設(shè)計(jì)案例：

需求：設(shè)計(jì)學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)表

1、每個(gè)教師可以教多門課程

2、每個(gè)課程由一個(gè)老師負(fù)責(zé)

3、每門課程可以由多個(gè)學(xué)生選修

4、每個(gè)學(xué)生可以選修多門課程

5、學(xué)生選修課程要有成績(jī)

分析：

教師和課程是一對(duì)多的關(guān)系，得在課程表中增加一列，引入教師表的主鍵，作為自己的外鍵；

課程和學(xué)生是多對(duì)多的關(guān)系，得創(chuàng)建一張中間表，增加兩列，引入課程和學(xué)生的主鍵，作為自己的外鍵；

學(xué)生選修課需要成績(jī)，所以得在學(xué)生和課程的中間表增加一列，存放成績(jī)

Sql語句：

創(chuàng)建教師表:

create table teacher(

id int primary key auto_increment,

name varchar(50)

);

創(chuàng)建課程表：

create table course(

id int primary key auto_increment,

name varchar(50),

teacher_id int,

foreign key(teacher_id) references teacher(id)

);

創(chuàng)建學(xué)生表:

create table student(

id int primary key auto_increment,

name varchar(50)

);

創(chuàng)建學(xué)生課程中間表：

create table studentcourse(

student_id int,

course_id int,

score double,

foreign key(student_id) references student(id),

foreign key(course_id) references course(id)

);

多表查詢：

準(zhǔn)備工作：

create table A(
  A_ID int primary key auto_increment,
  A_NAME varchar(20) not null);insert into A values(1,'蘋果');insert into A values(2,'橘子');insert into A values(3,'香蕉');create table B( 
   A_ID int primary key auto_increment,
   B_PRICE double);insert into B values(1,2.30);insert into B values(2,3.50);insert into B values(4,null);

內(nèi)鏈接：

查詢兩張表中公共的部分：

查詢出每種水果的價(jià)格：

方式一：

select a.A_ID,a.A_NAME,B_PRICE from a,b where a.A_ID = b.A_ID;

方式二：

select a.A_ID,a.A_NAME,B_PRICE from a inner join b on a.A_ID = b.A_ID;

左外連接：

以左邊的表為基礎(chǔ)去右邊的表查詢，如果有關(guān)聯(lián)的記錄，就顯示出來，如果沒有對(duì)應(yīng)的記錄就顯示null

select * from a left join b on a.A_ID = B.A_ID;

全連接：

就是左連接和右連接之后，去掉重復(fù)數(shù)據(jù)，就用union，不需要去重就用union all

select a.*,b.* from a left join b on a.A_ID = B.A_ID

UNION

select a.*,b.* from b left join a on a.A_ID = B.A_ID;

不去重：

select a.*,b.* from a left join b on a.A_ID = B.A_ID

UNION ALL

select a.*,b.* from b left join a on a.A_ID = B.A_ID;

事務(wù)：

事務(wù)就是：在處理一件完整的事情，要么全部成功，要么全部失??；在數(shù)據(jù)庫(kù)中，執(zhí)行一組sql語句，要么全部成功，要么全部失敗

開啟事務(wù)：start transaction;

提交事務(wù)：commit;

回滾事務(wù)：rollback;

事務(wù)的特性（ACID）:

A:原子性，一件事務(wù)不可分割，要么發(fā)生，要么全不發(fā)生

C:一致性， 事務(wù)執(zhí)行前后一致性，成功則提交，失敗回滾

I:隔離性，一個(gè)用戶的事務(wù)，不能被其他用戶的事務(wù)影響

D:持久性，事務(wù)一旦提交，對(duì)數(shù)據(jù)庫(kù)的改變就是永久性的

如果不考慮事務(wù)的隔離性，事務(wù)的并發(fā)會(huì)出現(xiàn)：

臟讀：讀取其他事務(wù)未提交的數(shù)據(jù)

不可重復(fù)讀：在一個(gè)事務(wù)內(nèi)，多次讀取表中的數(shù)據(jù)，結(jié)果不一致(與臟讀的區(qū)別是：讀取的是已經(jīng)提交的)，主要是針對(duì)記錄的值（update操作）

虛讀：在一個(gè)事務(wù)內(nèi)，多次查詢的表的數(shù)據(jù)不一致，主要是針對(duì)記錄的個(gè)數(shù)（insert，delete）

SQL查詢強(qiáng)化案例：

學(xué)生選課信息查詢案例:

準(zhǔn)備工作：

CREATE TABLE teacher (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) not null unique
 );CREATE TABLE student (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  city varchar(40) NOT NULL,
  age int ) ;CREATE TABLE course(
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  teacher_id int(11) NOT NULL,  FOREIGN KEY (teacher_id) REFERENCES teacher (id)
);CREATE TABLE studentcourse (
   student_id int NOT NULL,
   course_id int NOT NULL,
   score double NOT NULL,   FOREIGN KEY (student_id) REFERENCES student (id),   FOREIGN KEY (course_id) REFERENCES course (id)
);insert into teacher values(null,'關(guān)羽');insert into teacher values(null,'張飛');insert into teacher values(null,'趙云');insert into student values(null,'小王','北京',20);insert into student values(null,'小李','上海',18);insert into student values(null,'小周','北京',22);insert into student values(null,'小劉','北京',21);insert into student values(null,'小張','上海',22);insert into student values(null,'小趙','北京',17);insert into student values(null,'小蔣','上海',23);insert into student values(null,'小韓','北京',25);insert into student values(null,'小魏','上海',18);insert into student values(null,'小明','廣州',20);insert into course values(null,'語文',1);insert into course values(null,'數(shù)學(xué)',1);insert into course values(null,'生物',2);insert into course values(null,'化學(xué)',2);insert into course values(null,'物理',2);insert into course values(null,'英語',3);insert into studentcourse values(1,1,80);insert into studentcourse values(1,2,90);insert into studentcourse values(1,3,85);insert into studentcourse values(1,4,78);insert into studentcourse values(2,2,53);insert into studentcourse values(2,3,77);insert into studentcourse values(2,5,80);insert into studentcourse values(3,1,71);insert into studentcourse values(3,2,70);insert into studentcourse values(3,4,80);insert into studentcourse values(3,5,65);insert into studentcourse values(3,6,75);insert into studentcourse values(4,2,90);insert into studentcourse values(4,3,80);insert into studentcourse values(4,4,70);insert into studentcourse values(4,6,95);insert into studentcourse values(5,1,60);insert into studentcourse values(5,2,70);insert into studentcourse values(5,5,80);insert into studentcourse values(5,6,69);insert into studentcourse values(6,1,76);insert into studentcourse values(6,2,88);insert into studentcourse values(6,3,87);insert into studentcourse values(7,4,80);insert into studentcourse values(8,2,71);insert into studentcourse values(8,3,58);insert into studentcourse values(8,5,68);insert into studentcourse values(9,2,88);insert into studentcourse values(10,1,77);insert into studentcourse values(10,2,76);insert into studentcourse values(10,3,80);insert into studentcourse values(10,4,85);insert into studentcourse values(10,5,83);

-- 1、查詢平均成績(jī)大于70分的同學(xué)的學(xué)號(hào)和平均成績(jī)

select student_id,FORMAT(avg(score),2) as '平均成績(jī)' from studentcourse group by student_id having avg(score) > 70;

-- 2、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)

select student.id,student.name,t.countCourse,t.sumScore from student,(

select student_id,count(*) as 'countCourse',sum(score) as 'sumScore' from studentcourse group by student_id

) as t where student.id = t.student_id;

-- 3、查詢學(xué)過趙云老師所教課的同學(xué)的學(xué)號(hào)、姓名

select id,name from student where id in (

select student_id from studentcourse where course_id in(

select id from course where teacher_id = (

select id from teacher where name='趙云')));

http://www.cnblogs.com/xiaoshitoutest/p/7127377.html

延伸閱讀

• 珊珊老師——北京IT機(jī)構(gòu)十年一線教學(xué)經(jīng)驗(yàn) 2024-08-28
• 告別“老頑固” 2023-04-18
• 時(shí)光飛逝 2024-03-26
• 會(huì)當(dāng)凌絕頂，一覽眾山小 2023-04-18
• 自豪 2023-04-18
• 綻放 2023-04-18
• 畢業(yè)有感 2023-04-18
• 投入六個(gè)月的經(jīng)歷，收獲了整個(gè)人生 2023-04-18
• 非常響亮的名字 2023-04-18
• 不可磨滅的力量 2023-04-18

珊珊老師——北京IT機(jī)構(gòu)十年一線教學(xué)經(jīng)驗(yàn)