MySQL 操作练习

最新推荐文章于 2024-07-09 22:23:59 发布
最新推荐文章于 2024-07-09 22:23:59 发布
阅读量368 收藏 1
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/d_shaoshuai/article/details/108617682
版权

目录

操作 MySQL

启动 MySQL

net start mysql

登录本机 MySQL 数据库

mysql -u root -p

获取服务器版本信息

SELECT VERSION( );

当前用户名

SELECT USER( );

操作数据库

列出所有数据库列表

SHOW DATABASES;

选择要操作的数据库

USE study_database;

当前数据库名

SELECT DATABASE( );

操作表

创建数据表

CREATE TABLE hero(
	id_h INT NOT NULL AUTO_INCREMENT,
	name_h VARCHAR(20) NOT NULL,
	gender CHAR(2),
	age INT,
	type_h CHAR(4) NOT NULL,
	money INT,
	region VARCHAR(20) DEFAULT '王者峡谷',
	PRIMARY KEY (id_h),
	CONSTRAINT check_name_error UNIQUE (name_h),
	CONSTRAINT check_gender_error CHECK (gender IN('男','女')),
	CONSTRAINT check_age_error CHECK (age BETWEEN 10 AND 120),
	CONSTRAINT check_type_error CHECK (type_h IN('刺客','坦克','战士','射手','法师','辅助'))
);

CREATE TABLE persons(
	id_p INT NOT NULL AUTO_INCREMENT,
	name_p VARCHAR(20) NOT NULL,
	gender CHAR(2) NOT NULL,
	PRIMARY KEY (id_p),
	CONSTRAINT check_name_error UNIQUE (name_p),
	CONSTRAINT check_gender CHECK(gender IN('男','女'))
);

CREATE TABLE orders(
	id_o INT NOT NULL AUTO_INCREMENT,
	id_h INT NOT NULL,
	id_p INT NOT NULL,
	PRIMARY KEY (id_o),
	FOREIGN KEY (id_h) REFERENCES hero(id_h),
	FOREIGN KEY (id_p) REFERENCES persons(id_p)
);

删除 hero 表

DROP TABLE hero;

增加一列

ALTER TABLE persons ADD COLUMN like_h INT;

删除一列

ALTER TABLE persons DROP COLUMN like_h;

添加外键

ALTER TABLE persons ADD FOREIGN KEY (like_h) REFERENCES hero(id_h);

备份表

CREATE TABLE hero_new AS SELECT * FROM hero;
CREATE TABLE hero_new2 AS SELECT name_h,type_h FROM hero;

增

INSERT INTO hero(name_h,gender,age,type_h,money,region) VALUES('吕布','男',28,'战士',18888,'三国');

INSERT INTO hero(name_h,gender,age,type_h,money,region) VALUES
	('貂蝉','女',22,'法师',13888,'三国'),
	('后羿','男',110,'射手',6888,'神话'),
	('项羽','男',40,'坦克',7888,'楚汉'),
	('刘邦','男',22,'坦克',13888,'楚汉'),
	('嬴政','男',28,'法师',18888,'秦朝'),
	('小乔','女',20,'法师',7888,'三国'),
	('蔡文姬','女',13,'辅助',13888,'三国'),
	('孙悟空','男',22,'刺客',18888,'神话'),
	('李白','男',24,'刺客',18888,'唐朝');
	
INSERT INTO hero(name_h,type_h,region) VALUES
	('狄仁杰','射手','唐朝'),
	('孙策','战士','三国'),
	('马可波罗','射手','歪果仁'),
	('宫本武藏','战士','日本'),
	('橘右京','战士','SNK');
	
INSERT INTO hero(name_h,gender,age,type_h,money) VALUES('英雄1号','男',10,'辅助',0);

INSERT INTO persons(name_p,gender) VALUES
	('小波','男'),
	('安安','女'),
	('小鱼','男'),
	('老刀','男'),
	('逍遥','男'),
	('渡边','男'),
	('福丫','女'),
	('马田','男'),
	('心如','女');
	
INSERT INTO orders(id_h,id_p) VALUES
	(1,5),(2,1),(3,3),(4,4),(5,4),(6,3),(3,6),
	(2,7),(2,8),(2,5),(4,6),(4,7),(9,3),(1,6),
	(1,3),(2,6),(12,2),(10,3),(12,3),(11,4),(4,2);

删

删除所有数据

DELETE FROM hero;

根据条件删除

DELETE FROM hero WHERE name_h = '英雄1号';

改

UPDATE hero SET money = 18888 WHERE name_h = '宫本武藏';

UPDATE hero SET 
name_h = '大乔',gender = '女',age = 21,type_h = '辅助',money = 8888,region = '三国'
WHERE name_h = '英雄1号';

查

查询所有数据

SELECT * FROM hero;

查询部分字段

SELECT name_h,money FROM hero;
SELECT name_h,money FROM hero WHERE money>10000;
SELECT name_h,money,type_h FROM hero WHERE type_h = '战士' OR type_h = '法师';
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师');

排序

SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师') ORDER BY money;
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师') ORDER BY money ASC;
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师') ORDER BY money DESC;

选取唯一不同的值

SELECT DISTINCT type_h FROM hero;

Where 子句中的运算符

SELECT name_h,money FROM hero WHERE money = 13888;
SELECT name_h,money FROM hero WHERE money <> 13888 ORDER BY money;
SELECT name_h,money FROM hero WHERE money < 13888 ORDER BY money;
SELECT name_h,money FROM hero WHERE money >= 13888 ORDER BY money;
SELECT name_h,region FROM hero WHERE region IN('神话','楚汉');
SELECT name_h,region FROM hero WHERE region NOT IN('神话','楚汉');

查询两个数值之间的数据

SELECT name_h,money FROM hero WHERE money BETWEEN 13888 AND 18888 ORDER BY money;

通配符

