mysql入门笔记


数据库最基本的数据类型是:字符串类型、数值类型、日期和时间类型 1. 重启 Mysql 启动:输入 net start mysql 停止:输入 net stop mysql 2. 定长字符串: CHAR(n) 在定长数据类型里,通常使用空格来填充数量不足的字符。 3. 变长字符串:VARCHAR(n),n 是一个数字,表示字段里能够保存的最多字符数量. 4. 大对象类型:BLOB 和 TEXT BLOB 是二进制大对象,它的数据是很长的二进制字符串(字节串)。BLOB 适合在数据库 里存储二进制媒体文件,比如图像和 MP3 TEXT 数据类型是一种长字符串类型,可以被看做一个大 VARCHAR 字段,通常用于在数 据库里保存大字符集,比如博客站点的 HTML 输入。 数值类型:一般包括 NUMBER、INTEGER、REAL、DECIMAL 等 1. 下面是 SQL 的标准: BIT(n) BIT VARING(n) DECIMAL(p,S) INTEGER SMALLINT BIGINT DOUBLE PRECISION(p,s) REAL(s) P 表示字段的最大长度。s 表示小数点后面的位数 2. SQL 实现中一个通用的数值类型是 NUMERIC,它符合 ANSI 标准。数值可以是 0、正值、 负值、定点数和浮点数。MySQL 不支持 NUMERIC NUMERIC(5),这个命令把字段能够接受的最大值限制为 99 999 3. 小数类型:是指包含小数点的数值,DECIMAL(p,s) 4. 整数:不包含小数点的数值(包括整数和负数) 5. 浮点数是有效位数和标度都可变并且没有限制的小数数值 数据类型 REAL 代表单精度浮点数值,有效位数 1-21 DOUBLE PRECISION 表示双精度浮点数值,有效位数 22-53 MySQL 支持的数值类型: 6. 日期和时间数据类型用于保存日期和时间信息,标准 SQL 支持 DATETIME 数据类型,它 包含以下类型: DATE TIME DATETIME TIMESTAMP DATETIME 数据类型的元素包括: YEAR MONTH DAY HOUR MINUTE SECOND 7. 直义字符串 直义字符串就是一系列字符,比如姓名或电话号码,这是由用户或程序直接指定的。 实际上并不需要把字段指定为直义字符串数据类型,而是指定字符串 8. NULL 表示没有值 在使用 NULL 数据类型时,需要明确它表示相应字段不是必须要输入数据的。如果某个 字段必须包含数据,就把它设置为 NOT NULL。只要字段有可能不包含数据,最好就把 它设置为 NULL 9. 布尔值:布尔值的取值范围是 TRUE、FALSE 和 NULL,用于进行数据比较 10. 自定义类型:是由用户定义的类型,它允许用户根据已有的数据类型来指定自己的数据 类型,从而满足数据存储的需要。CREATE TYPE 用于创建自定义类型 MySql 不支持 CREATE TYPE PERSON AS OBJECT (NAME VARCHAR(30), SSN VARCHAR(9)); 引用上面的自定义类型: CREATE TABLE EMP_PAY (EMPLOYEE PERSON, SALARY DECIMAL(10,2), HIRE_DATE DATE); 11. 一般来说,列的名称应该是连续的字符串,一般使用下划线作为分隔符。 12. ALTER TABLE 命令:在表被创建后,使用 ALTER TABLE 命令对其进行修改。可以添加列、 删除列、修改列定义、添加和去除约束,在某些实现中还可以修改表 STORAGE 值。 1) 修改表名: mysql> alter table student rename person; 这里的 student 是原名,person 是修改过后的名字 用 rename 来重命名,也可以使用 rename to 2) 修改字段的数据类型 :长度、类型 Mysql>alter table person modify name varchar(20) 此处 modify 后面的 name 为字段名,我们将原来的 varchar(25)改为 varchar(20) 3) 修改字段名: Mysql> alter table person change stu_name name varchar(25); 这里 stu_name 是原名,name 是新名 需要注意的是不管改不改数据类型,后面的数据类型都要写 如果不修改数据类型写成原来的数据类型即可 我们同样可以使用 change 来达到 modify 的效果,只需在其后写一样的字段名 4) 删除行: Delete from table_name [where condition] 如: Delete from orders_tbl Where ord_num = ‘23A16’; 5) 删除列: ALTER TABLE [表名字] DROP [列名称] 6) 增加列: ALTER TABLE[表名字] ADD [列名称] varchar(20) not null…. 7) 在表头添加字段: Alter table person add name primary key first; 默认情况下添加字段都是添加到表尾,在添加语句后面加上 first 就能添加到表头 8) 在指定位置添加字段 Alter table person add birth date after name 这里添加一条新字段放在 name 字段后面 9) 修改字段到第一个位置 Alter table person modify id int first 此处注意要在字段后面写数据类型 10) 修改字段到指定位置: Alter table person modify name varchar(25) after id 我们把 name 放到了 id 后面,此处的 varchar(25)要写全,varchar 不行 13. 添加列:如果表已包含数据,这时添加的列就不能定义为 NOT NULL,这是一条基本规 则。 强行向表添加一列的方法: 1) 添加一列,把它定义为 NULL 2) 给这个新列在每条记录里都插入数据 3) 把列的定义修改为 NOT NULL 14. 添加自动增加的列。MySQL 提供了 SERIAL 方法为表生成真正的唯一值,如: CREATE TABLE TEST_INCREMENT( ID SERIAL, TEST_NAME VARCHAR(20)); 15. 列的默认属性是 NULL 16. 从现有表新建另一个表: Create table new_table_name as Select(*|column1, colum2) From table_name (where) 17. 显示表结构:mysql>DESCRIBE 表名字 18. 查询表的所有字段:mysql>SELECT *FROM 表名字 19. 删除表:如果使用了 RESTRICT 选项,并且表被视图或约束所引用,DROP 语句就会返 回一个错误。当使用了 CASCADE 选项时,删除操作会成功执行,而且全部引用视图和 约束都被删除。 Drop table table_name (restrict|cascade) 20. 主键: CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL PRIMARY KEY, …….); 或 CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL, ……, PRIMARY KEY(EMP_ID)); 21. 唯一性约束:要求表里某个字段的值在每条记录里都是唯一的。 CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL PRIMARY KEY, EMP_NAME VARCHAR(40) NOT NULL, EMP_PHONE INT(10) NULL UNIQUE, …….); 22. MySQL 中没有 NUMBER 数据类型 23. 外键:是子表里的一个字段,引用父表里的主键。在下面这个范例,EMP_ID 字段被定 义为表 EMPLOYEE_PAY_TBL 的外键。 CREATE TABLE EMPLOYEE_PAY_TBL( EMP_ID CHAR(9) NOT NULL, …… CONSTRATINT EMP_ID_FK FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID)); 利用 ALTER TABLE 命令可以向表里添加外键,如下: ALTER TABLE EMPLOYEE_PAY_TBL ADD CONSTRAINT ID_FK FORENGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID); 24. 检查约束:用于检查输入到特定字段的数据的有效性。可以提供后端的数据库编辑 CREATE TABLE EMPLOYEE_CHECK_TBL( EMP_ID CHAR(10) NOT NULL, …… EMP_ZIP INTEGER(5) NOT NULL, …… PRIMARY KEY(EMP_ID), CONSTRAINT CHK_EMP_ZIP CHECK (EMP_ZIP = ‘46243’)); 表里的 EMP_ZIP 字段设置了检查约束,确保了输入到这个表里的全部雇员的 ZIP 代码 都是“46234”。 若想利用检查约束来确保 ZIP 代码属于某个值列表,可以如下: CONSTRAINT CHK_EMP_ZIP CHECK(EMP_ZIP in (‘46234’,’46227’,’46754’)); 25. 去除约束:利用 ALTER TABLE 命令里的 DROP CONSTRAINT 选项可以去除已经定义的约 束:ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEE_PK 在 MySQL 中还可以使用如下命令去除主键约束: ALTER TABLE EMPLOYEES DROP PRIMARY KEY; 删除外键 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; 26. INSERT 语句可以把数据插入到表,基本语法如下: INSERT INTO TABLE_NAME VALUES(‘value1’,’value2’,[null]); 使用这种语法时,必须在 VALUES 列表里包含表里的每个列。在这个列表里,每个值之 间以逗号分隔。字符、日期和时间数据类型的值必须以单引号包围,而数值或 NULL 值 就不必了 27. 使用多个值表,可以一次提供多行数据。 Mysql>insert into worker values(‘tom’,’tom@yahoo.com’),(‘paul’,’paul@yahoo.com’) 28. 将一个表里的数据插入到另一个表 29. 查询命令查看表 PRODUCTS_TBL 里的全部数据 SELECT *FROM PRODUCTS_TBL; 30. 给表中特定列插入数据的语法如下: INSERT INTO TABLE_NAME(‘COLUMN1’,’COLUMN2’) VALUES(‘VALUE1’,’VALUE2’); 31. 从另一个表插入数据的语法: INSERT INTO TABLE_NAME[(‘COLUMN1’,’COLUMN2’)] SELECT[*|(‘COLUMN1’,’COLUM2’)] FROM TABLE_NAME [WHERE CONDITION(S)]; 如:INSERT INTO PRODUCTS_TMP SELECT *FROM PRODUCTS_TBL; 如:INSERT INTO TEST2 (EMP_ID, LAST_NAME) 这里有括号 SELECT EMP_ID,LAST_NAME 这里没有括号 FROM EMPLOYEE_TBL; 32. 插入 NULL 值: INSERT INTO TABLE_NAME VALUES (‘COLUMN1’, NULL, ‘COLUMN3’); 33. 更新一列数据 UPDATE TABLE_NAME SET COLUMN_NAME = ‘VALUE’ [WHERE CONDITION]; 34. 使用一条 UPDATE 语句更新多个字段,语法如下: UPDATE TABLE_NAME SET COLUMN1=’VALUE’. [COLUMN2 = ‘VALUE’,] [COLUMN3 = ‘VALUE’] [WHERE CONDITION UPDATE ORDERS_TBL SET QTY =1, CUST_ID =’221’ WHERE ORD_NUM = ‘23A16’; 35. DELETE 命令用于从表里删除整行的数据。它不能删除某一列的数据,而是删除行里全 部字段的数据。 DELETE FROM TABLE_NAME [WHERE CONDITION]; 如: DELETE FROM ORDERS_TBL WHERE ORD_NUM = ‘23A16’; 36. mysql 默认开启 autocommit 可以通过以下语言查看 1) select>@@autocommit; 2) SHOW VARIABLES LIKE‘%AUTOCOMMIT’; 3) SHOW GLOBAL STATUS LIKE ‚%AUTOCOMMIT‛ 通过以下方法关闭 autocommit 1)set autocommit = 0; 37. 控制事务的命令 COMMIT、ROLLBACK、SAVEPOINT,事务控制命令只与 DML 命令 INSERT、 UPDATE、DELETE 配合使用 38. COMMIT 命令:用于把事务所做的修改保存到数据库,语法:COMMIT [WORK]; 39. ROLLBACK 命令:用于撤销还没有被保存到数据库的命令,语法:ROLLBACK [WORK]; 40. SAVEPOINT 命令:保存点是事务过程中的一个逻辑点,我们可以把事务回退到这个点, 而不必回退整个事务:savepoint savepoint_name.这个命令就是在事务语句之间创 建一个保存点。 41. ROLLBACK TO SAVEPOINT:回退到保存点,ROLLBACK TO SAVEPOINT_NAME 42. RELEASE SAVEPOINT savepoint_name;删除创建的保存点。 43. SET TRANSACTION 命令,用于初始化数据库事务,可以指定事务的特性,如,我们可 以指定事务是只读的或是可读写的,如下: SET TRANSACTION READ WRITE; SET TRANSACTION READ ONLY; 44. 简单的 SELECT 语句的语法如下: SELECT [*|ALL|DISTINCT COLUMN1,COLUMN2] FROM TABLE1[,TABLE2]; *号表示输出结果里包含表里的全部字段。 ALL 用于显示一列的全部值,包括重复值。选项 ALL 是默认的操作方式,这意味着它并 不在 SELECT 语句中明确指定。 DISTINCT 禁止在输出结果里包含重复的行。 45. ORDER BY:它能够以用户指定的列表格式对查询结果进行排列,默认次序是升序(ASC)。 语法如下: SELECT[ALL|*|DISTINCT COLUMN1,COLUM2] FROM TABLE1 [,TABLE2] WHERE [CONDITION1|EXPRESSION1] [AND|OR CONDITION2|EXPRESSION2] ORDER BY COLUMN1|INTEGER [ASC|DESC] ORDER BY 子句里的字段可以缩写为一个整数,这个整数取代了实际的字段名称,表示 字段在关键字 SELECT 之后列表里的位置 SELECT PROD_DESC,PROD_ID,COST FROM PRODUCTS_TBL WHERE COST<2O ORDER BY 1[,2,3]; 1 代表 PROD_DESC,2 代表 PROD_ID,3 代表 COST 46. MySQL 默认大小写不敏感 47. COUNT 函数的语法如下:查询表里的记录数量或是某个字段里值得数量 SELECT COUNT(*) FROM TABLE_NAME; 函数使用一对圆括号来指定目标字段,或是一个星号表示统计表里的全部记录 如果要统计表中特定列所出现得值的种类数,需要在 COUNT 函数中使用 DISTINCT 关键 字,如: SELECT COUNT(DISTINCT PROD_ID) FROM PRODUCTS_TBL; COUNT 函数统计的是行数,不涉及数据类型。行里可以包含任意类型的数据。 48. 从另一个用户表里选择数据:必须拥有相应的权限,否则不能访问。 SELECT EMP_ID FROM ANOTHERUSERNANE.EMPLOYEE_TBL; 49. 使用字段别名 SELECT COLUMN_NAME ALIAS_NAME FROM TABLE_NAME; 50. 比较操作符 1)=:相等 2)<>:不相等,另一种表示方式为!=,在 Mysql 中,这两种方式通用 3)>:大于,<:小于 4)等号可以与小于号和大于号联合使用 51. 逻辑操作符,用于对 SQL 关键字而不是符号进行比较 1) IS NULL:用于与 NULL 之进行比较:WHERE SALARY IS NULL 2) BETWEEN:用于寻找位于一个给定最大值和最小值之间的值,这个最大值和最小值 是包含在内的,WHERE SALARY BETWEE’20000’ AND ’3000’ 3) IN:用于把一个值与一个指定列表进行比较 WHERE SALARY IN(‘2000’,’3000’,’4000’) 4) Like:利用通配符把一个值与类似的值进行比较,通配符有两个 百分号(%):代表 0 个、1 个或多个字符 下划线(_):代表一个数字或字符 WHERE SALARY LIKE’200%’,匹配任何以 200 开头的数值 WHERE SALARY LIKE’_00%’,匹配第二和第三个字符是 0 的值 5) EXISTS:用于搜索指定表里是否存在满足特定条件的记录,如: SELECT COST FROM PRODUCTS_TBL WHERE EXISTS(SELECT COST FROM PRODUCTS_TBL WHERE COST>100); 6) ALL、SOME 和 ANY ALL:用于把一个值与另一个集合里的全部值进行比较 ANY:用于把值与另一个列表里的任意值比较。 SOME 是 ANY 的别名,他们可以互换使用。 SELECT * FROM PRODUCTS_TBL WHERE COST > ALL (SELECT COST FROM PRODUCTS_TBL WHERE COST<10); 52. 连接操作符 1) AND: WHERE EMPLOYEE_ID = ‘333’AND SALARY = ‘20000’ 2) OR:WHERE SALARY = ‘2000’ OR SALARY = ‘3000’ 53. 求反操作符 <>,!= NOT BETWEEN NOT NI NOT LIKE IS NOT NULL NOT EXISTS NOT UNIQUE 54. 算数操作符 1) 加法:+实现 SELECT SALARY + BONUS FROM EMPLOYEE_PAY_TBL; SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY+BONUS>’40000’; 2) 减法:-实现 3) 乘法:*实现 4) 除法:/实现 SELECT SALARY / 10 FROM EMPLOYEE_PAY_TBL; SELECT SALARY FROM PRODUCTS_PAY_TBL WHERE (SALARY / 10) >’40000’; 汇总函数 55. COUNT 函数:用于统计不包含 NULL 值的记录或字段 COUNT[(*)|(DISTINCT|ALL)] (COLUMN NAME) SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE_PAY_ID;统计全部雇员 ID,不包含 NULL SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE_PAY_TAB;只统计不同的行 SELECT COUNT(ALL SALARY) FROM EMPLOYEE_PAY_TBL;统计 SALARY 字段全部行 SELECT COUNT(*) FROM EMPLOYEE_TBL;统计 EMPLOYEE_TBL 的全部行,包含 NULL 56. SUM 函数:返回一组记录中某一个字段值的总和 SUM([DISTINCT] COLUMN NAME) SUM 函数所处理的字段类型必须是数值型的。 SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL;计算薪水的总和 SELECT SUM(DISTINCT SALARY) FROM EMPLOYEE_PAY_TBL;计算不同薪水的总和 如果字段可以转换为数值型则也可以 sum,如果不能而使用 sum 那么结果为 0 57. AVG 函数:计算一组指定记录的平均值。AVG 函数的参数类型必须是数值类型 SELECT AVG(SALARY) FROM EMPLOYEE_PAY_TBL;返回薪水的平均值 SELECT AVG(DISTINCT SALARY) FROM EMPLOYEE_PAY)TBL;返回不同薪水的平均值 SELECT AVG(PAY_RATE),AVG(SALARY) FROM EMPLOYEE_PAY_TBL; 58. MAX/MIN 函数:返回一组记录中某个字段的最大/小值,NULL 值不在计算范围内 MAX([DISTINCT] COLUMN NAME),DISTINCT 没有啥意义,用不用结果都一样 MIN([DISTINCT] COMUMN NAME) SELECT MAX(SALARY) FROM EMPLOYEE_PAY_TBL; MAX/MIN 函数可以根据数据词典,返回字符型数据的最大/小值 注意:在使用汇总函数时,NULL 值是不被计算的,除非以 COUNT(*)形式使用 COUNT 函数。 数据排序与分组 59. GROUP BY:与 SELECT 语句配合使用,把相同的数据划分为组 SELECT COLUMN1,COLUMN2 FROM TABLE1,TABLE2 WHERE CONDITIONS GROUP BY COLUMN1,COLUMN2 ORDER BY COLUM1,COLUMN2 SELECT CITY, AVG(PAY_RATE),AVG(SALARY) FROM EMP_PAY_TBL WHERE CITY IN(‘INDIANAPOLIS’,’WHITELAND’) GROUP BY CITY ORDER BY 2,3; 这个取别名的方式注意下 记住,SELECT 语句里列出的全部字段,除了汇总字段(使用汇总函数的)外,全部都 要出现在 GROUP BY 子句里。 60. CUBE 和 ROLLUP 语句:对分组数据进行小计 ROLLUP GRPUP BY ordered column list of grouping sets WITH ROLLUP 工作方式:在完成基本的分组数据汇总后,按照从右向左的顺序,每次去掉字段列表中 的最后一个字段,再对剩余的字段进行分组统计,并将获得的小计结果插入返回列表, 被去掉的字段使用 NULL 填充。最后,再对全表进行一次统计,所有字段均使用 NULL 填充。 CUBE GROUP BY column list of grouping sets WITH CUBE 工作方式:对分组列表中的所有字段进行排列组合。并根据每一种组合结果,分别进行 统计汇总。最后,CUBE 语句也会对全表进行统计。 61. HAVING:用于告诉 GROUP BY 子句在输出里包含哪些分组。在查询里的位置: SELECT SELECT COLUMN1,COLUMN2 FROM FROM TABLE1,TABLE2 WHERE WHERE CONDITIONS GROUP BY GROUP BY COLUMN1,COLUMN2 HAVING HAVING CONDITIONS ORDER BY ORDER BY COLUMN1,COLUMN2 HAVING 子句必须跟在 GROUP BY 子句之后、在 ORDER BY 子句之前 调整数据的外观 62. 字符函数: 1) 串接函数 SELECT CONCAT(‘YANG’,’LI’,’YAN’); 2) TRANSLATE 函数:Mysql 不支持哦 SELECT CITY,TRANSLATE(CITY,’IND’,’ABC’) FROM EMPLOYEE_TBL 将 CITY 列中的 I 换为 A,N 换为 B,Y 换为 C 3) REPLACE:把某个字符或字符串替换为指定的一个字符(或多个字符)。 SELECT PEPLACE(CITY,’I’,’Z’) FROM EMPLOYEE_TBL 返回雇员表里的全部城市,并且把城市名称里的 I 都替换为 Z 4) UPPER:把字符串里的小写字母转化为大写 SELECT UPPER(CITY) FROM EMPLOYEE_TBL; 5) LOWER:把字符串里的大写字符转化为小写 LOWER(character string) SELECT LOWER(CITY) FROM EMPLOYEE_TBL; 6) SUBSTRING: 获 取 字 符 串 子 串 SUBSTRING(COLUMN NAME,STARING POSITION,LENGTH) SELECT SUBSTRING(EMP_ID,1,3) FROM EMPLOYEE_TBL; 返回 EMP_ID 的前 3 个字符 7) INSTR:用于在字符串里寻找指定的字符集,返回其所在的位置 INSTR(字段名,字 符串) SELECT PROD_DESC, INSTR(PROD_DESC,’A’) 查找字母 A 在字段 PROD_DESC 里第一次出现得位置 8) LTRIM(s)删除字符串左侧的空格,然后返回字符串 SELECT(‘ WWW.BAIKE369.COM’); 9) RTRIM(s),删除字符串 s 右侧的空格,然后返回字符串 10) TRIM(s), 删除字符串 s 两侧的空格,然后返回字符串 11) LENGTH:用于得到字符串、数字、日期或表达式的长度,单位是字节 SELECT PROD_DESC, LENGTH(PROD_DESC) FROM PRODUCTS_TBL; 12) IFNULL:用于在一个表达式是 NULL 时从另一个表达式获得值 IFNULL(‘VALUE’,’SUBSTITUTION’); SELECT PAGER, IFNULL(PAGER,999)。如果 PAGER 是 NULL,则换成 999 FROM EMPLOYEE_TBL; 13) COALESCE 函数:返回参数中的第一个非空表达式,,当有 N 个参数时选取第一个 非空值(从左到右) SELECT COALESCE(NULL,’CARROT’,’APPLE’); 返回结果:CARROT 也可以与 IFNULL 一样:COALESCE(PAGER,999),如果 PAGER 是 NULL,则换成 999 14) LPAD:LPAD(str, len, padstr),返回字符串 str,左填充用字符串 padstr 填 充到 len 字符长度。如果 str 为大于 len 长,返回值被缩短至 len 个字符 SELECT LPAD(‘HI’, 4,’??’); 结果为??HI 15) RPAD:右填充,同 LPAD 63. 算数函数,最常见的算数函数包括: 绝对值:ABS 舍入:ROUND 平方根:SQRT 符号:SIGN 幂:POWER 上限和下限(CEIL、FLOOR) 指数(EXP) SIN、COS、TAN 大多数算数函数的语法是:FUNCTION(EXPRESSION) 64. 转换函数: 1) 字符串转换为数字 字符串转换为数字:对于要转换为数值的字符串来说,其中的字符必须是 0~9,另 外加号、减号和句点可以分别用来表示正数、负数和小数。 2) 数字转换为字符串 3) 数值数据类型与字符串数据类型的两个主要的区别 算数表达式和函数可以用于数值; 在输出结果里,数值是右对齐的,而字符串是左对齐的 日期和时间 65. MySQL 的日期类型 66. 日期函数 1) 当前日期,当前日期保存在数据库所在的计算机上时,被称为系统日期。 SELECT NOW(); 从服务器显示当前日期:SELECT CURRENT_DATE; 2) 时区:如果在维护数据时需要考虑时区问题,我们就需要处理时区和进行时间转换 (如果 SQL 实现里有这样的函数) 3) 时间与日期相加 SELECT DATE_HIRE,DATE_ADD(DATE_HIRE, INTERVAL 1 DAY), DATE_HIRE +1 FROM EMPLOYEE_PAY_TBL; 3) 其他日期函数: 67. 日期转换 1) 日期描述 常见日期元素 2) 日期转换为字符串,要注意大小写 DATE_FORMAT(DATE,’%y-%m-%d); Select date_format(‘2013-03-09’,’%y-%m-%d); 4) 字符串转换为日期 68. EXTRACT 函数 EXTRACT()函数用户返回日期/时间的单独部分,比如年、月、日、小时、分钟等等 语法:EXTRACT(UNIT FROM DATE) 例:SELECT EMP_ID, EXTRACT(YEAR FOM DATE_HIRE) FROM EMPLOYEE_PAY_TBL; 建立复杂的数据库查询 在查询里结合表 69. 结合条件的位置 SELECT 和 FROM 是 SQL 语句的必要句子,在结合表时,WHERE 子句是必要的,要结合 的表列在 FROM 子句里,而结合是 WHERE 子句里完成的 70. 等值结合/内部结合 利用通用字段结合两个表,而这两个字段通常是每个表里的主键 71. 使用表的别名 这是一种临时性的改变,表在数据库里的实际名称不会受到影响。 让表具有别名是完成自结合的必要条件。 给表起别名同时也意味着被选择字段必须用表的别名加以修饰 72. 不等值结合 根据同一个字段在两个表里值不行等来实现结合 73. 外部结合 外部结合会返回一个表里的全部记录,即使对应的记录在第二个表里不存在。外部结合 被划分为左外部结合,右外部结合和全外部结合 74. 自结合 利用表别名在 SQL 语句对表进行重命名,像处理两个表一样把表结合到自身 75. 结合多个主键 76. 使用基表 需要从两个表里获取数据,但他们又没有公用字段,就必须结合另一个表,这个表与前 两个表都有公用字段,这个表就被称为基表。 基表用于结合具有公用字段的一个或多个表,或是结合没有公用字段的多个表 77. 笛卡尔积 笛卡尔积是笛卡尔结合或“无结合”的结果,如果从两个或多个没有结合的表里获取数 据,输出结果就是所有表里的全部记录 使用子查询定义未确定数据 78. 什么是子查询 子查询也被称为嵌套查询,是位于另一个查询的 WHERE 子句里的查询,它返回的数据通 常在主查询里作为一个条件,从而进一步限制数据库返回的数据 79. 操作符 BETWEEN 与子查询的关系 80. 子查询与 INSERT 语句 INSERT 语句,将子查询返回的结果插入到另一个表 81. 子查询与 UPDATE 语句 82. 子查询与 DELETE 语句 83. 嵌套的子查询 子查询可以嵌入到一个子查询里,就像子查询嵌套在普通查询里一样 在嵌套的查询里,最内层的查询先被执行,然后再依次执行行外层的子查询,直到主查 询 84. 关联子查询 关联子查询是依赖主查询里的信息的子查询。这意味着子查询里的表可以与主查询里的 表相关联。 SELECT C.CUST_NAME FROM CUSTOMER_TBL C WHERE 10 < (SELECT SUM(O.QTY FROM ORDERS_TBL O WHERE O.CUST_ID = C.CUST_ID); 组合多个查询 85. 组合查询操作符 1) UNION:可以组合两个或更多个 SELECT 语句的结果,不包含重复的记录。 在使用 UNION 操作符时,每个 SELECT 语句里必须选择同样数量的字段、同样数量 的字段表达式、同样的数据类型、同样的次序---但长度不必一样 在使用 UNION 操作符时,第一个查询决定了输出的字段名称 2) UNION ALL:可以组合两个 SELECT 语句的结果,并且包含重复的结果,使用规则和 UNION 一样 3) INSERSECT 可以组合两个 SELECT 语句,但只返回第一个 SELECT 语句里与第二个 SELECT 语句里一样的记录。其使用规则与 UNION 操作符一样。MYSQL 不支持哦 4) EXCEPT:组合两个 SELECT 语句,返回第一个 SELECT 语句里有但第二个 SELECT 语句里没有的记录,其使用规则与 UNION 操作符一样,目前 MYSQL 不支持。 5) 组合查询里使用 ORDER BY 用于对全部查询结果的排序,组合查询虽然可能包含多个查询或 SELECT 语句,但 只能有一个 ORDER BY 子句,而且它只能以别名或数字来引用字段 组合查询的结果以每个查询的第一个字段进行排序: 6) 组合查询里使用 GROUP BY GROUP BY 子句可以用于组合查询中的每一个 SELECT 语句,也可用于全部查询结果。 另外,HAVING 子句也可以用于组合查询里的每个 SELECT 语句 SQL 性能调整 利用索引改善性能 86. CREATE INDEX 命令 CREATE INDEX INDEX_NAME ON TABLE_NAME 87. 索引的类型 1) 单字段索引:是基于一个字段创建的,其基本语法如下所示 CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) 2) 唯一索引:用于改善性能和保证数据完整性。唯一索引不允许表里具有重复值 允许 NULL 值的字段上也不能创建唯一索引 CREATE UNION INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) 对于经常在查询的 WHERE 子句里共同使用的字段,组合索引是最有效的 3) 组合索引:是基于一个表里两个或多个字段的索引 CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1,COLUMN2) 5) 隐含索引:是数据库服务程序在创建对象时自动创建的。比如,数据库会为主键约 束和唯一性约束自动创建索引 88. 合适考虑使用索引 一般来说,大多数用于表结合的字段都应该设置索引 经常在 ORDER BY 和 GROUP BY 里引用的字段也应该考虑设置索引 具有大量唯一值的字段,或是在 WHERE 子句里会返回很小部分记录的字段,都可以考虑 设置索引 89. 何时应该避免使用索引 90. 修改索引 ALTER INDEX INDEXZ-NAMEA,深表怀疑呀 91. 删除索引 DROP INDEX INDEX_NAME ON TABLE_NAME; 在探索如何让数据库具有最佳性能时,调整索引是个必要的过程,其中可能包括创建索 引、删除它、最后再创建他(经过修改或不修改) 改善数据库性能 92. 格式化 SQL 语句 1) 为提高可读性格式化 SQL 语句 虽然提高句子的可读性并不会直接改善它的性能,但这样会帮助我们更方便的修改 和调整很长和很复杂的语句 2) FROM 子句里的表 把较小的表列在前面,把较大的表列在后面,就会获得更好的性能。 3) 结合条件的次序 在 WHERE 子句里,来自基表的字段一般放到结合操作的右侧,要被结合的表通常按 照从小到大的次序排列,就像 FROM 子句里表的排列顺序一样 如果没有基表,那表就应该从小到大排列 4) 最严格条件:它是 WHERE 子句里返回最少记录的条件 我们应该让 SQL 优化器首先计算最严格条件,因为它会返回最小的数据子集,从而 减小查询的开销 最严格条件的位置取决于优化器的工作方式 最好使用具有索引的字段作为查询里的最严格条件 5) 全表扫描 在读取大规模的表时,应该避免进行全表扫描 除了确保表里存在索引之外,避免全表扫描的最简单、最明显方法是在查询的 WHERE 子句里设置条件来过滤返回的数据 6) 使用 LIKE 操作符和通配符 Like 操作符,能够以灵活的方式为查询设置条件。在查询里使用通配符能够消除很 多可能返回的记录。 7) 避免使用 OR 操作符 使用谓词 IN 代替 OR 操作符能够提高数据检索速度 8) 避免使用 HAVING 子句 在可能的情况下,尽量不要在 SQL 语句中使用 HAVING 子句,如果需要使用,则最 好尽可能地使其中的限制条件简单化 9) 避免大规模排序操作 由于大规模排序操作不是总可以避免的,所以最好把大规模排序在批处理过程里, 在数据库使用的非繁忙期运行,从而避免影响大多数用户进程的性能 10) 使用存储过程 可以为经常运行的 SQL 语句(特别是大型事务或查询)创建存储过程。所谓存储过 程就是经过编译的、可执行格式永久保存在数据库里的 SQL 语句 11) 在批加载时关闭索引 12) 基于成本的优化 13) 性能工具 很多关系型数据库具有内置工具用于 SQL 语句和数据库性能调整 使用 SQL 管理用户和安全 管理数据库用户 93. 用户的类型 数据输入员 程序员 系统工程师 数据库管理员 系统分析员 开发人员 测试人员 管理者 终端用户 94. 创建用户 CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’; 说明:username 是将创建的用户名,host-指定该用户在哪个主机上可以登录,如果 是本地用户可用 localhost,如果想让该用户可以从任意远程主机登录,可以使用通配%。 Password-该用户的登录密码,密码可以为空,如果为空则该用户可以不需要密码登录 服务器 95. 授权 命令:GRANT privileges ON databasename.tablename TO ‘username’@’host’ 说明:privileges-用户的操作权限,如 SELECT,INSERT,UPDATE 等。如果要授予所有的权限 则使用 ALL;datebasename-数据库,tablename-表名,如果要授予该用户对所有数据库 和表的相应操作权限则可用*表示,如*.*. GRANT SELECT, INSERT ON test.user TO ‘pig’@’%’; GRANT ALL ON *.* TO ‘pig’@’%’; 96. 设置与更改用户密码 命令:SET PASSWORD FOR ‘username’@’host’ = PASSWORD(‘newpassword’); 如果是当前登录用户:SET PASSWORD = PASSWORD(‘newpassword’); 例如:SET PASSWORD FOR ‘pig’@’%’ = PASSWORD(‘123456’) 97. 撤销用户权限 命令:REVOKE privilege ON databasename.tablename FROM ‘username’@’host’; 例:REVOKE SELECT ON *.*FROM ‘pig’@’%’; 98. 删除用户 DROP USER ‘username’@’host’; 99. 显示数据库中所有用户 SELECT user from mysql.user; Select user, host, password from mysql.user; 100. 改 MySQL 用户名 Mysql 新设置用户或更改密码后需用 flush privileges 刷新 MySQL 的系统权限相关表,否 则会出现拒绝访问,还有一种方法,就是重新启动 mysql 服务器,来使新设置生效 101. 创建规划、删除规划 MySQL 不支持 CREATE SCHEMA 命令,在 MySQL 里,规划被看作一个数据库,所以我们 要使用 CREATE DATABASE 命令来创建一个规划,然后在其中创建对象 管理数据库安全 1. 权限 用户可以连接到数据库并不意味着可以访问数据库里的数据,要访问数据库里的数据还 需要权限。权限有两种类型,一种是系统权限,一种是对象权限 2. GRANT GRANT 命令用于向现有数据库用户账户授予系统级和对象级权限 语法: 1) GRANT OPTION 是个功能强大的 GRANT 选项。当对象的所有者利用 GRANT OPTION 把自己对象的权限授予另一个用户阿,这个用户还可以把这个对象的权限授予其他 用户,尽管他并不是这个对象的所有者 GRANT SELECT ON EMPLOYEE_TBL TO USER1 WITH GRANT OPTION; 2) ADMIN OPTION 使用 ADMIN OPTION 授予权限之后,用户不仅拥有了权限,也具有了把这个权限授 予其他用户的功能。这一点和 GRANT OPTION 类似。但 GRANT OPTION 用于对象级 权限,而 ADMIN OPTION 用于系统级权限 GRANT CREATE TABLE TO USER1 WITH ADMIN OPTION; 3. REVOKE 权限。 4.权限组 有些实现可以再数据库里形成权限组。这些权限组通过不同的名称来引用,通过使用权 限组,我们可以方便地给用户授予或撤销权限 5.剩下的暂时不看喽,接下来是复习前面的
还剩24页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

杨饭饭

贡献于2016-11-28

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf