sql 案例

select now();#获取当前系统时间
select now() from dual;#与Oracle兼容

show character set;#产看当前数据库支持的字符集

create database foreign_sales character set utf8;#修改当前数据库的默认字符集为utf8

alter table tab3 add column tel varchar(40) not null;#添加列

alter table tab4 add column id int not null primary key;#添加列将其设置为主建

alter table temp modify id int primary key;#修改id为主键

 创建表的基础语句

CREATE DATABASE `sign` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `s_admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_name` varchar(30) DEFAULT NULL,
  `admin_password` varchar(45) DEFAULT NULL,
  `admin_email` varchar(80) DEFAULT NULL,
  `admin_tel` varchar(45) DEFAULT NULL,
  `admin_status` int(11) DEFAULT NULL,
  `last_time` datetime DEFAULT NULL,
  `create_by` varchar(45) DEFAULT NULL,
  `update_by` varchar(45) DEFAULT NULL,
  `create_on` datetime DEFAULT NULL,
  `update_on` datetime DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `s_user` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) DEFAULT NULL,
  `user_mobile` varchar(45) DEFAULT NULL,
  `create_by` varchar(30) DEFAULT NULL,
  `update_by` varchar(30) DEFAULT NULL,
  `create_on` datetime DEFAULT NULL,
  `update_on` datetime DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111121 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `sp_user_account` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `AVAILABLE_AMOUNT` decimal(12,2) NOT NULL DEFAULT '0.00',
  `STATUS` int(2) NOT NULL,
  `USER_ID` int(11) NOT NULL,
  `VERSION_OPTIMIZED_LOCK` int(11) NOT NULL,
  `CREATED_BY` varchar(32) DEFAULT NULL,
  `CREATED_ON` datetime NOT NULL,
  `UPDATED_BY` varchar(32) DEFAULT NULL,
  `UPDATED_ON` datetime DEFAULT NULL,
  `FREEZE_AMOUNT` decimal(12,2) NOT NULL DEFAULT '0.00',
  `AVAILABLE_WITHDRAW_AMT` decimal(12,2) NOT NULL DEFAULT '0.00',
  `MIDDLE_AMT` decimal(12,2) DEFAULT NULL,
  `audit_status` int(2) DEFAULT NULL,
  `tty_amount` decimal(12,2) DEFAULT NULL,
  `tty_income` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_ACCOUNT_USERID` (`USER_ID`),
  CONSTRAINT `FK_ACCOUNT_USERID` FOREIGN KEY (`USER_ID`) REFERENCES `sp_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112455 DEFAULT CHARSET=utf8;

 数据库test

create table person
 (
   person_id int(11) unsigned,#unsigned代表无符号正整数
   fname varchar(20),
   lname varchar(20),
   gender enum ('M','F'),#性别只能呢在M或F中（MySQL）：gerder char(1) check(gender in('M','F')),(oracle等)
   birth_date date,
   street varchar(30),
   city varchar(20),
   state varchar(20),
   country varchar(20),
   postal_code varbinary(20),
   constraint pk_person primary key (person_id) #申明约束为主键约束
);

防止主键冲突设置自增：

alter table person modify person_id int(11) unsigned auto_increment;
或者
ALTER TABLE `mytest`.`person` CHANGE COLUMN `person_id` `person_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ;

查看表结构

create table favorite_food
(
person_id int(11) unsigned,
food varchar(20),
constraint pk_favorite_food primary key (person_id,food),#主键为person_id 和food
constraint fk_fav_food_person_id foreign key (person_id) references person (person_id)#外键为:person_id 依赖的是person表的person_id
);