mysql 學習筆記

一、數據庫命令行操作

登錄
mysql -h ip地址 -u 用戶名 -p 密碼
2.修改密碼
alter user 'root'@'localhost' identified by '密碼';
3.退出
exit
4.開啟mysql服務
net start mysql
5.關閉mysql服務
net stop mysql

二、數據庫客戶端工具

WorkBench
Navicat

三、數據庫命令

1.連接mysql

新建連接
- name：自定義名字
- method：tcp/ip
- hostname: sql服務地址，如果是本地就是127.0.0.1
- port：端口，默認3306
創建用戶
CREATE USER 'username'@'地址' IDENTIFIED BY 'password'
給用戶權限
grant all privileges on *.* to 'username'@'%'
% 代表任意地址

2.新建數據庫

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] 數據庫名 CHARACTER SET [] 字符集

3.查看數據庫

選擇數據庫
USE 數據庫名
顯示創建的所有數據庫
SHOW DATABASES
查看數據庫的定義信息
SHOW CREATE DATABASE 數據庫名
查看數據庫data存儲路徑
SHOW variables LIKE '%datadir%';

4.修改數據庫

修改數據庫
ALTER {DATABASE} [數據庫名] CHARACTER SET [=] 字符集 --不寫數據庫名默認修改當前數據庫

5.刪除數據庫

刪除已存在的數據庫
DROP DATABASE [IF EXISTS] 數據庫名

6.操作數據庫表

創建表
CREATE TABLE student(id INT,name VARCHAR(10))
創建表帶備注
CREATE TABLE student (id INT,name VARCHAR(10) COMMENT '學生名字')
復制表
CREATE TABLE 數據表名 {LIKE 源數據表名 | (LIKE 源數據表名)}
CREATE TABLE teacher LIKE student;
查看表
SHOW TABLES;
查看表結構
DESCRIBE 表名; -- 簡寫DESC
DESCRIBE 表名列名;
查看表完整信息，包括備注
SHOW FULL COLUMNS FROM student;
刪除表
DROP TABLE [IF EXISTS] 表名
修改表名
ALTER TABLE 原表名 RENAME 新表名;
RENAME TABLE 原表名 TO 新表名;
添加列
ALTER TABLE 表名 ADD 列名列屬性
ALTER TABLE studen ADD emial varchar(50) NOT NULL
ALTER TABLE student ADD (email VARCHAR(10),age INT);
修改列屬性
ALTER TABLE 表名 MODIFY 列名列屬性
ALTER TABLE student MODIFY email VARCHAR(5) NOT NULL;
修改列名
ALTER TABLE 表名 CHANGE 原列名新列名列屬性
ALTER TABLE student CHANGE COLUMN iphone new_iphone VARCHAR(10) DEFAULT '123';
刪除列
ALTER TABLE 表名 DROP 列名
ALTER TABLE student DROP new_iphone;
設置主鍵列
ALTER TABLE student ADD PRIMARY KEY(id);

7.表數據操作

插入數據
INSERT INTO 表名 (列名1，列名2) VALUES(值1，值2) -- 值1寫入列1，值2寫入列2，如果沒有寫列名，按表順序插入數據

CREATE TABLE user(
id INT,
name VARCHAR(10),
age INT,
sex CHAR(1),
address VARCHAR(50)
);
INSERT INTO user (id,name,sex) VALUES(01,'XXX','女');

完整添加所有列數據
INSERT INTO user VALUES(02,'xx1',12,'男','地址地址');
一次添加多條數據
INSERT INTO user (id,name,age) VALUES(03,'XX3',13),(04,'xx4',14);

修改表數據
UPDATE 表名 SET 列名=值 [WHERE 條件表達式]
INSERT INTO student VALUES (01,'趙','111@qq.com',12), (2,'錢','22@qq.com',13), (3,'孫','33@qq.com',14);
ALTER TABLE student ADD PRIMARY KEY(id); -- 必須有主鍵不然報錯
UPDATE student SET email='xxx@qq.com' WHERE id=2;
UPDATE student SET email='xxx11@qq.com',age=33 WHERE id=1;
刪除表數據
DELETE FROM 表名 WHERE 條件表達式
DELETE FROM student WHERE id = 3;
刪除表中所有數據
TRUNCATE TABLE 表名

