MySQL命令范例

windows上启动MySQL服务：net start mysql

windows上停止MySQL服务：net stop mysql

连接MySQL服务器命令：mysql -u root -p 123456 -h 127.0.0.1

#修改定界符
delimiter //
#设置客户端字符集
set names gbk
#使用某一个特定的数据库
use test;
#显示所有的数据库
show databases;
#创建数据库
create database db1;
#显示创建的数据库
show create database db1;
#修改数据库
alter database db1 character set utf8;
#删除数据库
drop database db1;
#创建数据表
create table tb_name(
id int unsigned primary key auto_increment,
username varchar(32) not null
);
#修改数据表
#修改列定义
#ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]
alter table tb1 modify age int unsigned not null default 0;
#修改列名称
#ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
alter talbe tb_name change id new_id int unsigned;
#删除数据表
drop table tb1;
#数据表更名
#方法一
#ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
#方法二
#RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

#对查询结果进行排序
#[ORDER BY {col_name | expr | position} [ASC | DESC]], ...]
select * from users order by age;
select * from users order by age desc;
select * from users order by age, id desc;

/*自定义函数
CREATE FUNCTION function_name
RETURNS {STRING | INTEGER | REAL | DECIMAL}
routine_body
*/
create function f1() returns varchar(30)
return date_format(now(), '%Y年%m月%d日 %H点:%i分:%s秒');

create function f2(num1 smallint unsigned, num2 smallint unsigned)
returns float(10, 2) unsigned
return (num1 + num2) / 2;

create function adduser(username varchar(20))
returns int unsigned 
begin
insert test(username) values(username);
return last_insert_id();
end

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

#定义用户变量
set @var = 100;
select @var

#定义局部变量
#DECLARE var_name[,...] type [DEFAULT value]

#限制查询结果返回的数量
#[LIMIT {[offset,] row_count OFFSET offset}]