慕课网

与MySQL的零距离接触

最热最新

g2om

show columns from users2; select id,username from users2; select id as userid,username as uname from users2; select * from users2; select gender from users2 group by gender; select id,username,password,age from users2 group by gender having age<=25; select * from users2; select * from users2 limit 2; select * from users2 order by id desc limit 2,2; create table test1 ( id tinyint unsigned primary key auto_increment, username varchar(20) ); select 8 from test1; insert test1(username) select username from users2 where age>=28;

查看全部

1 采集收起来源：回顾和概述
2018-03-22
g2om

update users2 set age=age+5; update users2 set age=age-id,gender=0 where gender is null; /* delete from tbl_name [where where_condition] */ delete from users2 where id=3; insert into users2 values (null,'Alice','123',22,0); /* 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 expr}] ] */ select version(); select now(); select 3+5; select pow(10,2); /* 每个表达式表示要查找的一列，至少存在一个；多个列之间用半角逗号分隔；星号" * "表示列的筛选，代表所有列；查询表达式可以使用[AS] alias_name 为其赋予别名；别名可用于GROUP BY，ORDER BY 或HAVING子句。 */

查看全部

0 采集收起来源：回顾和概述
2018-03-22
g2om

/* insert [into] tbl_name [(col_name1,...)] {value|values} ({expr|default},...),(...),... */ create table users2( id smallint unsigned primary key auto_increment, username varchar(20) not null, password varchar(32) not null, age tinyint unsigned not null default 10, gender boolean); show tables; show columns from users2; insert into users2 values (null,'Tom','123',25,1),(default,'John','456',22,1); select * from users2; insert into users2 values (null,'Tom','123',3*7+3,1),(null,'John',md5('123'),22,1); /* insert [into] tbl_name set col_name={expr|default},... #此方法相对前一种方法可使用子查询subquery，但无法一次插入多条记录 */ insert users2 set username='Ben',password='456'; /* insert [into] tbl_name [(col_name,...)] select ...; #将查询结果插入到指定数据表 */ /* #单表更新 update [low_priority] [ignore] table_reference set col_name1={expr1|default1} [,col_name2={expr2|default2}]... [where where_condition] */

查看全部

0 采集收起来源：回顾和概述
2017-11-21
g2om

SHOW CREATE TABLE users1\G; ALTER TABLE users1 MODIFY id SMALLINT unsigned FIRST; ALTER TABLE users1 MODIFY pid TINYINT unsigned AFTER age; SHOW COLUMNS FROM users1; /* ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name new_col_definition [FIRST|AFTER col_name]; */ ALTER TABLE users1 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

查看全部

0 采集收起来源：MySQL 表级约束与列级约束
2017-11-21
g2om

HOW CREATE TABLE users1; /* ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name](index_col_name,...) REFERENCES_difinition; */ ALTER TABLE users1 ADD CONSTRAINT fk_prov_id FOREIGN KEY (pid) REFERENCES provinces(id); /* ALTER TABLE tbl_name ALTER [OOLUMN] col_name {SET DEFAULT literal |DROP DEFAULT }; */ ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL; ALTER TABLE users1 ALTER age SET DEFAULT 18; ALTER TABLE users1 ALTER age DROP DEFAULT; /* ALTER TABLE tbl_name DROP PRIMARY KEY; */ ALTER TABLE users1 DROP PRIMARY KEY; /* ALTER TABLE tbl_name DROP {INDEX|KEY} index_name; */ SHOW INDEXES FROM users1; ALTER TABLE users1 DROP KEY uiq_user1_name; SHOW INDEXES FROM users1; SHOW COLUMNS FROM users1; /* ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; */ SHOW CREATE TABLE users1\G; ALTER TABLE users1 DROP FOREIGN KEY fk_prov_id; SHOW CREATE TABLE users1\G; SHOW INDEXES FROM users1\G; ALTER TABLE users1 DROP INDEX fk_prov_id;

查看全部

0 采集收起来源：MySQL 表级约束与列级约束
2017-11-21
g2om