7.查詢數據庫表

單表查詢
SELECT * FROM 表名;
字段查詢
SELECT dept_name FROM departments;
表別名
SELECT dept_name FROM departments dd;
字段別名
SELECT dept_name AS 'name' FROM departments dd;
去掉重復數據
SELECT DISTINCT 列名 FROM 表名
SELECT DISTINCT title FROM employees.titles;
條件查詢
SELECT * FROM 表名 WHERE 條件表達式
取特定位置的數據
SELECT * FROM student ORDER BY age LIMIT 5; -- 取前5條數據
SELECT * FROM student ORDER BY age LIMIT 2,1; -- 取第3條數據（從0開始數）
SELECT * FROM student ORDER BY age LIMIT 2,5; -- 取第3條數據開始的5條數據

8.運算

查詢運算
SELECT (列名運算表達式) FROM 表名;
SELECT emp_no,salary+1000 FROM salaries;

9.比較運算符

大于、小于……
> < <= >= = <> !=
SELECT emp_no,salary FROM salaries WHERE salary=61117;
范圍限定
BETWEEN ... AND ...
子集限定
IN
模糊查詢
LIKE '%or%'
空查詢
IS NULL

10.排序

ORDER BY 排序命令
DESC 降序
ASC 升序（默認）

SELECT * FROM student
ORDER BY age DESC;

組合排序
- 先以第一字段進行排序，第一字段相同就按照第二字段排序

-- 先對age進行降序排序，再按score進行排序
SELECT * FROM student ORDER BY age DESC,score;

11.聚合函數

SELECT 聚合函數(列) FROM 表名

COUNT() ：統計統計列不為NULL的記錄行數
SELECT COUNT(score) FROM student
MAX() ：計算指定列的最大值
SELECT MAX(score) FROM student
MIN() ：指定列的最小值
SELECT MIN(score) FROM student
SUM() : 指定列的數值和
SELECT SUM(age) FROM student WHERE sex='女'
AVG() : 指定列的平均值
SELECT AVG(score) FROM student WHERE sex='女'

12.分組查詢

SELECT 分組列/聚合函數 FROM 表名 GROUP BY 列名 HAVING 條件

SELECT emo_no,sum(salary) FROM salaries GROUP BY emo_no

先對相同的emo_no進行分組
再對每個分組的salary進行sum()操作

查詢員工編號小于10010的，薪資和小于400000的員工薪資和

SELECT 
   emp_no, SUM(salary)
FROM
   salaries
WHERE
   emp_no < 10010
GROUP BY emp_no
HAVING SUM(salary) < 400000;

WHERE 從源數據去篩選
GROUP BY 對數據進行分組，再利用聚合函數對分組進行計算
HAVING子句分完組之后再進行一次篩選

14.limit

限制查詢結果的數量，默認從0開始計數

SELECT * FROM student LIMIT 3; --展示前3條數據
SELECT * FROM employees LIMIT 3，100； -- 展示第3條到103條數據
SELECT *FROM employees LIMIT 50 OFFSET 3; -- 展示第3條到第53條數據

mysql語句執行順序

1、原始數據庫 FROM 產生一個虛擬表1
2、虛擬表1 WHERE 語句產生虛擬表2
3、虛擬表2 GROUP BY 語句產生虛擬表3
4、虛擬表3 HAVING子句產生虛擬表4
5、虛擬表4 SELECT 語句產生虛擬表5
6、虛擬表5 DISTINCT 語句產生虛擬表6
7、虛擬表6 ORDER BY語句產生虛擬表7
8、虛擬表7 LIMIT 語句產生最終結果

SQL 約束

對表中數據進行進一步的限制，保證數據的正確性、有效性、完整性

1. 主鍵約束 PRIMARY KEY

主鍵：唯一的標識表中的每一行
特點：不可重復，唯一，非空
創建主鍵的方式
1. 創建表時設置主鍵

 --列名 字段類型 PRIMARY KEY
CREATE TABLE leaders (
   leader_id INT PRIMARY KEY,
   name VARCHAR(10),
   sex CHAR(1)
);
      ```

  2. 已有的表創建主鍵
     ```
     -- ALTER TABLE 表名 ADD PRIMARY KEY(列名)
     ALTER TABLE teacher ADD PRIMARY KEY(id);