SELECT name_h,region FROM hero WHERE region LIKE '三%';
SELECT name_h,region FROM hero WHERE region LIKE '三_';
SELECT name_h,region FROM hero WHERE region RLIKE '^[神楚]';
SELECT name_h,region FROM hero WHERE region NOT RLIKE '^[神楚]';
SELECT name_h,region FROM hero WHERE region RLIKE '^[A-Z]';
SELECT name_h,region FROM hero WHERE region NOT RLIKE '^[A-Z]';

查询指定数量数据

SELECT * FROM hero LIMIT 10;
SELECT * FROM hero ORDER BY id_h DESC LIMIT 10 ;

别名

SELECT name_h AS 名称,money AS 出售金币 FROM hero;
SELECT name_h AS 名称,money AS 出售金币,type_h AS 位置 FROM hero WHERE type_h = '战士';
SELECT h.name_h,h.gender,h.age,h.type_h,h.money,h.region FROM hero AS h;

根据外键 like_h 表查询 person 喜欢使用的英雄

SELECT name_p,name_h FROM hero,persons WHERE hero.id_h = persons.like_h;

JOIN

  • INNER JOIN
    如果表中有至少一个匹配，则返回行
SELECT name_p,name_h FROM hero INNER JOIN persons ON hero.id_h = persons.like_h;
  • LEFT JOIN
    即使右表中没有匹配，也从左表返回所有的行
SELECT name_p,name_h FROM persons LEFT JOIN hero ON hero.id_h = persons.like_h;
  • RIGHT JOIN
    即使左表中没有匹配，也从右表返回所有的行
SELECT name_p,name_h FROM persons RIGHT JOIN hero ON hero.id_h = persons.like_h;

合并两个或多个 select 语句的结果

必须相似数据类型，数量一致

  • 默认选取不同的值
SELECT name_p FROM persons
UNION 
SELECT name_h FROM hero;
  • 如果允许重复的值，使用 UNION ALL
SELECT name_p FROM persons
UNION 
SELECT name_h FROM hero;

分组查询

SELECT region,COUNT(*) FROM hero GROUP BY region ORDER BY COUNT(*);

函数

返回数值列的平均值

SELECT AVG(money) FROM hero;
SELECT AVG(money) AS 平均价格 FROM hero;

/** 返回大于平均值的内容 **/
SELECT name_h,money FROM hero WHERE money>(SELECT AVG(money) FROM hero);

返回数量

/** 返回总数目 **/
SELECT COUNT(*) FROM hero;
/** 返回总数目（NULL不计入）**/
SELECT COUNT(age) FROM hero;
SELECT COUNT(money) FROM hero;
/** 返回不同值数目 **/
SELECT COUNT(DISTINCT money) FROM hero;
SELECT DISTINCT money FROM hero ORDER BY money;

返回一列最大值

SELECT MAX(money) AS max_value FROM hero;

返回一列最小值

SELECT MIN(money) FROM hero;

返回数值列的总数

SELECT SUM(money) FROM hero;

SELECT region,SUM(money) FROM hero GROUP BY region;
SELECT region,SUM(money) FROM hero GROUP BY region HAVING SUM(money)<35000;
SELECT region,SUM(money) FROM hero WHERE region = '王者峡谷' OR region = '秦朝' GROUP BY region HAVING SUM(money) > 10000;

转换大小写

SELECT UCASE(name_p) FROM persons ORDER BY id_p DESC;//大写
SELECT LCASE(name_P) FROM persons ORDER BY id_p DESC;//小写

从文本中提取字符串

/** 从name_h列中从第1个位置取1个值**/
SELECT name_h,MID(name_h,1,1) FROM hero;
/** 从name_h列中从第2个位置取2个值**/
SELECT name_h,MID(name_h,2,2) FROM hero;
SELECT name_h,MID(name_h,2,3) FROM hero;

返回字符串长度

/** 一个中文算3个字符？ **/
SELECT name_p,LENGTH(name_p) FROM persons;

四舍五入为指定的小数位数

CREATE TABLE testfloat(
	price FLOAT NOT NULL
);
INSERT INTO testfloat VALUES (10.6);
INSERT INTO testfloat VALUES (10.12);
INSERT INTO testfloat VALUES (10.123);
INSERT INTO testfloat VALUES (10.1234);
INSERT INTO testfloat VALUES (10.12345);
INSERT INTO testfloat VALUES (10.123456);
INSERT INTO testfloat VALUES (10.1234567);
/** 整数不会显示出小数点 .000000000 **/
SELECT money,ROUND(money,10) FROM hero;
/** 保留几位小数 **/
SELECT price,ROUND(price,0) FROM testfloat;
SELECT price,ROUND(price,1) FROM testfloat;
SELECT price,ROUND(price,2) FROM testfloat;
SELECT price,ROUND(price,3) FROM testfloat;

返回当前系统的日期和时间

SELECT *,NOW() AS 查询时间 FROM persons;

遇到的问题（执行失败）

  • FULL JOIN：只要其中一个表中存在匹配，则返回行
SELECT name_p,name_h FROM persons FULL OUTER JOIN hero ON hero.id_h = persons.like_h;


SHOW DATABASES;

USE study_database;