show columns from users; ALTER TABLE users ADD truename VARCHAR(20) NOT NULL FIRST; /* ALTER TABLE tbl_name ADD [COLUMN](col_name1 col_definition1,col_name2 col_definition2,...); */ /* ALTER TABLE tbl_name DROP [COLUMN] col_name; */ ALTER TABLE users DROP truename; ALTER TABLE users DROP age; ALTER TABLE users DROP passwd,ADD gender BOOLEAN; ALTER TABLE users MODIFY gender boolean AFTER username; /* ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...); */ CREATE TABLE users1 ( username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED ); SHOW CREATE TABLE users1; ALTER TABLE users1 ADD id SMALLINT unsigned; ALTER TABLE users1 ADD CONSTRAINT PK_user1_id PRIMARY KEY (id); /* ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name,...); */ ALTER TABLE users1 ADD CONSTRAINT uiq_user1_name UNIQUE KEY (username); SHOW CREATE TABLE users1;

查看全部

0 采集收起来源：MySQL 表级约束与列级约束
2017-11-21
g2om

/* CASCADE SET NULL RESTRICT NO ACTION */ DROP TABLE users; CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE ); INSERT INTO provinces (name,abbr) VALUES ('taiwan','tai');--32 INSERT INTO provinces (name,abbr) VALUES ('tritem','san');--33 INSERT INTO provinces (name,abbr) VALUES ('ampire','asn');--34 INSERT users (username,pid) VALUES ('Tom',32); INSERT users (username,pid) VALUES ('Jhon',33); INSERT users (username,pid) VALUES ('Cathy',34); INSERT users (username,pid) VALUES ('Alice',35); DELETE FROM provinces WHERE id=34; SELECT * FROM users; /* ALTER TABLE tbl_name ADD [COLUMN] col_name col_definition [FIRST|AFTER col_name]; */ show columns from users; ALTER TABLE users ADD age TINYINT unsigned NOT NULL DEFAULT 10; ALTER TABLE users ADD passwd VARCHAR(32) NOT NULL AFTER username;

查看全部

0 采集收起来源：MySQL 外键约束的参照操作
2017-11-21
g2om

INSERT provinces(name,abbr) VALUES ('anhui','wan'),('beijing','jing'),('chongqing','yu'),('fujian','min'), ('gansu','gan'),('guangdong','yue'),('guangxi','gui'),('guizhou','qian'), ('hainan','qiong'),('hebei','ji'),('henan','yu'),('heilongjiang','hei'), ('hubei','e'),('hunan','xiang'),('jilin','ji'),('jiangsu','su'),('jiangxi','gan'), ('liaoning','liao'),('neimenggu','meng'),('ningxia','ning'),('qinghai','qing'), ('shandong','lu'),('shanxi','jin'),('shanxi','shan'),('shanghai','hu'),('sichuan','chuan'), ('tianjin','jin'),('xizang','zang'),('weiwuer','xin'),('yunnan','dian'),('zhejiang','zhe');

查看全部

0 采集收起来源：MySQL 外键约束的参照操作
2017-11-21
g2om

/* FOREIGN KEY */ CREATE TABLE provinces ( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL ); SHOW TABLES; SHOW INDEXES FROM provinces\G; CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces (id) ); ALTER TABLE provinces ADD COLUMN abbr VARCHAR(10) DEFAULT 'null' AFTER name; --ALTER TABLE provinces modify COLUMN name VARCHAR(30) NOT NULL UNIQUE AFTER id;

查看全部

0 采集收起来源：MySQL 外键约束的要求解析
2017-11-21
g2om

/* ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name; RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2,...]; */

查看全部

0 采集收起来源：总结
2017-11-21
g2om