設置主鍵自動增加

CREATE TABLE new_table (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(10),
   sex CHAR(1)
);
INSERT INTO new_table VALUE (1,'XX','女');
INSERT INTO new_table(name,sex) VALUE ('XXY','女');

CREATE TABLE new_table (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(10),
   sex CHAR(1)
)AUTO_INCREMENT=100;  --主鍵自增的起始值為100

采用自增的方式添加主鍵，使用DELECT 刪除表中的數據，再次添加會繼續上一次的主鍵值繼續自增，使用TRUNCATE 刪除表后新增數據會重新創建新表

2. 非空約束 NOT NULL

- 特點：某一列不許為空
- `列名 字段類型 NOT NULL`
    `CREATE TABLE student (uid INT NOT NULL)`

3. 唯一約束 UNIQUE

- 特點：某一列值不能重復
- 對NULL 不做唯一的判斷（可以有多個NULL）
- `列名 字段類型 UNIQUE`

CREATE TABLE student (uid INT UNIQUE)
- 唯一約束和主鍵約束的區別：
1. 主鍵約束唯一且不能為空，唯一約束唯一但是可以為空
2.一個表內只能有一個主鍵，但是可以有多個唯一約束

默認值 DEFAULT

CREATE TABLE student (uid INT PRIMARY KEY AUTO_INCREMENT, sex CHAR(1) DEFAULT '女')

多表結構

特點：簡化數據、提高復用性、方便權限控制、提高系統的穩定性和負載能力

1.外鍵約束

定義：
1.外鍵：從表中與主表的主鍵對應的字段
2. 主表：外鍵所指向的表，約束其他表的表
價值：建立主表與從表的關聯關系，約束兩個表中數據的一致性和完整性
創建外鍵約束：
1. 創建表時創建外鍵約束
  CONSTRAINT [外鍵約束的名稱] FOREIGN KEY (外鍵字段) REFERENCES [主表名稱(主鍵字段)]
2. 插入外鍵約束
  ALTER TABLE [表名] ADD CONSTRAINT [外鍵約束的名稱] FOREIGN KEY [外鍵字段] REFERENCES [主表名稱(主鍵字段)]
- 創建外鍵，先創建主表數據再創建從表數據
刪除外鍵約束
ALTER TABLE [表名] DROP FOREIGN KEY [外鍵約束名稱]
- 刪除外鍵約束需要下刪除從表數據再刪除主表數據

2.外鍵查詢

3、多表查詢

內連接 INNER JOIN
- 隱式內連接 SELECT * FROM 表1，表2 WHERE [條件]（沒有寫出 INNER JOIN。通過過濾條件篩選出符合條件的兩個表相等的數據）
- 顯式內連接 SELECT * FROM 表1 INNER JOIN 表2 ON 條件
- 隱式內連接和顯式內連接作用是相同的
外連接
- 左外連接 LEFT JOIN：左表顯示所有數據，根據匹配條件，如果右表沒有，顯示為NULL
  SELECT * FROM dept LEFT JOIN emp_part ON dept.id=emp_part.id
- 右外連接 RIGHT JOIN
- 與內連接的差別：
  不僅返回符合匹配條件的行，也會返回左表（左連接）或右表（右連接）的所有數據行

4、子查詢

定義：一個查詢語句嵌套再另一個查詢語句內部，在SELECT 子句中先計算子查詢，子查詢的結果作為外層另一個查詢的過濾條件，MYSQL 4.1開始引入。
FROM 型子查詢：將子查詢的結果作為父查詢的表來使用

-- 計算各部門性別為男性的員工人數
SELECT
COUNT(em.emp_no), dept_no
FROM
(SELECT
*
FROM
employees
WHERE
gender = 'F') em
INNER JOIN
dept_emp ON em.emp_no = dept_emp.emp_no
GROUP BY dept_no;

   - IN/ NOT IN 型子查詢：子查詢的結果是單列多行，作為WHERE的過濾條件
   - WHERE 型子查詢：查詢結果（單行單列）作為過濾條件出現在比較運算符的一端

posted @ 2023-03-06 16:53 叢影HHZ 閱讀(31) 評論(0) 收藏舉報

刷新頁面返回頂部

叢影HHZ