mysql自己怎么练习_MySQL 练习攻略

#登录和退出数据库mysql -u root -p #输入密码 -P指定端口，-D指定数据库 ，--prompt 修改提示符exit #quit，\qmysql -u root -p

SELECT VERSION();

SELECT NOW();

SHOW ENGINES;

SHOW VARIABLES LIKE 'have%'; #查看存储引擎信息SHOW VARIABLES LIKE 'storage_engine'; #查看默认存储引擎SHOW WARNINGS \G;

#数据库创建，删除，进入数据库SHOW DATABASES;

DROP DATABASE IF EXISTS `testdatabase`;

CREATE DATABASE IF NOT EXISTS `testdatabase` DEFAULT CHARACTER SET = 'utf8';

ALTER DATABASE testdatabase CHARACTER SET = 'utf8';

SHOW CREATE DATABASE `testdatabase`;

USE testdatabase; #这里不应该使用 `XX` 括住名称SELECT USER();

SELECT DATABASE();

SHOW TABLES; #注释符号help 'SELECT';

help 'INT';

help '+';

#表的创建和删除，创建表同时设置属性CREATE TABLE IF NOT EXISTS testestable(

id INT ZEROFILL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) UNIQUE, #检索效率 CHAR>VARCHAR>TEXT age TINYINT UNSIGNED NOT NULL,

brithday DATE, #1000-01-01~9999-12-31 3个字节 sex TINYINT(1) DEFAULT 0 COMMENT '0代表男,1代表女',

address TINYTEXT,

married ENUM('是','否','保密'), #最多65535个值 fav_food SET('apple','orange','fish','meat','cake') DEFAULT NULL, #最多64个成员 score DECIMAL(2,2),

CHECK(score > 0)

) ENGINE = 'InnoDB' CHARACTER SET = 'utf8';

# InnoDB(支持事务、崩溃修复、并发控制，适合频繁更新删除)# MyISAM(适合对数据的存储和读取，空间使用比较低)、# MEMORY(速度快、安全性差)SHOW CREATE TABLE testestable \G;

DESC testestable;

SHOW FULL COLUMNS FROM testestable \G;

SHOW TABLES;

DROP TABLE testestable;

#key键： #主键：唯一标识一条记录，不能有重复的，不允许为空,用来保证数据完整性CREATE TABLE IF NOT EXISTS test1(

username CHAR(20),

password CHAR(20),

PRIMARY KEY(username)

);

#外键：外键是另一表的主键, 一个表可以有多个外键, 可以是空值,用来和其他表建立联系用的 #外键连接必须连接的是主表的主键，如果连接主表主键的一部分，必须连接主键的第一个字段 #外键关联的主从表字段数据类型必须完全一样CREATE TABLE IF NOT EXISTS test2(

id INT PRIMARY KEY AUTO_INCREMENT,

username CHAR(20),

level TINYINT UNSIGNED

#CONSTRAINT uname FOREIGN KEY(username) REFERENCES test1(username));

#修改表： #修改表名ALTER TABLE test1 RENAME temp;

SHOW TABLES;

DESC temp;

ALTER TABLE temp RENAME TO test1;

#修改表属性数据类型DESC test1;

ALTER TABLE test1 MODIFY password VARCHAR(50);

DESC test1;

INSERT test1 VALUES('FreezeJ','123456');

SELECT * FROM test1;

#ALTER TABLE test1 MODIFY username int; #有值的情况下转换字段类型失败，因为字符串类型无法自动转型为整数类型SHOW WARNINGS LIMIT 1; #查看第一条错误DELETE FROM test1; #删除表中所有元素SELECT * FROM test1;

ALTER TABLE test1 MODIFY username int;

DESC test1;

ALTER TABLE test1 MODIFY username CHAR(10);

#修改表属性ALTER TABLE test1 CHANGE password passwd CHAR(20);

#增加字段DESC test1;

ALTER TABLE test1 ADD sex TINYINT(1) NOT NULL after passwd;

#删除字段ALTER TABLE test1 DROP sex; #删除字段会直接删除，不管有没有内容，也不会要求确认

#修改属性排列顺序ALTER TABLE test1 MODIFY username CHAR(10) AFTER passwd; #会修改username的类型ALTER TABLE test1 MODIFY username CHAR(10) FIRST;

