Mysql基础(二)—– 表结构和数据的操作

756-周同学

发表文章数:47

热门标签

,
首页 » MySQL » 正文

一. 数据表完整性约束条件

CREATE TABLE IF NOT EXISTS tbl_name(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL ] [DEFAULT] [PRIMARY KEY |UNIQUE] [AUTO_INCREMEMT] 顺序
)ENGINE=INNOOB CHARSET=UTF8 AUTO_INCREMENT=100;

1. Primary Key 主键

唯一标识符,一般加到无意义字段,定义为主键的字段不能重复,自动停止非空

  1. 单个主键 (主键primary可省略)
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY ,
username VARCHAR (20)
);
mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE user1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
  `id` int NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
INSERT user1 VALUES (1,'king');
INSERT user1 VALUES (13,'queen');
-- 若下一个id是13,那么会报错
mysql> select * from user1 where id=1;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
+----+----------+
1 row in set (0.00 sec)
  1. 复合主键
CREATE TABLE IF NOT EXISTS user2(
id INT,
username VARCHAR (20),
card CHAR (18),
PRIMARY KEY (id,card)
);
INSERT user2 VALUES (1,'king','111');
INSERT user2 VALUES (1,'queen','112');
-- 下一条记录不能用1和112,1和111
mysql> select * from user2;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | king     | 111  |
|  1 | queen    | 112  |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| card     | char(18)    | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2. AUTO_INCREMENT 自增长

一个表中只有一个自增长,而且必须和主键一起用

CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR (20)
);
INSERT user5 VALUES (1,'King');
INSERT user5(username) VALUES ('queen');
INSERT user5 VALUES (111,'King1');
INSERT user5(username) VALUES ('queen1');
INSERT user5 VALUES (NULL,'AAAA');
INSERT user5 VALUES (DEFAULT ,'AAAA');
mysql> select * from user5;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | King     |
|   2 | queen    |
| 111 | King1    |
| 112 | queen1   |
| 113 | AAAA     |
| 114 | AAAA     |
+-----+----------+
6 rows in set (0.00 sec)
CREATE TABLE IF NOT EXISTS user6(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR (20)
)AUTO_INCREMENT=100;

INSERT user6(username) VALUES ('queen');
--修改自增长的值
ALTER TABLE user6 AUTO_INCREMENT=500;
INSERT user6(username) VALUES ('queen');
mysql> select * from user6;
+-----+----------+
| id  | username |
+-----+----------+
| 100 | queen    |
| 500 | queen    |
+-----+----------+
2 rows in set (0.00 sec)

3. NOT NULL非空

CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL ,
password CHAR (32) NOT NULL ,
age TINYINT UNSIGNED
);

INSERT user7(username,password) VALUES ('KING','KING');
INSERT user7(username,password,age) VALUES ('KING1','KING1',12);
mysql> select * from user7;
+----+----------+----------+------+
| id | username | password | age  |
+----+----------+----------+------+
|  1 | KING     | KING     | NULL |
|  2 | KING1    | KING1    |   12 |
+----+----------+----------+------+
2 rows in set (0.00 sec)

4.DEFAULT 默认值

CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL ,
password CHAR (32) NOT NULL ,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR (50) NOT NULL DEFAULT 'beijing',
sex ENUM('male','female','secret') NOT NULL DEFAULT 'male'
);

INSERT user8(username,password) VALUES ('KING','KING');
INSERT user8 VALUES (2,'Queen','Queen',29,'Shanghai','secret');
INSERT user8 VALUES (3,'Queen','Queen',DEFAULT ,DEFAULT ,'secret');
mysql> select * from user8;
+----+----------+----------+------+----------+--------+
| id | username | password | age  | addr     | sex    |
+----+----------+----------+------+----------+--------+
|  1 | KING     | KING     |   18 | beijing  | male   |
|  2 | Queen    | Queen    |   29 | Shanghai | secret |
|  3 | Queen    | Queen    |   18 | beijing  | secret |
+----+----------+----------+------+----------+--------+
3 rows in set (0.00 sec)

