MySql数据库学习笔记（1）

一、MySql是什么

MySql是Oracle公司旗下的一款开源的关系型数据库管理系统，分为社区版和企业版。可在MySQL官网免费下载安装。

二、基本操作

MySQL的默认端口号是3306；MySQL的超级用户名为root。

命令行中登录mysql：

mysql -uroot -p123456 //mysql -u[用户名] -p[密码]

退出

EXIT;
QUIT;
\Q;

输出版本信息

SELECT VERSION();

输出用户信息

SELECT USER();

输出当前时间

SELECT NOW();

查看当前用户的数据库信息

SHOW {DATABASES|SCHEMAS};

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

CREATE DATABASE IF NOT EXISTS t1 CHARACTER SET utf8;

//{}表示必选项，|表示选择，[]表示可选项
//[IF NOT EXISTS]可保证当db_name数据库已经存在时语句不报错；CHARACTER用于设置数据库编码方式

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

ALTER DATABASE t1 CHARACTER SET gbk;

删除数据库

DROP {DATABASE | SCHEMA}[IF NOT EXISTS] db_name;


DROP DATABASE IF EXISTS t1;

查看数据库信息

SHOW CREATE DATABASE db_name;

三、MySql语句规范

1、关键字与函数名称全部大写

2、数据库名和表名和字段名小写

3、命令语句以分号结尾

四、MySQL的数据类型

1、整型

2、浮点型

3、日期时间型

4、字符型

五、数据表操作

打开数据库

USE db_name;

显示当前用户打开的数据库

SELECT DATABASE();

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,……);

CREATE TABLE tb1(username VARCHAR(20),age TINYINT UNSIGNED,salary FLOAT(8,8) UNSIGNED);
//UNSIGNED 表示无负数；

查看数据表

SHOW TABLES [FROM db_name];
//不加[FROM db_name]时，默认显示当前数据库中的数据表。

查看数据表结构

SHOW COLUMNS FROM table_name;

添加单列

ALTER TABLE table_name ADD col_name column_definitions [FIRST | AFTER col_name];
ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

添加多列

ALTER TABLE table_name ADD [COLUMN](col_name column_definition,...)

删除列

ALTER TABLE table_name DROP [COLUMN] col_name;

ALTER TABLE users DROP age;
ALTER TABLE users DROP username,DROP id;//同时删除两列

修改列定义

ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name];

ALTER TABLE users2 MODIFY COLUMN name VARCHAR(20);

修改列名称

ALTER TABLE table_name CHANGE[COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];

修改表名称

ALTER TABLE table_name RENAME[TO|AS] new_tbl_name;
RENAME TABLE table_name TO new_table_name;

六、记录操作

插入记录

INSERT [INTO] table_name [(col_name,…)] VALUES(val,…);

INSERT INTO users (id,name,age,tel) VALUES (3,"yuan",10,"hunanchangsha");
//从users表中选择数据插入test表。
INSERT INTO test SELECT id,name FROM users WHERE age > 20;
INSERT INTO  users VALUES (4,"WEI",15,"stu","15422553366","HUANANMILUO");
//第二种方法
INSERT INTO users SET name = "hong", age = "40", tel ="14587778887";
//第三种方法，将查询结果插入到指定数据表
INSERT [INTO] table_name [(col_name,...)] SELECT ...

注意，为自动递增的字段赋值时，可以写成NULL,或 DEFAULT，使其采用自动递增的值。

更新记录

UPDATE users SET age = age +5;
UPDATE users SET age =0 WHERE id%2=0;

删除记录

DELETE FROM table_name [WHERE ...]
DELETE FROM users WHERE id = 4;

注意，当删掉某一条记录是，自动递增的字段并不会补全已经删除的字段值，而是在已有的最大值的基础上加一。

记录查找

SELECT id,name FROM users WHERE id = 2;
SELECT id AS userId ,name AS userName FROM users WHERE id = 2;

查询结果分组

使用GROUP语句分组

[GROUP BY {col_name | position} [ASC|DESC]...];

SELECT work,address FROM users GROUP BY work;
//等同于
SELECT work,address FROM users GROUP BY 1;

使用HAVING语句设置分组条件

对分组的结果进行排序

 SELECT * FROM users ORDER BY id DESC;
 SELECT * FROM users ORDER BY age ASC,name DESC;

使用LIMIT 语句限制返回的数量

SELECT * FROM users LIMIT 2;//数据代表返回记录的数量，从第一条开始。
 SELECT * FROM users LIMIT 2,2;//第一个数据代表从哪条记录开始返回（0为第一条），第二个数据代表返回多少条。

七、约束的使用

非空约束

 CREATE TABLE tb2(username VARCHAR(20) NOT NULL,age TINYINT UNSIGNED NULL,salary FLOAT(8,2) UNSIGNED NULL);
 //NOT NULL代表不允许为空值；NULL或者省略代表可以为空值。

自动编号与主键约束

自动编号属性为 AUTO_INCREMENT,必须与主键组合使用，默认情况下，起始值为1，增量为1。
主键能保证记录的唯一性，每张数据表只能存在一个主键，且主键自动为NOT NULL。
可以只使用主键约束但不使用AUTO_INCREMENT,此时，主键字段允许赋值，但记录之间的主键值不能相同。

CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL);

唯一约束

唯一约束可以保证记录的唯一性，每张数据表可以存在多个唯一约束，且唯一约束的字段可以为空值。

CREATE TABLE tb5(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL UNIQUE KEY,age TINYINT UNSIGNED);

默认约束

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

CREATE TABLE tb6(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL UNIQUE KEY,sex ENUM('1','2','3') DEFAULT '3');

外键约束

外键约束要求：

//父表
mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> pname VARCHAR(20) NOT NULL);
//子表
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES provinces(id)
    -> );

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
    -> );

添加约束

//添加主键约束
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](col_name,...)

ALTER TABLE users2 ADD PRIMARY KEY(id）;
//删除主键约束
ALTER TABLE table_name DROP PRIMARY KEY;