MySQL常用操作

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