MySQL相关知识(更多的是SQL操作)
MySQL总结和练习
1、关系型数据库的常见组件
数据库：database
表：table
行：row
列：column
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler
2、数据类型：
字符型、数值型、日期时间型、内建类型 通用类型修饰符： NOT NULL NULL DEFAULT PRIMARY KEY|UNIQUE KEY|UNIQUE 字符型： 定长数据类型： char(不区分字符大小写),binary(区分字符大小写):,结束符算一个字符，共255 变长数据类型： varchar(不区分字符大小写),varbinary(区分字符大小写):，共65535 对象存储： text不区分大小写定长:tinytext、text、mediumtext、logtext blob分大小写变长:tinyblob、blob、mediumblob、longblob 字符类型修饰符： DEFAULT 'STRING':指明默认值 CHARACTER SET '':使用的字符集 COLLATION '':使用的排序规则 数值型： 精确数值型： 整型：TINYINT(1byte),SMALLINT(2byte),MEDIUMINT(3byte),INT(4byte),BIGINT(8byte) 十进制型：DECIMAL主要是财务数据 近似数值型： 浮点型： FLOAT：单精度 DOUBLE：双精度 整型数据修饰符： DEFAULT NUMBER AUTO_INCREMENT：整数自动增长 UNSIGNED:无符号,不能表示负数 日期时间型： DATE:3byte TIME:3byte DATETIME:8byte TIMESTAMP：4byte 从1970年0点0分到现在 YEAR(2)：1byte YEAR(4): 1byte 内建类型： enum枚举类型('g','f') set集合类型
3、DDL: Data Defined Language 数据定义语言（CREATE DROP SHOW ALTER 增删查改）
用DDL的DB组件：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器
查找帮助：HELP KEYWORD;
数据库：
CREATE DATABASE [IF NOT EXISTS] db_name DEFAULT CHARACTER SET 'character set name' COLLATE 'collate name'; DROP DATABASE [IF EXISTS] db_name; SHOW CHARACTER SET; SHOW COLLATION; SHOW DATABASES; CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
表:
创建删除：
CREATE TABLE [IF NOT EXISTS] table_name (col1 data_type 修饰符，col2 data_type 修饰符) ENGINE 'engine_name' ROW_FORMAT COMPRESSED; CREATE TABLE [IF NOT EXISTS] tabl_name [LIKE old_tabl_name] CREATE TABLE [IF NOT EXISTS] tabl_name [(create_definition,...)] [table_options] [partition_options] select_statement DROP TABLE [IF EXISTS] table_name; SHOW ENGINES; SHOW GLOBAL VARIABLES LIKE 'default%engine'; SHOW TABLES DESC tb_name 表结构 SHOW CREATE TABLE table_name 表命令 SHOW TABLE STATUS LIKE 'table_name'\G 表状态 CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED ); CREATE TABLE students2 (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED NOT NULL,PRIMARY KEY(id,name));
修改:
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] DROP PRIMARY KEY DROP [COLUMN] col_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] 添加字段：add 删除字段：drop 修改字段：alter(默认值),change(字段名)，modify(数据类型) 添加字段 ALTER TABLE students2 ADD gender ENUM('m','f') NOT NULL after name; 修改字段名 ALTER TABLE students CHANGE gender Gender ENUM('M','F') NOT NULL; 修改数据类型 ALTER TABLE students MODIFY Gender ENUM('M','W'); 删除字段 ALTER TABLE students drop Gender; 增加删除索引 ADD INDEX(index_col_name,...) DROP INDEX index_name SHOW INDEX from table_name; ALTER TABLE students ADD INDEX(id); ALTER TALBE studnets DROP INDEX id; 创建索引： CREATE INDEX index_name ON tbl_name (index_col_name,...); DROP INDEX index_name ON tbl_name;
4、DML：数据操作语言（INSERT DELETE SELECT UPDATE ）
插入数据：如果有多条，则一次性插入数据，减少索引的更新 INSERT [INTO] tbl_name [(col_name,...)] VALUES (value1,....) SELECT * FROM table_name; INSERT INTO students VALUES (1,'Yang Guo','m'),(2,'Guo Xiang','f'); INSERT INTO students (id,name) VALUES (3,'Zhang Wuji'),(4,'Zhao Min'); 查询数据： SELECT col1,col2,.... FROM table_name [WHERE clause] [ORDER BY 'col_name'] [LIMIT [m,]n] [ASC | DESC]; 字段表示法： *：所有字段 as: 字段别名，col1 AS alias1; WHERE clase: 操作符： >,<,>=,<=,=,!= BETWEEN.....AND..... 条件逻辑操作： and or not LIKE:模糊匹配 %:任意长度的任意字符 _:任意单个字符 RLIKE:支持正则表达式模式匹配（能不用则不用,效率很低） IS NULL IS NOT NULL mysql> select last_insert_id(); 查看当前插入的id号 SELECT * FROM students WHERE gender IS NULL; SELECT * FROM students WHERE id<3; SELECT * FROM students ORDER BY name DESC; SELECT * FROM students ORDER BY name DESC LIMIT 2; SELECT * FROM students ORDER BY name DESC LIMIT 1,2; SELECT * FROM students WHERE id>=2 and id<=4; SELECT * FROM students WHERE id BETWEEN 2 AND 4; SELECT * FROM students WHERE name LIKE 'Z%'; SELECT id AS sid,name AS sname FROM students; 删除数据： DELETE FROM tbl_name [WHERE where_condition] [ORDER BY 'col1_name'] [LIMIT [m,]n] DELETE FROM students WHERE id=3; DELETE FROM students WHERE name='Yang Guo'; 更新数据： UPDATE table_name SET col1=new_val1, col2=new_val2 [WHERE clause] [ORDER BY 'col1_name'] [LIMIT [m,]n] UPDATE students SET gender='f' WHERE id=4; UPDATE students SET gender='m' WHERE id=1;
5、DCL（GRANT REVOKE）
(1)创建用户账号： CREATE USER 'user'@'host' [IDENTIFIED BY 'password']; CREATE USER 'wpuser'@'%' IDENTIFIED BY '123456'; (2)删除用户： DROP USER 'username'@'host'; DROP USER 'wpuser'@'%'; (3)用户授权： GRANT priv_type,....ON db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT]此选项很危险 priv_type:ALL [PRIVILEGES] db_name.tb_name: *.*:所有库的所有表 db_name.*:指定库的所有表 db_name.tb_name:指定库的指定表 db_name.routine_name:指定库的存储例程 GRANT ALL PRIVILEGES ON *.* TO 'wpuser'@'192.168.1.0/16' IDENTIFIED BY '123456'; (4)查看指定用户获得的授权 SHOW GRANTS SHOW GRANTS FOR 'wp'@'%'; SHOW GRANTS FOR 'root'@'localhost'; SHOW GRANTS FOR CURRENT_USER(); SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR 'root'@localhost; (5)回收授权： REVOKE priv_type,....ON db_name.tlb_name FROM 'user'@'host'; 注意：MariaDB服务进程启动时会读取Mysql库中的所有权限表至内存中 (1)GRANT或REVOKE等执行权限操作会保存于表中，MariaDB的服务进程会自动重读授权表 (2)对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表 mysql>FLUSH PRIVILEGES;
SQL语句练习：（照着敲就行了）
CREATE DATABASE IF NOT EXISTS mydatabase DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; SHOW DATABASES; SHOW CHARACTER SET; SHOW COLLATION; use mydatabase; CREATE TABLE IF NOT EXISTS students (id tinyint UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, name char(5) NOT NULL, age tinyint UNSIGNED NOT NULL) ENGINE innodb ROW_FORMAT COMPRESSED; SHOW ENGINES; SHOW CREATE TABLE students; SHOW TABLE STATUS LIKE 'students'\G DESC students; ALTER TABLE students ADD gender enum('m','f') NOT NULL after name; ALTER TABLE students ADD birthday datetime NOT NULL after gender; ALTER TABLE students MODIFY birthday datetime NOT NULL after age; ALTER TABLE students CHANGE birthday weight float UNSIGNED NOT NULL; ALTER TABLE students ADD UNIQUE KEY (name); ALTER TABLE students ADD INDEX myindex (gender,age); SHOW INDEX FROM students; ALTER TABLE students DROP weight; ALTER TABLE students DROP myindex; CREATE INDEX myindex on students (gender,age); SHOW INDEX FROM students; DROP INDEX myindex on students; ALTER TABLE students MODIFY name varchar(20) NOT NULL; INSERT INTO students (id,name,gender,age) VALUES (1,'Yang Guo','m','21'),(2,'Guo Xiang','f','18'); INSERT INTO students VALUES (3,'Zhang Wuji','m','25'),(4,'Zhao Min','f','19'); SELECT * FROM students; SELECT id,name FROM students WHERE id>2; SELECT id as sid,name as sname FROM students ORDER BY age DESC; INSERT INTO students VALUES (5,'Xiao Xiao','m',27); UPDATE students set name='Xiao Zhiqi' WHERE id=5; DELETE FROM students WHERE id=5; CREATE USER user1@'192.168.1.0/24' IDENTIFIED BY '123456'; use mysql; SELECT User,Host,Password FROM user; SHOW GRANTS FOR user1@'192.168.1.0/24'; GRANT ALL PRIVILEGES ON mydatabase.* TO user1@'192.168.1.0/24'; SHOW GRANTS FOR user1@'192.168.1.0/24'; DROP USER user1@'192.168.1.0/24'; SELECT User,Host,Password FROM user; GRANT ALL PRIVILEGES ON *.* TO 'user2'@'192.168.1.%' IDENTIFIED BY '123456'; UPDATE mysql.user set passwd=PASSWORD('12345') WHERE User=user2; SELECT User,Host,Password FROM user; DROP USER user2@'192.168.1.%'; SHOW GRANTS FOR CURRENT_USER; DROP DATABASE mydatabase; SHOW DATABASES;
SQL实例：（照着敲就行了）
把下列数据导入数据库
-- MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64) -- -- Host: localhost Database: hellodb -- ------------------------------------------------------ -- Server version 5.5.33-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!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 */; -- -- Current Database: `hellodb` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `hellodb`; -- -- Table structure for table `classes` -- DROP TABLE IF EXISTS `classes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `classes` ( `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `Class` varchar(100) DEFAULT NULL, `NumOfStu` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ClassID`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `classes` -- LOCK TABLES `classes` WRITE; /*!40000 ALTER TABLE `classes` DISABLE KEYS */; INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15); /*!40000 ALTER TABLE `classes` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `coc` -- DROP TABLE IF EXISTS `coc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `coc` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `ClassID` tinyint(3) unsigned NOT NULL, `CourseID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `coc` -- LOCK TABLES `coc` WRITE; /*!40000 ALTER TABLE `coc` DISABLE KEYS */; INSERT INTO `coc` VALUES (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3); /*!40000 ALTER TABLE `coc` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `courses` -- DROP TABLE IF EXISTS `courses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `courses` ( `CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `Course` varchar(100) NOT NULL, PRIMARY KEY (`CourseID`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `courses` -- LOCK TABLES `courses` WRITE; /*!40000 ALTER TABLE `courses` DISABLE KEYS */; INSERT INTO `courses` VALUES (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7,'Dagou Bangfa'); /*!40000 ALTER TABLE `courses` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `scores` -- DROP TABLE IF EXISTS `scores`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `scores` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `StuID` int(10) unsigned NOT NULL, `CourseID` smallint(5) unsigned NOT NULL, `Score` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `scores` -- LOCK TABLES `scores` WRITE; /*!40000 ALTER TABLE `scores` DISABLE KEYS */; INSERT INTO `scores` VALUES (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6,1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93); /*!40000 ALTER TABLE `scores` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `students` -- DROP TABLE IF EXISTS `students`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `students` -- LOCK TABLES `students` WRITE; /*!40000 ALTER TABLE `students` DISABLE KEYS */; INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5,'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL); /*!40000 ALTER TABLE `students` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `teachers` -- DROP TABLE IF EXISTS `teachers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `teachers` ( `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(100) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') DEFAULT NULL, PRIMARY KEY (`TID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `teachers` -- LOCK TABLES `teachers` WRITE; /*!40000 ALTER TABLE `teachers` DISABLE KEYS */; INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F'); /*!40000 ALTER TABLE `teachers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `toc` -- DROP TABLE IF EXISTS `toc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `toc` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CourseID` smallint(5) unsigned DEFAULT NULL, `TID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `toc` -- LOCK TABLES `toc` WRITE; /*!40000 ALTER TABLE `toc` DISABLE KEYS */; /*!40000 ALTER TABLE `toc` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2013-09-03 2:51:27
题目：
1.在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄 SELECT Name,Age FROM students WHERE Age > 25 AND Gender='m'; 2.以ClassID为分组依据，显示每组的平均年龄 SELECT avg(Age),ClassID FROM students GROUP BY ClassID; 3.显示第2题中平均年龄大于30的分组及平均年龄 SELECT avg(Age),ClassID FROM students GROUP BY ClassID HAVING avg(Age)>30; 4.显示以L开头的名字的同学的相关信息 SELECT * FROM students WHERE Name LIKE 'L%'; 5.显示TeacherID非空的同学的相关信息 SELECT * FROM students WHERE TeacherID IS NOT NULL; 6.以年龄排序后，显示年龄最大的前10位同学的信息 SELECT * FROM students ORDER BY Age LIMIT 10; 7.查询年龄大于等于20岁，小于等于25岁的同学的信息，用三种方法 SELECT * FROM students WHERE Age >=20 and Age<=25; SELECT * FROM students WHERE Age BETWEEN 20 AND 25; SELECT * FROM students WHERE Age >= 20 ORDER BY Age LIMIT 11; 8.交叉连接 SELECT * FROM students,teachers; 9.外连接（左右连接）以左为基准空数据不显示，以右为基准空数据不显示 SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID; SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.ClassID; 10.等值连接 SELECT s.Name,t.Name FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID; 11.自连接 SELECT s.name,t.name FROM students AS s,students AS t WHERE s.TeacherID=t.StuID; 12.联合查询 SELECT * FROM students UNION SELECT * FROM teachers;
