1.查询A，B表共有部分内容： SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key 2.查询A表所有和AB表共有部分： SELECT <select_list> FROM TableA A LEFT JOIN Table B B ON A.Key = B.Key 3.查询B表所有和AB表共有部分： SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key 4.查询A表独有部分内容： SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = TableB B ON A.Key = B.Key WHERE B.Key IS NULL 5.查询B表独有部分: SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL 6.查询AB表所有部分： SELECT <sclect_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key 7.查询AB表各自独有部分 SELECT<select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL
mysql 替换函数replace()实现mysql替换指定字段中的字符串mysql 替换字符串的实现方法:mysql中replace函数直接替换mysql数据库中某字段中的特定字符串，不再需要自己写函数去替换，用起来非常的方便。 mysql 替换函数replace()UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` LIKE '%from_str%' 说明：table_name —— 表的名字 field_name —— 字段名 from_str —— 需要替换的字符串 to_str —— 替换成的字符串 例如：mysql> SELECT REPLACE('www.looaon.com', 'www', 'http://looaon.com'); -> 'https://www.looaon.com' 该函数是多字节安全的,也就是说你不用考虑是中文字符还是英文字符.
AWS RDS上，可以使用mysqlbinlog来下载binlog，具体命令为：mysqlbinlog \ --read-from-remote-server \ --host=db-endpoint \ --port=3306 \ --user UserName \ --password \ --raw \ --result-file=/tmp/ \ binlog.xxxxx 需要注意Amazon RDS会随时清除binlog内容，并不会持久的保持binlog在数据库实例上，因此如果您可能需要使用 mysql.rds_set_configuration 这个存储过程来修改保持数据库保持binlog的时间，以便您有足够的时间下载binlog。在更改binlog保留时间后，请注意您数据库实例的存储空间，以确保binlog不会占用太多存储空间。 如果需要设置binlog的轮转时间为1天call mysql.rds_set_configuration('binlog retention hours', 24); 通过以下命令查看现在的轮转周期是多少call mysql.rds_show_configuration;
mysql.rds_kill_query_id 终止针对MySQL或MariaDB服务器运行的查询。语法CALL mysql.rds_kill_query_id(queryID); 参数queryID 整数。将终止的查询的标识。使用说明要终止针对 MariaDB 服务器运行的查询，请使用 mysql.rds_kill_query_id 过程并传入该查询的 ID。要获取查询 ID，如下所示：SELECT USER, HOST, COMMAND, TIME, STATE, INFO, QUERY_ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = '<user name> 以下示例终止查询 ID 为 11195102 的查询：call mysql.rds_kill_query_id(11195102); 批量kill session的语句:select concat('CALL mysql.rds_kill(',id,');') from information_schema.processlist where user='gwmmm'; select concat('CALL mysql.rds_kill(',trx_mysql_thread_id,');') from information_schema.innodb_trx; CALL mysql.rds_kill( 11195230); CALL mysql.rds_kill( 11195102); CALL mysql.rds_kill( 11195114); CALL mysql.rds_kill( 11195108); CALL mysql.rds_kill( 11195179); CALL mysql.rds_kill( 11195224); CALL mysql.rds_kill( 11195214); CALL mysql.rds_kill( 11194577); CALL mysql.rds_kill( 11195476); CALL mysql.rds_kill( 11194995); CALL mysql.rds_kill( 11195187); CALL mysql.rds_kill( 11195022); CALL mysql.rds_kill( 11195319);
1. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。 2. show databases; -- 显示mysql中所有数据库的名称。 3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。 4. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。 5. show index from table_name; -- 显示表的索引。 6. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。 7. show variables; -- 显示系统变量的名称和值。 8. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。 9. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 10. show privileges; -- 显示服务器所支持的不同权限。 11. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 12. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 13. show engines; -- 显示安装以后可用的存储引擎和默认引擎。 14. show innodb status; -- 显示innoDB存储引擎的状态。 15. show logs; -- 显示BDB存储引擎的日志。 16. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 17. show errors; -- 只显示最后一个执行语句所产生的错误。 18. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。 mysql show命令 show databases; show tables from db_name; show columns from table_name from db_name; show index from talbe_name [from db_name]; show status; show variables; show [full] processlist; show table status [from db_name]; show grants for user; 除了status,processlist和grants外，其它的都可以带有like wild选项，它可以使用SQL的'%'和'_'字符； show databases like '%t'; 将会列出所有数据库名字末尾为't'字符的数据库 当然了，在这些sql中，你也可以用db_name.table_name来代替 table_name from db_name这样写会更简便些! 如果一个用户没有一个表的任何权限，表将不在SHOW TABLES或mysqlshow db_name中的输出中显示 大家可能还记得describe table_name ，它实现的是与show columns from db_name.table_name一样的效果 show status将可以用mysqlshow --status 来得到同样的效果 列 含义 Name 表名 Type 表的类型 (ISAM，MyISAM或HEAP) Row_format 行存储格式 (固定, 动态, 或压缩） Rows 行数量 Avg_row_length 平均行长度 Data_length 数据文件的长度 Max_data_length 数据文件的最大长度 Index_length 索引文件的长度 Data_free 已分配但未使用了字节数 Auto_increment 下一个 autoincrement(自动加1）值 Create_time 表被创造的时间 Update_time 数据文件最后更新的时间 Check_time 最后对表运行一个检查的时间 Create_options 与CREATE TABLE一起使用的额外选项 Comment 当创造表时，使用的注释 (或为什么MySQL不能存取表信息的一些信息)。 SHOW FIELDS是SHOW COLUMNS一个同义词，SHOW KEYS是SHOW INDEX一个同义词。你也可以用mysqlshow db_name tbl_name或mysqlshow -k db_name tbl_name 列出一张表的列或索引。 SHOW INDEX以非常相似于ODBC的SQLStatistics调用的格式返回索引信息。下面的列被返回： 列 含义 Table 表名 Non_unique 0，如果索引不能包含重复。 Key_name 索引名 Seq_in_index 索引中的列顺序号, 从 1 开始。 Column_name 列名。 Collation 列怎样在索引中被排序。在MySQL中，这可以有值A（升序) 或NULL（不排序)。 Cardinality 索引中唯一值的数量。这可通过运行isamchk -a更改. Sub_part 如果列只是部分被索引，索引字符的数量。NULL，如果整个键被索引。
c:/users/administrator/documents/code/facebookads/public/ 关于多线程死锁，以前对这个概念总是很朦胧，不知道具体该怎么理解。前不久从网上看到一篇文章，感觉写的很透彻，很形象，现在摘录下来以备日后省查，希望也能对其他人起到帮助。俗话说的好，人多好办事！在程序当中也是这样，如果在同一个应用程序中需要并行处理多件任务，那就可以创建多条线程。但是人多了，往往也会出现冲突，使得这个工作无法进行下去了，（三个和尚没水喝啊），这就是死锁。举个形象的例子，就像三个人（A,B,C）在玩三个球（1,2,3），规则很简单，每个人都必须先拿到自己左手边的球，才能拿自己右边的球，两手都有球之后，才能把球放下。这个游戏看起来似乎可以永远进行下去，但是若干局之后，如果三个人刚好都只拿到自己左手边的球，都等着拿右手边的球，但是因为谁都不能放手，那么这三个人（线程）都将陷入无尽的等待中了，这就是传说中的死锁。下面就用JAVA来举例，例子中已经创建了三个boolean型的静态变量ball1、ball2、ball3（初始值为false），TURE代表球被拿起，FALSE代表球仍放在地上，接下来就是三个线程类：public class test { static class ThreadA extends Thread //A的线程 { @Override public void run() { // TODO Auto-generated method stub //super.run(); while(true)//无限循环 { while(ball3==true)//如果ball3已被拿起，贼进入等待。 {} ball3=true;//当ball3被放下后，立刻拿起 while(ball1==true)//如果ball1已被拿起，则进入等待。 {} ball1=true;//拿起ball1. System.out.println("A已经拿到两球");//输出结果，方便观察死锁状态 ball1=ball3=false;//放下两球 } } } static class ThreadB extends Thread //B的线程 { @Override public void run() { // TODO Auto-generated method stub //super.run(); while(true)//无限循环 { while(ball1==true)//如果ball1已被拿起，贼进入等待。 {} ball1=true;//当ball1被放下后，立刻拿起 while(ball2==true)//如果ball2已被拿起，则进入等待。 {} ball2=true;//拿起ball1. System.out.println("B已经拿到两球");//输出结果，方便观察死锁状态 ball1=ball2=false;//放下两球 } } } static class ThreadC extends Thread //C的线程 { @Override public void run() { // TODO Auto-generated method stub //super.run(); while(true)//无限循环 { while(ball2==true)//如果ball2已被拿起，贼进入等待。 {} ball2=true;//当ball2被放下后，立刻拿起 while(ball3==true)//如果ball3已被拿起，则进入等待。 {} ball3=true;//拿起ball3. System.out.println("C已经拿到两球");//输出结果，方便观察死锁状态 ball2=ball3=false;//放下两球 } } } /** * @param args */ private static boolean ball1,ball2,ball3=false; public static void main(String[] args) { // TODO Auto-generated method stub ThreadA A=new ThreadA(); ThreadB B=new ThreadB(); ThreadC C=new ThreadC(); A.start(); B.start(); C.start(); } } 运行这个程序,你会看到有若干行打印信息后,就不再有输出,那么就说明它“死锁”了。那么我们如何来消除“死锁”呢?首先,让我们来看看产生“死锁”的必要条件:互斥，就是说多个线程不能同时使用同一资源,比如,当线程A使用该资源时,B线程只能等待A释放后才能使用。占有等待，就是某线程必须同时拥有N个资源才能完成任务，否则它将占用已经拥有的资源直到拥有他所需的所有资源为止，就好像游戏中，必须两个球都拿到了，才能释放；3.非剥夺，就是说所有线程的优先级都相同，不能在别的线程没有释放资源的情况下，夺走其已占有的资源；4.循环等待，就是没有资源满足的线程无限期的等待。到了这，有的读者已经明白了，只要打破这几个必要条件，就能打破“死锁”！那么先来看看互斥：要打破这个条件，就是要让多个线程能共享资源，就相当于A和B能同时举起ball1一样，当然在这个例子里我们可以这样修改规则，但是在其他程序中就不一定能了，比如说一个“读”线程，一个“写”线程，他们都能操作同一文件。这种情况下，我们就不能“又读又写”文件，否则有可能会读到脏数据！因此我们很少从这方面考虑。占有等待：打破占有等待，只要当检测到自己所需的资源仍被别的线程占用，即释放自己已占有的资源（毫不利己，专门利人，呵呵~），或者在经过一段时间的等待后，还未得到所需资源，才释放，这就能打破占有等待。我们可以把While（true）中的代码改一下（以A为例）：Outer：While（true） { int i=0； while（ball3==true）{}//如果ball3已被拿起，则进入等待 ball3=true；//当ball3被放下后，立刻拿起。 while（ball1==true） { i++； if(i==1000)//当计数达到1000后还未得到ball1，则放下ball3，并重新开始。 { ball3=false； break Outer; } ball1=true;//拿起ball1 System.out.println(“A已经拿到两球！”)//为了方便观察死锁现象。 ball1=ball3=false;//然后放下两球 } } 其他两个线程也是如此，即可打破占有等待;非剥夺：打破非剥夺，只要给线程指定一个优先级即可。比如例子中，我们设优先级从高到低为A、B、C，即当A需要ball3，而C正占有它，但是A的优先级比C高，那么C必须马上释放ball3.同理，A对B、B对C也是如此。代码修改如下：复制代码public class test { static class ThreadA extends Thread //A的线程，优先级最高。 { @Override public void run() { // TODO Auto-generated method stub //super.run(); while(true)//无限循环 { while(ball3==true)//如果ball3已被C拿起，贼进入等待。 { ball3=false;//强迫C放下ball3 } ball3=true;//当ball3被放下后，立刻拿起 while(ball1==true)//如果ball1已被B拿起，则进入等待。 {ball1=false;}//强迫B放下ball1 ball1=true;//拿起ball1. System.out.println("A已经拿到两球");//输出结果，方便观察死锁状态 ball1=ball3=false;//放下两球 } } } static class ThreadB extends Thread //B的线程，优先级第二 { @Override public void run() { // TODO Auto-generated method stub //super.run(); while(true)//无限循环 { while(ball1==true)//如果ball1已被A拿起，贼进入等待。 {} ball1=true;//当ball1被放下后，立刻拿起 while(ball2==true)//如果ball2已被C拿起，则进入等待。 {ball2=false;}//强迫C放下ball2 ball2=true;//拿起ball1. System.out.println("B已经拿到两球");//输出结果，方便观察死锁状态 ball1=ball2=false;//放下两球 } } } static class ThreadC extends Thread //C的线程，优先级最低 { @Override public void run() { // TODO Auto-generated method stub //super.run(); while(true)//无限循环 { while(ball2==true)//如果ball2已被拿起，贼进入等待。 {} ball2=true;//当ball2被放下后，立刻拿起 while(ball3==true)//如果ball3已被拿起，则进入等待。 {} ball3=true;//拿起ball3. System.out.println("C已经拿到两球");//输出结果，方便观察死锁状态 ball2=ball3=false;//放下两球 } } } /** * @param args */ private static boolean ball1,ball2,ball3=false; public static void main(String[] args) { // TODO Auto-generated method stub ThreadA A=new ThreadA(); ThreadB B=new ThreadB(); ThreadC C=new ThreadC(); A.start(); B.start(); C.start(); } } 通过这样的修改我们就能打破“非剥夺”最后的循环等待的解决方法其实和占有等待是一样的，都是等待一段时间后释放资源
mysql导入导出sql文件window下1.导出整个数据库mysqldump -u 用户名 -p 数据库名 > 导出的文件名 mysqldump -u dbuser -p dbname > dbname.sql 2.导出一个表mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 mysqldump -u dbuser -p dbname users> dbname_users.sql 3.导出一个数据库结构mysqldump -u dbuser -p -d --add-drop-table dbname >d:/dbname_db.sql -d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table 4.导入数据库常用source 命令 进入mysql数据库控制台，如 mysql -u root -p mysql>use 数据库 然后使用source命令，后面参数为脚本文件(如这里用到的.sql) mysql>source d:/dbname.sql 导入数据到数据库mysql -uroot -D数据库名导入数据到数据库中得某个表mysql -uroot -D数据库名 表名D:\APMServ5.2.6\MySQL5.1\bin>mysqldump -u root -p erp lightinthebox_tags > ligh tinthebox.sql linux下一、导出数据库用mysqldump命令（注意mysql的安装路径，即此命令的路径）：1、导出数据和表结构：mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql #/usr/local/mysql/bin/ mysqldump -uroot -p abc > abc.sql 敲回车后会提示输入密码2、只导出表结构mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql #/usr/local/mysql/bin/ mysqldump -uroot -p -d abc > abc.sql 注：/usr/local/mysql/bin/ ---> mysql的data目录二、导入数据库1、首先建空数据库mysql>create database abc; 2、导入数据库方法一：（1）选择数据库mysql>use abc; （2）设置数据库编码mysql>set names utf8; （3）导入数据（注意sql文件的路径）mysql>source /home/abc/abc.sql; 方法二：mysql -u用户名 -p密码 数据库名 < 数据库名.sql #mysql -uabc_f -p abc < abc.sql
Windows服务 启动MySQL net start mysql -- 创建Windows服务 sc create mysql binPath= mysqld_bin_path(注意：等号与值之间有空格) 连接与断开服务器mysql -h 地址 -P 端口 -u 用户名 -p 密码 SHOW PROCESSLIST -- 显示哪些线程正在运行 SHOW VARIABLES -- 显示系统变量信息 数据库操作 查看当前数据库 SELECT DATABASE(); -- 显示当前时间、用户名、数据库版本 SELECT now(), user(), version(); -- 创建库 CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项 数据库选项： CHARACTER SET charset_name COLLATE collation_name -- 查看已有库 SHOW DATABASES[ LIKE 'PATTERN'] -- 查看当前库信息 SHOW CREATE DATABASE 数据库名 -- 修改库的选项信息 ALTER DATABASE 库名 选项信息 -- 删除库 DROP DATABASE[ IF EXISTS] 数据库名 同时删除该数据库相关的目录及其目录内容 表的操作-- 创建表 CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项] 每个字段必须有数据类型 最后一个字段后不能有逗号 TEMPORARY 临时表，会话结束时表自动消失 对于字段的定义： 字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] -- 表选项 -- 字符集 CHARSET = charset_name 如果表没有设定，则使用数据库字符集 -- 存储引擎 ENGINE = engine_name 表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同 常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive 不同的引擎在保存表的结构和数据时采用不同的方式 MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引 InnoDB表文件含义：.frm表定义，表空间数据和日志文件 SHOW ENGINES -- 显示存储引擎的状态信息 SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息 -- 自增起始数 AUTO_INCREMENT = 行数 -- 数据文件目录 DATA DIRECTORY = '目录' -- 索引文件目录 INDEX DIRECTORY = '目录' -- 表注释 COMMENT = 'string' -- 分区选项 PARTITION BY ... (详细见手册) -- 查看所有表 SHOW TABLES[ LIKE 'pattern'] SHOW TABLES FROM 表名 -- 查看表机构 SHOW CREATE TABLE 表名 （信息更详细） DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] -- 修改表 -- 修改表本身的选项 ALTER TABLE 表名 表的选项 eg: ALTER TABLE 表名 ENGINE=MYISAM; -- 对表进行重命名 RENAME TABLE 原表名 TO 新表名 RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库） -- RENAME可以交换两个表名 -- 修改表的字段机构（13.1.2. ALTER TABLE语法） ALTER TABLE 表名 操作名 -- 操作名 ADD[ COLUMN] 字段定义 -- 增加字段 AFTER 字段名 -- 表示增加在该字段名后面 FIRST -- 表示增加在第一个 ADD PRIMARY KEY(字段名) -- 创建主键 ADD UNIQUE [索引名] (字段名)-- 创建唯一索引 ADD INDEX [索引名] (字段名) -- 创建普通索引 DROP[ COLUMN] 字段名 -- 删除字段 MODIFY[ COLUMN] 字段名 字段属性 -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上) CHANGE[ COLUMN] 原字段名 新字段名 字段属性 -- 支持对字段名修改 DROP PRIMARY KEY -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性) DROP INDEX 索引名 -- 删除索引 DROP FOREIGN KEY 外键 -- 删除外键 -- 删除表 DROP TABLE[ IF EXISTS] 表名 ... -- 清空表数据 TRUNCATE [TABLE] 表名 -- 复制表结构 CREATE TABLE 表名 LIKE 要复制的表名 -- 复制表结构和数据 CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名 -- 检查表是否有错误 CHECK TABLE tbl_name [, tbl_name] ... [option] ... -- 优化表 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... -- 修复表 REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM] -- 分析表 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... 数据操作 增 INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...] -- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。 -- 可同时插入多条数据记录！ REPLACE 与 INSERT 完全一样，可互换。 INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...] -- 查 SELECT 字段列表 FROM 表名[ 其他子句] -- 可来自多个表的多个字段 -- 其他子句可以不使用 -- 字段列表可以用*代替，表示所有字段 -- 删 DELETE FROM 表名[ 删除条件子句] 没有条件子句，则会删除全部 -- 改 UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件] 字符集编码 -- MySQL、数据库、表、字段均可设置编码 -- 数据编码与客户端编码不需一致 SHOW VARIABLES LIKE 'character_set_%' -- 查看所有字符集编码项 character_set_client 客户端向服务器发送数据时使用的编码 character_set_results 服务器端将结果返回给客户端所使用的编码 character_set_connection 连接层编码 SET 变量名 = 变量值 SET character_set_client = gbk; SET character_set_results = gbk; SET character_set_connection = gbk; SET NAMES GBK; -- 相当于完成以上三个设置 -- 校对集 校对集用以排序 SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] 查看所有字符集 SHOW COLLATION [LIKE 'pattern'] 查看所有校对集 CHARSET 字符集编码 设置字符集编码 COLLATE 校对集编码 设置校对集编码 数据类型（列类型）1. 数值类型 -- a. 整型 ---------- 类型 字节 范围（有符号位） tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255 smallint 2字节 -32768 ~ 32767 mediumint 3字节 -8388608 ~ 8388607 int 4字节 bigint 8字节 int(M) M表示总位数 - 默认存在符号位，unsigned 属性修改 - 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改 例：int(5) 插入一个数'123'，补填后为'00123' - 在满足要求的情况下，越小越好。 - 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。 -- b. 浮点型 ---------- 类型 字节 范围 float(单精度) 4字节 double(双精度) 8字节 浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。 不同于整型，前后均会补填0. 定义浮点型时，需指定总位数和小数位数。 float(M, D) double(M, D) M表示总位数，D表示小数位数。 M和D的大小会决定浮点数的范围。不同于整型的固定范围。 M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。 支持科学计数法表示。 浮点数表示近似值。 -- c. 定点数 ---------- decimal -- 可变长度 decimal(M, D) M也表示总位数，D表示小数位数。 保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。 将浮点数转换为字符串来保存，每9位数字保存为4个字节。 2. 字符串类型 -- a. char, varchar ---------- char 定长字符串，速度快，但浪费空间 varchar 变长字符串，速度慢，但节省空间 M表示能存储的最大长度，此长度是字符数，非字节数。 不同的编码，所占用的空间不同。 char,最多255个字符，与编码无关。 varchar,最多65535字符，与编码有关。 一条有效记录最大不能超过65535个字节。 utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符 varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。 varchar 的最大有效长度由最大行大小和使用的字符集确定。 最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。 例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3 -- b. blob, text ---------- blob 二进制字符串（字节字符串） tinyblob, blob, mediumblob, longblob text 非二进制字符串（字符字符串） tinytext, text, mediumtext, longtext text 在定义时，不需要定义长度，也不会计算总长度。 text 类型在定义时，不可给default值 -- c. binary, varbinary ---------- 类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。 char, varchar, text 对应 binary, varbinary, blob. 3. 日期时间类型 一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。 datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59 date 3字节 日期 1000-01-01 到 9999-12-31 timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07 time 3字节 时间 -838:59:59 到 838:59:59 year 1字节 年份 1901 - 2155 datetime YYYY-MM-DD hh:mm:ss timestamp YY-MM-DD hh:mm:ss YYYYMMDDhhmmss YYMMDDhhmmss YYYYMMDDhhmmss YYMMDDhhmmss date YYYY-MM-DD YY-MM-DD YYYYMMDD YYMMDD YYYYMMDD YYMMDD time hh:mm:ss hhmmss hhmmss year YYYY YY YYYY YY 4. 枚举和集合 -- 枚举(enum) ---------- enum(val1, val2, val3...) 在已知的值中进行单选。最大数量为65535. 枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。 表现为字符串类型，存储却是整型。 NULL值的索引是NULL。 空字符串错误值的索引值是0。 -- 集合（set） ---------- set(val1, val2, val3...) create table tab ( gender set('男', '女', '无') ); insert into tab values ('男, 女'); 最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。 当创建表时，SET成员值的尾部空格将自动被删除。 选择类型 PHP角度 1. 功能满足 2. 存储空间尽量小，处理效率更高 3. 考虑兼容问题 -- IP存储 ---------- 1. 只需存储，可用字符串 2. 如果需计算，查找等，可存储为4个字节的无符号int，即unsigned 1) PHP函数转换 ip2long可转换为整型，但会出现携带符号问题。需格式化为无符号的整型。 利用sprintf函数格式化字符串 sprintf("%u", ip2long('192.168.3.134')); 然后用long2ip将整型转回IP字符串 2) MySQL函数转换(无符号整型，UNSIGNED) INET_ATON('127.0.0.1') 将IP转为整型 INET_NTOA(2130706433) 将整型转为IP 列属性（列约束）1. PRIMARY 主键 - 能唯一标识记录的字段，可以作为主键。 - 一个表只能有一个主键。 - 主键具有唯一性。 - 声明字段时，用 primary key 标识。 也可以在字段列表之后声明 例：create table tab ( id int, stu varchar(10), primary key (id)); - 主键字段的值不能为null。 - 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。 例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age)); 2. UNIQUE 唯一索引（唯一约束） 使得某字段的值也不能重复。 3. NULL 约束 null不是数据类型，是列的一个属性。 表示当前列是否可以为null，表示什么都没有。 null, 允许为空。默认。 not null, 不允许为空。 insert into tab values (null, 'val'); -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null 4. DEFAULT 默认值属性 当前字段的默认值。 insert into tab values (default, 'val'); -- 此时表示强制使用默认值。 create table tab ( add_time timestamp default current_timestamp ); -- 表示将当前时间的时间戳设为默认值。 current_date, current_time 5. AUTO_INCREMENT 自动增长约束 自动增长必须为索引（主键或unique） 只能存在一个字段为自动增长。 默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x; 6. COMMENT 注释 例：create table tab ( id int ) comment '注释内容'; 7. FOREIGN KEY 外键约束 用于限制主表与从表数据完整性。 alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id); -- 将表t1的t1_id外键关联到表t2的id字段。 -- 每个外键都有一个名字，可以通过 constraint 指定 存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。 作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。 MySQL中，可以对InnoDB引擎使用外键约束： ----语法： foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作] 此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。 可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。 如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择： 1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。 2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。 3. restrict，拒绝父表删除和更新。 注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。 建表规范 Normal Format, NF - 每个表保存一个实体信息 - 每个具有一个ID字段作为主键 - ID主键 + 原子表 -- 1NF, 第一范式 字段不能再分，就满足第一范式。 -- 2NF, 第二范式 满足第一范式的前提下，不能出现部分依赖。 消除符合主键就可以避免部分依赖。增加单列关键字。 -- 3NF, 第三范式 满足第二范式的前提下，不能出现传递依赖。 某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。 将一个实体信息的数据放在一个表内实现。 SELECT查询SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT a. select_expr -- 可以用 * 表示所有字段。 select * from tb; -- 可以使用表达式（计算公式、函数调用、字段也是个表达式） select stu, 29+25, now() from tb; -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。 - 使用 as 关键字，也可省略 as. select stu+10 as add10 from tb; b. FROM 子句 用于标识查询来源。 -- 可以为表起别名。使用as关键字。 SELECT * FROM tb1 AS tt, tb2 AS bb; -- from子句后，可以同时出现多个表。 -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。 SELECT * FROM tb1, tb2; -- 向优化符提示如何选择索引 USE INDEX、IGNORE INDEX、FORCE INDEX SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3; c. WHERE 子句 -- 从from获得的数据源中进行筛选。 -- 整型1表示真，0表示假。 -- 表达式由运算符和运算数组成。 -- 运算数：变量（字段）、值、函数返回值 -- 运算符： =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor is/is not 加上ture/false/unknown，检验某个值的真假 <=>与<>功能相同，<=>可用于null比较 d. GROUP BY 子句, 分组子句 GROUP BY 字段/别名 [排序方式] 分组后会进行排序。升序：ASC，降序：DESC 以下[合计函数]需配合 GROUP BY 使用： count 返回不同的非NULL值数目 count(*)、count(字段) sum 求和 max 求最大值 min 求最小值 avg 求平均值 group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。 e. HAVING 子句，条件子句 与 where 功能、用法相同，执行时机不同。 where 在开始时执行检测数据，对原数据进行过滤。 having 对筛选出的结果再次进行过滤。 having 字段必须是查询出来的，where 字段必须是数据表存在的。 where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。 where 不可以使用合计函数。一般需用合计函数才会用 having SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。 f. ORDER BY 子句，排序子句 order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]... 升序：ASC，降序：DESC 支持多个字段的排序。 g. LIMIT 子句，限制结果数量子句 仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。 limit 起始位置, 获取条数 省略第一个参数，表示从索引0开始。limit 获取条数 h. DISTINCT, ALL 选项 distinct 去除重复记录 默认为 all, 全部记录 UNION将多个select查询的结果组合成一个结果集合。 SELECT ... UNION [ALL|DISTINCT] SELECT ... 默认 DISTINCT 方式，即所有返回的行都是唯一的 建议，对每个SELECT查询加上小括号包裹。 ORDER BY 排序时，需加上 LIMIT 进行结合。 需要各select查询的字段数量一样。 每个select查询的字段列表(数量、类型)应一致，因为结果中的字段名以第一条select语句为准。 子查询 子查询需用括号包裹。 -- from型 from后要求是一个表，必须给子查询结果取个别名。 - 简化每个查询内的条件。 - from型需将结果生成一个临时表格，可用以原表的锁定的释放。 - 子查询返回一个表，表型子查询。 select * from (select * from tb where id>0) as subfrom where id>1; -- where型 - 子查询返回一个值，标量子查询。 - 不需要给子查询取别名。 - where子查询内的表，不能直接用以更新。 select * from tb where money = (select max(money) from tb); -- 列子查询 如果子查询结果返回的是一列。 使用 in 或 not in 完成查询 exists 和 not exists 条件 如果子查询返回数据，则返回1或0。常用于判断条件。 select column1 from t1 where exists (select * from t2); -- 行子查询 查询条件是一个行。 select * from t1 where (id, gender) in (select id, gender from t2); 行构造符：(col1, col2, ...) 或 ROW(col1, col2, ...) 行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。 -- 特殊运算符 != all() 相当于 not in = some() 相当于 in。any 是 some 的别名 != some() 不等同于 not in，不等于其中某一个。 all, some 可以配合其他运算符一起使用。 连接查询(join) ---------- 将多个表的字段进行连接，可以指定连接条件。 -- 内连接(inner join) - 默认就是内连接，可省略inner。 - 只有数据存在时才能发送连接。即连接结果不能出现空行。 on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真） 也可用where表示连接条件。 还有 using, 但需字段名相同。 using(字段名) -- 交叉连接 cross join 即，没有条件的内连接。 select * from tb1 cross join tb2; -- 外连接(outer join) - 如果数据不存在，也会出现在连接结果中。 -- 左外连接 left join 如果数据不存在，左表记录会出现，而右表为null填充 -- 右外连接 right join 如果数据不存在，右表记录会出现，而左表为null填充 -- 自然连接(natural join) 自动判断连接条件完成连接。 相当于省略了using，会自动查找相同字段名。 natural join natural left join natural right join select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id; 导出select * into outfile 文件地址 [控制格式] from 表名; -- 导出表数据 load data [local] infile 文件地址 [replace|ignore] into table 表名 [控制格式]; -- 导入数据 生成的数据默认的分隔符是制表符 local未指定，则数据文件必须在服务器上 replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理 -- 控制格式 fields 控制字段格式 默认：fields terminated by '\t' enclosed by '' escaped by '\\' terminated by 'string' -- 终止 enclosed by 'char' -- 包裹 escaped by 'char' -- 转义 -- 示例： SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table; lines 控制行格式 默认：lines terminated by '\n' terminated by 'string' -- 终止 INSERTselect语句获得的数据可以用insert插入。 可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。 或者使用set语法。 INSERT INTO tbl_name SET field=value,...； 可以一次性使用多个值，采用(), (), ();的形式。 INSERT INTO tbl_name VALUES (), (), (); 可以在列值指定时，使用表达式。 INSERT INTO tbl_name VALUES (field_value, 10+10, now()); 可以使用一个特殊值 DEFAULT，表示该列使用默认值。 INSERT INTO tbl_name VALUES (field_value, DEFAULT); 可以通过一个查询的结果，作为需要插入的值。 INSERT INTO tbl_name SELECT ...; 可以指定在插入的值出现主键（或唯一索引）冲突时，更新其他非主键列的信息。 INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE 字段=值, …; DELETEDELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 按照条件删除。where 指定删除的最多记录数。limit 可以通过排序条件删除。order by + limit 支持多表删除，使用类似连接语法。 delete from 需要删除数据多表1，表2 using 表连接操作 条件。 TRUNCATETRUNCATE [TABLE] tbl_name 清空数据 删除重建表 区别： 1，truncate 是删除表再创建，delete 是逐条删除 2，truncate 重置auto_increment的值。而delete不会 3，truncate 不知道删除了几条，而delete知道。 4，当被用于带分区的表时，truncate 会保留分区 备份与还原备份，将数据的结构与表内数据保存起来。 利用 mysqldump 指令完成。 -- 导出 mysqldump [options] db_name [tables] mysqldump [options] ---database DB1 [DB2 DB3...] mysqldump [options] --all--database 1. 导出一张表 mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 2. 导出多张表 mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) 3. 导出所有表 mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql) 4. 导出一个库 mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql) 可以-w携带WHERE条件 -- 导入 1. 在登录mysql的情况下： source 备份文件 2. 在不登录的情况下 mysql -u用户名 -p密码 库名 < 备份文件 视图什么是视图： 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。 视图具有表结构文件，但不存在数据文件。 对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。 视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。 -- 创建视图 CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement - 视图名必须唯一，同时不能与表重名。 - 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。 - 可以指定视图执行的算法，通过ALGORITHM指定。 - column_list如果存在，则数目必须等于SELECT语句检索的列数 -- 查看结构 SHOW CREATE VIEW view_name -- 删除视图 - 删除视图后，数据依然存在。 - 可同时删除多个视图。 DROP VIEW [IF EXISTS] view_name ... -- 修改视图结构 - 一般不修改视图，因为不是所有的更新视图都会映射到表上。 ALTER VIEW view_name [(column_list)] AS select_statement -- 视图作用 1. 简化业务逻辑 2. 对客户端隐藏真实的表结构 -- 视图算法(ALGORITHM) MERGE 合并 将视图的查询语句，与外部查询需要先合并再执行！ TEMPTABLE 临时表 将视图执行完毕后，形成临时表，再做外层查询！ UNDEFINED 未定义(默认)，指的是MySQL自主去选择相应的算法。 事务(transaction)事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。 - 支持连续SQL的集体成功或集体撤销。 - 事务是数据库在数据晚自习方面的一个功能。 - 需要利用 InnoDB 或 BDB 存储引擎，对自动提交的特性支持完成。 - InnoDB被称为事务安全型引擎。 -- 事务开启 START TRANSACTION; 或者 BEGIN; 开启事务后，所有被执行的SQL语句均被认作当前事务内的SQL语句。 -- 事务提交 COMMIT; -- 事务回滚 ROLLBACK; 如果部分操作发生问题，映射到事务开启前。 -- 事务的特性 1. 原子性（Atomicity） 事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 2. 一致性（Consistency） 事务前后数据的完整性必须保持一致。 - 事务开始和结束时，外部数据一致 - 在整个事务过程中，操作是连续的 3. 隔离性（Isolation） 多个用户并发访问数据库时，一个用户的事务不能被其它用户的事物所干扰，多个并发事务之间的数据要相互隔离。 4. 持久性（Durability） 一个事务一旦被提交，它对数据库中的数据改变就是永久性的。 -- 事务的实现 1. 要求是事务支持的表类型 2. 执行一组相关的操作前开启事务 3. 整组操作完成后，都成功，则提交；如果存在失败，选择回滚，则会回到事务开始的备份点。 -- 事务的原理 利用InnoDB的自动提交(autocommit)特性完成。 普通的MySQL执行语句后，当前的数据提交操作均可被其他客户端可见。 而事务是暂时关闭“自动提交”机制，需要commit提交持久化数据操作。 -- 注意 1. 数据定义语言（DDL）语句不能被回滚，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。 2. 事务不能被嵌套 -- 保存点 SAVEPOINT 保存点名称 -- 设置一个事务保存点 ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点 RELEASE SAVEPOINT 保存点名称 -- 删除保存点 -- InnoDB自动提交特性设置 SET autocommit = 0|1; 0表示关闭自动提交，1表示开启自动提交。 - 如果关闭了，那普通操作的结果对其他客户端也不可见，需要commit提交后才能持久化数据操作。 - 也可以关闭自动提交来开启事务。但与START TRANSACTION不同的是， SET autocommit是永久改变服务器的设置，直到下次再次修改该设置。(针对当前连接) 而START TRANSACTION记录开启前的状态，而一旦事务提交或回滚后就需要再次开启事务。(针对当前事务) 锁表表锁定只用于防止其它客户端进行不正当地读取和写入 MyISAM 支持表锁，InnoDB 支持行锁 -- 锁定 LOCK TABLES tbl_name [AS alias] -- 解锁 UNLOCK TABLES 触发器触发程序是与表有关的命名数据库对象，当该表出现特定事件时，将激活该对象。 监听：记录的增加、修改、删除。 -- 创建触发器 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 参数： trigger_time是触发程序的动作时间。它可以是 before 或 after，以指明触发程序是在激活它的语句之前或之后触发。 trigger_event指明了激活触发程序的语句的类型 INSERT：将新行插入表时激活触发程序 UPDATE：更改某一行时激活触发程序 DELETE：从表中删除某一行时激活触发程序 tbl_name：监听的表，必须是永久性的表，不能将触发程序与TEMPORARY表或视图关联起来。 trigger_stmt：当触发程序激活时执行的语句。执行多个语句，可使用BEGIN...END复合语句结构 -- 删除 DROP TRIGGER [schema_name.]trigger_name 可以使用old和new代替旧的和新的数据 更新操作，更新前是old，更新后是new. 删除操作，只有old. 增加操作，只有new. -- 注意 1. 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。 -- 字符连接函数 concat(str1,str2,...]) concat_ws(separator,str1,str2,...) -- 分支语句 if 条件 then 执行语句 elseif 条件 then 执行语句 else 执行语句 end if; -- 修改最外层语句结束符 delimiter 自定义结束符号 SQL语句 自定义结束符号 delimiter ; -- 修改回原来的分号 -- 语句块包裹 begin 语句块 end -- 特殊的执行 1. 只要添加记录，就会触发程序。 2. Insert into on duplicate key update 语法会触发： 如果没有重复记录，会触发 before insert, after insert; 如果有重复记录并更新，会触发 before insert, before update, after update; 如果有重复记录但是没有发生更新，则触发 before insert, before update 3. Replace 语法 如果有记录，则执行 before insert, before delete, after delete, after insert SQL编程// 局部变量 ---------- -- 变量声明 declare var_name[,...] type [default value] 这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。 -- 赋值 使用 set 和 select into 语句为变量赋值。 - 注意：在函数内是可以使用全局变量（用户自定义的变量） --// 全局变量 ---------- -- 定义、赋值 set 语句可以定义并为变量赋值。 set @var = value; 也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。 还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。 select @var:=20; select @v1:=id, @v2=name from t1 limit 1; select * from tbl_name where @var:=30; select into 可以将表中查询获得的数据赋给变量。 -| select max(height) into @max_height from tb; -- 自定义变量名 为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。 @var=10; - 变量被定义后，在整个会话周期都有效（登录到退出） --// 控制结构 ---------- -- if语句 if search_condition then statement_list [elseif search_condition then statement_list] ... [else statement_list] end if; -- case语句 CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END -- while循环 [begin_label:] while search_condition do statement_list end while [end_label]; - 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。 -- 退出循环 退出整个循环 leave 退出当前循环 iterate 通过退出的标签决定退出哪个循环 --// 内置函数 ---------- -- 数值函数 abs(x) -- 绝对值 abs(-10.9) = 10 format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46 ceil(x) -- 向上取整 ceil(10.1) = 11 floor(x) -- 向下取整 floor (10.1) = 10 round(x) -- 四舍五入去整 mod(m, n) -- m%n m mod n 求余 10%3=1 pi() -- 获得圆周率 pow(m, n) -- m^n sqrt(x) -- 算术平方根 rand() -- 随机数 truncate(x, d) -- 截取d位小数 -- 时间日期函数 now(), current_timestamp(); -- 当前日期时间 current_date(); -- 当前日期 current_time(); -- 当前时间 date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分 time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间 unix_timestamp(); -- 获得unix时间戳 from_unixtime(); -- 从时间戳获得时间 -- 字符串函数 length(string) -- string长度，字节 char_length(string) -- string的字符个数 substring(str, position [,length]) -- 从str的position开始,取length个字符 replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str instr(string ,substring) -- 返回substring首次在string中出现的位置 concat(string [,...]) -- 连接字串 charset(str) -- 返回字串字符集 lcase(string) -- 转换成小写 left(string, length) -- 从string2中的左边起取length个字符 load_file(file_name) -- 从文件读取内容 locate(substring, string [,start_position]) -- 同instr,但可指定开始位置 lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length ltrim(string) -- 去除前端空格 repeat(string, count) -- 重复count次 rpad(string, length, pad) --在str后用pad补充,直到长度为length rtrim(string) -- 去除后端空格 strcmp(string1 ,string2) -- 逐字符比较两字串大小 -- 流程函数 case when [condition] then result [when [condition] then result ...] [else result] end 多分支 if(expr1,expr2,expr3) 双分支。 -- 聚合函数 count() sum(); max(); min(); avg(); group_concat() -- 其他常用函数 md5(); default(); --// 存储函数，自定义函数 ---------- -- 新建 CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型 函数体 - 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。 - 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。 - 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。 - 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。 - 多条语句应该使用 begin...end 语句块包含。 - 一定要有 return 返回值语句。 -- 删除 DROP FUNCTION [IF EXISTS] function_name; -- 查看 SHOW FUNCTION STATUS LIKE 'partten' SHOW CREATE FUNCTION function_name; -- 修改 ALTER FUNCTION function_name 函数选项 --// 存储过程，自定义功能 ---------- -- 定义 存储存储过程 是一段代码（过程），存储在数据库中的sql组成。 一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。 而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。 -- 创建 CREATE PROCEDURE sp_name (参数列表) 过程体 参数列表：不同于函数的参数列表，需要指明参数类型 IN，表示输入型 OUT，表示输出型 INOUT，表示混合型 注意，没有返回值。 /* 存储过程 */ ------------------ 存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。 调用：CALL 过程名 -- 注意 - 没有返回值。 - 只能单独调用，不可夹杂在其他语句中 -- 参数 IN|OUT|INOUT 参数名 数据类型 IN 输入：在调用过程中，将数据输入到过程体内部的参数 OUT 输出：在调用过程中，将过程体处理完的结果返回到客户端 INOUT 输入输出：既可输入，也可输出 -- 语法 CREATE PROCEDURE 过程名 (参数列表) BEGIN 过程体 END 用户和权限管理 root密码重置 1. 停止MySQL服务 2. [Linux] /usr/local/mysql/bin/safe_mysqld --skip-grant-tables & [Windows] mysqld --skip-grant-tables 3. use mysql; 4. UPDATE `user` SET PASSWORD=PASSWORD("密码") WHERE `user` = "root"; 5. FLUSH PRIVILEGES; 用户信息表：mysql.user -- 刷新权限 FLUSH PRIVILEGES; -- 增加用户 CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串) - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。 - 只能创建用户，不能赋予权限。 - 用户名，注意引号：如 'user_name'@'192.168.1.1' - 密码也需引号，纯数字密码也要加引号 - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD -- 重命名用户 RENAME USER old_user TO new_user -- 设置密码 SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码 SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码 -- 删除用户 DROP USER 用户名 -- 分配权限/添加用户 GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password'] - all privileges 表示所有权限 - *.* 表示所有库的所有表 - 库名.表名 表示某库下面的某表 GRANT ALL PRIVILEGES ON `pms`.* TO 'pms'@'%' IDENTIFIED BY 'pms0817'; -- 查看权限 SHOW GRANTS FOR 用户名 -- 查看当前用户权限 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER(); -- 撤消权限 REVOKE 权限列表 ON 表名 FROM 用户名 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限 -- 权限层级 -- 要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。 全局层级：全局权限适用于一个给定服务器中的所有数据库，mysql.user GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤销全局权限。 数据库层级：数据库权限适用于一个给定数据库中的所有目标，mysql.db, mysql.host GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。 表层级：表权限适用于一个给定表中的所有列，mysql.talbes_priv GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。 列层级：列权限适用于一个给定表中的单一列，mysql.columns_priv 当使用REVOKE时，您必须指定与被授权列相同的列。 -- 权限列表 ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限 ALTER -- 允许使用ALTER TABLE ALTER ROUTINE -- 更改或取消已存储的子程序 CREATE -- 允许使用CREATE TABLE CREATE ROUTINE -- 创建已存储的子程序 CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 CREATE VIEW -- 允许使用CREATE VIEW DELETE -- 允许使用DELETE DROP -- 允许使用DROP TABLE EXECUTE -- 允许用户运行已存储的子程序 FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE INDEX -- 允许使用CREATE INDEX和DROP INDEX INSERT -- 允许使用INSERT LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES PROCESS -- 允许使用SHOW FULL PROCESSLIST REFERENCES -- 未被实施 RELOAD -- 允许使用FLUSH REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址 REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件） SELECT -- 允许使用SELECT SHOW DATABASES -- 显示所有数据库 SHOW VIEW -- 允许使用SHOW CREATE VIEW SHUTDOWN -- 允许使用mysqladmin shutdown SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。 UPDATE -- 允许使用UPDATE USAGE -- “无权限”的同义词 GRANT OPTION -- 允许授予权限 表维护 分析和存储表的关键字分布 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ... -- 检查一个或多个表是否有错误 CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} -- 整理数据文件的碎片 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... 杂项1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！ 2. 每个库目录存在一个保存当前数据库的选项文件db.opt。 3. 注释： 单行注释 # 注释内容 多行注释 /* 注释内容 */ 单行注释 -- 注释内容 (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）) 4. 模式通配符： _ 任意单个字符 % 任意多个字符，甚至包括零字符 单引号需要进行转义 \' 5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。 6. SQL对大小写不敏感 7. 清除已有语句：\c
有时候我们需要对大数据进行测试，本地一般没有那么多数据，就需要我们自己生成一些。下面会借助内存表的特点进行生成百万条测试数据。 创建一个临时内存表, 做数据插入的时候会比较快些-- 创建一个临时内存表 DROP TABLE IF EXISTS `vote_record_memory`; CREATE TABLE `vote_record_memory` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` varchar(20) NOT NULL DEFAULT '', `vote_num` int(10) unsigned NOT NULL DEFAULT '0', `group_id` int(10) unsigned NOT NULL DEFAULT '0', `status` tinyint(2) unsigned NOT NULL DEFAULT '1', `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `index_user_id` (`user_id`) USING HASH ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 创建一个普通表，用作模拟大数据的测试用例DROP TABLE IF EXISTS `vote_record`; CREATE TABLE `vote_record` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id', `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数', `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户', `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除', `create_time` int(10) unsigned NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间', PRIMARY KEY (`id`), KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表'; 为了数据的随机性和真实性，我们需要创建一个可生成长度为n的随机字符串的函数。--创建生成长度为n的随机字符串的函数 DELIMITER // -- 修改MySQL delimiter：'//' DROP FUNCTION IF EXISTS `rand_string` // SET NAMES utf8 // CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8' BEGIN DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE return_str varchar(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1)); SET i = i+1; END WHILE; RETURN return_str; END // 为了操作方便，我们再创建一个插入数据的存储过程-- 创建插入数据的存储过程 DROP PROCEDURE IF EXISTS `add_vote_record_memory` // CREATE PROCEDURE `add_vote_record_memory`(IN n INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE vote_num INT DEFAULT 0; DECLARE group_id INT DEFAULT 0; DECLARE status TINYINT DEFAULT 1; WHILE i < n DO SET vote_num = FLOOR(1 + RAND() * 10000); SET group_id = FLOOR(0 + RAND()*3); SET status = FLOOR(1 + RAND()*2); INSERT INTO `vote_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW()); SET i = i + 1; END WHILE; END // DELIMITER ; -- 改回默认的 MySQL delimiter：';' 开始执行存储过程，等待生成数据(10W条生成大约需要40分钟)-- 调用存储过程 生成100W条数据 CALL add_vote_record_memory(1000000); 查询内存表已生成记录(为了下步测试，目前仅生成了105645条)SELECT count(*) FROM `vote_record_memory`; -- count(*) -- 105646 把数据从内存表插入到普通表中(10w条数据13s就插入完了)INSERT INTO vote_record SELECT * FROM `vote_record_memory`; 查询普通表已的生成记录SELECT count(*) FROM `vote_record`; -- count(*) -- 105646 如果一次性插入普通表太慢，可以分批插入，这就需要写个存储过程了：-- 参数n是每次要插入的条数 -- lastid是已导入的最大id CREATE PROCEDURE `copy_data_from_tmp`(IN n INT) BEGIN DECLARE lastid INT DEFAULT 0; SELECT MAX(id) INTO lastid FROM `vote_record`; INSERT INTO `vote_record` SELECT * FROM `vote_record_memory` where id > lastid LIMIT n; END 调用存储过程:-- 调用存储过程 插入60w条 CALL copy_data_from_tmp(600000);
No.1 数据库概念 什么是数据库？ 数据库就是一种特殊的文件，内部存储着需要的数据 RDBMS 所谓关系数据库，是建立在关系模型基础的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据 SQL SQL是结构化语言，是一种用来操作关系数据库的数据库语言。 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 MySQL MySQL是一个关系数据库管理系统 特点： 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性 支持多种操作系统，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等 支持多线程，充分利用CPU资源 优化的SQL查询算法，有效地提高查询速度 提供多语言支持，常见的编码如GB2312、BIG5、UTF8 提供TCP/IP、ODBC和JDBC等多种数据库连接途径 提供用于管理、检查、优化数据库操作的管理工具 大型的数据库。可以处理拥有上千万条记录的大型数据库 支持多种存储引擎 MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库 MySQL使用标准的SQL数据语言形式 Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统 在线DDL更改功能 复制全局事务标识 复制无崩溃从机 复制多线程从机 No.2 数据完整性 一个数据库就是一个完整的业务单元，可以包含多张表，在表中为了更加精准的存储数据，保证数据的正确性，可以在创建表的时候，为表增加一些强制性的验证，包括数据类型、约束 数据类型 整数：int，bit 小数：decimal 字符串：varchar,char 日期时间: date, time, datetime 枚举类型(enum) decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位 char表示固定长度的字符串，如char(3)，如果填充ab时会补一个空格 varchar表示可变长度的字符串，如varchar(3)，填充ab时就会存储ab 字符串text表示存储大文本，当字符大于4000时推荐使用 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 日期时间类型 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 约束 主键primary key：物理上存储的顺序 非空not null：此字段不允许填写空值 惟一unique：此字段的值不允许重复 默认default：当不填写此值时会使用默认值，如果填写时以填写为准 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常 No.3 命令行脚本 数据库操作 show databases; 查看数据库 use 数据库名; 使用数据库 select database(); 查看当前使用的数据库 create database 数据库名 charset=utf8； 创建数据库 drop database 数据库名; 删除数据库 数据表操作 show tables; 查看当前数据库中的所有表 desc 表名; 查看表结构 创建表 create table students( id int unsingned primary key auto_increment not null, name varchar(20) default '', age int unsingned default 0, height descimal(3,2) default 1.8, gender enum('男','女') default '男' ) alter table 表名 add 列名 类型; 添加字段 alter table 表名 change 类型及约束; 修改字段 alter table 表名 change 原名 新名 类型及约束; 修改字段(重命名字段) alter table 表名 drop 列名; 删除字段 drop table 表名; 删除表 show create table 表名; 查看表的创建语句 数据增删改查 增 insert into 表名 values(); 全部列插入 insert into 表名(列1,...) values(); 部分列插入 insert into 表名(列1,...) values(),...; 插入多条数据 删 delete from 表名 where 条件; 删除删选出的数据 改 update 表名 set 列1=值1,列2=值2... where 条件; 修改数据 查 select 列1,列2,... from 表名; 查询数据 数据备份与恢复 备份 mysqldump –uroot –p 数据库名 > 数据库名.sql; 恢复 创建新的数据库 ysql -uroot –p 新数据库名 < 数据库名.sql No.4 数据库设计 关系型数据库建议在E-R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步，在开发中有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系，设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成 三范式 经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式 第一范式(1NF) 强调的是列的原子性，即列不能够再分成其他几列 第二范式(2NF) 首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分 第三范式(3NF) 首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况 E-R模型 E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表 R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多 关系也是一种数据，需要通过一个字段存储在表中 实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 逻辑删除 对于重要数据，并不希望物理删除，一旦删除，数据无法找回 删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0 对于非重要数据，可以进行物理删除 数据的重要性，要根据实际开发决定 No.5 MySQL查询 准备测试数据 创建数据库 create database python charset=utf8; 使用数据库 use python; 创建students表 create table students( id int unsigned primary key auto_increment not null, name varchar(20) default '', age tinyint unsigned default 0, height decimal(5,2), gender enum('男','女','中性','保密') default '保密', cls_id int unsigned default 0, is_delete bit default 0 ); 创建classes表 create table classes ( id int unsigned auto_increment primary key not null, name varchar(30) not null ); 向students表中插入数据 insert into students values (0,'小明',18,180.00,2,1,0), (0,'小月月',18,180.00,2,2,1), (0,'彭于晏',29,185.00,1,1,0), (0,'刘德华',59,175.00,1,2,1), (0,'黄蓉',38,160.00,2,1,0), (0,'凤姐',28,150.00,4,2,1), (0,'王祖贤',18,172.00,2,1,1), (0,'周杰伦',36,NULL,1,1,0), (0,'程坤',27,181.00,1,2,0), (0,'刘亦菲',25,166.00,2,2,0), (0,'金星',33,162.00,3,3,1), (0,'静香',12,180.00,2,4,0), (0,'郭靖',12,170.00,1,4,0), (0,'周杰',34,176.00,2,5,0); 向classes表中插入数据 insert into classes values (0, "python_01期"), (0, "python_02期"); 查询所有字段 select * from students; 查询指定字段 select name from students; 使用as给字段起别名 select id as 序号,name as 姓名,gender as 性别 from students; 使用as给表起别名 select s.id,s.name from students as s; 消除重复行 select distinct gender from students; 条件 使用where子句对表中的数据筛选，结果为True的行会出现在结果集中 where后面支持比较运算符、逻辑运算符、模糊查询、范围查询、空判断 比较运算符 等于: = 大于: > 大于等于: >= 小于: < 小于等于: <= 不等于: != 或 <> select * from students where id > 3; 查询id大于3的学生 select * from students where id <= 4; 查询小于等于4的学生 select * from students where name != '黄蓉'; 查询名字不是黄蓉的 select * from students where is_delete=0; 查询没有被删除的 逻辑运算符 and or not select * from students where id > 3 and gender=0; 查询id大于3的女同学 select * from students where id < 4 or is_delete=0; 查询id<4没被删除的同学 模糊查询 like %表示任意多个任意字符 _表示一个任意字符 select * from students where name like '黄%'; 查询姓黄的同学 select * from students where name like '李_'; 查询姓黄并且名字是两个字的同学 select * from students where name like '黄%' or name like '%泽'; 查询姓黄的或者最后一个字是泽的同学 范围查询 in表示在一个非连续的范围内 between ... and ...表示在一个连续的范围内 select * from students where id in(1,4,5); 查询id是1或者4或者5的同学 select * from students where id between 3 and 7; 查询id为3到8的同学 空判断 注意：null与''是不同的 判空is null select * from students where height is null; 查询没有填写身高的同学 select * from students where height is not null; 查询填写了身高的同学 优先级 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符 and比or先运算，如果同时出现并希望先算or，需要结合()使用 排序 语法 select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] 说明 将数据先按照列1进行排序，如果列1相同，按照列2，以此类推 asc从小到大排列，即升序(默认) desc从大到小排序，即降序 select * from students where gender=1 and is_delete=0 order by id asc; 查询未删除的男生升序排列 select * from students order by age desc,height desc；查询所有同学，按年龄排序，年龄相同的按身高排序 聚合 总数 select count(*) from students; 查询表中总共有多少条数据 最大值 select max(age) from students where gender=1; 查询所有男生中最老的 最小值 select min(age) from students where gender=2; 查询所有女生中年龄最小的 求和 select sum(age) from students; 查询所有同学的总年龄 平均值 select avg(height) from students where is_delete=0 and gender=2; 查询未删除的女生的平均身高 分组 group by 将查询结果按照一个或多个字段进行分组，字段值相同的为一组 SELECT gender FROM students GROUP BY gender; group by + group_concat() group_concat(字段名)可以作为一个输出字段来使用，表示分组以后，根据分组查看某字段的集合 select gender,group_concat(name) from students group by gender; +--------+-----------------------------------------------------------+ | gender | group_concat(name) | +--------+-----------------------------------------------------------+ | 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 | | 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 | | 中性 | 金星 | | 保密 | 凤姐 | +--------+-----------------------------------------------------------+ select gender,group_concat(id) from students group by gender; +--------+------------------+ | gender | group_concat(id) | +--------+------------------+ | 男 | 3,4,8,9,14 | | 女 | 1,2,5,7,10,12,13 | | 中性 | 11 | | 保密 | 6 | +--------+------------------+ group by + 聚合函数 可以通过聚合函数来对这个值的集合做一些操聚合操作 select gender,avg(height) from students group by gender; +--------+-------------+ | gender | avg(height) | +--------+-------------+ | 男 | 177.750000 | | 女 | 173.428571 | | 中性 | 162.000000 | | 保密 | 150.000000 | +--------+-------------+ group by + having having表达式，用来分组以后设定条件筛选数据，功能和where一样，但是having只能用于group by select gender,count(*) from students group by gender having count(*)>2; +--------+----------+ | gender | count(*) | +--------+----------+ | 男 | 5 | | 女 | 7 | +--------+----------+ group by + with rollup 在最后增加一行，计算该列的和 select gender,count(*) from students group by gender with rollup; +--------+----------+ | gender | count(*) | +--------+----------+ | 男 | 5 | | 女 | 7 | | 中性 | 1 | | 保密 | 1 | | NULL | 14 | +--------+----------+ 分页 当数据量很大的时候，就不可能在一页中查看所有数据了，需要对它进行分页操作 语法 select * from 表名 limit start,count 说明 从start开始，获取count条数据 select * from where gender=1 limit 0,3; 查询前三条男生记录 连接 mysql支持三种连接查询 内连接 查询的结果为两个表匹配到的数据 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 左连接 查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 右连接 查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充 Python全栈开发当中MySQL是一个难点，今天一文带你掌握 语法 select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列 select * from students inner join classes on students.cls_id = classes.id; 使用内关联查询班级表和学生表 +----+--------+------+--------+--------+--------+-----------+----+-------------+ | id | name | age | height | gender | cls_id | is_delete | id | name | +----+--------+------+--------+--------+--------+-----------+----+-------------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 | +----+--------+------+--------+--------+--------+-----------+----+-------------+ select * from students as s left join classes as c on s.cls_id = c.id; 使用左关联查询班级表和学生表 +----+--------+------+--------+--------+--------+-----------+------+-------------+ | id | name | age | height | gender | cls_id | is_delete | id | name | +----+--------+------+--------+--------+--------+-----------+------+-------------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 | | 11 | 金星 | 33 | 162.00 | 中性 | 3 | | NULL | NULL | | 12 | 静香 | 12 | 180.00 | 女 | 4 | | NULL | NULL | | 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | NULL | NULL | | 14 | 周杰 | 34 | 176.00 | 女 | 5 | | NULL | NULL | +----+--------+------+--------+--------+--------+-----------+------+-------------+ select * from students as s right join classes as c on s.cls_id = c.id; 使用右关联查询班级表和学生表 +------+--------+------+--------+--------+--------+-----------+----+-------------+ | id | name | age | height | gender | cls_id | is_delete | id | name | +------+--------+------+--------+--------+--------+-----------+----+-------------+ | 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 | | 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 | | 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 | | 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 | | 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 | | 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 | | 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 | | 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 | | 9 | 程坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 | | 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 | +------+--------+------+--------+--------+--------+-----------+----+-------------+ select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id; 查询学生姓名及班级名称 +--------+-------------+ | name | name | +--------+-------------+ | 小明 | python_01期 | | 小月月 | python_02期 | | 彭于晏 | python_01期 | | 刘德华 | python_02期 | | 黄蓉 | python_01期 | | 凤姐 | python_02期 | | 王祖贤 | python_01期 | | 周杰伦 | python_01期 | | 程坤 | python_02期 | | 刘亦菲 | python_02期 | +--------+-------------+ 子查询 子查询 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句 主查询 主要查询的对象,第一条 select 语句 主查询和子查询的关系 子查询是嵌入到主查询中 子查询是辅助主查询的,要么充当条件,要么充当数据源 子查询是可以独立存在的语句,是一条完整的 select 语句 子查询分类 标量子查询: 子查询返回的结果是一个数据(一行一列) 列子查询: 返回的结果是一列(一列多行) 行子查询: 返回的结果是一行(一行多列) 查询大于平均年龄的学生 select * from students where age > (select avg(age) from students); 查询还有学生在班的所有班级名字 select name from classes where id in (select cls_id from students); 需求: 查找班级年龄最大,身高最高的学生 select * from students where (height,age) = (select max(height),max(age) from students); 查询的执行顺序 from表名>where>group by>slect distinct>having>order by>limit No.6 Python与MySQL交互 准备数据 创建京东数据库 create database jing_dong charset=utf8; 使用京东数据库 use jing_dong; 创建一个商品goods数据表 create table goods( id int unsigned primary key auto_increment not null, name varchar(150) not null, cate_name varchar(40) not null, brand_name varchar(40) not null, price decimal(10,3) not null default 0, is_show bit not null default 1, is_saleoff bit not null default 0 ); 向goods表中插入数据 insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default); insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default); insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 数据库设计 创建商品分类表 create table goods_cates( id int unsigned primary key auto_increment not null, name varchar(40) not null ); 创建商品品牌表 create table goods_brands ( id int unsigned primary key auto_increment not null, name varchar(40) not null ); 创建商品表 create table goods( id int unsigned primary key auto_increment not null, name varchar(40) default '', price decimal(5,2), cate_id int unsigned, brand_id int unsigned, is_show bit default 1, is_saleoff bit default 0, foreign key(cate_id) references goods_cates(id), foreign key(brand_id) references goods_brands(id) ); 创建顾客表 create table customer( id int unsigned auto_increment primary key not null, name varchar(30) not null, addr varchar(100), tel varchar(11) not null ); 创建订单表 create table orders( id int unsigned auto_increment primary key not null, order_date_time datetime not null, customer_id int unsigned, foreign key(customer_id) references customer(id) ); 创建订单状态表 create table order_detail( id int unsigned auto_increment primary key not null, order_id int unsigned not null, goods_id int unsigned not null, quantity tinyint unsigned not null, foreign key(order_id) references orders(id), foreign key(goods_id) references goods(id) ); PyMySQL操作流程 Connection对象 用于建立与数据库的连接 conn=connect(参数列表) host：连接的mysql主机，如果本机是'localhost' port：连接的mysql主机的端口，默认是3306 database：数据库的名称 user：连接的用户名 password：连接的密码 charset：通信采用的编码方式，推荐使用utf8 close() 关闭连接 commit() 提交 cursor() 返回cursor对象，执行sql并返回结果 Cursor对象 返回cursor对象，执行sql并返回结果 rowcount只读属性，表示最近一次execute()执行后受影响的行数 connection获得当前连接对象 close()关闭 execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句 fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组 fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回 增删改查 增删改 import pymysql if __name__ == '__main__': conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8') cursor = conn.cursor() # 增加 count = cursor.execute('insert into goods(name,cate_name,brand_name) values("硬盘","","")') print(count) count = cursor.execute('insert into goods(name) values("光盘")') print(count) # 更新 count = cursor.execute('update goods set name="机械硬盘" where name="硬盘"') # 删除 count = cursor.execute('delete from goods where id=6') print(count) conn.commit() cursor.close() conn.close() 查询一条数据 import pymysql if __name__ == '__main__': conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8') cursor = conn.cursor() count = cursor.execute('select id,name from goods where id<=4') print(count) for i in range(count): ret = cursor.fetchone() print(ret) conn.commit() cursor.close() conn.close() 查询多条数据 import pymysql if __name__ == '__main__': conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8') cursor = conn.cursor() count = cursor.execute('select id,name from goods where id<=4') print(count) ret = cursor.fetchall() print(ret) conn.commit() cursor.close() conn.close() 参数化 sql语句的参数化，可以有效防止sql注入 import pymysql if __name__ == '__main__': conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8') cursor = conn.cursor() count = cursor.execute('select id,name from goods where id<=%s',4) print(count) ret = cursor.fetchall() print(ret) conn.commit() cursor.close() conn.close() No.7 视图 视图是什么？ 通俗的讲，视图就是一条SELECT语句执行后返回的结果集，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上，视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变），方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性 定义视图 create view 视图名称 as select语句; 查看视图 show tables; 使用视图 select * from 视图名称; 删除视图 drop view 视图名称; 视图的作用 提高了重用性，就像一个函数 对数据库重构，却不影响程序的运行 提高了安全性能，可以对不同的用户 让数据更加清晰 No.8 事务 什么是事务？ 所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位，只有事务内的所有操作全部执行成功才会提交到数据库，只要有一条执行失败，也不会提交，广泛应用于订单系统、银行系统 事务四大特性 原子性(Atomicity) 一致性(Consistency) 隔离性(Isolation) 持久性(Durability) 开启事务 start transaction; 提交事务 commit; 回滚事务 rollback; No.9 索引 什么是索引？ 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针，索引就像一本书的目录，加快查找速度 索引的使用 查看索引 show index from 表名; 创建索引 create index 索引名称 on 表名(字段名称(长度)) 删除索引 drop index 索引名称 on 表名; No.10 权限 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表 数据库级别账号：对特定数据库执行增删改查的所有操作 数据表级别账号：对特定表执行增删改查等所有操作 字段级别的权限：对某些表的特定字段进行操作 存储程序级别的账号：对存储程序进行增删改查的操作 帐户的操作主要包括创建账户、删除账户、修改密码、授权权限等 No.11 主从 主从同步的定义 主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave），因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器，通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表 使用主从同步的好处： 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能 主从同步的机制 Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致 在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句 每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接 主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里） 配置主从同步的步骤 在主服务器上，必须开启二进制日志机制和配置一个独立的ID 在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号 在开始复制进程前，在主服务器上记录二进制文件的位置信息 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件） 配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置 详细配置主从同步的方法 主和从的身份可以自己指定，我们将虚拟机Ubuntu中MySQL作为主服务器，将Windows中的MySQL作为从服务器 备份主服务器原有数据到从服务器 如果在设置主从同步前，主服务器上已有大量数据，可以使用mysqldump进行数据备份并还原到从服务器以实现数据的复制 在主服务器Ubuntu上进行备份，执行命令 mysqldump -uroot -p123456 --all-databases --lock-all-tables > ~/master_db.sql -u ：用户名 -p ：密码 --all-databases ：导出所有数据库 --lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改 ~/master_db.sql :导出的备份数据（sql文件）位置，可自己指定 在从服务器Windows上进行数据还原 mysql –uroot –p123456 < master_db.sql 配置主服务器master 编辑设置mysqld的配置文件，设置log_bin和server-id vim /etc/mysql/mysql.conf.d/mysqld.cnf server-id = 1 log_bin = /var/log/mysql/mysql-bin.log 重启mysql服务 service mysql restart 登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号 mysql –uroot –p123456 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave'; FLUSH PRIVILEGES; 获取主服务器的二进制日志信息 SHOW MASTER STATUS; -File: 使用的日志文件名字 Position: 使用的文件位置 配置从服务器slave（Windows中的MySQL） 找到Windows中MySQL的配置文件 编辑my.ini文件，将server-id修改为2，并保存退出 重启MySQL服务 进入MySQL，设置连接到master主服务器 change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590; master_host：主服务器Ubuntu的ip地址 master_log_file: 前面查询到的主服务器日志文件名 master_log_pos: 前面查询到的主服务器日志文件位置 开启同步，查看同步状态 start slave; 开启同步 show slave status G; 查看同步状态