【SQL】MySql记录

一、常用语句

一、建立、删除

建立数据库
CREATE DATABASE `mybatis`;
选择数据库
USE `mybatis`;
查看表
SHOW TABLES;
建立表
CREATE TABLE `user`(
	`id` INT(20) not null PRIMARY key,
	`name` VARCHAR(30) DEFAULT null,
	`pwd` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;


DROP TABLE `user`; # 删除表
例子：部门表和员工表

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`(
	id INT(11) NOT NULL PRIMARY KEY COMMENT'部门编号（主键）',
	department VARCHAR(50) COMMENT'部门名称'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE employee(
	id INT(11) NOT NULL PRIMARY KEY COMMENT'员工编号',
	lastName VARCHAR(50) COMMENT'姓名',
	email VARCHAR(50) COMMENT'邮箱',
	gender INT(2) COMMENT'性别',
	departmentId INT(11) NOT NULL COMMENT'对应部门表的外键',
	birth datetime COMMENT'员工生日',
	FOREIGN KEY(departmentId) REFERENCES departemt(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

二、插入

INSERT INTO `user`(`id`, `name`, `pwd`) VALUES 
(1, 'zs', '123456'),
(2, 'ls', '223456'),
(3, 'ww', '323456')

、查询

SELECT 列名1,列名2 FROM 表名 WHERE 列名1 运算符 值
select * from smbms_user u where u.userRole=3;
select * from smbms_user u where u.userRole<3;

# and
select * from smbms_user u where u.userRole<3 and gender=1;

#查询符合的总数
select COUNT(*) as count 
from smbms_user u,smbms_role r 
where u.userRole=r.id and u.userName=? and u.userRole=?;

# 模糊查询 LIKE
select *
from smbms_user u
where u.userName like '%张%';
返回所有userName中含有张字的信息

# 降序排序 并按照 限定取第6到第6+5个（序号从0开始）
select *
from smbms_user u
where u.id>0
order by creationDate DESC
limit 6,5 ;	# 如果只有一个参数 例如limit 5; 则查询的是从0开始的五个

# 将r表中roleName 列按照u.userRole=r.id追加一列userRoleName到查询到的表中
select u.*,r.roleName as userRoleName
from smbms_user u,smbms_role r
where u.userRole=r.id
order by creationDate DESC limit 0,5;

更新

update mybatis.user set name=#{name}, pwd=#{pwd} where id=#{id}

笔记

四种常用数据库引擎

• InnoDB
  • 默认的，事务型首选，支持事务安全表（ACID）
• MyISAM
  • 较高的插入、查询速度 但不支持事务
• MEMORY
  • 表中的数据存储到内存中
• Archive
  • 支持高并发的插入操作 事务不安全

总结：

1. 事务型选InnoDB
2. 如果只是插入和查询记录，使用MyISAM提供较高的效率
3. 临时存放数据，安全性不高，暂放于内存中，使用MEMORY
4. Archive适合存储归档数据，如记录日志信息

问题记录

无法连接数据库
net start mysql显示服务正在启动或停止中，请稍候片刻后再试一次

查看进程
tasklist| findstr "mysql"
杀死进程
taskkill /f /t /im mysqld.exe
重开
net start mysql

案例记录

1. 员工表记录

创建表
CREATE TABLE `department`(
                             id INT(11) NOT NULL PRIMARY KEY COMMENT'部门编号（主键）',
                             department VARCHAR(50) COMMENT'部门名称'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE employee(
                         id INT(11) NOT NULL PRIMARY KEY COMMENT'员工编号',
                         lastName VARCHAR(50) COMMENT'姓名',
                         email VARCHAR(50) COMMENT'邮箱',
                         gender INT(2) COMMENT'性别',
                         departmentId INT(11) NOT NULL COMMENT'对应部门表的外键',
                         birth datetime COMMENT'员工生日',
                         FOREIGN KEY(departmentId) REFERENCES department(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into department(id, department) values
(1, '技术部'),
(2, '市场部'),
(3, '调研部'),
(4, '后勤部'),
(5, '运营部');

insert into employee(id, lastName, email, gender, departmentId, birth) values
(1, 'AA', 'A@qq.com', 1, 1,'2001-08-01'),
(2, 'BB', 'B@qq.com', 0, 2,'2002-08-01'),
(3, 'CC', 'C@qq.com', 1, 3,'2003-08-01'),
(4, 'DD', 'D@qq.com', 0, 4,'2004-08-01'),
(5, 'EE', 'E@qq.com', 1, 5,'2005-08-01');

select e.id, e.lastName, e.email, e.gender, d.id departmentId, d.department, e.birth from employee e, department d where e.departmentId=d.id;

alter table employee modify id int(11) auto_increment;	# 改为自增

insert into employee(id, lastName, email, gender, departmentId, birth) value
(null, 'AA', 'A@qq.com', 1, 1,'2001-08-01');