5. UNIQUE KEY 唯一

NULL可以重复,其他不可以

CREATE TABLE IF NOT EXISTS user9(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL UNIQUE ,
card CHAR (18) UNIQUE
);

INSERT user9(username) VALUES ('A');
INSERT user9(username) VALUES ('A1');
INSERT user9(username,card) VALUES ('B','111');
INSERT user9(username,card) VALUES ('B1',NULL );
INSERT user9(username,card) VALUES ('B2',NULL);
mysql> select * from user9;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | A        | NULL |
|  2 | A1       | NULL |
|  3 | B        | 111  |
|  4 | B1       | NULL |
|  5 | B2       | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

二. 表结构相关内容

1. 修改表名

--创建用户表
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL UNIQUE ,
password CHAR (32) NOT NULL,
email VARCHAR (50) NOT NULL DEFAULT '322345665@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('male','female','secret') DEFAULT 'secret',
addr VARCHAR (200) NOT NULL DEFAULT 'beijing',
salary FLOAT (6,2),
regTime INT UNSIGNED,
face CHAR (100) NOT NULL DEFAULT 'default.jpg'
);
--将user10重命名user11
ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user10;
ALTER TABLE user10 RENAME user11;

RENAME TABLE user11 TO user10;
--这里不可以省略TO

2.添加和删除字段

  1. 添加字段
--添加card字段
ALTER TABLE user10 ADD card CHAR (18);
ALTER TABLE user10 ADD test1 VARCHAR (100) NOT NULL UNIQUE ;
ALTER TABLE user10 ADD test2 VARCHAR (20) NOT NULL FIRST ;
--指定字段位置
ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;

--选中一次表,完成多个操作
ALTER TABLE user10
ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
ADD test5 FLOAT (6,2) FIRST ,
ADD test6 SET ('A','B','C');
  1. 删除字段
ALTER TABLE user10 DROP test6;
ALTER TABLE user10 DROP test1;

--一次删除多个字段
ALTER TABLE user10
DROP test2,
DROP test3,
DROP test4,
DROP test5;
  1. 添加一个字段并删除一个字段
ALTER TABLE user10
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP addr;

3.修改字段

  1. Modify
--修改字段
ALTER TABLE user10 MODIFY email VARCHAR (200);
ALTER TABLE user10 MODIFY email VARCHAR (50) NOT NULL DEFAULT 'eyeyienc.com';

--修改字段位置
ALTER TABLE user10 MODIFY card CHAR (18) AFTER test;

--修改字段以及位置
ALTER TABLE user10 MODIFY test CHAR (32) NOT NULL DEFAULT '123' FIRST ;
  1. Change
--用change修改字段名字和属性
ALTER TABLE user10 CHANGE test test1 CHAR (32) NOT NULL DEFAULT '123';
ALTER TABLE user10 CHANGE test1 test VARCHAR (200) NOT NULL AFTER username;
ALTER TABLE user10 CHANGE test test INT;

4. 添加删除默认值

  1. 添加默认值
CREATE TABLE IF NOT EXISTS user11(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL UNIQUE ,
age TINYINT UNSIGNED
);

ALTER TABLE user11 ALTER age SET DEFAULT 18;
ALTER TABLE user11 ADD email VARCHAR (50);
ALTER TABLE user11 ALTER email SET DEFAULT '789hhj.com';
  1. 删除默认值
ALTER TABLE user11 ALTER age DROP DEFAULT ;
ALTER TABLE user11 ALTER email DROP DEFAULT ;

5. 添加删除主键

  1. 添加主键
CREATE TABLE IF NOT EXISTS user12(
id INT
);

ALTER TABLE user12 ADD PRIMARY KEY(id);

CREATE TABLE IF NOT EXISTS test13(
id INT,
card CHAR (18),
username VARCHAR (20) NOT NULL
);

ALTER TABLE test13 ADD PRIMARY KEY (id,card);

  1. 删除主键
