重新学习MySQL数据库1：无废话MySQL入门

重新学习Mysql数据库1：无废话MySQL入门

开始使用

我下面所有的SQL语句是基于MySQL 5.6+运行。

MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成, 如图所示的一个表格：

• 表头(header): 每一列的名称;

• 列(col): 具有相同数据类型的数据的集合;

• 行(row): 每一行用来描述某个人/物的具体信息;

• 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;

• 键(key): 表中用来识别某个特定的人物的方法, 键的值在当前列中具有唯一性。

登录MySQL

mysql -h 127.0.0.1 -u 用户名 -p
​
​
​
mysql -D 所选择的数据库名 -h 主机名 -u 用户名 -p
​
​
​
mysql> exit # 退出 使用 “quit;” 或 “\q;” 一样的效果
​
​
​
mysql> status;  # 显示当前mysql的version的各种信息
​
​
​
mysql> select version(); # 显示当前mysql的version信息
​
​
​
mysql> show global variables like 'port'; # 查看MySQL端口号

创建数据库

对于表的操作需要先进入库use 库名;

-- 创建一个名为 samp_db 的数据库，数据库字符编码指定为 gbk
​
​
​
create database [if no exists]samp_db character set gbk;
​
​
​
drop database samp_db; -- 删除 库名为samp_db的库
​
​
​
show databases;        -- 显示数据库列表。
​
​
​
use samp_db;     -- 选择创建的数据库samp_db
​
​
​
show tables;     -- 显示samp_db下面所有的表名字
​
​
​
describe 表名;    -- 显示数据表的结构
​
​
​
delete from 表名; -- 清空表中记录

创建数据库表

使用 create table 语句可完成对表的创建, create table 的常见形式: 语法：create table 表名称(列声明);