CREATE TABLE hero(
	id_h INT NOT NULL AUTO_INCREMENT,
	name_h VARCHAR(20) NOT NULL,
	gender CHAR(2),
	age INT,
	type_h CHAR(4) NOT NULL,
	money INT,
	region VARCHAR(20) DEFAULT '王者峡谷',
	PRIMARY KEY (id_h),
	CONSTRAINT check_name_error UNIQUE (name_h),
	CONSTRAINT check_gender_error CHECK (gender IN('男','女')),
	CONSTRAINT check_age_error CHECK (age BETWEEN 10 AND 120),
	CONSTRAINT check_type_error CHECK (type_h IN('刺客','坦克','战士','射手','法师','辅助'))
);
INSERT INTO hero(name_h,gender,age,type_h,money,region) VALUES('吕布','男',28,'战士',18888,'三国');
INSERT INTO hero(name_h,gender,age,type_h,money,region) VALUES
	('貂蝉','女',22,'法师',13888,'三国'),
	('后羿','男',110,'射手',6888,'神话'),
	('项羽','男',40,'坦克',7888,'楚汉'),
	('刘邦','男',22,'坦克',13888,'楚汉'),
	('嬴政','男',28,'法师',18888,'秦朝'),
	('小乔','女',20,'法师',7888,'三国'),
	('蔡文姬','女',13,'辅助',13888,'三国'),
	('孙悟空','男',22,'刺客',18888,'神话'),
	('李白','男',24,'刺客',18888,'唐朝');
INSERT INTO hero(name_h,type_h,region) VALUES
	('狄仁杰','射手','唐朝'),
	('孙策','战士','三国'),
	('马可波罗','射手','歪果仁'),
	('宫本武藏','战士','日本'),
	('橘右京','战士','SNK');
	
UPDATE hero SET money = 18888 WHERE name_h = '宫本武藏';
	
	
DROP TABLE hero;

SELECT * FROM hero;
SELECT name_h,money FROM hero;
SELECT name_h AS 名称,money AS 出售金币 FROM hero;
SELECT name_h AS 名称,money AS 出售金币,type_h AS 位置 FROM hero WHERE type_h = '战士';
SELECT name_h,money FROM hero WHERE money>10000;
SELECT name_h,money,type_h FROM hero WHERE type_h = '战士' AND money > 10000;
SELECT name_h,money,type_h FROM hero WHERE type_h = '战士' OR type_h = '法师';
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师');
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师') ORDER BY money;
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师') ORDER BY money ASC;
SELECT name_h,money,type_h FROM hero WHERE type_h IN('战士','法师') ORDER BY money DESC;
SELECT DISTINCT type_h FROM hero;
SELECT name_h,money FROM hero WHERE money = 13888;
SELECT name_h,money FROM hero WHERE money <> 13888 ORDER BY money;
SELECT name_h,money FROM hero WHERE money < 13888 ORDER BY money;
SELECT name_h,money FROM hero WHERE money >= 13888 ORDER BY money;
SELECT name_h,money FROM hero WHERE money BETWEEN 13888 AND 18888 ORDER BY money;
SELECT name_h,region FROM hero WHERE region LIKE '三%';
SELECT name_h,region FROM hero WHERE region LIKE '三_';
SELECT name_h,region FROM hero WHERE region RLIKE '^[神楚]';
SELECT name_h,region FROM hero WHERE region NOT RLIKE '^[神楚]';
SELECT name_h,region FROM hero WHERE region RLIKE '^[A-Z]';
SELECT name_h,region FROM hero WHERE region NOT RLIKE '^[A-Z]';

SELECT name_h,region FROM hero WHERE region IN('神话','楚汉');
SELECT name_h,region FROM hero WHERE region NOT IN('神话','楚汉');


INSERT INTO hero(name_h,gender,age,type_h,money) VALUES('英雄1号','男',10,'辅助',0);
DELETE FROM hero WHERE name_h = '英雄1号';
UPDATE hero SET 
name_h = '大乔',gender = '女',age = 21,type_h = '辅助',money = 8888,region = '三国'
WHERE name_h = '英雄1号';

DELETE FROM hero;

SELECT * FROM hero LIMIT 10;
SELECT * FROM hero ORDER BY id_h DESC LIMIT 10 ;

SELECT h.name_h,h.gender,h.age,h.type_h,h.money,h.region FROM hero AS h;

CREATE TABLE persons(
	id_p INT NOT NULL AUTO_INCREMENT,
	name_p VARCHAR(20) NOT NULL,
	gender CHAR(2) NOT NULL,
	PRIMARY KEY (id_p),
	CONSTRAINT check_name_error UNIQUE (name_p),
	CONSTRAINT check_gender CHECK(gender IN('男','女'))
);

INSERT INTO persons(name_p,gender) VALUES
	('小波','男'),
	('安安','女'),
	('小鱼','男'),
	('老刀','男'),
	('逍遥','男'),
	('渡边','男'),
	('福丫','女'),
	('马田','男'),
	('心如','女');
		
SELECT * FROM persons;

CREATE TABLE orders(
	id_o INT NOT NULL AUTO_INCREMENT,
	id_h INT NOT NULL,
	id_p INT NOT NULL,
	PRIMARY KEY (id_o),
	FOREIGN KEY (id_h) REFERENCES hero(id_h),
	FOREIGN KEY (id_p) REFERENCES persons(id_p)
);

INSERT INTO orders(id_h,id_p) VALUES
	(1,5),(2,1),(3,3),(4,4),(5,4),(6,3),(3,6),
	(2,7),(2,8),(2,5),(4,6),(4,7),(9,3),(1,6),
	(1,3),(2,6),(12,2),(10,3),(12,3),(11,4),(4,2);


SELECT COUNT(*) FROM hero;
SELECT COUNT(*) FROM persons;
SELECT * FROM orders;
DROP TABLE orders;
SELECT * FROM hero;
SELECT * FROM persons;
SELECT * FROM orders;

SELECT h.name_h,h.type_h,h.money,p.name_p 
FROM hero AS h,persons AS p,orders AS o
WHERE o.id_h = h.id_h AND o.id_p = p.id_p;

SELECT hero.name_h,persons.name_p FROM hero,persons INNER JOIN orders WHERE orders.id_h = hero.id_h AND orders.id_p = persons.id_p;

ALTER TABLE persons ADD COLUMN like_h INT;
ALTER TABLE persons ADD FOREIGN KEY (like_h) REFERENCES hero(id_h);

USE study_database;

