1.命令行进入mysql:mysql -u root -p ;回车后输入密码即可进入
2.show命令:show databases;显示所有的数据库
show tables;显示选定的数据库的所有表
show columns from student;显示表中的所有列,等同于desc student;
show create database pager;显示创建数据库的sql语句
show create table student;显示创建表的sql语句
3.查询时取出重复记录,即只查询显示出不同的数据:select distinct gender from student;
4.限制结果:
- SELECT * FROM student LIMIT 5;限制查找的结果的个数不能超过多少
- SELECT * FROM student LIMIT 2,3;从行2(0.1.2)数据开始,查询3个,
- SELECT * FROM student LIMIT 3 OFFSET 2;同上
5.使用完全限定名:SELECT DISTINCT
student.
sage
FROM student;查询表名.字段,单引号可以不加
6.排序数据:默认升序排序ASC,想要降序排序则需要在末尾指定关键字 DESC
- SELECT * FROM student ORDER BY sid DESC;根据某一字段进行排序
- SELECT * FROM student ORDER BY sname,sage;也可以根据多个条件进行排序,先按照第一个条件,然后再按照第二个排序,但是如果第一个列的值都唯一第二个条件不会起作用
注: ORDER BY 必须保证放在 FROM 之后,如果再使用 LIMIT ,则 LIMIT 必须放在 ORDER BY 之后
7.过滤数据:
- 使用WHERE子句
-
- 子句操作符:= 、<> 、!= 、< 、> 、<= 、>= 、BETWEEN … AND… 、IS NULL 、
- MySQL在执行匹配时默认不区分大小写
- 使用组合WHERE子句
-
- 条件之间用 AND 或者 OR 连接
- 当有多个条件 组合在一起时,AND的优先级更高,会优先处理AND连接的条件,然后和OR可能会单独处理,此时可以使用圆括号明确的分组操作符,达到目的
- IN操作符:SELECT * FROM student WHERE sid IN(1,2);相当于OR ,但是执行速度快于OR
- NOT操作符:可以和IN用在一起,表示不在其中
8.用通配符进行过滤:
- 通配符:用来匹配值的一部分的特殊字符,本身实际是SQL的WHERE子句中有特殊含义的字符
- 为在搜索子句中使用通配符,必须使用LIKE操作符
- % 通配符:匹配多个字符,SELECT * FROM student WHERE sname LIKE ‘%汤%’;
- _ 通配符:匹配单个字符
9.使用正则表达式:
- 操作符:REGEXP
- 正则表达式不区分大小写,如果需要区分,可使用BINARY关键字,如SELECT * FROM student WHERE sname REGEXP BINARY ‘Tom’; 只会匹配Tom不会匹配tom
- 关于LIKE REGEXP 的区别,可以查看MySQL必知必会p69,需要重新看
- 正则表达式:
-
- .000 -> 表示匹配任意一个字符,比如结果会匹配到1000和2000
- 1000|2000 -> | 为正则表达式的OR 语句
- [123] Ton -> []是另一种形式的OR语句,[123]为[1|2|3]的缩写,得到结果 1 ton ava 、2 ton ava…
- [1-9] -> - 表示范围,[1-9]等价于[123456789]
- \ ->转义
10.创建计算字段:
- 拼接字符串:CONCAT(item1,item2,item3) item之间用逗号隔开。SELECT CONCAT(sid,’(‘,sname,’)’) FROM student; 输出为concar(sid,’(‘,sname,’)’) 1(甄志远) 2(汤浩然)……、
- 当然,可以使用别名,SELECT CONCAT(sid,’(‘,sname,’)’) AS title FROM student; 输出为title 1(甄志远) 2(汤浩然)……
- 计算,如可以计算订单中单价和数量的乘积,select prod_id ,quantity,item_price quantity*item_prize as expanded_prize from orderitems where order_num = 2005; 两个字符串相加会显示0
11.使用数据处理函数
- 文本处理函数
-
- 将文本转换成大写:SELECT UPPER(sname) FROM student;
- 将文本转换成小写:SELECT LOWER(sname) FROM student;
- 去除文本左右空格:LTrim( str )、 RTrim(str)
- 返回串左边的字符:LEFT(str,len)
- 返回串右边的字符:RIGHT(str,len)
- 返回串的长度:LENGTH( str );
- 返回串的子串:SUBSTRING(str,pos) 有多种形式,此形式返回第pos到最后的子串
- 返回串的SOUNDEX:可以理解为发音 如:soundex(cust_contact) = soundex(‘Y Lie’);因为YLee 和Y Lie 的发音相同,故可以查出
12.使用数据处理函数
- 聚集函数:运行在行组上,计算和返回单个值的函数
-
- 返回某列的平均值:AVG()
-
- 如果某行为NULL,则不考虑,即忽略这行,个数不加1,
- 返回某列的行数 :COUNT()
-
- COUNT() 不论行是否为NULL,均计数,注意空不相当于NULL
- COUNT(gender) 如果gender列的某一行的数值为NULL则不计数
- 返回某列的最大值: MAX()
- 返回某列的最小值: MIN()
- 返回某列值之和: SUM()
- 聚集不同值:5及后期版本
-
- 五个聚集函数都可以使用
- 默认对所有的行执行计算,指定ALL或者不指定
- 指定 DISTINCT,只对不同的数据进行计算
- 如果指定列名,则COUNT(DISTINCT )不可用,COUNT(DISTINCT sname)可用
- 组合聚集函数:所有的聚集函数可以组合使用
13.分组数据:
14.使用子查询:
15.联结表
- 用来在一条SELECT语句中关联表,以检索出多个表中的数据
- 如果引用的列可能出现二义性,即两个或两个以上的表拥有相同的列名(字段名),则需要使用完全限定列名,即表名.列名的形式
- 如果不使用WHERE把两个表进行联结,如果进行查询,结果出现笛卡尔积,行数为多个表的行数之积
-
- 等值联结:使用WHERE语句,通过字段的值来形成联结
- 内部联结:SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
16.创建高级联结
- 使用表别名:SELECT a.sid,a.sname,b.smark FROM student a ,mark b WHERE a.sid = b.sid;
-
- 好处:
-
- 缩短SQL语句
- 允许在单条SELECT中多次使用相同的表
- 自联结:利用别名可以实现自联结
- 自然联结:如果想要检索出的数据不出现重复的列,需要自己设定想要显示的列(可以一个表使用通配符 * ,其它表指定要显示的列),直接使用通配符 * 不能保证数据列不出现重复
- 外部联结:SELECT a.sid,a.sname,b.smark FROM student a LEFT JOIN mark b ON a.sid = b.sid;
-
- 在需要关联没有关联行的那些行时使用外部联结
- LEFT JOIN:从FROM左边的表中选择所有行,并显示,并不关心右边表中的值
- RIGHT JOIN:从FROM右边的表中选择所有行,并显示,并不关心表边表中的值
17.组合查询
- 组合多条SELECT查询语句,并将结果作为单个查询结果集返回,这些组合查询同城成为并(union)或复合查询
- 使用方法:在各条语句之间放上关键字UNION,如SELECT * FROM student WHERE sid IN(1,2) UNION SELECT * FROM student WHERE sname LIKE ‘%浩%’
- 使用规则
-
- union必须由两条或两条以上的SELECT语句组成,语句之间用UNION分隔
- UNION中的每个查询必须包含相同的列、表达式或聚集函数(各列不需要以相同的次序列出,不过最好以相同次序列出)
- 列数据类型必须兼容:类型不必完全相同,但是必须可以隐含转换
- 如果只是用UNION,如果出现重复的行会自动取消,如果想返回所有匹配的行,可使用UNION ALL,此时也体现和WHERE语句的区别
- 如果使用排序,不能再在每个语句后加ORDER BY,只能在最后一个语句后加
18.全文本搜索
19.数据插入
- 插入完整的行
-
- 不指定字段名
-
- INSERT INTO student VALUES(1,’汤浩然’,23,’男’);
- 即不指定要插入的字段名,而是依赖定义表的次序进行插入,语法简单但不安全,不推荐使用
- 指定字段名
-
- INSERT INTO student(sid,sname,sage,gender) VALUES(11,’汤浩然’,23,’男’);
- 如果表的定义允许,则可以在INSERT语句中省略某些列,需要满足一下条件
-
- 该列允许NULL值
- 表会给该列指定值
- 因为插入数据可能很耗时(插入大量数据时),可以在INSERT INTO之间插入LOW_PRIORITY,构成INSERT LOW_PRIORITY INTO,以降低插入语句的优先级
- 插入多个行
-
- INSERT INTO student(sid,sname,sage,gender) VALUES(12,’汤浩然’,23,’男’),(13,’汤浩然’,23,’男’);
- 每组值用花括号括起来,中间用逗号分隔
- 插入多个行会比每次插入一组值快
- 搜索插入
-
- INSERT INTO studentnew(ssname,ssage,sgender)SELECT sname,sage,gender FROM student;
- 定义:将搜索的结果插入表中,即SELECT INSERT
- 用处:可以用于从一张表合并到另一张表
- 插入数据时并不要求列名一致,只要求位置 的匹配
20.更新和删除数据
- 更新数据
-
- UPDATE语句由三部分组成
-
- 要更新的表
- 列名和他们的新值
- 确定要更新行的过滤条件
- UPDATE student SET sname = ‘Milly’,gender = ‘女’ WHERE sid = 9
- 更新多列,只需要使用单个SET命令,在“列=值”对之间用逗号分隔
- 更新多行,如果一行或者多行出现错误,则整个UPDATE操作被取消,错误发生前更新的所有行会被恢复到原来的值,如要想要发生错误也继续向下执行,可以使用 UPDATE IGNORE student
- 如果想要删除某个列的值,可设置其为空(允许为空的情况下)
- 删除数据
-
- DELETE FROM student WHERE sid = 11;
- 删除整个表,TRUNCATE TABLE student; 此语句实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据
21.创建和操纵表
- 创建表
-
- 组成
-
- 表名
- 列名及数据类型
- 限制信息
- CREATE TABLE people(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) NOT NULL,
psge INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
- 主键也可以在所有列的后面指定 PRIMARY KEY (pid);
- 自增AUTO_INCREMENT,每个表最多只能有一列为自增,而且它必须被索引(如成为主键)
如果某一列是自增,在插入数据到表后可以使用SELECT LAST_INSERT_ID();查询最后一次插入操作的此列的值,此语句返回最后一个AUTO_INCREMENT值
- 删除表
-
- DROP TABLE student;
- 重命名表
-
- 重命名一个表,RENAME TABLE student TO student2;
- 重命名多个表,RENAME TABLE student TO student2,studentnew TO studentnew2;
- 更新表
-
- 添加新列
-
- ALTER TABLE mark ADD result VARCHAR(10);
- 删除列
-
- ALTER TABLE mark DROP COLUMN result;
- 定义外键
-
- ALTER TABLE mark ADD CONSTRAINT abcd FOREIGN KEY(sid) REFERENCES people(pid);
- 上句中,abcd是一个主键的约束名,可以任取,上句的作用是把mark.sid作为people.pid的外键
22.使用视图
- 定义:视图是虚拟的表,与包含数据的表不同,视图只包含使用时动态检索数据的查询。
- 个人理解:仅仅是用来查看存储在别处的数据的一种设施,本身不包含数据,其返回的数据是从其他表中检索出来的,和Java中函数的封装类似,把某个查询封装到一个视图,以简化代码,使用时不需要知道具体实现。
- 为什么使用视图:
-
- 重用SQL语句
- 简化复杂的SQL操作。在编写查询后,可以方便的使用二不需要知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。即,将返回特定格式的查询封装到视图中,从视图查询数据就可以返回特定格式
- 可以对视图执行SLELCT操作,过滤和排序操作,将视图联结到其他视图或表,甚至还能添加和更新数据
- 注意:ORDER BY 可以用在视图中,但如果从该视图中检索数据SELECT中也含有ORDER BY,那么视图中的ORDER BY 将会被覆盖
- 例子:DROP VIEW mark_people; //删除视图
CREATE VIEW mark_people AS SELECT
sid,CONCAT(sname,’(‘,sage,’)’ ) AS result FROM student; //创建视图
SHOW CREATE VIEW mark_people;//查看创建视图的语句
CREATE OR REPLACE VIEW….;//视图存在,替换原有视图
SELECT sid,result FROM mark_people WHERE
sid = 10;使用视图查询,查询条件使用的字段必须是SELECT语句中查询的字段
23.存储过程
25.使用触发器
- 定义:是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END之间的一组语句)
-
- DELETE
- INSERT
- UPDATE
其它MySQL不支持触发器
- 创建触发器需要给出的四条信息
-
- 唯一�%2