mysql基本操作之增删改查

mysql基本操作之增删改查

 

一、增

　　1.增加（创建）数据库

1、 CREATE DATABASE 数据库名;

2、 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';

3、 SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');

依次执行3个命令完成数据库创建。注意：中文 “密码”和“数据库”是户自己需要设置的。

 　　2.增加表

　　　　最基本：

CREATE TABLE table_name (column_name column_type,column_name column_type,...);

 　　　　创建时，指定InnoDB引擎和utf8编码：

CREATE TABLE table_name (column_name column_type,column_name column_type,...)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 　　3.增加字段

　　　　增加字段：

ALTER TABLE table_name ADD field_name field_type;

　　　　增加默认值：

ALTER TABLE 表名 ALTER 字段名 SET DEFAUTL 值;

 

　　4.增加索引

　　　　普通索引：

alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

 　　　　主键索引：

alter table 表名 add primary key (字段名);

 　　　　唯一索引：

alter table 表名 add unique 索引名 (字段名);

　　5.增加用户

　　　　创建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

　　　　说明：

　　　　　　（1）username：你将创建的用户名

　　　　　　（2）host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

　　　　　　（3）password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

 　　　　

　　　　增加授权：

GRANT privileges ON databasename.tablename TO 'username'@'host'

　　　　说明：

　　　　　　（1）privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL

　　　　　　（2）databasename：数据库名

　　　　　　（3）tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

　　　　创建同时授权：

mysql> grant all privileges on mq.* to test@localhost identified by '1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

PS:必须执行flush privileges; 

否则登录时提示：ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES ) 

 　　6.添加数据

　　　　表中插入数据：

-- 指定字段名，没有指定的字段，将被添加默认值
INSERT INTO 表名 （字段名1，字段名2，...） VALUES(值1，值2，...);
-- 若向表的所有字段添加数据，可以忽略字段名，值按字段顺序排列
INSERT INTO 表名 VALUES(值1，值2，....);

 

二、删

　　1.删数据库

DROP DATABASE 库名;

 　　2.删表

格式：DROP TABLE 表名;

　　　　删除没有被关联的普通表：直接上面的SQL语句就行了

　　　　删除被其他表关联的父表：

　　　　方法一：先删除子表，在删除父表

　　　　方法二：删除父表的外键约束，再删该表

　　3.删字段

ALTER TABLE table_name DROP field_name;

 

　　4.删数据

DELETE FROM table_name [WHERE Clause]

--如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
--你可以在 WHERE 子句中指定任何条件
--您可以在单个表中一次性删除记录。

 

　　5.删索引

　　　　一般索引：

alter table 表名 drop index 索引名;

 　　　　主键索引：

alter table table_name drop primary key;

 　　　　外键：

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

 　　

　　6.清空表

DELETE * FROM 表名;

TRUNCATE TABLE table_name;

 

其中truncate操作中的table可以省略，delete操作中的*可以省略。这两者都是将table_name表中数据清空，不过也是有区别的，如下：

• truncate是整体删除（速度较快）， delete是逐条删除（速度较慢）。
• truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因。
• truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
• 如果只需删除表中的部分记录，只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE ...

　　7.删用户

　　　　删除用户：

DROP USER 'username'@'host';

 　　　　撤销权限：

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

 　　　　　　说明: privilege, databasename, tablename - 同授权部分.

 

三、改

　　1.改表

　　　　修改表名称：

ALTER TABLE 表名 RENAME [TO|AS] 新表名

　　 　　修改表存储引擎：

-- 修改表的存储引擎
-- ALTER TABLE tb_name ENGINE=存储引擎名称
ALTER TABLE user12 ENGINE=MyISAM;

 　　　　修改表自增长值：

-- 修改自增长的值
-- ALTER TABLE tb_name AUTO_INCREMENT=值
ALTER TABLE user12 AUTO_INCREMENT=100;

 

　　2.改字段

　　　　修改字段名称和属性：

-- 修改字段属性
-- ALTER TABLE tb_name MODIFY 字段名称 字段类型 [完整性约束条件]
-- 将email字段 VARCHAR(50)修改成VARCHAR(200)
-- 注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件
ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT 'a@a.com';

　　　　修改字段名称：