/* PRIMARY KEY AUTO_INCREMENT :BE USED WITH PRIMARY KEY */ CREATE TABLE tb3 ( id SMALLINT UNSIGNED AUTO_INCREMENT, username VARCHAR(30) NOT NULL , PRIMARY KEY(id) ); INSERT tb3(username) VALUES ('Tom'),('John'),('Rose'),('Dimitar'); SELECT * FROM tb3; CREATE TABLE tb4 ( id SMALLINT UNSIGNED PRIMARY KEY, username VARCHAR(20) NOT NULL ); SHOW COLUMNS FROM tb4; INSERT tb4 VALUES (4,'Tom'),(22,'John'); /* UNIQUE */ CREATE TABLE tb5( id SMALLINT UNSIGNED PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE, age TINYINT UNSIGNED ); SHOW COLUMNS FROM tb5; ALTER TABLE tb5 MODIFY COLUMN id SMALLINT UNSIGNED AUTO_INCREMENT; INSERT tb5 (username,age) VALUES ('Tom',22); SELECT * FROM tb5; /* DEFAULT */ CREATE TABLE tb6( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE KEY, gender ENUM('0','1','2') DEFAULT '2' ); SHOW COLUMNS FROM tb6; INSERT tb6 (username) VALUES ('Tom'); SELECT * FROM tb6;

查看全部

0 采集收起来源：MySQL创建数据表
2017-11-21
g2om

/* USE db_name; */ SHOW DATABASES; USE test; SELECT DATABASE(); /* CREATE TABLE [IF NOT EXISTS] table_name( column1 col_def1,colum2 col_def2, ......); */ CREATE TABLE IF NOT EXISTS tb1( username VARCHAR(20), age TINYINT UNSIGNED, salary FLOAT(8,2) UNSIGNED ); /* SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHERE expr] */ SHOW TABLES; /* SHOW COLUMNS FROM tbl_name; */ SHOW COLUMNS FROM tb1; /* INSERT [INTO] tbl_name [col_name1,col_name2,...] VALUES (value1,value2,..),[(value1,value2...),...]; */ INSERT tb1 VALUES ('Tom',25,7863.25); INSERT tb1(username,salary) VALUES ('John',4500.69); /* SELECT expr,... FROM tbl_name; /*SIMPLE VERSION*/ */ SELECT * FROM tb1; /* NULL & NOT NULL */ CREATE TABLE tb2 ( username VARCHAR(20) NOT NULL, age TINYINT UNSIGNED NULL ); SHOW CREATE TABLE tb2\G; INSERT tb2 VALUES ('Tom',NULL); SELECT * FROM tb2;

查看全部

0 采集收起来源：内容回顾
2018-03-22
g2om

/* CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name; */ CREATE DATABASE t1; /* SHOW {DATABASES|SCHEMAS} [LIKE 'pattern'|WHERE expr]; */ SHOW DATABASES; /* SHOW CREATE DATABASE db_name; */ SHOW CREATE DATABASE t1; CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk; /* ALTER {DATABASE|SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name; */ ALTER DATABASE t2 CHARACTER SET utf8; SHOW CREATE DATABASE t2; /* DROP {DATABASE|SCHEMA} db_name; */ DROP DATABASE IF EXISTS t1; DROP DATABASE IF EXISTS t2; SHOW DATABASES;

查看全部

0 采集收起来源：操作数据库
2017-11-21
g2om

--Typical installation --D:\Program Files (x86)\MySQL\MySQL Server 5.5\bin --MySQLInstanceConfig.exe --standard configuration --install windows service & Include * windows PATH --D:\Program Files (x86)\MySQL\MySQL Server 5.5\myini --[mysql]'default-character-set=utf8' --[mysqld]'character-set-server=utf8' --cmd: net start/stop service_name --cmd:mysql [options] -- option: -u{login user} -p{password} -- -P{port} -D{database_name} -- -h{host_name} -V{version & quit} --exit;quit;\q; /* prompt [options] option: \u{:user} \d{:database} \h{host_name} \D{datetime} */ SELECT VERSION(); SELECT NOW(); SELECT USER();

查看全部

0 采集收起来源：MySQL概述
2018-03-22
幕布斯9048109

刚才说到的整数类型有，tiny,small,medium,int ,bingint,还有一个就是float函数

查看全部

0 采集收起来源：MySQL数据类型之日期时间型
2017-11-20

首页上一页 528 529 530 531 532 533 534 下一页尾页

0/150

提交

取消

该课程已下架

课程须知: 要想学习本课程，你只需要知道数据库是个什么东东就足够了，是不是很easy？！

老师告诉你能学到什么？: 1、MySQL安装与配置 2、数据类型 3、流程控制与运算符 4、DDL、DCL、DQL、DML 5、常用函数 6、表类型(存储引擎) 7、图形化工具