(一)MySQL基础
一、MySQL启动
方法一:此电脑 --> 管理 --> 服务和应用程序 --> 服务 --> MySQL(根据自己电脑上的名字) --> 右键启动/停止/重新启动
方式二:命令提示符(cmd) 以管理员身份运行
net start MySQL(根据自己电脑上的名字)
net stop MySQL
二、MySQL登陆
方式一:cmd:mysql -h localhost -P 3306 -u root -p
000000(密码)
方式二:cmd:mysql -h localhost -P 3306 -u root -p000000
退出:exit
-P:Port,-u:user,-p:password
三、查看有哪些数据库
show databases;(MySQL语句以;结尾)
四、数据库中有哪些表
方法一:
use test;(使用test数据库,下面的操作若无指定的数据库,就是对test进行操作)
show tables;(显示test数据库中有哪些表)
方法二:
show tables from mysql;(显示mysql数据库中有哪些表)
五、查看现在操作的数据库
select database();
六、查看表的结构
desc boys;(查看boys表的结构)
七、查看表中数据
select * from boys;(查看boys表中的数据)
八、查看MySQL版本
方式一:mysql:select version();
方式二:cmd:mysql --version
九、MySQL语法规范:
1、不区分大小写(关键字大写,表名、列名小写)
2、每行命令以“;”结尾
3、缩进、换行
4、注释:#单行注释;-- 单行注释;/* */:多行注释
十、导入SQL文件
girls.sql
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `girls`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `beauty` */
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
/*Table structure for table `boys` */
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `boys` */
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
myemployees.sql
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50562
Source Host : localhost:3306
Source Schema : myemployees
Target Server Type : MySQL
Target Server Version : 50562
File Encoding : 65001
Date: 23/10/2024 19:12:54
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT,
`department_name` varchar(3) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`manager_id` int(6) NULL DEFAULT NULL,
`location_id` int(4) NULL DEFAULT NULL,
PRIMARY KEY (`department_id`) USING BTREE,
INDEX `loc_id_fk`(`location_id`) USING BTREE,
CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 271 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (10, 'Adm', 200, 1700);
INSERT INTO `departments` VALUES (20, 'Mar', 201, 1800);
INSERT INTO `departments` VALUES (30, 'Pur', 114, 1700);
INSERT INTO `departments` VALUES (40, 'Hum', 203, 2400);
INSERT INTO `departments` VALUES (50, 'Shi', 121, 1500);
INSERT INTO `departments` VALUES (60, 'IT', 103, 1400);
INSERT INTO `departments` VALUES (70, 'Pub', 204, 2700);
INSERT INTO `departments` VALUES (80, 'Sal', 145, 2500);
INSERT INTO `departments` VALUES (90, 'Exe', 100, 1700);
INSERT INTO `departments` VALUES (100, 'Fin', 108, 1700);
INSERT INTO `departments` VALUES (110, 'Acc', 205, 1700);
INSERT INTO `departments` VALUES (120, 'Tre', NULL, 1700);
INSERT INTO `departments` VALUES (130, 'Cor', NULL, 1700);
INSERT INTO `departments` VALUES (140, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (150, 'Sha', NULL, 1700);
INSERT INTO `departments` VALUES (160, 'Ben', NULL, 1700);
INSERT INTO `departments` VALUES (170, 'Man', NULL, 1700);
INSERT INTO `departments` VALUES (180, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (190, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (200, 'Ope', NULL, 1700);
INSERT INTO `departments` VALUES (210, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (220, 'NOC', NULL, 1700);
INSERT INTO `departments` VALUES (230, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (240, 'Gov', NULL, 1700);
INSERT INTO `departments` VALUES (250, 'Ret', NULL, 1700);
INSERT INTO `departments` VALUES (260, 'Rec', NULL, 1700);
INSERT INTO `departments` VALUES (270, 'Pay', NULL, 1700);
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`last_name` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`email` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`phone_number` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`job_id` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`salary` double(10, 2) NULL DEFAULT NULL,
`commission_pct` double(4, 2) NULL DEFAULT NULL,
`manager_id` int(6) NULL DEFAULT NULL,
`department_id` int(4) NULL DEFAULT NULL,
`hiredate` datetime NULL DEFAULT NULL,
PRIMARY KEY (`employee_id`) USING BTREE,
INDEX `dept_id_fk`(`department_id`) USING BTREE,
INDEX `job_id_fk`(`job_id`) USING BTREE,
CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 207 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (100, 'Steven', 'K_ing', 'SKING', '515.123.4567', 'AD_PRES', 24000.00, NULL, NULL, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', 'IT_PROG', 9000.00, NULL, 102, 60, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', 'IT_PROG', 6000.00, NULL, 103, 60, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', 'IT_PROG', 4200.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', 'FI_MGR', 12000.00, NULL, 101, 100, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', 'FI_ACCOUNT', 9000.00, NULL, 108, 100, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (110, 'John', 'Chen', 'JCHEN', '515.124.4269', 'FI_ACCOUNT', 8200.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', 'FI_ACCOUNT', 7700.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', 'FI_ACCOUNT', 7800.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', 'FI_ACCOUNT', 6900.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', 'PU_MAN', 11000.00, NULL, 100, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', 'PU_CLERK', 3100.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', 'PU_CLERK', 2900.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', 'PU_CLERK', 2800.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', 'PU_CLERK', 2600.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', 'PU_CLERK', 2500.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', 'ST_MAN', 8000.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', 'ST_MAN', 8200.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', 'ST_MAN', 7900.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', 'ST_MAN', 6500.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', 'ST_MAN', 5800.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', 'ST_CLERK', 3200.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', 'ST_CLERK', 2700.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', 'ST_CLERK', 2400.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', 'ST_CLERK', 2200.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', 'ST_CLERK', 3300.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', 'ST_CLERK', 2800.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', 'ST_CLERK', 2500.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', 'ST_CLERK', 2100.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', 'ST_CLERK', 3300.00, NULL, 122, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', 'ST_CLERK', 2900.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', 'ST_CLERK', 2400.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', 'ST_CLERK', 2200.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', 'ST_CLERK', 3600.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', 'ST_CLERK', 3200.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (139, 'John', 'Seo', 'JSEO', '650.121.2019', 'ST_CLERK', 2700.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', 'ST_CLERK', 2500.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', 'ST_CLERK', 3500.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', 'ST_CLERK', 3100.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', 'ST_CLERK', 2600.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', 'ST_CLERK', 2500.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', 'SA_MAN', 14000.00, 0.40, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', 'SA_MAN', 13500.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', 'SA_MAN', 12000.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', 'SA_MAN', 11000.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', 'SA_MAN', 10500.00, 0.20, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', 'SA_REP', 10000.00, 0.30, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', 'SA_REP', 9500.00, 0.25, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', 'SA_REP', 9000.00, 0.25, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', 'SA_REP', 8000.00, 0.20, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', 'SA_REP', 7500.00, 0.20, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', 'SA_REP', 7000.00, 0.15, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (156, 'Janette', 'K_ing', 'JKING', '011.44.1345.429268', 'SA_REP', 10000.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', 'SA_REP', 9500.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', 'SA_REP', 9000.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', 'SA_REP', 8000.00, 0.30, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', 'SA_REP', 7500.00, 0.30, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', 'SA_REP', 7000.00, 0.25, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', 'SA_REP', 10500.00, 0.25, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', 'SA_REP', 9500.00, 0.15, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', 'SA_REP', 7200.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', 'SA_REP', 6800.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', 'SA_REP', 6400.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', 'SA_REP', 6200.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', 'SA_REP', 11500.00, 0.25, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', 'SA_REP', 10000.00, 0.20, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', 'SA_REP', 9600.00, 0.20, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', 'SA_REP', 7400.00, 0.15, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', 'SA_REP', 7300.00, 0.15, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', 'SA_REP', 6100.00, 0.10, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', 'SA_REP', 11000.00, 0.30, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', 'SA_REP', 8800.00, 0.25, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', 'SA_REP', 8600.00, 0.20, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', 'SA_REP', 8400.00, 0.20, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', 'SA_REP', 7000.00, 0.15, 149, NULL, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', 'SA_REP', 6200.00, 0.10, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', 'SH_CLERK', 3200.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', 'SH_CLERK', 3100.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', 'SH_CLERK', 2500.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', 'SH_CLERK', 2800.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', 'SH_CLERK', 4200.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', 'SH_CLERK', 4100.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', 'SH_CLERK', 3400.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', 'SH_CLERK', 3000.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', 'SH_CLERK', 3800.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', 'SH_CLERK', 3600.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', 'SH_CLERK', 2900.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', 'SH_CLERK', 2500.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', 'SH_CLERK', 4000.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', 'SH_CLERK', 3900.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', 'SH_CLERK', 3200.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', 'SH_CLERK', 2800.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', 'SH_CLERK', 3100.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', 'SH_CLERK', 3000.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', 'AD_ASST', 4400.00, NULL, 101, 10, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', 'MK_MAN', 13000.00, NULL, 100, 20, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', 'MK_REP', 6000.00, NULL, 201, 20, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', 'HR_REP', 6500.00, NULL, 101, 40, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', 'PR_REP', 10000.00, NULL, 101, 70, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', 'AC_MGR', 12000.00, NULL, 101, 110, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', 'AC_ACCOUNT', 8300.00, NULL, 205, 110, '2016-03-03 00:00:00');
-- ----------------------------
-- Table structure for job_grades
-- ----------------------------
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades` (
`grade_level` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,
`lowest_sal` int(255) NULL DEFAULT NULL,
`highest_sal` int(255) NULL DEFAULT NULL,
PRIMARY KEY (`grade_level`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of job_grades
-- ----------------------------
INSERT INTO `job_grades` VALUES ('A', 20000, 50000);
INSERT INTO `job_grades` VALUES ('B', 15000, 20000);
INSERT INTO `job_grades` VALUES ('C', 10000, 15000);
INSERT INTO `job_grades` VALUES ('D', 5000, 10000);
INSERT INTO `job_grades` VALUES ('E', 0, 5000);
-- ----------------------------
-- Table structure for jobs
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,
`job_title` varchar(35) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`min_salary` int(6) NULL DEFAULT NULL,
`max_salary` int(6) NULL DEFAULT NULL,
PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of jobs
-- ----------------------------
INSERT INTO `jobs` VALUES ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO `jobs` VALUES ('AD_PRES', 'President', 20000, 40000);
INSERT INTO `jobs` VALUES ('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO `jobs` VALUES ('FI_ACCOUNT', 'Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('FI_MGR', 'Finance Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('HR_REP', 'Human Resources Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO `jobs` VALUES ('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO `jobs` VALUES ('MK_REP', 'Marketing Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('PR_REP', 'Public Relations Representative', 4500, 10500);
INSERT INTO `jobs` VALUES ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('PU_MAN', 'Purchasing Manager', 8000, 15000);
INSERT INTO `jobs` VALUES ('SA_MAN', 'Sales Manager', 10000, 20000);
INSERT INTO `jobs` VALUES ('SA_REP', 'Sales Representative', 6000, 12000);
INSERT INTO `jobs` VALUES ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('ST_CLERK', 'Stock Clerk', 2000, 5000);
INSERT INTO `jobs` VALUES ('ST_MAN', 'Stock Manager', 5500, 8500);
-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT,
`street_address` varchar(40) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`postal_code` varchar(12) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`city` varchar(30) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`state_province` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`country_id` varchar(2) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3201 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of locations
-- ----------------------------
INSERT INTO `locations` VALUES (1000, '1297 Via Cola di Rie', '00989', 'Roma', NULL, 'IT');
INSERT INTO `locations` VALUES (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT');
INSERT INTO `locations` VALUES (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
INSERT INTO `locations` VALUES (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP');
INSERT INTO `locations` VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO `locations` VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO `locations` VALUES (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
INSERT INTO `locations` VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO `locations` VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO `locations` VALUES (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
INSERT INTO `locations` VALUES (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN');
INSERT INTO `locations` VALUES (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
INSERT INTO `locations` VALUES (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
INSERT INTO `locations` VALUES (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG');
INSERT INTO `locations` VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO `locations` VALUES (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO `locations` VALUES (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
INSERT INTO `locations` VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
INSERT INTO `locations` VALUES (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
INSERT INTO `locations` VALUES (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
INSERT INTO `locations` VALUES (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
INSERT INTO `locations` VALUES (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
INSERT INTO `locations` VALUES (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
SET FOREIGN_KEY_CHECKS = 1;
(二)DQL(Data Query Language)
一、基础查询
语法:select 查询列表 from 表名;
特点:a.查询列表可以是:表中的字段、常量值、表达式、函数
b.查询的结果是一个虚拟的表格
1、查询表中的单个字段
SELECT last_name FROM employees;
2、查询表中的多个字段
SELECT last_name, salary, email FROM employees;
3、查询表中所有字段
SELECT * FROM employees;
4、查询常量值
SELECT 100;
SELECT 'John';
5、查询表达式
SELECT 100%3;
6、查询函数
SELECT VERSION();
7、起别名
SELECT 100%3 AS 结果;
SELECT last_name as 姓, first_name as 名 FROM employees;
或
SELECT last_name 姓, first_name 名 FROM employees;
别名中有空格,需使用双引号
SELECT salary as "out put" FROM employees;
8、去重(distinct)
SELECT DISTINCT department_id FROM employees;
9、+号的作用:运算符
两个操作数都为数值型,则作加号运算
SELECT 100+6.3;
只要其中一方为字符型,试图将字符型数值转换成数值型,如果转换成功,则继续做加法运算;如果转换失败,则将字符型数值转换为0
SELECT '123'+6.3;
SELECT 'John'+6.3;
只要其中一方为null,则结果为null
SELECT NULL+6.3;
10、concat拼接
SELECT CONCAT('A','B','C');
11、ifnull(id,结果) 如果某列为null,返回结果
SELECT IFNULL(commission_pct,0) as 奖金率, commission_pct FROM employees;
12.查询表的结构
DESC employees;
二、条件查询
语法:select 查询列表 from 表名 where 筛选条件;
分类:
a.按条件表达式筛选
条件运算符:>、<、=、!=、<>、>=、<=
b.按逻辑表达式筛选
逻辑运算符:&&(and)、||(or)、!(not)
c.模糊查询
like、between like、in、is null
1、按条件表达式筛选
查询工资大于12000的员工信息
SELECT * FROM employees WHERE salary>12000;
查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90;
2、按逻辑表达式筛选
查询工资在10000到20000之间的员工名、工资以及奖金
SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;
查询部门编号不在90到110之间,或者工资高于15000的员工信息
SELECT *
FROM employees
WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;
3、模糊查询
查询员工名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';
like和通配符搭配使用;%:任意多个字符,包含0个字符;_:任意单个字符
查询员工名中第三个字符为c,第5个字符为e的员工名和工资
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '__c_e%';
查询员工名中第二个字符为_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_%';
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';
查询员工编号在100到120之间的员工信息
SELECT *
FROM employees
WHERE employee_id>=100 AND employee_id<=120;
或
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;
between and:包含两个临界值
查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name,job_id
FROM employees
WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
in:判断某字段的值是否属于in列表中的某一项
in列表的值类型必须一致或兼容
查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
查询有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
=或<>不能用于判断null值
is null或is not null可以判断null值,不可判断其他类型
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct <=> NULL;
<=>:安全等于,可以判断null,也可以判断非null
三、排序查询
语法:select 查询列表 from [where 筛选条件] order by 排序列表 [asc|desc]
asc:升序;desc:降序;默认升序
1、简单排序
查询员工信息,工资从高到低排序
SELECT *
FROM employees
ORDER BY salary DESC;
2、筛选排序
查询部门编号>=90的员工信息,按入职时间的先后进行排序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;
3、按表达式排序
按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4、按别名排序
按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
5、按函数排序
按姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
6、按多个字段排序
查询员工信息,要求先按工资升序,再按员工编号降序
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
四、常见函数
语法:select 函数名(实参列表) [from 表];
分类:
a.单行函数:concat、length、ifnull等
b.分组函数:做统计使用
1、单行函数
(1)字符函数
length(str):获取参数值的字节个数
SELECT LENGTH("aaaa");
concat(str1,str2,.....):拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名
FROM employees;
upper(str):大写;lower(str):小写
SELECT UPPER('John');
姓变大写,名变小写,然后排序
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名
FROM employees;
substr(str,pos,len)、substring(str,pos,len)
索引从1开始
姓名中首字母大写,其他字符小写,然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
instr(str,substr)
返回子串第一次出现的索引(从1开始),如果找不到返回0
SELECT INSTR("abcdefg","de");
SELECT INSTR("abcdefg","ac");
trim([substr from ]str)
去除字符串前后的某字符,默认是空格
SELECT TRIM("a" FROM "abcdefg abdefg abdefg abdefga");
SELECT TRIM(" abcdefg abdefg abdefg abdefga ");
lpad(str,len,padstr)
用指定的字符实现左填充指定长度
SELECT LPAD("abc",10,"*");
rpad(str,len,padstr)
用指定的字符实现右填充指定长度
SELECT RPAD("abc",10,"*");
replace(str,from_str,to_str)
替换
SELECT REPLACE("abcdeabcdeabcde","abc","p");

(2)数学函数
round:四舍五入
round(x) eg:round(1.55)=2,round(-1.55)=-2
round(x,D) eg:round(1.567,2)=1.57
ceil:向上取整
返回大于等于该参数的最小整数
ceil(1.00)=1
ceil(1.5)=2
floor:向下取整
返回小于等于该参数的最大整数
floor(1.00)=1
floor(1.5)=1
truncate:截断
truncate(x,D),返回D为小数
truncate(1.6999,2)=1.69
mod:取余
mod(a,b)=a-a/b*b
(3)日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前系统时间,不包含日期
获取指定的部分,年、月、日、小时、分钟、秒
year(x): eg:year(now()),year('1998-1-1')=1998
month(x):
monthname(x):返回英文
str_to_date(str,format):将日期格式的字符转换成指定格式的日期
date_format(date,format):将日期转换成字符
%Y:4位的年份
%y:2位的年份
%m:月份(01,02,...,12)
%c:月份(1,2,...,12)
%d:日(00,01,...)
%H:小时(24小时制)
%h:小时(12小时制)
%i:分钟(00,01,...,59)
%s:秒(00,01,...,59)
查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees
WHERE hiredate='1992-4-3';
或
SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
(4)其他函数
version();
datebase();
user();
(5)流程控制函数
if函数
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金') 备注
FROM employees;
case函数
case 要判断的字段或表达式
when 常量值1 then 要显示的值1或语句1
when 常量值2 then 要显示的值2或语句2
......
else 要显示的值n或语句n
end
SELECT salary 工资,department_id,
CASE department_id
WHEN 30 THEN
salary*1.1
WHEN 40 THEN
salary*1.2
WHEN 50 THEN
salary*1.3
ELSE
salary
END as 新工资
FROM employees;
case函数
case
when 条件1 then 值1或语句1
when 条件2 then 值2或语句2
......
else 值n或语句n
end
SELECT salary,
CASE
WHEN salary>10000 THEN
'A'
WHEN salary>8000 THEN
'B'
WHEN salary>6000 THEN
'C'
ELSE
'D'
END as 级别
FROM employees;
2、分组函数
(1)sum求和
sum([distinct ]expr)
(2)avg平均值
avg(expr)
(3)min最小值
min(expr)
(4)max最大值
max(expr)
(5)count统计个数
count(expr)
特点:
sum、avg只支持数值型
min、max、count可以处理任何类型
都忽略null值
可以和distinct搭配
SELECT SUM(DISTINCT salary)
FROM employees;
SELECT COUNT(*) FROM employees;
统计表的行数
SELECT COUNT(1) FROM employees;
在表中增加1列值为1,统计表的行数
五、分组查询
语法:
select column,group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column];
分组前筛选 | 原始表 | group by子句的前面 | where |
分组后筛选 | 分组后的结果集 | group by子句的后面 | having |
a.分组函数做条件肯定是放在having子句中
b.能用分组前筛选的,就优先考虑使用分组前筛选
c.group by支持单个字段分组、多个字段分组、表达式或函数
d.也可以添加排序(排序放在最后)
查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
1、分组前查询
查询邮箱中包含a字符的每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE "%a%"
GROUP BY department_id;
查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
2、分组后查询
查询哪个部门员工个数大于2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id
HAVING MAX(salary)>12000;
查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个,以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
GROUP BY manager_id
HAVING manager_id>102;
3、按表达式或函数分组
按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些
SELECT COUNT(*) c,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING c>5;
4、按多个字段分组
查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY job_id,department_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;
六、连接查询(多表查询)
按功能分类:
a.内连接:等值连接、非等值连接、自连接
b.外连接:左外连接、右外连接、全外连接
c.交叉连接
1、sql92标准
特点:
a.多表等值连接的结果为多表的交集部分
b.n表连接,至少需要n-1个连接条件
c.多表的顺序没有要求
d.一般需要为表起别名
e.可以搭配所有子句使用,eg:排序、分组筛选
(1)等值连接
查询女神名和对应的男神名
SELECT name,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;
查询员工名和对应的部门名
SELECT last_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
查询员工名、工种号、工种名(为表起别名)
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;
查询有奖金的员工名、部门名(可以加筛选)
SELECT last_name,department_name,commission_pct
FROM employees,departments
WHERE employees.department_id=departments.department_id
AND employees.commission_pct IS NOT NULL;
查询城市名中第二个字符为o的部门名和城市名(可以加筛选)
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE "_o%";
查询每个城市的部门个数(加分组)
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;
查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资(加分组)
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;
查询每个工种的工种名和员工个数,并按员工个数排序(加排序)
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
查询员工名、部门名和所在城市,且城市以s开头(三表连接)
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE "s%";
(2)非等值连接
查询员工的工资和工资级别
SELECT last_name,salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level='C';
(3)自连接
查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
2、sql99语法
select 查询列表
from 表1 别名
[连接类型] join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
(1)内连接(inner)
特点:
a.可添加排序、分组、筛选
b.inner可省略
c.筛选条件放在where后面,连接条件放在on后面
1)等值连接
查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
查询员工名中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.last_name LIKE "%e%";
查询部门个数大于3的城市名和部门个数(添加分组+筛选)
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#查询员工名、部门名、工种名,并按部门名降序(三表连接)
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id
GROUP BY department_name DESC;
2)非等值连接
查询员工的工资级别
SELECT last_name,salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
查询工资级别的个数大于20的个数,并且按工资级别降序
SELECT grade_level,COUNT(*)
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20;
3)自连接
查询员工的名字,上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id;
查询姓名中包含字符k员工的名字,上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.last_name LIKE "%k%";
(2)外连接
场景:用于查询一个表格中有另一个表格没有的记录
特点:
a.外连接的查询结果为主表中的记录:
如果从表中有和它匹配的,则显示匹配的值;
如果从表中没有和它匹配的值,则显示null;
外连接查询结果=内连接结果+主表中有而从表中没有的记录
b.左外连接,left join左边的是主表
右外连接,right join右边的是主表
c.左外和右外交换两个表的顺序,可以实现同样的效果
d.全外连接=内连接的结果+表1中有但表2没有的+表1没有但表2有的
查询男朋友不在男神表的女神名
左外连接
SELECT b.name,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
右外连接
SELECT b.name,bo.*
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
查询哪个部门没有员工
左外连接
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;
右外连接
SELECT d.*,e.employee_id
FROM employees e
LEFT OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;
全外连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
UNION
SELECT b.*,bo.*
FROM beauty b
RIGHT JOIN boys bo
ON b.boyfriend_id=bo.id;
交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
七、子查询
出现在其他语句中的select语句称为子查询/内查询,外部的查询语句称为主查询/外查询
分类:
a.按子查询出现的位置:
select后面:标量子查询
from后面:表子查询
where或having后面:标量子查询、列子查询、行子查询
exists后面:表子查询
b.按结果集的行列数不同:
标量子查询:结果集一行一列
列子查询:结果集一列多行
行子查询:结果集一行多列
表子查询:结果集多行多列
1、where或having后面
标量子查询/列子查询/行子查询
特点:
a.子查询放在小括号内
b.子查询一般放在条件的右侧
c.标量子查询一般搭配着单行操作符使用,>、<、>=、<=、=、<>;列子查询一般搭配着多行操作符使用,in、any、some、all
d.子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
(1)标量子查询
谁的工资比Abel高?(子查询结果为Abel的工资,一行一列)
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
查询job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);
返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id=50
);
(2)列子查询(多行子查询)
操作符:
in/not in:等于列表中的任意一个
any/some:和子查询返回的某一个值比较
all:和子查询返回的所有值比较
in=all,not in=<>all
返回location_id是1400或1700的部门中所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM locations
WHERE location_id IN(1400,1700)
);
返回其它部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
或
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
返回其他部门中比job_id为IT_PROG部门所有员工工资低的员工的员工号
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
或
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
(3)行子查询(结果集一行多列或多行多列)
查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
) AND salary=(
SELECT MAX(salary)
FROM employees
);
或
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
2、select后面
标量子查询(一行一列)
查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
) 个数
FROM departments d;
查询员工号等于102的部门名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;
3、from后面
表子查询(多行多列),必须取别名
查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
4、exists后面(是否存在,只关系有没有),结果只有1或0
SELECT EXISTS(
SELECT employee_id
FROM employees
);
结果为1
SELECT EXISTS(
SELECT employee_id
FROM employees
WHERE salary=30000;
);
结果为0
查询有员工的部门
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
);
或
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees e
);
查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
);
或
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);
八、分页查询
场景:当要显示的数据,一页显示不全,需要分页提交sql请单
语法:
select 查询列表
from 表
[join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit [offset,]size;
offset:要显示条目的起始索引(起始索引从0开始),默认为0
size:要显示的条目个数
查询前5个员工信息
SELECT *
FROM employees
LIMIT 0,5;
查询第11条到第25条
SELECT *
FROM employees
LIMIT 10,15;
有奖金的员工信息,并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
九、联合查询(union)
将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
.....
查询部门编号大于90或邮箱中包含a的员工信息
SELECT *
FROM employees
WHERE department_id>90
OR email LIKE "%a";
或
SELECT *
FROM employees
WHERE department_id>90
UNION
SELECT *
FROM employees
WHERE email LIKE "%a";
特点:
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重,如果使用union all可以包含重复项
(三)DML语言(Data Manipulation Language)
插入:insert
修改:update
删除:delete
一、插入语句
方式一:insert into 表名(列名,列名,...) values(值1,值2,...);
方式二:insert into set 列名=值,列名=值...;
特点:
a.插入的值的类型要与列的类型一致或兼容
b.不可以为null的列必须插入值
c.列的顺序可以调换
d.列数和值的个数必须一致
e.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
方式一支持插入多行;方式二支持子查询,方式二不支持
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'张三','女','1990-4-23','18988888888',NULL,2);
INSERT INTO beauty(id,name,sex,phone)
VALUES(14,'李四','女','18988888888');
INSERT INTO beauty(name,phone,id,sex)
VALUES('王五','18988888888',15,'女');
INSERT INTO beauty
VALUES(16,'六六','女','1999-4-1','19825145236',NULL,2);
INSERT INTO beauty
SET id=17,name='七七',phone='14523657452';
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUE (18,'八八','女','2000-5-3','15236524567',NULL,3),
(19,'九九','女','2002-5-3','15236852567',NULL,4);
INSERT INTO beauty(id,name,phone)
SELECT 20,'二十','13945268526';
二、修改语句
1、修改单表的记录
语法:
update 表名
set 列=新值,列=新值
where 筛选条件;
修改beauty表中姓王的女神的电话为12345678
UPDATE beauty
SET phone='12345678'
WHERE name LIKE "王%";
修改boys表中的id为1的,名称为宋江、魅力值为33
UPDATE boys
SET boyName='宋江',userCP=33
WHERE id=1;
2、修改多表的记录
sql92语法:
update 表1 别名,表2 别名
set 列=值,列=值,...
where 筛选条件
and 筛选条件;
sql99语法:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,列=值,...
where 筛选条件;
修改鹿晗的女朋友的手机号为123456789
UPDATE boys bo
INNER JOIN beauty b
ON bo.id=b.boyfriend_id
SET b.phone='123456789'
WHERE bo.boyName='鹿晗';
修改没有男朋友的女神名的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b
ON bo.id=b.boyfriend_id
SET b.boyfriend_id=2
WHERE bo.id IS NULL;
三、删除语句
1、单表的删除
delete from 表名 where 筛选条件;
删除手机号以9结尾的女神信息
DELETE
FROM beauty
WHERE phone LIKE "%9";
2、多表的删除
sql92语句:
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 筛选条件
and 筛选条件;
sql99语句:
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件;
删除张无忌的女朋友信息
DELETE b
FROM beauty b
INNER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.boyName="张无忌";
删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.boyName="黄晓明";
3、truncate(全删)
删除所有的男神信息
TRUNCATE TABLE boys;
特点:
a.delete可以加where条件,truncate不能加
b.truncate效率比delete高
c.假如要删除的表中有自增序列,如果用delete删除后,再插入数据,自增序列的值从断点开始,而truncate删除后,再插入序号从1开始
d.truncate删除后没有返回值,delete删除有返回值
e.truncate删除后不能回滚,delete删除后可以回滚
(四)DLL语言(Data Definition Language)
库的管理:创建(create)、修改(alter)、删除(drop)
表的管理:创建(create)、修改(alter)、删除(drop)
一、库的管理
1、库的创建
create database 库名;
创建库Books
CREATE DATABASE IF NOT EXISTS Books;
2、库的修改
rename database 旧库名 to 新库名;
更改库的字符集
ALTER DATABASE Books CHARACTER SET GBK;
3、库的删除
drop database if exists 库名;
DROP DATABASE IF EXISTS Books;
二、表的管理
1、表的创建
create table 表名(
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
......
);
创建表book
CREATE TABLE book(
id INT,
name VARCHAR(20),
price DOUBLE,
authorId INT,
publishDate DATETIME
);
创建表author
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
);
2、表的修改
列名、列的类型或约束、添加新列、删除列、修改表名
alter table 表名 add|modify|change|drop column 列名 [类型 约束];
(1)修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate datetime;
(2)修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate timestamp;
(3)添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
(4)删除列
ALTER TABLE author DROP COLUMN annual;
(5)修改表名
ALTER TABLE author RENAME TO book_author;
3、表的删除
DROP TABLE IF EXISTS book_author;
建库:
drop database if exists 旧库名;
create database 新库名;
建表:
drop table if exists 旧表名;
create table 新表名;
4、表的复制
(1)仅复制表的结构
CREATE TABLE copy1 LIKE author;
(2)复制表的结构+数据
CREATE TABLE copy2
SELECT *
FROM author;
(3)只复制部分数据
CREATE TABLE copy3
SELECT *
FROM author
WHERE nation="中国";
(4)仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
(五)常见的数据类型
a.数值型:
整型:tinyint、smallint、mediumint、int、integer、bigint
小数:定点数、浮点数
b.字符型:
短字符:char、varchar
长字符:text、blob(二进制)
c.日期型
一、整型
设置无符号和有符号
CREATE TABLE table_int(
id INT PRIMARY KEY,
t1 INT,
t2 INT UNSIGNED
);
特点:
a.如果不设置无符号还是有符号,默认是有符号;如果想设置无符号,需要添加UNSIGNED关键字
b.如果插入的数值超出了整型范围,会报out of range异常,并且插入临界值
c.如果不设置长度,会有默认的长度;长度代表了显示的最大宽度,如果不够会用0左边填充,但必须搭配zerofill使用
CREATE TABLE table_int1(
id INT PRIMARY KEY,
t1 INT(7),
t2 INT(7) UNSIGNED
);
CREATE TABLE table_int2(
id INT PRIMARY KEY,
t1 INT(7) ZEROFILL,
t2 INT(6) ZEROFILL
);
二、小数
浮点型:
float(M,D),4字节
double(M,D),8字节
定点型:
dec(M,D)
decimal(M,D)
特点:
a.M:整数部位+小数部位;D:小数部位;如果超过范围,则插入临界值
b.M和D都可以省略;如果是decimal,则M默认为10,D默认为0;如果是float和double,则会根据插入的数值的精度来决定
c.定点型的精确度较高
CREATE TABLE table_float(
id INT PRIMARY KEY,
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL
);
所选择的类型越简单越好,能保存数值类型越小越好
三、字符型
短字符:char(M),0~255,固定长度,耗费空间,效率高,M可以省略,默认为1
长字符:varchar(M),0~65535,固定长度,节省空间,效率低,M不可以省略
CREATE TABLE table_var(
id INT PRIMARY KEY,
c1 ENUM('a','b','c')
);
INSERT INTO table_var VALUES(1,'a');
INSERT INTO table_var VALUES(2,'b');
INSERT INTO table_var VALUES(3,'c');
四、日期型
date:4字节,1000-01-01~9999-12-31
datetime:8字节,1000-01-01 00:00:00~9999-12-31 23:59:59
time:3字节,-838:59:59~838:59:59
year:1字节,1901~2155
CREATE TABLE table_date(
id INT PRIMARY KEY,
t1 DATETIME,
t2 TIMESTAMP
);
(六)常见约束
用于限制表中的数据:
a.not null:非空,用于保证该字段的值不能为空
b.default:默认,用于保证该字段有默认值
c.primary key:主键,用于保证该字段的值具有唯一性,且非空
d.unique:唯一,可以为空
e.check:检查约束
f.foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
添加时机:
a.创建表
b.修改表
一、创建表时添加约束
分类:
a.列级约束
b.表级约束
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
1、添加列级约束
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
CREATE TABLE stuinfo(
id INT PRIMARY KEY,
stuName VARCHAR(20) NOT NULL,
gender CHAR(1) CHECK(gender='男' OR gender='女'),
seat INT UNIQUE,
age INT DEFAULT 18,
majorId INT,
FOREIGN KEY(majorId) REFERENCES major(id)
);
2、添加表级约束
[constraint 约束名] 约束类型(字段名)
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
CONSTRAINT pk PRIMARY KEY(id),
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender='男' OR gender='女'),
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);
二、修改表时添加约束
1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
2、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3、添加主键
a.列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
b.表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4、添加唯一
a.列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
b.表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
5、添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major
FOREIGN KEY(majorId) REFERENCES major(id);
三、修改时删除约束
1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;
2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
3、删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
4、删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
5、删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorId;
四、列标识(自增长列)
auto_increment:不用手动的插入,系统提供默认的序列值
创建表时设置列标识
CREATE TABLE table_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
特点:
a.一个表中最多只能有一个列标识
b.列标识的类型只能是数值型
c.标识可以通过set auto_increment_increment=3;设置步长
d.可以通过手动插入,设置起始值
修改表时设置列标识
ALTER TABLE table_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
修改表时删除列标识
ALTER TABLE table_identity MODIFY COLUMN id INT PRIMARY KEY;
(七)TCL(Transaction Control Language)事务控制语言
事务:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
特点:
a.原子性
b.一致性
c.隔离性
d.持久性
一、事务的创建
1、隐式事务
事务没有明显的开启和结束标记
select、insert、update、delete
delete from 表 where id=1;
2、显式事务
事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用 set autocommit=0;
a.开启事务
set autocommit=0;
start transaction;
b.编写事务中的sql语句(select、insert、update、delete)
c.结束事务
commit;(提交事务)
rollback;(回滚事务)
SET AUTOCOMMIT=0;
START TRANSACTION;
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';
COMMIT;
savepoint保存点
SET AUTOCOMMIT=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a; #回滚到保存点
二、隔离
脏读 | 不可重复 | 幻读 | |
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read | × | × | √ |
serializable | × | × | × |
设置当前MySQL连接的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置数据库系统的全局的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
查看当前隔离级别
SELECT @@GLOBAL.TX_ISOLATION;
(八)视图
虚拟表,和普通表一样使用
查询姓张的学生名和专业名
CREATE VIEW v1
AS
SELECT stuName,majorName
FROM stuinfo s
INNER JOIN major m
ON s.majorId=m.id;
SELECT *
FROM v1
WHERE stuName LIKE "张%";
一、创建视图
create view 视图名
as
查询语句;
查询邮箱中包含a字符的员工名,部门名和工种信息
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
JOIN jobs j
ON j.job_id=e.job_id;
SELECT *
FROM myv1
WHERE last_name LIKE "%a%";
二、视图的修改
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句;
eg:
ALTER VIEW myv3
AS
SELECT *
FROM employees;
三、删除视图
drop view 视图名,视图名;
DROP VIEW myv1,myv2;
四、查看视图
DESC 视图名;
DESC myv3;
show create view 视图名;
SHOW CREATE VIEW myv3;
查询电话号以“011”开头的员工名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE "011%";
SELECT *
FROM emp_v1;
五、视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT *
FROM myv1;
1、插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
SELECT * FROM myv1;
2、修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';
SELECT * FROM myv1;
3、删除
DELETE FROM myv1 WHERE last_name='张无忌';
SELECT * FROM myv1;
以下视图不允许更新:
a.分组函数,distinct、group by、having、union、union all
b.常量视图
c.select中包含子查询
d.join
e.from一个不能更新的视图
f.where子句的子查询引用了from子句中的表
(九)变量
系统变量:全局变量、会话变量
自定义变量:用户变量、局部变量
一、系统变量
1、查看系统变量
SHOW GLOBAL|[SESSION] VARIABLES;
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
2、查看满足条件的部分系统变量
SHOW GLOBAL|[SESSION] VARIABLES LIKE "%char%";
SHOW GLOBAL VARIABLES LIKE 'char%';
SHOW SESSION VARIABLES LIKE 'char%';
3、查看指定的某个系统变量
SELECT @@GLOBAL|[SESSION].系统变量名;
SELECT @@GLOBAL.tx_isolation;
SELECT @@SESSION.tx_isolation;
4、为某个系统变量赋值
SET GLOBAL|[SESSION].系统变量名=值;
SET @@GLOBAL|[SESSION].系统变量名=值;
全局是GLOBAL,会话是SESSION,不写默认是SESSION。
二、自定义变量
1、用户变量
(1)声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
(2)赋值(更新用户变量的值)
方式一:通过set或select
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
方式二:通过select into
select 字段 into 变量名 from 表;
(3)使用(查看用户变量的值)
select @用户变量名;
SET @name='John';
SET @name=100;
SET @count=1;
SELECT COUNT(*) INTO @count FROM employees;
SELECT @count;
2、局部变量
仅在定义它的begin end中有效
(1)声明
declare 变量名 类型;
declare 变量名 类型 default 值;
(2)赋值
方式一:通过set或select
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
方式二:通过select into
select 字段 into 变量名 from 表;
(3)使用
select 局部变量名;
作用域 | 定义和使用的位置 | 语法 | |
用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号,不用限定类型 |
局部变量 | begin end中 | 只能在begin end中,且为第一句话 | 一般不用加@符号,需限定类型 |
声明两个变量并赋初始值,求和,并打印
1.用户变量
SET @m=1;
SET @n:=2;
SET @sum=@m+@n;
SELECT @sum;
2.局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE sum INT;
SET sum=m+n;
SELECT sum;
(十)存储过程和函数
一、存储过程
1、创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语句)
end
(1)参数列表包含三部分:参数模式(in:输入、out:输出、inout:输入/输出)、参数名、参数类型
eg:in stuname varchar(20)
(2)存储过程体只有一句话,begin end可省略
每条SQL语句后必须加分号
存储过程的结尾可使用delimiter重新设置
delimiter 结束标记
eg:delimiter $
2、调用语法
call 存储过程名(实参列表);
(1)空参列表
插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,password)
VALUES ('john','0000'),('lily','1111'),('rose','2222'),('jack','3333'),('tom','4444');
END $
CALL myp1() $
(2)带in模式参数的存储过程
根据女神名,查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b
ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
CALL myp2('小昭') $
用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DELETE '';
SELECT COUNT(*)
FROM admin
WHERE admin.username=username
AND admin.password=password;
SELECT IF(result>0,'成功','失败');
END $
CALL myp3('root','0000') $
(3)带out模式
根据女神名,返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b
ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
CALL myp4('小昭',@bName) $
SELECT @bName$
(4)带inout模式
传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp5(INOUT a INT, INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=5;
SET @n=6;
CALL myp5(@m,@n) $
SELECT @m,@n;
3、删除存储过程
drop procedure 存储过程名;
4、查看存储过程的信息
show create procedure 存储过程名;
二、函数
有且仅有一个返回值
1、创建
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
a.参数列表:参数名 参数类型
b.函数体:必须有return
c.函数体中只有一句话,可以省略begin end
d.使用delimiter设置结束标记,delimiter $;
2、调用
select 函数名(参数列表)
无参有返回
CREATE PROCEDURE myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM employees;
RETURN c;
END
SELECT myf1() $;
有参有返回
CREATE PROCEDURE myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;
SELECT salary INTO @sal
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $
SELECT myf2('k_ing') $
3、查看函数
show create function 函数名;
4、删除函数
drop function 函数名;
(十一)流程控制结构
顺序结构、分支结构、循环结构
一、分支结构
1、if函数
if(表达式1,表达式2,表达式3)
如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值
2、case结构
(1)
case 变量|表达式|字段:
when 要判断的值 then 返回值1;
when 要判断的值 then 返回值2;
when 要判断的值 then 返回值3;
......
else 要返回的值n;
end
(2)
case
when 要判断的条件1 then 返回值1或语句1;
when 要判断的条件2 then 返回值2或语句2;
when 要判断的条件3 then 返回值3或语句3;
......
else 返回值n或语句n;
end case;
CREATE FUNCTION test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN score>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $;
CALL test_case(96) $
3、if多分支
if 条件1 then 语句1;
elseif 条件2 then 语句2;
......
[else 语句n;]
end if;
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END $;
CALL test_if(85) $
二、循环语句
while、loop、repeat
循环控制:iterate(类似于continue)、leave(类似于break)
1、while
[标签:] while 循环条件 do
循环体
end while [标签];
2、loop
[标签:] loop
循环体
end loop [标签];
3、repeat
[标签:] repeat
循环体
until 结束循环的条件
end repeat [标签];
CREATE FUNCTION pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<insertCount DO
INSERT INTO admin(username,password) VALUES(CONCAT('Rose',i),'6666');
SET i=i+1;
END WHILE;
END $;
CALL pro_while(100) $