SELECT name_p,name_h FROM hero,persons WHERE hero.id_h = persons.like_h;

SELECT name_p,name_h FROM hero INNER JOIN persons ON hero.id_h = persons.like_h;

SELECT name_p,name_h FROM persons LEFT JOIN hero ON hero.id_h = persons.like_h;
SELECT name_p,name_h FROM persons RIGHT JOIN hero ON hero.id_h = persons.like_h;
SELECT name_p,name_h FROM persons FULL OUTER JOIN hero ON hero.id_h = persons.like_h;

SELECT name_p FROM persons
UNION ALL
SELECT name_h FROM hero;

CREATE TABLE hero_new AS SELECT * FROM hero;
CREATE TABLE hero_new2 AS SELECT name_h,type_h FROM hero;

SELECT * FROM hero_new2;

ALTER TABLE hero ADD COLUMN test INT;
ALTER TABLE hero ADD FOREIGN KEY (test) REFERENCES persons(id_p);
ALTER TABLE hero DROP FOREIGN KEY test;

ALTER TABLE hero DROP COLUMN test;
SELECT * FROM persons;

ALTER TABLE persons ADD COLUMN test INT;
ALTER TABLE persons DROP COLUMN test;

SELECT COUNT(*) FROM hero;


SELECT *,NOW() AS 查询时间 FROM persons;


/** 返回平均值 **/
SELECT AVG(money) FROM hero;
SELECT AVG(money) AS 平均价格 FROM hero;
/** 返回大于平均值的内容 **/
SELECT name_h,money FROM hero WHERE money>(SELECT AVG(money) FROM hero);

/** 返回总数目 **/
SELECT COUNT(*) FROM hero;
/** 返回总数目（NULL不计入）**/
SELECT COUNT(age) FROM hero;
SELECT COUNT(money) FROM hero;
/** 返回不同值数目 **/
SELECT COUNT(DISTINCT money) FROM hero;
SELECT DISTINCT money FROM hero ORDER BY money;

/** 返回一列中最大值 **/
SELECT MAX(money) AS max_value FROM hero;
SELECT MIN(money) FROM hero;

SELECT SUM(money) FROM hero;

SELECT region,SUM(money) FROM hero GROUP BY region;
SELECT region,SUM(money) FROM hero GROUP BY region HAVING SUM(money)<35000;
SELECT region,SUM(money) FROM hero WHERE region = '王者峡谷' OR region = '秦朝' GROUP BY region HAVING SUM(money) > 10000;

INSERT INTO persons(name_p,gender) VALUES ('ABCD','女');
SELECT UCASE(name_p) FROM persons ORDER BY id_p DESC;
SELECT LCASE(name_P) FROM persons ORDER BY id_p DESC;

/** 从name_h列中从第1个位置取1个值**/
SELECT name_h,MID(name_h,1,1) FROM hero;
/** 从name_h列中从第2个位置取2个值**/
SELECT name_h,MID(name_h,2,2) FROM hero;
SELECT name_h,MID(name_h,2,3) FROM hero;

SELECT name_h,LENGTH(name_h) FROM hero;



SHOW DATABASES;

CREATE DATABASE testCreate;

USE arrange;


CREATE TABLE persons(
	id_p INT NOT NULL AUTO_INCREMENT,
	name_p VARCHAR(10) NOT NULL,
	gender VARCHAR(1) NOT NULL,
	age INT,
	email VARCHAR(255),
	city VARCHAR(20),
	PRIMARY KEY (id_p)
);

INSERT INTO persons(name_p,gender,age,email,city) VALUES ('小波','男',18,'xiaobo@163.com','运城');
INSERT INTO persons(name_p,gender,age,email,city) VALUES ('安安','女',19,'anan@163.com','岳阳');
INSERT INTO persons(persons.name_p,persons.gender,persons.age) VALUES ('小鱼','男',20);
INSERT INTO persons(persons.name_p,persons.gender,persons.city) VALUES ('逍遥','男','大理');
INSERT INTO persons(persons.name_p,persons.gender,persons.email) VALUES ('老刀','男','laodao@163.com');
INSERT INTO persons(name_p,gender,age,city) VALUES ('安安','女',20,'岳阳');
INSERT INTO persons(name_p,gender,age,city) VALUES ('小鱼','男',23,'揭阳');
INSERT INTO persons(name_p,gender,age,city) VALUES ('小鱼','男',19,'汕头');
INSERT INTO persons(name_p,gender,age,city) VALUES ('小鱼','男',20,'汕尾');
INSERT INTO persons(name_p,gender) VALUES ('渡边','男');
INSERT INTO persons(name_p,gender) VALUES ('马田','男');
INSERT INTO persons(name_p,gender) VALUES ('福丫','女');
INSERT INTO persons(name_p,gender) VALUES ('jack','男');
INSERT INTO persons(name_p,gender) VALUES ('teddy','男');
INSERT INTO persons(name_p,gender) VALUES ('abby','女');
INSERT INTO persons(name_p,gender) VALUES ('cara','女');
INSERT INTO persons(name_p,gender) VALUES ('男一号','男');
INSERT INTO persons(name_p,gender) VALUES ('女一号','男');
INSERT INTO persons(name_p,gender) VALUES ('男二号','男');
INSERT INTO persons(name_p,gender) VALUES ('女二号','女');

