資料庫基本操作

update user set password=password('123456')where user='root'; 修改密碼
flush privileges; 刷新資料庫
show databases; 顯示所有資料庫
use dbname； 打開某個資料庫
show tables; 顯示資料庫mysql中所有的表
describe user; 顯示錶mysql資料庫中user表的列信息
create database name; 創建資料庫
use databasename; 選擇資料庫
exit; 退出Mysql
? 命令關鍵詞 : 尋求幫助
-- 表示注釋

創建資料庫：

CREATE DATABASE 資料庫名    //創建資料庫

刪除資料庫 :

drop database [if exists] 資料庫名;

查看資料庫 :

show databases;

使用資料庫 :

use 資料庫名;

表的操作

偽代碼：

create table [if not exists] `表名`(
    '欄位名1' 列類型 [屬性][索引][注釋],
    '欄位名2' 列類型 [屬性][索引][注釋],
    '欄位名n' 列類型 [屬性][索引][注釋]
)[表類型][表字符集][注釋]

實例：

-- 創建外鍵的方式一 : 創建子表同時創建外鍵
-- 年級表 (id\年級名稱)
CREATE TABLE `grade` (
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級ID',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 學生信息表 (學號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號)
CREATE TABLE `Student` (
    `Sno` INT(4) NOT NULL COMMENT '學號',
    `Sname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `Ssex` TINYINT(1) DEFAULT '1' COMMENT '性別',
    `gradeid` INT(10) DEFAULT NULL COMMENT '年級',
    `phoneNum` VARCHAR(50) NOT NULL COMMENT '手機',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
    `borndate` DATETIME DEFAULT NULL COMMENT '生日',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
    `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
    PRIMARY KEY (`studentno`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
    (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

主鍵：

PRIMARY KEY (`欄位名`)

外鍵：

KEY FK_ `欄位名` (`欄位名`)

外鍵約束：給外鍵欄位添加約束執行引用另外一張表的同一個欄位名

CONSTRAINT `FK_欄位名` FOREIGN KEY (`欄位名`) REFERENCES `另外一張表名` (`另外一張表欄位名`)

設置非空：

NOT NULL

自增：

AUTO_INCREMENT

備註：

COMMENT `備註信息`

默認值：

DEFAULT '默認值'

設置索引：

ENGINE=InnoD

設置默認字元編碼 :

DEFAULT CHARSET=utf8

修改表名 :

ALTER TABLE 舊錶名 RENAME AS 新表名

添加欄位 :

ALTER TABLE 表名 ADD欄位名 列屬性[屬性]

修改欄位 :

ALTER TABLE 表名 MODIFY 欄位名 列類型[屬性]
ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 列屬性[屬性]

刪除欄位 :

ALTER TABLE 表名 DROP 欄位名

刪除表語法：

DROP TABLE [IF EXISTS] 表名

添加數據

INSERT命令

語法：

 INSERT
 INTO <表名>[(<欄位1>,<欄位2>,<欄位3>,...)]
 VALUES('值1','值2','值3')

實例一：

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES(1001,'張三','男','信息工程',18)

實例二：

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES(1001,'張三','男','信息工程',18),(1002,'李四','男','信息工程',19)

刪除數據

DELETE命令

TRUNCATE命令

作用：用於完全清空表數據 , 但表結構 , 索引 , 約束等不變 ;

語法：

DELETE
FROM <表名>
[WHERE <條件>];

實例：

DELETE
FROM Student
WHERE Sno = 10001;

修改數據

update命令

語法：

UPDATE <表名>
SET <列名>=<表達式> [, <列名>=<表達式>,]...
[WHERE <條件>];

實例一：

UPDATE Student
SET Sdept = `計算機`,Sage = 23
WHERE Sno = 1001

實例二：多條件

UPDATE Student
SET Sdept = `計算機`,Sage = 23
WHERE Sname = `張三` and Ssex = `男`

where條件語句：

查詢數據

select命令

簡單語法:

select * from Student --查詢所有學生信息

指定欄位查詢：

select Sno,Sname from Student   --查詢學生表的學號和姓名

別名：可以給….. 欄位起別名;也可以給 ….表起別名

selcet Sno as 學號,Sname as 名字 from Student

函數Concat(<字元串>,<列表名>)：拼接字元串

select CONCAT('姓名：',Sname) from Student --查詢結果 姓名：張三

DISTINCT 去重：去除重複數據

-- # 查看哪些同學參加了考試(學號) 去除重複項
SELECT * FROM result; -- 查看考試成績
SELECT studentno FROM result; -- 查看哪些同學參加了考試
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重複項 , (默認是ALL)

where條件語句

實例：

-- 查詢考試成績在95-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- 除了1000號同學,要其他同學的成績
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查詢 ： 比較操作符

實例：

<spna style=”margin: 0px; padding: 0px; color: rgb(255, 0, 0);”>LIKE的運用</spna>

-- 查詢姓張的同學的學號及姓名
-- like結合使用的通配符 : % (代表0到任意個字元) _ (一個字元)
SELECT Sno,Sname FROM Student
WHERE Sname LIKE '張%';

-- 查詢姓張的同學,後面只有一個字的
SELECT Sno,Sname FROM Student
WHERE studentname LIKE '張_';

-- 查詢姓張的同學,後面只有兩個字的
SELECT Sno,Sname FROM Student
WHERE Sname LIKE '張__';

IN的運用

-- 查詢學號為1000,1001,1002的學生姓名
SELECT Sno,Sname FROM Student
WHERE Sno IN (1000,1001,1002);

-- 查詢地址在廣州,深圳的學生
SELECT Sno,Sname,address FROM Student
WHERE address IN ('廣州','深圳');

NULL的運用

-- 查詢出生日期沒有填寫的同學
SELECT Sname FROM Student
WHERE BornDate IS NULL;
-- 查詢出生日期填寫的同學
SELECT Sname FROM Student
WHERE BornDate IS NOT NULL;
-- 查詢沒有寫家庭住址的同學(空字元串不等於null)
SELECT Sname FROM Student
WHERE Address='' OR Address IS NULL;

多表查詢

JOIN連接:

內連接 inner join
查詢兩個表中的結果集中的交集
外連接 outer join
查詢兩個表中的結果集中的並集
左外連接 left join
以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充
右外連接 right join
以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充

— 查詢參加了考試的同學信息(學號,學生姓名,科目編號,分數)
–Student表(學號,學生姓名)和result表(學生姓名,科目編號,分數)

SELECT s.Sno,Sname,subjectno,StudentResult
FROM Student s
INNER JOIN result r
ON r.Sno = s.Sno

-- 右連接(也可實現)
SELECT s.Sno,Sname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.Sno = s.Sno

-- 等值連接
SELECT s.Sno,Sname,subjectno,StudentResult
FROM Student s , result r
WHERE r.Sno = s.Sno

-- 左連接 (查詢了所有同學,不考試的也會查出來)
SELECT s.Sno,Sname,subjectno,StudentResult
FROM Student s
LEFT JOIN result r
ON r.Sno = s.Sno

-- 查一下缺考的同學(左連接應用場景)
SELECT s.Sno,Sname,subjectno,StudentResult
FROM Student s
LEFT JOIN result r
ON r.Sno = s.Sno
WHERE StudentResult IS NULL

-- 思考題:查詢參加了考試的同學信息(學號,學生姓名,科目名,分數)
SELECT s.Sno,Sname,Sname,StudentResult
FROM Student s
INNER JOIN result r
ON r.Sno = s.Sno
INNER JOIN `subject` sub
ON sub.Sno = r.Sno

自連接查詢

-- 創建一個表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入數據
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技術'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公信息');

實例操作：

-- 編寫SQL語句,將欄目的父子關係呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然後將這兩張表連接查詢(自連接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考題:查詢參加了考試的同學信息(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'

子查詢

什麼是子查詢?
在查詢語句中的WHERE條件子句中,又嵌套了另一個查詢語句
嵌套查詢可由多個子查詢組成,求解的方式是由里及外;
子查詢返回的結果一般都是集合,故而建議使用IN關鍵字;

操作實例：

-- 查詢 資料庫結構-1 的所有考試結果(學號,科目編號,成績),並且成績降序排列
-- 方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '資料庫結構-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查詢(執行順序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '資料庫結構-1'
)
ORDER BY studentresult DESC;

-- 查詢課程為 高等數學-2 且分數不小於80分的學生的學號和姓名
-- 方法一:使用連接查詢
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等數學-2' AND StudentResult>=80

-- 方法二:使用連接查詢+子查詢
-- 分數不小於80分的學生的學號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 方法三:使用子查詢
-- 分步寫簡單sql語句,然後將其嵌套起來
SELECT studentno,studentname FROM student WHERE studentno IN(
    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
        SELECT subjectno FROM `subject` WHERE subjectname = '高等數學-2'
    )
)

常用函數

數據函數:

SELECT ABS(-8); /*絕對值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*隨機數,返回一個0-1之間的隨機數*/
SELECT SIGN(0); /*符號函數: 負數返回-1,正數返回1,0返回0*/

字元串函數:

SELECT CHAR_LENGTH('我在學Mysql'); /*返回字元串包含的字元數*/
SELECT CONCAT('我','愛','程序'); /*合併字元串,參數可以有多個*/
SELECT INSERT('我愛編程helloworld',1,2,'超級熱愛'); /*替換字元串,從某個位置開始替
換某個長度*/
SELECT LOWER('Study'); /*小寫*/
SELECT UPPER('Study'); /*大寫*/
SELECT LEFT('hello,world',5); /*從左邊截取*/
SELECT RIGHT('hello,world',5); /*從右邊截取*/
SELECT REPLACE('堅持就能成功','堅持','努力'); /*替換字元串*/
SELECT SUBSTR('堅持就能成功',4,6); /*截取字元串,開始和長度*/
SELECT REVERSE('堅持就能成功'); /*反轉
-- 查詢姓周的同學,改成鄒
SELECT REPLACE(studentname,'周','鄒') AS 新名字
FROM student WHERE studentname LIKE '周%';

日期和時間函數:

SELECT CURRENT_DATE(); /*獲取當前日期*/
SELECT CURDATE(); /*獲取當前日期*/
SELECT NOW(); /*獲取當前日期和時間*/
SELECT LOCALTIME(); /*獲取當前日期和時間*/
SELECT SYSDATE(); /*獲取當前日期和時間*/
-- 獲取年月日,時分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

聚合函數

實例：

-- 查詢不同課程的平均分,最高分,最低分
-- 前提:根據不同的課程進行分組
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高
分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;