mysql数据库语句实例

创建数据库
create database bsms；
连接数据库
use bsms;
建表
create table users(id int not null AUTO_INCREMENT PRIMARY KEY,name varchar(20) not null,password varchar(20) not null);
设置字段值唯一
alter table users add unique(name);
插入
insert into users(name,password) values('1001','123');
insert into users(name,password) values('admin','123');
insert into users(name,password) values('test','123');
insert into users(name,password) values('administrator','123');
insert into users(name,password) values('1002','123');
insert into users(name,password) values('byht','123');

create table employees(id int not null AUTO_INCREMENT PRIMARY KEY,e_name_ varchar(20) not null,e_no_ varchar(20) default null,e_dep_ varchar(20) default null,e_tel_ varchar(20) default null,e_mail_ varchar(30) default null,hiredate datetime default null,is_valid_ boolean not null default 1,operation_date_ timestamp default current_timestamp);
修改字段名称
alter table employees change column e_dep_ e_dep_id_ varchar(20);
修改字段类型
alter table employees modify e_dep_id_ int;

create table departments(id int not null AUTO_INCREMENT PRIMARY KEY,dep_name_ varchar(20));

create table books(id int not null AUTO_INCREMENT PRIMARY KEY,book_name_ varchar(20) not null,author_ varchar(20) default null,introduction_ varchar(100) default null,publication_date_ datetime default null,is_valid_ boolean not null default 1,is_borrow_ boolean not null default 0);

create table borrowbooks(id int not null AUTO_INCREMENT PRIMARY KEY,book_id_ int not null,e_name_id_ int not null,borrow_date_ timestamp default current_timestamp,return_date_ datetime default null);

create table booktypes(id int not null AUTO_INCREMENT PRIMARY KEY,book_type_ varchar(20));
增加列
alter table books add column book_type_id_ int default null;

alter table borrowbooks add column book_type_id_ int default null;

alter table booktypes add column book_type_no_ varchar(10) default null;

alter table booktypes add column user_id_ int default null;

alter table booktypes add column datetime_ timestamp default current_timestamp;

alter table books add column user_id_ int default null;

alter table books add column datetime_ timestamp default current_timestamp;

alter table departments add column user_id_ int default null;

alter table departments add column datetime_ timestamp default current_timestamp;

alter table employees add column user_id_ int default null;

alter table employees change column operation_date_ datetime_ timestamp default current_timestamp;

ALTER TABLE books ADD CONSTRAINT fk_book_type FOREIGN KEY (book_type_id_) REFERENCES booktypes(id) ON UPDATE CASCADE;

ALTER TABLE employees ADD CONSTRAINT fk_employee_dep FOREIGN KEY (e_dep_id_) REFERENCES departments(id) ON UPDATE CASCADE;

ALTER TABLE borrowbooks ADD CONSTRAINT fk_book_ FOREIGN KEY (book_id_) REFERENCES books(id) ON UPDATE CASCADE;

ALTER TABLE borrowbooks ADD CONSTRAINT fk_employee_ FOREIGN KEY (e_name_id_) REFERENCES employees(id) ON UPDATE CASCADE;

alter table borrowbooks add column user_id_ int default null;

alter table borrowbooks modify borrow_date_ datetime default null;

alter table borrowbooks add column datetime_ timestamp default current_timestamp;