SELECT * FROM persons;
SELECT name_p,gender FROM persons;
SELECT name_p,gender FROM persons WHERE gender = '女';
SELECT * FROM persons WHERE name_p = '小鱼';
SELECT DISTINCT name_p FROM persons;
SELECT * FROM persons WHERE age = 20;
SELECT * FROM persons WHERE age<>20;
SELECT * FROM persons WHERE age >=20;
SELECT * FROM persons WHERE age BETWEEN 19 AND 20 ORDER BY age;
SELECT * FROM persons WHERE age NOT BETWEEN 19 AND 20 ORDER BY age;
SELECT * FROM persons WHERE name_p LIKE '小%';
SELECT * FROM persons WHERE name_p LIKE '%鱼';
SELECT * FROM persons WHERE email LIKE '%@163%';
SELECT * FROM persons WHERE email LIKE '%@qq%';
SELECT * FROM persons WHERE name_p LIKE '小_';
SELECT * FROM persons WHERE name_p LIKE '[ac]%';/** 查找不出来 **/
SELECT * FROM persons WHERE name_p RLIKE '^[ac]';
SELECT * FROM persons WHERE name_p RLIKE '^[小安]';

SELECT * FROM persons WHERE name_p LIKE 'a%' OR name_p LIKE 'c%';
SELECT * FROM persons WHERE name_p = '小鱼' AND city = '汕头';
SELECT * FROM persons WHERE name_p = '逍遥' OR name_p = '老刀';
SELECT name_p,age FROM persons ORDER BY age; 
SELECT * FROM persons ORDER BY age DESC;
SELECT * FROM persons ORDER BY id_p DESC;
SELECT * FROM persons LIMIT 5;
SELECT * FROM persons WHERE age IN (18,20);
SELECT * FROM persons WHERE name_p IN ('逍遥','老刀');

UPDATE persons SET email = 'dubian@163.com' WHERE name_p = '渡边';
UPDATE persons SET email = 'matian@163.com' WHERE name_p = '马田';
UPDATE persons SET email = 'fuya@163.com' WHERE name_p = '福丫';
UPDATE persons SET email = 'xiaoyu_shantou' WHERE name_p = '小鱼' AND city = '汕头';
UPDATE persons SET email = 'xiaoyu_jieyang' WHERE name_p = '小鱼' AND city = '揭阳';
UPDATE persons SET email = 'xiaoyu_shantou@163.com' WHERE name_p = '小鱼' AND email = 'xiaoyu_shantou';
UPDATE persons SET email = 'xiaoyu_jieyang@163.com' WHERE name_p = '小鱼' AND email = 'xiaoyu_jieyang';
UPDATE persons SET email = 'dubian@qq.com' WHERE name_p = '渡边';
UPDATE persons SET email = 'matian@qq.com' WHERE name_p = '马田';
UPDATE persons SET email = 'fuya@qq.com' WHERE name_p = '福丫';

DELETE FROM persons;
DELETE FROM persons WHERE name_p = '男一号';

CREATE TABLE orders(
	id_o INT NOT NULL AUTO_INCREMENT,
	name_o VARCHAR(20) NOT NULL,
	gender VARCHAR(1),
	money INT,
	region VARCHAR(20),
	PRIMARY KEY (id_o)
);

ALTER TABLE orders ADD COLUMN id_p INT NOT NULL;
INSERT INTO orders(name_o,gender,money,id_p) VALUES ('李白','男',88,1);
INSERT INTO orders(name_o,gender,money,region,id_p) VALUES ('吕布','男',78,'三国',1);
INSERT INTO orders(name_o,gender,money,region,id_p) VALUES ('貂蝉','女',168,'三国',1);
INSERT INTO orders(name_o,gender,money,id_p) VALUES ('狂铁','男',68,4);
INSERT INTO orders(name_o,gender,money,id_p) VALUES ('兰陵王','男',128,2);
INSERT INTO orders(name_o,gender,money,id_p) VALUES ('后羿','男',228,2);
INSERT INTO orders(name_o,gender,money,id_p) VALUES ('测试应用','男',228,-1);
INSERT INTO orders(name_o,gender,money,id_p) VALUES ('测试 CHECK 报错','男',228,0);

UPDATE orders SET region = '神话' WHERE name_o = '后羿';
DELETE FROM orders WHERE name_o = '后羿' AND id_p = '0';

SELECT * FROM orders;
DROP TABLE orders;

ALTER TABLE orders ADD CONSTRAINT check_idP_error CHECK (id_p > 0);

SELECT o.name_o,o.money,p.name_p FROM persons AS p,orders AS o WHERE o.id_p = p.id_p;
SELECT o.name_o,o.money,p.name_p FROM persons AS p,orders AS o WHERE o.id_p = p.id_p AND o.money > 100;
SELECT orders.name_o,orders.money,persons.name_p FROM persons INNER JOIN orders ON persons.id_p = orders.id_p ORDER BY money;
SELECT orders.name_o,orders.money,persons.name_p FROM persons INNER JOIN orders ON persons.id_p = orders.id_p AND orders.money < 100 ORDER BY money;
SELECT p.name_p,orders.name_o FROM persons AS p LEFT JOIN orders ON p.id_p = orders.id_p;
SELECT o.name_o,p.name_p FROM orders AS o RIGHT JOIN persons AS p ON p.id_p = o.id_p;
SELECT o.name_o,p.name_p FROM orders AS o INNER JOIN persons AS p ON p.id_p = o.id_p;

SELECT o.name_o,p.name_p FROM orders AS o LEFT JOIN persons AS p ON p.id_p = o.id_p
UNION
SELECT o.name_o,p.name_p FROM orders AS o RIGHT JOIN persons AS p ON p.id_p = o.id_p;

SELECT o.name_o,p.name_p FROM orders AS o OUTER JOIN persons AS p ON p.id_p = o.id_p;

SELECT * FROM persons UNION SELECT * FROM orders;
SELECT id_p FROM persons UNION SELECT id_o FROM orders;

SELECT name_p,gender FROM persons
UNION
SELECT name_o,gender FROM orders;

SELECT name_p,gender FROM persons
UNION ALL
SELECT name_o,gender FROM orders;

SELECT name_p,gender,city FROM persons
UNION 
SELECT name_o,gender,region FROM orders;

