mysql基本操作

# mysql基本操作

展示查询数据库

 show databases;//展示查询数据库

创建数据库，数据库表
- 可视化界面
- sql语句

CREATE TABLE user(
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL DEFAULT '',
	`age` TINYINT UNSIGNED NOT NULL DEFAULT 0,
	`gender` ENUM('男', '女') NOT NULL DEFAULT '男',
	PRIMARY KEY (`id`),
	INDEX uname(`username`),
	INDEX age(`age`),
	INDEX gender(`gender`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

查看数据库下的表,--sql语句中的注释

use myblog;
-- 使用myblog数据库
show tables;

添加数据

 insert into users(username,`password`,realname)values('zhangsan','123','张三')
 -- password是关键字，需要加``中间，其他的可以不加

id	username	password	realname
1	zhangsan	123	张三

批量添加

 INSERT INTO `user` (`username`, `age`, `gender`) 
		VALUES ('reci', 6, '女'),('zMouse', 35, '男');

查询

 SELECT username,age,gender FROM user;
 SELECT * FROM user;

查询条件

 SELECT * FROM user WHERE gender='男';
 SELECT * FROM user WHERE gender='男' AND age<20;
 -- BETWEEN
  SELECT * FROM user WHERE age BETWEEN 1 AND 30;
 
 -- LIKE ,一般需要配合指定的通配符来运行：% -> 任意一个或多个字符，_ -> 一个任意字符
  SELECT * FROM user WHERE username LIKE 'o%';
  SELECT * FROM user WHERE username LIKE '%o';
  SELECT * FROM user WHERE username LIKE '%o%';
  SELECT * FROM user WHERE username LIKE '__o%';
 
 -- IN，范围选择
  SELECT * FROM user WHERE username='zMouse' OR username='reci';
  SELECT * FROM user WHERE username IN ('zMouse','reci');
 
 -- BETWEEN、LIKE、IN 都有对应的 NOT 
  SELECT * FROM user WHERE username NOT IN ('zMouse','reci');
 
 -- 去重
  SELECT DISTINCT gender FROM user;				-- 如果gender值完全一样的话，会被去重
  SELECT DISTINCT gender,username FROM user;	-- 如果gender和username值完全一样的话，会被去重
 -- 正则
  SELECT * FROM user WHERE age REGEXP '3|1';
  SELECT * FROM user WHERE username REGEXP '^o';
-- 排序
  SELECT * FROM user ORDER BY age DESC, id DESC;
-- 分组
  SELECT gender, count(gender) as count FROM user GROUP BY gender;
-- 限制偏移
  SELECT * FROM user LIMIT 2 OFFSET 1;
  SELECT * FROM user LIMIT 1,2;
-- 函数
  SELECT * FROM user WHERE username='zmouse';	-- 当前数据校对使用的区分大小写的模式
  SELECT * FROM user WHERE UCASE(username)=UCASE('zmouse');
-- <>不等于
 select * from users where state <>"0";
 -- state不等于0的时候

更新

 update users set `password`='1234';
 -- users表中所有密码重置为1234
 update users set `password`='123456' where username='apple1';
 --users表中username为apple1的密码改为123456

 SET SQL_SAFE_UPDATES=0;
 -- 当因更新安全模式出现警告时可以使用此来解决无法更新的问题

删除

 DELETE FROM `user`;	-- 删除也好格外小心
 DELETE FROM `user` WHERE `id`=8;

 -- 用一个字段state来判断是否应展示，1展示，2表示隐藏
 update users set state='0' where username='apple1';
 -- 软删除，数据库中还存在，可恢复

truncate删除的表自增长的字段会重新开始计算，而delete不会

truncate users

-- 多表查询

 SELECT * FROM user,message;

 SELECT * FROM user,message WHERE user.id=message.uid;

 SELECT * FROM user JOIN message ON user.id=message.uid;	-- 同上


 SELECT * FROM user LEFT JOIN message ON user.id=message.uid;
 SELECT * FROM user RIGHT JOIN message ON user.id=message.uid;



 SELECT USER.id as uid, user.username, USER.age, USER.gender,
  message.id as message_id, message.content FROM user 
  LEFT JOIN message ON user.id=message.uid;

← mysql redis →