CREATE TABLE `user_accounts` (
​
  `id`             int(100) unsigned NOT NULL AUTO_INCREMENT primary key,
​
​
  `password`       varchar(32)       NOT NULL DEFAULT '' COMMENT '用户密码',
​
​
  `reset_password` tinyint(32)       NOT NULL DEFAULT 0 COMMENT '用户类型：0－不需要重置密码；1-需要重置密码',
​
​
  `mobile`         varchar(20)       NOT NULL DEFAULT '' COMMENT '手机',
​
  `create_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
​
  `update_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
​
  -- 创建唯一索引，不允许重复
​
  UNIQUE INDEX idx_user_mobile(`mobile`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
​
COMMENT='用户表信息';

数据类型的属性解释

• NULL：数据列可包含NULL值；

• NOT NULL：数据列不允许包含NULL值；

• DEFAULT：默认值；

• PRIMARY KEY： 主键；

• AUTO_INCREMENT：自动递增，适用于整数类型；

• UNSIGNED：是指数值类型只能为正数；

• CHARACTER SET name：指定一个字符集；

• COMMENT：对表或者字段说明；

增删改查

SELECT

SELECT 语句用于从表中选取数据。 语法：SELECT 列名称 FROM 表名称 语法：SELECT * FROM 表名称

子查询

(镶嵌在其它语句内部的Select语句)

标量子查询(单行子查询)

    列子查询(多行子查询)
​
    行子查询(多行多列)
​
    表子查询(多行多列)
​
    用途:
​
    select 后面
​
    from 后面
​
    where 或者 having后面(主要)

union 联合查询(将多次查询的结果联合在一起)

查询语句 1 unoin [all]
​
查询语句2  unoin [all]
​
......
​
注意:查询语句的字段要一致,不加all会去重

-- 表station取个别名叫s，表station中不包含 字段id=13或者14 的，并且id不等于4的 查询出来，只显示id
​
​
​
SELECT s.id from station s WHERE id in (13,14) and user_id not in (4);
​
​
​
-- 从表 Persons 选取 LastName 列的数据
​
​
​
SELECT LastName FROM Persons
​
​
-- 结果集中会自动去重复数据
​
SELECT DISTINCT Company FROM Orders 
​
-- 表 Persons 字段 Id_P 等于 Orders 字段 Id_P 的值，
​
​
-- 结果集显示 Persons表的 LastName、FirstName字段，Orders表的OrderNo字段
​
​
SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.Id_P = o.Id_P 
​
​
​
​
-- gbk 和 utf8 中英文混合排序最简单的办法 
​
​
​
-- ci是 case insensitive, 即 “大小写不敏感”
​
​
SELECT tag, COUNT(tag) from news GROUP BY tag order by convert(tag using gbk) collate gbk_chinese_ci;
​
​
​
SELECT tag, COUNT(tag) from news GROUP BY tag order by convert(tag using utf8) collate utf8_unicode_ci;

UPDATE

Update 语句用于修改表中的数据。 语法：UPDATE 表名称 SET 列名称 = 新值 ,列名称=新值 WHERE 列名称 = 某值

-- update语句设置字段值为另一个结果取出来的字段
​
​
​
update user set name = (select name from user1 where user1 .id = 1 )
​
​
​
where id = (select id from user2 where user2 .name='小苏');
​
​
​
-- 更新表 orders 中 id=1 的那一行数据更新它的 title 字段
​
​
​
UPDATE `orders` set title='这里是标题' WHERE id=1;

INSERT

INSERT INTO 语句用于向表格中插入新的行。 语法：INSERT INTO 表名称 VALUES (值1, 值2,....) 语法：INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

-- 向表 Persons 插入一条字段 LastName = JSLite 字段 Address = shanghai
​
​
​
INSERT INTO Persons (LastName, Address) VALUES ('JSLite', 'shanghai');
​
​
​
-- 向表 meeting 插入 字段 a=1 和字段 b=2
​
​
​
INSERT INTO meeting SET a=1,b=2;
​
​
​
-- SQL实现将一个表的数据插入到另外一个表的代码
​
​
​
-- 如果只希望导入指定字段，可以用这种方法：
​
​
​
-- INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
​
​
​
INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.id=1;

DELETE

DELETE 语句用于删除表中的行。 语法：DELETE FROM 表名称 WHERE 列名称 = 值

-- 在不删除table_name表的情况下删除所有的行，清空表。

DELETE FROM table_name

-- 或者

DELETE * FROM table_name

-- 删除 Person表字段 LastName = 'JSLite' 

DELETE FROM Person WHERE LastName = 'JSLite' 

-- 删除 表meeting id 为2和3的两条数据

DELETE from meeting where id in (2,3);

WHERE

WHERE 子句用于规定选择的标准。 语法：SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

-- 从表 Persons 中选出 Year 字段大于 1965 的数据
SELECT * FROM Persons WHERE Year>1965

AND 和 OR

AND - 如果第一个条件和第二个条件都成立； OR - 如果第一个条件和第二个条件中只要有一个成立；

AND

-- 删除 meeting 表字段 

-- id=2 并且 user_id=5 的数据  和

-- id=3 并且 user_id=6 的数据 

DELETE from meeting where id in (2,3) and user_id in (5,6);

-- 使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人：

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';

OR

-- 使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人：

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

ORDER BY

语句默认按照升序对记录进行排序。 ORDER BY - 语句用于根据指定的列对结果集进行排序。 DESC - 按照降序对记录进行排序。 ASC - 按照顺序对记录进行排序。

-- Company在表Orders中为字母，则会以字母顺序显示公司名称

SELECT Company, OrderNumber FROM Orders ORDER BY Company

-- 后面跟上 DESC 则为降序显示

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

-- Company以降序显示公司名称，并OrderNumber以顺序显示

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

IN

IN - 操作符允许我们在 WHERE 子句中规定多个值。 IN - 操作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。 语法：SELECT "字段名"FROM "表格名"WHERE "字段名" IN ('值一', '值二', ...);

-- 从表 Persons 选取 字段 LastName 等于 Adams、Carter

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

NOT

NOT - 操作符总是与其他操作符一起使用，用在要过滤的前面。

SELECT vend_id, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BYprod_name;

UNION

UNION - 操作符用于合并两个或多个 SELECT 语句的结果集。

-- 列出所有在中国表（Employees_China）和美国（Employees_USA）的不同的雇员名

SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

-- 列出 meeting 表中的 pic_url，

-- station 表中的 number_station 别名设置成 pic_url 避免字段不一样报错

-- 按更新时间排序

SELECT id,pic_url FROM meeting UNION ALL SELECT id,number_station AS pic_url FROM station  ORDER BY update_at;

AS

as - 可理解为：用作、当成，作为；别名 一般是重命名列名或者表名。 语法：select column_1 as 列1,column_2 as 列2 from table as 表

SELECT * FROM Employee AS emp

-- 这句意思是查找所有Employee 表里面的数据，并把Employee表格命名为 emp。

-- 当你命名一个表之后，你可以在下面用 emp 代替 Employee.

-- 例如 SELECT * FROM emp.

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

-- 列出表 Orders 字段 OrderPrice 列最大值，

-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice

-- 显示表 users_profile 中的 name 列

SELECT t.name from (SELECT * from users_profile a) AS t;

-- 表 user_accounts 命名别名 ua，表 users_profile 命名别名 up

-- 满足条件 表 user_accounts 字段 id 等于 表 users_profile 字段 user_id

-- 结果集只显示mobile、name两列

SELECT ua.mobile,up.name FROM user_accounts as ua INNER JOIN users_profile as up ON ua.id = up.user_id;

JOIN

用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

• JOIN: 如果表中有至少一个匹配，则返回行

• INNER JOIN:在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

• FULL JOIN: 只要其中一个表中存在匹配，就返回行

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders

ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName;

SQL 函数

COUNT

COUNT 让我们能够数出在表格中有多少笔资料被选出来。 语法：SELECT COUNT("字段名") FROM "表格名";

-- 表 Store_Information 有几笔 store_name 栏不是空白的资料。

-- "IS NOT NULL" 是 "这个栏位不是空白" 的意思。

SELECT COUNT (Store_Name) FROM Store_Information WHERE Store_Name IS NOT NULL; 

-- 获取 Persons 表的总数

SELECT COUNT(1) AS totals FROM Persons;

-- 获取表 station 字段 user_id 相同的总数

select user_id, count(*) as totals from station group by user_id;

1.  count(1) and count(*)

当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！ 
从执行计划来看，count(1)和count(*)的效果是一样的。 但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。 
 
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*)，sql会帮你完成优化的 因此：count(1)和count(*)基本没有差别！ 

2. count(1) and count(字段)
两者的主要区别是
（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。 
转自：http://www.cnblogs.com/Dhouse/p/6734837.html

count(*) 和 count(1)和count(列名)区别  

执行效果上：  
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：  
列名为主键，count(列名)会比count(1)快  
列名不为主键，count(1)会比count(列名)快  
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
如果有主键，则 select count（主键）的执行效率是最优的  
如果表只有一个字段，则 select count（*）最优。

MAX

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。 语法：SELECT MAX("字段名") FROM "表格名"

-- 列出表 Orders 字段 OrderPrice 列最大值，

-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

MIN

常见函数

	字符型函数:

		concat()|substr()截取子串|upper()|lower()|repace()|length()|trim()去掉前后空格|lpad()左填充|rpad()右填充

	数值型函数:round()四舍五入|floor()向下取整,(-5.9)为-6|ceil()向上取整|mod()取模|rand()随机(0~1)之间的数

	日期函数:

	now()|year('date')|moth()|day()|date-formate()将日期转换成字符|datediff('date1','date2')返回2个日期相差的天数

触发器

语法： create trigger <触发器名称> { before | after} # 之前或者之后出发 insert | update | delete # 指明了激活触发程序的语句的类型 on <表名> # 操作哪张表 for each row # 触发器的执行间隔，for each row 通知触发器每隔一行执行一次动作，而不是对整个表执行一次。 <触发器SQL语句>

DELIMITER $ -- 自定义结束符号

CREATE TRIGGER set_userdate BEFORE INSERT 

on `message`

for EACH ROW

BEGIN

  UPDATE `user_accounts` SET status=1 WHERE openid=NEW.openid;

END

$DELIMITER ; -- 恢复结束符号

OLD和NEW不区分大小写

• NEW 用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。

• OLD 用OLD.col_name来引用更新前的某一行的列

变量

1.系统变量

	(全局变量|会话变量)默认session

		查看系统变量 show [global|session(默认)] variables like ''

			     select @@[global|session].变量名

		设置变量名

			set [global|session].变量名 = 值

			set 变量名 = 值

2.自定义变量

		(用户变量|局部变量)

		用户变量:可放在begin end 里面或者外面

		声明并赋值：

			set @变量名=值

			select @变量名:=值

		查询: select @变量名

		将从表中的数据插入到用户变量中去

		select * from into @变量 from 表;

		局部变量:只能放在begin end中,而且只能放在第一句

视图

作用:不是保存数据,只是为了保存执行的sql代码,起到解释的作用;简化了代码,提高sql的重用性

创建视图

1.create view 视图名 as 查询语句

创建或者修改

create or replace view 视图名 as 查询语句

修改视图

alter view 视图名 as 查询语句

删除视图

drop view 视图1,视图2

查看视图

desc 视图名

存储过程

创建存储过程

	delimiter 结束标记(需要先身明)

	create procedure 存储过程名(in 参数名 参数类型| out 参数名 参数类型|inout 参数名 参数类型 )

	begin

		sql 语句1;

		select 字段 into out参数名 from 表名 where 字段=参数名;

		sql语句2;

	end

调用: call 存储过程名(实际参数)

调用带out的参数时候 delimiter 结束标记=> call(in xxx,@name) =>select @name

#4.创建带inout模式参数的存储过程

create procedure pc3(inout m int,inout n int)

begin

	set m=m*2;

	set n=n*2;

end

set @m=10;

set @n=20;

call pc3(@m,@n);

select @m,@n;

删除存储过程

drop procedure 存储过程名

查看存储过程名

show create procedure 存储过程名

函数

创建函数

	delimiter 结束标记

	create function 函数名(参数名 参数类型 ) returns 返回类型

	begin

		//初始化参数

		set @name='name'

		select 字段 into @name  from 表 where id=参数 ;

		return @name

	end

结束标记

调用函数

select 函数名(实参值)结束标记

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE

BEGIN

	DECLARE sal DOUBLE ;

	SELECT AVG(salary) INTO sal

	FROM employees e

	JOIN departments d ON e.department_id = d.department_id

	WHERE d.department_name=deptName;

	RETURN sal;

END $

SELECT myf3('IT')$      --调用函数

删除函数

   drop function 函数名

查看函数

show create function 函数名

添加索引

普通索引(INDEX)

语法：ALTER TABLE 表名字 ADD INDEX 索引名字 ( 字段名字 )

-- –直接创建索引

CREATE INDEX index_user ON user(title)

-- –修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name  (column(length))

-- 给 user 表中的 name字段 添加普通索引(INDEX)

ALTER TABLE `table` ADD INDEX index_name (columnName(length))

-- –创建表的时候同时创建索引

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    INDEX index_name (title(length))

)

