INSERT INTO student ( id, name, age)
VALUES ( 1 , '张三' , 18 ) ;
INSERT INTO student( name, age) VALUES ( 'jack' , 21 ) ;
INSERT INTO student VALUES ( 3 , 'zsss' , 21 , 1 , 2 ) ;
SELECT * FROM student;
SELECT name, age from student;
SELECT name, age from student where name= 'zsss' ;
SELECT name, age from student HAVING name= 'zsss' ;
select host, user , authentication_string from user ;
UPDATE student set age= 23 where name= 'zsss' ;
DELETE FROM student WHERE name= 'zsss' ;
DELETE FROM student;
truncate < dbtablename>
CREATE TABLE s select * FROM student;
CREATE TABLE s1 LIKE student;
INSERT INTO s1 SELECT * from student;
show VARIABLES LIKE "%character%" ;
utf8
CREATE DATABASE test;
use test;
create table ss1(
id int PRIMARY key auto_increment ,
name VARCHAR ( 32 ) ) ;
mysql root@localhost :test> INSERT INTO ss1 VALUES ( 1 , '张三' ) ;
( 1366 , u"Incorrect string value: '\\xE5\\xBC\\xA0\\xE4\\xB8\\x89' for column 'name' at row 1" )
ALTER TABLE ss1 charset = utf8;
alter TABLE ss1 MODIFY name VARCHAR ( 32 ) charset utf8;
INSERT INTO ss1 VALUES ( 1 , '张三' ) ;
create DATABASE demo charset = utf8;
SHOW CHARACTER SET ;
utf8 | UTF- 8 Unicode | utf8_general_ci | 3 |
int 整数类型 比较常用 4 个字节
ALTER TABLE ss1 add xxx int ( 10 ) zerofill;
INSERT INTO ss1 VALUES ( 2 , 22 , 2 ) ;
| 2 | 22 | 0000000002 |
浮点型 小数
float ( 10 , 2 ) 长度10 小数点后2 位
字符串类型
char ( 3 ) 3 个字符 固定占3 个字节 不足空格补齐 0 - 255
'a' 'a ' 3
数据 数据库 长度
varchar ( 3 ) 3 个字符 字符占的字节 额外在用1 字节记录位长 0 - 65535
'a' 'a' 2 'a' + 1
'a ' 'a' 2 'a' + 3
ALTER TABLE ss1 add gender enum ( '男' , '女' , '保密' ) ;
INSERT INTO ss1( id, name, xxx, gender) VALUES ( 3 , 'zss' , 22 , '男' ) ;
INSERT INTO ss1( id, name, xxx, gender) VALUES ( 4 , 'zsss' , 22 , '女' ) ;
INSERT INTO ss1( id, name, xxx, gender) VALUES ( 5 , 'zsss' , 22 , '22' ) ;
ALTER TABLE ss1 add hobby set ( '吃' , '喝' , '唱' , '跳' , 'rap' ) ;
INSERT INTO ss1( id, name, xxx, gender, hobby) VALUES ( 5 , 'herry' , 22 , '女' , '唱,rap' ) ;
date 1000 - 01 - 01 9999 - 12 - 31
datetime 1000 - 01 - 01 00 :00 :00 9999 - 12 - 31 23 :59 :59
now 当前的时间
INSERT INTO ss1( id, name, xxx, gender, hobby, birthday) VALUES ( 7 , 'tom' , 22 , '女' , '唱,rap' , '2020-10-2' ) ;
INSERT INTO ss1( id, name, xxx, gender, hobby, birthday) VALUES ( 8 , 'tom2' , 22 , '女' , '唱,rap' , now ( ) ) ;
7 | tom | 0000000022 | 女 | 唱, rap | 2020 - 10 - 02 00 :00 :00
8 | tom2 | 0000000022 | 女 | 唱, rap | 2020 - 10 - 08 16 :56 :37
True 1
Flase 0
也可以给数字 有一定的范围
create TABLE t(
id int PRIMARY key auto_increment ,
name varchar ( 32 ) not null COMMENT '姓名不能为空' ,
age tinyint unsigned DEFAULT 18 COMMENT '默认值18' ) ;
not null 不为空 必须要给值
default 默认值
auto_increment 主键自增
PRIMARY key 主键唯一标识 不为空 不重复 一个表只有一个
PRIMARY key ( id, sid)
unique 唯一约束
= > <
between 40 and 50
模糊比较
like
` ` 反引号 避免表名和命令重复
SELECT rand( ) ;
SQL 注释
P349