- > mysql --help
- mysql> help
- >mysql -h 192.168.1.114 -P 3306 -u root -p
- >mysqldump -h192.168.1.114 -P3306 -uroot -pRoot_3306 piwigo test lab > u.sql
- mysql> SELECT DISTINCT id FROM lab;
- mysql> LOAD DATA LOCAL INFILE 'lab.txt' INTO TABLE lab LINES TERMINATED BY '\r\n';
- mysql> SET sql_mode = '';
- mysql> SELECT id, COUNT(*) FROM lab;
- mysql> alter table lab add name varchar(64);
- mysql> alter table lab drop city;
- mysql> SELECT * FROM lab WHERE id NOT IN(1,6);
- mysql> SELECT * FROM lab WHERE id NOT BETWEEN 5 AND 10;
- mysql> select test.id,lab.id,lab.name from lab left join test on test.id=lab.id order by lab.id;
- right,left,inner,full join(compare)
- mysql> SELECT id FROM test UNION ALL SELECT id FROM lab;
- mysql> SELECT id FROM test UNION SELECT id FROM lab;(seem to distinct different result.)
- mysql> ALTER TABLE test ADD UNIQUE(age);
- mysql> CREATE INDEX id_index ON test(age DESC);
- mysql> ALTER TABLE test DROP INDEX id_index;
- mysql> ALTER TABLE lab ADD FOREIGN KEY(major_id) REFERENCES test(id);//foreign key col value must could found in PRIMARY KEY col value;
- ALTER TABLE lab DROP FOREIGN KEY major_id;
-
mysql> ALTER TABLE test DROP PRIMARY KEY; Query OK, 7 rows affected (0.07 sec) Records: 7 Duplicates: 0 Warnings: 0 说明了索引确实对应字段每个数据上都附加的。
- mysql> ALTER TABLE test ADD PRIMARY KEY(id);
- mysql> ALTER TABLE lab ALTER major_id SET DEFAULT 18;>
- mysql> alter table lab alter name drop default;
- mysql> CREATE VIEW u AS SELECT * FROM test;//INDEX will lose.
- mysql> DROP VIEW u;
- mysql> SELECT * FROM test WHERE major IS NOT NULL;
- mysql> ALTER TABLE test MODIFY major CHAR(50);
- mysql> SELECT id,SUM(id) AS su FROM test GROUP BY major HAVING su < 50;
- select id,COUNT(*) from lab where a=1 and id =3 group by name,age order by age desc limit 3\c
- mysql> SELECT id,MONTH(NOW()) AS age FROM lab;
- mysql> select * from lab where id REGEXP '..';
- mysql> QUIT
- mysql> SELECT VERSION(),CURRENT_DATE;
- mysql> SELECT SIN(PI()/4), (4+1)*5;
- SELECT NOW();
- SELECT USER()
- 存储过程 中的 RDBMS - 关系数据库管理系统(Relational Database Management System) 关系数据库管理系统 (RDBMS) 也是一种数据库管理系统,其数据库是根据数据间的关系来组织和访问数据的。
-
mysql sql problem opt?
- mysql> SELECT * FROM lab WHERE id = 1 OR 1 = 1;
- mysql> CREATE TABLE f(1 int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 int)' at line 1