mysql_数据库-增删改查-示例-语句分类-查看信息-转账-日期-备份/还原-备注-数据类型的属性-安装

创建表

CREATE DATABASE dt55 CHARACTER SET utf8;
USE dt55;
CREATE TABLE java成绩表(
	姓名 VARCHAR(40),
	班级 VARCHAR(20),
	Java成绩 FLOAT
)

删除表

DROP TABLE java成绩表;

添加数据

INSERT INTO `java成绩表`(姓名，班级，Java成绩) VALUES('王二麻子','dt55班',90.5)

插入

INSERT INTO `java成绩表` SET 姓名='李四';

删除

DELETE FROM `java成绩表` WHERE 姓名='王二麻子'

同时插入多条

INSERT INTO `java成绩表`(姓名,班级,java成绩)
VALUES('abc','dt55班',90.5),('def','dt55班',90.6),('ccc','dt55班',90.5)

查询 AS取别名（通过空格取别名）

SELECT 姓名,班级,java成绩 FROM `java成绩表`;
SELECT 姓名 FROM `java成绩表`;
SELECT * FROM `java成绩表`;
SELECT 姓名 FROM `java成绩表` WHERE java成绩>=60;
SELECT 姓名 AS 名字1 FROM `java成绩表` WHERE java成绩>=60;

示例

CREATE DATABASE dt55_mysql CHARACTER SET utf8;
USE dt55_mysql;
CREATE TABLE students(
							id INT,
							stuName VARCHAR(40),
							age INT,
							weight FLOAT
);
INSERT INTO students VALUES ( 1, '王二蛮子', 20, 30.5 ),( 2, '李四', 24, 62.5 ),(
	3,
	'王五',
22,
64.5);
SELECT id AS 主键,stuName 学生名,age AS 年龄,weight 体重 FROM students;
#给表取别名
SELECT s.id,stuName FROM students s;
#修改
UPDATE students SET stuName='王五11111',age=20 WHERE id=1;
#删除数据
DELETE FROM students WHERE 1=1;

语句分类

查看信息
SHOW CREATE DATABASE dt55_mysql;
SHOW DATABASES:查看mysql服务器软件下所有的库
#查看是连接哪个库
SELECT DATABASE()
转账
UPDATE bank SET money = money-200 WHERE bankNo='911';
UPDATE bank SET money = money+200 WHERE bankNo='110';
#修改指定库的编码
SHOW CREATE DATABASE dt55_mysql;
ALTER DATABASE dt55_mysql CHARACTER SET gbk;
#删除表
drop table 表名
#查询表
DESC 表名
SHOW CREATE TABLE bank;
#修改表名
RENAME TABLE bank TO aaa;
#往表添加字段
![在这里插入图片描述](https://img-blog.csdnimg.cn/c38f77ca65c040e4a2995096fda852f3.png)

ALTER TABLE aaa ADD gender VARCHAR(2);
#删除某一个字段
ALTER TABLE aaa DROP gender;
字段重命名
ALTER TABLE aaa CHANGE username usernames VARCHAR(40);

#-日期
ALTER TABLE aaa ADD birthday DATE;
ALTER TABLE aaa CHANGE birthday birthday DATETIME;
#备份/还原（要在某一个库下还原
mysqldump -uroot -p dt55_mysql>c:\aaa.sql
#还原
source aaa.sql;
show tables
select  *  from aaa
#备注

删表
数据类型的属性

安装

https://blog.csdn.net/weixin_42869365/article/details/83472466?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162859919116780261975272%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162859919116780261975272&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-1-83472466.pc_search_download_positive&utm_term=mysql%E4%B8%8B%E8%BD%BD&spm=1018.2226.3001.4187