-- –删除索引

DROP INDEX index_name ON table

主键索引(PRIMARY key)

语法：ALTER TABLE 表名字 ADD PRIMARY KEY ( 字段名字 )

-- 给 user 表中的 id字段 添加主键索引(PRIMARY key)

ALTER TABLE `user` ADD PRIMARY key (id);

唯一索引(UNIQUE)

语法：ALTER TABLE 表名字 ADD UNIQUE (字段名字)

-- 给 user 表中的 creattime 字段添加唯一索引(UNIQUE)

ALTER TABLE `user` ADD UNIQUE (creattime);

全文索引(FULLTEXT)

语法：ALTER TABLE 表名字 ADD FULLTEXT (字段名字)

-- 给 user 表中的 description 字段添加全文索引(FULLTEXT)

ALTER TABLE `user` ADD FULLTEXT (description);

添加多列索引(复合索引)

语法：

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3)

-- 给 user 表中的 name、city、age 字段添加名字为name_city_age的普通索引(INDEX)

ALTER TABLE user ADD INDEX name_city_age (name(10),city,age);

建立索引的时机

在WHERE和JOIN中出现的列需要建立索引，但也不完全如此：

• MySQL只对<，<=，=，>，>=，BETWEEN，IN使用索引

• 某些时候的LIKE也会使用索引。