　　　　　　修改字段名称时，需要重新提供字段类型和约束条件（是否为空及默认值）

-- 将test字段改为test1
-- ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
　　　　

　　　　调整字段位置：

-- 将card移到test后面
ALTER TABLE user10 MODIFY card CHAR(10) AFTER test;

-- 将test放到第一个，保留原完整性约束条件
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;

 　　3.改数据

　　　　更新数据：

UPDATE 表名
  SET  字段名1=值1[,字段名2=值2，...]
  [WHERE 条件表达式]

　　语法说明：
　　　　字段名1，字段名2，用于指定更新的字段名称
　　　　值1，值2，用于表示字段更新的新数据。
　　　　where条件表达式，可选参数，用于指定更新数据需要满足的条件。

　　　　UPDATE语句在更新表中数据时可 部分、全部更新

 

 四、查

　　https://www.cnblogs.com/irockcode/p/6841669.html　　

　　https://www.cnblogs.com/xiaocen/p/3720358.html

　　1.查数据　　

SELECT语句的完整语法为：
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
说明：
用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。

 　　　　1 FROM子句
　　　　　　FROM子句指定了SELECT语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开)，其中的表达式可为单一表名称、已保存的查询或由 INNER JOIN、LEFT JOIN 或 RIGHT JOIN 得到的复合结果。
　　　　如果表或查询存储在外部数据库，在IN 子句之后指明其完整路径。
　　　　例：下列SQL语句返回所有有定单的客户

SELECT OrderID,Customer.customerID
FROM Orders Customers
WHERE Orders.CustomerID=Customers.CustomeersID

 　　　　（1） ALL、DISTINCT、DISTINCTROW、TOP谓词

　　　　　　(1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。
　　　　　　例：SELECT ALL FirstName,LastName
　　　　　　FROM Employees
　　　　　　(2) DISTINCT 如果有多个记录的选择字段的数据相同，只返回一个。
　　　　　　(3) DISTINCTROW 如果有重复的记录，只返回一个
　　　　　　(4) TOP显示查询头尾若干记录。也可返回记录的百分比，这是要用 TOP N PERCENT子句（其中N 表示百分比）
　　　　　　例：返回5%定货额最大的定单

 

SELECT TOP 5 PERCENT*
FROM [ Order Details]
ORDER BY UnitPrice*Quantity*(1-Discount) DESC

 　　　　（2） 用 AS 子句为字段取别名

　　　　　　如果想为返回的列取一个新的标题，或者，经过对字段的计算或总结之后，产生了一个新的值，希望把它放到一个新的列里显示，则用AS保留。
　　　　　　例：返回FirstName字段取别名为NickName

SELECT FirstName AS NickName ,LastName ,City
FROM Employees 

 　　　　2 WHERE 子句指定查询条件

select * from tb_name where-clause

 　　　　　　（1）where-clause：布尔条件表达式
　　　　　　　　=   |等值比较
　　　　　　　　<=>   |等值比较，包括与NULL的安全比较
　　　　　　　　<>或!=   |不等值比较
　　　　　　　　<，<=，>，>=    |其它比较符
　　　　　　　　IS NULL   |是否为空值
　　　　　　　　IS NOT NULL   |是否不为空值
　　　　　　　　LIKE   |支持的通配符有%和_
　　　　　　　　RLIKE或REGEXP    |正则表达式匹配
　　　　　　　　IN    |判指定字段的值是否在给定的列表中
　　　　　　　　BETWEEN … AND …    | 在某取值范围内 

　　　　　　（2）组合条件：

　　　　　　　　NOT，!    |非
　　　　　　　　AND，&&    | 和
　　　　　　　　OR，||    | 或

                             关系型运算符优先级高到低为：NOT >AND >OR

 

　　　　　　（3）LIKE（模式匹配）

　　　　　　　　LIKE运算符检验一个包含字符串数据的字段值是否匹配一指定模式。
　　　　　　　　LIKE运算符里使用的通配符　　　

　　　　　　　　通配符 含义
　　　　　　　　？ 任何一个单一的字符
　　　　　　　　* 任意长度的字符
　　　　　　　　# 0~9之间的单一数字
　　　　　　　　[字符列表] 在字符列表里的任一值
　　　　　　　　[！字符列表] 不在字符列表里的任一值
　　　　　　　　- 指定字符范围，两边的值分别为其上下限　　　

　　　　聚合函数查询

SUM() # 求总和
AVG() # 求平均值
MAX() # 求最大值
MIN() # 求最小值
COUNT() # 求记录总数
#注：count(*)效率最低，可指定某一字段求总数，如count(Name)

 　　　　查询语句　

select ... from tb_name where-clause [others-clause]

 　　　　　　各子句类型及作用

　　　　　　　　where # 条件过滤
　　　　　　　　group by # 对查询结果分组
　　　　　　　　having # 对group by的结果进行条件过滤
　　　　　　　　order by # 排序
　　　　　　　　limit # 限制输出行数，如”limit 2“表示只显示前2行，“limit 2,3”表示偏移前2行，显示3-5行

　　　　select的执行流程

from-->where-->group by-->having-->order by-->select-->limit

 　　　　多表查询

　　　　　　说明
　　　　　　　　事先将两张或多张表连接(join)，根据连接的结果进行查询
　　　　　　语法 

select … from tb1 Join_Type tb2 [on (Join_Condition)]

连接类型有：

cross join，inner join，left join和right join等

 　　　　　　连接分类

            　　　　cross join：交叉连接，又称笛卡尔乘积

            　　　　表1中的记录(共M条)与表2中的记录(共N条)逐一连接，交叉连接数据量较大，共M*N条记录；

          　　　　  select * from students,classes
          　　　　  # 注：不带where子句哦

         　　　　   inner join：内连接，根据连接判断的运算符又可分类如下：

         　　　　   等值连接：根据表1和表2中某字段值相等进行连接，其查询结果中列出被连接表中的所有列，包括其中的重复列

          　　　　  select * from students,classes
　　　　            # 注：不带where子句哦
　　　　
　　　　            条件比较连接：在连接条件中使用除等号(=)运算符之外的运算符，如>,>=,<,<=,<>等

　　　　            自然连接：在连接条件中使用等号(=)运算符，且只选择结果集中的部分列，并删除连接表中的重复列

　　　　            select a.name,p.pub_name from authors as a inner join publishers as p on a.city=p.city

　　　　            外连接：返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接)、右表(右外连接)或两个边接表(全外连接)中的所有数据行

