mysql03

SELECT * FROM class;
SELECT * FROM class WHERE cname = ‘Php’;
INSERT INTO class (cname, description) VALUES (‘a’, null),(‘b’, null),(‘A’,null),(‘B’,null);
SELECT * FROM class ORDER BY cname;
SELECT left(cname, 2), right(cname,2), mid(cname,2,1),mid(cname,2), left(description, 2) FROM class;

INSERT INTO class (cname) VALUES (‘aliyun.com/1.mp4’),(‘aliyun.com/2.mp4’);

UPDATE class SET cname = concat(‘https:’, mid(cname, 31)) WHERE id >= 12;
ALTER TABLE class MODIFY cname varchar(40) NOT NULL;
SELECT substring(cname, 2) FROM class;
SELECT char_length(cname) FROM class;
SELECT concat(‘编号:’, id) AS id , concat(‘班级:’, cname) AS cname FROM class;
SELECT if(char_length(cname)>8, concat(left(cname, 8),’…’),cname) AS cname FROM class;

SELECT * FROM class WHERE cname REGEXP ‘^.h’;
SELECT * FROM class WHERE description REGEXP ‘php|mysql’;
UPDATE class SET cname = REPLACE(cname, cname, concat(‘后盾人’,cname)) WHERE description REGEXP ‘php|mysql’;
SELECT * FROM class WHERE cname LIKE ‘_t%’;

ALTER TABLE class ADD status TINYINT;
UPDATE class SET status=21 WHERE id=14;

ALTER TABLE class ADD state TINYINT UNSIGNED;
ALTER TABLE class MODIFY status int(5) ZEROFILL;
UPDATE class SET status=123456789 WHERE id=1;
SELECT * FROM class;
UPDATE class SET status=123 WHERE id=2;

ALTER TABLE class ADD a float(10,2);
UPDATE class SET a = 33333333.33 WHERE id=10;
SELECT * FROM class;
ALTER TABLE class MODIFY a decimal(10,2);
ALTER TABLE stu MODIFY id int AUTO_INCREMENT;
– enum
ALTER TABLE stu ADD sex varchar(30);
ALTER TABLE stu MODIFY sex enum(‘man’, ‘woman’,‘secret’) DEFAULT null;
UPDATE stu SET sex = ‘secret’;
INSERT INTO stu (sname,class_id,age,sex) VALUES (‘bb’,2,22,2);

SELECT sname,if(sex=‘man’,‘男同学’,‘女同学’) AS sex2, sex FROM stu WHERE sex = ‘woman’;

CREATE TABLE IF NOT EXISTS runoob_tbl(
runoob_id INT UNSIGNED AUTO_INCREMENT,
runoob_title VARCHAR(100) NOT NULL,
runoob_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( runoob_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS artical(id int) DEFAULT charset=utf8;
ALTER TABLE artical ADD flag SET(‘推荐’,‘置顶’,‘热门’,‘图文’);
ALTER TABLE artical ADD tile varchar(20);
ALTER TABLE artical ADD publish_time time;
ALTER TABLE artical ADD status SMALLINT(5);
ALTER TABLE artical ADD click int(5) ZEROFILL;
ALTER TABLE artical MODIFY publish_time varchar(33) DEFAULT null;
ALTER TABLE artical MODIFY id int AUTO_INCREMENT;
ALTER TABLE artical MODIFY id int;
ALTER TABLE artical DROP PRIMARY KEY;
ALTER TABLE artical MODIFY id int PRIMARY KEY AUTO_INCREMENT;
INSERT INTO artical (tile, status, click,flag) VALUES (‘后盾人’,1,100,‘推荐,热门’);
SELECT * FROM artical WHERE find_in_set(‘热门’,flag);
SELECT * FROM artical WHERE flag LIKE ‘%推荐’;

推荐 0001 1
置顶 0010 2
热门 0100 4
图文 1000 8

SELECT * FROM artical WHERE flag & 5;