• 在LIKE以通配符%和_开头作查询时，MySQL不会使用索引。

-- 此时就需要对city和age建立索引，

-- 由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

SELECT t.Name  

FROM mytable t LEFT JOIN mytable m ON t.Name=m.username 

WHERE m.age=20 AND m.city='上海';

SELECT * FROM mytable WHERE username like'admin%'; -- 而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin'; -- 因此，在使用LIKE时应注意以上的区别。

索引的注意事项

• 索引不会包含有NULL值的列

• 使用短索引

• 不要在列上进行运算 索引会失效

• like的使用

创建后表的修改

添加列

语法：alter table 表名 add 列名 列数据类型 [after 插入位置];

示例:

-- 在表students的最后追加列 address: 

alter table students add address char(60);

-- 在名为 age 的列后插入列 birthday: 

alter table students add birthday date after age;

-- 在名为 number_people 的列后插入列 weeks: 

alter table students add column `weeks` varchar(5) not null default "" after `number_people`;

修改列

语法：alter table 表名 change 列名称 列新名称 新数据类型;

-- 将表 tel 列改名为 telphone: 

alter table students change tel telphone char(13) default "-";

-- 将 name 列的数据类型改为 char(16): 

alter table students change name name char(16) not null;

-- 修改 COMMENT 前面必须得有类型属性

alter table students change name name char(16) COMMENT '这里是名字';

-- 修改列属性的时候 建议使用modify,不需要重建表

-- change用于修改列名字，这个需要重建表

alter table meeting modify `weeks` varchar(20) NOT NULL DEFAULT "" COMMENT "开放日期 周一到周日：0~6，间隔用英文逗号隔开";

删除列

语法：alter table 表名 drop 列名称;

-- 删除表students中的 birthday 列: 

alter table students drop birthday;

重命名表

语法：alter table 表名 rename 新表名;

-- 重命名 students 表为 workmates: 
​
​
​
alter table students rename workmates;

清空表数据

方法一：delete from 表名; 方法二：truncate from "表名";

• DELETE:1. DML语言;2. 可以回退;3. 可以有条件的删除;4.自增主键ID不会被删除

• TRUNCATE:1. DDL语言;2. 无法回退;3. 默认所有的表内容都删除;4. 删除速度比delete快。

-- 清空表为 workmates 里面的数据，不删除表。 
​
​
delete from workmates;
​
​
-- 删除workmates表中的所有数据，且无法恢复
​
​
truncate from workmates;

删除整张表

语法：drop table 表名;

-- 删除 workmates 表: 
​
drop table workmates;

删除整个数据库

语法：drop database 数据库名;

-- 删除 samp_db 数据库: 
​
drop database samp_db;