#索引： #创建带索引的数据库表CREATE TABLE IF NOT EXISTS test3(

id INTEGER PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(10) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

UNIQUE INDEX index_id(id)

); #唯一索引，唯一索引指的是被索引的字段值唯一SHOW CREATE TABLE test3 \G;

#在已存在表上添加索引#create [unique|fulltext|spatial] index indexname on tablename (columnname [(length)] [ASC|DESC]); #Alter语句添加索引 #Alter table tablename add [unique|fulltext|spatial] index indexname (columename [(length)] [ASC|DESC]);CREATE INDEX index_username ON test1(username);

ALTER TABLE test1 ADD INDEX index_passwd(passwd);

#查看索引SHOW INDEX FROM test1 \G;

#删除索引DROP INDEX index_username ON test1;

ALTER TABLE test1 DROP INDEX index_passwd;

CREATE TABLE student(

id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name CHAR(20) NOT NULL,

num INTEGER UNSIGNED UNIQUE NOT NULL,

age TINYINT UNSIGNED NOT NULL,

sex TINYINT(1) DEFAULT 0,

address CHAR(50),

room CHAR(5),

class TINYINT

);

CREATE TABLE teacher(

id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name CHAR(20) NOT NULL,

sex TINYINT(1) DEFAULT 1 COMMENT '1代表女',

teach_class TINYINT DEFAULT NULL

);

#插入INSERT INTO student VALUES(1,'DRJ', 11, 21, 0, 'AAAA', '6-208', 1);

INSERT INTO student VALUES(2,'LYC', 10, 20, 0, 'AAAA', '6-210', 2);

INSERT INTO student VALUES(3,'LZY', 25, 23, 0, 'BBBB', '6-208', 3);

INSERT INTO student VALUES(4,'WJX', 5, 21, 0, 'CCCC', '6-207', 1);

INSERT INTO student VALUES(5,'YJH', 29, 21, 0, 'DDDD', '6-208', 1);

INSERT INTO student VALUES(6,'WL', 33, 21, 0, 'CCCC', '6-208', 5);

INSERT INTO teacher VALUES(1,'YYY', 1, 2);

INSERT INTO teacher VALUES(2,'QQQ', 2, 3);

INSERT INTO teacher VALUES(3,'WWW', 1, 1);

INSERT INTO teacher VALUES(4,'EEE', 2, 3);

INSERT INTO teacher VALUES(5,'RRR', 1, 4);

#更改属性ALTER TABLE student MODIFY sex TINYINT(1) COMMENT '0代表男';

#更新属性UPDATE student SET name = 'ZXB', num = 27 WHERE id = 4;

#删除属性DELETE FROM student WHERE name = 'WL';

INSERT INTO student VALUES(6,'WL', 33, 21, 0, 'CCCC', '6-208', 5);

#常用查询语句

SELECT name, address FROM student WHERE room LIKE '__208' ORDER BY age DESC;

SELECT name, room FROM student GROUP BY room;

SELECT name FROM student WHERE age = (SELECT age FROM student ORDER BY age DESC LIMIT 1);

SELECT name AS '宿舍成员' FROM student GROUP BY room;

SELECT DISTINCT age AS '年龄分布' FROM student ORDER BY age;

SELECT student.name AS '学生名字',student.class AS '班级', teacher.name AS '老师姓名'

FROM student JOIN teacher

ON student.class = teacher.teach_class;

SELECT name FROM student

UNION #UNION自动去重复，UNION ALL可以不去重复SELECT name FROM teacher;

#聚集函数#AVG() - 返回平均值#COUNT() - 返回行数#FIRST() - 返回第一个记录的值#LAST() - 返回最后一个记录的值#MAX() - 返回最大值#MIN() - 返回最小值#SUM() - 返回总和SELECT count(*) AS '总人数' FROM student;

CREATE VIEW view_room AS

SELECT name, room

FROM student

WHERE room = '6-208';

SHOW TABLES;

SELECT * FROM view_room;

INSERT INTO student VALUES (7,'WJZ', 17, 21, 0, 'GGGG', '6-208', 5);

SELECT * FROM view_room;