CREATE TABLE hero(
	id_h INT NOT NULL AUTO_INCREMENT,
	name_h VARCHAR(255) NOT NULL,
	gender VARCHAR(1),
	age INT,
	region VARCHAR(20) DEFAULT '王者峡谷',
	money INT NOT NULL,
	PRIMARY KEY (id_h),
	CONSTRAINT check_name_error UNIQUE(name_h),
	CONSTRAINT check_gender_error CHECK (gender = '男' OR gender = '女'),
	CONSTRAINT check_age_error CHECK (age>5 AND age<120),
	CONSTRAINT check_money_error CHECK (money>=288 AND  money<=18888)
);

ALTER TABLE hero DROP PRIMARY KEY;/** 有问题 **/
ALTER TABLE hero DROP INDEX check_name_error;
ALTER TABLE hero ADD CONSTRAINT check_name_error UNIQUE (name_h);

ALTER TABLE hero DROP INDEX check_age_error;/** 有问题 **/
ALTER TABLE hero ADD CONSTRAINT check_age_error CHECK (age>5 AND age < 120);

INSERT INTO hero(name_h,gender,age,region,money) VALUES ('李白','男',24,'唐朝',18888);
INSERT INTO hero(name_h,region,money) VALUES ('后羿','神话',6888);
INSERT INTO hero(name_h,money) VALUES ('狂铁',13888);
INSERT INTO hero(name_h,gender,region,money) VALUES ('貂蝉','女','三国',13888);
INSERT INTO hero(name_h,gender,region,age,money) VALUES ('宫本武藏','男','日本',38,18888);
INSERT INTO hero(name_h,gender,age,region,money) VALUES ('吕布','男',28,'三国',18888);
INSERT INTO hero(name_h,gender,age,region,money) VALUES ('武则天','女',25,'唐朝',18888);
INSERT INTO hero(name_h,gender,region,money) VALUES ('嬴政','男','秦朝',788);
INSERT INTO hero(name_h,gender,region,age,money) VALUES ('橘右京','男','日本',28,12888);
INSERT INTO hero(name_h,age,money) VALUES ('鲁班',10,2888);
INSERT INTO hero(name_h,gender,money) VALUES ('上官婉儿','女',5888);
INSERT INTO hero(name_h,gender,age,money) VALUES ('马可波罗','男',30,13888);
INSERT INTO hero(name_h,gender,age,money) VALUES ('狄仁杰','男',23,5888);
INSERT INTO hero(id_h,name_h,gender,age,money) VALUES (14,'伽罗','女',25,13888);

INSERT INTO hero(name_h,gender,money) VALUES ('元歌','男',13888);
INSERT INTO hero(name_h,gender,money) VALUES ('露娜','女',13888);
INSERT INTO hero(name_h,gender,money) VALUES ('百里玄策','男',13888);

ALTER TABLE hero ADD COLUMN money INT NOT NULL;
UPDATE hero SET money = 7888 WHERE name_h = '嬴政';

SELECT * FROM hero;
DELETE FROM hero;

DROP TABLE hero;

CREATE TABLE history(
	id_t INT NOT NULL AUTO_INCREMENT,
	id_h INT NOT NULL,
	id_p INT NOT NULL,
	PRIMARY KEY (id_t),
	FOREIGN KEY (id_h) REFERENCES hero(id_h),
	FOREIGN KEY (id_p) REFERENCES persons(id_p)
);

INSERT INTO history(id_h,id_p) VALUES (8,2);
INSERT INTO history(id_h,id_p) VALUES (1,1);
INSERT INTO history(id_h,id_p) VALUES (3,2);
INSERT INTO history(id_h,id_p) VALUES (11,2);
INSERT INTO history(id_h,id_p) VALUES (2,5);
INSERT INTO history(id_h,id_p) VALUES (6,5);
INSERT INTO history(id_h,id_p) VALUES (9,3);
INSERT INTO history(id_h,id_p) VALUES (4,4);
INSERT INTO history(id_h,id_p) VALUES (6,3);
INSERT INTO history(id_h,id_p) VALUES (5,4);
INSERT INTO history(id_h,id_p) VALUES (7,1);
INSERT INTO history(id_h,id_p) VALUES (5,1);
INSERT INTO history(id_h,id_p) VALUES (12,3);
INSERT INTO history(id_h,id_p) VALUES (13,1);
INSERT INTO history(id_h,id_p) VALUES (10,10);

SELECT * FROM history;
SELECT * FROM history ORDER BY id_p;

ALTER TABLE history ADD COLUMN str VARCHAR(255);


TRUNCATE TABLE history;
DELETE FROM history;

DROP TABLE history;

SELECT h.name_h,p.name_p FROM hero AS h,persons AS p,history AS t WHERE t.id_h = h.id_h AND t.id_p = p.id_p ORDER BY h.id_h;

SELECT h.name_h,h.money,p.name_p FROM hero AS h,persons AS p 
INNER JOIN history AS t 
WHERE t.id_h = h.id_h AND t.id_p = p.id_p ORDER BY h.id_h;

SELECT h.name_h,h.money,p.name_p FROM hero AS h,persons AS p 
INNER JOIN history AS t 
WHERE t.id_h = h.id_h AND t.id_p = p.id_p ORDER BY h.money;

ALTER TABLE history ADD COLUMN test INT;
ALTER TABLE history ADD CONSTRAINT fk_test FOREIGN KEY (test) REFERENCES persons(id_p);
ALTER TABLE history DROP FOREIGN KEY fk_test;
ALTER TABLE history DROP COLUMN test;

CREATE INDEX name_h_index ON hero(name_h);

SELECT name_h_index FROM hero;

SHOW INDEX FROM hero;

CREATE VIEW view_hero AS
SELECT name_h
FROM hero
WHERE money=168;

SELECT * FROM hero ORDER BY id_h DESC;

SELECT * FROM hero WHERE age IS NULL;

