1.查看表信息,里面包括使用的引擎类型,字符编码,表结构等
使用这个命令
mysql> show create table t1;--t1是表名
2. 可以执行以下命令来切换非事务表到事务(数据不会丢失),innodb表比myisam表更安全:
alter table t1 type=innodb;--t1是表名
3. innodb表不能用repair table命令和myisamchk -r table_name
但可以用check table t1,以及mysqlcheck [OPTIONS] database [tables]
4. 启动mysql数据库的命令行中添加了以下参数可以使新发布的mysql数据表都默认为使用事务(
只影响到create语句。)
--default-table-type=InnoDB
5. 临时改变默认表类型可以用:
set table_type=InnoDB;
6. 显示所有数据库
SHOW DATABASES
7.显示数据库所有表
SHOW TABLES from 数据库名
查看所有函数
show function status
查看所有视图
SELECT * from information_schema.VIEWS
查看所有触发器
show triggers;
8.判断表是否存在
1. SHOW TABLES LIKE '%tb_bp_d_case%';
2. select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname' and
`TABLE_NAME`='tablename' ;
3. 如果表不存在就建立这个表,那么可以直接用 create table if not exists tablename 这样的指令来建立,不
需要先去查询表是否存在。
4. 从模板表创建表:create table if not exists like old_table_name;
9.判断列是否存在
SELECT * FROM information_schema.columns where table_name='clhcs'
10.删除列
ALTER TABLE `T_DT_HOMEWORK` DROP COLUMN `RECOMMEND_RECORD`;
11.添加列
ALTER TABLE `T_DT_HOMEWORK` ADD COLUMN `RECOMMEND_RECORD`
varchar(20) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `RECOMMEND_ORG_CODE`;
12.删除存储过程
drop procedure if exists add_col_homework;
13.if使用
IF EXISTS (SELECT FROM information_schema.columns)
THEN
end if
14.手动抛出异常
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '手动抛出异常' ;
15.调用存储过程
call clhcs()
16.删除函数
drop function if exists 函数名;
在MySQL的trigger和function中不能出现select * from table形式的查询,因为其会返回一个结果集;
而这在mysql的trigger和function中是不可接受的,但是在存储过程中可以。
在trigger和function中可以使用select ... into ...形式的查询。
17.循环
WHILE……DO……END WHILE
REPEAT……UNTIL END REPEAT
LOOP……END LOOP
GOTO。
WHILE:
out_while begin;
while i < 11 do # 循环体
insert into user_profile (uid) values (i);
set i = i +1;
if 1=1 then
leave out_while; #退出循环
end while;
end out_while ;
repeat:
declare i int;
set i = 1;
repeat
insert into user_profile_company (uid) values (i+1);
set i = i + 1;
until i >= 20
end repeat;
LOOP:
lp1 : LOOP // lp1 为循环体名称 LOOP 为关键字insert into user_profile
(uid) values (i);
set i = i+1;
if i > 30 then
leave lp1; // 离开循环体
end if;
end LOOP;
18.动态执行sql,?表示参数
set @sql='SELECT * FROM information_schema.columns where table_name=? ';
set @param='';
PREPARE s1 FROM @sql;
EXECUTE s1 using @param;
DEALLOCATE PREPARE s1;
19.锁
if get_lock(‘锁名称’,60) then
…..
select release_lock(‘锁名称’) into lockstatus;
end if;
20.select 赋值
SELECT column_name into @column_name FROM information_schema.columns
21.limit 选取数据
SELECT * FROM information_schema.columns LIMIT 1, 1;
22,空值判断
isnull(expr) 如expr为null,那么isnull()的返回值为1,否则返回值为0。
ifnull(expr1,expr2)假如expr1不为NULL,则IFNULL()的返回值为expr1; 否则其返回值为expr2。IFNULL()的返回值
是数字或是字符串,具体情况取决于其所使用的语境。
nullif(expr1,expr2) 如果expr1=expr2成立,那么返回值为NULL,否则返回值为expr1
23.局部变量
declare c int default 0;
24.用户变量
用户变量可以作用于当前整个连接,但是当当前连接断开后,其所定义的用户变量都会消失
set @var1 = 1;
对用户变量赋值有两种方式,一种是直接用"="号,另一种是用":="号。其区别在于使用set命令对用户变量进行赋值
时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用":="方式,因为在select语句
中,"="号被看作是比较操作符。
25.会话变量
服务器为每个连接的客户端维护一系列会话变量。在客户端连接时,使用相应全局变量的当前值对客户端的会话变量
进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量
。会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效。
设置会话变量有如下三种方式:
set session var_name = value;
set @@session.var_name = value;
set var_name = value;
26.全局变量
全局变量影响服务器整体操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中
或在命令行中指定的选项进行更改。要想更改全局变量,必须具有SUPER权限。全局变量作用于server的整个生命周
期,但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效,需要更改相应的配置
文件。
要设置一个全局变量,有如下两种方式:
set global var_name = value;
set @@global.var_name = value;
查看一个全局变量,有如下两种方式:
select @@global.var_name;
show global variables like "%var%";
根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION
27.行锁
SELECT * FROM products WHERE id='3' FOR UPDATE;
28.表锁
29.事务
MYSQL的事务处理主要有两种方法。
1、用begin,rollback,commit来实现
begin 开始一个事务
rollback 事务回滚
commit 事务确认
2、直接用set来改变mysql的自动提交模式
MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行!我们可以通过
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
来实现事务的处理。
但注意当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务处理,直到你用commit确认或rollback结
束,注意当你结束这个事务的同时也开启了个新的事务!按第一种方法只将当前的作为一个事务!
30.显示表锁
show status like 'table%';
行锁
Innodb_row_lock_current_waits:当前正在等待锁的数量;
Innodb_row_lock_time:从系统启动到现在锁定的总时间长度;
Innodb_row_lock_time_avg:每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间;
Innodb_row_lock_waits:从系统启动到现在总共等待的次数;
31.表锁
lock tables XXX read(write);
例子:
Lock tables tableName1 read local, tableName12 read local;
Unlock tables;
local 允许其他用户在表尾并发插入记录
34.并发插入
MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
当concurrent_insert设置为0时,不允许并发插入。
当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程
读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
35.删除数据
SET SQL_SAFE_UPDATES = 0;#将安全删除关闭
delete from clhcsdb.cs where rowOrder=1;
36.定义异常
//方法一:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二:使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
37.抛出异常
SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = 'cw';
38.捕获异常
declare _err int;
DECLARE CONTINUE HANDLER FOR command_not_allowed,sqlexception,SQLWARNING,NOT FOUND set _err=1;
39 查询结果连接
GROUP_CONCAT(ColumnId separator ';')
使用这个命令
mysql> show create table t1;--t1是表名
2. 可以执行以下命令来切换非事务表到事务(数据不会丢失),innodb表比myisam表更安全:
alter table t1 type=innodb;--t1是表名
3. innodb表不能用repair table命令和myisamchk -r table_name
但可以用check table t1,以及mysqlcheck [OPTIONS] database [tables]
4. 启动mysql数据库的命令行中添加了以下参数可以使新发布的mysql数据表都默认为使用事务(
只影响到create语句。)
--default-table-type=InnoDB
5. 临时改变默认表类型可以用:
set table_type=InnoDB;
6. 显示所有数据库
SHOW DATABASES
7.显示数据库所有表
SHOW TABLES from 数据库名
查看所有函数
show function status
查看所有视图
SELECT * from information_schema.VIEWS
查看所有触发器
show triggers;
8.判断表是否存在
1. SHOW TABLES LIKE '%tb_bp_d_case%';
2. select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname' and
`TABLE_NAME`='tablename' ;
3. 如果表不存在就建立这个表,那么可以直接用 create table if not exists tablename 这样的指令来建立,不
需要先去查询表是否存在。
4. 从模板表创建表:create table if not exists like old_table_name;
9.判断列是否存在
SELECT * FROM information_schema.columns where table_name='clhcs'
10.删除列
ALTER TABLE `T_DT_HOMEWORK` DROP COLUMN `RECOMMEND_RECORD`;
11.添加列
ALTER TABLE `T_DT_HOMEWORK` ADD COLUMN `RECOMMEND_RECORD`
varchar(20) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `RECOMMEND_ORG_CODE`;
12.删除存储过程
drop procedure if exists add_col_homework;
13.if使用
IF EXISTS (SELECT FROM information_schema.columns)
THEN
end if
14.手动抛出异常
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '手动抛出异常' ;
15.调用存储过程
call clhcs()
16.删除函数
drop function if exists 函数名;
在MySQL的trigger和function中不能出现select * from table形式的查询,因为其会返回一个结果集;
而这在mysql的trigger和function中是不可接受的,但是在存储过程中可以。
在trigger和function中可以使用select ... into ...形式的查询。
17.循环
WHILE……DO……END WHILE
REPEAT……UNTIL END REPEAT
LOOP……END LOOP
GOTO。
WHILE:
out_while begin;
while i < 11 do # 循环体
insert into user_profile (uid) values (i);
set i = i +1;
if 1=1 then
leave out_while; #退出循环
end while;
end out_while ;
repeat:
declare i int;
set i = 1;
repeat
insert into user_profile_company (uid) values (i+1);
set i = i + 1;
until i >= 20
end repeat;
LOOP:
lp1 : LOOP // lp1 为循环体名称 LOOP 为关键字insert into user_profile
(uid) values (i);
set i = i+1;
if i > 30 then
leave lp1; // 离开循环体
end if;
end LOOP;
18.动态执行sql,?表示参数
set @sql='SELECT * FROM information_schema.columns where table_name=? ';
set @param='';
PREPARE s1 FROM @sql;
EXECUTE s1 using @param;
DEALLOCATE PREPARE s1;
19.锁
if get_lock(‘锁名称’,60) then
…..
select release_lock(‘锁名称’) into lockstatus;
end if;
20.select 赋值
SELECT column_name into @column_name FROM information_schema.columns
21.limit 选取数据
SELECT * FROM information_schema.columns LIMIT 1, 1;
22,空值判断
isnull(expr) 如expr为null,那么isnull()的返回值为1,否则返回值为0。
ifnull(expr1,expr2)假如expr1不为NULL,则IFNULL()的返回值为expr1; 否则其返回值为expr2。IFNULL()的返回值
是数字或是字符串,具体情况取决于其所使用的语境。
nullif(expr1,expr2) 如果expr1=expr2成立,那么返回值为NULL,否则返回值为expr1
23.局部变量
declare c int default 0;
24.用户变量
用户变量可以作用于当前整个连接,但是当当前连接断开后,其所定义的用户变量都会消失
set @var1 = 1;
对用户变量赋值有两种方式,一种是直接用"="号,另一种是用":="号。其区别在于使用set命令对用户变量进行赋值
时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用":="方式,因为在select语句
中,"="号被看作是比较操作符。
25.会话变量
服务器为每个连接的客户端维护一系列会话变量。在客户端连接时,使用相应全局变量的当前值对客户端的会话变量
进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量
。会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效。
设置会话变量有如下三种方式:
set session var_name = value;
set @@session.var_name = value;
set var_name = value;
26.全局变量
全局变量影响服务器整体操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中
或在命令行中指定的选项进行更改。要想更改全局变量,必须具有SUPER权限。全局变量作用于server的整个生命周
期,但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效,需要更改相应的配置
文件。
要设置一个全局变量,有如下两种方式:
set global var_name = value;
set @@global.var_name = value;
查看一个全局变量,有如下两种方式:
select @@global.var_name;
show global variables like "%var%";
根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION
27.行锁
SELECT * FROM products WHERE id='3' FOR UPDATE;
28.表锁
29.事务
MYSQL的事务处理主要有两种方法。
1、用begin,rollback,commit来实现
begin 开始一个事务
rollback 事务回滚
commit 事务确认
2、直接用set来改变mysql的自动提交模式
MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行!我们可以通过
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
来实现事务的处理。
但注意当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务处理,直到你用commit确认或rollback结
束,注意当你结束这个事务的同时也开启了个新的事务!按第一种方法只将当前的作为一个事务!
30.显示表锁
show status like 'table%';
行锁
Innodb_row_lock_current_waits:当前正在等待锁的数量;
Innodb_row_lock_time:从系统启动到现在锁定的总时间长度;
Innodb_row_lock_time_avg:每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间;
Innodb_row_lock_waits:从系统启动到现在总共等待的次数;
31.表锁
lock tables XXX read(write);
例子:
Lock tables tableName1 read local, tableName12 read local;
Unlock tables;
local 允许其他用户在表尾并发插入记录
34.并发插入
MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
当concurrent_insert设置为0时,不允许并发插入。
当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程
读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
35.删除数据
SET SQL_SAFE_UPDATES = 0;#将安全删除关闭
delete from clhcsdb.cs where rowOrder=1;
36.定义异常
//方法一:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二:使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
37.抛出异常
SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = 'cw';
38.捕获异常
declare _err int;
DECLARE CONTINUE HANDLER FOR command_not_allowed,sqlexception,SQLWARNING,NOT FOUND set _err=1;
39 查询结果连接
GROUP_CONCAT(ColumnId separator ';')