ALTER TABLE user12 DROP PRIMARY KEY ;
ALTER TABLE test13 DROP PRIMARY KEY ;

ALTER TABLE user12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);

CREATE TABLE IF NOT EXISTS test14(
id INT UNSIGNED KEY AUTO_INCREMENT
);

--先去除auto_increment
ALTER TABLE test14 MODIFY id INT UNSIGNED;
ALTER TABLE test14 DROP PRIMARY KEY ;

6. 添加删除唯一

  1. 添加唯一索引
CREATE TABLE IF NOT EXISTS test12(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL ,
card CHAR (18) NOT NULL,
test VARCHAR (20) NOT NULL,
test1 CHAR (32) NOT NULL
);
ALTER TABLE test12 ADD UNIQUE (username);
ALTER TABLE test12 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);
ALTER TABLE test12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);
  1. 删除唯一索引
ALTER TABLE test12 DROP INDEX username;
ALTER TABLE test12 DROP KEY uni_card;
ALTER TABLE test12 DROP KEY mulUni_test_test1;
  1. 删除表的存储引擎
ALTER TABLE test12 ENGINE=MyISAM;
  1. 修改自增长值
ALTER TABLE test12 AUTO_INCREMENT=100;

7. 删除数据表

DROP TABLE user12;
DROP TABLE IF EXISTS user12;
DROP TABLE IF EXISTS user11,user10,user9;
DROP TABLE IF EXISTS user8,user7,user6,user123;

打开数据库

mysql -root -p -D maizi
mysql -uroot -p maizi --prompt=/d~/D~/u~/h

三. 数据的操作

1. DML

  1. 插入数据
    (1) 不指定具体的字段名
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
password CHAR (32) NOT NULL ,
email VARCHAR (50) NOT NULL DEFAULT '2222.com',
age TINYINT UNSIGNED DEFAULT 18
);
INSERT INTO user VALUES(1,'King','king','king.com',20);
INSERT user VALUE(2,'queen','queen','queen.com',30);

(2) 列出指定字段

INSERT user(username,password) VALUES('A','AAA');
INSERT user(username,password) VALUES('B','BBB');
INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC.com',DEFAULT );

(3) 同时插入多条记录

INSERT user VALUES (9,'D','DDD','DDD.com',35),
(8,'E','EEE','EEE.com',9),
(18,'F','FFF','FFF.com',12);

(4) 通过SET形式插入记录

INSERT INTO user SET id=98,username='test',password='this is a test',email='123.com',age=48;
INSERT user SET username='maizi',password='maizi';

(5) 将查询记录结果插入到表中

--插入其他表的记录
INSERT testUser SELECT id,username FROM user;
--字段数目不匹配
INSERT testUser SELECT * FROM user;
--这种形式可以
INSERT testUser(username) SELECT username FROM user;
  1. 更新数据
UPDATE user SET age=5;
UPDATE user SET age=20,email='test.com';
--将第一条记录的密码邮箱年龄改变
UPDATE user SET password='king123',email='123.com',age=99
WHERE id=1;
UPDATE user SET age=age-5 WHERE id>=3;
UPDATE user SET age=DEFAULT WHERE username='A';
  1. 删除数据
DELETE FROM testUser;
DELETE FROM user WHERE id=1;
--delete删除记录时没有重置自增长值
--truncate可以彻底清空数据表,重置自增长值
TRUNCATE TABLE user;

2. DQL

  1. 查询表达式
--查询
SELECT * FROM cms_admin;
--查询想要的内容
SELECT id,username FROM cms_admin;
SELECT username,id,role FROM cms_admin;

--表来自于哪个数据库db_name.table_name
SELECT id,username,role FROM cms.cms_admin;
SELECT id,username FROM maizi.user1;

--字段来自于哪张表
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

--给表名起别名
SELECT id,username FROM cms_admin AS a;
SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

--自字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;
SELECT a.id AS i,a.username AS u,a.email AS e,a.role AS r FROM cms_admin AS a;
SELECT 1,2,3,4,5,id,username FROM cms_user;
  1. 待查询的条件
