mysql备份数据库 创建表 select的各种用法 以及还剩多少天

必须记住的命令行

mysql -u root -p

mysql -h 主机名（192.168.200.1）填你要链接的主机名 -P 3306端口号 -p

输入密码

在工作中精度用DOUBLE和DECIMAL不要用float

启动服务

net start mysql

关闭服务

net stop mysql

备份数据库

在dos（不是MYSQL下）命令下执行-B代表整个表

mysqldump -u root -p -B db02 > d:\\bak.sql

mysqldump -u root -p 数据库 db02 表2 > d:\\bak.sql

记到带上路径

恢复数据库（在进入MYSQL下执行命令）

mysql -u -root -p

SOURCE d:\\bak.sql

删除数据库

DROP DATABASE db02

查找数据库元素

SELECT * FROM users WHERE NAME='张三'

创建MySql表（注意表的位置）

CREATE TABLE `bobo`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

创建表emp,char里面要带字符，字符和字节的区别

CREATE TABLE `emp`(
	id INT,
	`name` VARCHAR(32),
	sex CHAR(1),
	brithday DATE,
	entryday DATETIME,
	job CHAR(32),
	salary FLOAT,
	`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;

写入数据到表中,记得加参数（id,name,sex,date,datetime,zhiye,sarlary,resume）

INSERT INTO `emp` 
	VALUES('100','唐波','男','1996-08-18','2019-11-11 11:12','程序员','5000',
	'我是bobo');

#增加image列

#查看表结构
DESC `emp`

修改job表属性

#增加image列
ALTER TABLE `emp` ADD image VARCHAR(32) AFTER RESUME
#查看表结构
DESC `emp`

#修改job type 为 varchar(80)默认不为空
ALTER TABLE emp
	MODIFY job VARCHAR(80) NOT NULL DEFAULT ''

删除表属性

ALTER TABLE emp
	DROP brithday;

修改表名

RENAME TABLE emp TO eeemmppp

修改列名

ALTER TABLE eeemmppp CHANGE `name`  `user_name` VARCHAR(50) NOT NULL DEFAULT ''

更新表数据

UPDATE eeemmppp SET salary = '9000' WHERE id ='120'

删除老妖怪的记录

DELETE FROM eemmpp
    WHERE user_name = '老妖怪';

各种select用法

CREATE TABLE students(
	`id` INT NOT NULL DEFAULT 1,
	`name` VARCHAR(20) NOT NULL DEFAULT '',
	`chinese` FLOAT NOT NULL DEFAULT '0.0',
	`english` FLOAT NOT NULL DEFAULT '0.0',
	`match` FLOAT NOT NULL DEFAULT '0.0'
);
INSERT INTO students(id,`name`,chinese,english,`match`) VALUES ('1','唐波','100','100','100'),
INSERT INTO students(id,`name`,chinese,english,`match`) VALUES ('2','张波','12','102','104');

SELECT * FROM students
SELECT * FROM students WHERE chinese =100
SELECT `name`,english FROM students WHERE english = 100
SELECT DISTINCT english FROM students
SELECT `name`,(chinese+english+`match`) FROM students
SELECT `name`,(chinese+english+`match`+10) FROM students
SELECT `name` AS `姓名`,(chinese+english+`match`+10) AS total FROM students

还剩多少天

SELECT * FROM students
	ORDER BY chinese
	
SELECT `name`,(chinese+english+`match`) AS total FROM students
	ORDER BY total DESC
	
SELECT COUNT(*) FROM students WHERE chinese = 100
SELECT COUNT(*) FROM students WHERE `match` > 100

SELECT SUM(english) FROM students
SELECT SUM(english) AS english,SUM(chinese) AS chinese,SUM(`match`) AS `match` FROM students

SELECT AVG(`match`) FROM students
SELECT AVG(`match`+english+chinese) FROM students

SELECT MAX(`chinese`) FROM students
SELECT MIN(`chinese`) FROM students

#重点
SELECT AVG(`match`),MAX(english) FROM students GROUP BY chinese HAVING chinese > 20、

#我活了多少天
SELECT DATEDIFF('2021-09-11','1996-08-18') FROM DUAL
SELECT DATEDIFF(NOW(),'1996-08-18') FROM DUAL

#活到80岁还剩多少天
SELECT DATEDIFF(DATE_ADD('1996-08-18',INTERVAL 80 YEAR),NOW()) FROM DUAL

创建索引和查询索引(UNIQUE索引和普通索引都能用），删除用DROP

SHOW TABLES
SELECT * FROM students

CREATE UNIQUE INDEX bobo ON students(`name`);

SHOW  UNIQUE INDEX FROM students

显示当前数据库

#显示当前用户以及IP
SELECT USER() FROM DUAL

#当前数据库
SELECT DATABASE() FROM DUAL

#MD5加密
SELECT MD5('唐波6768787') FROM DUAL

大写转小写

SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) FROM emp

显示姓名中不带R的 所有人得名字

SELECT * FROM EMP 
        where ename not like "%R%"

显示员工姓名前三个字符

select LEFT(ename,3)
        from emp

将所有大写写的A替换成小写的a

select replace(ename,"A","a") from emp

工作超过十年的

select ename,hiredate
        from emp
        where DATE_ADD(hiredate,INTERVAL 10 YESR)< NOW()

创建新用户格式

CREATE USER 'bobo2' @'localhost' IDENTIFIED BY '123456'

bobo2修改自己的密码 set password=password('1234567')

root 修改波波的密码可以成功

set password for 'bobo2' @ 'localhost'=password('1234567')

root用户给bobo2查看表的权限

GRANT SELECT,INSERT
	ON db02.students 
	TO 'bobo2' @'localhost'

给bobo2修改权限

GRANT UPDATE,INSERT
	ON db02.students 
	TO 'bobo2' @'localhost'

查看用户

SELECT * FROM mysql.user