一、登录参数
-D 打开指定数据库
-h 服务器名称
-p 密码
-P 端口
-u 用户名
-V 输出版本信息并退出
--prompt 提示符 mysql> ,或者登陆后 用prompt命令
提示符:
\D 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户
二、常用命令
1.显示当前服务器版本
select version();
2.显示当前时间
select now();
3.显示当前用户
select user();
4.显示当前用户打开的数据库
select database();
5.字符连接
select concat('a','b');
6.使用指定分隔符连接
select concat_ws('-','a','b');
7.数字格式话
select format(20.25,1);
8.转小写
select lower('MYSQL');
9.转大写
select upper('mysql');
10.左截取
select left('mysql',2);
11.右截取
select right('mysql',2);
12.获取字符串长度
select length('mysql');
13.删除左字符
select ltrim(' mysql');
14.删除右字符
select rtrim('mysql ');
15.删除左右字符
select trim(leading '-' from '-mysql');
16.替换字符
select replace('-my-sql','-','+');
17.截取字符
select substring('mysql',1,2);
18.进一取整
select ceil(3.99);
19.去尾取整
select filoor(3.01);
20.整数除法
select 5 div 3;
21.取余数
select 5 mod 3;
22.幂运算
select power(2,8);
23.四舍五入
select round(3.5);
24.数字截取
select truncate(20.235,2);
25.在范围内
[not] between...and...
26.在列出值范围内
[not] in()
25.为空
is [not] null
26.当前日期
select curdate();
27.当前时间
select curtime();
28.日期变化
select date_add('2015-2-12',interval -365 day);
29.日期差值
select datediff('2013-2-12','2015-2-12');
30.日期格式化
select date_fromat('2015-2-12','%Y-%m-%d');
31.连接id
select connection_id();
32.最后插入记录id
select last_insert_id();
33.平均值
select avg(age);
34.总数
select count(age);
35.最大值
select max(age);
36.最小值
select min(age);
37.和值
select sum(age);
38.更改客户端编码
set names utf8;
38.被影响的记录总数
select row_count();
39.查看状态
show status;
40.查看索引
show index from A;
41.查看数据库进程
show processlist;
三、操作数据库
1.创建数据库
create {database|schema} [if not exists] db_name [default] character set [=] charset_name
例:create database test;
2.修改数据库
alter {database|schema} db_name [default] character set [=] charset_name
例:alter database test charset=utf8;
3.删除数据库
drop {database|schema} [if exists] db_name
例:drop database test;
4.查看库的信息
show create database db_name;
例:show create database test;
5.查看当前服务器下的数据库列表
show {databases|schemas} [like ‘pattern’|where expr]
例:show databases;
6.查看警告信息
show warnings;
四、操作数据表
1.打开数据库
use db_name;
例:use test;
2.创建表
create table [if not exists] tbl_name(
age tinyint(2) unsigned not null auto_increment primary key
);
例:
create table user(
id int(10) unsigned auto_increment primary key,//主键自增
name varchar(20) not null unique key,//唯一
price decimal(8,2) unsigned default 0.00,//默认
cid int(10) unsigned,
KEY cid(cid),
foreign key (cid) references cate (id) on delete cascade//外键 (删除时执行CASCADE)
) engine=InnoDB default charset=utf8;
3.查看数据列表
show tables [from db_name] [like ‘pattern’|where expr]
例:SHOW TABLES;
4.查看表信息
show create table tbl_name;
例:SHOW CREATE TABLE user;
5.查看表结构
(1)show columns from tbl_name;
例:show columns from user;
(2)desc tbl_name;
例:desc user;
6.添加单字段
alert table tbl_name add [column] col_name column_definition [first|after col_name];
例:alter table user add num int(10) unsigned;
7.添加多字段
alter table tbl_name add [column] (col_name column_definition,…);
例:alter table user add num int(10) unsigned, time int(10) unsigned;
8.删除字段
alter table tbl_name drop [column] col_name,drop [column] col_name,…
例:alter table user drop num ,drop time;
9.修改字段定义–不重命名字段
alter table 表名 add 列名 类型;
alter table tbl_name modify [column] col_name column_definition [first|after col_name]
例:alter table user modify num tinyint(10) after name;
10.修改字段名称–重命名
alter table 表名 change 原名 新名 类型及约束;
alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]
例:alter table user change price money int(10);
11.修改表名称
(1)alter table tbl_name rename [to|as] new_tbl_name
例:alter table user rename goods;
(2)rename table tbl_name to new_tbl_name [,tbl_name2 to new_tbl_name2]
例:rename table user to goods;
12.插入记录
(1)insert [into] tbl_name [(col_name,…)] {values|value} ({expr|default},…),(…),…;
例:insert user (id, name, price) values (default, 'tom', 20);
(2)insert [into] tbl_name set col_name={expr|default},…
例:insert user set name = 'tom';
(3)insert [into] tbl_name [(col_name,…)] select …
例:insert user select id,name from goods;
13.更新记录
update [low_priority] [ignore] tbl_name set col_name1={expr1|default} [,col_name2={expr2|default}]… [where where_condition]
例:update user set num = num + id;
14.删除记录(清空表)
delete from tbl_name [where where_condition]
例:delete from user where id = 3;
15.查找记录
select select_expr [,select_expr…]
[
from tbl_name
[where where_condition]
[group by {col_name|position} [asc|desc],…]
[having where_condition]
[order by {col_name|expr|position} [asc|desc],…]
[limit {[offset,] row_count|row_count offset of_set}]
]
select [distinct] * from 表名 where .... group by ... having ... order by ... limit start,count
16.多表更新
update user inner join cate on user.cid=cate.id set user.cname=cate.name;
17.创建表的同时将查询结果写入数据表
CREATE TABLE cate (
id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10) NOT NULL
)
SELECT cid,cname FROM user GROUP BY cid;
18.修改表引擎
alter table tbl_name engine [=] engine_name
例:ALTER TABLE user ENGINE = MyISAM;
19.修改分隔符
delimiter $$
20.删除表
drop table A;
21.截断表
truncate A;
五、约束:
保证数据的完整性和一致性;
列级约束(针对一个字段),表级约束(针对多个字段)
1.主键约束:primary key
(1)每个表只存在一个
(2)保证记录的唯一性
(3)自动为not null
(4)添加主键约束:
alter table tbl_name add [constraint [symbol]] primary key [index_type] (index_col_name,…)
例:alter table user add primary key (id);
(5)删除主键约束:
alter table tbl_name dro p primary key
例:alter table user drop primary key;
2.唯一约束:unique key
(1)每个表可存在多个
(2)保证记录的唯一性
(3)可以存一个null
(4)添加唯一约束:
alter table tbl_name add [constraint [symbol]] unique [index|key] [index_name] [index_type] (index_col_name,…)
例:alter table user add unique (name);
(5)删除唯一约束:
alter table tbl_name drop {index|key} index_name
例:alter table user drop index name;
(6)查看约束:
show indexe from tbl_name\G;
例:show index from user\G;
3.默认约束:default
(1)添加删除默认约束:
alter table tbl_name alter [column] col_name {set default literal | drop default}
例:alter table user alter num set default 0;
alter table user alter num drop default;
4.非空约束:not null
5.外键约束:foreign key
保证数据的完整性和一致性;
实现1对1、多对1关系。
(1)cascade:从父表删除或更新且自动删除或更新子表中的匹配行
(2)set null:从父表删除或更新并设置子表中的外键列为null。如果使用该选项,必须保证子表没有指定not null
(3)restrict:拒绝对父表的删除或更新操作
(4)no action:标准sql的关键字,在mysql中与restrict相同
(5)添加外键约束
alter table tbl_name add [constraint [symbol]] foreign key [index_name] (index_col_name,…) reference_definition
例:ALTER TABLE user ADD FOREIGN KEY (cid) REFERENCES cate (id)
(6)删除外键约束:
alter table tbl_name drop foreign key symbol
例:ALTER TABLE user DROP FOREIGN KEY cid;
六、子查询:
嵌套在内部,始终出现在括号内;
可以包含多个关键字或条件,如distinct,group by,order by,limit,函数等;
外层可以是:select,insert,update,set,do
1.比较运算符:=,>,<,<=,>=,<>
select * from t1 where col_name1 >= ANY (select col_name2 from t2);
(1)any(some) :符合任一个
(2)all:符合所有
2.[NOT] IN/EXISTS
select * from t1 where col_name1 not in (select col_name2 from t2);
七、表连接
1.内连接(交集):inner join
2.外链接:
(1)左连接(左全及右符合):left join
(2)右连接(右全及左符合):right join
一般建议使用左连接
八、自定义函数
create function fun_name
returns
{string|int|real|decimal}
routine_body
例(无参数):
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y-%m-%d');
例(参数):
CREATE FUNCTION f2(num1 INT , num2 INT)
RETURNS DECIMAL(10,2)
RETURN (num1+num2)/2;
例(复合):
CREATE FUNCTION addusers(username VARCHAR(20))
RETURNS INT
BEGIN
INSERT user (name) VALUES (username);
RETURN LAST_INSERT_ID();
END
$$
九、存储过程
存储过程: procedure
概念类似于函数,就是把一段代码封装起来,
当要执行这一段代码的时候,可以通过调用该存储过程来实现.
在封装的语句体里面,可以用if/else, case,while等控制结构.
可以进行sql编程.
存储过程与自定义函数的区别
1.存储过程复杂,函数针对性强
2.存储过程返回多个值,函数返回一个值
3.存储过程独立执行,函数可作为其他sql语句的组成部分出现
创建:
create
[definer = {user|current_user}]
procedure sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[in|out|inout] param_name type
调用:
call sa_name[()];
修改:
alter procedure sp_name [characteristic ...]
comment 'string'
| {contains sql|no sql|reads sql data| modifies sql data} | sql security {definer|invoker}
删除:
drop procedure [if exists] sp_name;
例(无参数):
CREATE PROCEDURE sp1() SELECT VERSION();
例(IN):
CREATE PROCEDURE sp2(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id=p_id ;
例(IN OUT):
CREATE PROCEDURE sp3(IN p_id INT UNSIGNED,OUT userNums INT )
BEGIN
DELETE FROM user WHERE id=p_id ;
SELECT COUNT(id) FROM users INTO userNums;
例(IN OUT):
CREATE PROCEDURE sp4(IN p_age INT UNSIGNED,OUT deleteUsers INT ,OUT userCounts INT)
BEGIN
DELETE FROM user WHERE age=p_age ;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
十、视图的定义:
视图是由查询结果形成的一张虚拟表.
视图的创建语法:
Create [algorethm=temptable] view 视图名 as select 语句;
1:可以简化查询
2:可以进行权限控制
3:大数据分表时可以用到
Merge: 当引用视图时,引用视图的语句与定义视图的语句合并.
Temptable:当引用视图时,根据视图的创建语句建立一个临时表
Undefined:未定义,自动,让系统帮你选.
十一、事务的ACID特性
原子性(Atomicity):原子意为最小的粒子,或者说不能再分的事物。
数据库事务的不可再分的原则即为原子性。
组成事务的所有查询必须:要么全部执行,要么全部取消(就像上面的银行例子)。
一致性(Consistency):指数据的规则,在事务前/后应保持一致
隔离性(Isolation):简单点说,某个事务的操作对其他事务不可见的.
持久性(Durability):当事务完成后,其影响应该保留下来,不能撤消