-- Where条件
SELECT id,username,emailFROM cms_user WHERE id=1;
SELECT id,username,emaail FROM cms_user WHERE username='king';
--不等于
SELECT * FROM cms_user WHERE id!=1;
SELECT * FROM cms_user WHERE id<>1;
--查询值为NULL
SELECT * FROM cms_user WHERE age<=>NULL;
SELECT * FROM cms_user WHERE age IS NULL;
SELECT * FROM cms_user WHERE age IS NOT NULL;
SELECT * FROM cms_user WHERE age<=>18;
  1. 范围查询
--查询编号在3-10之间的用户
SELECT * FROM WHERE id BETWEEN 3 AND 10;
SELECT * FROM WHERE id NOT BETWEEN 3 AND 10;
--查询编号为1,3,5,7,9的用户
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9);
--查询proID为1和3的用户
SELECT * FROM cms_user WHERE proId IN(1,3);
--查询用户名为king,queen(忽略大小写)
SELECT * FROM cms_user WHERE username IN('king','queen');
SELECT * FROM cms_user WHERE username IN('KinG','queen');
SELECT * FROM cms_user WHERE username NOT IN('KinG','queen');
  1. 模糊查询
--%:代表0个一个或多个任意字符
-- _:代表一个任意字符
--查询姓zhang的用户
SELECT * FROM cms_user WHERE username LIKE '%zhang%';
SELECT * FROM cms_user WHERE username LIKE 'zhang%';
--查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';
--没有写条件
SELECT * FROM cms_user WHERE username LIKE '%';
--查询用户名为3位的用户
SELECT * FROM cms_user WHERE username LIKE'___';
--用户名—_i%,第二位是i,后面不知道
SELECT * FROM cms_user WHERE username LIKE'_i%';
SELECT * FROM cms_user WHERE username NOT LIKE'_i%';
  1. 分组查询
--按照用户所属省份分组,只会显示组中第一条记录
SELECT * FROM cms_user GROUP BY proId;
--向用户表中添加性别字段
ALTER TABLE cms_user ADD sex SNUM('male','female','secret');
UPDATE cms_user SET sex='male' WHERE id IN(1,3,5,7,9);
UPDATE cms_user SET sex='female' WHERE id IN(2,4,6,8,10);
UPDATE cms_user SET sex='secret' WHERE id IN(11,12);
--按照性别分组
SELECT * FROM cms_user GROUP BY sex;
--按照位置分组
SELECT * FROM cms_user GROUP BY 7;
--按照多个字段分组,先按性别分,再根据每个性别按照proId分
SELECT * FROM cms_user GROUP BY sex,proId;
--查询编号>=5的用户按照sex分组
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;
  1. 分组查询配合聚合函数
    (1)配合GROUP_CONCAT()得到分组详情
--查询id,sex,用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
--查询proId,性别详情,注册时间详情,用户名详情按照proId
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId;

(2)配合聚合函数

--查询编号,sex,用户名详情以及组中总人数按照性别分组
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
--统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;
SELECT COUNT(id) AS totalUsers FROM cms_user;
--COUNT(字段)不统计NULL值
SELECT COUNT(age) AS totalUsers FROM cms_user;
--查询编号,性别,用户名详情,组中总人数,组中最大,最小,平均年龄以及年龄总和,按照sex分组
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY age;

(3)配合WITH ROLLUP记录上面所有记录的总和

--WITH ROLLUP
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY age WITH ROLLUP;
  1. having语句对分组结果进行二次筛选
--查询sex,用户名详情,组中总人数,最大年龄,年龄总和
--查询组中人数大于2,并且最大年龄大于60
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT (*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex
HAVING COUNT (*) >2 AND MAX(age)>60;
--基于以上查询,查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT (*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id>=2
GROUP BY sex
HAVING COUNT (*) >2 AND MAX(age)>60;
标签:

未经允许不得转载:作者:756-周同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《Mysql基础(二)—– 表结构和数据的操作》 发布于2020-05-15

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录