ALTER TABLE hero ADD COLUMN createDate DATE;
UPDATE hero SET createDate = CURDATE() ;
ALTER TABLE hero ALTER createDate SET DEFAULT '1970-01-01';
INSERT INTO hero(name_h,gender,age,money) VALUES ('花木兰','女',28,18888); 
DELETE FROM hero WHERE name_h = '花木兰';

SELECT * FROM persons ORDER BY id_p DESC;
DROP TABLE persons;

/** 返回平均值 **/
SELECT AVG(money) FROM hero;
SELECT AVG(money) AS hero_money FROM hero;
/** 返回大于平均值的内容 **/
SELECT name_h,money FROM hero WHERE money>(SELECT AVG(money) FROM hero);

/** 返回总数目 **/
SELECT COUNT(*) FROM hero;
/** 返回总数目（NULL不计入）**/
SELECT COUNT(age) FROM hero;
SELECT COUNT(money) FROM hero;
/** 返回不同值数目 **/
SELECT COUNT(DISTINCT money) FROM hero;
SELECT DISTINCT money FROM hero ORDER BY money;

/** 返回一列中最大值 **/
SELECT MAX(money) AS max_value FROM hero;
SELECT MIN(money) FROM hero;

SELECT SUM(money) FROM hero;

SELECT region,SUM(money) FROM hero GROUP BY region;
SELECT region,SUM(money) FROM hero GROUP BY region HAVING SUM(money)<35000;
SELECT region,SUM(money) FROM hero WHERE region = '王者峡谷' OR region = '秦朝' GROUP BY region HAVING SUM(money) > 10000;

INSERT INTO persons(name_p,gender) VALUES ('ABCD','女');
SELECT UCASE(name_p) FROM persons ORDER BY id_p DESC;
SELECT LCASE(name_P) FROM persons ORDER BY id_p DESC;

/** 从name_h列中从第1个位置取1个值**/
SELECT name_h,MID(name_h,1,1) FROM hero;
/** 从name_h列中从第2个位置取2个值**/
SELECT name_h,MID(name_h,2,2) FROM hero;
SELECT name_h,MID(name_h,2,3) FROM hero;

/** 一个中文算3个字符？ **/
SELECT name_p,LENGTH(name_p) FROM persons;

SELECT * FROM hero;


CREATE TABLE testfloat(
	price FLOAT NOT NULL
);
INSERT INTO testfloat VALUES (10.6);
INSERT INTO testfloat VALUES (10.12);
INSERT INTO testfloat VALUES (10.123);
INSERT INTO testfloat VALUES (10.1234);
INSERT INTO testfloat VALUES (10.12345);
INSERT INTO testfloat VALUES (10.123456);
INSERT INTO testfloat VALUES (10.1234567);
/** 整数不会显示出小数点 .000000000 **/
SELECT money,ROUND(money,10) FROM hero;
/** 保留几位小数 **/
SELECT price,ROUND(price,0) FROM testfloat;
SELECT price,ROUND(price,1) FROM testfloat;
SELECT price,ROUND(price,2) FROM testfloat;
SELECT price,ROUND(price,3) FROM testfloat;

SELECT *,NOW() AS currentTime FROM hero;

SELECT * FROM persons;

SELECT * FROM hero;

CREATE TABLE hero_backup (SELECT * FROM hero);

SELECT * FROM hero_backup;


INSERT INTO hero_backup (SELECT * FROM hero)
INSERT INTO hero_backup (SELECT name_h,gender,money FROM hero);/** error **/

CREATE TABLE orders2(
SELECT h.name_h,h.money,p.name_p FROM persons AS p,hero AS h INNER JOIN history WHERE h.id_h = history.id_h AND p.id_p = history.id_p
)

USE arrange;

SELECT * FROM orders2;

DROP TABLE orders2;

CREATE TABLE test2(
	name_t VARCHAR(20)
);

ALTER TABLE test2 ADD COLUMN id_t INT NOT NULL;
ALTER TABLE test2 ADD PRIMARY KEY (id_t);
ALTER TABLE test2 DROP id_t;

DELETE TABLE hero DROP PRIMARY KEY;

SELECT * FROM test2;

DROP TABLE test2;

ALTER TABLE test2 MODIFY COLUMN name_t INT ;
ALTER TABLE test2 CHANGE COLUMN name_t id_t INT;

INSERT INTO test2 VALUES (10);
INSERT INTO test2 VALUES('哈哈');


SELECT * FROM hero;
SELECT * FROM hero LIMIT 5 OFFSET 10;
SELECT * FROM hero LIMIT 1;
SELECT * FROM hero ORDER BY id_h DESC LIMIT 1;

DROP TABLE test,test2;

SELECT * FROM hero_backup;

INSERT INTO hero_backup (SELECT * FROM hero);

SELECT * INTO persons_backup FROM persons WHERE id_p = 1;

SELECT money,CONCAT(name_h,'，',gender,'，',region) FROM hero;

CREATE table persons2 AS SELECT * FROM persons;

SELECT * FROM persons2;

CREATE TABLE persons3 AS SELECT * FROM persons;
INSERT INTO persons3(SELECT * FROM persons WHERE name_p='小鱼');

SELECT * FROM persons4;

CREATE TABLE persons4 LIKE persons;

INSERT INTO persons4(name_p,gender) SELECT name_p,gender FROM persons WHERE id_p = 1;
INSERT INTO persons4 * SELECT * FROM persons;

DELETE FROM persons4;

SELECT name_h,region,IFNULL(age,100) FROM hero;

SELECT name_h,region FROM hero WHERE EXISTS (SELECT * FROM hero WHERE age>20);

