mysql基础知识1

启动服务：net start mysql


停止服务：net stop mysql


退出 


exit 


quit


\q
当前版本
SELECT VERSION();
当前时间
SELECT NOW();
当前用户
SELECT USER();
端口：3306
创建数据库：


CREATE DATABASE [IF NOT EXISTS] t1 [DEFAULT] CHARACTER SET = UTF8;


显示数据库:


SHOW DATABASES;
SHOW CREATE DATABASE t1;


修改数据库字符集:


ALTER DATABASE t1 CHARACTER SET gbk;


删除数据库: 
DROP DATABASE [IF EXISTS] t1;


打开数据库： USE 数据库名称;


整形
TINYINT 0-255 1
SMALLINT 0-65535 2
MEDIUMINT 0-16777215 3
INT  0-4294967295 4
BIGINT    0-18446744073709551615


浮点型


M是数字总位数 D是小数点后面的位数 
M>=D


FLOAT[(M,D)] 单精度浮点数 浮点精确到大约7位小数
DOUBLE[(M,D)] 双精度浮点数


日期和时间型


YEAR 1
TIME 3
DATE 3
DATETIME 8
TIMESTAMP 4
字符型


CHAR(M) M个字节0 - 255 定长
VARCHAR(M) 0-65535 变长
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM（’value1‘,’value2’,….） 枚举值 最多65535个值
SET(’value1‘,’value2’,….)集合 最多64个成员
M代表该数据类型所允许保存的字符串的最大长度，使用额外的1-2字节来存储值长度，列长度<=255使用1字节保存，其它情况使用2字节保存。例如varchar(10)会占用11字节存储空间，


varchar(500)会占用502字节存储空间。


创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(


column_name data_type,


....


);


1.查看数据表是否存在：SHOW TABLES；


2.查看数据库中的所有列表：SHOW TABLES FROM mysql；


三种方式查看表结构：


SHOW COLUMNS FROM table_name;


DESC table_name;


DESCRIBE table_name;


插入数据
INSERT [INTO] table_name(columns_name) values();


AUTO_INCREMENT
自动编号，且必须与主键组合使用
默认情况下，起始值为1，每次增量为1


数据表约束
PRIMARY KEY
主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL


UNIQUE KEY
唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（NULL)
每张数据表可以存在多个唯一约束


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




FOREIGN KEYp
保持数据一致性，完整性
实现一对一或一对多关系
外键约束的要求
1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表
2.数据表的存储引擎只能为InnoDB
3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；
而字符的长度则可以不同。
4.外键列和参照列必须创建索引。如果外键列不存在索引的话，MYSQL将自动创建索引。
父表
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL);
子表参照父表
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces(id));
show indexes from provinces \G
参照列provinces（id）指定AUTO_INCREMENT PRIMARY KEY创建索引，外键列users（pid）没有创建索引，SHOW INDEXES FROM users\G中Seq_in_index为索引列的序列号，外键列没有创建索


引，MySQL自动创建。


1. CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。


2. SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。


3. RESTRICT: 拒绝对父表的删除或者更新操作。
CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE);
4. NO ACTION: 标准SQL的关键字，在MySQL中与RESTRICT相同。


对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束
列级约束既可以在列定义时声明，也可以在列定义后声明。
表级约束只能在列定义后声明。
表级约束(非空约束和默认约束只有表级) 列级约束
非空约束 主键约束 唯一约束 默认约束 外键约束


针对字段的操作：添加/删除字段、修改列定义、修改列名称等
添加单列：ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];
添加多列：ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);
删除列：ALTER TABLE tb1_name DROP[COLUMN] col_name,DROP [COLUMN] columnName;


修改列定义：ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name  ]


修改列名称：ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name


针对约束的操作：添加/删除各种约束
// 添加主键约束
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...);
// 添加唯一约束
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...);
// 添加外键约束
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition
// 添加、删除默认约束
ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}


删除主键约束：ALTER TABLE tbl_name DROP PRIMARY KEY


删除唯一约束：ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;


删除外键约束：ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;


数据表更名：
方法1：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法2：RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name]...
不要随意更改数据列 数据表的名字




mysql下抛出错误：


error:no query specified


出现此错误是sql不合法原因：


如：select * from category \G;


或者  select * from abc;;


 解决：


G后面不需要再加分号;


分号后面也无需再加分号


CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,password VARCHAR(20) NOT NULL,age TINYINT UNSIGNED NOT NULL DEFAULT 


10,sex BOOLEAN);


INSERT users VALUES(NULL,'TOM',123,15,1);
INSERT users VALUES(DEFAULT,'JOHN',456,25,1);
INSERT users VALUES(DEFAULT,'ANNY',789,5*5+2,1);
INSERT users VALUES(DEFAULT,'ANNY',789,DEFAULT,1);
INSERT users VALUES(DEFAULT,'ANNY1','789',DEFAULT,1),(NULL,'ANNY2',md5('1'),7+8,1);


INSERT INTO users SET username='aaa',password='123456';


INSERT [ INTO ] table_name SET column_name={expr|DEFAULT},...... 此方法可以使用子查询
INSERT [ INTO ] table_name [column] SELECT 
单表更新
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}]... [WHERE where_condition]
UPDATE users SET age = age + 5 WHERE id%2 = 0;
UPDATE users SET age = age + 5, sex = 2 WHERE username='TOM';
### 删除记录（单表删除）


如果省略WHERE，则该表所有记录都将被删除


DELETE FROM table_name [WHERE where_condition]


### 　删除表：


DROP TABLE table_name;


删除没有被关联的普通表：直接上面的SQL语句就行了


删除被其他表关联的父表：


- 方法一：先删除子表，再删除父表


- 方法二：先删除父表的外键约束，再删除父表


SELECT select_expr[, select_expr ...]


[


  FROM table_references


  [WHERE where_condition]


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


  [HAVING where_condition]


  [ORDER BY {col_name | expr | position} [ASC | DESC], ...]


  [LIMIT {[offset, ]row_count | row_count OFFSET offset}]


]


每一个表达式表示想要的一列，必须有至少一个。


 多个列之间以英文逗号分隔。


 星号(*)表示所有列。tbl_name.*可以表示命名表的所有列。


 查询表达式可以使用[ AS ] alias_name为其赋予别名。


 别名可用于GROUP BY,ORDER BY或HAVING子句。




 SELECT sex FROM users GROUP BY 1;//sex在第1个


SELECT id,sex FROM users GROUP BY 2;//sex在第2个




所以最好使用:


SELECT id,username,age,sex FROM users GROUP BY sex;


聚合函数 max min sum count


SELECT id,username,age,sex FROM users GROUP BY sex HAVING COUNT(id) > 2;


0            这里sex=0         有7条记录


null        这里sex=null    有1条记录


然后后面还有个count(id)>2条件限制，


0这个记录里，id的计数有7个，大于2，所以显示为sex的内容。


null这个记录里，id计数只有1个，小于2，所以不显示。
SELECT id,username,age,sex FROM users ORDER BY id DESC;降序
SELECT id,username,age,sex FROM users ORDER BY id ASC;升序


SELECT * FROM tb_name where where_condition limit((p-1)*pageSize ,pageSize)) 从0开始记录
CREATE TABLE test(id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20));
INSERT test(username) SELECT username FROM users WHERE age >= 30;