{"id":121,"date":"2024-01-09T15:02:40","date_gmt":"2024-01-09T07:02:40","guid":{"rendered":"http:\/\/www.sunqizheng1997.com\/?p=121"},"modified":"2024-01-09T15:03:46","modified_gmt":"2024-01-09T07:03:46","slug":"mysql%e5%9f%ba%e7%a1%80","status":"publish","type":"post","link":"http:\/\/www.sunqizheng1997.com\/index.php\/2024\/01\/09\/mysql%e5%9f%ba%e7%a1%80\/","title":{"rendered":"MySQL\u57fa\u7840"},"content":{"rendered":"

1.MySQL\u6982\u8ff0<\/h1>\n

1.1 \u6570\u636e\u5e93\u76f8\u5173\u6982\u5ff5<\/h2>\n

\u6570\u636e\u5e93\uff0c\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff0cSQL<\/p>\n

\"image-20231024105714229\"<\/div><\/p>\n

\u4e3b\u6d41\u5173\u7cfb\u578b\u6570\u636e\u5e93<\/p>\n

\"image-20231024105809983\"<\/div><\/p>\n

2.SQL<\/h1>\n

\"image-20231024105935002\"<\/div><\/p>\n

2.1 SQL\u901a\u7528\u8bed\u6cd5<\/h2>\n

\"image-20231024110006710\"<\/div><\/p>\n

2.2 SQL\u5206\u7c7b<\/h2>\n

\u6839\u636e\u529f\u80fd\u4e3b\u8981\u5206\u4e3aDDL\uff0cDML\uff0cDQL\uff0cDCL<\/p>\n

\"image-20231024110059791\"<\/div><\/p>\n

2.3 DDL<\/h2>\n

Data Definition Language\uff0c\u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff0c\u7528\u6765\u5b9a\u4e49\u6570\u636e\u5e93\u5bf9\u8c61\uff08\u6570\u636e\u5e93\uff0c\u8868\uff0c\u5b57\u6bb5\uff09<\/p>\n

2.3.1 \u6570\u636e\u5e93\u64cd\u4f5c<\/h3>\n

1\uff09\u67e5\u8be2\u6240\u6709\u6570\u636e\u5e93<\/h4>\n
show databases;<\/code><\/pre>\n
2\uff09\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93<\/h4>\n
select databases;<\/code><\/pre>\n
3\uff09\u521b\u5efa\u6570\u636e\u5e93<\/h4>\n
create database [if not exists] \u6570\u636e\u5e93\u540d [default charset \u5b57\u7b26\u96c6][collate \u6392\u5e8f\u89c4\u5219]\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u521b\u5efa\u4e00\u4e2aitcase\u6570\u636e\u5e93\uff0c\u4f7f\u7528\u6570\u636e\u5e93\u9ed8\u8ba4\u7684\u5b57\u7b26\u96c6<\/p>\n
create database if not exists itcast\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\u521b\u5efa\u4e00\u4e2aitheima\u6570\u636e\u5e93\uff0c\u5e76\u6307\u5b9a\u5b57\u7b26\u96c6<\/p>\n
create database itheima default charset utf8mb4;<\/code><\/pre>\n
4\uff09\u5220\u9664\u6570\u636e\u5e93<\/h4>\n
drop databases [if exists] \u6570\u636e\u5e93\u540d\uff1b<\/code><\/pre>\n
5\uff09\u5207\u6362\u6570\u636e\u5e93<\/h4>\n
use \u6570\u636e\u5e93\u540d\uff1b<\/code><\/pre>\n
2.3.2 \u8868\u64cd\u4f5c<\/h3>\n
2.3.2.1 \u8868\u64cd\u4f5c-\u67e5\u8be2\u521b\u5efa<\/h4>\n
1\uff09\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93\u6240\u6709\u8868<\/h5>\n
show tables\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u5207\u6362\u5230sys\u8fd9\u4e2a\u7cfb\u7edf\u6570\u636e\u5e93\uff0c\u5e76\u67e5\u770b\u7cfb\u7edf\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u8868\u7ed3\u6784<\/p>\n
use sys;\n\nshow tables;<\/code><\/pre>\n
2\uff09\u67e5\u770b\u6307\u5b9a\u8868\u7ed3\u6784<\/h5>\n
desc \u8868\u540d\uff1b<\/code><\/pre>\n
3\uff09\u67e5\u770b\u6307\u5b9a\u8868\u7684\u5efa\u8868\u8bed\u53e5<\/h5>\n
show create table \u8868\u540d\uff1b<\/code><\/pre>\n
4\uff09\u521b\u5efa\u8868\u7ed3\u6784<\/h5>\n
CREATE TABLE \u8868\u540d\uff08\n       \u5b57\u6bb51 \u5b57\u6bb51\u7c7b\u578b[COMMENT \u5b57\u6bb51\u6ce8\u91ca]\uff0c\n       \u5b57\u6bb52 \u5b57\u6bb52\u7c7b\u578b[COMMENT \u5b57\u6bb52\u6ce8\u91ca]\uff0c\n       \u5b57\u6bb53 \u5b57\u6bb53\u7c7b\u578b[COMMENT \u5b57\u6bb53\u6ce8\u91ca]\uff0c\n       ...\n       \u5b57\u6bb5n \u5b57\u6bb5n\u7c7b\u578b[COMMENT \u5b57\u6bb5n\u6ce8\u91ca]\n\uff09[COMMENT \u8868\u6ce8\u91ca]\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u6bd4\u5982\u6211\u4eec\u521b\u9020\u5982\u4e0b\u7684\u8868<\/p>\n\n\n\n\n\n\n\n
id<\/th>\nname<\/th>\nage<\/th>\ngender<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n\u4ee4\u72d0\u51b2<\/td>\n28<\/td>\n\u7537<\/td>\n<\/tr>\n
2<\/td>\n\u98ce\u6e05\u626c<\/td>\n68<\/td>\n\u7537<\/td>\n<\/tr>\n
3<\/td>\n\u4e1c\u65b9\u4e0d\u8d25<\/td>\n32<\/td>\n\u7537<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
create table tb_user(\n       id int comment '\u7f16\u53f7'\uff0c\n       name varchar(50) comment '\u59d3\u540d'\uff0c\n       age int comment '\u5e74\u9f84'\uff0c\n       gender varchar(1) comment '\u6027\u522b'\n)comment '\u7528\u6237\u8868'\uff1b<\/code><\/pre>\n
2.3.2.2 \u8868\u64cd\u4f5c-\u6570\u636e\u7c7b\u578b<\/h4>\n
\u6570\u503c\u7c7b\u578b\uff0c\u5b57\u7b26\u4e32\u7c7b\u578b\uff0c\u65e5\u671f\u65f6\u95f4\u7c7b\u578b<\/p>\n
1\uff09\u6570\u503c\u7c7b\u578b<\/h5>\n
\"image-20231025082239849\"<\/div><\/p>\n
\u4e3e\u4f8b\uff1a<\/p>\n
1\uff09age tinyint unsigned\n\n2\uff09score double\uff084,1\uff09<\/code><\/pre>\n
2\uff09\u5b57\u7b26\u4e32\u7c7b\u578b<\/h5>\n
\"image-20231025082428195\"<\/div><\/p>\n
char\u548cvarchar\uff1achar\u662f\u5b9a\u957f\u5b57\u7b26\u4e32\uff0c\u6307\u5b9a\u957f\u5ea6\u5c31\u662f\u5360\u7528\u957f\u5ea6\uff0cvarchar\u662f\u53d8\u957f\u5b57\u7b26\u4e32\uff0cchar\u6027\u80fd\u9ad8\u4e9b\u3002<\/p>\n
\u4e3e\u4f8b\uff1a<\/p>\n
user varchar(50)\n\ngender char(1)\n\nphone char(11)<\/code><\/pre>\n
3\uff09\u65e5\u671f\u65f6\u95f4\u7c7b\u578b<\/p>\n
\"image-20231025082859728\"<\/div><\/p>\n
\u4e3e\u4f8b\uff1a<\/p>\n
birthday date\n\ncreatetime datetime<\/code><\/pre>\n
2.3.2.3 \u8868\u64cd\u4f5c-\u4e3e\u4f8b<\/h4>\n
\"image-20231025083017793\"<\/div><\/p>\n
\u7ed3\u679c\uff1a<\/p>\n
use mydb1;\n\ncreate table emp (\n      id int comment '\u7f16\u53f7',\n      workno varchar(10) comment '\u5de5\u53f7',\n      name varchar(10) comment '\u59d3\u540d',\n      gender char(1) comment '\u6027\u522b',\n      age tinyint unsigned comment '\u5e74\u9f84',\n      idcard char(18) comment '\u8eab\u4efd\u8bc1\u53f7',\n      entrydate date comment '\u5165\u804c\u65f6\u95f4'\n) comment '\u5458\u5de5\u8868';<\/code><\/pre>\n
2.3.2.4 \u8868\u64cd\u4f5c-\u4fee\u6539<\/h4>\n
1\uff09\u6dfb\u52a0\u5b57\u6bb5<\/h5>\n
ALTER TABLE \u8868\u540d ADD \u5b57\u6bb5\u540d \u7c7b\u578b\uff08\u957f\u5ea6\uff09 [COMMENT \u6ce8\u91ca][\u7ea6\u675f]\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a<\/p>\n
\u4e3aemp\u8868\u6dfb\u52a0\u4e00\u4e2a\u65b0\u5b57\u6bb5\u201c\u6635\u79f0\u201d\u4e3anickname\uff0c\u7c7b\u578b\u4e3avarchar\uff0820\uff09<\/p>\n
ALTER TABLE emp ADD nickname varchar\uff0820\uff09 COMMENT '\u6635\u79f0'\uff1b<\/code><\/pre>\n
2\uff09\u4fee\u6539\u6570\u636e\u7c7b\u578b<\/h5>\n
ALTER TABLE \u8868\u540d MODIFY \u5b57\u6bb5\u540d \u65b0\u6570\u636e\u7c7b\u578b\uff08\u957f\u5ea6\uff09\uff1b<\/code><\/pre>\n
3\uff09\u4fee\u6539\u5b57\u6bb5\u540d\u548c\u5b57\u6bb5\u7c7b\u578b<\/h5>\n
ALTER TABLE \u8868\u540d CHANGE \u65e7\u5b57\u6bb5\u540d \u65b0\u5b57\u6bb5\u540d \u7c7b\u578b\uff08\u957f\u5ea6\uff09 [COMMENT \u6ce8\u91ca] [\u7ea6\u675f] \uff1b<\/code><\/pre>\n
4\uff09\u5220\u9664\u5b57\u6bb5<\/h5>\n
ALTER TABLE \u8868\u540d DROP \u5b57\u6bb5\u540d\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u5c06emp\u8868\u7684\u5b57\u6bb5username\u5220\u9664<\/p>\n
ALTER TABLE emp DROP username\uff1b<\/code><\/pre>\n
5\uff09\u4fee\u6539\u8868\u540d<\/h5>\n
ALTER TABLE \u8868\u540d RENAME TO \u65b0\u8868\u540d\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u5c06emp\u8868\u7684\u8868\u540d\u4fee\u6539\u4e3aemployee<\/p>\n
ALTER TABLE emp RENAME TO employee\uff1b<\/code><\/pre>\n
2.3.2.5 \u8868\u64cd\u4f5c-\u5220\u9664<\/h4>\n
1\uff09\u5220\u9664\u8868<\/h5>\n
DROP TABLE [if exists] \u8868\u540d\uff1b<\/code><\/pre>\n
2.4 \u56fe\u5f62\u5316\u754c\u9762<\/h2>\n
2.5 DML<\/h2>\n
\"image-20231025091906987\"<\/div><\/p>\n
\u6dfb\u52a0\u6570\u636e\uff08INSERT\uff09<\/p>\n
\u4fee\u6539\u6570\u636e\uff08UPDATE\uff09<\/p>\n
\u5220\u9664\u6570\u636e\uff08DELETE\uff09<\/p>\n
2.5.1 \u6dfb\u52a0\u6570\u636e<\/h3>\n
1\uff09\u7ed9\u6307\u5b9a\u5b57\u6bb5\u6dfb\u52a0\u6570\u636e<\/h4>\n
INSERT INTO \u8868\u540d \uff08\u5b57\u6bb5\u540d1\uff0c\u5b57\u6bb5\u540d2\uff0c...\uff09 VALUES\uff08\u503c1\uff0c\u503c2\uff0c...\uff09;<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u7ed9employee\u8868\u6240\u6709\u5b57\u6bb5\u6dfb\u52a0\u6570\u636e\uff1a<\/p>\n
insert into employee(id,workno,name,gender,age,idcard,entrydate)\n\nvalues(1,'1','Itcast','\u7537',10,'123456789012345678','2000-01-01');<\/code><\/pre>\n
2\uff09\u7ed9\u5168\u90e8\u5b57\u6bb5\u6dfb\u52a0\u6570\u636e<\/h4>\n
INSERT INTO \u8868\u540d VALUES\uff08\u503c1\uff0c\u503c2\uff0c...\uff09;<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u63d2\u5165\u6570\u636e\u5230employee\u8868\uff0c\u5177\u4f53\u7684SQL\u5982\u4e0b\uff1a<\/p>\n
insert into employee values(2,'2','\u5f20\u65e0\u5fcc','\u7537',18,'123456789012345678'\uff0c'2005-01-01');<\/code><\/pre>\n
3\uff09\u6279\u91cf\u6dfb\u52a0\u6570\u636e<\/h4>\n
INSERT INTO \u8868\u540d \uff08\u5b57\u6bb5\u540d1\uff0c\u5b57\u6bb5\u540d2\uff0c...\uff09 VALUES \uff08\u503c1\uff0c\u503c2\uff0c...\uff09,(\u503c1\uff0c\u503c2\uff0c...)\uff1b<\/code><\/pre>\n
INSERT INTO \u8868\u540d VALUES\uff08\u503c1\uff0c\u503c2\uff0c...\uff09, \uff08\u503c1\uff0c\u503c2\uff0c...\uff09,\uff08\u503c1\uff0c\u503c2\uff0c...\uff09;<\/code><\/pre>\n
\u6ce8\u610f\u4e8b\u9879\uff1a<\/p>\n
\u63d2\u5165\u6570\u636e\u65f6\uff0c\u5236\u5b9a\u7684\u5b57\u6bb5\u987a\u5e8f\u9700\u8981\u4e0e\u503c\u7684\u987a\u5e8f\u4e00\u4e00\u5bf9\u5e94\uff1b<\/p>\n
\u5b57\u7b26\u4e32\u548c\u65e5\u671f\u578b\u6570\u636e\u5e94\u5305\u542b\u5728\u5f15\u53f7\u4e2d\uff1b<\/p>\n
\u63d2\u5165\u7684\u6570\u636e\u5927\u5c0f\uff0c\u5e94\u8be5\u5728\u5b57\u6bb5\u7684\u89c4\u5b9a\u8303\u56f4\u5185\u3002<\/p>\n
2.5.2 \u4fee\u6539\u6570\u636e<\/h3>\n
UPDATE \u8868\u540d SET \u5b57\u6bb5\u540d1=\u503c1\uff0c\u5b57\u6bb5\u540d2=\u503c2\uff0c...[WHERE \u6761\u4ef6]\uff1b<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u4fee\u6539id\u4e3a1\u7684\u6570\u636e\uff0c\u5c06name\u4fee\u6539\u4e3aitheima<\/p>\n
update employee set name = 'itheima' where id = 1;<\/code><\/pre>\n
\u4fee\u6539id\u4e3a1\u7684\u6570\u636e\uff0c\u5c06name\u4fee\u6539\u4e3a\u5c0f\u662d\uff0cgender\u4fee\u6539\u4e3a\u5973<\/p>\n
update employee set name = '\u5c0f\u662d' , gender = '\u5973' where id = 1;<\/code><\/pre>\n
\u5c06\u6240\u6709\u5458\u5de5\u7684\u5165\u804c\u65e5\u671f\u4fee\u6539\u4e3a2008-01-01<\/p>\n
update employee set entrydate = '2008-01-01';<\/code><\/pre>\n
\u4fee\u6539\u8bed\u53e5\u7684\u6761\u4ef6\u5982\u679c\u6ca1\u6709\uff0c\u5219\u4f1a\u4fee\u6539\u6574\u5f20\u8868\u7684\u6570\u636e<\/p>\n
2.5.3 \u5220\u9664\u6570\u636e<\/h3>\n
DELETE FROM \u8868\u540d [WHERE \u6761\u4ef6];<\/code><\/pre>\n
\u4e3e\u4f8b\uff1a\u5220\u9664gender\u4e3a\u5973\u7684\u5458\u5de5<\/p>\n
delete from employee\uff1b<\/code><\/pre>\n
\u6ce8\u610f\uff1a<\/p>\n
delete\u8bed\u53e5\u7684\u6761\u4ef6\u53ef\u4ee5\u6ca1\u6709\uff0c\u6ca1\u6709\u5c31\u662f\u5220\u9664\u6240\u6709\u7684\u6570\u636e<\/p>\n
delete\u4e0d\u80fd\u5220\u9664\u67d0\u4e00\u4e2a\u5b57\u6bb5\u7684\u503c\uff08\u53ef\u4ee5\u4f7f\u7528update\uff0c\u5c06\u6539\u5b57\u6bb5\u7f6e\u4e3aNULL\u5c31\u884c\uff09<\/p>\n
2.6 DQL<\/h2>\n
\u5728\u4e00\u4e2a\u6b63\u5e38\u7684\u4e1a\u52a1\u7cfb\u7edf\u4e2d\uff0c\u67e5\u8be2\u64cd\u4f5c\u7684\u9891\u6b21\u662f\u8981\u8fdc\u9ad8\u4e8e\u589e\u5220\u6539\u7684\uff0c\u5f53\u6211\u4eec\u53bb\u8bbf\u95ee\u4f01\u4e1a\u5b98\u7f51\u3001\u7535\u5546\u7f51\u7ad9\uff0c\u5728\u8fd9\u4e9b\u7f51\u7ad9\u4e2d\u6211\u4eec\u6240\u770b\u5230\u7684\u6570\u636e\uff0c\u5b9e\u9645\u90fd\u662f\u9700\u8981\u4ece\u6570\u636e\u5e93\u4e2d\u67e5\u8be2\u5e76\u5c55\u793a\u7684\u3002\u800c\u4e14\u5728\u67e5\u8be2\u7684\u8fc7\u7a0b\u4e2d\uff0c\u53ef\u80fd\u8fd8\u4f1a\u6d89\u53ca\u5230\u6761\u4ef6\u3001\u6392\u5e8f\u3001\u5206\u9875\u7b49\u64cd\u4f5c\u3002<\/p>\n
\u51c6\u5907\u6570\u636e\uff1a<\/p>\n
use mydb1;\nDROP TABLE IF EXISTS employee;\nCREATE TABLE emp (\n                     id INT COMMENT '\u7f16\u53f7',\n                     workno VARCHAR(10) COMMENT '\u5de5\u53f7',\n                     name VARCHAR(10) COMMENT '\u59d3\u540d',\n                     gender CHAR(1) COMMENT '\u6027\u522b',\n                     age TINYINT UNSIGNED COMMENT '\u5e74\u9f84',\n                     idcard CHAR(18) COMMENT '\u8eab\u4efd\u8bc1\u53f7',\n                     workaddress VARCHAR(50) COMMENT '\u5de5\u4f5c\u5730\u5740',\n                     entrydate DATE COMMENT '\u5165\u804c\u65f6\u95f4'\n) COMMENT '\u5458\u5de5\u8868';\n\nINSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)\nVALUES\n    (1, '00001', '\u67f3\u5ca9666', '\u5973', 20, '123456789012345678', '\u5317\u4eac', '2000-01-01'),\n    (2, '00002', '\u5f20\u65e0\u5fcc', '\u7537', 18, '123456789012345670', '\u5317\u4eac', '2005-09-01'),\n    (3, '00003', '\u97e6\u4e00\u7b11', '\u7537', 38, '123456789712345670', '\u4e0a\u6d77', '2005-08-01'),\n    (4, '00004', '\u8d75\u654f', '\u5973', 18, '123456757123845670', '\u5317\u4eac', '2009-12-01'),\n    (5, '00005', '\u5c0f\u662d', '\u5973', 16, '123456769012345678', '\u4e0a\u6d77', '2007-07-01'),\n    (6, '00006', '\u6768\u900d', '\u7537', 28, '12345678931234567X', '\u5317\u4eac', '2006-01-01'),\n    (7, '00007', '\u8303\u7476', '\u7537', 40, '123456789212345670', '\u5317\u4eac', '2005-05-01'),\n    (8, '00008', '\u9edb\u7eee\u4e1d', '\u5973', 38, '123456157123645670', '\u5929\u6d25', '2015-05-01'),\n    (9, '00009', '\u8303\u51c9\u51c9', '\u5973', 45, '123156789012345678', '\u5317\u4eac', '2010-04-01'),\n    (10, '00010', '\u9648\u53cb\u8c05', '\u7537', 53, '123456789012345670', '\u4e0a\u6d77', '2011-01-01'),\n    (11, '00011', '\u5f20\u58eb\u8bda', '\u7537', 55, '123567897123465670', '\u6c5f\u82cf', '2015-05-01'),\n    (12, '00012', '\u5e38\u9047\u6625', '\u7537', 32, '123446757152345670', '\u5317\u4eac', '2004-02-01'),\n    (13, '00013', '\u5f20\u4e09\u4e30', '\u7537', 88, '123656789012345678', '\u6c5f\u82cf', '2020-11-01'),\n    (14, '00014', '\u706d\u7edd', '\u5973', 65, '123456719012345670', '\u897f\u5b89', '2019-05-01'),\n    (15, '00015', '\u80e1\u9752\u725b', '\u7537', 70, '12345674971234567X', '\u897f\u5b89', '2018-04-01'),\n    (16, '00016', '\u5468\u82b7\u82e5', '\u5973', 18, NULL, '\u5317\u4eac', '2012-06-01');<\/code><\/pre>\n
2.6.1 \u57fa\u672c\u8bed\u6cd5<\/h3>\n
DQL \u67e5\u8be2\u8bed\u53e5\uff0c\u7ed3\u6784\u5982\u4e0b\uff1a<\/p>\n
SELECT\n        \u5b57\u6bb5\u5217\u8868\nFROM\n        \u8868\u660e\u5217\u8868\nWHERE\n        \u6761\u4ef6\u5217\u8868\nGROUP BY\n        \u5206\u7ec4\u5b57\u6bb5\u5217\u8868\nHAVING\n        \u5206\u7ec4\u540e\u6761\u4ef6\u5217\u8868\nORDER BY\n        \u6392\u5e8f\u5b57\u6bb5\u5217\u8868\nLIMIT\n        \u5206\u9875\u53c2\u6570<\/code><\/pre>\n
\u67e5\u8be2\u5206\u7c7b\uff1a<\/p>\n
    \n
  • \u57fa\u672c\u67e5\u8be2\uff08\u4e0d\u5e26\u4efb\u4f55\u6761\u4ef6\uff09<\/li>\n
  • \u6761\u4ef6\u67e5\u8be2\uff08WHERE\uff09<\/li>\n
  • \u805a\u5408\u51fd\u6570\uff08count,max,min,avg,sum\uff09<\/li>\n
  • \u5206\u7ec4\u67e5\u8be2\uff08group by\uff09<\/li>\n
  • \u6392\u5e8f\u67e5\u8be2\uff08order by\uff09<\/li>\n
  • \u5206\u9875\u67e5\u8be2\uff08limit\uff09<\/li>\n<\/ul>\n
    2.6.2 \u57fa\u7840\u67e5\u8be2<\/h3>\n
    1\uff09\u67e5\u8be2\u591a\u4e2a\u5b57\u6bb5<\/h4>\n
    SELECT \u5b57\u6bb51\uff0c\u5b57\u6bb52\uff0c\u5b57\u6bb53\uff0c... FROM \u8868\u540d;<\/code><\/pre>\n
    SELECT * FROM \u8868\u540d;<\/code><\/pre>\n
    2\uff09\u5b57\u6bb5\u8bbe\u7f6e\u522b\u540d<\/h4>\n
    SELECT \u5b57\u6bb51 [AS \u522b\u540d1], \u5b57\u6bb52 [AS \u522b\u540d2], ... FROM \u8868\u540d;<\/code><\/pre>\n
    SELECT \u5b57\u6bb51 [\u522b\u540d1], \u5b57\u6bb52[\u522b\u540d2] ... FROM \u8868\u540d;<\/code><\/pre>\n
    3\uff09\u53bb\u9664\u91cd\u590d\u8bb0\u5f55<\/h4>\n
    SELECT DISTINCT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u6307\u5b9a\u5b57\u6bb5name\uff0cworkno\uff0cage\u5e76\u8fd4\u56de<\/p>\n
    select name,workno,age from emp;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u8fd4\u56de\u6240\u6709\u5b57\u6bb5<\/p>\n
    select id ,workno,name,gender,age,idcard,workaddress,entrydate from emp;<\/code><\/pre>\n
    select * from emp;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u6240\u6709\u5458\u5de5\u7684\u5de5\u4f5c\u5730\u5740\uff0c\u8d77\u522b\u540d(as\u53ef\u4ee5\u7701\u7565)<\/p>\n
    select workaddress as '\u5de5\u4f5c\u5730\u5740' from emp;<\/code><\/pre>\n
    select distinct workaddress as '\u5de5\u4f5c\u5730\u5740' from emp;<\/code><\/pre>\n
    2.6.3 \u6761\u4ef6\u67e5\u8be2<\/h3>\n
    1\uff09\u8bed\u6cd5<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d WHERE \u6761\u4ef6\u5217\u8868<\/code><\/pre>\n
    2\uff09\u6761\u4ef6<\/p>\n
    \u6761\u4ef6\u6bd4\u8f83\u7b26\uff1a<\/p>\n
    \"image-20231027221151270\"<\/div><\/p>\n
    \u903b\u8f91\u8fd0\u7b97\u7b26\uff1a<\/p>\n
    \"image-20231027221221528\"<\/div><\/p>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5e74\u9f84\u4e3a88\u7684\u5458\u5de5<\/p>\n
    select * from emp where age=88;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5e74\u9f84\u5c0f\u4e8e20\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where age<20;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u6709\u8eab\u4efd\u8bc1\u53f7\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where idcard is not null;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5e74\u9f84\u4e0d\u7b49\u4e8e88\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where age != 88;\n\nselect * from emp where age <> 88;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5e74\u9f84\u572815\uff08\u5305\u542b\uff09\u523020\uff08\u5305\u542b\uff09\u4e4b\u95f4\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where age >=15 && age <=20;\n\nselect * from emp where age >=15 and age <=20;\n\nselect * from emp where age from 15 to 20;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u6027\u522b\u4e3a\u5973\u4e14\u5e74\u9f84\u5c0f\u4e8e25\u5c81\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where gender = '\u5973' and age < 25;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5e74\u9f84\u4e3a18\u621620\u621640\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where age = 18 or age = 20 or age = 40;\n\nselect * from emp where age in (18,20,40);<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u59d3\u540d\u4e3a\u4e24\u4e2a\u5b57\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where name like '__';<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u8eab\u4efd\u8bc1\u53f7\u6700\u540e\u4e00\u4f4d\u662fX\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where idacard like '%X';<\/code><\/pre>\n
    2.6.4 \u805a\u5408\u51fd\u6570<\/h3>\n
    1\uff09\u4ecb\u7ecd<\/h4>\n
    \u5c06\u4e00\u5217\u6570\u636e\u4f5c\u4e3a\u4e00\u4e2a\u6574\u4f53\uff0c\u8fdb\u884c\u7eb5\u5411\u8ba1\u7b97<\/p>\n
    2\uff09\u5e38\u89c1\u7684\u805a\u5408\u51fd\u6570<\/h4>\n
    \"image-20231030080635271\"<\/div><\/p>\n
    3\uff09\u8bed\u6cd5<\/h4>\n
    SELECT \u805a\u5408\u51fd\u6570\uff08\u5b57\u6bb5\u5217\u8868\uff09 FROM \u8868\u540d\uff1b<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7edf\u8ba1\u8be5\u4f01\u4e1a\u5458\u5de5\u6570\u91cf<\/p>\n
    select count\uff08*\uff09 from emp; --\u7edf\u8ba1\u7684\u662f\u603b\u8bb0\u5f55\u6570\n\nselect count(idcard) from emp; --\u7edf\u8ba1\u7684\u662fidcard\u5b57\u6bb5\u4e0d\u4e3anull\u7684\u8bb0\u5f55\u6570<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7edf\u8ba1\u8be5\u4f01\u4e1a\u5458\u5de5\u7684\u5e73\u5747\u5e74\u9f84<\/p>\n
    select avg(age) from emp;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7edf\u8ba1\u8be5\u4f01\u4e1a\u5458\u5de5\u7684\u6700\u5927\u5e74\u9f84<\/p>\n
    select max(age) from emp;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7edf\u8ba1\u8be5\u4f01\u4e1a\u5458\u5de5\u7684\u6700\u5c0f\u5e74\u9f84<\/p>\n
    select max(age) from emp;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7edf\u8ba1\u897f\u5b89\u5730\u533a\u5458\u5de5\u7684\u5e74\u9f84\u4e4b\u548c<\/p>\n
    select sum(age) from emp where workaddress = '\u897f\u5b89'\uff1b<\/p>\n
    2.6.5 \u5206\u7ec4\u67e5\u8be2<\/h3>\n
    1\uff09\u8bed\u6cd5<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d [WHERE \u6761\u4ef6] GROUP BY \u5206\u7ec4\u5b57\u6bb5\u540d [HAVING \u5206\u7ec4\u540e\u8fc7\u6ee4\u6761\u4ef6]\uff1b<\/p>\n
    2\uff09 where \u548c having \u7684\u533a\u522b<\/p>\n
    \u6267\u884c\u65f6\u673a\u4e0d\u540c: where\u662f\u5206\u7ec4\u4e4b\u524d\u8fdb\u884c\u8fc7\u6ee4\uff0c\u4e0d\u6ee1\u8db3where\u6761\u4ef6\uff0c\u4e0d\u53c2\u4e0e\u5206\u7ec4; \u800chaving\u662f\u5206\u7ec4\u4e4b\u540e\u5bf9\u7ed3\u679c\u8fdb\u884c\u8fc7\u6ee4
    \n\u5224\u65ad\u6761\u4ef6\u4e0d\u540c: where\u4e0d\u80fd\u5bf9\u805a\u5408\u51fd\u6570\u8fdb\u884c\u5224\u65ad\uff0c\u800chaving\u53ef\u4ee5<\/p>\n
    \u6ce8\u610f\u4e8b\u9879\uff1a<\/p>\n
    \u5206\u7ec4\u4e4b\u540e\uff0c\u67e5\u8be2\u7684\u5b57\u6bb5\u4e00\u822c\u4e3a\u805a\u5408\u5b57\u6bb5\u548c\u5206\u7ec4\u5b57\u6bb5\uff0c\u67e5\u8be2\u5176\u4ed6\u5b57\u6bb5\u65e0\u610f\u4e49\u3002<\/p>\n
    \u6267\u884c\u987a\u5e8f\uff1awhere > \u805a\u5408\u51fd\u6570 > having<\/p>\n
    \u652f\u6301\u591a\u5b57\u6bb5\u5206\u7ec4\uff1a\u8bed\u6cd5\u4e3a group by column A, column B<\/p>\n
    \u4e3e\u4f8b\uff1a\u6839\u636e\u6027\u522b\u5206\u7ec4\uff0c\u7edf\u8ba1\u7537\u6027\u5458\u5de5\u548c\u5973\u6027\u5458\u5de5\u7684\u6570\u91cf<\/p>\n
    select gender, count(*) from emp group by gender;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u6839\u636e\u6027\u522b\u5206\u7ec4 , \u7edf\u8ba1\u7537\u6027\u5458\u5de5\u548c\u5973\u6027\u5458\u5de5\u7684\u5e73\u5747\u5e74\u9f84<\/p>\n
    select gender,avg(age) from emp group by gender;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5e74\u9f84\u5c0f\u4e8e45\u7684\u5458\u5de5 , \u5e76\u6839\u636e\u5de5\u4f5c\u5730\u5740\u5206\u7ec4 , \u83b7\u53d6\u5458\u5de5\u6570\u91cf\u5927\u4e8e\u7b49\u4e8e3\u7684\u5de5\u4f5c\u5730\u5740<\/p>\n
    select workaddress,count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7edf\u8ba1\u5404\u4e2a\u5de5\u4f5c\u5730\u5740\u4e0a\u73ed\u7684\u7537\u6027\u53ca\u5973\u6027\u5458\u5de5\u7684\u6570\u91cf<\/p>\n
    select workaddress,gender,count(*) '\u6570\u91cf' from emp group by gender,workaddress;<\/code><\/pre>\n
    2.6.6 \u6392\u5e8f\u67e5\u8be2<\/h3>\n
    1\uff09\u8bed\u6cd5<\/h4>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d ORDER BY \u5b57\u6bb51 \u6392\u5e8f\u65b9\u5f0f1,\u5b57\u6bb52 \u6392\u5e8f\u65b9\u5f0f2;<\/code><\/pre>\n
    2\uff09\u6392\u5e8f\u65b9\u5f0f<\/h4>\n
    ASC\uff1a\u5347\u5e8f\uff08\u9ed8\u8ba4\u503c\uff09<\/p>\n
    DESC\uff1a\u964d\u5e8f<\/p>\n
    \u6ce8\u610f\u4e8b\u9879\uff1a<\/p>\n
    \u2022 \u5982\u679c\u662f\u5347\u5e8f, \u53ef\u4ee5\u4e0d\u6307\u5b9a\u6392\u5e8f\u65b9\u5f0fASC ;<\/p>\n
    \u2022 \u5982\u679c\u662f\u591a\u5b57\u6bb5\u6392\u5e8f\uff0c\u5f53\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u503c\u76f8\u540c\u65f6\uff0c\u624d\u4f1a\u6839\u636e\u7b2c\u4e8c\u4e2a\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f ;<\/p>\n
    \u4e3e\u4f8b\uff1a\u6839\u636e\u5e74\u9f84\u5bf9\u516c\u53f8\u7684\u5458\u5de5\u8fdb\u884c\u5347\u5e8f\u6392\u5e8f<\/p>\n
    select * from emp order by age asc;\n\nselect * from emp order by age;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u6839\u636e\u5165\u804c\u65f6\u95f4, \u5bf9\u5458\u5de5\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f<\/p>\n
    select * from emp order by entrydate desc;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u6839\u636e\u5e74\u9f84\u5bf9\u516c\u53f8\u7684\u5458\u5de5\u8fdb\u884c\u5347\u5e8f\u6392\u5e8f , \u5e74\u9f84\u76f8\u540c , \u518d\u6309\u7167\u5165\u804c\u65f6\u95f4\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f<\/p>\n
    select * from emp order by age asc , entrydate desc; <\/code><\/pre>\n
    2.6.7 \u5206\u9875\u67e5\u8be2<\/h3>\n
    \u5206\u9875\u64cd\u4f5c\u5728\u4e1a\u52a1\u7cfb\u7edf\u5f00\u53d1\u65f6\uff0c\u4e5f\u662f\u975e\u5e38\u5e38\u89c1\u7684\u4e00\u4e2a\u529f\u80fd\uff0c\u6211\u4eec\u5728\u7f51\u7ad9\u4e2d\u770b\u5230\u7684\u5404\u79cd\u5404\u6837\u7684\u5206\u9875\u6761\uff0c\u540e\u53f0\u90fd\u9700\u8981\u501f\u52a9\u4e8e\u6570\u636e\u5e93\u7684\u5206\u9875\u64cd\u4f5c\u3002<\/p>\n
    1\uff09\u8bed\u6cd5<\/h4>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868\u540d LIMIT \u8d77\u59cb\u7d22\u5f15, \u67e5\u8be2\u8bb0\u5f55\u6570 ; <\/code><\/pre>\n
    \u6ce8\u610f\u4e8b\u9879:<\/p>\n
    \u2022 \u8d77\u59cb\u7d22\u5f15\u4ece0\u5f00\u59cb\uff0c\u8d77\u59cb\u7d22\u5f15 = \uff08\u67e5\u8be2\u9875\u7801 - 1\uff09* \u6bcf\u9875\u663e\u793a\u8bb0\u5f55\u6570\u3002<\/p>\n
    \u2022 \u5206\u9875\u67e5\u8be2\u662f\u6570\u636e\u5e93\u7684\u65b9\u8a00\uff0c\u4e0d\u540c\u7684\u6570\u636e\u5e93\u6709\u4e0d\u540c\u7684\u5b9e\u73b0\uff0cMySQL\u4e2d\u662fLIMIT\u3002<\/p>\n
    \u2022 \u5982\u679c\u67e5\u8be2\u7684\u662f\u7b2c\u4e00\u9875\u6570\u636e\uff0c\u8d77\u59cb\u7d22\u5f15\u53ef\u4ee5\u7701\u7565\uff0c\u76f4\u63a5\u7b80\u5199\u4e3a limit 10\u3002<\/p>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u7b2c1\u9875\u5458\u5de5\u6570\u636e, \u6bcf\u9875\u5c55\u793a10\u6761\u8bb0\u5f55<\/p>\n
    select * from emp limit 0,10;\n\nselect * from emp limit 10;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u7b2c2\u9875\u5458\u5de5\u6570\u636e, \u6bcf\u9875\u5c55\u793a10\u6761\u8bb0\u5f55 --------> (\u9875\u7801-1)*\u9875\u5c55\u793a\u8bb0\u5f55\u6570<\/p>\n
    select * from emp limit 10,10; <\/code><\/pre>\n
    2.6.9 \u6267\u884c\u987a\u5e8f<\/h3>\n
    \"image-20231030083329481\"<\/div><\/p>\n
    2.7 DCL<\/h2>\n
    DCL\u82f1\u6587\u5168\u79f0\u662fData Control Language<\/strong>(\u6570\u636e\u63a7\u5236\u8bed\u8a00)\uff0c\u7528\u6765\u7ba1\u7406\u6570\u636e\u5e93\u7528\u6237\u3001\u63a7\u5236\u6570\u636e\u5e93\u7684\u8bbf\u95ee\u6743\u9650\u3002<\/p>\n
    2.7.1 \u7ba1\u7406\u7528\u6237<\/h3>\n
    1\uff09\u67e5\u8be2\u7528\u6237<\/h4>\n
    select * from mysql.user;<\/code><\/pre>\n
    2\uff09\u521b\u5efa\u7528\u6237<\/h4>\n
    CREATE USER '\u7528\u6237\u540d'@'\u4e3b\u673a\u540d' IDENTIFIED BY '\u5bc6\u7801';<\/code><\/pre>\n
    3\uff09\u4fee\u6539\u7528\u6237\u5bc6\u7801<\/h4>\n
    ALTER USER '\u7528\u6237\u540d'@'\u4e3b\u673a\u540d' IDENTIFIED WITH mysql_native_password BY '\u65b0\u5bc6\u7801' ; <\/code><\/pre>\n
    4\uff09\u5220\u9664\u7528\u6237<\/h4>\n
    DROP USER '\u7528\u6237\u540d'@'\u4e3b\u673a\u540d' ; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u521b\u5efa\u7528\u6237itcast, \u53ea\u80fd\u591f\u5728\u5f53\u524d\u4e3b\u673alocalhost\u8bbf\u95ee, \u5bc6\u7801123456;<\/p>\n
    create user 'itcast'@'localhost' identified by '123456'; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u521b\u5efa\u7528\u6237heima, \u53ef\u4ee5\u5728\u4efb\u610f\u4e3b\u673a\u8bbf\u95ee\u8be5\u6570\u636e\u5e93, \u5bc6\u7801123456;<\/p>\n
    create user 'heima'@'%' identified by '123456'; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u4fee\u6539\u7528\u6237heima\u7684\u8bbf\u95ee\u5bc6\u7801\u4e3a1234;<\/p>\n
    alter user 'heima'@'%' identified with mysql_native_password by '1234'; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u5220\u9664 itcast@localhost \u7528\u6237<\/p>\n
    drop user 'itcast'@'localhost'; <\/code><\/pre>\n
    2.7.2 \u6743\u9650\u63a7\u5236<\/h3>\n
    \u5e38\u7528\u7684\u6743\u9650\u5982\u4e0b<\/p>\n
    \"image-20231030084034624\"<\/div><\/p>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2\u6743\u9650<\/p>\n
    SHOW GRANTS FOR '\u7528\u6237\u540d'@'\u4e3b\u673a\u540d' ; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u6388\u4e88\u6743\u9650<\/p>\n
    GRANT \u6743\u9650\u5217\u8868 ON \u6570\u636e\u5e93\u540d.\u8868\u540d TO '\u7528\u6237\u540d'@'\u4e3b\u673a\u540d'; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u64a4\u9500\u6743\u9650<\/p>\n
    REVOKE \u6743\u9650\u5217\u8868 ON \u6570\u636e\u5e93\u540d.\u8868\u540d FROM '\u7528\u6237\u540d'@'\u4e3b\u673a\u540d'; <\/code><\/pre>\n
    \u6ce8\u610f\u4e8b\u9879\uff1a<\/p>\n
    \u2022 \u591a\u4e2a\u6743\u9650\u4e4b\u95f4\uff0c\u4f7f\u7528\u9017\u53f7\u5206\u9694<\/p>\n
    \u2022 \u6388\u6743\u65f6\uff0c \u6570\u636e\u5e93\u540d\u548c\u8868\u540d\u53ef\u4ee5\u4f7f\u7528 * \u8fdb\u884c\u901a\u914d\uff0c\u4ee3\u8868\u6240\u6709\u3002<\/p>\n
    \u4e3e\u4f8b\uff1a\u67e5\u8be2 'heima'@'%' \u7528\u6237\u7684\u6743\u9650<\/p>\n
    show grants for 'heima'@'%'; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u6388\u4e88 'heima'@'%' \u7528\u6237itcast\u6570\u636e\u5e93\u6240\u6709\u8868\u7684\u6240\u6709\u64cd\u4f5c\u6743\u9650<\/p>\n
    grant all on itcast.* to 'heima'@'%'; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u64a4\u9500 'heima'@'%' \u7528\u6237\u7684itcast\u6570\u636e\u5e93\u7684\u6240\u6709\u6743\u9650<\/p>\n
    revoke all on itcast.* from 'heima'@'%'; <\/code><\/pre>\n
    3.\u51fd\u6570<\/h1>\n
    MySQL\u4e2d\u7684\u51fd\u6570\u4e3b\u8981\u5206\u4e3a\u4ee5\u4e0b\u56db\u7c7b\uff1a \u5b57\u7b26\u4e32\u51fd\u6570\u3001\u6570\u503c\u51fd\u6570\u3001\u65e5\u671f\u51fd\u6570\u3001\u6d41\u7a0b\u51fd\u6570\u3002<\/p>\n
    3.1 \u5b57\u7b26\u4e32\u51fd\u6570<\/h2>\n
    \"image-20231030085146721\"<\/div><\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    concat : \u5b57\u7b26\u4e32\u62fc\u63a5<\/p>\n
    select concat('Hello' , ' MySQL'); <\/code><\/pre>\n
    lower : \u5168\u90e8\u8f6c\u5c0f\u5199<\/p>\n
    select lower('Hello'); <\/code><\/pre>\n
    upper : \u5168\u90e8\u8f6c\u5927\u5199<\/p>\n
    select upper('Hello');<\/code><\/pre>\n
    lpad : \u5de6\u586b\u5145<\/p>\n
    select lpad('01', 5, '-'); <\/code><\/pre>\n
    rpad : \u53f3\u586b\u5145<\/p>\n
    select rpad('01', 5, '-'); <\/code><\/pre>\n
    trim : \u53bb\u9664\u7a7a\u683c<\/p>\n
    select trim(' Hello MySQL '); <\/code><\/pre>\n
    substring : \u622a\u53d6\u5b50\u5b57\u7b26\u4e32<\/p>\n
    select substring('Hello MySQL',1,5);<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u7531\u4e8e\u4e1a\u52a1\u9700\u6c42\u53d8\u66f4\uff0c\u4f01\u4e1a\u5458\u5de5\u7684\u5de5\u53f7\uff0c\u7edf\u4e00\u4e3a5\u4f4d\u6570\uff0c\u76ee\u524d\u4e0d\u8db35\u4f4d\u6570\u7684\u5168\u90e8\u5728\u524d\u9762\u88650\u3002\u6bd4\u5982\uff1a 1\u53f7\u5458\u5de5\u7684\u5de5\u53f7\u5e94\u8be5\u4e3a00001\u3002<\/p>\n
    update emp set workno = lpad(workno, 5, '0'); <\/code><\/pre>\n
    3.2 \u6570\u503c\u51fd\u6570<\/h2>\n
    \"image-20231030085653427\"<\/div><\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    ceil\uff1a\u5411\u4e0a\u53d6\u6574<\/p>\n
    select ceil(1.1); <\/code><\/pre>\n
    floor\uff1a\u5411\u4e0b\u53d6\u6574<\/p>\n
    select floor(1.9); <\/code><\/pre>\n
    mod\uff1a\u53d6\u6a21<\/p>\n
    select mod(7,4); <\/code><\/pre>\n
    rand\uff1a\u83b7\u53d6\u968f\u673a\u6570<\/p>\n
    select rand(); <\/code><\/pre>\n
    round\uff1a\u56db\u820d\u4e94\u5165<\/p>\n
    select round(2.344,2); <\/code><\/pre>\n
    \u901a\u8fc7\u6570\u636e\u5e93\u7684\u51fd\u6570\uff0c\u751f\u6210\u4e00\u4e2a\u516d\u4f4d\u6570\u7684\u968f\u673a\u9a8c\u8bc1\u7801\u3002<\/p>\n
    select lpad(round(rand()*1000000 , 0), 6, '0'); <\/code><\/pre>\n
    3.3 \u65e5\u671f\u51fd\u6570<\/h2>\n
    \"image-20231030090118696\"<\/div><\/p>\n
    curdate\uff1a\u5f53\u524d\u65e5\u671f<\/p>\n
    select curdate();<\/code><\/pre>\n
    curtime\uff1a\u5f53\u524d\u65f6\u95f4<\/p>\n
    select curtime(); <\/code><\/pre>\n
    now\uff1a\u5f53\u524d\u65e5\u671f\u548c\u65f6\u95f4<\/p>\n
    select now(); <\/code><\/pre>\n
    YEAR , MONTH , DAY\uff1a\u5f53\u524d\u5e74\u3001\u6708\u3001\u65e5<\/p>\n
    select YEAR(now());\n\nselect MONTH(now());\n\nselect DAY(now());<\/code><\/pre>\n
    date_add\uff1a\u589e\u52a0\u6307\u5b9a\u7684\u65f6\u95f4\u95f4\u9694<\/p>\n
    select date_add(now(), INTERVAL 70 YEAR ); <\/code><\/pre>\n
    datediff\uff1a\u83b7\u53d6\u4e24\u4e2a\u65e5\u671f\u76f8\u5dee\u7684\u5929\u6570<\/p>\n
    select datediff('2021-10-01', '2021-12-01'); <\/code><\/pre>\n
    \u67e5\u8be2\u6240\u6709\u5458\u5de5\u7684\u5165\u804c\u5929\u6570\uff0c\u5e76\u6839\u636e\u5165\u804c\u5929\u6570\u5012\u5e8f\u6392\u5e8f<\/p>\n
    select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;<\/code><\/pre>\n
    3.4 \u6d41\u7a0b\u51fd\u6570<\/h2>\n
    \u6d41\u7a0b\u51fd\u6570\u4e5f\u662f\u5f88\u5e38\u7528\u7684\u4e00\u7c7b\u51fd\u6570\uff0c\u53ef\u4ee5\u5728SQL\u8bed\u53e5\u4e2d\u5b9e\u73b0\u6761\u4ef6\u7b5b\u9009\uff0c\u4ece\u800c\u63d0\u9ad8\u8bed\u53e5\u7684\u6548\u7387\u3002<\/p>\n
    \"image-20231030090920273\"<\/div><\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    if<\/p>\n
    select if(false, 'Ok', 'Error'); <\/code><\/pre>\n
    ifnull<\/p>\n
    select ifnull('Ok','Default');\n\nselect ifnull('','Default');\n\nselect ifnull(null,'Default');<\/code><\/pre>\n
    case when then else end<\/p>\n
    \u9700\u6c42: \u67e5\u8be2emp\u8868\u7684\u5458\u5de5\u59d3\u540d\u548c\u5de5\u4f5c\u5730\u5740 (\u5317\u4eac\/\u4e0a\u6d77 ----> \u4e00\u7ebf\u57ce\u5e02 , \u5176\u4ed6 ----> \u4e8c\u7ebf\u57ce\u5e02)<\/p>\n
    select\nname,\n( case workaddress when '\u5317\u4eac' then '\u4e00\u7ebf\u57ce\u5e02' when '\u4e0a\u6d77' then '\u4e00\u7ebf\u57ce\u5e02' else\n'\u4e8c\u7ebf\u57ce\u5e02' end ) as '\u5de5\u4f5c\u5730\u5740'\nfrom emp;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    \u521b\u5efascore\u8868\u683c\uff1a<\/p>\n
    create table score(\nid int comment 'ID',\nname varchar(20) comment '\u59d3\u540d',\n    math int comment '\u6570\u5b66',\n    english int comment '\u82f1\u8bed',\n    chinese int comment '\u8bed\u6587'\n) comment '\u5b66\u5458\u6210\u7ee9\u8868';\ninsert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);<\/code><\/pre>\n
    select\nid,\nname,\n(case when math >= 85 then '\u4f18\u79c0' when math >=60 then '\u53ca\u683c' else '\u4e0d\u53ca\u683c' end )\n'\u6570\u5b66',\n(case when english >= 85 then '\u4f18\u79c0' when english >=60 then '\u53ca\u683c' else '\u4e0d\u53ca\u683c'\nend ) '\u82f1\u8bed',\n(case when chinese >= 85 then '\u4f18\u79c0' when chinese >=60 then '\u53ca\u683c' else '\u4e0d\u53ca\u683c'\nend ) '\u8bed\u6587'\nfrom score;<\/code><\/pre>\n
    4.\u7ea6\u675f<\/h1>\n
    4.1 \u6982\u8ff0<\/h2>\n
    \u6982\u5ff5\uff1a\u7ea6\u675f\u662f\u4f5c\u7528\u4e8e\u8868\u4e2d\u5b57\u6bb5\u4e0a\u7684\u89c4\u5219\uff0c\u7528\u4e8e\u9650\u5236\u5b58\u50a8\u5728\u8868\u4e2d\u7684\u6570\u636e\u3002<\/p>\n
    \u76ee\u7684\uff1a\u4fdd\u8bc1\u6570\u636e\u5e93\u4e2d\u6570\u636e\u7684\u6b63\u786e\u3001\u6709\u6548\u6027\u548c\u5b8c\u6574\u6027\u3002<\/p>\n
    \u5206\u7c7b\uff1a<\/p>\n
    \"image-20231030091617902\"<\/div><\/p>\n
    4.2 \u7ea6\u675f\u6f14\u793a<\/h2>\n
    \u4e3e\u4f8b\uff1a\u6839\u636e\u9700\u6c42\u521b\u5efa<\/p>\n
    \"image-20231030093031279\"<\/div><\/p>\n
    CREATE TABLE tb_user(\nid int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID\u552f\u4e00\u6807\u8bc6',\nname varchar(10) NOT NULL UNIQUE COMMENT '\u59d3\u540d' ,\nage int check (age > 0 && age <= 120) COMMENT '\u5e74\u9f84' ,\nstatus char(1) default '1' COMMENT '\u72b6\u6001',\ngender char(1) COMMENT '\u6027\u522b'\n);<\/code><\/pre>\n
    4.3 \u5916\u952e\u7ea6\u675f<\/h2>\n
    4.3.1 \u4ecb\u7ecd<\/h3>\n
    \u5916\u952e\uff1a\u7528\u6765\u8ba9\u4e24\u5f20\u8868\u7684\u6570\u636e\u4e4b\u95f4\u5efa\u7acb\u8fde\u63a5\uff0c\u4ece\u800c\u4fdd\u8bc1\u6570\u636e\u7684\u4e00\u81f4\u6027\u548c\u5b8c\u6574\u6027\u3002<\/p>\n
    4.3.2 \u8bed\u6cd5<\/h3>\n
    1\uff09\u6dfb\u52a0\u5916\u952e<\/h4>\n
    CREATE TABLE \u8868\u540d(\n\u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b,\n...\n[CONSTRAINT] [\u5916\u952e\u540d\u79f0] FOREIGN KEY (\u5916\u952e\u5b57\u6bb5\u540d) REFERENCES \u4e3b\u8868 (\u4e3b\u8868\u5217\u540d)\n);<\/code><\/pre>\n
    ALTER TABLE \u8868\u540d ADD CONSTRAINT \u5916\u952e\u540d\u79f0 FOREIGN KEY (\u5916\u952e\u5b57\u6bb5\u540d)\nREFERENCES \u4e3b\u8868 (\u4e3b\u8868\u5217\u540d) ;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a\u4e3aemp\u8868\u7684dept_id\u5b57\u6bb5\u6dfb\u52a0\u5916\u952e\u7ea6\u675f,\u5173\u8054dept\u8868\u7684\u4e3b\u952eid\u3002<\/p>\n
    alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);<\/code><\/pre>\n
    2\uff09\u5220\u9664\u5916\u952e<\/h4>\n
    ALTER TABLE \u8868\u540d DROP FOREIGN KEY \u5916\u952e\u540d\u79f0; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    \u5220\u9664emp\u8868\u7684\u5916\u952efk_emp_dept_id<\/p>\n
    alter table emp drop foreign key fk_emp_dept_id; <\/code><\/pre>\n
    4.3.3 \u5220\u9664\/\u66f4\u65b0\u884c\u4e3a<\/h3>\n
    \u6dfb\u52a0\u4e86\u5916\u952e\u4e4b\u540e\uff0c\u518d\u5220\u9664\u7236\u8868\u6570\u636e\u65f6\u4ea7\u751f\u7684\u7ea6\u675f\u884c\u4e3a\uff0c\u6211\u4eec\u5c31\u79f0\u4e3a\u5220\u9664\/\u66f4\u65b0\u884c\u4e3a\u3002\u5177\u4f53\u7684\u5220\u9664\/\u66f4\u65b0\u884c\u4e3a\u6709\u4ee5\u4e0b\u51e0\u79cd:<\/p>\n
    \"image-20231030164639171\"<\/div><\/p>\n
    \u8bed\u6cd5\u4e3a\uff1a<\/p>\n
    ALTER TABLE \u8868\u540d ADD CONSTRAINT \u5916\u952e\u540d\u79f0 FOREIGN KEY (\u5916\u952e\u5b57\u6bb5) REFERENCES \u4e3b\u8868\u540d (\u4e3b\u8868\u5b57\u6bb5\u540d) ON UPDATE CASCADE ON DELETE CASCADE;<\/code><\/pre>\n
    1\uff09CASCADE<\/p>\n
    alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;<\/code><\/pre>\n
    2\uff09SET NULL<\/p>\n
    alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;<\/code><\/pre>\n
    5. \u591a\u8868\u67e5\u8be2<\/h1>\n
    5.1 \u591a\u8868\u5173\u7cfb<\/h2>\n
    \u4e09\u79cd\u5173\u7cfb\uff1a\u4e00\u5bf9\u591a\uff08\u591a\u5bf9\u4e00\uff09\uff0c\u591a\u5bf9\u591a\uff0c\u4e00\u5bf9\u4e00<\/p>\n
    5.1.1 \u4e00\u5bf9\u591a<\/h3>\n
    \u6848\u4f8b\uff1a\u90e8\u95e8\u548c\u5458\u5de5\u4e4b\u95f4<\/p>\n
    \u5173\u7cfb: \u4e00\u4e2a\u90e8\u95e8\u5bf9\u5e94\u591a\u4e2a\u5458\u5de5\uff0c\u4e00\u4e2a\u5458\u5de5\u5bf9\u5e94\u4e00\u4e2a\u90e8\u95e8<\/p>\n
    \u5b9e\u73b0: \u5728\u591a\u7684\u4e00\u65b9\u5efa\u7acb\u5916\u952e\uff0c\u6307\u5411\u4e00\u7684\u4e00\u65b9\u7684\u4e3b\u952e<\/p>\n
    5.1.2 \u591a\u5bf9\u591a<\/h3>\n
    \u6848\u4f8b: \u5b66\u751f \u4e0e \u8bfe\u7a0b\u7684\u5173\u7cfb<\/p>\n
    \u5173\u7cfb: \u4e00\u4e2a\u5b66\u751f\u53ef\u4ee5\u9009\u4fee\u591a\u95e8\u8bfe\u7a0b\uff0c\u4e00\u95e8\u8bfe\u7a0b\u4e5f\u53ef\u4ee5\u4f9b\u591a\u4e2a\u5b66\u751f\u9009\u62e9<\/p>\n
    \u5b9e\u73b0: \u5efa\u7acb\u7b2c\u4e09\u5f20\u4e2d\u95f4\u8868\uff0c\u4e2d\u95f4\u8868\u81f3\u5c11\u5305\u542b\u4e24\u4e2a\u5916\u952e\uff0c\u5206\u522b\u5173\u8054\u4e24\u65b9\u4e3b\u952e<\/p>\n
    \u5b9e\u73b0\uff1a<\/p>\n
    use mydb2;\ncreate table student(\n                        id int auto_increment primary key comment '\u4e3b\u952eID',\n                        name varchar(10) comment '\u59d3\u540d',\n                        no varchar(10) comment '\u5b66\u53f7'\n) comment '\u5b66\u751f\u8868';\ninsert into student values (null, '\u9edb\u7eee\u4e1d', '2000100101'),(null, '\u8c22\u900a', '2000100102'),(null, '\u6bb7\u5929\u6b63', '2000100103'),(null, '\u97e6\u4e00\u7b11', '2000100104');\ncreate table course(\n                       id int auto_increment primary key comment '\u4e3b\u952eID',\n                       name varchar(10) comment '\u8bfe\u7a0b\u540d\u79f0'\n) comment '\u8bfe\u7a0b\u8868';\ninsert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,\n                          (null, 'Hadoop');\ncreate table student_course(\n    id int auto_increment comment '\u4e3b\u952e' primary key,\n    studentid int not null comment '\u5b66\u751fID',\n    courseid int not null comment '\u8bfe\u7a0bID',\n    constraint fk_courseid foreign key (courseid) references course (id),\n    constraint fk_studentid foreign key (studentid) references student (id)\n    )comment '\u5b66\u751f\u8bfe\u7a0b\u4e2d\u95f4\u8868';\ninsert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),\n                                  (null,2,3),(null,3,4)<\/code><\/pre>\n
    5.1.3 \u4e00\u5bf9\u4e00<\/h3>\n
    \u6848\u4f8b: \u7528\u6237 \u4e0e \u7528\u6237\u8be6\u60c5\u7684\u5173\u7cfb<\/p>\n
    \u5173\u7cfb: \u4e00\u5bf9\u4e00\u5173\u7cfb\uff0c\u591a\u7528\u4e8e\u5355\u8868\u62c6\u5206\uff0c\u5c06\u4e00\u5f20\u8868\u7684\u57fa\u7840\u5b57\u6bb5\u653e\u5728\u4e00\u5f20\u8868\u4e2d\uff0c\u5176\u4ed6\u8be6\u60c5\u5b57\u6bb5\u653e\u5728\u53e6\u4e00\u5f20\u8868\u4e2d\uff0c\u4ee5\u63d0\u5347\u64cd\u4f5c\u6548\u7387<\/p>\n
    \u5b9e\u73b0: \u5728\u4efb\u610f\u4e00\u65b9\u52a0\u5165\u5916\u952e\uff0c\u5173\u8054\u53e6\u5916\u4e00\u65b9\u7684\u4e3b\u952e\uff0c\u5e76\u4e14\u8bbe\u7f6e\u5916\u952e\u4e3a\u552f\u4e00\u7684(UNIQUE)<\/p>\n
    create table tb_user(\n                        id int auto_increment primary key comment '\u4e3b\u952eID',\n                        name varchar(10) comment '\u59d3\u540d',\n                        age int comment '\u5e74\u9f84',\n                        gender char(1) comment '1: \u7537 , 2: \u5973',\n                        phone char(11) comment '\u624b\u673a\u53f7'\n) comment '\u7528\u6237\u57fa\u672c\u4fe1\u606f\u8868';\ncreate table tb_user_edu(\n                            id int auto_increment primary key comment '\u4e3b\u952eID',\n                            degree varchar(20) comment '\u5b66\u5386',\n                            major varchar(50) comment '\u4e13\u4e1a',\n                            primaryschool varchar(50) comment '\u5c0f\u5b66',\n                            middleschool varchar(50) comment '\u4e2d\u5b66',\nuniversity varchar(50) comment '\u5927\u5b66',\nuserid int unique comment '\u7528\u6237ID',\nconstraint fk_userid foreign key (userid) references tb_user(id)\n) comment '\u7528\u6237\u6559\u80b2\u4fe1\u606f\u8868';\ninsert into tb_user(id, name, age, gender, phone) values\n                                                      (null,'\u9ec4\u6e24',45,'1','18800001111'),\n                                                      (null,'\u51b0\u51b0',35,'2','18800002222'),\n                                                      (null,'\u7801\u4e91',55,'1','18800008888'),\n                                                      (null,'\u674e\u5f66\u5b8f',50,'1','18800009999');\ninsert into tb_user_edu(id, degree, major, primaryschool, middleschool,\n                        university, userid) values\n                                                (null,'\u672c\u79d1','\u821e\u8e48','\u9759\u5b89\u533a\u7b2c\u4e00\u5c0f\u5b66','\u9759\u5b89\u533a\u7b2c\u4e00\u4e2d\u5b66','\u5317\u4eac\u821e\u8e48\u5b66\u9662',1),\n                                                (null,'\u7855\u58eb','\u8868\u6f14','\u671d\u9633\u533a\u7b2c\u4e00\u5c0f\u5b66','\u671d\u9633\u533a\u7b2c\u4e00\u4e2d\u5b66','\u5317\u4eac\u7535\u5f71\u5b66\u9662',2),\n                                                (null,'\u672c\u79d1','\u82f1\u8bed','\u676d\u5dde\u5e02\u7b2c\u4e00\u5c0f\u5b66','\u676d\u5dde\u5e02\u7b2c\u4e00\u4e2d\u5b66','\u676d\u5dde\u5e08\u8303\u5927\u5b66',3),\n                                                (null,'\u672c\u79d1','\u5e94\u7528\u6570\u5b66','\u9633\u6cc9\u7b2c\u4e00\u5c0f\u5b66','\u9633\u6cc9\u533a\u7b2c\u4e00\u4e2d\u5b66','\u6e05\u534e\u5927\u5b66',4);<\/code><\/pre>\n
    5.2 \u591a\u8868\u67e5\u8be2\u6982\u8ff0<\/h2>\n
    5.2.1 \u6570\u636e\u51c6\u5907<\/h3>\n
    use mydb2;\n-- \u521b\u5efadept\u8868\uff0c\u5e76\u63d2\u5165\u6570\u636e\ncreate table dept(\n                     id int auto_increment comment 'ID' primary key,\n                     name varchar(50) not null comment '\u90e8\u95e8\u540d\u79f0'\n)comment '\u90e8\u95e8\u8868';\nINSERT INTO dept (id, name) VALUES (1, '\u7814\u53d1\u90e8'), (2, '\u5e02\u573a\u90e8'),(3, '\u8d22\u52a1\u90e8'), (4,\n                                                                                '\u9500\u552e\u90e8'), (5, '\u603b\u7ecf\u529e'), (6, '\u4eba\u4e8b\u90e8');\n-- \u521b\u5efaemp\u8868\uff0c\u5e76\u63d2\u5165\u6570\u636e\ncreate table emp(\n                    id int auto_increment comment 'ID' primary key,\n    name varchar(50) not null comment '\u59d3\u540d',\n    age int comment '\u5e74\u9f84',\n    job varchar(20) comment '\u804c\u4f4d',\n    salary int comment '\u85aa\u8d44',\n    entrydate date comment '\u5165\u804c\u65f6\u95f4',\n    managerid int comment '\u76f4\u5c5e\u9886\u5bfcID',\n    dept_id int comment '\u90e8\u95e8ID'\n    )comment '\u5458\u5de5\u8868';\n-- \u6dfb\u52a0\u5916\u952e\nalter table emp add constraint fk_emp_dept_id foreign key (dept_id) references\n    dept(id);\nINSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)\nVALUES\n    (1, '\u91d1\u5eb8', 66, '\u603b\u88c1',20000, '2000-01-01', null,5),\n    (2, '\u5f20\u65e0\u5fcc', 20, '\u9879\u76ee\u7ecf\u7406',12500, '2005-12-05', 1,1),\n    (3, '\u6768\u900d', 33, '\u5f00\u53d1', 8400,'2000-11-03', 2,1),\n    (4, '\u97e6\u4e00\u7b11', 48, '\u5f00\u53d1',11000, '2002-02-05', 2,1),\n    (5, '\u5e38\u9047\u6625', 43, '\u5f00\u53d1',10500, '2004-09-07', 3,1),\n    (6, '\u5c0f\u662d', 19, '\u7a0b\u5e8f\u5458\u9f13\u52b1\u5e08',6600, '2004-10-12', 2,1),\n    (7, '\u706d\u7edd', 60, '\u8d22\u52a1\u603b\u76d1',8500, '2002-09-12', 1,3),\n    (8, '\u5468\u82b7\u82e5', 19, '\u4f1a\u8ba1',48000, '2006-06-02', 7,3),\n    (9, '\u4e01\u654f\u541b', 23, '\u51fa\u7eb3',5250, '2009-05-13', 7,3),\n    (10, '\u8d75\u654f', 20, '\u5e02\u573a\u90e8\u603b\u76d1',12500, '2004-10-12', 1,2),\n    (11, '\u9e7f\u6756\u5ba2', 56, '\u804c\u5458',3750, '2006-10-03', 10,2),\n    (12, '\u9e64\u7b14\u7fc1', 19, '\u804c\u5458',3750, '2007-05-09', 10,2),\n    (13, '\u65b9\u4e1c\u767d', 19, '\u804c\u5458',5500, '2009-02-12', 10,2),\n    (14, '\u5f20\u4e09\u4e30', 88, '\u9500\u552e\u603b\u76d1',14000, '2004-10-12', 1,4),\n    (15, '\u4fde\u83b2\u821f', 38, '\u9500\u552e',4600, '2004-10-12', 14,4),\n    (16, '\u5b8b\u8fdc\u6865', 40, '\u9500\u552e',4600, '2004-10-12', 14,4),\n    (17, '\u9648\u53cb\u8c05', 42, null,2000, '2011-10-12', 1,null);<\/code><\/pre>\n
    \u76f4\u63a5\u67e5\u8be2\u4f1a\u6709\u5f88\u591a\u65e0\u6548\u7684\u7b1b\u5361\u5c14\u79ef\uff0c\u7ed9\u591a\u8868\u67e5\u8be2\u52a0\u4e0a\u8fde\u63a5\u67e5\u8be2\u7684\u6761\u4ef6\u5373\u53ef<\/p>\n
    select * from emp , dept where emp.dept_id = dept.id;<\/code><\/pre>\n
    5.2.3 \u5206\u7c7b<\/h3>\n
    \u8fde\u63a5\u67e5\u8be2<\/h4>\n
    \u5185\u8fde\u63a5\uff1a\u76f8\u5f53\u4e8e\u67e5\u8be2A\u3001B\u4ea4\u96c6\u90e8\u5206\u6570\u636e<\/p>\n
    \u5916\u8fde\u63a5\uff1a<\/p>\n
    \u5de6\u5916\u8fde\u63a5\uff1a\u67e5\u8be2\u5de6\u8868\u6240\u6709\u6570\u636e\uff0c\u4ee5\u53ca\u4e24\u5f20\u8868\u4ea4\u96c6\u90e8\u5206\u6570\u636e<\/p>\n
    \u53f3\u5916\u8fde\u63a5\uff1a\u67e5\u8be2\u53f3\u8868\u6240\u6709\u6570\u636e\uff0c\u4ee5\u53ca\u4e24\u5f20\u8868\u4ea4\u96c6\u90e8\u5206\u6570\u636e<\/p>\n
    \u81ea\u8fde\u63a5\uff1a\u5f53\u524d\u8868\u4e0e\u81ea\u8eab\u7684\u8fde\u63a5\u67e5\u8be2\uff0c\u81ea\u8fde\u63a5\u5fc5\u987b\u4f7f\u7528\u8868\u522b\u540d<\/p>\n
    \u5b50\u67e5\u8be2<\/h4>\n
    \"image-20231103205944838\"<\/div><\/p>\n
    5.3 \u5185\u8fde\u63a5<\/h2>\n
    \u5185\u8fde\u63a5\u67e5\u8be2\u7684\u662f\u4e24\u5f20\u8868\u4ea4\u96c6\u90e8\u5206\u7684\u6570\u636e\u3002(\u4e5f\u5c31\u662f\u7eff\u8272\u90e8\u5206\u7684\u6570\u636e)<\/p>\n
    \u5185\u8fde\u63a5\u7684\u8bed\u6cd5\u5206\u4e3a\u4e24\u79cd: \u9690\u5f0f\u5185\u8fde\u63a5\u3001\u663e\u5f0f\u5185\u8fde\u63a5\u3002\u5148\u6765\u5b66\u4e60\u4e00\u4e0b\u5177\u4f53\u7684\u8bed\u6cd5\u7ed3\u6784\u3002<\/p>\n
    1\uff09\u9690\u5f0f\u5185\u8fde\u63a5<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u88681 , \u88682 WHERE \u6761\u4ef6 ... ; <\/code><\/pre>\n
    2\uff09\u663e\u5f0f\u5185\u8fde\u63a5<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u88681 [ INNER ] JOIN \u88682 ON \u8fde\u63a5\u6761\u4ef6 ... ; <\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    A. \u67e5\u8be2\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u59d3\u540d , \u53ca\u5173\u8054\u7684\u90e8\u95e8\u7684\u540d\u79f0 (\u9690\u5f0f\u5185\u8fde\u63a5\u5b9e\u73b0)<\/p>\n
    \u8868\u7ed3\u6784: emp , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;<\/code><\/pre>\n
    \u8868\u7684\u522b\u540d:<\/p>\n
    tablea as \u522b\u540d1 , tableb as \u522b\u540d2 ;\n\ntablea \u522b\u540d1 , tableb \u522b\u540d2 ;<\/code><\/pre>\n
    B. \u67e5\u8be2\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u59d3\u540d , \u53ca\u5173\u8054\u7684\u90e8\u95e8\u7684\u540d\u79f0 (\u663e\u5f0f\u5185\u8fde\u63a5\u5b9e\u73b0) --- INNER JOIN ...<\/p>\n
    ON ...<\/p>\n
    \u8868\u7ed3\u6784: emp , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;\n\n-- \u4e3a\u6bcf\u4e00\u5f20\u8868\u8d77\u522b\u540d,\u7b80\u5316SQL\u7f16\u5199\n\nselect e.name, d.name from emp e join dept d on e.dept_id = d.id;<\/code><\/pre>\n
    5.4 \u5916\u8fde\u63a5<\/h2>\n
    \u5916\u8fde\u63a5\u5206\u4e3a\u4e24\u79cd\uff0c\u5206\u522b\u662f\uff1a\u5de6\u5916\u8fde\u63a5 \u548c \u53f3\u5916\u8fde\u63a5\u3002\u5177\u4f53\u7684\u8bed\u6cd5\u7ed3\u6784\u4e3a\uff1a<\/p>\n
    1\uff09\u5de6\u5916\u8fde\u63a5<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u88681 LEFT [ OUTER ] JOIN \u88682 ON \u6761\u4ef6 ... ; <\/code><\/pre>\n
    \u5de6\u5916\u8fde\u63a5\u76f8\u5f53\u4e8e\u67e5\u8be2\u88681(\u5de6\u8868)\u7684\u6240\u6709\u6570\u636e\uff0c\u5f53\u7136\u4e5f\u5305\u542b\u88681\u548c\u88682\u4ea4\u96c6\u90e8\u5206\u7684\u6570\u636e<\/p>\n
    2\uff09\u53f3\u5916\u8fde\u63a5<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u88681 RIGHT [ OUTER ] JOIN \u88682 ON \u6761\u4ef6 ... ; <\/code><\/pre>\n
    \u53f3\u5916\u8fde\u63a5\u76f8\u5f53\u4e8e\u67e5\u8be2\u88682(\u53f3\u8868)\u7684\u6240\u6709\u6570\u636e\uff0c\u5f53\u7136\u4e5f\u5305\u542b\u88681\u548c\u88682\u4ea4\u96c6\u90e8\u5206\u7684\u6570\u636e<\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    A.  \u67e5\u8be2emp\u8868\u7684\u6240\u6709\u6570\u636e, \u548c\u5bf9\u5e94\u7684\u90e8\u95e8\u4fe1\u606f<\/p>\n
    \u8868\u7ed3\u6784: emp, dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;\nselect e.*, d.name from emp e left join dept d on e.dept_id = d.id;<\/code><\/pre>\n
    B.  \u67e5\u8be2dept\u8868\u7684\u6240\u6709\u6570\u636e, \u548c\u5bf9\u5e94\u7684\u5458\u5de5\u4fe1\u606f(\u53f3\u5916\u8fde\u63a5)<\/p>\n
    \u8868\u7ed3\u6784: emp, dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;\nselect d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;<\/code><\/pre>\n
    5.5 \u81ea\u8fde\u63a5<\/h2>\n
    5.5.1 \u81ea\u8fde\u63a5\u67e5\u8be2<\/h3>\n
    \u81ea\u8fde\u63a5\u67e5\u8be2\uff0c\u987e\u540d\u601d\u4e49\uff0c\u5c31\u662f\u81ea\u5df1\u8fde\u63a5\u81ea\u5df1\uff0c\u4e5f\u5c31\u662f\u628a\u4e00\u5f20\u8868\u8fde\u63a5\u67e5\u8be2\u591a\u6b21<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868A \u522b\u540dA JOIN \u8868A \u522b\u540dB ON \u6761\u4ef6 ... ;<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    A. \u67e5\u8be2\u5458\u5de5\u53ca\u5176\u6240\u5c5e\u9886\u5bfc\u7684\u540d\u5b57<\/p>\n
    \u8868\u7ed3\u6784: emp<\/p>\n
    select a.name , b.name from emp a , emp b where a.managerid = b.id; <\/code><\/pre>\n
    B. \u67e5\u8be2\u6240\u6709\u5458\u5de5 emp \u53ca\u5176\u9886\u5bfc\u7684\u540d\u5b57 emp , \u5982\u679c\u5458\u5de5\u6ca1\u6709\u9886\u5bfc, \u4e5f\u9700\u8981\u67e5\u8be2\u51fa\u6765<\/p>\n
    \u8868\u7ed3\u6784: emp a , emp b<\/p>\n
    select a.name '\u5458\u5de5', b.name '\u9886\u5bfc' from emp a left join emp b on a.managerid = b.id;<\/code><\/pre>\n
    5.5.2 \u8054\u5408\u67e5\u8be2<\/h3>\n
    \u5bf9\u4e8eunion\u67e5\u8be2\uff0c\u5c31\u662f\u628a\u591a\u6b21\u67e5\u8be2\u7684\u7ed3\u679c\u5408\u5e76\u8d77\u6765\uff0c\u5f62\u6210\u4e00\u4e2a\u65b0\u7684\u67e5\u8be2\u7ed3\u679c\u96c6<\/p>\n
    SELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868A ...\nUNION [ ALL ]\nSELECT \u5b57\u6bb5\u5217\u8868 FROM \u8868B ....;<\/code><\/pre>\n
    \u5bf9\u4e8e\u8054\u5408\u67e5\u8be2\u7684\u591a\u5f20\u8868\u7684\u5217\u6570\u5fc5\u987b\u4fdd\u6301\u4e00\u81f4\uff0c\u5b57\u6bb5\u7c7b\u578b\u4e5f\u9700\u8981\u4fdd\u6301\u4e00\u81f4\u3002<\/p>\n
    union all \u4f1a\u5c06\u5168\u90e8\u7684\u6570\u636e\u76f4\u63a5\u5408\u5e76\u5728\u4e00\u8d77\uff0cunion \u4f1a\u5bf9\u5408\u5e76\u4e4b\u540e\u7684\u6570\u636e\u53bb\u91cd<\/p>\n
    \u4e3e\u4f8b\uff1a \u5c06\u85aa\u8d44\u4f4e\u4e8e 5000 \u7684\u5458\u5de5 , \u548c \u5e74\u9f84\u5927\u4e8e 50 \u5c81\u7684\u5458\u5de5\u5168\u90e8\u67e5\u8be2\u51fa\u6765<\/p>\n
    select * from emp where salary < 5000\nunion all\nselect * from emp where age > 50;<\/code><\/pre>\n
    union all\u67e5\u8be2\u51fa\u6765\u7684\u7ed3\u679c\uff0c\u4ec5\u4ec5\u8fdb\u884c\u7b80\u5355\u7684\u5408\u5e76\uff0c\u5e76\u672a\u53bb\u91cd<\/p>\n
    union \u8054\u5408\u67e5\u8be2\uff0c\u4f1a\u5bf9\u67e5\u8be2\u51fa\u6765\u7684\u7ed3\u679c\u8fdb\u884c\u53bb\u91cd\u5904\u7406<\/p>\n
    5.6 \u5b50\u67e5\u8be2<\/h1>\n
    5.6.1 \u6982\u8ff0<\/h2>\n
    1\uff09\u6982\u5ff5<\/h4>\n
    SQL\u8bed\u53e5\u4e2d\u5d4c\u5957SELECT\u8bed\u53e5\uff0c\u79f0\u4e3a\u5d4c\u5957\u67e5\u8be2\uff0c\u53c8\u79f0\u5b50\u67e5\u8be2<\/p>\n
    SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );<\/code><\/pre>\n
    \u5b50\u67e5\u8be2\u5916\u90e8\u7684\u8bed\u53e5\u53ef\u4ee5\u662fINSERT \/ UPDATE \/ DELETE \/ SELECT \u7684\u4efb\u4f55\u4e00\u4e2a<\/p>\n
    2\uff09 \u5206\u7c7b<\/h4>\n
    \u6839\u636e\u5b50\u67e5\u8be2\u7ed3\u679c\u4e0d\u540c\uff0c\u5206\u4e3a\uff1a<\/p>\n
    A. \u6807\u91cf\u5b50\u67e5\u8be2\uff08\u5b50\u67e5\u8be2\u7ed3\u679c\u4e3a\u5355\u4e2a\u503c\uff09<\/p>\n
    B. \u5217\u5b50\u67e5\u8be2(\u5b50\u67e5\u8be2\u7ed3\u679c\u4e3a\u4e00\u5217)<\/p>\n
    C. \u884c\u5b50\u67e5\u8be2(\u5b50\u67e5\u8be2\u7ed3\u679c\u4e3a\u4e00\u884c)<\/p>\n
    D. \u8868\u5b50\u67e5\u8be2(\u5b50\u67e5\u8be2\u7ed3\u679c\u4e3a\u591a\u884c\u591a\u5217)<\/p>\n
    \u6839\u636e\u5b50\u67e5\u8be2\u4f4d\u7f6e\uff0c\u5206\u4e3a\uff1a<\/p>\n
    A. WHERE\u4e4b\u540e<\/p>\n
    B. FROM\u4e4b\u540e<\/p>\n
    C. SELECT\u4e4b\u540e<\/p>\n
    5.6.2 \u6807\u91cf\u5b50\u67e5\u8be2<\/h3>\n
    \u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u662f\u5355\u4e2a\u503c\uff08\u6570\u5b57\u3001\u5b57\u7b26\u4e32\u3001\u65e5\u671f\u7b49\uff09\uff0c\u6700\u7b80\u5355\u7684\u5f62\u5f0f\uff0c\u8fd9\u79cd\u5b50\u67e5\u8be2\u79f0\u4e3a\u6807\u91cf\u5b50\u67e5\u8be2\u3002<\/p>\n
    \u5e38\u7528\u7684\u64cd\u4f5c\u7b26\uff1a= <> > >= < <= <\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    A. \u67e5\u8be2 "\u9500\u552e\u90e8" \u7684\u6240\u6709\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u5206\u6210\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2 "\u9500\u552e\u90e8" \u90e8\u95e8ID<\/p>\n
    select id from dept where name = '\u9500\u552e\u90e8'; <\/code><\/pre>\n
    \u6839\u636e "\u9500\u552e\u90e8" \u90e8\u95e8ID, \u67e5\u8be2\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where dept_id = (select id from dept where name = '\u9500\u552e\u90e8'); <\/code><\/pre>\n
    B.  \u67e5\u8be2\u5728 "\u65b9\u4e1c\u767d" \u5165\u804c\u4e4b\u540e\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u5206\u6210\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2 \u65b9\u4e1c\u767d \u7684\u5165\u804c\u65e5\u671f<\/p>\n
    select entrydate from emp where name = '\u65b9\u4e1c\u767d'; <\/code><\/pre>\n
    \u67e5\u8be2\u6307\u5b9a\u5165\u804c\u65e5\u671f\u4e4b\u540e\u5165\u804c\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where entrydate > (select entrydate from emp where name = '\u65b9\u4e1c\u767d');<\/code><\/pre>\n
    5.6.3 \u5217\u5b50\u67e5\u8be2<\/h3>\n
    \u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u662f\u4e00\u5217\uff08\u53ef\u4ee5\u662f\u591a\u884c\uff09\uff0c\u8fd9\u79cd\u5b50\u67e5\u8be2\u79f0\u4e3a\u5217\u5b50\u67e5\u8be2\u3002<\/p>\n
    \u5e38\u7528\u7684\u64cd\u4f5c\u7b26\uff1aIN \u3001NOT IN \u3001 ANY \u3001SOME \u3001 ALL<\/p>\n
    \"image-20231103215655858\"<\/div><\/p>\n
    \u67e5\u8be2 "\u9500\u552e\u90e8" \u548c "\u5e02\u573a\u90e8" \u7684\u6240\u6709\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u5206\u4e3a\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2 "\u9500\u552e\u90e8" \u548c "\u5e02\u573a\u90e8" \u7684\u90e8\u95e8ID<\/p>\n
    select id from dept where name = '\u9500\u552e\u90e8' or name = '\u5e02\u573a\u90e8'; <\/code><\/pre>\n
    \u6839\u636e\u90e8\u95e8ID, \u67e5\u8be2\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where dept_id in (select id from dept where name = '\u9500\u552e\u90e8' or name = '\u5e02\u573a\u90e8');<\/code><\/pre>\n
    \u67e5\u8be2\u6bd4\u8d22\u52a1\u90e8\u6240\u6709\u4eba\u5de5\u8d44\u90fd\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u5206\u4e3a\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2\u6240\u6709 \u8d22\u52a1\u90e8 \u4eba\u5458\u5de5\u8d44<\/p>\n
    select id from dept where name = '\u8d22\u52a1\u90e8';\nselect salary from emp where dept_id = (select id from dept where name = '\u8d22\u52a1\u90e8');<\/code><\/pre>\n
    \u6bd4 \u8d22\u52a1\u90e8 \u6240\u6709\u4eba\u5de5\u8d44\u90fd\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '\u8d22\u52a1\u90e8') );<\/code><\/pre>\n
    C.  \u67e5\u8be2\u6bd4\u7814\u53d1\u90e8\u5176\u4e2d\u4efb\u610f\u4e00\u4eba\u5de5\u8d44\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u5206\u4e3a\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2\u7814\u53d1\u90e8\u6240\u6709\u4eba\u5de5\u8d44<\/p>\n
    select salary from emp where dept_id = (select id from dept where name = '\u7814\u53d1\u90e8');<\/code><\/pre>\n
    \u6bd4\u7814\u53d1\u90e8\u5176\u4e2d\u4efb\u610f\u4e00\u4eba\u5de5\u8d44\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '\u7814\u53d1\u90e8') );<\/code><\/pre>\n
    5.6.4 \u884c\u5b50\u67e5\u8be2<\/h3>\n
    \u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u662f\u4e00\u884c\uff08\u53ef\u4ee5\u662f\u591a\u5217\uff09\uff0c\u8fd9\u79cd\u5b50\u67e5\u8be2\u79f0\u4e3a\u884c\u5b50\u67e5\u8be2\u3002<\/p>\n
    \u5e38\u7528\u7684\u64cd\u4f5c\u7b26\uff1a= \u3001<> \u3001IN \u3001NOT IN<\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    A. \u67e5\u8be2\u4e0e "\u5f20\u65e0\u5fcc" \u7684\u85aa\u8d44\u53ca\u76f4\u5c5e\u9886\u5bfc\u76f8\u540c\u7684\u5458\u5de5\u4fe1\u606f <\/p>\n
    \u5206\u4e3a\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2 "\u5f20\u65e0\u5fcc" \u7684\u85aa\u8d44\u53ca\u76f4\u5c5e\u9886\u5bfc<\/p>\n
    select salary, managerid from emp where name = '\u5f20\u65e0\u5fcc'; <\/code><\/pre>\n
    \u67e5\u8be2\u4e0e "\u5f20\u65e0\u5fcc" \u7684\u85aa\u8d44\u53ca\u76f4\u5c5e\u9886\u5bfc\u76f8\u540c\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '\u5f20\u65e0\u5fcc');<\/code><\/pre>\n
    5.6.5 \u8868\u5b50\u67e5\u8be2<\/h3>\n
    \u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u662f\u591a\u884c\u591a\u5217\uff0c\u8fd9\u79cd\u5b50\u67e5\u8be2\u79f0\u4e3a\u8868\u5b50\u67e5\u8be2\u3002<\/p>\n
    \u5e38\u7528\u7684\u64cd\u4f5c\u7b26\uff1aIN<\/p>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    A. \u67e5\u8be2\u4e0e "\u9e7f\u6756\u5ba2" , "\u5b8b\u8fdc\u6865" \u7684\u804c\u4f4d\u548c\u85aa\u8d44\u76f8\u540c\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u5206\u4e3a\u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2 "\u9e7f\u6756\u5ba2" , "\u5b8b\u8fdc\u6865" \u7684\u804c\u4f4d\u548c\u85aa\u8d44<\/p>\n
    select job, salary from emp where name = '\u9e7f\u6756\u5ba2' or name = '\u5b8b\u8fdc\u6865'; <\/code><\/pre>\n
    \u67e5\u8be2\u4e0e "\u9e7f\u6756\u5ba2" , "\u5b8b\u8fdc\u6865" \u7684\u804c\u4f4d\u548c\u85aa\u8d44\u76f8\u540c\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where (job,salary) in ( select job, salary from emp where name = '\u9e7f\u6756\u5ba2' or name = '\u5b8b\u8fdc\u6865' );<\/code><\/pre>\n
    B.  \u67e5\u8be2\u5165\u804c\u65e5\u671f\u662f "2006-01-01" \u4e4b\u540e\u7684\u5458\u5de5\u4fe1\u606f , \u53ca\u5176\u90e8\u95e8\u4fe1\u606f<\/p>\n
    \u5206\u4e3a\u4e24\u6b65\uff1a<\/p>\n
    \u5165\u804c\u65e5\u671f\u662f "2006-01-01" \u4e4b\u540e\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where entrydate > '2006-01-01'; <\/code><\/pre>\n
    \u67e5\u8be2\u8fd9\u90e8\u5206\u5458\u5de5, \u5bf9\u5e94\u7684\u90e8\u95e8\u4fe1\u606f<\/p>\n
    select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;<\/code><\/pre>\n
    5.7 \u591a\u8868\u67e5\u8be2\u4e3e\u4f8b<\/h2>\n
    create table salgrade(\n                         grade int,\n                         losal int,\n                         hisal int\n) comment '\u85aa\u8d44\u7b49\u7ea7\u8868';\ninsert into salgrade values (1,0,3000);\ninsert into salgrade values (2,3001,5000);\ninsert into salgrade values (3,5001,8000);\ninsert into salgrade values (4,8001,10000);\ninsert into salgrade values (5,10001,15000);\ninsert into salgrade values (6,15001,20000);\ninsert into salgrade values (7,20001,25000);\ninsert into salgrade values (8,25001,30000);<\/code><\/pre>\n
    1\uff09 \u67e5\u8be2\u5458\u5de5\u7684\u59d3\u540d\u3001\u5e74\u9f84\u3001\u804c\u4f4d\u3001\u90e8\u95e8\u4fe1\u606f \uff08\u9690\u5f0f\u5185\u8fde\u63a5\uff09<\/p>\n
    \u8868: emp , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;<\/code><\/pre>\n
    2\uff09 \u67e5\u8be2\u5e74\u9f84\u5c0f\u4e8e30\u5c81\u7684\u5458\u5de5\u7684\u59d3\u540d\u3001\u5e74\u9f84\u3001\u804c\u4f4d\u3001\u90e8\u95e8\u4fe1\u606f\uff08\u663e\u5f0f\u5185\u8fde\u63a5\uff09<\/p>\n
    \u8868: emp , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;<\/code><\/pre>\n
    3\uff09\u67e5\u8be2\u62e5\u6709\u5458\u5de5\u7684\u90e8\u95e8ID\u3001\u90e8\u95e8\u540d\u79f0<\/p>\n
    \u8868: emp , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: emp.dept_id = dept.id<\/p>\n
    select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id; <\/code><\/pre>\n
    4\uff09 \u67e5\u8be2\u6240\u6709\u5e74\u9f84\u5927\u4e8e40\u5c81\u7684\u5458\u5de5, \u53ca\u5176\u5f52\u5c5e\u7684\u90e8\u95e8\u540d\u79f0; \u5982\u679c\u5458\u5de5\u6ca1\u6709\u5206\u914d\u90e8\u95e8, \u4e5f\u9700\u8981\u5c55\u793a\u51fa\u6765(\u5916\u8fde\u63a5)<\/p>\n
    select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;<\/code><\/pre>\n
    5\uff09\u67e5\u8be2\u6240\u6709\u5458\u5de5\u7684\u5de5\u8d44\u7b49\u7ea7<\/p>\n
    \u8868: emp , salgrade<\/p>\n
    \u8fde\u63a5\u6761\u4ef6 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal<\/p>\n
    -- \u65b9\u5f0f\u4e00\nselect e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;\n-- \u65b9\u5f0f\u4e8c\nselect e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;<\/code><\/pre>\n
    6\uff09\u67e5\u8be2  "\u7814\u53d1\u90e8" \u6240\u6709\u5458\u5de5\u7684\u4fe1\u606f\u53ca \u5de5\u8d44\u7b49\u7ea7<\/p>\n
    \u8868: emp , salgrade , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6 : emp.salary between salgrade.losal and salgrade.hisal ,<\/p>\n
    emp.dept_id = dept.id<\/p>\n
    \u67e5\u8be2\u6761\u4ef6 : dept.name = '\u7814\u53d1\u90e8'<\/p>\n
    select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal ) and d.name = '\u7814\u53d1\u90e8';<\/code><\/pre>\n
    7\uff09 \u67e5\u8be2 "\u7814\u53d1\u90e8" \u5458\u5de5\u7684\u5e73\u5747\u5de5\u8d44<\/p>\n
    \u8868: emp , dept<\/p>\n
    \u8fde\u63a5\u6761\u4ef6 : emp.dept_id = dept.id<\/p>\n
    select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '\u7814\u53d1\u90e8';<\/code><\/pre>\n
    8\uff09 \u67e5\u8be2\u5de5\u8d44\u6bd4 "\u706d\u7edd" \u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2 "\u706d\u7edd" \u7684\u85aa\u8d44<\/p>\n
    select salary from emp where name = '\u706d\u7edd'; <\/code><\/pre>\n
    \u67e5\u8be2\u6bd4\u5979\u5de5\u8d44\u9ad8\u7684\u5458\u5de5\u6570\u636e<\/p>\n
    select * from emp where salary > ( select salary from emp where name = '\u706d\u7edd' );<\/code><\/pre>\n
    9\uff09\u67e5\u8be2\u6bd4\u5e73\u5747\u85aa\u8d44\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2\u5458\u5de5\u7684\u5e73\u5747\u85aa\u8d44<\/p>\n
    select avg(salary) from emp; <\/code><\/pre>\n
    \u67e5\u8be2\u6bd4\u5e73\u5747\u85aa\u8d44\u9ad8\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp where salary > ( select avg(salary) from emp );<\/code><\/pre>\n
    10\uff09\u67e5\u8be2\u4f4e\u4e8e\u672c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    \u4e24\u6b65\uff1a<\/p>\n
    \u67e5\u8be2\u6307\u5b9a\u90e8\u95e8\u5e73\u5747\u85aa\u8d44<\/p>\n
    select avg(e1.salary) from emp e1 where e1.dept_id = 1;\nselect avg(e1.salary) from emp e1 where e1.dept_id = 2;<\/code><\/pre>\n
    \u67e5\u8be2\u4f4e\u4e8e\u672c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n
    select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );<\/code><\/pre>\n
    11\uff09 \u67e5\u8be2\u6240\u6709\u7684\u90e8\u95e8\u4fe1\u606f, \u5e76\u7edf\u8ba1\u90e8\u95e8\u7684\u5458\u5de5\u4eba\u6570<\/p>\n
    select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '\u4eba\u6570' from dept d;<\/code><\/pre>\n
    12\uff09 \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u9009\u8bfe\u60c5\u51b5, \u5c55\u793a\u51fa\u5b66\u751f\u540d\u79f0, \u5b66\u53f7, \u8bfe\u7a0b\u540d\u79f0<\/p>\n
    \u8868: student , course , student_course<\/p>\n
    \u8fde\u63a5\u6761\u4ef6: student.id = student_course.studentid , course.id =<\/p>\n
    student_course.courseid\nselect s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;<\/code><\/pre>\n
    6. \u4e8b\u52a1<\/h1>\n
    6.1 \u4e8b\u52a1\u7b80\u4ecb<\/h2>\n
    \u4e8b\u52a1 \u662f\u4e00\u7ec4\u64cd\u4f5c\u7684\u96c6\u5408\uff0c\u5b83\u662f\u4e00\u4e2a\u4e0d\u53ef\u5206\u5272\u7684\u5de5\u4f5c\u5355\u4f4d\uff0c\u4e8b\u52a1\u4f1a\u628a\u6240\u6709\u7684\u64cd\u4f5c\u4f5c\u4e3a\u4e00\u4e2a\u6574\u4f53\u4e00\u8d77\u5411\u7cfb\u7edf\u63d0\u4ea4\u6216\u64a4\u9500\u64cd\u4f5c\u8bf7\u6c42\uff0c\u5373\u8fd9\u4e9b\u64cd\u4f5c\u8981\u4e48\u540c\u65f6\u6210\u529f\uff0c\u8981\u4e48\u540c\u65f6\u5931\u8d25\u3002<\/p>\n
    \u6211\u4eec\u53ea\u9700\u8981\u5728\u4e1a\u52a1\u903b\u8f91\u6267\u884c\u4e4b\u524d\u5f00\u542f\u4e8b\u52a1\uff0c\u6267\u884c\u5b8c\u6bd5\u540e\u63d0\u4ea4\u4e8b\u52a1\u3002\u5982\u679c\u6267\u884c\u8fc7\u7a0b\u4e2d\u62a5\u9519\uff0c\u5219\u56de\u6eda\u4e8b\u52a1\uff0c\u628a\u6570\u636e\u6062\u590d\u5230\u4e8b\u52a1\u5f00\u59cb\u4e4b\u524d\u7684\u72b6\u6001\u3002<\/p>\n
    \"image-20231104020025409\"<\/div><\/p>\n
    \u9ed8\u8ba4MySQL\u7684\u4e8b\u52a1\u662f\u81ea\u52a8\u63d0\u4ea4\u7684\uff0c\u4e5f\u5c31\u662f\u8bf4\uff0c\u5f53\u6267\u884c\u5b8c\u4e00\u6761DML\u8bed\u53e5\u65f6\uff0cMySQL\u4f1a\u7acb\u5373\u9690<\/p>\n
    \u5f0f\u7684\u63d0\u4ea4\u4e8b\u52a1<\/p>\n
    6.2 \u4e8b\u52a1\u64cd\u4f5c<\/h2>\n
    \u6570\u636e\u51c6\u5907<\/p>\n
    drop table if exists account;\ncreate table account(\nid int primary key AUTO_INCREMENT comment 'ID',\nname varchar(10) comment '\u59d3\u540d',\nmoney double(10,2) comment '\u4f59\u989d'\n) comment '\u8d26\u6237\u8868';\ninsert into account(name, money) VALUES ('\u5f20\u4e09',2000), ('\u674e\u56db',2000);<\/code><\/pre>\n
    6.2.1 \u672a\u63a7\u5236\u4e8b\u52a1<\/h3>\n
    \u4e2d\u95f4\u51fa\u73b0\u5f02\u5e38\uff0c\u6570\u636e\u5c31\u4f1a\u5f02\u5e38<\/p>\n
    6.2.2 \u63a7\u5236\u4e8b\u52a1\u4e00<\/h3>\n
    1\uff09 \u67e5\u770b\/\u8bbe\u7f6e\u4e8b\u52a1\u63d0\u4ea4\u65b9\u5f0f<\/p>\n
    SELECT @@autocommit ;\nSET @@autocommit = 0 ;<\/code><\/pre>\n
    2\uff09\u63d0\u4ea4\u4e8b\u52a1<\/p>\n
    COMMIT\uff1b<\/code><\/pre>\n
    3\uff09\u56de\u6eda\u4e8b\u52a1<\/p>\n
    ROLLBACK\uff1b<\/code><\/pre>\n
    \u4e0a\u8ff0\u7684\u8fd9\u79cd\u65b9\u5f0f\uff0c\u6211\u4eec\u662f\u4fee\u6539\u4e86\u4e8b\u52a1\u7684\u81ea\u52a8\u63d0\u4ea4\u884c\u4e3a, \u628a\u9ed8\u8ba4\u7684\u81ea\u52a8\u63d0\u4ea4\u4fee\u6539\u4e3a\u4e86\u624b\u52a8\u63d0<\/p>\n
    \u4ea4, \u6b64\u65f6\u6211\u4eec\u6267\u884c\u7684DML\u8bed\u53e5\u90fd\u4e0d\u4f1a\u63d0\u4ea4, \u9700\u8981\u624b\u52a8\u7684\u6267\u884ccommit\u8fdb\u884c\u63d0\u4ea4\u3002<\/p>\n
    6.2.3 \u63a7\u5236\u4e8b\u52a1\u4e8c<\/h3>\n
    1\uff09\u5f00\u542f\u4e8b\u52a1<\/p>\n
    START TRANSACTION \u6216 BEGIN ; <\/code><\/pre>\n
    2\uff09\u63d0\u4ea4\u4e8b\u52a1<\/p>\n
    COMMIT\uff1b<\/code><\/pre>\n
    3\uff09\u56de\u6eda\u4e8b\u52a1<\/p>\n
    ROLLBACK\uff1b<\/code><\/pre>\n
    \u4e3e\u4f8b\uff1a<\/p>\n
    -- \u5f00\u542f\u4e8b\u52a1\nstart transaction\n-- 1. \u67e5\u8be2\u5f20\u4e09\u4f59\u989d\nselect * from account where name = '\u5f20\u4e09';\n-- 2. \u5f20\u4e09\u7684\u4f59\u989d\u51cf\u5c111000\nupdate account set money = money - 1000 where name = '\u5f20\u4e09';\n-- 3. \u674e\u56db\u7684\u4f59\u989d\u589e\u52a01000\nupdate account set money = money + 1000 where name = '\u674e\u56db';\n-- \u5982\u679c\u6b63\u5e38\u6267\u884c\u5b8c\u6bd5, \u5219\u63d0\u4ea4\u4e8b\u52a1\ncommit;\n-- \u5982\u679c\u6267\u884c\u8fc7\u7a0b\u4e2d\u62a5\u9519, \u5219\u56de\u6eda\u4e8b\u52a1\n-- rollback;<\/code><\/pre>\n
    6.3 \u4e8b\u52a1\u56db\u5927\u7279\u6027<\/h2>\n
      \n
    • \u539f\u5b50\u6027\uff08Atomicity\uff09\uff1a\u4e8b\u52a1\u662f\u4e0d\u53ef\u5206\u5272\u7684\u6700\u5c0f\u64cd\u4f5c\u5355\u5143\uff0c\u8981\u4e48\u5168\u90e8\u6210\u529f\uff0c\u8981\u4e48\u5168\u90e8\u5931\u8d25\u3002<\/li>\n
    • \u4e00\u81f4\u6027\uff08Consistency\uff09\uff1a\u4e8b\u52a1\u5b8c\u6210\u65f6\uff0c\u5fc5\u987b\u4f7f\u6240\u6709\u7684\u6570\u636e\u90fd\u4fdd\u6301\u4e00\u81f4\u72b6\u6001\u3002<\/li>\n
    • \u9694\u79bb\u6027\uff08Isolation\uff09\uff1a\u6570\u636e\u5e93\u7cfb\u7edf\u63d0\u4f9b\u7684\u9694\u79bb\u673a\u5236\uff0c\u4fdd\u8bc1\u4e8b\u52a1\u5728\u4e0d\u53d7\u5916\u90e8\u5e76\u53d1\u64cd\u4f5c\u5f71\u54cd\u7684\u72ec\u7acb<\/li>\n
    • \u73af\u5883\u4e0b\u8fd0\u884c\u3002<\/li>\n
    • \u6301\u4e45\u6027\uff08Durability\uff09\uff1a\u4e8b\u52a1\u4e00\u65e6\u63d0\u4ea4\u6216\u56de\u6eda\uff0c\u5b83\u5bf9\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u7684\u6539\u53d8\u5c31\u662f\u6c38\u4e45\u7684\u3002<\/li>\n<\/ul>\n
      \u4e0a\u8ff0\u5c31\u662f\u4e8b\u52a1\u7684\u56db\u5927\u7279\u6027\uff0c\u7b80\u79f0ACID\u3002<\/p>\n
      \"image-20231104122404409\"<\/div><\/p>\n
      6.4 \u5e76\u53d1\u4e8b\u52a1\u95ee\u9898<\/h2>\n
      1\uff09\u810f\u8bfb\uff1a\u4e00\u4e2a\u4e8b\u52a1\u8bfb\u5230\u5230\u53e6\u5916\u4e00\u4e2a\u4e8b\u52a1\u8fd8\u6ca1\u6709\u63d0\u4ea4\u7684\u6570\u636e<\/p>\n
      \"image-20231104122450758\"<\/div><\/p>\n
      \u6bd4\u5982B\u8bfb\u53d6\u5230\u4e86A\u672a\u63d0\u4ea4\u7684\u6570\u636e<\/p>\n
      2\uff09 \u4e0d\u53ef\u91cd\u590d\u8bfb\uff1a\u4e00\u4e2a\u4e8b\u52a1\u5148\u540e\u8bfb\u53d6\u540c\u4e00\u6761\u8bb0\u5f55\uff0c\u4f46\u4e24\u6b21\u8bfb\u53d6\u7684\u6570\u636e\u4e0d\u540c\uff0c\u79f0\u4e4b\u4e3a\u4e0d\u53ef\u91cd\u590d\u8bfb<\/p>\n
      \"image-20231104122528155\"<\/div><\/p>\n
      \u4e8b\u52a1A\u4e24\u6b21\u8bfb\u53d6\u540c\u4e00\u6761\u8bb0\u5f55\uff0c\u4f46\u662f\u8bfb\u53d6\u5230\u7684\u6570\u636e\u5374\u662f\u4e0d\u4e00\u6837\u7684<\/p>\n
      3\uff09\u5e7b\u8bfb\uff1a\u4e00\u4e2a\u4e8b\u52a1\u6309\u7167\u6761\u4ef6\u67e5\u8be2\u6570\u636e\u65f6\uff0c\u6ca1\u6709\u5bf9\u5e94\u7684\u6570\u636e\u884c\uff0c\u4f46\u662f\u5728\u63d2\u5165\u6570\u636e\u65f6\uff0c\u53c8\u53d1\u73b0\u8fd9\u884c\u6570\u636e\u5df2\u7ecf\u5b58\u5728\uff0c\u597d\u50cf\u51fa\u73b0\u4e86 "\u5e7b\u5f71"\u3002<\/p>\n
      \"image-20231104122611790\"<\/div><\/p>\n
      6.5 \u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/h2>\n
      \u4e3a\u4e86\u89e3\u51b3\u5e76\u53d1\u4e8b\u52a1\u6240\u5f15\u53d1\u7684\u95ee\u9898\uff0c\u5728\u6570\u636e\u5e93\u4e2d\u5f15\u5165\u4e86\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/p>\n\n\n\n\n\n\n\n\n
      \u9694\u79bb\u7ea7\u522b<\/th>\n\u810f\u8bfb<\/th>\n\u4e0d\u53ef\u91cd\u590d\u8bfb<\/th>\n\u5e7b\u8bfb<\/th>\n<\/tr>\n<\/thead>\n
      Read uncommitted<\/td>\nV<\/td>\nV<\/td>\nV<\/td>\n<\/tr>\n
      Read committed<\/td>\nX<\/td>\nV<\/td>\nV<\/td>\n<\/tr>\n
      Repeatable Read\uff08\u9ed8\u8ba4\uff09<\/td>\nX<\/td>\nX<\/td>\nV<\/td>\n<\/tr>\n
      Serializable<\/td>\nX<\/td>\nX<\/td>\nX<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      1\uff09\u67e5\u770b\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/p>\n
      SELECT @@TRANSACTION_ISOLATION;<\/code><\/pre>\n
      2\uff09\u8bbe\u7f6e\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/p>\n
      SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE }<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      1.MySQL\u6982\u8ff0 1.1 \u6570\u636e\u5e93\u76f8\u5173\u6982\u5ff5 \u6570\u636e\u5e93\uff0c\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff0cSQL \u4e3b\u6d41\u5173\u7cfb\u578b\u6570\u636e\u5e93 2.SQL 2. […]<\/p>\n","protected":false},"author":1,"featured_media":112,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[10,11],"class_list":["post-121","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql","tag-11"],"_links":{"self":[{"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/posts\/121","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/comments?post=121"}],"version-history":[{"count":1,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/posts\/121\/revisions"}],"predecessor-version":[{"id":122,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/posts\/121\/revisions\/122"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/media\/112"}],"wp:attachment":[{"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/media?parent=121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/categories?post=121"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sunqizheng1997.com\/index.php\/wp-json\/wp\/v2\/tags?post=121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}