　　　　            左外连接：返回包括左表中的所有记录和右表中联结字段相等的记录

　　　　            tb1 LEFT JOIN tb2 ON 连接条件

　　　　            右外连接：返回包括右表中的所有记录和左表中联结字段相等的记录

　　　　            tb1 RIGHT JOIN tb2 ON 连接条件

　　　　            全外连接：连接运算两边的关系中的元组都保留，MySQL不支持此种连接

　　　　            自连接：同一表中某字段的多次比较使用

select stu1.Name as StuName,stu2.Name as TeachName from students as stu1,students as stu2 where stu1.TeacherID=stu2.StuID;

 　　　　子查询

　　　　　　说明：

　　　　　　　　在外层查询中嵌套的内层查询叫做子查询，类型有：

　　　　　　where型子查询：把内层查询结果当做外层查询的比较条件

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods)
# 子查询的返回值只能有1个

 　　　　　　exists型子查询：把内层查询结果拿到外层，看外层的查询是否成立

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);
# 可通过转换为内连接查询：
select cat_id,cat_name from category inner join goods on goods.cat_id=category.cat_id;
# 与exists类似的还有in子查询，表示外层查询条件在一个内层查询的结果集内
select * from user_core where userId in (select userId from user_classes where class_id=’A001’);

 　　　　　　from型子查询：把内层的查询结果供外层查询使用

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2)) group by name；

 　　　　注：MySQL不擅长于子查询的优化，故应避免使用子查询

　　　　联合查询

　　　　　　说明

　　　　　　　　把两个或多个查询语句的结果合并成一个结果进行输出，前提是多个查询结果的输出字段是一致的

　　　　　　语法

SELECT clause UNION SELECT clause UNION …

 

　　　　　　union：会将多个表的查询结果合并后进行排序，同时删除重复的行，故效率略慢；union和union all的区别

　　　　　　union all：只是简单的将多个表的结果合并后返回，不删除重复行，效率较高