自己总结的用到的Mysql的用法

创建自己的数据库：create database mydata;
用mydata:use mydata;
创建表：dept
create table dept
(
deptno int primary key,
dname varchar(14),
loc varchar(13)
);
创建表emp:
 create table emp
 (
 empno int primary key,
 ename varchar(10),
 job varchar(10),
 mgr int,
 hiredate datetime,
 sal double,
 comm double,
 deptno int,
 foreign key(deptno) references dept(deptno)这句话的意思是参考外键deptno这张表的deptno字段
 );
看有多少的数据库：show databases;
看数据库中有多少表：show tables;
观察表的结构：desc dept;
向表中插入数据：
就dept这张表而言
insert into dept values(10,’A’,’A’);
insert into dept values(20,’B’,’B’);
insert into dept values(30,’C’,’C’);
insert into dept values(40,’D’,’D’);
insert into dept values(50,’E’,’E’);
commit；（然后执行提交）
分页查找：
select*from dept order by deptno desc limit 3,2;(从第三条往后数两条（倒着数的）)
结果是：
   Deptno    dname   loc 
      20       B      B
      10       A      A
select * from (用select 查询到的结果)t
显示当前的日期：  select now();
select data_format(now(),’%Y-%m-%d %H%i%s’);

create or replace procedure p
 (v_a in number,v_b number,v_temp in our number)
is

begin
 if(v_a>v_b)then
  v_ret:=v_a;
 else
  v_ret:=v_b;
 end if;
 v_temp:=v_temp+1;
end;
/

 create table test(id int primary key auto_increment,big_test text );
意思是创建一个表test把id设为自动增加，如果不设需要自己设置id号 
查询mysql的隔离级别    select @@tx_isolation;




改变乱码：show variables like "%char%";
+--------------------------+---------------+
 | Variable_name | Value |
 +--------------------------+---------------+
 | character_set_client | gbk |
 | character_set_connection | gbk |
 | character_set_database | utf8 |
 | character_set_filesystem | binary |
 | character_set_results | gbk |
 | character_set_server | utf8 |
 | character_set_system | utf8 |
 +--------------------------+-------------+
 注：以前2个来确定,可以使用set names utf8,set names gbk设置默认的编码格式;
执行SET NAMES utf8的效果等同于同时设定如下：
 SET character_set_client='utf8';
 SET character_set_connection='utf8';
 SET character_set_results='utf8';

********select * from users order by userId limit ？,？;
在表中按照要求查询

赋值语句
Insert into users (?,?,?) select ?,?,? From users;

删除
DELETE FROM tablename where id=1;

Mysql漏洞 :  selct * from table where name=’1’ or ‘1’=’1’;
这句话将数据库里面的内容都拿出来了   是一漏洞


改变一个表的结构语句、
ALTER TABLE * ADD age INT;
*表添加一个age属性


以下是在book表中计算price与rebate的乘积取两位有效小树将结果命名为currentP
Select round(price*rebate,2) as currentP from book;