SELECT * FROM hero WHERE age>20;
七绝x
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL基本操作与习题
10-23
数据库的基本操作（后带习题），DDL ：数据定义语言，用于定义和管理SQL数据库中的所有对象的语言 （建设者的角度，建表，建库，建视图） DCL ：数据控制语言，用于授予或回收访问数据的某种特特权， 并控制数据库操纵事务发生的时间及效果，对数据库实行监视等 （管理员角度，DBA）
mysql 操作练习
张默的博客
07-13 356
查询表class SELECT * FROM class 查询表class里studentid为1的学生 SELECT * FROM class WHERE studentid = 1 查询表class里studentid正序排列 SELECT * FROM class ORDER BY studentid ASC 查询表class里studentid倒序排列 SELECT * FROM c...
MySQL库表操作练习
最新发布
weixin_73943382的博客
07-09 161
MySQL库表操作练习
MySQL入门操作练习（一）
qq_44013216的博客
06-10 237
数据库的操作 登录 C:\Users\AN515-44>MySQL -h 127.0.0.1 -uroot -p密码 创建数据库 mysql> create database <库名>; #创建数据库school 结果： mysql> create database school; Query OK, 1 row affected (0.03 sec) 选择数据库 mysql> use <库名>; #选择数据库school 结果： mysql
MySQL练习题
weixin_30472035的博客
03-27 246
MySQL测试题 一、表关系 请创建如下表，并创建相关约束 二、操作表 1、自行创建测试数据 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号； 3、查询平均成绩大于60分的同学的学号和平均成绩； 4、查询所有同学的学号、姓名、选课数、总成绩； 5、查询姓“李”的老师的个数； 6、查询没学过“叶平”老师课的同学的学号、姓名； 7、查询学过“001...
mysql自己怎么练习_mysql常用操作与练习题
weixin_36238982的博客
02-19 172
创建数据库：create database kaliboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;create database kaliboy_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;显示数据库:show databases;show databases l...
mysql操作练习的表数据
03-17
练习mysql操作的习题 还有答案 见链接 http://blog.csdn.net/qq_33665647/article/details/62888383
mysql练习
12-22
### MySQL练习知识点详解 #### 一、数据库与表的基本操作 ...以上是基于给定文件内容的MySQL练习中涉及的一些重要知识点，通过这些练习可以帮助学习者更好地理解并掌握MySQL的基础操作及高级功能。
MySQL10.19练习
10-19
MySQL 10.19 实践 MySQL 是一个关系型数据库管理系统，广泛...MySQL 10.19 实践中涵盖了许多重要的知识点，包括数值类型、字符串、日期、对表结构的常用操作等。这些知识点对于数据库管理员和开发人员都是非常重要的。
mysql练习10.24
10-24
本文档旨在提供 MySQL 实践指南，涵盖 MySQL 基础知识、数据类型、表结构操作、数据操作语言（DML）等方面的内容。 一、MySQL 基础知识 MySQL 是一种关系型数据库管理系统，用于存储和管理数据。它支持多种编程...
MySQL练习10.14
10-14
MySQL 练习 10.14 MySQL 是一个开源的关系型数据库管理系统，广泛应用于 web 应用程序中。下面是 MySQL 练习 10.14 中...本练习涵盖了 MySQL 的基本操作，包括数据库管理、表管理、数据约束和数据操作等方面的知识点。
Mysql命令实操练习.sql文件
11-18
Mysql基本命令实操.sql文件， bjpowernode.sql文件有三张表{ 部门表：dept；员工表:emp；工资等级表:salgrade }
MySQL数据库练习题
03-19
收集比较全面的MySQL数据库练习题，初学者可以借鉴使用。
mysql操作练习
asfdgfhjk的博客
09-09 101
1创建数据库zjgm 2具体配置 创建IDEA文件 创建com.zhongruan包 在包里创建Text文件 输入代码如下 运行结果 输入删除效果代码
MySQL表操作习题
lz你ps的博客
04-04 1164
习题一：超过5名学生的课（难度：简单） 创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。 例如,表: ±--------±-----------+ | student | class | ±--------±-----------+‘’ | A | Math | | B | English | | C ...
MySQL数据库基础操作练习（一）基本操作
weixin_49516922的博客
07-21 386
MySQL数据库基础操作练习（一）基本操作 1、基本查询操作 SELECT 10;//查询常量10 SELECT 'alex'//查询常量alex SELECT LENGTH('alex')//查询alex的长度 SELECT VERSION();//查询当前版本 SELECT last_name FROM employees;//查询employees表中的last_name字段 SELECT * FROM employees;//查询employees表中的所有字段 SELECT email '邮
MySQL数据库——多表查询练习2
Word_Smith_的博客
07-10 433
16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序。4.求工资在7500到8500元之间，年龄最大的人的姓名及部门。12.列出部门员工收入大于7000的部门编号，部门名称；14.列出每一个部门中年纪最大的员工姓名，部门名称；3.列出每个部门收入总和高于9000的部门名称。13.列出每一个部门的员工总收入及部门名称；1.找出销售部门中年纪最大的员工的姓名。5.找出销售部门收入最低的员工入职时间。17.列出部门员工数大于1个的部门名称。7.列出每个部门的平均收入及部门名称。
mysql的题_MySQL练习题
weixin_31861847的博客
01-28 140
MySQL练习题一、表关系请创建如下表，并创建相关约束二、操作表1、自行创建测试数据2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；3、查询平均成绩大于60分的同学的学号和平均成绩；4、查询所有同学的学号、姓名、选课数、总成绩；5、查询姓“李”的老师的个数；6、查询没学过“叶平”老师课的同学的学号、姓名；7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；8、查询学过“...
关于MySQL的简单操作练习
Tianay的博客
12-03 214
关于MySQL的简单操作练习1、对表的操作①创建和管理表方式一：白手起家create table +（表名）（ （变量名）+（变量类型）+（键约束） …… ）；方式二：基于现有的表//所使用的表，列的别名充当新标的别名，新表与表的数据和存储范围一致。 create table （表名） as select * （复制所有的数据） （选择需要复制的数据） 如：select employ
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值